oracle 11g物理data guard转逻辑data guard

测试环境不管是数据真实性、环境配置和正式环境都有所差异,这时必须用线上的数据进行测试。将物理DG临时转换成snapshot DG进行测试,测试完成之后再转换成物理DG,这个方法是比较实用的。

下面实际操作一下:

[oracle@qht131 admin]$ dgmgrl /


DGMGRL> show configuration;

Configuration - dg_test11

  Protection Mode: MaxPerformance
  Databases:
    db131 - Primary database
    db132 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

将备库转换成snapshot standy:

DGMGRL> convert database db132 to snapshot standby;
Converting database "db132" to a Snapshot Standby database, please wait...
Database "db132" converted successfully


DB132的alert日志如下:

Fri Dec 13 15:01:56 2019
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Fri Dec 13 15:01:56 2019
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/db132/orcl/trace/orcl_mrp0_18882.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1473614
MRP0: Background Media Recovery process shutdown (orcl)
Managed Standby Recovery Canceled (orcl)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'
Active process 22356 user 'oracle' program 'oracle@qht132 (TNS V1-V3)'

CLOSE: all sessions shutdown successfully.
Fri Dec 13 15:04:10 2019
SMON: disabling cache recovery
Fri Dec 13 15:04:36 2019
alter database convert to snapshot standby
Starting background process RVWR
Fri Dec 13 15:04:37 2019
RVWR started with pid=18, OS id=22542
Fri Dec 13 15:04:52 2019
Allocated 3981120 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_12/13/2019 15:04:36
krsv_proc_kill: Killing 2 processes (all RFS)
Fri Dec 13 15:04:53 2019
idle dispatcher 'D000' terminated, pid = (17, 1)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1473614
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 1554639171 (0x5ca9e943)
Online log /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_1_gz145kfy_.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_2_gz145l2o_.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_3_gz145m22_.log: Thread 1 Group 3 was previously cleared
Fri Dec 13 15:05:02 2019
Standby became primary SCN: 1473612
Fri Dec 13 15:05:02 2019
Setting recovery target incarnation to 3
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby

Completed: alter database convert to snapshot standby
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
Fri Dec 13 15:05:07 2019
Assigning activation ID 1554656041 (0x5caa2b29)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_1_gz145kfy_.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Dec 13 15:05:09 2019
SMON: enabling cache recovery
Fri Dec 13 15:05:24 2019
[18879] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:255561044 end:255576134 diff:15090 (150 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Fri Dec 13 15:05:26 2019
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Starting background process SMCO
Fri Dec 13 15:05:38 2019
SMCO started with pid=26, OS id=22550
Fri Dec 13 15:05:41 2019
No Resource Manager plan active
Fri Dec 13 15:05:50 2019
NSA2 started with pid=32, OS id=22556
Fri Dec 13 15:05:54 2019
Thread 1 advanced to log sequence 2 (LGWR switch)
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/orcl/flash_recovery_area/DB132/onlinelog/o1_mf_2_gz145l2o_.log
Fri Dec 13 15:06:15 2019
Starting background process QMNC
Fri Dec 13 15:06:15 2019
QMNC started with pid=33, OS id=22560
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Fri Dec 13 15:06:48 2019
ARC0: Becoming the 'no SRL' ARCH
Fri Dec 13 15:06:48 2019
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 80 added for thread 1 sequence 1 ID 0x5caa2b29 dest 1:
Fri Dec 13 15:06:52 2019
ARC1: Becoming the 'no SRL' ARCH
Fri Dec 13 15:06:53 2019
Completed: ALTER DATABASE OPEN
Fri Dec 13 15:07:05 2019
RFS[4]: Assigned to RFS process 22568
RFS[4]: Selected log 5 for thread 1 sequence 66 dbid 1509177297 branch 981396562
RFS[4]: Selected log 6 for thread 1 sequence 67 dbid 1509177297 branch 981396562
Fri Dec 13 15:07:21 2019
RFS[5]: Assigned to RFS process 22572
RFS[5]: Selected log 4 for thread 1 sequence 65 dbid 1509177297 branch 981396562
Fri Dec 13 15:07:42 2019
Starting background process CJQ0
Fri Dec 13 15:07:43 2019
CJQ0 started with pid=41, OS id=22588

切换成功后,看一下备库当前的状态:

  闪回区中有闪回数据库的日志信息:

[oracle@qht132 onlinelog]$ ls -lth
total 351M
-rw-r----- 1 oracle oinstall 51M Dec 13 15:16 o1_mf_6_gz145p0r_.log
-rw-r----- 1 oracle oinstall 51M Dec 13 15:16 o1_mf_2_gz145l2o_.log
-rw-r----- 1 oracle oinstall 51M Dec 13 15:07 o1_mf_4_gz145mrx_.log
-rw-r----- 1 oracle oinstall 51M Dec 13 15:07 o1_mf_5_gz145ngw_.log
-rw-r----- 1 oracle oinstall 51M Dec 13 15:05 o1_mf_1_gz145kfy_.log
-rw-r----- 1 oracle oinstall 51M Dec 13 15:05 o1_mf_3_gz145m22_.log
-rw-r----- 1 oracle oinstall 51M Dec 12 15:01 o1_mf_7_gz145pmt_.log

  查看备库归档日志,后面的归档只接收了,没有被应用。

SQL> SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
         1 ARCH    13-DEC-19 13-DEC-19 NO
         8 RFS     11-DEC-19 11-DEC-19 YES
         9 RFS     11-DEC-19 11-DEC-19 YES
        10 RFS     11-DEC-19 11-DEC-19 YES
        11 RFS     11-DEC-19 11-DEC-19 YES
        12 RFS     11-DEC-19 11-DEC-19 YES
        13 RFS     11-DEC-19 11-DEC-19 YES
        14 RFS     11-DEC-19 11-DEC-19 YES
        15 RFS     11-DEC-19 11-DEC-19 YES
        16 RFS     11-DEC-19 11-DEC-19 YES
        17 RFS     11-DEC-19 11-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        18 RFS     11-DEC-19 11-DEC-19 YES
        19 RFS     11-DEC-19 11-DEC-19 YES
        20 RFS     11-DEC-19 11-DEC-19 YES
        21 RFS     11-DEC-19 11-DEC-19 YES
        22 ARCH    11-DEC-19 11-DEC-19 YES
        22 ARCH    11-DEC-19 11-DEC-19 YES
        23 ARCH    11-DEC-19 11-DEC-19 YES
        23 ARCH    11-DEC-19 11-DEC-19 YES
        24 FGRD    11-DEC-19 11-DEC-19 YES
        24 ARCH    11-DEC-19 11-DEC-19 YES
        25 ARCH    11-DEC-19 11-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        25 FGRD    11-DEC-19 11-DEC-19 YES
        26 LGWR    11-DEC-19 11-DEC-19 YES
        26 ARCH    11-DEC-19 11-DEC-19 YES
        27 FGRD    11-DEC-19 11-DEC-19 YES
        27 RFS     11-DEC-19 11-DEC-19 YES
        28 RFS     11-DEC-19 11-DEC-19 YES
        29 RFS     11-DEC-19 11-DEC-19 YES
        30 RFS     11-DEC-19 11-DEC-19 YES
        31 RFS     11-DEC-19 11-DEC-19 YES
        32 RFS     11-DEC-19 11-DEC-19 YES
        33 RFS     11-DEC-19 11-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        34 ARCH    11-DEC-19 11-DEC-19 YES
        34 ARCH    11-DEC-19 11-DEC-19 YES
        35 ARCH    11-DEC-19 11-DEC-19 YES
        35 ARCH    11-DEC-19 11-DEC-19 YES
        36 FGRD    11-DEC-19 11-DEC-19 YES
        36 ARCH    11-DEC-19 11-DEC-19 YES
        37 ARCH    11-DEC-19 11-DEC-19 YES
        37 ARCH    11-DEC-19 11-DEC-19 YES
        38 FGRD    11-DEC-19 11-DEC-19 YES
        38 LGWR    11-DEC-19 11-DEC-19 YES
        39 LGWR    11-DEC-19 11-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        39 ARCH    11-DEC-19 11-DEC-19 YES
        40 LGWR    11-DEC-19 12-DEC-19 YES
        40 ARCH    11-DEC-19 12-DEC-19 YES
        41 LGWR    12-DEC-19 12-DEC-19 YES
        41 ARCH    12-DEC-19 12-DEC-19 YES
        42 RFS     12-DEC-19 12-DEC-19 YES
        42 FGRD    12-DEC-19 12-DEC-19 YES
        43 RFS     12-DEC-19 12-DEC-19 YES
        44 RFS     12-DEC-19 12-DEC-19 YES
        45 RFS     12-DEC-19 12-DEC-19 YES
        46 RFS     12-DEC-19 12-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        47 RFS     12-DEC-19 12-DEC-19 YES
        48 RFS     12-DEC-19 12-DEC-19 YES
        49 RFS     12-DEC-19 12-DEC-19 YES
        50 ARCH    12-DEC-19 12-DEC-19 YES
        50 ARCH    12-DEC-19 12-DEC-19 YES
        51 ARCH    12-DEC-19 12-DEC-19 YES
        51 ARCH    12-DEC-19 12-DEC-19 YES
        52 ARCH    12-DEC-19 12-DEC-19 YES
        52 FGRD    12-DEC-19 12-DEC-19 YES
        53 ARCH    12-DEC-19 12-DEC-19 YES
        53 ARCH    12-DEC-19 12-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        54 LGWR    12-DEC-19 12-DEC-19 YES
        54 FGRD    12-DEC-19 12-DEC-19 YES
        55 LGWR    12-DEC-19 12-DEC-19 NO
        55 ARCH    12-DEC-19 12-DEC-19 YES
        56 RFS     12-DEC-19 12-DEC-19 YES
        56 FGRD    12-DEC-19 12-DEC-19 NO
        57 RFS     12-DEC-19 12-DEC-19 YES
        58 RFS     12-DEC-19 12-DEC-19 YES
        59 RFS     12-DEC-19 12-DEC-19 YES
        60 RFS     12-DEC-19 12-DEC-19 YES
        61 RFS     12-DEC-19 12-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        62 RFS     12-DEC-19 12-DEC-19 YES
        63 RFS     12-DEC-19 13-DEC-19 YES
        64 RFS     13-DEC-19 13-DEC-19 YES
        65 RFS     13-DEC-19 13-DEC-19 NO
        66 RFS     13-DEC-19 13-DEC-19 NO

82 rows selected.

flashback_on的属性已发生的改变,从ON变成了RESTORE POINT ONLY

SQL>  select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE


SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

这个时候在备库就可以进行读写操作了,方便我们进行一些必须在实际环境下面的测试,测试完成之后再切换为物理standby,非常方便。

备库做如下操作:

SQL> create user ethan identified by ethan;

User created.

SQL> grant dba to ethan;

Grant succeeded.

SQL> create table ethan.t1 as select * from cat;

Table created.

SQL> select count(*) from ethan.t1;

  COUNT(*)
----------
      4944

主库做如下操作:

SQL> create user tom identified by tom;

User created.

SQL> grant dba to tom;

Grant succeeded.

SQL> create table tom.t1 as select * from cat;

Table created.

SQL> select count(*) from tom.t1;

  COUNT(*)
----------
      4944

主库做一次switch logfile,备库接收了归档,备库日志如下:

Fri Dec 13 15:48:07 2019
RFS[4]: Selected log 4 for thread 1 sequence 68 dbid 1509177297 branch 981396562

假如备库的测试任务已结束,现恢复到物理standby

--备库执行
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             197132568 bytes
Database Buffers          109051904 bytes
Redo Buffers                4747264 bytes
Database mounted.
SQL> alter database  convert to  physical standby;

Database altered.

手动重启一下备库

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

Total System Global Area  313159680 bytes
Fixed Size                  2227944 bytes
Variable Size             192938264 bytes
Database Buffers          113246208 bytes
Redo Buffers                4747264 bytes
Database mounted.
SQL> alter database open;

Database altered.

再来查看一下备库状态:

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL>  select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL>  select count(*) from ethan.t1;   --snashot dg时备库的测试数据已被删除
 select count(*) from ethan.t1
                            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>  select count(*) from tom.t1;   --snashot dg时主库的数据暂还没有同步过来
 select count(*) from tom.t1
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

tom.t1没有从主库同步过来,看一下归档是否被应用了。

SQL>  SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
         1 ARCH    13-DEC-19 13-DEC-19 NO
         2 ARCH    13-DEC-19 13-DEC-19 NO
         3 ARCH    13-DEC-19 13-DEC-19 NO
         4 ARCH    13-DEC-19 13-DEC-19 NO
         5 ARCH    13-DEC-19 14-DEC-19 NO
         6 ARCH    14-DEC-19 14-DEC-19 NO
         7 ARCH    14-DEC-19 14-DEC-19 NO
         8 RFS     11-DEC-19 11-DEC-19 YES
         8 ARCH    14-DEC-19 14-DEC-19 NO
         9 RFS     11-DEC-19 11-DEC-19 YES
         9 ARCH    14-DEC-19 15-DEC-19 NO
。。。
        55 ARCH    12-DEC-19 12-DEC-19 YES
        56 RFS     12-DEC-19 12-DEC-19 YES
        56 FGRD    12-DEC-19 12-DEC-19 NO
        57 RFS     12-DEC-19 12-DEC-19 YES

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        58 RFS     12-DEC-19 12-DEC-19 YES
        59 RFS     12-DEC-19 12-DEC-19 YES
        60 RFS     12-DEC-19 12-DEC-19 YES
        61 RFS     12-DEC-19 12-DEC-19 YES
        62 RFS     12-DEC-19 12-DEC-19 YES
        63 RFS     12-DEC-19 13-DEC-19 YES
        64 RFS     13-DEC-19 13-DEC-19 YES
        65 RFS     13-DEC-19 13-DEC-19 NO
        66 RFS     13-DEC-19 13-DEC-19 NO
        67 RFS     13-DEC-19 13-DEC-19 NO
        68 RFS     13-DEC-19 13-DEC-19 NO

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        69 RFS     13-DEC-19 13-DEC-19 NO
        70 RFS     13-DEC-19 13-DEC-19 NO
        71 RFS     13-DEC-19 14-DEC-19 NO
        72 RFS     14-DEC-19 14-DEC-19 NO
        73 RFS     14-DEC-19 14-DEC-19 NO
        74 RFS     14-DEC-19 14-DEC-19 NO
        75 RFS     14-DEC-19 14-DEC-19 NO
        76 RFS     14-DEC-19 15-DEC-19 NO
        77 RFS     15-DEC-19 15-DEC-19 NO
        78 RFS     15-DEC-19 15-DEC-19 NO
        79 RFS     15-DEC-19 15-DEC-19 NO

 SEQUENCE# REGISTR FIRST_TIM NEXT_TIME APPLIED
---------- ------- --------- --------- ---------
        80 RFS     15-DEC-19 16-DEC-19 NO
        81 RFS     16-DEC-19 16-DEC-19 NO
        82 RFS     16-DEC-19 16-DEC-19 NO
        83 RFS     16-DEC-19 16-DEC-19 NO
        84 RFS     16-DEC-19 16-DEC-19 NO
        85 RFS     16-DEC-19 16-DEC-19 NO

116 rows selected.

归档接收了没有被应用,打开active data gurad特性,应用归档后同步正常。

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL>  select count(*) from tom.t1;

  COUNT(*)
----------
      4944

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页