签到成功

知道了

CNDBA社区CNDBA社区

Oracle 12c 新特性 --- 使用DBMS_ROLLING 实现物理备库转换为逻辑备库

2017-08-28 23:18 2840 0 原创 Oracle 12c Data Guard
作者： leo
本文链接：http://www.cndba.cn/leo1990/article/2187
概念 
DBMS_ROLLING.START_PLAN程序是滚动升级的正式开始。START_PLAN过程的目标是配置临时的逻辑备用数据库和任何指定保护它的物理备用数据库。在调用时，START_PLAN程序执行升级计划中的所有指令，并使用START_PLAN的PHASE value 。执行的指令的类型包括:
        *Backing up the control file for each database to a trace file
        *Creating flashback database guaranteed restore points
        *Building a LogMiner dictionary at the primary database
        *Recovering the designated physical standby into a transient logical standby database
        *Loading the LogMiner dictionary into the logical standby database
        *Configuring LGS databases with the transient logical standby database

实验步骤:
1 环境准备
1.1 开启主备库闪回 
[[email protected] admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 23 15:37:47 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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 1560281088 bytes
Fixed Size		    2924784 bytes
Variable Size		  939527952 bytes
Database Buffers	  603979776 bytes
Redo Buffers		   13848576 bytes
Database mounted.
SQL> alter database flashback on;
select flashback_on from V$database;
Database altered.

SQL> select flashback_on from V$database;

FLASHBACK_ON
------------------
YES

SQL> alter database open;

Database altered.

SQL> show parameter recovery

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size	     big integer 4560M
recovery_parallelism		     integer	 0
1.2 修改主备库参数 

--修改主库参数
--另外之前的LOG_ARCHIVE_DEST_1是valid for (ALL_LOGFILES,ALL_ROLES)， 需要修改为(ONLINE_LOGFILES,ALL_ROLES)
SQL> alter system set log_archive_dest_1='location=/u01/archive valid_for=(ONLINE_LOGFILES,all_roles) db_unique_name=pdbcndba_p' scope=both;

System altered.
--注意:LOG_ARCHIVE_DEST_3只在主数据库转换成逻辑备用角色时生效
SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pdbcndba_p' scope=both;

System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; 

System altered.

--修改备库参数
--另外之前的LOG_ARCHIVE_DEST_1是valid for (ALL_LOGFILES,ALL_ROLES)， 需要修改为(ONLINE_LOGFILES,ALL_ROLES)
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdbcndba_s' scope=both;  

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/archivelog_logical VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=pdbcndba_s' scope=both; 

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;  

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; 

System altered.
1.3 DG 要处于maximize performance 或者 maximize availability 模式 
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PRIMARY	      MAXIMUM PERFORMANCE  READ WRITE
1.4 执行存储过程START_PLAN 之前 备库数据库处于mount 状态

2 执行dbms_rolling 包中相关的存储过程
2.1 Init Plan  
--将备库启动到mount 状态
[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 25 12:06:59 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown immediate                     
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1560281088 bytes
Fixed Size		    2924784 bytes
Variable Size		  939527952 bytes
Database Buffers	  603979776 bytes
Redo Buffers		   13848576 bytes
Database mounted.

--主库执行dbms_rolling.init_plan 存储过程
[[email protected] dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 25 10:33:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  exec dbms_rolling.init_plan('pdbcndba_s');

PL/SQL procedure successfully completed.

SQL>  select revision, status, phase from dba_rolling_status;   

  REVISION       STATUS	    PHASE
---------- ------------ --------------
	 0            READY	  BUILD PENDING
2.2 BUILD_PLAN 
SQL> exec dbms_rolling.build_plan; 

PL/SQL procedure successfully completed.

SQL> select revision, status, phase from dba_rolling_status; 

  REVISION STATUS	    PHASE
---------- ------------ --------------
	     1 READY	    START PENDING

SQL> col DBUN for a20
SQL> col ROLE for a20
SQL> col open_mode for a20
SQL> col engine_status for a20
SQL> col update_progress for a20
SQL> select dbun,role,open_mode,engine_status,update_progress from dba_rolling_databases; 

DBUN		         ROLE		          OPEN_MODE	           ENGINE_STATUS	    UPDATE_PROGRESS
-------------------- -------------------- -------------------- -------------------- --------------------
pdbcndba_p	         PRIMARY		      OPEN READ WRITE      NOT APPLICABLE	    NOT STARTED
pdbcndba_s	         PHYSICAL		      MOUNTED	           STOPPED		        NOT STARTED
2.3 START PLAN 
SQL> exec dbms_rolling.start_plan();

PL/SQL procedure successfully completed.

---------------Alert log of pdbcndba_p（主库）---------------

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
ARC0: RFS network connection re-established at host 'pdbcndba_s'
ARC0: RFS destination opened for reconnect at host 'pdbcndba_s'
Fri Aug 25 17:37:08 2017
Drop guaranteed restore point PRE_UPGRADE
Guaranteed restore point PRE_UPGRADE dropped
Created guaranteed restore point PRE_UPGRADE
Fri Aug 25 17:43:04 2017
RTS(6538): executing rolling upgrade instruction 1 from plan revision 1
Fri Aug 25 17:43:04 2017
RTS(6538): completed instruction 10 from plan revision 1
Fri Aug 25 17:43:04 2017
RTS(6538): executing rolling upgrade instruction 16 from plan revision 1
Drop guaranteed restore point DBMSRU_INITIAL
Guaranteed restore point DBMSRU_INITIAL dropped
Fri Aug 25 17:43:04 2017
RTS(6538): completed instruction 16 from plan revision 1
Fri Aug 25 17:43:04 2017
RTS(6538): executing rolling upgrade instruction 17 from plan revision 1
Created guaranteed restore point DBMSRU_INITIAL
Fri Aug 25 17:43:04 2017
RTS(6538): completed instruction 17 from plan revision 1
Fri Aug 25 17:43:10 2017
RTS(6538): executing rolling upgrade instruction 20 from plan revision 1
Fri Aug 25 17:43:10 2017
RTS(6538): completed instruction 20 from plan revision 1
Fri Aug 25 17:43:10 2017
RTS(6538): executing rolling upgrade instruction 21 from plan revision 1
alter database backup controlfile to 'rolling_change_backup.f' reuse
Completed: alter database backup controlfile to 'rolling_change_backup.f' reuse
Fri Aug 25 17:43:13 2017
RTS(6538): completed instruction 21 from plan revision 1
Fri Aug 25 17:43:17 2017
RTS(6538): executing rolling upgrade instruction 24 from plan revision 1
Fri Aug 25 17:43:17 2017
RTS(6538): completed instruction 24 from plan revision 1
Fri Aug 25 17:43:18 2017
RTS(6538): executing rolling upgrade instruction 27 from plan revision 1
alter database add supplemental log data (primary key, unique index) columns
Fri Aug 25 17:43:18 2017
SUPLOG: Previous supplemental logging attributes at scn = 4561914
SUPLOG:  minimal = OFF, primary key = OFF
SUPLOG:  unique = OFF, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Fri Aug 25 17:43:18 2017
SUPLOG: New supplemental logging attributes at scn = 4561914
SUPLOG:  minimal = ON, primary key = ON
SUPLOG:  unique = ON, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Completed: alter database add supplemental log data (primary key, unique index) columns
alter database add supplemental log data for procedural replication
Fri Aug 25 17:43:18 2017
SUPLOG: Previous supplemental logging attributes at scn = 4561922
SUPLOG:  minimal = ON, primary key = ON
SUPLOG:  unique = ON, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = OFF
Fri Aug 25 17:43:18 2017
SUPLOG: New supplemental logging attributes at scn = 4561922
SUPLOG:  minimal = ON, primary key = ON
SUPLOG:  unique = ON, foreign key = OFF, all column = OFF
SUPLOG:  procedural replication = ON
Completed: alter database add supplemental log data for procedural replication
Fri Aug 25 17:43:19 2017
Logminer Bld: Build started
Fri Aug 25 17:43:19 2017
ALTER SYSTEM SWITCH ALL LOGFILE start (cndba)
Fri Aug 25 17:43:19 2017
LGWR: Failed to archive log 2 thread 1 sequence 14 (3113)
Fri Aug 25 17:43:19 2017
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
Fri Aug 25 17:43:19 2017
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 3 seq# 15 mem# 0: /u01/app/oracle/oradata/cndba/redo03.log
Fri Aug 25 17:43:19 2017
ALTER SYSTEM SWITCH ALL LOGFILE complete (cndba)
Fri Aug 25 17:43:19 2017
Archived Log entry 404 added for thread 1 sequence 14 ID 0xcf6712c dest 1:
Fri Aug 25 17:43:19 2017
Fri Aug 25 17:43:19 2017

---------------Alert log of pdbcndba_s (备库)---------------

RVWR started with pid=29, OS id=6264 
Fri Aug 25 13:58:55 2017
Created guaranteed restore point PRE_UPGRADE
Fri Aug 25 14:04:56 2017
RTS(6412): completed instruction 14 from plan revision 1
Fri Aug 25 14:04:56 2017
RTS(6412): executing rolling upgrade instruction 15 from plan revision 1
Created guaranteed restore point DBMSRU_INITIAL
Fri Aug 25 14:04:56 2017
RTS(6412): completed instruction 15 from plan revision 1
Fri Aug 25 14:04:56 2017
RTS(6412): executing rolling upgrade instruction 18 from plan revision 1
alter database recover managed standby database disconnect
WARNING: There are no standby redo logs.
Standby redo logs should be configured for real time apply. Real time apply will be ignored.
Fri Aug 25 14:04:56 2017
Attempt to start background Managed Standby Recovery process (cndba)
Starting background process MRP0
Fri Aug 25 14:04:56 2017
MRP0 started with pid=32, OS id=6414 
Fri Aug 25 14:04:56 2017
MRP0: Background Managed Standby Recovery process started (cndba)
...
Completed: alter database recover managed standby database disconnect
Fri Aug 25 14:05:02 2017
RTS(6412): completed instruction 22 from plan revision 1
Fri Aug 25 14:05:05 2017
RTS(6412): executing rolling upgrade instruction 23 from plan revision 1
alter database backup controlfile to 'rolling_change_backup.f' reuse
Completed: alter database backup controlfile to 'rolling_change_backup.f' reuse
...
RTS(6412): executing rolling upgrade instruction 26 from plan revision 1
alter database recover managed standby database cancel
Fri Aug 25 14:05:09 2017
MRP0: Background Media Recovery cancelled with status 16037
Fri Aug 25 14:05:09 2017
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_s/cndba/trace/cndba_mrp0_6414.trc:
ORA-16037: user requested cancel of managed recovery operation
Recovery interrupted!
Fri Aug 25 14:05:09 2017
Errors in file /u01/app/oracle/diag/rdbms/pdbcndba_s/cndba/trace/cndba_mrp0_6414.trc:
ORA-16037: user requested cancel of managed recovery operation
Fri Aug 25 14:05:09 2017
MRP0: Background Media Recovery process shutdown (cndba)
Fri Aug 25 14:05:10 2017
Managed Standby Recovery Canceled (cndba)
Completed: alter database recover managed standby database cancel
Fri Aug 25 14:05:10 2017
RTS(6412): completed instruction 26 from plan revision 1
Fri Aug 25 14:05:56 2017
RFS[1]: Assigned to RFS process (PID:6426)
RFS[1]: Opened log for thread 1 sequence 14 dbid 215559030 branch 952960462
Fri Aug 25 14:05:56 2017
RFS[2]: Assigned to RFS process (PID:6428)
RFS[2]: Opened log for thread 1 sequence 15 dbid 215559030 branch 952960462
Fri Aug 25 14:05:56 2017
Archived Log entry 27 added for thread 1 sequence 14 rlc 952960462 ID 0xcf6712c dest 2:
Fri Aug 25 14:05:56 2017
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process (PID:6430)
RFS[3]: No standby redo logfiles created
RFS[3]: Opened log for thread 1 sequence 17 dbid 215559030 branch 952960462
Fri Aug 25 14:05:59 2017
Archived Log entry 28 added for thread 1 sequence 15 rlc 952960462 ID 0xcf6712c dest 2:
RFS[1]: Opened log for thread 1 sequence 16 dbid 215559030 branch 952960462
Fri Aug 25 14:06:00 2017
Archived Log entry 29 added for thread 1 sequence 16 rlc 952960462 ID 0xcf6712c dest 2:
Fri Aug 25 14:06:02 2017
Archived Log entry 30 added for thread 1 sequence 17 rlc 952960462 ID 0xcf6712c dest 2:
RFS[3]: No standby redo logfiles created
RFS[3]: Opened log for thread 1 sequence 18 dbid 215559030 branch 952960462
Fri Aug 25 14:06:03 2017
RTS(6412): executing rolling upgrade instruction 28 from plan revision 1
alter database recover to logical standby keep identity
Fri Aug 25 14:06:03 2017
Media Recovery Start: Managed Standby Recovery (cndba)
Fri Aug 25 14:06:03 2017
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Fri Aug 25 14:06:04 2017
Media Recovery Log /u01/archivelog_logical/1_14_952960462.dbf
Fri Aug 25 14:06:05 2017
Media Recovery Log /u01/archivelog_logical/1_15_952960462.dbf
Resize operation completed for file# 1, old size 819200K, new size 829440K
Resize operation completed for file# 1, old size 829440K, new size 839680K
Fri Aug 25 14:06:16 2017
Resize operation completed for file# 8, old size 276480K, new size 286720K
Fri Aug 25 14:06:17 2017
Media Recovery Log /u01/archivelog_logical/1_16_952960462.dbf
Fri Aug 25 14:06:22 2017
Media Recovery Log /u01/archivelog_logical/1_17_952960462.dbf
Fri Aug 25 14:06:22 2017
Incomplete Recovery applied until change 4563713 time 08/25/2017 17:44:09
Fri Aug 25 14:06:22 2017
Media Recovery Complete (cndba)
Killing 4 processes (PIDS:6424,6426,6428,6430) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 6412
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 4563713 time 08/25/2017 17:44:09
Resetting resetlogs activation ID 217477420 (0xcf6712c)
Online log /u01/app/oracle/oradata/cndba/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/cndba/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/cndba/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 4563711
Fri Aug 25 14:06:25 2017
Setting recovery target incarnation to 6
Fri Aug 25 14:06:25 2017
RECOVER TO LOGICAL STANDBY: Complete - Database mounted as logical standby
Completed: alter database recover to logical standby keep identity
Fri Aug 25 14:06:25 2017
RTS(6412): completed instruction 28 from plan revision 1
Fri Aug 25 14:06:25 2017
RTS(6412): executing rolling upgrade instruction 29 from plan revision 1
alter database open
Ping without log force is disabled

SQL>  select revision, status, phase from dba_rolling_status; 

  REVISION STATUS	    PHASE
---------- ------------ --------------
	 1     READY	SWITCH PENDING

SQL> col dbun for a20
SQL>col role for a20
SQL>col engine_status for a20
SQL>col update_progress for a20 
SQL> select dbun, role, engine_status,update_progress from dba_rolling_databases; 

DBUN		         ROLE		          ENGINE_STATUS        UPDATE_PROGRESS
-------------------- -------------------- -------------------- --------------------
pdbcndba_p	         PRIMARY		      NOT APPLICABLE       NOT STARTED
pdbcndba_s	         LOGICAL		      RUNNING	           NOT STARTED

--主库查询
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  PRIMARY	      MAXIMUM PERFORMANCE  READ WRITE

--备库查询，可以看到物理备库转换成逻辑备库
SQL> select protection_mode,database_role,protection_level,open_mode from v$database;

PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL	   OPEN_MODE
-------------------- ---------------- -------------------- --------------------
MAXIMUM PERFORMANCE  LOGICAL STANDBY  MAXIMUM PERFORMANCE  READ WRITE
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187 http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187
http://www.cndba.cn/leo1990/article/2187

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
leo

leo

关注

坚持你的坚持

  • 202
    原创
  • 0
    翻译
  • 39
    转载
  • 16
    评论
  • 访问：765468次
  • 积分：1270
  • 等级：核心会员
  • 排名：第8名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ