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.

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;

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.

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

Check for view recompilation

Use the following to identify which DB's need view recompilation by using the following PL/SQL as the parameter for a SQL Script job:

WHENEVER SQLERROR EXIT FAILURE;
declare 
 xvar number;
begin
SELECT count(*) into xvar FROM sys.registry$history where ID = '6452863';
if xvar=0 then
  dbms_output.put_line('Needs View Recompliation');
  raise_application_error(-20101,'Needs View Recompilation');
else
  dbms_output.put_line('View Recompliation Already Done');
end if;
end;
/

Run that against all targets, any that fail likely need the view recompilation job run.

Error after 11g upgrade

Saw the following error in utlrp after upgrading a db to 11gR2:

Warning: XDB now invalid, could not find xdbconfig

After a bit of digging I found metalink doc 944088.1.

Check your LD_LIBRARY_PATH for DB and Listener. If not set correctly you'll need to restart both DB and Listener, and then try utlrp again.

Now with syntax highlighting goodness

Via this excellent post, we now have syntax highlighting.

code goes here

Much better looking than quotes or pre alone.