Automated Process to Apply Oracle PSU for Multiple Instances (Oracle 10g)

Assumes that there is more than one instance of an Oracle database in a Streams environment.

Assumes that a patch is to be applied along with a PSU.

Assumes that the listeners have been stopped.

Assumes that all relevant patches have been copied to $ORACLE_HOME/patches.

Should also work for 11g environments.

The operator is asked to run the script in the following order:


Apply the Patch (e.g. Patch ID 11724977)
1. Shutdown all database instances 
   $ sh patch_it.sh shutdown

2. Apply the patch 
   $ sh patch_it.sh patch_apply

3. Start up the database instances 
   $ sh patch_it.sh startup 

Apply the PSU 

1. Stop streams (note that for this case, the PSU does not require that the instances be shutdown). 

   $ sh patch_it.sh stop_streams 

2. Apply PSU
   $ sh patch_it.sh psu_apply 

3. Start streams 

   $ sh patch_it.sh start_streams 

Post-Installation Procedure
1. Check that the patch has been installed (this verifies that patch 11724977 has been applied) 
   $ sh patch_it.sh patch_check 

Back-out Procedure
1. Shutdown all database instances 
   $ sh patch_it.sh shutdown



2. Rollback the patch (Patch ID 11724977) 
   $ sh patch_it.sh patch_rollback

3. Start up all instances 
   $ sh patch_it.sh startup

4. Stop streams 
   $ sh patch_it.sh stop_streams

5. Rollback the PSU 
   $ sh patch_it.sh psu_rollback 

patch_it.sh Shell Script

#!/bin/bash

# Shuts down all streams processes
function stop_streams {
   echo
   echo "Shutting Down Streams"
   echo
   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' @stop_streams.sql << EOD
EOD
   done
}
# Starts up all streams processes
function start_streams {
   echo
   echo "Start Up Streams"
   echo
   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' @start_streams.sql << EOD
EOD
   done
}

# Shuts down all databases
function shutdown {
   echo
   echo "Shutdown databases"
   echo


   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      shutdown immediate
EOD
      done

   echo
   echo "All databases have been shutdown"
   echo
}

# Starts up all databases
function startup {
   echo
   echo "Startup databases"
   echo

   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      startup
EOD
   done

   echo
   echo "All databases have been started"
   echo
}

# Starts up all databases in UPGRADE mode
function startup_upgrade {
   echo
   echo "Startup databases - upgrade"
   echo

   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      startup upgrade
      spool patch_$NAME.log
      @?/rdbms/admin/catupgrd.sql
      spool off
EOD
   done

   echo
   echo "All databases have been started in upgrade mode"
   echo
}

# Run utlrp
function run_utlrp {
   echo
   echo "Running utlrp..."
   echo

   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      shutdown immediate
EOD
   done

   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      startup
      @?/rdbms/admin/utlrp.sql
EOD
   done

   echo
   echo "All databases have been upgraded"
   echo

}

# Check database registy
function check_db_registry {
   echo
   echo "Checking database registry..."
   echo

   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      @rmOCM.sql
      SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;
EOD
   done

}

# Checks opatch functionality
function patch_check {
   echo "Checking OPatch Functionality"

   cd $ORACLE_HOME/patches
   $OPATCH prereq CheckConflictAgainstOHWithDetail -jre $ORACLE_HOME/$JRE -phBaseDir ./$PATCH_ID -invPtrLoc $ORACLE_HOME/oraInst.loc || { echo "opatch check failed"; exit 1; }
}

# Applies patch
function patch_apply {
   echo "Applying Patch $PATCH_ID"
   cd $ORACLE_HOME/patches/$PATCH_ID
   $OPATCH apply -jre $ORACLE_HOME/$JRE -invPtrLoc $ORACLE_HOME/oraInst.loc || { echo "opatch apply $PATCH_ID failed"; exit 1; }
}

# Applies psu_apply
function psu_apply {
   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' << EOD
      @?/rdbms/admin/catbundle.sql psu apply
EOD
   done
}

# Rolls back patch
function patch_rollback {
   echo "Rolling back patch"
   cd $ORACLE_HOME/patches/$PATCH_ID
   $OPATCH rollback -id $PATCH_ID -jre $ORACLE_HOME/$JRE -invPtrLoc $ORACLE_HOME/oraInst.loc || { echo "opatch rollback $PATCH_ID failed"; exit 1; }
     
}

# Rollback psu_apply
function psu_rollback {
   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      NAME=`echo $NAME | tr '[:lower:]' '[:upper:]'`
      sqlplus '/ as sysdba' << EOD
      @?/rdbms/admin/catbundle_PSU_${NAME}_ROLLBACK.sql

EOD
   done
}

function downgrade_db {
   cp -rfp $ORACLE_HOME/rdbms/admin/catrelod.sql .
   cp -rfp $ORACLE_HOME/network/admin .
   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' <<EOD
      startup downgrade
      SPOOL downgrade_$NAME.out
      @?/rdbms/admin/catdwgrd.sql
      SPOOL OFF
      SHUTDOWN IMMEDIATE
EOD
   done
}

function downgrade_cat {
   cp -rfp catrelod.sql $ORACLE_HOME/rdbms/admin/
   cp -rfp admin $ORACLE_HOME/network/
   for NAME in $NAME_LIST
   do
      export ORACLE_SID=$NAME
      sqlplus '/ as sysdba' <<EOD
      startup downgrade
      SPOOL catrelod_$NAME.out
      @?/rdbms/admin/catrelod.sql
      SPOOL OFF
      SHUTDOWN IMMEDIATE
EOD
   done
}

##########################
# main                   #
##########################

# Must NOT be run as root
if [ "$(id -n -u)" != "oracle" ]
then
   echo "This script must be run as oracle" 1>&2
   exit 1
fi

ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1
OPATCH=$ORACLE_HOME/patches/OPatch/opatch
JRE=`ls -l $ORACLE_HOME|grep jre1|awk -F" " '{ print $9 }'`
#
# Change to relevant Patch ID
#
PATCH_ID=11724962
HOSTNAME=`hostname -f | awk -F . '{print $1}'`
# Hostname is in the form of servername-1a.<domain_name>/servername-2a.<domain_name>
# etc...  Matching on 1, we can determine if we are on servername-1 or servername-4
match="1"

if [[ "$HOSTNAME" =~ "${match}" ]]; then
     NAME_LIST="dbwhrs01 dbprod01 dbweb01"
     export NAME_LIST
  else
     NAME_LIST="dbwhrs02 dbprod02 dbweb02"
     export NAME_LIST
fi

case "$1" in
'startup')
   startup
;;
'startup_upgrade')
   startup_upgrade
;;
'run_utlrp')
   run_utlrp
;;
'check_db_registry')
   check_db_registry
;;
'shutdown')
   shutdown
;;
'patch_check')
   patch_check
;;
'patch_apply')
   patch_apply
;;
'psu_apply')
   psu_apply
;;
'patch_rollback')
   patch_rollback
;;
'psu_rollback')
   psu_rollback
;;
'start_streams')
   start_streams
;;
'stop_streams')
   stop_streams
;;
'downgrade_db')
   downgrade_db
;;
'downgrade_cat')
   downgrade_cat
;;
*)
   echo "Usage: $0 [startup|shutdown|patch_check|patch_apply|psu_apply|startup_upgrade|run_utlrp|check_db_registry|stop_streams|start_streams|patch_rollback|psu_rollback|downgrade_db|downgrade_cat]"
esac

stop_streams.sql Script
set serveroutput on size 1000000
declare

v_apply_name varchar2(100) := '';
v_capture_name varchar2(100) := '';
v_propagation_name varchar2(100) := '';

begin

   select apply_name into v_apply_name from dba_apply;
   select capture_name into v_capture_name from dba_capture;
   select propagation_name into v_propagation_name from dba_propagation;

   begin
      DBMS_APPLY_ADM.STOP_APPLY(apply_name => v_apply_name);
   exception
   when others then
      null;
   end;

   begin
      DBMS_CAPTURE_ADM.STOP_CAPTURE(capture_name => v_capture_name);
   exception
   when others then
      null;
   end;

   begin
      DBMS_PROPAGATION_ADM.STOP_PROPAGATION(v_propagation_name);
   exception
   when others then
      null;
   end;
end;
/

start_streams.sql Script
set serveroutput on size 1000000
declare

v_apply_name varchar2(100) := '';
v_capture_name varchar2(100) := '';
v_propagation_name varchar2(100) := '';

begin

   select apply_name into v_apply_name from dba_apply;
   select capture_name into v_capture_name from dba_capture;
   select propagation_name into v_propagation_name from dba_propagation;

   begin
      DBMS_APPLY_ADM.START_APPLY( apply_name => v_apply_name);
   exception
   when others then
      null;
   end;

   begin
      DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => v_capture_name);
   exception
   when others then
      null;
   end;

   begin
      DBMS_PROPAGATION_ADM.START_PROPAGATION(v_propagation_name);
   exception
   when others then
      null;
   end;
end;
/
rmOCM.sql Script
Our site does not allow OCM component to be installed, hence it needs to be removed.

-- remove old dba jobs, if exists
DECLARE
job_num NUMBER;
CURSOR job_cursor is
    SELECT job
    FROM dba_jobs
    WHERE schema_user = 'ORACLE_OCM'
    AND (what like 'ORACLE_OCM.MGMT_CONFIG.%'
     OR what like 'ORACLE_OCM.MGMT_DB_LL_METRICS.%');
BEGIN
   FOR r in job_cursor LOOP
     sys.DBMS_IJOB.REMOVE(r.job);
     COMMIT;
   END LOOP;
   EXCEPTION
     WHEN OTHERS THEN
       -- ignore any exception
       null;
END;
/

#Rem stop the job
BEGIN
   BEGIN
     -- call to stop the job
     ORACLE_OCM.MGMT_CONFIG.stop_job;  
   EXCEPTION
     WHEN OTHERS THEN
       -- ignore any exception
       null;
  END;
END;
/

-- disable jobs

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

-- drop user ORACLE_OCM

drop user ORACLE_OCM cascade;