Steps to apply Oracle database Patch on windows platform

Apply Oracle Patch on windows platform

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s