以RW及只读模式打开物理standby数据库并恢复为打开前状态

一、用Read only方式打开standby db，做一些查询，然后恢复到打开前的状态(standby db的报表用途)。

在备库上进行操作。首先取消恢复应用
SYS@dg2>recover managed standby database cancel;
Media recovery complete.
SYS@dg2>alter database open;
Database altered.
SYS@dg2>select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SYS@dg2>conn bys/bys
Connected.
BYS@dg2>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
BYS@dg2>select * from test;
         A
----------
       999
       111
关闭并重新打开数据库到MOUNT状态，执行恢复应用：alter database recover managed standby database disconnect  from session;

二、用R/W方式打开standby数据库，创建一个对象，然后通过flashback database将standby db恢复到打开前的状态（standby db的应用测试用途）。

1.在dg2上打开闪回区：

SYS@dg2>startup mount;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.
SYS@dg2>alter database recover managed standby database disconnect from session;
Database altered.
SYS@dg2>alter database recover managed standby database cancel;
Database altered.
SYS@dg2>alter system set db_recovery_file_dest_size=2G;
System altered.

2.需要提前建好目录，设置好目录权限。

SYS@dg2>alter system set db_recovery_file_dest='/u01/flashback_log';
System altered.
SYS@dg2>select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SYS@dg2>alter database flashback on;
Database altered.
SYS@dg2>select flashback_on from v$database;

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

3.创建还原点，名字为bys0802

SYS@dg2>create restore point bys0802 guarantee flashback database;
Restore point created.
#################################################################
在dg1上操作
SYS@dg1>alter system archive log current;
System altered.
SYS@dg1>alter system set log_archive_dest_state_2=defer;
System altered.
#################################

4.在dg2上打开数据库为RW模式并在数据库中进行创建表等操作

SYS@dg2>alter database activate standby database;
Database altered.
SYS@dg2>alter database open;
Database altered.
SYS@dg2>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
在dg2上建一个表：
SYS@dg2>conn bys/bys
Connected.
BYS@dg2>create table test1 as select * from test;
Table created.
BYS@dg2>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
TEST1                          TABLE
BYS@dg2>conn / as sysdba
Connected.
SYS@dg2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dg2>startup mount;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.
#############################################

4.闪回到上面设置的还原点，并转换为物理standby并打开应用--在MOUNT状态下

SYS@dg2>flashback database to restore point bys0802;
Flashback complete.
SYS@dg2>alter database convert to physical standby;
Database altered.
SYS@dg2>startup mount force;
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             281020828 bytes
Database Buffers          130023424 bytes
Redo Buffers                6103040 bytes
Database mounted.
SYS@dg2>alter database recover managed standby database disconnect from session;
Database altered.
################################################
在dg1上打开到归档目录2的日志
SYS@dg1>alter system set log_archive_dest_state_2=enable;
System altered.
#########################################

5.重新恢复备库为STANDBY后查询主备库日志应用状态：

dg1查询：
SYS@dg1>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PRIMARY          MAXIMUM AVAILABILITY
SYS@dg1>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@dg1>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            64
SYS@dg1>select sequence#,status,thread#,block# from v$managed_standby;
 SEQUENCE# STATUS          THREAD#     BLOCK#
---------- ------------ ---------- ----------
        62 CLOSING               1          1
        58 CLOSING               1       2049
        55 CLOSING               1          1
        64 CLOSING               1          1
         65 WRITING               1       1470
SYS@dg1>select process,status from v$managed_standby;
PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CLOSING
ARCH      CLOSING
LGWR      WRITING

dg2查询：
SYS@dg2>select protection_mode,database_role,protection_level from v$database;
PROTECTION_MODE      DATABASE_ROLE    PROTECTION_LEVEL
-------------------- ---------------- --------------------
MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY
SYS@dg2>select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SYS@dg2>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
            64
SYS@dg2>select sequence#,status,thread#,block# from v$managed_standby;

 SEQUENCE# STATUS          THREAD#     BLOCK#
---------- ------------ ---------- ----------
        64 CLOSING               1          1
         0 CONNECTED             0          0
         0 CONNECTED             0          0
        63 CLOSING               1          1
        65 WAIT_FOR_LOG          1          0
        65 IDLE                  1       1233
         0 IDLE                  0          0
         0 IDLE                  0          0
         0 IDLE                  0          0