【DATAGUARD】物理dg在主庫丟失歸檔檔案的情況下的恢復(七)
【DATAGUARD】物理dg在主庫丟失歸檔檔案的情況下的恢復(七)
一.1 BLOG文件結構圖
一.2 前言部分
一.2.1 導讀
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 物理dg的在主庫丟失歸檔檔案的情況下的恢復
② 物理dg管理和維護的一些sql
注意：本篇BLOG中程式碼部分需要特別關注的地方我都用黃色背景和紅色字型來表示，比如下邊的例子中，thread 1的最大歸檔日誌號為33，thread 2的最大歸檔日誌號為43是需要特別關注的地方。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
一.2.2 實驗環境介紹
|
專案 |
主庫 |
dg庫 |
|
db 型別 |
單例項 |
單例項 |
|
db version |
11.2.0.3 |
11.2.0.3 |
|
db 儲存 |
FS type |
FS type |
|
ORACLE_SID |
oradg11g |
oradgphy |
|
db_name |
oradg11g |
oradg11g |
|
主機IP地址： |
192.168.59.130 |
192.168.59.130 |
|
OS版本及kernel版本 |
RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
|
OS hostname |
rhel6_lhr |
rhel6_lhr |
一.2.3 相關參考文章連結
dg的系列文章參考：
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）： http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二 ): http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（三 ): http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (四)--新增一個物理dg節點 ：http://blog.itpub.net/26736162/viewspace-1484878/
【DATAGUARD】物理dg的switchover切換（五） ：http://blog.itpub.net/26736162/viewspace-1753111/
【DATAGUARD】物理dg的failover切換(六)： http://blog.itpub.net/26736162/viewspace-1753130/
一.2.4 本文簡介
最近由於合同到期，去面試了幾家做oracle dba的工作，面試中出現了不少的問題，有的面試官太奇葩了，問的問題也比較難以回答，可憐我的表達能力不太好，俗話就是說不會忽悠人，因此面試連連碰壁，這也是哥的軟肋，本來哥的技術已經很好的了，一般的DBA的活一點問題都沒有，可面試就是不能通過，最近真的是身心俱創，不說了，說多了都是淚，這篇blog是基於我去1號店面試的時候面試官提的一個問題，當時隱約覺得有什麼辦法可以恢復，但是想不起來，結果就回答只能重建了，回來後搜了搜資料還是可以恢復的，趁著週末就實驗了一番，今天貼出來給大家共享共享。
一.3 相關知識點掃盲
都是DG的一些基本維護知識，這裡就不貼了，直接進入實驗環節吧。
Using RMAN Incremental Backups to Refresh a Standby Database
You can create an incremental backup of the target database containing changes to the database since the creation of the duplicate or the previous syncrhonization. You can apply the incremental backup to the standby database.
Note:
This technique cannot be used to update a duplicate database.
RMAN enables you to synchronize a standby database with a primary database by creating an incremental backup at the source database that contains all changed blocks since the duplicate was created or last refreshed. You then apply the incremental backup to the standby database, which updates it with all changes.
This capability facilitates the temporary conversion of a physical standby database into a reporting database, as described in Oracle Data Guard Concepts and Administration. In particular, this capability makes it possible to reverse the effects of converting the standby into a reporting database. After the standby database has been used for reporting or testing, Flashback Database can reverse any changes resulting from that work, returning the database to its contents when it was still a standby. An incremental backup created with BACKUP INCREMENTAL... FROM SCN can be used to refresh the standby with changes at the primary since the conversion and then managed recovery can resume. The effect is to return the reporting database to its role as standby.
For more details on this scenario, see Oracle Data Guard Concepts and Administration.
Using BACKUP INCREMENTAL... FROM SCN
The incremental backup is created at the source database by means of the BACKUP INCREMENTAL FROM SCN=n form of the BACKUP command. For example:
BACKUP DEVICE TYPE SBT INCREMENTAL FROM SCN 750923 DATABASE;
BACKUP INCREMENTAL FROM SCN 750923 DATABASE;
BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 750983 DATABASE
FORMAT '/tmp/incr_standby_%U';
RMAN uses the selected SCN as the basis for this incremental backup. For all files being backed up, RMAN includes all data blocks that were changed at SCNs greater than or equal to the FROM SCN in the incremental backup.
Note:
· RMAN does not consider the incremental backup as part of a backup strategy at the source database. The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database.
· The backup sets produced by this command are written to ?/dbs by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.
· You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby, you must catalog it at the standby as described in "Step 3: Catalog the Incremental Backup Files at the Standby Database". Backups on tape cannot be cataloged.
See Also:
《Oracle Database Backup and Recovery Reference 》 for more details on BACKUP command syntax
Refreshing a Standby Database With INCREMENTAL FROM SCN Backups: Example
This example shows the steps required to update a standby database using incremental backups. The assumption is that you have already activated the standby, performed your tests or other operations at the standby, and then used Flashback Database to undo the effects of those changes. The task here is to refresh the standby with the latest changes to the primary, so that it can resume its role as a standby database.
Step 1: Create the Incremental Backup
Create the needed incremental backup at the source database, using BACKUP with the INCREMENTAL FROM SCN clause.
Assume that the incremental backup to be used in updating the duplicate database is to be created on disk, with the filenames for backup pieces determined by the format /tmp/incr_for_standby/bkup_%U.
RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 750983 DATABASE
FORMAT '/tmp/incr_for_standby/bkup_%U';
Step 2: Make the Incremental Backup Accessible at the Standby Database
Make the backup pieces containing the incremental backup available in some directory accessible on the system containing the standby database. For this example, assume that the destination directory is called /standbydisk1/incrback/ and ensure that it contains nothing besides the incremental backups from Step 1.
Step 3: Catalog the Incremental Backup Files at the Standby Database
Use the RMAN CATALOG command to register the backup sets in the RMAN repository at the duplicate. With an RMAN client connected to the standby database and the recovery catalog (if you use one at the standby), mount the standby and run the following command:
RMAN> CATALOG START WITH '/standbydisk1/incrback/';
The backups are now available for use in recovery of the standby.
Step 4: Apply the Incremental Backup to the Standby Database
Use the RMAN RECOVER command with the NOREDO option to apply the incremental backup to the standby database. All changed blocks captured in the incremental backup are updated at the standby database, bringing it up to date with the primary database. With an RMAN client connected to the standby database, run the following command:
RMAN> RECOVER DATABASE NOREDO;
You can now resume managed recovery at the standby. Any redo logs required at the standby with changes since those contained in the incremental are automatically requested from the primary and applied
一.4 實驗部分
一.4.1 實驗目標
① 主庫丟失歸檔檔案，然後在不重建物理dg的情況下來恢復物理dg
一.4.2 實驗過程
一.4.2.1 主備庫環境
主庫：
20:39:41 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2240299 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
已用時間: 00: 00: 00.01
20:39:42 SQL>
20:42:29 SQL> archive log list;
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列 47
下一個存檔日誌序列 49
當前日誌序列 49
20:43:02 SQL>
備庫：
20:40:39 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2240295 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用時間: 00: 00: 00.06
20:40:44 SQL>
20:42:23 SQL> archive log list;
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列 47
下一個存檔日誌序列 0
當前日誌序列 49
20:43:23 SQL>
一.4.2.2 模擬歸檔丟失
備庫操作，備庫取消歸檔應用，讓備庫處於只讀模式：
20:43:23 SQL> ALTER DATABASE recover managed standby DATABASE cancel;
資料庫已更改。
已用時間: 00: 00: 01.00
20:44:39 SQL>
20:44:39 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- --------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2240536 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY NOT ALLOWED
已用時間: 00: 00: 00.00
20:45:09 SQL>
主庫配置歸檔2的狀態為defer，目的是為了不把歸檔自動傳遞到備庫,實際情況下往往是由於網路故障，備庫掛掉等等情況導致，我們多次切換主庫日誌：
20:50:48 SQL> ALTER system SET log_archive_dest_state_2 = 'defer';
系統已更改。
已用時間: 00: 00: 00.01
20:52:31 SQL>
20:52:31 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.01
20:54:54 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.03
20:54:56 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.01
20:54:57 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.01
20:55:05 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.00
20:55:45 SQL> create table lhr.testdg as select * from dual;
表已建立。
已用時間: 00: 00: 00.10
20:55:49 SQL> insert into lhr.testdg select * from dual;
已建立 1 行。
已用時間: 00: 00: 00.01
20:56:10 SQL> commit;
提交完成。
已用時間: 00: 00: 00.00
20:56:43 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.01
20:56:52 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.01
20:56:56 SQL> insert into lhr.testdg select * from dual;
已建立 1 行。
已用時間: 00: 00: 00.00
20:57:07 SQL> commit;
提交完成。
已用時間: 00: 00: 00.00
20:57:11 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 01.57
20:57:15 SQL>
20:57:15 SQL> select * from lhr.testdg;
D
-
X
X
X
已用時間: 00: 00: 00.00
20:58:00 SQL>
20:58:00 SQL> archive log list;
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列 55
下一個存檔日誌序列 57
當前日誌序列 57
20:58:30 SQL>
檢視主庫歸檔情況：
20:58:30 SQL> col name for a100
20:58:55 SQL> set linesize 9999 pagesize 9999
20:58:55 SQL> SELECT dest_id,
20:58:55 2 THREAD#,
20:58:55 3 NAME,
sequence#,
archived,
applied,
a.NEXT_CHANGE#
FROM v$archived_log a
WHERE a.sequence# >= 40
AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
and a.dest_id=1
ORDER BY a.THREAD#,
a.sequence#,
20:58:55 14 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ------------
1 1 40 YES NO 2181533
1 1 41 YES NO 2181856
1 1 42 YES NO 2182794
1 1 43 YES NO 2182842
1 1 44 YES NO 2223480
1 1 45 YES NO 2223488
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_46_bxm58pvo_.arc 46 YES NO 2224321
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_47_bxmc8z90_.arc 47 YES NO 2234639
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_48_bxmc917l_.arc 48 YES NO 2234642
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_49_bxmjnyoh_.arc 49 YES NO 2241189
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_50_bxmjo0gk_.arc 50 YES NO 2241194
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_51_bxmjo1vw_.arc 51 YES NO 2241198
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_52_bxmjo9pw_.arc 52 YES NO 2241209
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_53_bxmjocqc_.arc 53 YES NO 2241214
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_54_bxmjrnt2_.arc 54 YES NO 2241390
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_55_bxmjrrbl_.arc 55 YES NO 2241396
1 1 /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23/o1_mf_1_56_bxmjscy0_.arc 56 YES NO 2241419
已選擇17行。
已用時間: 00: 00: 00.00
20:58:56 SQL>
檢視備庫歸檔情況：
20:59:04 SQL> col name for a100
21:00:45 SQL> set linesize 9999 pagesize 9999
21:00:45 SQL> SELECT dest_id,
21:00:45 2 THREAD#,
21:00:45 3 NAME,
21:00:45 4 sequence#,
21:00:45 5 archived,
21:00:45 6 applied,
21:00:45 7 a.NEXT_CHANGE#
21:00:45 8 FROM v$archived_log a
21:00:45 9 WHERE a.sequence# >= 45
21:00:45 10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
21:00:45 11 and a.dest_id=1
21:00:45 12 ORDER BY a.THREAD#,
21:00:45 13 a.sequence#,
21:00:45 14 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ------------
1 1 46 YES YES 2224321
1 1 47 YES YES 2234639
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_48_bxmc9189_.arc 48 YES YES 2234642
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_49_bxmjnyj3_.arc 49 YES NO 2241189
已用時間: 00: 00: 00.01
21:00:46 SQL>
可以看到，備庫已經斷檔了，50到56都沒有接收，接下來我們刪除主庫的歸檔日誌，我們只刪除54、55這2個歸檔日誌：
[oracle@rhel6_lhr ~]$ cd /u01/app/oracle/flash_recovery_area/ORADG11G/archivelog/2015_08_23
[oracle@rhel6_lhr 2015_08_23]$ ls
o1_mf_1_46_bxm58pvo_.arc o1_mf_1_48_bxmc917l_.arc o1_mf_1_50_bxmjo0gk_.arc o1_mf_1_52_bxmjo9pw_.arc o1_mf_1_54_bxmjrnt2_.arc o1_mf_1_56_bxmjscy0_.arc
o1_mf_1_47_bxmc8z90_.arc o1_mf_1_49_bxmjnyoh_.arc o1_mf_1_51_bxmjo1vw_.arc o1_mf_1_53_bxmjocqc_.arc o1_mf_1_55_bxmjrrbl_.arc
[oracle@rhel6_lhr 2015_08_23]$ ll
total 23624
-rw-r----- 1 oracle asmadmin 422400 Aug 23 17:40 o1_mf_1_46_bxm58pvo_.arc
-rw-r----- 1 oracle asmadmin 17354240 Aug 23 19:23 o1_mf_1_47_bxmc8z90_.arc
-rw-r----- 1 oracle asmadmin 1536 Aug 23 19:23 o1_mf_1_48_bxmc917l_.arc
-rw-r----- 1 oracle asmadmin 6266368 Aug 23 20:54 o1_mf_1_49_bxmjnyoh_.arc
-rw-r----- 1 oracle asmadmin 2048 Aug 23 20:54 o1_mf_1_50_bxmjo0gk_.arc
-rw-r----- 1 oracle asmadmin 1536 Aug 23 20:54 o1_mf_1_51_bxmjo1vw_.arc
-rw-r----- 1 oracle asmadmin 5120 Aug 23 20:55 o1_mf_1_52_bxmjo9pw_.arc
-rw-r----- 1 oracle asmadmin 2048 Aug 23 20:55 o1_mf_1_53_bxmjocqc_.arc
-rw-r----- 1 oracle asmadmin 96256 Aug 23 20:56 o1_mf_1_54_bxmjrnt2_.arc
-rw-r----- 1 oracle asmadmin 2560 Aug 23 20:56 o1_mf_1_55_bxmjrrbl_.arc
-rw-r----- 1 oracle asmadmin 12800 Aug 23 20:57 o1_mf_1_56_bxmjscy0_.arc
[oracle@rhel6_lhr 2015_08_23]$ rm -rf o1_mf_1_54*
[oracle@rhel6_lhr 2015_08_23]$ rm -rf o1_mf_1_55*
You have new mail in /var/spool/mail/oracle
[oracle@rhel6_lhr 2015_08_23]$ ll
total 23524
-rw-r----- 1 oracle asmadmin 422400 Aug 23 17:40 o1_mf_1_46_bxm58pvo_.arc
-rw-r----- 1 oracle asmadmin 17354240 Aug 23 19:23 o1_mf_1_47_bxmc8z90_.arc
-rw-r----- 1 oracle asmadmin 1536 Aug 23 19:23 o1_mf_1_48_bxmc917l_.arc
-rw-r----- 1 oracle asmadmin 6266368 Aug 23 20:54 o1_mf_1_49_bxmjnyoh_.arc
-rw-r----- 1 oracle asmadmin 2048 Aug 23 20:54 o1_mf_1_50_bxmjo0gk_.arc
-rw-r----- 1 oracle asmadmin 1536 Aug 23 20:54 o1_mf_1_51_bxmjo1vw_.arc
-rw-r----- 1 oracle asmadmin 5120 Aug 23 20:55 o1_mf_1_52_bxmjo9pw_.arc
-rw-r----- 1 oracle asmadmin 2048 Aug 23 20:55 o1_mf_1_53_bxmjocqc_.arc
-rw-r----- 1 oracle asmadmin 12800 Aug 23 20:57 o1_mf_1_56_bxmjscy0_.arc
[oracle@rhel6_lhr 2015_08_23]$
主庫開啟備庫的歸檔：
21:05:44 SQL> ALTER system SET log_archive_dest_state_2 = 'enable';
系統已更改。
已用時間: 00: 00: 00.00
21:19:46 SQL>
備庫開啟實時應用：
21:00:46 SQL> alter database recover managed standby database using current logfile disconnect from session;
資料庫已更改。
已用時間: 00: 00: 06.02
21:22:17 SQL>
此時備庫告警日誌：
Sun Aug 23 21:22:16 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_49_bxmjnyj3_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_50_bxml3lv7_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_51_bxml3lrh_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_52_bxml3lqv_.arc
Media Recovery Log /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_53_bxml3lz7_.arc
Media Recovery Waiting for thread 1 sequence 54
Fetching gap sequence in thread 1, gap sequence 54-55
Completed: alter database recover managed standby database using current logfile disconnect from session
Sun Aug 23 21:25:17 2015
Error 12154 received logging on to the standby
FAL[client, USER]: Error 12154 connecting to oradg11g for fetching gap sequence
Sun Aug 23 21:57:57 2015
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 54-55
DBID 1403587593 branch 886695024
FAL[client]: All defined FAL servers have been attempted.
------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that's sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
------------------------------------------------------------
再次檢視備庫歸檔情況：
21:34:58 SQL>
21:36:10 SQL> col name for a100
21:37:40 SQL> set linesize 9999 pagesize 9999
21:37:40 SQL> SELECT dest_id,
21:37:40 2 THREAD#,
21:37:40 3 NAME,
21:37:40 4 sequence#,
21:37:40 5 archived,
21:37:41 6 applied,
21:37:41 7 a.NEXT_CHANGE#
21:37:41 8 FROM v$archived_log a
21:37:41 9 WHERE a.sequence# >= 45
21:37:41 10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
21:37:41 11 ORDER BY a.THREAD#,
21:37:41 12 a.sequence#,
21:37:41 13 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ------------
2 1 45 YES YES 2223488
1 1 46 YES YES 2224321
1 1 47 YES YES 2234639
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_48_bxmc9189_.arc 48 YES YES 2234642
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_49_bxmjnyj3_.arc 49 YES YES 2241189
2 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_50_bxml3lv7_.arc 50 YES YES 2241194
2 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_51_bxml3lrh_.arc 51 YES YES 2241198
2 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_52_bxml3lqv_.arc 52 YES YES 2241209
2 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_53_bxml3lz7_.arc 53 YES YES 2241214
2 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_56_bxml3lz0_.arc 56 YES NO 2241419
2 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_57_bxml3m03_.arc 57 YES NO 2243353
已選擇11行。
已用時間: 00: 00: 00.01
21:37:41 SQL>
21:40:35 SQL> select thread#,low_sequence#,high_sequence# from v$archive_gap;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 54 55
已用時間: 00: 00: 00.01
21:50:38 SQL>
可以看到備庫已經產生gap了。
一.4.2.3 主庫基於SCN備份
54、55號日誌不見了，這個時候我們以53號的歸檔日誌的next_change#即54號的first_change#為scn號來對主庫基於scn的rman增量備份。
SELECT (SELECT MIN(d.CHECKPOINT_CHANGE#) FROM v$datafile d) datafile_scn,
(SELECT MIN(d.CHECKPOINT_CHANGE#)
FROM v$datafile_header d
WHERE rownum = 1) datafile_header_scn,
(SELECT current_scn FROM v$database) current_scn,
(SELECT b.NEXT_CHANGE#
FROM v$archived_log b
WHERE b.SEQUENCE# = 53
AND resetlogs_change# =
(SELECT d.RESETLOGS_CHANGE# FROM v$database d)
AND rownum = 1) NEXT_CHANGE#
FROM dual;
這幾個值基本上差不多，我們可以以 2241214 或者2241213為基準來備份，若是資料檔案和檔案頭的scn不一致我們應該取這幾個值中最小的一個。
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup as compressed backupset incremental from SCN 2241214 database format '/u05/oracle/oracle_bk/ORADG11G/standby_%d_%T_%U.bak' include current controlfile for standby filesperset=5 tag 'FOR STANDBY';
release channel d1;
release channel d2;
}
[oracle@rhel6_lhr ~]$ rman target /
恢復管理器: Release 11.2.0.3.0 - Production on 星期日 8月 23 21:55:49 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已連線到目標資料庫: ORADG11G (DBID=1403587593)
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> backup as compressed backupset incremental from SCN 2241214 database format '/u05/oracle/oracle_bk/ORADG11G/standby_%d_%T_%U.bak' include current controlfile for standby filesperset=5 tag 'FOR STANDBY';
6> release channel d1;
7> release channel d2;
8> }
使用目標資料庫控制檔案替代恢復目錄
分配的通道: d1
通道 d1: SID=145 裝置型別=DISK
分配的通道: d2
通道 d2: SID=19 裝置型別=DISK
啟動 backup 於 2015-08-23 21:55:54
備份將於 2015-08-30 21:55:54 廢棄
將不保留或備份歸檔日誌
通道 d1: 正在啟動壓縮的全部資料檔案備份集
通道 d1: 正在指定備份集內的資料檔案
輸入資料檔案: 檔案號=00001 名稱=/u01/app/oracle/oradata/oradg11g/system01.dbf
輸入資料檔案: 檔案號=00003 名稱=/u01/app/oracle/oradata/oradg11g/undotbs01.dbf
輸入資料檔案: 檔案號=00005 名稱=/u01/app/oracle/oradata/oradg11g/example01.dbf
通道 d1: 正在啟動段 1 於 2015-08-23 21:55:54
通道 d2: 正在啟動壓縮的全部資料檔案備份集
通道 d2: 正在指定備份集內的資料檔案
輸入資料檔案: 檔案號=00002 名稱=/u01/app/oracle/oradata/oradg11g/sysaux01.dbf
輸入資料檔案: 檔案號=00006 名稱=/u01/app/oracle/oradata/oradg11g/logmnrtbs1.dbf
輸入資料檔案: 檔案號=00004 名稱=/u01/app/oracle/oradata/oradg11g/users01.dbf
通道 d2: 正在啟動段 1 於 2015-08-23 21:55:54
通道 d2: 已完成段 1 於 2015-08-23 22:00:00
段控制程式碼=/u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2sqfbp7a_1_1.bak 標記=FOR STANDBY 註釋=NONE
通道 d2: 備份集已完成, 經過時間:00:04:06
通道 d2: 正在啟動壓縮的全部資料檔案備份集
通道 d2: 正在指定備份集內的資料檔案
備份集內包括備用控制檔案
通道 d2: 正在啟動段 1 於 2015-08-23 22:00:03
通道 d2: 已完成段 1 於 2015-08-23 22:00:04
段控制程式碼=/u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2tqfbpf0_1_1.bak 標記=FOR STANDBY 註釋=NONE
通道 d2: 備份集已完成, 經過時間:00:00:01
通道 d1: 已完成段 1 於 2015-08-23 22:00:54
段控制程式碼=/u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2rqfbp7a_1_1.bak 標記=FOR STANDBY 註釋=NONE
通道 d1: 備份集已完成, 經過時間:00:05:00
備份將於 2015-08-30 22:00:54 廢棄
將不保留或備份歸檔日誌
通道 d1: 正在啟動壓縮的全部資料檔案備份集
通道 d1: 正在指定備份集內的資料檔案
備份集內包括當前控制檔案
通道 d1: 正在啟動段 1 於 2015-08-23 22:00:55
通道 d1: 已完成段 1 於 2015-08-23 22:00:56
段控制程式碼=/u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2uqfbpgm_1_1.bak 標記=FOR STANDBY 註釋=NONE
通道 d1: 備份集已完成, 經過時間:00:00:01
完成 backup 於 2015-08-23 22:00:56
釋放的通道: d1
釋放的通道: d2
RMAN>
RMAN>
RMAN> list backupset summary;
備份列表
===============
關鍵字 TY LV S 裝置型別 完成時間 段數 副本數 壓縮標記
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
9 B A DISK 2015-08-23 21:59:53 1 1 YES FOR STANDBY
10 B A DISK 2015-08-23 22:00:04 1 1 YES FOR STANDBY
11 B A DISK 2015-08-23 22:00:49 1 1 YES FOR STANDBY
12 B A DISK 2015-08-23 22:00:55 1 1 YES FOR STANDBY
RMAN> exit
恢復管理器完成。
[oracle@rhel6_lhr ~]$ ll -h /u05/oracle/oracle_bk/ORADG11G/*
-rw-r----- 1 oracle asmadmin 1.2M Aug 23 22:00 /u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2rqfbp7a_1_1.bak
-rw-r----- 1 oracle asmadmin 1.4M Aug 23 21:59 /u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2sqfbp7a_1_1.bak
-rw-r----- 1 oracle asmadmin 1.1M Aug 23 22:00 /u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2tqfbpf0_1_1.bak
-rw-r----- 1 oracle asmadmin 1.1M Aug 23 22:00 /u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2uqfbpgm_1_1.bak
[oracle@rhel6_lhr ~]$
可以看到增量備份成功，接下來將備份傳遞到備庫，可以用scp或者ftp工具， scp * oracle@192.168.213.101:/u05/oracle/oracle_bk/ORADG11G/。
[oracle@rhel6_lhr ~]$ scp /u05/oracle/oracle_bk/ORADG11G/* oracle@192.168.59.130:/u05/oracle/
The authenticity of host '192.168.59.130 (192.168.59.130)' can't be established.
RSA key fingerprint is 77:e6:11:1a:7c:c7:81:7c:88:c9:21:18:51:2a:84:d1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.59.130' (RSA) to the list of known hosts.
oracle@192.168.59.130's password:
standby_ORADG11G_20150823_2rqfbp7a_1_1.bak 100% 1144KB 1.1MB/s 00:00
standby_ORADG11G_20150823_2sqfbp7a_1_1.bak 100% 1376KB 1.3MB/s 00:00
standby_ORADG11G_20150823_2tqfbpf0_1_1.bak 100% 1104KB 1.1MB/s 00:00
standby_ORADG11G_20150823_2uqfbpgm_1_1.bak 100% 1120KB 1.1MB/s 00:00
You have new mail in /var/spool/mail/oracle
[oracle@rhel6_lhr ~]$
一.4.2.4 備庫執行恢復操作
一、 重啟備庫到nomount狀態來恢復控制檔案
22:09:23 SQL> set line 9999
22:10:26 SQL> col name format a10
22:10:26 SQL> select dbid,name,current_scn,protection_mode,protection_level,database_role,force_logging,open_mode,switchover_status from v$database;
DBID NAME CURRENT_SCN PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE FOR OPEN_MODE SWITCHOVER_STATUS
---------- ---------- ----------- -------------------- -------------------- ---------------- --- -------------------- --------------------
1403587593 ORADG11G 2241213 MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
已用時間: 00: 00: 00.01
22:11:41 SQL> create pfile='?/dbs/standby_pfile_before_recover_dg.ora' from spfile;
檔案已建立。
已用時間: 00: 00: 00.01
22:12:29 SQL>
22:12:29 SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
22:13:46 SQL> startup nomount;
ORACLE 例程已經啟動。
Total System Global Area 242171904 bytes
Fixed Size 2227256 bytes
Variable Size 197133256 bytes
Database Buffers 37748736 bytes
Redo Buffers 5062656 bytes
22:13:56 SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/oradgp
hy/crontal01.ctl, /u01/app/ora
cle/oradata/oradgphy/control02
.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
[oracle@rhel6_lhr ~]$ rman target /
恢復管理器: Release 11.2.0.3.0 - Production on 星期日 8月 23 22:30:24 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已連線到目標資料庫: ORADG11G (未裝載)
RMAN> restore standby controlfile to '/u01/app/oracle/oradata/oradgphy/crontal01.ctl' from '/u05/oracle/standby_ORADG11G_20150823_2tqfbpf0_1_1.bak';
啟動 restore 於 2015-08-23 22:30:26
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=134 裝置型別=DISK
通道 ORA_DISK_1: 正在還原控制檔案
通道 ORA_DISK_1: 還原完成, 用時: 00:00:01
完成 restore 於 2015-08-23 22:30:27
RMAN> restore standby controlfile to '/u01/app/oracle/oradata/oradgphy/control02.ctl' from '/u05/oracle/standby_ORADG11G_20150823_2tqfbpf0_1_1.bak';
啟動 restore 於 2015-08-23 22:30:52
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在還原控制檔案
通道 ORA_DISK_1: 還原完成, 用時: 00:00:01
完成 restore 於 2015-08-23 22:30:53
RMAN>
RMAN> catalog start with '/u05/oracle/';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: catalog 命令 (在 08/23/2015 22:33:07 上) 失敗
ORA-01507: 未裝載資料庫
RMAN> alter database mount;
資料庫已裝載
釋放的通道: ORA_DISK_1
RMAN> catalog start with '/u05/oracle/';
搜尋與樣式 /u05/oracle 匹配的所有檔案
資料庫未知檔案的列表
=====================================
檔名: /u05/oracle/standby_ORADG11G_20150823_2rqfbp7a_1_1.bak
檔名: /u05/oracle/standby_ORADG11G_20150823_2sqfbp7a_1_1.bak
檔名: /u05/oracle/standby_ORADG11G_20150823_2uqfbpgm_1_1.bak
檔名: /u05/oracle/standby_ORADG11G_20150823_2tqfbpf0_1_1.bak
是否確實要將上述檔案列入目錄 (輸入 YES 或 NO)? yes
正在編制檔案目錄...
目錄編制完畢
已列入目錄的檔案的列表
=======================
檔名: /u05/oracle/standby_ORADG11G_20150823_2rqfbp7a_1_1.bak
檔名: /u05/oracle/standby_ORADG11G_20150823_2sqfbp7a_1_1.bak
檔名: /u05/oracle/standby_ORADG11G_20150823_2uqfbpgm_1_1.bak
檔名: /u05/oracle/standby_ORADG11G_20150823_2tqfbpf0_1_1.bak
二、 恢復備庫
RMAN> recover DATABASE noredo;
啟動 recover 於 2015-08-23 22:37:12
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在開始還原增量資料檔案備份集
通道 ORA_DISK_1: 正在指定從備份集還原的資料檔案
資料檔案 00002 的還原目標: /u01/app/oracle/oradata/oradgphy/sysaux01.dbf
資料檔案 00004 的還原目標: /u01/app/oracle/oradata/oradgphy/users01.dbf
資料檔案 00006 的還原目標: /u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf
通道 ORA_DISK_1: 正在讀取備份片段 /u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2sqfbp7a_1_1.bak
通道 ORA_DISK_1: 段控制程式碼 = /u05/oracle/oracle_bk/ORADG11G/standby_ORADG11G_20150823_2sqfbp7a_1_1.bak 標記 = FOR STANDBY
通道 ORA_DISK_1: 已還原備份片段 1
通道 ORA_DISK_1: 還原完成, 用時: 00:00:01
通道 ORA_DISK_1: 正在開始還原增量資料檔案備份集
通道 ORA_DISK_1: 正在指定從備份集還原的資料檔案
資料檔案 00001 的還原目標: /u01/app/oracle/oradata/oradgphy/system01.dbf
資料檔案 00003 的還原目標: /u01/app/oracle/oradata/oradgphy/undotbs01.dbf
資料檔案 00005 的還原目標: /u01/app/oracle/oradata/oradgphy/example01.dbf
通道 ORA_DISK_1: 正在讀取備份片段 /u05/oracle/standby_ORADG11G_20150823_2rqfbp7a_1_1.bak
通道 ORA_DISK_1: 段控制程式碼 = /u05/oracle/standby_ORADG11G_20150823_2rqfbp7a_1_1.bak 標記 = FOR STANDBY
通道 ORA_DISK_1: 已還原備份片段 1
通道 ORA_DISK_1: 還原完成, 用時: 00:00:03
完成 recover 於 2015-08-23 22:37:16
RMAN>
告警日誌：
Sun Aug 23 22:37:12 2015
Incremental restore complete of datafile 4 /u01/app/oracle/oradata/oradgphy/users01.dbf
checkpoint is 2245592
last deallocation scn is 3
Incremental restore complete of datafile 6 /u01/app/oracle/oradata/oradgphy/logmnrtbs1.dbf
checkpoint is 2245592
Incremental restore complete of datafile 2 /u01/app/oracle/oradata/oradgphy/sysaux01.dbf
checkpoint is 2245592
last deallocation scn is 995211
Incremental restore complete of datafile 3 /u01/app/oracle/oradata/oradgphy/undotbs01.dbf
checkpoint is 2245591
last deallocation scn is 3
Incremental restore complete of datafile 5 /u01/app/oracle/oradata/oradgphy/example01.dbf
checkpoint is 2245591
last deallocation scn is 1015098
Incremental restore complete of datafile 1 /u01/app/oracle/oradata/oradgphy/system01.dbf
checkpoint is 2245591
last deallocation scn is 993074
三、 備庫開始應用redo
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期日 8月 23 22:39:48 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
22:39:48 SQL> col name for a100
22:40:29 SQL> set linesize 9999 pagesize 9999
22:40:29 SQL> SELECT dest_id,
22:40:29 2 THREAD#,
22:40:29 3 NAME,
22:40:29 4 sequence#,
22:40:29 5 archived,
22:40:29 6 applied,
22:40:29 7 a.NEXT_CHANGE#
22:40:29 8 FROM v$archived_log a
22:40:29 9 WHERE a.sequence# >= 40
22:40:29 10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
22:40:29 11 ORDER BY a.THREAD#,
22:40:29 12 a.sequence#,
22:40:30 13 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ------------
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_48_bxmc9189_.arc 48 YES NO 2234642
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_49_bxmjnyj3_.arc 49 YES NO 2241189
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_50_bxml3lv7_.arc 50 YES NO 2241194
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_51_bxml3lrh_.arc 51 YES NO 2241198
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_52_bxml3lqv_.arc 52 YES NO 2241209
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_53_bxml3lz7_.arc 53 YES NO 2241214
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_56_bxml3lz0_.arc 56 YES NO 2241419
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_57_bxml3m03_.arc 57 YES NO 2243353
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_58_bxmp464x_.arc 58 YES NO 2248351
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_59_bxmpksob_.arc 59 YES NO 2248782
已選擇10行。
已用時間: 00: 00: 00.01
22:40:30 SQL> alter database recover managed standby database using current logfile disconnect from session;
資料庫已更改。
已用時間: 00: 00: 06.02
22:41:19 SQL> col name for a100
22:41:40 SQL> set linesize 9999 pagesize 9999
22:41:40 SQL> SELECT dest_id,
22:41:41 2 THREAD#,
22:41:41 3 NAME,
22:41:41 4 sequence#,
22:41:41 5 archived,
22:41:41 6 applied,
22:41:41 7 a.NEXT_CHANGE#
22:41:41 8 FROM v$archived_log a
22:41:41 9 WHERE a.sequence# >= 40
22:41:41 10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
22:41:41 11 ORDER BY a.THREAD#,
22:41:41 12 a.sequence#,
22:41:41 13 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ------------
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_48_bxmc9189_.arc 48 YES NO 2234642
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_49_bxmjnyj3_.arc 49 YES NO 2241189
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_50_bxml3lv7_.arc 50 YES NO 2241194
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_51_bxml3lrh_.arc 51 YES NO 2241198
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_52_bxml3lqv_.arc 52 YES NO 2241209
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_53_bxml3lz7_.arc 53 YES NO 2241214
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_56_bxml3lz0_.arc 56 YES NO 2241419
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_57_bxml3m03_.arc 57 YES NO 2243353
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_58_bxmp464x_.arc 58 YES YES 2248351
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_59_bxmpksob_.arc 59 YES IN-MEMORY 2248782
已選擇10行。
已用時間: 00: 00: 00.00
22:41:41 SQL>
22:41:41 SQL> SELECT * FROM V$ARCHIVE_GAP;
未選定行
已用時間: 00: 00: 00.01
22:41:41 SQL>
四、 備庫read only 模式開啟
22:41:41 SQL> alter database recover managed standby database cancel;
資料庫已更改。
已用時間: 00: 00: 01.00
22:43:40 SQL> alter database open;
資料庫已更改。
已用時間: 00: 00: 00.22
22:43:45 SQL> alter database recover managed standby database using current logfile disconnect from session;
資料庫已更改。
已用時間: 00: 00: 06.04
22:44:00 SQL> 22:44:00 SQL>
22:44:02 SQL> select * from lhr.testdg;
D
-
X
X
X
已用時間: 00: 00: 00.00
22:44:10 SQL>
一.4.2.5 校驗操作
主庫：
22:41:00 SQL> alter system switch logfile;
系統已更改。
已用時間: 00: 00: 00.01
22:45:14 SQL> insert into lhr.testdg select * from lhr.testdg;
已建立 3 行。
已用時間: 00: 00: 00.00
22:45:29 SQL> commit;
提交完成。
已用時間: 00: 00: 00.01
22:45:32 SQL> select count(1) from lhr.testdg;
COUNT(1)
----------
6
已用時間: 00: 00: 00.00
22:45:42 SQL> archive log list;
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列 59
下一個存檔日誌序列 61
當前日誌序列 61
22:46:03 SQL>
備庫：
22:46:54 SQL> col name for a100
22:47:13 SQL> set linesize 9999 pagesize 9999
22:47:13 SQL> SELECT dest_id,
22:47:13 2 THREAD#,
22:47:13 3 NAME,
22:47:13 4 sequence#,
22:47:13 5 archived,
22:47:13 6 applied,
22:47:13 7 a.NEXT_CHANGE#
22:47:13 8 FROM v$archived_log a
22:47:13 9 WHERE a.sequence# >= 50
22:47:13 10 AND resetlogs_change# = (SELECT d.RESETLOGS_CHANGE# FROM v$database d)
22:47:13 11 ORDER BY a.THREAD#,
22:47:13 12 a.sequence#,
22:47:13 13 a.dest_id;
DEST_ID THREAD# NAME SEQUENCE# ARC APPLIED NEXT_CHANGE#
---------- ---------- ---------------------------------------------------------------------------------------------------- ---------- --- --------- ------------
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_50_bxml3lv7_.arc 50 YES NO 2241194
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_51_bxml3lrh_.arc 51 YES NO 2241198
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_52_bxml3lqv_.arc 52 YES NO 2241209
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_53_bxml3lz7_.arc 53 YES NO 2241214
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_56_bxml3lz0_.arc 56 YES NO 2241419
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_57_bxml3m03_.arc 57 YES NO 2243353
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_58_bxmp464x_.arc 58 YES YES 2248351
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_59_bxmpksob_.arc 59 YES YES 2248782
1 1 /u01/app/oracle/flash_recovery_area/ORADGPHY/archivelog/2015_08_23/o1_mf_1_60_bxmq3tn7_.arc 60 YES IN-MEMORY 2249391
已選擇9行。
已用時間: 00: 00: 00.00
22:47:13 SQL>
可以看到主備庫正常。
一.4.2.6 刪除備庫
如果備份用不到了，則現在可以刪除
RMAN> DELETE BACKUP TAG 'FOR STANDBY';
一.4.3 實驗總結
最後，我們可以看到，在主庫archivelog丟失無法同步到備庫時，可以利用增量scn的方式，來避免重建standby，千萬不要以為結束了，既然丟失了歸檔，資料庫還是進行一次全備吧。
一.5 總結
進行Dataguard 的維護非常常見的運維需求，在實際場景下，我們儘可能選擇穩妥完全的策略進行操作，保證資料不丟失。
一.6 About Me
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
ITPUB BLOG：http://blog.itpub.net/26736162
本文地址：http://blog.itpub.net/26736162/viewspace-1780863/
本文pdf版：http://yunpan.cn/QCwUAI9bn7g7w 提取碼：af2d
QQ：642808185 若加QQ請註明你所正在讀的文章標題
創作時間地點：2015-08-23 09:00~ 2015-08-23 23:00 於唐鎮金唐公寓宿舍
<版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1780863/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【故障處理】DG環境主庫丟失歸檔情況下資料檔案的恢復
- 【BBED】丟失歸檔檔案情況下的恢復
- DG 主庫丟失歸檔
- 在歸檔模式下丟失日誌檔案的恢復模式
- Oracle主庫歸檔丟失，備庫日誌有gap，在不重建備庫的情況下，恢復備庫Oracle
- dataguard 歸檔丟失,備庫基於SCN恢復
- 歸檔模式下資料檔案丟失的恢復模式
- oracle dg 歸檔日誌恢復情況Oracle
- Oracle控制檔案在缺失歸檔日誌的情況下的恢復Oracle
- 恢復案例：歸檔模式下丟失全部資料檔案的恢復模式
- dg 主庫丟失歸檔 解決方案
- 丟失已歸檔日誌檔案下恢復資料庫資料庫
- 測試在丟失歸檔日誌的情況下，跳過部分歸檔日誌進行資料恢復資料恢復
- 非歸檔丟失日誌檔案的恢復
- 非歸檔無備份下控制檔案丟失的恢復
- Oracle 10g DG 主庫丟失歸檔Oracle 10g
- dg丟失歸檔，使用rman增量備份恢復
- 利用增量備份恢復gap歸檔丟失DG
- 恢復之利用備份在所有控制檔案丟失情況下恢復（四）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（三）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（二）
- 恢復之利用備份在所有控制檔案丟失情況下恢復（一）
- REDO檔案丟失的恢復__沒有任何備份的情況
- windows重灌/資料庫物理檔案完好的情況下恢復資料庫Windows資料庫
- 歸檔模式下丟失普通資料檔案並恢復模式
- 沒有自動備份的情況下控制檔案全部丟失的恢復
- dataguard之物理備庫丟失資料檔案
- 非歸檔模式下非當前日誌檔案的丟失的恢復模式
- 誤刪資料檔案在沒有歸檔的情況下恢復實驗
- undo表空間檔案丟失恢復（2）--無備份有redo的情況下恢復
- undo表空間檔案丟失恢復（3）--無備份無redo的情況下恢復
- undo表空間檔案丟失恢復（4）--無備份無recover的情況下恢復
- 利用增量備份恢復因歸檔丟失造成的DG gap
- [Dataguard]主庫歸檔丟失，備庫不需重建實驗
- DATA GUARD主庫丟失資料檔案的恢復(2)
- DATA GUARD主庫丟失資料檔案的恢復(3)
- DATA GUARD主庫丟失資料檔案的恢復(1)
- rman恢復--歸檔模式有備份，丟失資料檔案的恢復模式