11.2.0.4 Dataguard临时读写三种方法

  • 你好我是李白

    2020-07-26 16:13:34
  • Oracle
  • 原创

包含以下三部分内容，都可以实现物理备库临时读写以及再恢复为physical standby：

  • flashback database闪回physical standby

  • activate standby database

  • snapshot standby

1. 在physical standby闪回恢复主库误操作数据

只要Flashback dest空间跟归档在，可以任意闪回。

1.1 查询当前primary SCN

SYS@honor1 > select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
17031266

1.2 primary创建测试表

HR@honor1 > create table test_flashback parallel as select * from user_objects;
HR@honor1 > insert into test_flashback parallel select * from user_objects;
HR@honor1 > commit;

1.3 备库查询测试表，闪回到创建该表前

SYS@honordg > desc hr.test_flashback;    # 检查，备库已经同步该表
SYS@honordg > alter database recover managed standby database cancel;
SYS@honordg > shutdown immediate;
SYS@honordg > startup mount;
SYS@honordg > flashback database to scn 17031266;
Flashback complete.
21:03:11 SYS@honordg > alter database open read only;
Database altered.
HR@honordg > desc test_flashback;     # 可以看到已经闪回到创建该表前。
ERROR:
ORA-04043: object test_flashback does not exist

1.4 恢复physical standby

# 检查备库当前状态

SYS@honordg > select GUARD_STATUS,OPEN_MODE,STANDBY_BECAME_PRIMARY_SCN,SWITCHOVER_STATUS,DATABASE_ROLE from v$database;
GUARD_S OPEN_MODE                          STANDBY_BECAME_PRIMARY_SCN SWITCHOVER_STATUS    DATABASE_ROLE
------- -------------------- ---------------------------------------- -------------------- ----------------
NONE    READ ONLY                                                   0 NOT ALLOWED          PHYSICAL STANDBY
SYS@honordg > select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
17031266

# 直接开启恢复，备库即可利用归档

SYS@honordg > alter database recover managed standby database disconnect from session;
Database altered.

1.5 通过alert详解闪回以及恢复physical standby过程

（1）可以看到physical standby已经恢复sequence 914日志

Archived Log entry 39 added for thread 1 sequence 914 rlc 984508005 ID 0x150ac660 dest 2:
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for thread 1 sequence 915 dbid 353046371 branch 984508005
Tue Jun 09 20:30:02 2020
Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_914.493.1042661953
RFS[1]: Opened log for thread 1 sequence 915 dbid 353046371 branch 984508005
RFS[2]: Assigned to RFS process 14109
RFS[2]: No standby redo logfiles created
Archived Log entry 40 added for thread 1 sequence 915 rlc 984508005 ID 0x150ac660 dest 2:
RFS[2]: Opened log for thread 1 sequence 916 dbid 353046371 branch 984508005
Tue Jun 09 21:02:12 2020

（2）执行闪回，可以看到利用闪回日志以及归档闪回到指定scn

flashback database to scn 17031266
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Flashback Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_912.498.1042661497
Flashback Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_913.467.1042661953
Flashback Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_914.493.1042661953
Incomplete Recovery applied until change 17031267 time 06/18/2020 10:47:12
Flashback Media Recovery Complete
Completed: flashback database to scn 17031266

（3）恢复physical standby

# 可以看到会再次利用sequence 914恢复physical standby

Physical standby database opened for read only access.
Completed: alter database open read only
Tue Jun 09 21:03:28 2020
db_recovery_file_dest_size of 9216 MB is 17.72% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jun 09 21:06:41 2020
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (honordg)
Tue Jun 09 21:06:41 2020
MRP0 started with pid=34, OS id=14273 
MRP0: Background Managed Standby Recovery process started (honordg)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_914.493.1042661953
Completed: alter database recover managed standby database disconnect from session
Media Recovery Log +DGFRA/honordg/archivelog/2020_06_09/thread_1_seq_915.494.1042664529
Media Recovery Waiting for thread 1 sequence 916 (in transit)

2. 临时激活转换为读写库

2.1 取消日志应用

SYS@honordg > alter database recover managed standby database cancel;
Database altered.

2.2 创建guarantee还原点

SYS@honordg > create restore point rst_guar guarantee flashback database;
Restore point created.
SYS@honordg > select scn,name,guarantee_flashback_database,preserved from v$restore_point;
                                     SCN NAME                                                                   GUA PRE
---------------------------------------- ---------------------------------------------------------------------- --- ---
                                17517039 RST_GUAR                                                               YES YES

2.3 激活备库，激活后库由read only转化为mount

SYS@honordg > ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database altered.
SYS@honordg > select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          MOUNTED
SYS@honordg > select scn,name,guarantee_flashback_database,preserved from v$restore_point;
                                     SCN NAME                                                                   GUA PRE
---------------------------------------- ---------------------------------------------------------------------- --- ---
                                17517039 RST_GUAR                                                               YES YES

2.4 打开数据库，创建测试数据

SYS@honordg > alter database open;
Database altered.
SYS@honordg > select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE
SYS@honordg > create table hr.test_guarantee as select * from dba_tables;
Table created.
SYS@honordg > select count(1) from hr.test_guarantee;
                                COUNT(1)
----------------------------------------
                                    3024

2.5 还原physical standby

（1）关闭数据库，启动到mount

SYS@honordg > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@honordg > startup mount;
ORACLE instance started.
Total System Global Area                                521936896 bytes
Fixed Size                                                2254824 bytes
Variable Size                                           377489432 bytes
Database Buffers                                        138412032 bytes
Redo Buffers                                              3780608 bytes
Database mounted.

# 闪回还原点 

SYS@honordg > flashback database to restore point RST_GUAR;
Flashback complete.

# 查看测试数据

SYS@honordg > alter database open;    #闪回之后，需要执行convert语句转换为physical standby
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SYS@honordg > select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          MOUNTED
SYS@honordg > alter database convert to physical standby;
Database altered.

# 转换后数据库处于nomount阶段

SYS@honordg > select status from v$instance;
STATUS
----------
STARTED
SYS@honordg > shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@honordg > startup;
ORACLE instance started.
Total System Global Area                                521936896 bytes
Fixed Size                                                2254824 bytes
Variable Size                                           377489432 bytes
Database Buffers                                        138412032 bytes
Redo Buffers                                              3780608 bytes
Database mounted.
Database opened.

# 开启日志应用

SYS@honordg > alter database recover managed standby database disconnect from session;
Database altered.

# 检查数据库状态角色

SYS@honordg > select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

3. 利用snapshot database，临时读写备库

3.1 查看physical状态，是否开启闪回 

SYS@honordg > select * from v$restore_point;
no rows selected
     
SYS@honordg > show parameter recovery;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DGFRA
db_recovery_file_dest_size           big integer 9G
recovery_parallelism                 integer     0
SYS@honordg > select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SYS@honordg > select database_role,open_mode from v$database; 
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY

3.2 取消日志应用， 转换为snapshot standby database

SYS@honordg > alter database recover managed standby database cancel;
Database altered.
SYS@honordg > alter database convert to snapshot standby;
Database altered.
SYS@honordg > select database_role,open_mode from v$database; 
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
SYS@honordg > alter database open;
Database altered.

3.3 检查physical standby当前状态

SYS@honordg > select database_role,open_mode from v$database; 
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE
SYS@honordg > select scn,name,guarantee_flashback_database,preserved from v$restore_point;
           SCN NAME                                                                   GUA PRE
-------------- ---------------------------------------------------------------------- --- ---
      17034110 SNAPSHOT_STANDBY_REQUIRED_06/09/2020 21:36:15                          YES YES

3.4 临时读写

SYS@honordg > create table hr.test_snapshot as select * from dba_users;
Table created.
SYS@honordg > select count(*) from hr.test_snapshot;
                                COUNT(*)
----------------------------------------
                                      44

 3 .5 恢复physical standby

（1）检查状态，转换为physical standby

SYS@honordg > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@honordg > startup mount;
ORACLE instance started.
Total System Global Area                                521936896 bytes
Fixed Size                                                2254824 bytes
Variable Size                                           377489432 bytes
Database Buffers                                        138412032 bytes
Redo Buffers                                              3780608 bytes
Database mounted.
SYS@honordg > select database_role,open_mode from v$database;
DATABASE_ROLE    OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED
Elapsed: 00:00:00.02
SYS@honordg > select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
Elapsed: 00:00:00.00
SYS@honordg > alter database convert to physical standby;
Database altered.
SYS@honordg > select status from v$instance;      # 可以看到转换完成数据库处于nomount状态
STATUS
----------
STARTED
SYS@honordg > shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@honordg > startup;
ORACLE instance started.
Total System Global Area                                521936896 bytes
Fixed Size                                                2254824 bytes
Variable Size                                           377489432 bytes
Database Buffers                                        138412032 bytes
Redo Buffers                                              3780608 bytes
Database mounted.
Database opened.
SYS@honordg > alter database recover managed standby database disconnect from session;
Database altered.

# 查看之前读写数据，发现已经清除

SYS@honordg > select count(*) from hr.test_snapshot;
select count(*) from hr.test_snapshot
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

# 检查alert日志，可以发现，snapshot standby是利用还原点restore point完成恢复

Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point 
Guaranteed restore point  dropped



请使用浏览器的分享功能分享到微信等