11R2-DataGuard Scenarios.DG中应用闪回技术

前言：在11R2中的DG中有8种场景，Converting a Failed Primary Into a Standby Database Using Flashback Database是第二个场景，大体为Failover后闪回主库，并将主库转变为备库。
13.2 Converting a Failed Primary Into a Standby Database Using Flashback Database
前言：对于主备DG来说，如果主库故障，那我们可以将备库切换为主库，但此时原主库可能就无法切换为备库了，就必须在重新搭建DG。但如果主库在发生故障前，开启了闪回功能，那在将备库切换为主库后，还可以将原来的主库闪回到故障前，再重新改为备库，这样，又可以组成一套完整的DG。
13.2.1 Flashing Back a Failed Primary Database into a Physical Standby Database
实验环境：
 主库+物理备库。开启主库闪回，模拟主库故障（主库：dong_pri  物理备库：mm_stb）
step 0：prepare environment
主库：
SQL> show parameter db_re

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 3852M
db_recycle_cache_size                big integer 0
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'
-----------------
20140106 16:57:39
SQL> select CHECKPOINT_CHANGE# from v$database;
CHECKPOINT_CHANGE#
------------------
           1597967
SQL> create restore point fail_pri_0108 guarantee flashback database;
Restore point created.
SQL> select * from v$restore_point;
       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME                                                          PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
   1604209                     4 YES      8192000
06-JAN-14 04.59.10.000000000 PM                                                                        YES
FAIL_PRI_0108
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence     105
Next log sequence to archive   107
Current log sequence           107
主库做了一些操作：
SQL> create table alter_point(time date);
Table created.
SQL> insert into alter_point values(sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
随后主库故障：
SQL> shutdown abort
ORACLE instance shut down.
备注：就当shutdown abort后，数据文件不一致，主库启动到open状态失败。
备库：
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> alter database recover managed standby database finish force;
Database altered.
SQL>  select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL>  alter database commit to switchover to primary;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
Step 1   Determine the SCN at which the old standby database became the primary database.
On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;----备库。这里官方文档是将主库一会闪回到某个 
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)      scn点，但我这里就直接闪回到我设置的那个点。
----------------------------------------        scn的闪回将在下一个实验中使用。
1604745
Step 2   Flash back the failed primary database.
SQL> flashback database to restore point FAIL_PRI_0108;
Flashback complete.
Step 3   Convert the database to a physical standby database.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             226494876 bytes
Database Buffers          184549376 bytes
Redo Buffers                6103040 bytes
Database mounted.
Step 4   Start transporting redo to the new physical standby database.
新主库：
SQL> alter system set log_archive_dest_2='SERVICE=dong_pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=dong_pri';
System altered.
SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> show parameter log_archive_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/diag/
                                                 rdbms/dong/ VALID_FOR=(ALL_LOG
                                                 FILES,ALL_ROLES)  DB_UNIQUE_NA
                                                 ME=mm_stb
log_archive_dest_2                   string      SERVICE=dong_pri LGWR ASYNC VA
                                                 LID_FOR=(ONLINE_LOGFILES,PRIMA
                                                 RY_ROLES) DB_UNIQUE_NAME=dong_
                                                 pri
新备库：
SQL> alter system set log_archive_dest_3='';--把原来的都取消，只需要留log_archive_dest_1即可
System altered.
SQL> alter system set log_archive_dest_2='';
System altered.
SQL> show parameter archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string      DG_CONFIG=(dong_pri,mm_stb,mm_
                                                 stb2)
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/u01/app/oracle/diag/
                                                 rdbms/dong/ VALID_FOR=(ALL_LOG
                                                 FILES,ALL_ROLES)  DB_UNIQUE_NA
                                                 ME=dong_pri
Step 5   Start Redo Apply on the new physical standby database.
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
测试：
新主库：
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
新备库：
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/diag/rdbms/dong/
Oldest online log sequence     0
Next log sequence to archive   0
Current log sequence           6
SQL> select DEST_ID,name,COMPLETION_TIME,applied from v$archived_log order by COMPLETION_TIME asc;
         1 /u01/app/oracle/diag/rdbms/dong/1_107_833209595.dbf     06-JAN-14 NO
         1 /u01/app/oracle/diag/rdbms/dong/1_2_836154439.dbf       06-JAN-14 NO

   DEST_ID NAME                                                    COMPLETIO APPLIED
---------- ------------------------------------------------------- --------- ---------
         1 /u01/app/oracle/diag/rdbms/dong/1_1_836154439.dbf       06-JAN-14 NO
         2 /u01/app/oracle/diag/rdbms/dong/1_107_833209595.dbf     06-JAN-14 YES---已经应用
         2 /u01/app/oracle/diag/rdbms/dong/1_108_833209595.dbf     06-JAN-14 YES
         1 /u01/app/oracle/diag/rdbms/dong/1_3_836154439.dbf       06-JAN-14 NO
         1 /u01/app/oracle/diag/rdbms/dong/1_4_836154439.dbf       06-JAN-14 NO
         1 /u01/app/oracle/diag/rdbms/dong/1_5_836154439.dbf       06-JAN-14 NO
SQL> Select  flashback_on from v$database ;
FLASHBACK_ON
------------------
RESTORE POINT ONLY

13.2.2 Flashing Back a Failed Primary Database into a Logical Standby Database
实验环境：
 主库+物理备库。开启主库闪回，模拟主库故障（主库：dong_pri  物理备库：mm_stb）。和上一个实验唯一的区别是前者是将主库变为物理备库，而这个实验是将主库变为逻辑备库。
step 0：prepare environment
开启主库闪回：
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on;
Database altered.
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1597257
SQL> alter system switch logfile;
System altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1597324
SQL> create table flash_after(time date);
Table created.
SQL> insert into flash_after values(sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown abort
ORACLE instance shut down.
备库：
SQL>  alter database recover managed standby database finish force;
Database altered.
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
Step 1   Determine the flashback SCN and the recovery SCN.
SQL>  SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;--新主库
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
1597408
Step 2   Flash back the failed primary database to the flashback SCN identified in Step 1.
将原主库闪回到故障前的scn：
SQL> shutdown abort 
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  418484224 bytes
Fixed Size                  1336932 bytes
Variable Size             222300572 bytes
Database Buffers          188743680 bytes
Redo Buffers                6103040 bytes
Database mounted.
SQL> flashback database to scn 1597408;
Flashback complete.
Step 3   Convert the failed primary into a physical standby, and remount the standby database in preparation for recovery.
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;--将原主库转变为新备库。
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
Step 4   Configure the FAL_SERVER parameter to enable automatic recovery of log files.
配置新备库的故障转移：
SQL> show parameter fal_server
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fal_server                           string      mm_stb
Step 5   Remove divergent archive logs from the failed primary database.
Step 6   configure the old primary database to a new logical database.
新主库：
SQL> alter system set log_archive_dest_2='SERVICE=dong_pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=dong_pri';
System altered.
SQL> SQL>  alter system set log_archive_dest_state_2=enable;
System altered.
新备库：
SQL> ALTER DATABASE GUARD ALL;
Database altered.
SQL> alter system set LOG_ARCHIVE_DEST_4='LOCATION=/u01/app/oracle/diag/rdbms/dong/lgc_dong0/ VALID_for=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=dong_pri';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_4='ENABLE';
SQL> alter database recover to logical standby oldpri;
SQL>shutdown immediate
SQL>startup mount
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;
SQL> create temporary tablespace ....
SQL> CREATE PUBLIC DATABASE LINK mylink CONNECT TO system IDENTIFIED BY oracle  USING 'mm_stb';
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY mylink;
现在新的逻辑备库和新的主库就成了一套DG了。

13.2.3 Flashing Back a Logical Standby Database to a Specific Applied SCN
 对于逻辑DG来说，有时候需要对逻辑standby进行修改，但还想继续同步。这样的需求，闪回是再好不过了，将备库设置闪回，然后尽情修改，改完之后再闪回回去，然后继续同步。
备库开启闪回：
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on;
SQL> alter database open;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1613716
SQL> create table flashback_after as select * from dba_objects;---一会闪回后，将看不到此表
Table created.
闪回备库：
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to scn 1613716;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
          0
SQL> alter database open resetlogs;
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    1613868
SQL> alter database start logical standby apply immediate;
SQL>  select sequence#,first_change#,next_change#,timestamp,applied from dba_logstdby_log;
 SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# TIMESTAMP APPLIED
---------- ------------- ------------ --------- --------
       105       1594249      1597230 06-JAN-14 YES
       106       1597230      1599179 06-JAN-14 YES
       107       1599179      1599218 06-JAN-14 YES
       108       1599218      1599383 06-JAN-14 YES
       109       1599383      1599514 06-JAN-14 YES
       110       1599514      1599578 06-JAN-14 YES
6 rows selected.
SQL>  SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;
APPLIED_SCN
-----------
    1599618
主库切换日志，随后发现备库可以正常应用。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24500180/viewspace-1068495/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24500180/viewspace-1068495/