1．环境：
Database Role DB_UNIQUE_NAME
Version ip
Primary Database vwdb
11.2.0.3.0 2.144
Physical Standby svwdb
11.2.0.3.0 2.143
公司一般使用DGMGRL管理DG，方便OEM监控等，但使用Rolling Upgrade需要禁用DGMGRL Broker功能。
2.主库打开 FLASHBACK功能，回退使用
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed
Size
2237008 bytes
Variable
Size
922750384 bytes
Database Buffers 4093640704
bytes
Redo
Buffers
8757248 bytes
Database mounted.
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
同时创建保证还原点以保证升级失败时可以回退
SQL> create restore point pre_rolling_upgrd guarantee flashback database;
Restore point created.
3.禁止DGMGRL Broker
DGMGRL> show configuration
Configuration - vwdb_dg
Protection Mode: MaxPerformance
Databases:
vwdb - Primary database
svwdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> disable configuration
Disabled.
主备库
SQL> alter system set
dg_broker_start=false;
System altered.
4.将物理库转化为逻辑库：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed
Size
2237008 bytes
Variable
Size
922750384 bytes
Database Buffers 4093640704
bytes
Redo Buffers
8757248 bytes
Database mounted.
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;--主库执行
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
--------------------------------
LOGICAL STANDBY
4.1 禁止在逻辑备库端删除外籍日志
SQL> @?/rdbms/admin/dbmslsby.sql
SQL> EXECUTE DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'FALSE');
PL/SQL procedure successfully completed.
4.2执行以下语句可以将逻辑备库不支持的而又在主库上运行过的事务记录到DBA_LOGSTDBY_EVENTS中
SQL> EXECUTE
DBMS_LOGSTDBY.APPLY_SET('MAX_EVENTS_RECORDED',DBMS_LOGSTDBY.MAX_EVENTS);
PL/SQL procedure successfully completed.
4.3启动在逻辑备库上的SQL APPLY:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
5.升级备库
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
修改oracle用户的bash_profile
ORACLE_HOME路径
1.拷贝参数文件到新版本的路径下
$ cp /U01/app/oracle/product/11.2.3/db_1/dbs/spfilevwdb.ora
/U01/app/oracle/product/11.2.4/db_2/dbs/
$ cp
/U01/app/oracle/product/11.2.3/db_1/dbs/orapwvwdb/U01/app/oracle/product/11.2.4/db_2/dbs/
$ cp /U01/app/oracle/product/11.2.3/db_1/network/admin/listener.ora
/U01/app/oracle/product/11.2.4/db_2/network/admin/
$ cp /U01/app/oracle/product/11.2.3/db_1/network/admin/tnsnames.ora
/U01/app/oracle/product/11.2.4/db_2/network/admin/
$ ll /U01/app/oracle/product/11.2.4/db_2/network/admin/
total 16
-rw-r--r-- 1 oracle oinstall 926 Oct 13 13:46
listener.ora --修改文件内ORACLE_HOME路径
drwxr-xr-x 2 oracle oinstall 4096 Aug 19 11:15 samples
-rw-r--r-- 1 oracle oinstall 381 Dec 17 2012 shrept.lst
-rw-r----- 1 oracle oinstall 1492 Oct 13 13:46 tnsnames.ora
SQL> startup upgrade;
SQL>@?/rdbms/admin/catupgrd
SQL>@?/rdbms/admin/utlu112s
SQL>@?/rdbms/admin/catuppst
SQL>@?/rdbms/admin/utlrp
SQL> startup
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed
Size
2261888 bytes
Variable Size
1073745024 bytes
Database Buffers 3942645760
bytes
Redo
Buffers
8732672 bytes
Database mounted.
Database opened.
查看各组件状态
SQL> col comp_name for a30;
SQL> col namespace for a20;
SQL> col version for a15;
SQL> col status for a10;
SQL> SELECT COMP_NAME,namespace,VERSION, STATUS FROM SYS.DBA_REGISTRY;
COMP_NAME
NAMESPACE
VERSION STATUS
------------------------------ -------------------- --------------- ----------
OWB
SERVER
11.2.0.3.0 VALID
Oracle Application Express
SERVER
3.2.1.00.12 VALID
Oracle Enterprise Manager
SERVER
11.2.0.4.0 VALID
OLAP
Catalog
SERVER
11.2.0.3.0 OPTION OFF
Spatial
SERVER
11.2.0.4.0 VALID
Oracle
Multimedia
SERVER
11.2.0.4.0 VALID
Oracle XML
Database
SERVER
11.2.0.4.0 VALID
Oracle Text
SERVER
11.2.0.4.0 VALID
Oracle Expression Filter
SERVER
11.2.0.4.0 VALID
Oracle Rules
Manager
SERVER
11.2.0.4.0 VALID
Oracle Workspace Manager
SERVER
11.2.0.4.0 VALID
COMP_NAME
NAMESPACE
VERSION STATUS
------------------------------ -------------------- --------------- ----------
Oracle Database Catalog Views
SERVER
11.2.0.4.0 VALID
Oracle Database Packages and T
SERVER
11.2.0.4.0 VALID
ypes
JServer JAVA Virtual Machine
SERVER
11.2.0.4.0 VALID
Oracle
XDK
SERVER
11.2.0.4.0 VALID
Oracle Database Java Packages
SERVER
11.2.0.4.0 VALID
OLAP Analytic Workspace
SERVER
11.2.0.3.0 OPTION OFF
Oracle OLAP
API
SERVER
11.2.0.3.0 OPTION OFF
启用日志应用
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
SYSDATE
APPLIED_TIME
------------------- -------------------
2014-10-17 11:44:51 2014-10-17 11:18:21
alert.log日志
LOGMINER: Begin mining
logfile for session 1 thread 1 sequence 41,
/U01/app/oracle/fast_recovery_area/SVWDB/foreign_archivelog/vwdb/2014_10_17/o1_mf_1_41_b414612h_.arc
LOGMINER: End mining logfile for session 1 thread 1 sequence 41,
/U01/app/oracle/fast_recovery_area/SVWDB/foreign_archivelog/vwdb/2014_10_17/o1_mf_1_41_b414612h_.arc
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 42,
/U01/app/oracle/fast_recovery_area/SVWDB/foreign_archivelog/vwdb/2014_10_17/o1_mf_1_42_b4144ftj_.arc
Fri Oct 17 11:45:06 2014
LOGSTDBY Apply process AS01 started with server id=1 pid=37 OS id=30733
Fri Oct 17 11:45:06 2014
LOGSTDBY Apply process AS05 started with server id=5 pid=41 OS id=30741
Fri Oct 17 11:45:06 2014
LOGSTDBY Apply process AS03 started with server id=3 pid=39 OS id=30737
Fri Oct 17 11:45:06 2014
LOGSTDBY Apply process AS02 started with server id=2 pid=38 OS id=30735
Fri Oct 17 11:45:06 2014
LOGSTDBY Analyzer process AS00 started with server id=0 pid=36 OS id=30731
Fri Oct 17 11:45:06 2014
LOGSTDBY Apply process AS04 started with server id=4 pid=40 OS id=30739
LOGMINER: End mining logfile for session 1 thread 1 sequence 42,
/U01/app/oracle/fast_recovery_area/SVWDB/foreign_archivelog/vwdb/2014_10_17/o1_mf_1_42_b4144ftj_.arc
SQL> SELECT SYSDATE,
APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
SYSDATE
APPLIED_TIME
------------------- -------------------
2014-10-17 11:44:51 2014-10-17 11:18:21
SQL> /
SYSDATE
APPLIED_TIME
------------------- -------------------
2014-10-17 11:45:31 2014-10-17 11:48:45
7.切换主备库
主库：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
Database altered.
备库：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
----------------------------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
此时备库可以提供业务了。
4.将原主库(PROD)闪回到之前创建的还原点上
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed
Size
2237008 bytes
Variable
Size
922750384 bytes
Database Buffers 4093640704
bytes
Redo
Buffers
8757248 bytes
Database mounted.
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
PRE_ROLLING_UPGRD
SQL> flashback database to restore point pre_rolling_upgrd;
Flashback complete.
8.升级原主库：
关闭数据库
SQL> shut immediate
修改环境变量，加载最新的oracle_home路径
SQL> startup mount;
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed
Size
2261888 bytes
Variable Size
1006636160 bytes
Database Buffers 4009754624 bytes
Redo
Buffers
8732672 bytes
Database mounted.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 5027385344 bytes
Fixed
Size
2261888 bytes
Variable Size
1006636160 bytes
Database Buffers 4009754624
bytes
Redo
Buffers
8732672 bytes
Database mounted.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> alter database
recover managed standby database using current logfile disconnect from session;
Database altered.
alert.log 日志：
Archived Log entry 132
added for thread 1 sequence 59 rlc 861189104 ID 0x51a35658 dest 2:
Archived Log entry 133 added for thread 1 sequence 58 rlc 861189104 ID
0x51a35658 dest 2:
RFS[1]: Opened log for thread 1 sequence 61 dbid 1369681924 branch 861189104
Archived Log entry 134 added for thread 1 sequence 61 rlc 861189104 ID
0x51a35658 dest 2:
Fri Oct 17 11:58:29 2014
Archived Log entry 135 added for thread 1 sequence 60 ID 0x51a35658 dest 1:
Media Recovery Log
/U01/app/oracle/fast_recovery_area/VWDB/archivelog/2014_10_17/o1_mf_1_10_b414yh8q_.arc
Fri Oct 17 11:58:34 2014
Media Recovery Log
/U01/app/oracle/fast_recovery_area/VWDB/archivelog/2014_10_17/o1_mf_1_11_b414yhch_.arc
Media Recovery Log
/U01/app/oracle/fast_recovery_area/VWDB/archivelog/2014_10_17/o1_mf_1_12_b414yjn9_.arc
等日志全部应用完成，就可以再切换主备库了。
主备库应该一致并应用成功
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
62
9.在主库与物理备库间实施切换
新主库switchover回物理备库状态
SQL> alter database commit to switchover to physical standby;
Database altered.
老主库切换为Primary Database
SQL> alter database commit to switchover to primary;
Database altered.
SQL> alter database recover managed standby database disconnect from
session;
Database altered.
10.回退
SQL> flashback
database to restore point pre_rolling_upgrd;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26390465/viewspace-1771242/ ，如需转载，请注明出处，否则将追究法律责任。
