ORACLE中主從中斷后如何恢復

這篇文章給大家介紹ORACLE中主從中斷后如何恢復，內容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。

一、知識回顧
1、正常情況我們在生產中配置ADG會使用最大可用模式配合參數lgwr和sync。
SQL> show parameter log_archive_dest_2
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2       string    SERVICE=stdtest lgwr sync affi
                               rm VALID_FOR=(ONLINE_LOGFILES,
                               PRIMARY_ROLE) DB_UNIQUE_NAME=s
                               tdtest
這種配置在保證備庫同步情況不影響主庫的情況下最大限度的保證了主備的實時性。

SQL> select name,dbid,database_role,protection_mode from v$database;

NAME  DBID DATABASE_ROLE    PROTECTION_MODE
--------- ---------- ---------------- --------------------
PRITEST   2249383711 PRIMARY       MAXIMUM AVAILABILITY

2、監控進程
主庫：
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH    145 CLOSING
ARCH   ARCH    135 CLOSING
ARCH   ARCH    141 CLOSING
ARCH   ARCH    146 CLOSING
LGWR   LGWR    147 WRITING

備庫：
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH    145 CLOSING
ARCH   ARCH    146 CLOSING
ARCH   ARCH      0 CONNECTED
ARCH   ARCH    144 CLOSING
RFS   ARCH      0 IDLE
RFS   UNKNOWN     0 IDLE
RFS   LGWR    147 IDLE
RFS   UNKNOWN     0 IDLE
MRP0   N/A    147 APPLYING_LOG

3、監控恢復操作的進程
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
        1    146       0    0
        .......
        1    146       1  146
32 rows selected.

4、回顧三種同步模式
4.1 最大保護模式（Maximum Protection）
1）這種模式提供了最高級別的數據保護能力；
2）要求至少一個物理備庫收到重做日志后，主庫的事務才能夠提交；
3）主庫找不到合適的備庫寫入時，主庫會自動關閉，防止未受保護的數據出現；
4）優點：該模式可以保證備庫沒有數據丟失；
5）缺點：主庫的自動關閉會影響到主庫的可用性，同時需要備庫恢復后才能提交，對網絡等客觀條件要求非常的高，
         主庫的性能會因此受到非常大的沖擊。

4.2 最大可用性模式（Maximum Availability）
1）該模式提供了僅次于“最大保護模式”的數據保護能力；
2）要求至少一個物理備庫收到重做日志后，主庫的事務才能夠提交；
3）主庫找不到合適的備庫寫入時，主數據庫不會關閉，在沒有達到net_timeout之前主庫會hang住，但是并不是shutdown。而后主數據庫以
   最大性能模式運行直到故障消除，并且解決所有重做日志文件的中斷。當所有中斷解決之后，主數據庫自動繼續以最大可用性模式運行；
4）優點：該模式可以在沒有問題出現的情況下，保證備庫沒有數據丟失，是一種折中的方法；
5）缺點：在正常運行的過程中缺點是主庫的性能受到諸多因素的影響。

4.3 最大性能模式（Maximum Performance）
1）該模式是默認模式，可以保證主數據庫的最高可用性；
2）保證主庫運行過程中不受備庫的影響，主庫事務正常提交，不因備庫的任何問題影響到主庫的運行；
3）優點：避免了備庫對主數據庫的性能和可用性影響；
4）缺點：如果與主庫提交的事務相關的恢復數據沒有發送到備庫，這些事務數據將被丟失，不能保證數據無損失。

二、主備庫同步中斷后，如何恢復同步
在很多場合下主從同步中斷，如恢復同步可能會遇到很多不同情況，現根據幾個場景做以下實驗。
實驗一
主備中斷后，當主庫歸檔日志完整，備庫啟動后會自動恢復
1、查看主備庫日志情況
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     63
2、備庫：
模擬備庫故障，直接關機
SQL> shutdown abort
ORACLE instance shut down

3、主庫：
SQL> alter system switch logfile;
System altered.
SQL> create table a (id integer);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> insert into a values (11);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     68
4、備庫：
模擬排除故障，重新同步備庫。
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size      2252664 bytes
Variable Size    754974856 bytes
Database Buffers   419430400 bytes
Redo Buffers      9195520 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     68
由于主庫歸檔日志都在，在備庫故障期間的log file gap，會被歸檔的日志文件由arch進程負責傳輸到從數據庫。
同時通過LNSn把歸檔日志傳到備庫，備庫RFS接受，MRP進程應用到standby redo log。
查看日志應用情況。
SQL> select sequence#, applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
  7 YES
 ......
 68 IN-MEMORY

實驗二
由于歸檔丟失或備庫控制文件損壞等，需主庫全量備份恢復
1、備庫模擬宕機，直接關閉數據庫
SQL> shutdown abort
ORACLE instance shut down.

2、在備庫宕機期間主庫做一些操作
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     70
SQL> create table b (name char(1));
Table created.
SQL> insert into b values ('a');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     72
[oracle@pritest archivelog]$ pwd
/u01/app/oracle/archivelog
[oracle@pritest archivelog]$ ls -trl
......
-rw-r----- 1 oracle oinstall   444416 Nov 10 11:00 arch_1_69_956333727.arc
-rw-r----- 1 oracle oinstall  1224704 Nov 10 11:00 arch_1_70_956333727.arc
-rw-r----- 1 oracle oinstall  1097216 Nov 10 11:32 arch_1_71_956333727.arc
-rw-r----- 1 oracle oinstall  4003840 Nov 10 13:53 arch_1_72_956333727.arc

3、主庫刪掉備庫宕機期間產生的歸檔日志
[oracle@pritest archivelog]$ rm arch_1_7*

4、啟動備庫
由于歸檔日志丟失，備庫不能直接恢復
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size      2252664 bytes
Variable Size    754974856 bytes
Database Buffers   419430400 bytes
Redo Buffers      9195520 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/pritest/system01.dbf'

5、備份主庫控制文件和做全備，并同步主庫
SQL> alter database create standby controlfile as '/tmp/controldg01.ctl';
Database altered.
[oracle@pritest pritest]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/oradata/pritest/control01.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9808KB   9.6MB/s   00:00   
[oracle@pritest pritest]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/fast_recovery_area/pritest/control02.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9808KB   9.6MB/s   00:00 

[oracle@pritest archivelog]$ rman target /
RMAN> backup database format '/u01/app/oracle/backup/full_%d_%T_%s_%U' plus archivelog FORMAT '/u01/app/oracle/backup/arc_%d_%s_%p.bak';
Starting backup at 2017-11-10 14:18:11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/10/2017 14:18:13
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/archivelog/arch_1_7_956333727.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> crosscheck archivelog all;
。。。。。。
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
List of Archived Log Copies for database with db_unique_name PRITEST
=====================================================================
Key     Thrd Seq     S Low Time          
------- ---- ------- - -------------------
2       1    7       X 2017-10-02 16:29:17
        Name: /u01/app/oracle/archivelog/arch_1_7_956333727.arc
123     1    70      X 2017-11-10 11:00:02
        Name: /u01/app/oracle/archivelog/arch_1_70_956333727.arc
124     1    71      X 2017-11-10 11:00:05
        Name: /u01/app/oracle/archivelog/arch_1_71_956333727.arc
125     1    72      X 2017-11-10 11:32:15
        Name: /u01/app/oracle/archivelog/arch_1_72_956333727.arc

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_7_956333727.arc RECID=2 STAMP=956335716
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_70_956333727.arc RECID=123 STAMP=959684405
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_71_956333727.arc RECID=124 STAMP=959686335
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_72_956333727.arc RECID=125 STAMP=959694835
Deleted 4 EXPIRED objects

RMAN> backup database format '/u01/app/oracle/backup/full_%d_%T_%s_%U' plus archivelog FORMAT '/u01/app/oracle/backup/arc_%d_%s_%p.bak';
......
Starting backup at 2017-11-10 15:43:09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=81 RECID=141 STAMP=959701390
channel ORA_DISK_1: starting piece 1 at 2017-11-10 15:43:10
channel ORA_DISK_1: finished piece 1 at 2017-11-10 15:43:11
piece handle=/u01/app/oracle/backup/arc_PRITEST_20_1.bak tag=TAG20171110T154310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-11-10 15:43:11

RMAN> exit

[oracle@pritest backup]$ ll
total 1152500
-rw-r----- 1 oracle oinstall   72775680 Nov 10 15:42 arc_PRITEST_16_1.bak
-rw-r----- 1 oracle oinstall   25392128 Nov 10 15:42 arc_PRITEST_17_1.bak
-rw-r----- 1 oracle oinstall      22528 Nov 10 15:43 arc_PRITEST_20_1.bak
-rw-r----- 1 oracle oinstall 1071833088 Nov 10 15:43 full_PRITEST_20171110_18_0isj7ob8_1_1
-rw-r----- 1 oracle oinstall   10125312 Nov 10 15:43 full_PRITEST_20171110_19_0jsj7ocb_1_1
[oracle@pritest backup]$ pwd
/u01/app/oracle/backup
[oracle@pritest backup]$ scp -r ./* oracle@192.168.91.129:/u01/app/oracle/backup/
oracle@192.168.91.129's password:
arc_PRITEST_11_1.bak                          100%   69MB  69.4MB/s   00:01   
arc_PRITEST_12_1.bak                          100%   23MB  23.0MB/s   00:00   
arc_PRITEST_15_1.bak                          100%   40KB  39.5KB/s   00:00   
full_PRITEST_20171110_13_0dsj7m2i_1_1         100% 1023MB  39.3MB/s   00:26   
full_PRITEST_20171110_14_0esj7m4b_1_1         100% 9888KB   9.7MB/s   00:00  

6、用備份恢復備庫
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

[oracle@stdtest backup]$ rman target /
RMAN> catalog start with '/u01/app/oracle/backup';
Starting implicit crosscheck backup at 2017-11-10 15:47:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Finished implicit crosscheck backup at 2017-11-10 15:47:18
Starting implicit crosscheck copy at 2017-11-10 15:47:18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 2017-11-10 15:47:18
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/backup/arc_PRITEST_11_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_12_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_17_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_15_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_16_1.bak
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_18_0isj7ob8_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_19_0jsj7ocb_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_14_0esj7m4b_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1
File Name: /u01/app/oracle/backup/arc_PRITEST_20_1.bak
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/backup/arc_PRITEST_11_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_12_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_17_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_15_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_16_1.bak
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_18_0isj7ob8_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_19_0jsj7ocb_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_14_0esj7m4b_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1
File Name: /u01/app/oracle/backup/arc_PRITEST_20_1.bak

RMAN> restore database;
Starting restore at 2017-11-10 15:18:38
Starting implicit crosscheck backup at 2017-11-10 15:18:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 2017-11-10 15:18:39
Starting implicit crosscheck copy at 2017-11-10 15:18:39
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 2017-11-10 15:18:39
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/pritest/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/pritest/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/pritest/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/pritest/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1 tag=TAG20171110T150346
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2017-11-10 15:19:14

RMAN> recover database;
Starting recover at 2017-11-10 15:49:39
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 80 is already on disk as file /u01/app/oracle/archivelog/arch_1_80_956333727.arc
archived log for thread 1 with sequence 81 is already on disk as file /u01/app/oracle/archivelog/arch_1_81_956333727.arc
archived log for thread 1 with sequence 82 is already on disk as file /u01/app/oracle/archivelog/arch_1_82_956333727.arc
archived log for thread 1 with sequence 83 is already on disk as file /u01/app/oracle/archivelog/arch_1_83_956333727.arc
archived log file name=/u01/app/oracle/archivelog/arch_1_80_956333727.arc thread=1 sequence=80
archived log file name=/u01/app/oracle/archivelog/arch_1_81_956333727.arc thread=1 sequence=81
archived log file name=/u01/app/oracle/archivelog/arch_1_82_956333727.arc thread=1 sequence=82
archived log file name=/u01/app/oracle/archivelog/arch_1_83_956333727.arc thread=1 sequence=83
unable to find archived log
archived log thread=1 sequence=84
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/10/2017 15:49:40
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 84 and starting SCN of 1089419

這報錯可以忽略，主機84號歸檔日志還未歸檔。

SQL> alter database open read only;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

7、驗證主備庫日志同步
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     83

實驗三
1、場景介紹
當主備同步中斷了，備庫想快一點恢復，偏偏這個時候歸檔太多恢復不過來或者說需要的歸檔直接丟了，可以選擇
重新搭建備庫。如果庫小的話還是可以的，但是如果主庫比較大可能耗費的時間會很久，而且容易出一些問題。單單是全庫
備份恢復這個時間就不會短，更何況中間還會涉及到很多東西。其實利用基于scn的備份去恢復我們的備庫，從而繞開中間
過多或者丟失的歸檔。
我們都知道我們傳統的dg都是屬于物理dg，下面是物理dg的簡單解釋：
物理備用數據庫：以基于塊對塊的主數據庫同樣的磁盤數據庫結構，物理備用數據庫物理等同于主數據庫。
特性：
1.數據庫的每一個塊的內容包括塊的邏輯位置都和主庫完全一致
2.DG通過執行重做應用，維護物理備用數據庫
3.物理STANDBY 打開flashbackdatabase后可以完全讀寫打開
4.物理備用數據庫使用通過oracle恢復機制，從歸檔重做日志文件或直接從備系統上的備重做日志文件用用重做數據來恢復。
5.物理備用數據庫可用于執行備份
6.物理備用數據庫使用重做應用技術使用低級別的恢復機制應用更改，繞過了所有SQL基本代碼層，因此應用海量重做數據最有效，
  性能大于邏輯備份。
我們找到備庫端數據文件中最低的scn，然后在主庫去基于這個scn進行備份，這個時候rman回去掃描整個主庫的塊，如果塊內的scn小于
備庫端數據文件中最低的scn，則證明這個塊從備庫應用到的時間點到現在是沒有改變的，就忽略掉這個塊。如果塊內的scn大于備庫端數據
文件中最低的scn證明在這個階段這個快進行了修改，就記錄下這個塊的內容。等拿到備庫端去恢復的時候就替換這個塊的內容。
官方文檔mos的id(Doc ID 836986.1)，大家可自行去查看。
Steps to perform for Rolling Forward aPhysical Standby Database using RMAN Incremental Backup.

2、開始模擬實驗
2.1 直接關閉備庫數據庫
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
    123
SQL> shutdown abort
ORACLE instance shut down.

2.2 在備庫宕機期間主庫數據變化
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
    123

SQL> alter database enable block change tracking using file '/u01/app/oracle/oradata/pritest/rman_change_track.bct' reuse;
Database altered.
SQL> col filename for a55
SQL> select * from v$block_change_tracking;
STATUS    FILENAME       BYTES
---------- ------------------------------------------------------- ----------
ENABLED    /u01/app/oracle/oradata/pritest/rman_change_track.bct     11599872

SQL> update a set id=18;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> create table ee (id integer);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> alter table ee move tablespace USERS;
Table altered.
SQL> alter system switch logfile;
System altered.

2.3 模擬主庫丟失歸檔日志
[oracle@pritest archivelog]$ ls -trl
...
-rw-r----- 1 oracle oinstall   989184 Nov 16 16:20 arch_1_124_956333727.arc
-rw-r----- 1 oracle oinstall    24576 Nov 16 16:21 arch_1_125_956333727.arc
-rw-r----- 1 oracle oinstall    31232 Nov 16 16:21 arch_1_126_956333727.arc
-rw-r----- 1 oracle oinstall    17920 Nov 16 16:22 arch_1_127_956333727.arc

[oracle@pritest archivelog]$ rm arch_1_125_956333727.arc arch_1_126_956333727.arc

2.4 查找備庫數據文件最低的scn
[oracle@stdtest ~]$ sqlplus / as sysdba
SQL> startup mount
SQL> select CHECKPOINT_CHANGE#  from v$datafile_header order by 1;
CHECKPOINT_CHANGE#
------------------
    1160832
    1160832
    1160832
    1160832

SQL> select CHECKPOINT_CHANGE#  from v$database  order by 1;
CHECKPOINT_CHANGE#
------------------
    1159823

SQL> shutdown abort
ORACLE instance shut down.

2.5 備份主庫控制文件,更新備庫控制文件
SQL> alter database create standby controlfile as '/tmp/controldg01.ctl';
Database altered.
[oracle@pritest ~]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/oradata/pritest/control01.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9872KB   9.6MB/s   00:00   
[oracle@pritest ~]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/fast_recovery_area/pritest/control02.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9872KB   9.6MB/s   00:00  

2.6 執行基于SCN的增量備份
[oracle@pritest backup]$ rman target /
RMAN> run {
sql 'alter system switch logfile';
backup incremental from scn 1159823 database format '/u01/app/oracle/backup/incre_%d_%T_%s_%U' tag 'FORSTANDBY';
}2> 3> 4>

using target database control file instead of recovery catalog
sql statement: alter system switch logfile

Starting backup at 2017-11-14 11:07:02
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/pritest/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/pritest/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/pritest/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/pritest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-11-14 11:07:02
channel ORA_DISK_1: finished piece 1 at 2017-11-14 11:07:03
piece handle=/u01/app/oracle/backup/incre_PRITEST_20171114_32_10sjhpmm_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2017-11-14 11:07:04
channel ORA_DISK_1: finished piece 1 at 2017-11-14 11:07:05
piece handle=/u01/app/oracle/backup/incre_PRITEST_20171114_33_11sjhpmn_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-11-14 11:07:05

RMAN> list backupset; (我有點不解？)
using target database control file instead of recovery catalog
specification does not match any backup in the repository

[oracle@pritest backup]$ ls -trl
total 13808
-rw-r----- 1 oracle oinstall  3981312 Nov 16 16:28 incre_PRITEST_20171116_39_17sjnl9v_1_1
-rw-r----- 1 oracle oinstall 10158080 Nov 16 16:28 incre_PRITEST_20171116_40_18sjnla3_1_1

[oracle@pritest backup]$ scp -r ./incre_PRITEST_20171116* oracle@192.168.91.129:/u01/app/oracle/backup
oracle@192.168.91.129's password:
incre_PRITEST_20171114_32_10sjhpmm_1_1        100%   16MB  15.6MB/s   00:00   
incre_PRITEST_20171114_33_11sjhpmn_1_1        100% 9920KB   9.7MB/s   00:00 

2.7 備庫恢復控制文件
[oracle@stdtest backup]$ rman target/
RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    1185853440 bytes

Fixed Size                     2252664 bytes
Variable Size                754974856 bytes
Database Buffers             419430400 bytes
Redo Buffers                   9195520 bytes

RMAN> catalog start with '/u01/app/oracle/backup';

Starting implicit crosscheck backup at 2017-11-16 16:31:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
Finished implicit crosscheck backup at 2017-11-16 16:31:26

Starting implicit crosscheck copy at 2017-11-16 16:31:26
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 2017-11-16 16:31:26

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/STDTEST/autobackup/2017_11_13/o1_mf_s_959944964_f0l4lkk8_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STDTEST/autobackup/2017_11_13/o1_mf_s_959707108_f0kyqno7_.bkp

searching for all files that match the pattern /u01/app/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_40_18sjnla3_1_1
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_40_18sjnla3_1_1
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1

2.8 恢復數據庫
restore 是還原，文件級的恢復。就是物理文件還原。
recover 是恢復，數據級的恢復。邏輯上恢復，比如應用歸檔日志、重做日志，全部同步，保持一致。
這里數據文件是基于原來的，所以不需要 restore database。

RMAN> recover database;
Starting recover at 2017-11-16 16:32:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/pritest/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/pritest/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/pritest/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/pritest/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 131 is already on disk as file /u01/app/oracle/archivelog/arch_1_131_956333727.arc
archived log for thread 1 with sequence 132 is already on disk as file /u01/app/oracle/archivelog/arch_1_132_956333727.arc
archived log for thread 1 with sequence 133 is already on disk as file /u01/app/oracle/archivelog/arch_1_133_956333727.arc
archived log file name=/u01/app/oracle/archivelog/arch_1_131_956333727.arc thread=1 sequence=131
archived log file name=/u01/app/oracle/archivelog/arch_1_132_956333727.arc thread=1 sequence=132
archived log file name=/u01/app/oracle/archivelog/arch_1_133_956333727.arc thread=1 sequence=133
unable to find archived log
archived log thread=1 sequence=134
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/16/2017 16:32:16
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 134 and starting SCN of 1162339
這報錯可以忽略，主機134號歸檔日志還未歸檔。

2.9 啟動數據庫應用MRP并且驗證主備同步
[oracle@stdtest ~]$ sqlplus / as sysdba
SQL> alter database open read only;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
    133

SQL> select * from ee;
no rows selected

3、知識補充
我們在進行基于scn增量備份的時候他需要去掃描全庫，再做兩個scn點的差集，去判斷這個塊會不會需不需要進行備份，
那么如果主庫很大，那么做增量備份的也不會很快。當然會比全部備份快很多，他只需要掃描所有的塊，需要記錄的不一定會很多。

實驗四
1、場景
由于一些測試必須使用生產環境，可以拿備庫給做測試，測試完后再恢復。
主要流程是備庫開啟數據庫閃回，把備庫從PHYSICAL STANDBY模式切換到SNAPSHOT STANDBY模式，該模式可讀寫，等測試完切回
PHYSICAL STANDBY模式，利用閃回恢復到測試之前的狀態，再應用歸檔日志恢復原狀。

2、先查看備庫是否開啟閃回
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> show parameter DB_RECOVERY_FILE_DEST
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size      big integer 4182M

查看閃回數據文件
SQL> !ls -trl /u01/app/oracle/fast_recovery_area/STDTEST/flashback
total 102416
-rw-r----- 1 oracle oinstall 52436992 Nov 17 09:51 o1_mf_f0wj4n6t_.flb
-rw-r----- 1 oracle oinstall 52436992 Nov 17 10:42 o1_mf_f0wj4jkw_.flb

3、查看備庫當前狀態
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE  DB_UNIQUE_NAME   OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY stdtest   READ ONLY WITH APPLY

4、切換到 snapshot standby
SQL> alter database convert to snapshot standby;
Database altered.

5、查看日志
[oracle@stdtest trace]$tail -f /u01/app/oracle/diag/rdbms/stdtest/pritest/trace/alert_pritest.log
Fri Nov 17 10:19:35 2017
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/17/2017 10:19:35
Killing 4 processes with pids 2902,2896,2898,2900 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2850
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Fri Nov 17 10:19:38 2017
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1170706
Resetting resetlogs activation ID 2249370806 (0x8612acb6)
Online log /u01/app/oracle/oradata/pritest/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/pritest/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/pritest/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1170704
Fri Nov 17 10:19:38 2017
Setting recovery target incarnation to 3
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

6、開打備庫
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

SQL>  alter database open;
Database altered.

7、對備庫做一些操作
SQL> select * from ee;
no rows selected

SQL> drop table ee purge;
Table dropped.

SQL> create user test identified by test123;
User created.

SQL> grant dba to test;
Grant succeeded.

SQL> conn test/test123
Connected.
SQL> create table abc as select * from dba_users;
Table created.

8、在SNAPSHOT STANDBY模式下，日志正常傳輸但不應用，下面語句進行確認
SQL> col CTIME for a18
SQL> col NAME for a15
SQL> col VALUE for a13
SQL> col DATUM_TIME for a20
SQL> select to_char(SYSDATE,'yyyymmdd hh34:mi:ss') CTIME,NAME,VALUE,DATUM_TIME
SQL> from V$DATAGUARD_STATS WHERE NAME LIKE '%lag';
CTIME     NAME     VALUE  DATUM_TIME
------------------ --------------- ------------- --------------------
20171117 11:03:21  transport lag   +00 00:00:00  11/17/2017 11:03:20
20171117 11:03:21  apply lag    +00 00:44:20  11/17/2017 11:03:20
看日志我們是 10:19 切換成，大致是44分鐘。

9、切回 PHYSICAL STADNBY
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
......
Database mounted.
SQL> alter database convert to physical standby;
Database altered.

10、切換完成后，發現閃回日志恢復完成，同時自動刪除閃回日志，日志如下：
Fri Nov 17 11:12:58 2017
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (pritest)
Killing 3 processes with pids 3489,3491,3493 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 3487
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 2253336566 (0x864f2ff6)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Shutting down archive processes
Archiving is disabled
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC3: Archival stopped
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC2: Archival stopped
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC1: Archival stopped
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC0: Archival stopped
Completed: alter database convert to physical standby

11、開啟同步主庫
SQL> startup mount force
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size      2252664 bytes
Variable Size    754974856 bytes
Database Buffers   419430400 bytes
Redo Buffers      9195520 bytes
Database mounted.
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE  DB_UNIQUE_NAME   OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY stdtest   MOUNTED

SQL> alter database open read only;
Database altered.

SQL>  alter database recover managed standby database using current logfile disconnect from session;
Database altered.

12、驗證是否恢復到最初切換前

SQL> select database_role,db_unique_name,open_mode from v$database;

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

SQL> select * from ee;
no rows selected

SQL> conn test/test123
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

關于ORACLE中主從中斷后如何恢復就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。