ADG操作之闪回数据库-转换快照数据库-转换物理备库

注意：
    1、最大保护模式物理备库无法转换为快照数据库
    2、转换为快照数据库会创建还原点，为保证还原点scn日志可用，建议创建还原点前(即转换为快照数据库前)，切换redo日志为归档

1、查看当前备库状态(物理备库)

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

2、查询当前数据文件最新的SCN号

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           5081939
           5081939
           5081939
           5081939
           5081939
           5081939
           5081939
           5081939

8 rows selected.

3、关闭数据库

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

 

4、启动数据库到mount状态

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             884999048 bytes
Database Buffers          176160768 bytes
Redo Buffers                5517312 bytes
Database mounted.

 

5、闪回数据库到指定的SCN号

SQL> flashback database to scn 5081800;

Flashback complete.

 

6、将standby数据库转换为snapshot数据库

SQL> alter database convert to snapshot standby;

Database altered.

 

7、查看数据库状态

SQL> select status from v$instance;

STATUS
------------
MOUNTED

 

8、打开数据库

SQL> alter database open;

Database altered.

 

9、查询当前数据库角色(快照数据库)

SQL> select open_mode,database_role from v$database;

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

SQL>

 

 

10、创建一张表，验证快照数据库读写能力

SQL> create table t1(id number);

Table created.

SQL> select * from t1;

no rows selected

 

11、关闭数据库

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

 

12、启动数据库到mount状态

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             884999048 bytes
Database Buffers          176160768 bytes
Redo Buffers                5517312 bytes
Database mounted.

 

13、将快照数据库转换为物理备库

SQL> alter database convert to physical standby;

Database altered.

 

14、关闭数据库

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

 

15、启动数据库(open)

SQL> startup

ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             884999048 bytes
Database Buffers          176160768 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.

 

 

16、查看数据库当前状态(物理备库)

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY            PHYSICAL STANDBY

 

17、验证快照数据库的操作是否回退

SQL> select * from t1;

select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

 

18、开启物理备库日志应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

 

 

 

19、验证数据文件scn号

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
           5086010
           5086010
           5086010
           5086010
           5086010
           5086010
           5086010
           5086010

8 rows selected.

SQL>

 

 

20、验证日志应用延迟

SQL> set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

SQL> SQL> SQL> SQL> SQL> SQL>
NAME          VALUE                UNIT                           TIME_COMPUTED
------------- -------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00         day(2) to second(0) interval   06/22/2018 11:50:55
apply lag     +00 00:00:00         day(2) to second(0) interval   06/22/2018 11:50:55

SQL>