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;
