|
| Practical Steps of Applying 11g database Oneoff Patch 11.1.0.7.4 !!!!! |
| Tuesday, November 30, 2010 |
These steps have been followed on RHEL 5.3 after applying Database Patch Set 11.1.0.7.
--> Download Oneoff patch 9654987
Issue : there was lower version of Opatch then we have applied latest available patch of Opatch utility.
[oracle@testdbserver 9654987]$ /oraapp/TESTDB/product/11.1.0/db_1/OPatch/opatch apply
Invoking OPatch 11.2.0.1.3
Oracle Interim Patch Installer version 11.2.0.1.3
Copyright (c) 2010, Oracle Corporation. All rights reserved.
Oracle Home : /oraapp/TESTDB/product/11.1.0/db_1
Central Inventory : /oraapp/TESTDB/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.3
OUI version : 11.1.0.7.0
OUI location : /oraapp/TESTDB/product/11.1.0/db_1/oui
Log file location : /oraapp/TESTDB/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2010-09-23_09-54-32AM.log
Patch history file: /oraapp/TESTDB/product/11.1.0/db_1/cfgtoollogs/opatch/opatch_history.txt
ApplySession applying interim patch '9654987' to OH '/oraapp/TESTDB/product/11.1.0/db_1'
Running prerequisite checks...
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: jaswinder.singh@wipro.com
Provide your My Oracle Support password to receive security updates via your My Oracle Support account.
Password (optional):
Unable to establish a network connection to Oracle. If your systems require a
proxy server for outbound Internet connections, enter the proxy server details
in this format:
[@][:]
If you want to remain uninformed of critical security issues in your
configuration, enter NONE
Proxy specification: NONE
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oraapp/TESTDB/product/11.1.0/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '9654987' for restore. This might take a while...
Backing up files affected by the patch '9654987' for rollback. This might take a while...
Execution of 'sh /oraapp/Oracle_Media/9654987/custom/scripts/pre -apply 9654987 ':
Return Code = 0
Patching component oracle.network.listener, 11.1.0.7.0...
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglgr.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglro.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsgcs.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib/libnlsnr11.a" with "network/lib/libnlsnr11.a/nsglsn.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib32/libnlsnr11.a" with "network/lib32/libnlsnr11.a/nsglgr.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/network/lib32/libnlsnr11.a" with "network/lib32/libnlsnr11.a/nsglro.o"
....
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kghu.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcsj.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcodfdef.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcs.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/qcto.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgsk.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgi.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgl.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgl2.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/kgh.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libgeneric11.a" with "lib32/libgeneric11.a/koll.o"
Patching component oracle.network.rsf, 11.1.0.7.0...
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libnro11.a" with "lib/libnro11.a/ncrfidsa.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libnro11.a" with "lib/libnro11.a/ncrfgp.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libnro11.a" with "lib32/libnro11.a/ncrfidsa.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib32/libnro11.a" with "lib32/libnro11.a/ncrfgp.o"
Patching component oracle.rdbms.plsql, 11.1.0.7.0...
Patching component oracle.rdbms.rman, 11.1.0.7.0...
Patching component oracle.sdo.locator, 11.1.0.7.0...
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/md/admin/sdomigrt.sql"
Patching component oracle.sysman.console.db, 11.1.0.7.0...
Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistoryBean.class"
Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistoryController.class"
Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/history/ConfigHistorySearch.class"
Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/ecm/config/compare/CompareWizSecondConfigBean.class"
Patching component oracle.rdbms.dbscripts, 11.1.0.7.0...
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/catexp.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/dbmsaqds.plb"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtaqds.plb"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtaqiu.plb"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/catmetx.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtlsib.plb"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtlsby.plb"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/prvtlmd.plb"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/e1001000.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/c1001000.sql"
Patching component oracle.javavm.server, 11.1.0.7.0...
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/javavm/install/initjvm.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/javavm/install/jvmursc.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/javavm/install/jvm_exp.sql"
Patching component oracle.precomp.common, 11.1.0.7.0...
Patching component oracle.rdbms, 11.1.0.7.0...
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/libperfsrv11.a" with "rdbms/lib/libperfsrv11.a/qcodfdef_PERF.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/libknlopt.a" with "rdbms/lib/libknlopt.a/ktd.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/libknlopt.a" with "rdbms/lib/libknlopt.a/jox.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/atb.o"
Updating archive file "/oraapp/TESTDB/product/11.1.0/db_1/lib/libserver11.a" with "lib/libserver11.a/aud.o"
.........
Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/jlib/CDC.jar" with "/rdbms/jlib/CDC.jar/oracle/CDC/PublishApi.class"
Updating jar file "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/jlib/CDC.jar" with "/rdbms/jlib/CDC.jar/oracle/CDC/Purge.class"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/view_recompile/recompile_precheck_jan2008cpu.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/view_recompile/view_recompile_jan2008cpu.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/sdo_oct2009.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/bug9016295.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/bug9371993.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/amdcpu.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/apscpu.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/cpu/scripts/xoqcpu.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/psu/11.1.0.7.4/catpsu.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/psu/11.1.0.7.4/catpsu_rollback.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/catbundle.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/bundledata_PSU.xml"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/admin/incremental_compress.sql"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/ktd.o"
Copying file to "/oraapp/TESTDB/product/11.1.0/db_1/rdbms/lib/jox.o"
Running make for target itnslsnr
Running make for target client_sharedlib
Running make for target iwrap
Running make for target irman
Running make for target proc
Running make for target client_sharedlib
Running make for target iextproc
Running make for target ioracle
ApplySession adding interim patch '9654987' to inventory
Verifying the update...
Inventory check OK: Patch ID 9654987 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9654987 are present in Oracle Home.
Execution of 'sh /oraapp/Oracle_Media/9654987/custom/scripts/post -apply 9654987 ':
Return Code = 0
The local system has been patched and can be restarted.
OPatch succeeded.
[oracle@testdbserver 9654987]$
[oracle@testdbserver 9654987]$ cd $ORACLE_HOME/rdbms/admin
[oracle@testdbserver admin]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 23 10:12:14 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP
SQL> @catbundle.sql psu apply
1 row created.
It takes 5-10 mintues to complete.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/oraapp/TESTDB/product/11.1.0/db_1/cfgtoollogs/catbundle/catbundle_PSU_TESTDB_APPLY_2010Sep23_10_15_02.log
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
COUNT(*)
----------
0
1 row selected.
SQL> QUIT
SQL> SELECT NAMESPACE,VERSION,ID,BUNDLE_SERIES,COMMENTS FROM dba_registry_history;
NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
------------------------------ ------------------------------ ---------- ------------------------------ ----------------------------------------
SERVER 11.1.0.7.0 Upgraded from 11.1.0.6.0
SERVER 11.1.0.7 4 PSU PSU 11.1.0.7.4
SQL>
[oracle@testdbserver admin]$ cd $ORACLE_HOME/cpu/view_recompile
[oracle@testdbserver view_recompile]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 23 10:27:06 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> @recompile_precheck_jan2008cpu.sql
Running precheck.sql...
Number of views to be recompiled :2838
-----------------------------------------------------------------------
Number of objects to be recompiled :5658
Please follow the README.txt instructions for running viewrecomp.sql
PL/SQL procedure successfully completed.
SQL>
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2161400 bytes
Variable Size 939525384 bytes
Database Buffers 1191182336 bytes
Redo Buffers 5017600 bytes
Database mounted.
Database opened.
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
No. of Invalid Objects is :51
Please refer to README.html to for instructions on validating these objects
PL/SQL procedure successfully completed.
Logfile for the current viewrecomp.sql session is : vcomp_TESTDB_23Sep2010_10_33_13.log
SQL>
--> Check the log file for any errors. The log file is in the current directory and is named: vcomp__.log
[oracle@testdbserver view_recompile]$ cat vcomp_TESTDB_23Sep2010_10_33_13.log
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
No. of Invalid Objects is :51
Please refer to README.html to for instructions on validating these objects
PL/SQL procedure successfully completed.
Logfile for the current viewrecomp.sql session is : vcomp_TESTDB_23Sep2010_10_33_13.log
[oracle@testdbserver view_recompile]$
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
COUNT(*)
----------
51
SQL>
[oracle@testdbserver view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@testdbserver admin]$
[oracle@testdbserver admin]$
[oracle@testdbserver admin]$ sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 23 10:40:02 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2010-09-23 10:40:14
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2010-09-23 10:40:53
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT count(*) FROM dba_objects WHERE status='INVALID';
COUNT(*)
----------
0
SQL>
===> Patchset and OneOff patch is applied successfully to the TEST instance;
SQL> SELECT * FROM v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
SQL>
SQL> column COMMENTS format a30
SQL> SELECT NAMESPACE,VERSION,ID,COMMENTS FROM dba_registry_history;
NAMESPACE VERSION ID COMMENTS
------------------------------ ------------------------------ ---------- ------------------------------
SERVER 11.1.0.7.0 Upgraded from 11.1.0.6.0
SERVER 11.1.0.7 4 PSU 11.1.0.7.4
6452863 view recompilation
SQL>
SQL> SELECT ACTION_TIME,ACTION,NAMESPACE,VERSION,ID,COMMENTS,BUNDLE_SERIES FROM registry$history where ID = '6452863';
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES
------------------------------ ---------- ------------------------------ ---------- ---------- -------------------- ------------------------------
23-SEP-10 10.34.56.655633 AM CPU 6452863 view recompilation
SQL> SELECT ACTION_TIME,ACTION,NAMESPACE,VERSION,ID,COMMENTS,BUNDLE_SERIES FROM registry$history where ID ='4';
ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SERIES
------------------------------ ---------- ------------------------------ ---------- ---------- -------------------- ------------------------------
23-SEP-10 10.18.30.433026 AM APPLY SERVER 11.1.0.7 4 PSU 11.1.0.7.4 PSU |
posted by Jaswinder Singh @ 3:57 AM   |
|
|
|
|
|
