Tuesday, January 22, 2013

Applying 11gR2 database patch


Apply the latest CPU patches for 11gR2 (11.2.0.2) using opatch

Steps:
1: Install the latest OPATCH
2: Apply the DB patches
 
Step #1
Check current version of opatch
$ opatch lsinv | grep -i version
Oracle Interim Patch Installer version 11.2.0.3.0
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.2.0

Delete the OPatch directory from $ORACLE_HOME

$ cd $ORACLE_HOME
$ pwd
/u01/oracle/maria/db/11.2.0
$ rm -rf OPatch


Download the latest Opatch, and unzip it in $ORACLE_HOME

$ cp /staging/11gR2/opatch/p6880880_112000_HPUX-IA64.zip .
$ unzip p6880880_112000_HPUX-IA64.zip
 

Change to the new OPatch directory and run emocmrsp to create a response file that opatch will use

$ cd OPatch
$ cd ocm/bin
$ pwd
/u01/oracle/maria/db/11.2.0/OPatch/ocm/bin
$ ./emocmrsp

……..
The OCM configuration response file (ocm.rsp) was successfully created.
 

Verify the new version of opatch

$ opatch lsinv | grep -i version
Oracle Interim Patch Installer version 11.2.0.3.3
OPatch version    : 11.2.0.3.3
OUI version       : 11.2.0.2.0

 
Cleanup by deleting the no longer needed zip file:

$ rm -rf p6880880_112000_HPUX-IA64.zip

Step #2
Stop the Database

SQL> shutdown immediate
 

Stop the listener

$ lsnrctl stop $ORACLE_SID
LSNRCTL for HPUX: Version 11.2.0.2.0 - Production on 22-JAN-2013 14:42:27
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCmaria))
The command completed successfully
 

Apply the patch
- Copy the patch to an appropriate directory, unzip the file, change to the patch directory and run opatch
 

$ cd patch/
$ cp /staging/11gR2/patchsets/11.2.0.2/oneoffs/p14727315_112020_HPUX-IA64.zip .
$ unzip p14727315_112020_HPUX-IA64.zip
$ cd 14727315
$ opatch apply -silent -ocmrf $ORACLE_HOME/OPatch/ocm/bin/ocm.rsp
Oracle Interim Patch Installer version 11.2.0.3.3
Copyright (c) 2012, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/oracle/maria/db/11.2.0
Central Inventory : /var/opt/oracle
   from           : /u01/oracle/maria/db/11.2.0/oraInst.loc
OPatch version    : 11.2.0.3.3
OUI version       : 11.2.0.2.0
Log file location : /u01/oracle/maria/db/11.2.0/cfgtoollogs/opatch/opatch2013-01-22_14-22-37PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   14275621  14727315
Do you want to proceed? [y|n]
Y (auto-answered by -silent)
User Responded with: Y
All checks passed.
…....
Verifying the update...
Composite patch 14727315 successfully applied.
Log file location: /u01/oracle/maria/db/11.2.0/cfgtoollogs/opatch/opatch2013-01-22_14-22-37PM_1.log
OPatch succeeded.


Verify patch was applied

$ opatch lsinv | grep 14727315
Patch  14727315     : applied on Tue Jan 22 14:25:47 EST 2013
Patch description:  "Database Patch Set Update : 11.2.0.2.9 (14727315)"
     10238786, 10132870, 10247152, 9953542, 14727315, 9572787, 15896434

 
Start the database and the listener

SQL> startup mount

ORACLE instance started.
….
 
$ lsnrctl start $ORACLE_SID

 Cleanup by deleting the zip file which is no longer needed.

Monday, January 21, 2013

Oracle Data Pump Export Utility


Oracle Data Pump replaced the older ‘exp’ and ‘imp’ utilities.

Create Data Pump Directory in the source DB host specifying the OS directory where the export will unload the data into a .dmp file

You can skip this step, and use an existing Directory.

SQL> CREATE DIRECTORY schema_refresh AS '/staging/tmpdir/dwdb';
Directory created.

SQL> SELECT directory_name, directory_path from all_directories;


SCHEMA_REFRESH
/staging/tmpdir/dwdb


I prefer to use a parameter file with expdp.
Create a parameter file to use with expdp; there a number of options to chose from, below basic options:

$ touch schema_refresh.par
$ vi  schema_refresh.par
" schema_refresh.par" [New file]
DUMPFILE= schema_refresh.dmp
DIRECTORY= SCHEMA_REFRESH
SCHEMAS=whse
EXCLUDE=TABLE:"IN ('USAGE_CONCURRENT_REQUESTS')"

 Run expdp command using the parameter file created in the step above. Preferably, and as per Oracle, do not run it ‘AS SYSDBA’.


$ expdp parfile= schema_refresh.par
…..
Export: Release 11.2.0.3.0 - Production on Mon Jan 21 15:31:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: whse
Password: *****
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
…….
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
….

While expdp is running, CTRL+C will allow one to make a number of changes to the job, such as:

Export> STATUS                {To check status of the job}
Export> KILL_JOB             {To kill the job}
Export> PARALLEL=16    {Assign number of workers to the job}
Export> EXIT_CLIENT      {Exit the client but job continues to run}


From another session, one can attach to a running expdp job:
$ expdp ATTACH=SYS_EXPORT_SCHEMA_01
….
Export: Release 11.2.0.3.0 - Production on Mon Jan 21 16:09:05 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: whse
Password: *****
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_01