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
主庫切換日誌，隨後發現備庫可以正常應用。