(1)更新psu和jvm补丁
-
PSU顺序
- 在主库上停止传送日志到备库。
-
SQL> select sequence#,applied from v$archived_log where dest_id=2;
SEQUENCE# APPLIED
---------- ---------
9 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
SEQUENCE# APPLIED
---------- ---------
40 YES
41 YES
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
48 YES
49 NO
21 rows selected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 48
Next log sequence to archive 50
Current log sequence 50
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database using current logfile disconnect from session
Media recovery complete.
SQL>
SQL> select sequence#,applied from v$archived_log where dest_id=2;
SEQUENCE# APPLIED
---------- ---------
9 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
SEQUENCE# APPLIED
---------- ---------
40 YES
41 YES
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
21 rows selected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 48
Next log sequence to archive 50
Current log sequence 50
SQL>
SQL> alter system set log_archive_dest_state_2=defer;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 49
Next log sequence to archive 51
Current log sequence 51
SQL> select sequence#,applied from v$archived_log where dest_id=2;
SEQUENCE# APPLIED
---------- ---------
9 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
39 YES
SEQUENCE# APPLIED
---------- ---------
40 YES
41 YES
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
50 NO
22 rows selected.
SQL>
2. 关闭备库,按照README安装补丁集到RDBMS 软件。这包括Patchset/Patchset Update(PSU)/Critical Patch Update
(CPU)。对备库RDBMS本身您将无法也不需要运行脚本(catpatch.sql等)。启动备库到mount状态,不要启动日志应用服
务。
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@pri arch]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:11:42
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pri)(PORT=1521)))
The command completed successfully
[root@pri oracle]# chown oracle:oinstall p*
[oracle@pri ~]$ unzip p6880880_112000_Linux-x86-64.zip
[oracle@pri db_1]$ mv OPatch OPatchbak
[oracle@pri db_1]$ mv /home/oracle/OPatch ./
[oracle@pri ~]$ opatch version
OPatch Version: 11.2.0.3.21
[oracle@pri ~]$ unzip p29141056_112040_Linux-x86-64.zip
[oracle@pri ~]$ cd 29141056/
[oracle@pri 29141056]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.21
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2/db_1/oraInst.loc
OPatch version : 11.2.0.3.21
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2/db_1/cfgtoollogs/opatch/opatch2019-06-20_22-16-29PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 21352635 21948347 22502456 23054359 24006111 24732075 25869727 26609445 26392168 26925576 27338049 27734982 28204707 28729262 29141056
Do you want to proceed? [y|n]
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:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u01/app/oracle/product/11.2/db_1')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying sub-patch '17478514' to OH '/u01/app/oracle/product/11.2/db_1'
Patching component oracle.rdbms, 11.2.0.4.0...
[oracle@pri 29141056]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:20:08
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pri)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=pri)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-JUN-2019 22:20:08
Uptime 0 days 0 hr. 0 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/pri/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=pri)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "pri" has 1 instance(s).
Instance "pri", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@pri 29141056]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:20:37 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 419432344 bytes
Database Buffers 109051904 bytes
Redo Buffers 3723264 bytes
Database mounted.
SQL>
3. 关闭主库,按照README安装Patchset/PSU/CPU到RDBMS软件和RDBMS本身(运行catpatch/catbundle/catcpu
等)。
注意: Oracle11gR2的(11.2.0)的最新补丁集需要被安装到一个新的ORACLE_HOME。所以记得重置您的环境变量并复制
相应的文件(SPFILE,网络文件等..)到新的ORACLE_HOME。详细信息参看数据库升级指南。
[oracle@std ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:21:42
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521)))
The command completed successfully
[oracle@std ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:21:48 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
[oracle@std 29141056]$ opatch apply
Oracle Interim Patch Installer version 11.2.0.3.21
Copyright (c) 2019, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2/db_1/oraInst.loc
OPatch version : 11.2.0.3.21
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2/db_1/cfgtoollogs/opatch/opatch2019-06-20_22-23-53PM_1.log
Verifying environment and performing prerequisite checks...
4. 启动主库,重新启用日志传送到备库。
[oracle@std 29141056]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUN-2019 22:27:56
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/std/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=std)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-JUN-2019 22:27:56
Uptime 0 days 0 hr. 0 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/std/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=std)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "std" has 1 instance(s).
Instance "std", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@std 29141056]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:28:54 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 402655128 bytes
Database Buffers 125829120 bytes
Redo Buffers 3723264 bytes
Database mounted.
Database opened.
SQL>@?/rdbms/admin/catbundle.sql psu apply
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch
Oldest online log sequence 51
Next log sequence to archive 52
Current log sequence 53
SQL>
5. 在备库启动日志应用服务,主库中的RDBMS变更(catpatch/ catbundle/catcpu 脚本)通过重做日志被应用到备库。
[oracle@pri ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 20 22:32:54 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
7 rows selected.
SQL> recover managed standby database using current logfile disconnect from session ;
Media recovery complete.
SQL>
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CLOSING
ARCH CONNECTED
ARCH CONNECTED
ARCH CLOSING
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 APPLYING_LOG
8 rows selected.
注意: 备库上的数据库软件升级后应立即执行步骤5。这是为了确保数据字典(CATPROC)版本与数据库软件的版本匹配。
如果不匹配(例如,您首先升级了备库软件,升级主库前在备库中执行了角色转换(switchover)),你可能会遇到严重的
问题。在Data Guard物理备库环境中不支持不同的补丁级别,详细的信息请参看
Document 785347.1 Mixed Oracle Version support with Data Guard Redo Transport Services
6. 执行检查,以确保补丁已成功安装在主库和备库。
opatch lsinventory -bugs_fixed|grep "MOLECULE"
执行完后我们可以查到PSU更新信息:
set line 150
col ACTION_TIME for a30
col ACTION for a8
col NAMESPACE for a8
col VERSION for a10
col BUNDLE_SERIES for a5
col COMMENTS for a20
select * from dba_registry_history;
$ opatch lsinventory
$opatch lsinventory –bugs_fixed
主
SQL> set line 150
SQL> col ACTION_TIME for a30
SQL> col ACTION for a8
SQL> col NAMESPACE for a8
SQL> col VERSION for a10
SQL> col BUNDLE_SERIES for a5
SQL> col COMMENTS for a20
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 0 PSU Patchset 11.2.0.2.0
19-JUN-19 08.06.28.480376 PM APPLY SERVER 11.2.0.4 0 PSU Patchset 11.2.0.2.0
20-JUN-19 10.31.25.457297 PM APPLY SERVER 11.2.0.4 190416 PSU PSU 11.2.0.4.190416
3 rows selected.
备
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only
2 ;
Database altered.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> set line 150
SQL> col ACTION_TIME for a30
SQL> col ACTION for a8
SQL> col NAMESPACE for a8
SQL> col VERSION for a10
SQL> col BUNDLE_SERIES for a5
SQL> col COMMENTS for a20
SQL> select * from dba_registry_history;
ACTION_TIME ACTION NAMESPAC VERSION ID BUNDL COMMENTS
------------------------------ -------- -------- ---------- ---------- ----- --------------------
24-AUG-13 12.03.45.119862 PM APPLY SERVER 11.2.0.4 0 PSU Patchset 11.2.0.2.0
19-JUN-19 08.06.28.480376 PM APPLY SERVER 11.2.0.4 0 PSU Patchset 11.2.0.2.0
20-JUN-19 10.31.25.457297 PM APPLY SERVER 11.2.0.4 190416 PSU PSU 11.2.0.4.190416
SQL>
JVM
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/pri/pri/trace/pri_mrp0_8690.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1221198
MRP0: Background Media Recovery process shutdown (pri)
Fri Jun 21 00:43:17 2019
到mount下可以恢复成功