ITPub博客

首页 > 数据库 > Oracle > 最小化宕机时间Rolling Upgrade 实战

最小化宕机时间Rolling Upgrade 实战

原创 Oracle 作者:lucyne 时间:2015-08-14 10:48:36 0 删除 编辑


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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: GoldenGate 实战
请登录后发表评论 登录
全部评论
. . 一个 DBA. . .

注册时间:2012-01-06

  • 博文量
    84
  • 访问量
    742556