Thursday, February 24, 2011

Clean up old xauth entries

In a followup to a previous post on forwarding x sessions and su, here's a quick way to clean up old xauth entries.

> xauth list | cut -f1 -d\  | xargs -i xauth remove {}


You'll recall that cleanup will not happen automatically for the sessions that got su'ed to, so if you have several entries hanging around the one-liner will make quick work of them.

On the subject of support requests

Last night I was working on providing some information to a tech on MOS, and part of their request was screenshots. While I don't mind taking screenshots, it's actually a little quicker to take a screencast.

I used to use CamStudio or Wink, but now that I'm primarily using a Mac, I find that it's much simpler to use the java app at http://screencast-o-matic.com/.

Screencast-o-matic is drop dead simple to use, creates a reasonably small file, and allows you to provide more information to Oracle support with less effort. Edit: It's free for up to 15 minute long files.

Highly recommended.

Wednesday, February 23, 2011

Oracle database file migration script

I recently had a project to migrate datafiles to our new san array, and part of that project I wanted to split apart each of the databases to their own mount points to allow mirror splits to be easier for quick backups. To perform this I pretty much had two options to move all the datafiles (60-70 total/database). I could dump the controlfile to trace, make the modifications then recreate the controlfile or I would have to move the datafiles on the os and perform alter database statements.

I wanted to just go the route of recreating the controlfile because it would have been easier to do mass edits of one file but these particular databases were not registered to an rman catalog, and I did not want to lose backup history even though these were just test environments cloned on a regular basis.

So, the choice I had left was to move files and do the alter databases. After doing a bit of cursory searching I didn't find any scripts to generate the statements for me, so I put together a driver script that made all the necessary steps. Hopefully this will save someone time in the future with a script to start from. This was done against 10gR2.

Some assumptions:

  • The datafile locations assume ofa compliant structure "/uxx/oradata/SID/"
  • For the driver script being created the destination will be all the same, you can change this after the script has been generated.
  • You need to change the source and destination.



set serveroutput on size unlimited for wra;
set linesize 200
set feedback off
set echo off
set timi off
set trimspool on

spool &&SID._generate

CREATE TABLE weg_temp (
step INTEGER,
seq integer,
STATEMENT varchar2(200));

-- Generate move the controlfile script
DECLARE
-- REGEX MATCHING PARMS
source VARCHAR2 (10) := '/u0[1-3]';
destination VARCHAR2 (5) := '/u06';

CURSOR ctrl_file_cur
IS
SELECT name FROM v$controlfile;

CURSOR redo_cur
IS
SELECT MEMBER FROM v$logfile;

CURSOR datafile_cur
IS
SELECT name FROM v$datafile;

CURSOR tempfile_cur
IS
SELECT name FROM v$tempfile;

ctrl_file_rec ctrl_file_cur%ROWTYPE;
redo_rec redo_cur%ROWTYPE;
curholder VARCHAR2 (500);
sqlstatement VARCHAR2 (2000);
unixstatement VARCHAR2 (2000);
loopcount INTEGER;
regexbuffer varchar2(100);
BEGIN
sqlstatement := 'ALTER SYSTEM SET control_files=';
loopcount := 0;

-- Populate the table with the section headings and static steps
insert into weg_temp values (1,0,'-- 1. Change the controlfile locations in the spfile');
insert into weg_temp values (2,0,'-- 2. Shutdown the database');
insert into weg_temp values (2,1, 'shutdown immediate;');
insert into weg_temp values (3,0,'# 3. Move the control files on the os');
insert into weg_temp values (4,0,'# 4. Move the redo log files on the os');
insert into weg_temp values (5,0,'# 5. Move the datafiles on the os');
insert into weg_temp values (6,0,'# 6. Move the tempfiles on the os');
insert into weg_temp values (7,0, '-- 7. Start the database in mount');
insert into weg_temp values (7,1, 'startup mount;');
insert into weg_temp values (8,0, '-- 8. Change redo log files in the database');
insert into weg_temp values (9,0, '-- 9. Change datafiles in the database');
insert into weg_temp values (10,0, '-- 10. Change tempfiles in the database');
insert into weg_temp values (11,0, '-- 11. Open the database and hope it works.');
insert into weg_temp values (11,1, 'alter database open;');

-- Spacing for file
insert into weg_temp values (2,-1,NULL);
insert into weg_temp values (3,-1,NULL);
insert into weg_temp values (4,-1,NULL);
insert into weg_temp values (5,-1,NULL);
insert into weg_temp values (6,-1,NULL);
insert into weg_temp values (7,-1,NULL);
insert into weg_temp values (8,-1,NULL);
insert into weg_temp values (9,-1,NULL);
insert into weg_temp values (10,-1,NULL);
insert into weg_temp values (11,-1,NULL);



OPEN ctrl_file_cur;

LOOP
FETCH ctrl_file_cur INTO ctrl_file_rec;

EXIT WHEN ctrl_file_cur%NOTFOUND;

IF loopcount > 0
THEN
sqlstatement := sqlstatement || ', ';
END IF;

-- Calculate the destination filename
regexbuffer := REGEXP_REPLACE (ctrl_file_rec.name, source, destination);
sqlstatement :=
sqlstatement
|| ''''
|| regexbuffer
|| '''';

insert into weg_temp values (3 ,loopcount+1,'mv ' || ctrl_file_rec.name || ' ' || regexbuffer);
loopcount := loopcount + 1;

END LOOP;

CLOSE ctrl_file_cur;
-- Finish out the sqlstatement
sqlstatement := sqlstatement || ' SCOPE=SPFILE;';
-- Put generated sql statement into the table.
insert into weg_temp values (1,1,sqlstatement);

OPEN redo_cur;

-- Reset loopcount back at 0
loopcount := 0;
LOOP
FETCH redo_cur INTO curholder;

EXIT WHEN redo_cur%NOTFOUND;
regexbuffer := REGEXP_REPLACE (curholder, source, destination);

insert into weg_temp values (4 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);

insert into weg_temp values (8 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');

loopcount := loopcount + 1;

END LOOP;

CLOSE redo_cur;

OPEN datafile_cur;

-- Reset loopcount back at 0
loopcount := 0;

LOOP
FETCH datafile_cur INTO curholder;

EXIT WHEN datafile_cur%NOTFOUND;

regexbuffer := REGEXP_REPLACE (curholder, source, destination);
insert into weg_temp values (5 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);

insert into weg_temp values (9 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');

loopcount := loopcount + 1;

END LOOP;

CLOSE datafile_cur;


OPEN tempfile_cur;
-- Reset loopcount back at 0
loopcount := 0;

LOOP
FETCH tempfile_cur INTO curholder;

EXIT WHEN tempfile_cur%NOTFOUND;

regexbuffer := REGEXP_REPLACE (curholder, source, destination);
insert into weg_temp values (6 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);

insert into weg_temp values (10 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');
loopcount := loopcount + 1;

END LOOP;

CLOSE tempfile_cur;

commit;
END;
/

spool off;

spool &&SID._script.txt

DECLARE
CURSOR weg_temp_cur
IS
SELECT statement
FROM weg_temp
ORDER BY step, seq;

wtr weg_temp_cur%ROWTYPE;
BEGIN
OPEN weg_temp_cur;

LOOP
FETCH weg_temp_cur INTO wtr;

EXIT WHEN weg_temp_cur%NOTFOUND;
DBMS_OUTPUT.put_line (wtr.statement);
END LOOP;

CLOSE weg_temp_cur;
END;
/

spool off;

drop table weg_temp;
exit;

Thursday, February 3, 2011

Grid control 11g... and unable to patch using deployment procedures

Recently upgraded grid control from 10.2.0.5 to 11g, and now unable to apply patches using the standard deployment procedure.

The job bombs out at the initialize step.

Here's the error
Downloading the patches from My Oracle Support.
Error while evaluating expression:[Error evaluating expression: ${doInitialization(step.guid)} : [no oraInstaller in java.library.path]]


Found note 427577.1, installed patches 9495798, 9705138, and 10388644. Still not working.

Couldn't find any additional info, so opened a ticket with Oracle support...

Resolution log:
1) Reran OPatch Update job and Refresh from Metalink, restarted job, still broke
2) Deleted the job, restarted from scratch again and noticed a warning during the "Search and select software updates" screen -- the component already exists in the software library.
So I chose not to overwrite the version in the software library, and instead of failing at the initialize stage, now the job fails a few steps down at the "Stage Patches" step.

Here's that error:

Received null input stream of binary file, null', for the item identified by, 'Components/Oracle Software Updates/p10248523_11.2.0.2.0_46(Revision: 0.1)'. See log file '$ORACLE_HOME/sysman/log/emoms.log on Oracle Management Service host for more details.


At this point my guess is there's something wrong with the software library not getting upgraded correctly from 10g to 11g. We'll have to see what Oracle support recommends...

--- Update 2/8
Oracle support eventually concurred it's likely a problem with the software library. "Trying fix described in Oracle Internal Support Portal: Document 602886.1", which looks to be a pretty straightforward method to rebuild the software library.

Making a cold backup now, will post more info later.
--- Update 2/17

Rebuilding the software library was successful, but did not resolve our issues with patching. Oracle support suggests that either we used the wrong JDK version (we used 1.6.0_24 rather than the required 1.6.0_18), or I did not use the required 64 bit flag when installing the weblogic server.

So -- we're reinstalling grid control in a new location on the same server to try and see if that resolves our patching issues, and if it does migrate all our targets to it.

--- Update 2/17
Found someone else who experienced a similar error to the one above who also had to reinstall.

Seems this is the correct way to start the weblogic installer for grid (where JAVA_HOME is set to your 1.6.0_18 JDK):
$JAVA_HOME/bin/java -d64 -jar wls1032_generic.jar


As for install guides, I think I would recommend this one from CamOra ICT.

Tuesday, February 1, 2011

x forwarding and sudo for oracle installs

From my linux desktop I ssh to my oracle box and forward X packets back to my desktop over ssh.

bill@wgwsvm:~$ ssh -X 192.168.56.101


When I invoke xclock this works like you would expect.

If I wanted to forward xpackets from other users that I might ssh to I have to do two additional steps. Obviously set the DISPLAY and additionally set the x auth token to forward through the connection.

bill@wgdbvm:/home/bill> xauth list
wgdbvm/unix:10 MIT-MAGIC-COOKIE-1 4696e4a4a52fac8af8b85e42195baa84
bill@wgdbvm:/home/bill> echo $DISPLAY
localhost:10.0
bill@wgdbvm:/home/bill> sudo su - oracle
[sudo] password for bill:
oracle@wgdbvm:/home/oracle> xauth add wgdbvm/unix:10 MIT-MAGIC-COOKIE-1 4696e4a4a52fac8af8b85e42195baa84
oracle@wgdbvm:/home/oracle> export DISPLAY=localhost:10.0




One note, when disconnecting from my bill account the xauth file is cleaned up automatically for me. But oracle's xauth will still contain the token, so it needs to be deleted manually.


oracle@wgdbvm:/home/oracle> xauth list
wgdbvm/unix:10 MIT-MAGIC-COOKIE-1 4696e4a4a52fac8af8b85e42195baa84
oracle@wgdbvm:/home/oracle> xauth remove wgdbvm/unix:10