1、 Preparation before upgrading
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;
There is no record. Generally, the patch is not installed. You can also use the opatch lsinventory opatch command to query the current patch installation after the environment is set.
3. Query the number of invalid objects in the database, which is recorded as N1
SQL> SELECT sum（OBJECT_NAME）N1 FROM DBA_OBJECTS WHERE STATUS= 'INVALID';
If there are invalid objects, it is better to compile them first
After compiling, re execute the following statement to view the current actual effect object, which is marked as N2
SQL> SELECT sum（OBJECT_NAME）N2 FROM DBA_OBJECTS WHERE STATUS= 'INVALID';
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS= 'INVALID';
–The results will be stored in Excel or table for checking after patching.
4. Patch type
Oracle database patches are generally divided into two types of CPU and PSU patches.
The full name of Oracle PSU is patch set update. The patch package that Oracle releases once a quarter for its products contains bug fixes. Oracle selects patches that have been downloaded by users and have been verified to have low risk and put them into the PSU every quarter. Each PSU contains not only bug fixes, but also the latest CPU. The PSU is usually released with the CPU. PSUs are usually incremental. Most PSUs can be installed directly. However, for some PSUs, you must install the previous version of the PSU before continuing the installation. You should carefully read the readme documentation of each PSU.
The full name of Oracle CPU is critical patch update. Oracle releases security patches for its products once a quarter, usually to repair the security risks in the products. The CPU is cumulative, that is, the latest CPU patch already contains previous CPU patches, so just install the latest CPU patch.
We need to install the CPU patch when we rectify the security vulnerability.
5. Query patch information
Login Oracle official website security topic http://www.oracle.com/technetwork/topics/security Click learn more to find critical path update advisory
After opening, you can see the list of patches released by Oracle, which contains the patches released by Oracle products.
6. Patch download
For example, the current version of Oracle is 10.2.0.5, and the last update patch of this version is July 2013. Click the red link above to see the database patch of 10.2.0.5.
Note: download the account with metalink and download the corresponding version of the patch according to the type of server. If there is no metalink account of Oracle, the patch cannot be downloaded.
7. Database backup work
Before the patch upgrade, do a good job of database backup, and use RMAN or export data file for database full backup. If possible, the operating system should also be backed up once.
After all the above operations, we can install the patch.
2、 Patch installation operation
1. Read patch installation requirements
The compressed package after each patch download will contain a copy README.html Read carefully before installation. Generally, it will indicate the database version and operating system version that the patch is applicable to, what bugs have been fixed, etc., and there will be relevant prompt information.
2. Environment settings
2.1 under Windows Environment
Enter computer properties → advanced system settings → system properties → environment variables
Edit path and add% Oracle to the end of path_ Home% \ \ opatch or set path =% path%;% Oracle under CMD_ HOME%\OPatch;
2.2 environment variable setting under Linux
Use Oracle to install users and set oracle_ Export path = $Oracle after home_ HOME/OPatch:$PATH。
2.3 opatch command test
After setting the environment variable successfully, test whether the opatch command is available. Under the opatch – help terminal, you can see the relevant information, indicating that the environment variable is set successfully.
3. Query patch installation information
View the current patch installation under the operating system opatch lsinventory – patch
4. Stop monitoring and Oracle services
4.1. Shut down the service under windows
Close the Oracle related service process in the service, and stop the monitoring and Oracle service. Close the PLSQL client and the sqlplus interface.
4.2 shut down the service under Linux
Shut down all instances and shut down the monitoring and Oracle related processes. If you have installed GI and other applications using Oracle, exit the sqlplus interface.
5. Install the patch
Execute opatch apply in the patch directory. For example, if my patch is decompressed in E: 13460968, CD it to the directory first, and then execute opatch apply
Note: sometimes all Oracle processes will be shut down under windows, but after the command is executed, there will still be D: ﹣ Oracle ﹣ product ﹣ 10.2.0 ﹣ dB_ 1\bin\ oci.dll Occupied error.
In Linux, when the process is occupied, the sqlplus process is not closed. You can use fuser to query the user occupied. However, when the database is forced to kill, an error may be reported when the database is started.
Go to the directory to find the file name, such as change to oci.dllbak If the patch rollback operation has been performed, remember to change it back and execute opatch apply again.
Select y and wait for the patch to be installed.
6. Start the database
Windows starts Oracle service and monitoring in the service. Log in the system with SYSDBA.
Start Oracle instance and monitor under Linux. Log in the system with SYSDBA.
7. Execute the script to update the patch information to the database
Execute the script under the directory in the patch package and wait for the script to run. Linux can find the patch directory and execute it.
Note: the location of different patch sets may be different, but the script is generally available.
8. Compile the invalid object and execute the following statement
Note: most invalid object errors can be ignored, and the specific types can be checked with the following command.
SQL> SELECT sum (OBJECT_NAME)N3 FROM DBA_OBJECTS WHERE STATUS='INVALID';
Check the N2 comparison of N3 and section 3 of the first page to see whether new failure objects are added. If there is a specific failure object, use the list obtained in Section 3 of Chapter 1 for comparison. If it can not be handled, consult the database administrator.
9. Query patch update information
select * from dba_registry_history;
You can view the information about the patch you just installed. (here, I installed and uninstalled once. Finally, the information about the successful installation was recorded in the database). Execute opatch lsinventory opatch from the command line
The whole installation process is finished and the database is normal.
3、 Rollback operation
Generally, when the cause of the problem is uncertain, the DBA is consulted to check and determine the cause of the problem. If the cause cannot be verified, it can be rolled back. The specific rollback operation command is as follows.
1. If the database is abnormal or bug occurs after the patch is applied, opatch can be used to roll back the patch update operation.
2. Close the Oracle related services, set the environment variables according to Section 2 of section 2, and execute the following command. The red part is the ID of the patch to be installed.
opatch rollback -id 13460968
3. After the execution, start the Oracle related services. Windows remembers to restore the renamed DLL file.
4. Query whether the database can be started normally.
5. After the rollback, execute the rollback script using the SYSDBA user
6. Check whether the database is normal.
Note: the database patch upgrade needs to stop application and related services. Before upgrading, the database must be backed up.
The above is the Oracle database opatch patch operation process introduced by Xiaobian. I hope it can help you. If you have any questions, please leave me a message, and the editor will reply to you in time. Thank you very much for your support to the developeppaer website!