Oracle database Opatch patch operation process _oracle

I. Pre-upgrade preparation work

1. Confirm Database Version

Use DBA Login to query the version of the current database

Sql> select * from V$version; 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition release 10.2.0.5.0-64bi 
pl/sql release 10.2.0.5.0-production 
CORE 10.2.0.5.0 Production 
TNS for 64 -bit windows:version 10.2.0.5.0-production 
nlsrtl Version 10.2.0.5.0–production

2. Query patch Installation Information

Sql> select * from Dba_registry_history;

No records are typically not installed, or you can query the current patch installation with the Opatch lsinventory opatch command after environment settings.

3, query the number of invalid objects database, this number is recorded as N1

sql> SELECT sum (object_name) N1 from dba_objects WHERE status= ' INVALID ';

If there is an invalid object it is best to compile it first

After the compilation completes, execute the following statement again to see the current actual effect object as N2

sql> SELECT sum (object_name) N2 from dba_objects WHERE status= ' INVALID ';

Sql> SELECT object_name from dba_objects WHERE status= ' INVALID ';--store the result in Excel or in a table so that it is easy to check after the patch is finished.

4, Patch Type

Oracle database patches are typically divided into two CPUs and PSU patches.

The full name of the Oracle PSU is patch Set update,oracle, a patch package that is released quarterly for its products, including bug fixes. Oracle picks up the number of downloads that are downloaded by the user, and the patches that have been validated for lower risk are placed in the PSU per quarter. Each PSU contains not only bug fixes but also the latest CPUs. PSU is usually released along with the CPU. PSU is usually incremental, most PSU can be installed directly, but some PSU must require the installation of a previous version of the PSU before you can continue to install, to carefully look at each PSU's Readme document.

The full name of the Oracle CPU is critical Patch Update, where Oracle releases a security patch package for each quarter of its products, usually to fix security risks in the product. CPUs are cumulative (cumulative), that is, the latest CPU patches already contain previous CPU patches, so just install the latest CPU patches.
The security flaw rectification when we want to install is the CPU patch.

5, Query the patch Information

Landing Oracle Website Security topic http://www.oracle.com/technetwork/topics/security Click Learn More to find critical Path Update Advisory

You will see a list of the patches released by Oracle, which contains patches for Oracle product releases.

6, Patch Download

For example, our current Oracle version is 10.2.0.5, this version of the last update patch for July 2013, click on the red link above to see the 10.2.0.5 database patch.

Note: Download the use of Metalink account, according to the type of server download the corresponding version of the patch can be. If there is no Oracle Metalink account cannot download the patch.

7. Database backup work

Do the database backup work before the patch upgrade, use Rman or export the data file way to complete the database preparation. When conditions permit, the operating system is best to make a backup.

After all the above operation, we can do the patch installation work.

Second, the patch installation operation

1, read the installation requirements of the patch

After each patch download the compressed package will contain a copy of the readme.html need to be read carefully before installation. It will typically indicate the database version and operating system version that the patch applies to, what bugs are fixed, and the relevant prompts.

2. Environment setting

2.1. Under Windows environment

Access to computer properties → advanced system settings → system properties → environment variables

Edit path adds%oracle_home%\opatch at the end of path or set path=%path%;%o under cmd Racle_home%\opatch;

2.2. Environment variable setting under Linux

Using ORACLE installation user, set Oracle_home export path= $ORACLE _home/opatch: $PATH.

2.3, Opatch command test

When the environment variable is set successfully, the test Opatch command is available, the terminal opatch–help, you can see the relevant information indicates the environment variable set successfully.

3. Query patch Installation Information

View current patch installation under operating system Opatch Lsinventory–patch

4. Stop Listening and Oracle Services

4.1. Shut down service under Windows

Shut down Oracle-related service processes and stop listening and Oracle services in the service. Close the Plsql client and close the Sqlplus interface.

4.2. Shut down the service under Linux

Shutdown all instances, turn off listening and Oracle related processes. If the installation of GI and other applications using Oracle are stopped, exit the Sqlplus interface.

5, install the patch

Perform opatch apply under the patch directory. For example my patch unpacked in E:\13460968, then CD to the directory, then execute opatch apply

Note: There are times when all Oracle processes are turned off under windows, but D:\oracle\product\10.2.0\db_1\bin\oci.dll is still occupied after the command is executed.

Linux occurs when the process is occupied generally sqlplus process is not closed, you can use the Fuser query is what users occupy, but the mandatory kill time when the database is started may be an error.

Go to the directory to find the file name change, for example to Oci.dllbak, if you perform a patch rollback operation remember to change it back, to perform opatch apply again.

Select Y, and next wait for the patch to be installed.

6, start the database

Windows first initiates Oracle's services and listening in the service. To Sysdba landing system.

Linux to start the Oracle instance and monitor normally. To Sysdba landing system.

7, execute the script, update the patch information to the database

Execute the script in the directory in the patch package and wait for the script to finish running. Linux can find the patch directory to execute.

Sql>@e:\13460968\files\bundle\patch14\catcpu.sql

Note: The location of different patch sets may vary, but the script is generally available.

8, compile the invalid object, execute the following statement

SQL >@?/rdbms/admin/utlrp.sql

Note: Most of the failed object errors can be ignored, the specific types can be checked with the following command.

View the N3 and the first 3rd section of the N2 comparison to see if the new invalid object, if there is a view of what the specific failure object, using the first chapter 3 of the list of the comparison, if unable to process the consulting database administrator processing.

9, query the updated information of the patch

SELECT * from Dba_registry_history;

You can find information about the patch that you just installed. (I installed it once, uninstalled it once, and finally the information that was just installed was logged to the database). Execute Opatch lsinventory opatch at the command line

The entire installation process is complete and the database is normal.

Third, rollback operation

Under normal circumstances, in the case of uncertainty for the cause of the consultation with the DBA to check, determine the cause of the problem, can not verify the cause of the case may be rolled back, the specific rollback operation commands are as follows.

1, if the patch application after the database exception or the occurrence of bugs, you can use opatch rollback patch update operation.

2. Close Oracle related services, set environment variables according to section 2, execute the following command, the Red section is the ID of the installation patch.

Opatch Rollback-id 13460968

3, after the completion of the implementation of Oracle related services, Windows remember to restore the renamed DLL file.

4, query whether the database can start normally.

5. Use SYSDBA user to execute Roolback script after rollback completed

Sql>@e:\13460968\files\bundle\patch14\ Catcpu_rollback.sql

6, check the database is normal.

Note: Database patch upgrades need to be discontinued and related services, the database must be backed up before the upgrade work.

The above is a small set to introduce the Oracle database Opatch patch operation process, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!