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博客 ” ，链接：http://blog.itpub.net/26390465/viewspace-1771242/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26390465/viewspace-1771242/
1373
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
