Apply Oracle Patch on windows platform
NOTE:412160.1 – Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches
1. Set oracle_home,patch_home, PATH and ORACLE_SID
SET ORACLE_HOME=E:\oracle\11.2.0\dbhome_1
SET PATH=E:\oracle\11.2.0\dbhome_1\bin;E:\oracle\11.2.0\dbhome_1\opatch;%PATH%
SET ORACLE_SID=ORCL
SET PATH=%ORACLE_HOME%\perl\bin;%PATH%
2. Check the Patch compatibility.
Go to patch location where it downloaded, unzip it.
cd E:\patch
opatch prereq CheckConflictAgainstOHWithDetail -ph ./20233168
3. Check the Opatch lsinventory detail
It will give you the already applied patch list
opatch lsinventory -detail
4. Check the opatch version and check the README of the patch you downloaded.
— Check that your opatch version is greater or same as mentioned in README.
opatch version
5. Take a snapshot of Oracle Services from SERVICE.MSC
6. Stop the listener.
-- Check status of listener
lsnrctl status
-- Stop the listener
lsnrctl stop
7. Take the backup of database (optional)
run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup format 'F:\rman\full_db_%t_%sp%p' filesperset 10 database plus archivelog;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format 'F:\rman\cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format 'F:\rman\spfile_%s_%p_%t' spfile;
release channel ch00;
}
8. Create pfile as current Spfile.
create pfile='E:\oracle\initpfile.ora' from spfile;
9. Check the invalid objects before patching
select count(*) from dba_objects where status='INVALID';
10. Check the registry$history table for check already patching history.
-- Used in case of 11g version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;
-- Used in case of 12c version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select patch_id, version, status, Action, Action_time from dba_registry_sqlpatch order by action_time;
11. Check the DBA Registry component is valid before patching:
col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;
12. Shutdown the database:
shutdown immediate;
13. Take the services down in Windows platform:
–You can do from services.msc also
net stop OracleServiceIC
net stop OracleDBConsoleIC
net stop OracleJobSchedulerIC
net stop "Oracle IC VSS Writer Service"
net stop OracleREMExecService
net stop "Oracle Object Service"
net stop ocfs (required only for OCFS installation)
net stop OraFenceService
net stop OracleMTSRecoveryServicenet stop msdtc
net stop winmgmt
sc config Winmgmt start= disabled
14. Take the backup of Oracle home
–copy and paste in another folder for backup
15. Take the backup of Oracle Inventory
–copy and paste in another folder for backup
16. Apply the patch
Go to patch directory
— Follow the readme steps for Installation the patch
cd 20233168
opatch apply
17. Check the opatch log file generated at %ORACLE_HOME%\cfgtoollogs\opatch for error
18. Start the Oracle database Service for Post installation steps mentioned in readme.
19. Execute the Post steps for bundle patch
-- In 11g readme steps need to follow for each patch have different steps:
cd %ORACLE_HOME%\Bundle\Patch36
STARTUP
SQL> @catwinbundle.sql
SQL> QUIT
-- In 12c readme steps need to follow:
cd %ORACLE_HOME%\opatch
datapatch -verbose
20. Check the log files for errors:
$ORACLE_BASE/cfgtoollogs/catbundle
catbundle_WINBUNDLE__APPLY_.log
catbundle_WINBUNDLE__GENERATE_.log
21. Compile the invalid objects and verify with before patching count.
cd %ORACLE_HOME%\rdbms\admin
sqlplus /nolog
CONNECT / AS SYSDBA
@utlrp.sql
select count(*) from dba_objects where status='INVALID';
22. Check the db_registry;
col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;
23. Verify the registry$history table for patch applied:
-- Used in case of 11g version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select action_time,action,version,comments from registry$history;
-- Used in case of 12c version
col action_time for a28
col action for a6
col version for a8
col comments for a30
set line 999 pages 999
select patch_id, version, status, Action, Action_time from dba_registry_sqlpatch order by action_time;
24. Start the oracle and windows Services.
— you can start from services.msc window
net start OracleServiceIC
net start OracleDBConsoleIC
net start OracleJobSchedulerIC
net start "Oracle IC VSS Writer Service"
net start OracleREMExecService
net start "Oracle Object Service"
net start ocfs (required only for OCFS installation)
net start OraFenceService
net start OracleMTSRecoveryServicenet start msdtc
net start winmgmt
25. Check the listener connectivity
sqlplus sys@IC as sysdba
Rollback plan:
1. Copy the old oracle home back.
2. Restore the database backup
Restore Script Through RMAN:
=============================
run{
allocate channel t1 type DISK;
set until time "to_date('2015/02/27 12:52:00','yyyy/mm/dd HH24:MI:SS')";
restore controlfile from 'F:\rman\cntr_18_1';
release channel t1;
}alter database mount;
run {
allocate channel t1 type DISK;
set until time “to_date(‘2015/02/27 12:52:00′,’yyyy/mm/dd HH24:MI:SS’)”;
restore database;
release channel t1;
}
run {
allocate channel t1 type DISK;
set until time “to_date(‘2015/02/27 12:52:00′,’yyyy/mm/dd HH24:MI:SS’)”;
recover database;
release channel t1;
}
alter database open resetlogs;
resource: https://smarttechways.com/2015/03/05/steps-to-apply-windows-patch-20233168-jan-2015/