Apply latest PSU patch on Oracle 11gr2

Hello Guys,

Here are the below step to apply latest PSU patch to you databases running on ASM file system. Note, this is not a clustered environment. This is single instance database with ASM file system.

Please download the latest PSU patch from metalink. In my case latest patch id is 17272731 and I have downloaded the file as p17272731_112030_Linux-x86-64.zip

I have copied this zip file to "/oradata/PSUOCT2013/" directory location in my server. And then unzip the file downloaded from metalink.

Here are few details before we proceed:

Oracle Version: 11.2.0.3.0
OS Version: Red Hat Enterprise Linux Server release 5.10 (x86_64)
Oracle Doc ID: 1494646.1
ORACLE_HOME: /oradata/app/oracle/product/11.2.0.3/db/
GRID_HOME: /oradata/app/oracle/product/11.2.0.3/grid/

Note : Take full backup of all the database before starting the activity.



Step 1:  Stop all the Oracle services running.

If this is a GI Home environment, as the database home owner execute:
Stop the ASM Instance and Databases
#crsctl stop resources



Step 2: Backing up the Grid Home and RDBMS Home

Make a tar backup for both the homes.
tar -zcvf grid-rdbms-home_ddmmyy.tar.gz /oradata/app/oracle



Step3: Run the pre root script

As the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/rootcrs.pl -unlock

If this is an Oracle Restart Home, as the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/roothas.pl –unlock


Step 4: Apply the CRS patch

As the GI home owner execute:
$/oradata/app/oracle/product/11.2.0.3/grid/OPatch/opatch napply -oh /oradata/app/oracle/product/11.2.0.3/grid –local /oradata/PSUOCT2013/17076717

As the GI home owner execute:
$/oradata/app/oracle/product/11.2.0.3/grid/OPatch/opatch apply -oh /oradata/app/oracle/product/11.2.0.3/grid -local /oradata/PSUOCT2013/16902043




Step 5: Run the pre script for DB component of the patch

As the database home owner execute:
$/oradata/PSUOCT2013/17076717/custom/server/17076717/custom/scripts/prepatch.sh -dbhome /oradata/app/oracle/product/11.2.0.3/db



Step 6: Apply the DB patch

As the database home owner execute:
$/oradata/app/oracle/product/11.2.0.3/db/OPatch/opatch napply –oh /oradata/app/oracle/product/11.2.0.3/db –local /oradata/PSUOCT2013/17076717/custom/server/17076717

$/oradata/app/oracle/product/11.2.0.3/db/OPatch/opatch apply -oh /oradata/app/oracle/product/11.2.0.3/db -local /oradata/PSUOCT2013/16902043



Step 7: Run the post script for DB component of the patch.

As the database home owner execute:
/oradata/PSUOCT2013/17076717/custom/server/17076717/custom/scripts/postpatch.sh -dbhome /oradata/app/oracle/product/11.2.0.3/db



Step 8: Run the post script

If this is an Oracle Restart Home, as the root user execute:
# /oradata/app/oracle/product/11.2.0.3/grid/crs/install/roothas.pl -patch



Step 9: Start the Oracle services

If this is an Oracle Restart Home environment, as the database home owner execute:
#crsctl start resource -all

Start the ASM Instance and Databases

Step 10:

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT

Good luck !!!!!!!!!!

Drop table commands ends up with ORA-02449

Hello,

Many have noticed that when you try to drop table we face ORA-02449 error. This is due to the primary key in the table which is referring to the foreign key of another table.

Error occurred is :

ORA-02449: unique/primary keys in table referenced by foreign keys


To overcome this situation, make sure you do not need this table. And then fire below command to delete the table. This drop statement with cascade constraints option will delete the primary key constraints from the table and drop the table.


DROP TABLE EMP CASCADE CONSTRAINTS;

table dropped.