dataguard中需要注意的一些資料檔案操作

因為最近需要做一個測試，就順手搭建了一套簡單的dg環境。不過碰到了一些小問題。
資料庫環境是11gR2，備庫是開在open狀態，配置了dg broker,一切都很快完成了。備庫狀態為"READ ONLY WITH APPLY"當然這是期望之中ADG的狀態。
然後在主庫需要做一些配置，準備建立幾個表空間
先建立了一個表空間
 create tablespace testdata datafile '/DATA/app/oracle/oradata/test04/testdata01.dbf' size 100M;
然後檢視備庫，狀態就變成"READ ONLY"了，意味著MRP似乎有些問題了。
檢視日誌，就發現了下面的一些內容：
Media Recovery Waiting for thread 1 sequence 55 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 55 Reading mem 0
  Mem# 0: /home/U01/app/oracle/oradata/test04/redo04.log
File #5 added to control file as 'UNNAMED00005' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
MRP0: Background Media Recovery terminated with error 1274
Errors in file /home/U01/app/oracle/diag/rdbms/stest04/test04/trace/test04_pr00_8049.trc:
ORA-01274: cannot add datafile '/DATA/app/oracle/oradata/test04/testdata01.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 2016680
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
MRP0: Background Media Recovery process shutdown (test04)
在這個場景中，因為主備庫的路徑是不一致的，做了對映，那麼在主庫建立資料檔案的時候，備庫建立失敗，主要原因就是備庫檔案管理是使用了手工方式（STANDBY_FILE_MANAGEMENT=MANUAL)
當然這個問題比較簡單了。我們就從這裡開始說說一些額外的分析。
我們先修復這個小問題，思路就是設定STANBY_FILE_MANAGEMENT=AUTO,然後開啟MRP。
當然也不是一帆風順。日誌如下：
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;
Fri Feb 26 13:05:15 2016
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (test04)
Fri Feb 26 13:05:15 2016
MRP0 started with pid=29, OS id=8103
MRP0: Background Managed Standby Recovery process started (test04)
 started logmerger process
Fri Feb 26 13:05:20 2016
Managed Standby Recovery starting Real Time Apply
Fri Feb 26 13:05:20 2016
Errors in file /home/U01/app/oracle/diag/rdbms/stest04/test04/trace/test04_dbw0_7900.trc:
ORA-01186: file 5 failed verification tests
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00005'
File 5 not verified due to error ORA-01157
MRP0: Background Media Recovery terminated with error 1111
Errors in file /home/U01/app/oracle/diag/rdbms/stest04/test04/trace/test04_pr00_8105.trc:
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00005'
Managed Standby Recovery not using Real Time Apply
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (test04)
看日誌發現是$ORACLE_HOME/dbs下生成了一個相應的檔案控制程式碼，實際上檔案還不存在。
[oracle@testdb2 trace]$ ls -l /home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00005
ls: cannot access /home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00005: No such file or directory
這個時候可以嘗試使用create datafile的方式來修復。
ALTER DATABASE CREATE DATAFILE  '/home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00007'  AS  '/home/U01/app/oracle/oradata/test04/testdata02.dbf'
*
ERROR at line 1:
ORA-01275: Operation CREATE DATAFILE is not allowed if standby file management
is automatic.
不過從錯誤來看這個還是需要在manual模式下使用，也是合情合理的。繼續修復。
SQL> alter system set standby_file_management=manual;
System altered.
SQL> ALTER DATABASE CREATE DATAFILE  '/home/U01/app/oracle/product/11.2.0.4/dbs/UNNAMED00007'  AS  '/home/U01/app/oracle/oradata/test04/testdata02.dbf';
Database altered.
然後開啟MRP的日誌應用
SQL> recover managed standby database disconnect from session using current logfile;
Media recovery complete.
再次檢視這個新的資料檔案就同步過來了。
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
.....
/home/U01/app/oracle/oradata/test04/testdata02.dbf
當然修復之後還是設定備庫檔案管理模式為auto吧。
然後在主庫又做了一些測試，操作太多我都有些模糊了。
檢視備庫的情況時，又發現一個奇怪的小問題。dba_data_files的bytes欄位為空
FILE_NAME                                                         BYTES
------------------------------------------------------------ ----------
/home/U01/app/oracle/oradata/test04/system01.dbf              786432000
/home/U01/testdata01.dbf
/home/U01/app/oracle/oradata/test04/testidx01.dbf             104857600
是日誌應用的問題嗎？
SQL> recover managed standby database disconnect from session using current logfile;
Media recovery complete.

SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
READ ONLY WITH APPLY
我們來換個姿勢看看。
TABLESPACE_NAME                FILE_NAME                                                         BYTES STATUS             ONLINE_STATUS
------------------------------ ------------------------------------------------------------ ---------- ------------------ --------------
SYSTEM                         /home/U01/app/oracle/oradata/test04/system01.dbf              786432000 AVAILABLE          SYSTEM
TESTDATA                       /home/U01/testdata01.dbf                                                AVAILABLE          RECOVER
在ADG中，如果仔細觀察還是會發現有時候資料檔案的Online_status在RECOVER和ONLINE之間切換。
在做了一些嘗試未果後，我們來看看主庫到底在幹嘛？
FILE_NAME                                                       FILE_ID      BYTES ONLINE_
------------------------------------------------------------ ---------- ---------- -------
/DATA/app/oracle/oradata/test04/system01.dbf                          1  786432000 SYSTEM
/DATA/app/oracle/oradata/test04/testdata01.dbf                        5            OFFLINE
/DATA/app/oracle/oradata/test04/testidx01.dbf                         6  104857600 ONLINE
發現原來主庫的表空間是offline的。
那把主庫的表空間置為online.
alter tablespace testdata2 online;
在備庫是用不了online選項的，因為是read only
SQL> alter tablespace testdata2 online;
alter tablespace testdata2 online
                           *
ERROR at line 1:
ORA-16000: database open for read-only access
說明這一部分變更沒有傳遞到備庫
在備庫開啟恢復是不可行的。
SQL> recover datafile 5;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
這個問題該繼續怎麼修復呢。
主庫備份 controlfile然後傳送到備庫
SQL> alter database create standby controlfile as '/tmp/control.ctl';
然後就和平常一樣把庫開啟，發現狀態又成了online了。因為這部分的資訊算是同步好了。
FILE_NAME                                                         BYTES ONLINE_STATUS
------------------------------------------------------------ ---------- --------------
/home/U01/app/oracle/oradata/test04/testdata01.dbf            209715200 ONLINE
/home/U01/app/oracle/oradata/test04/testidx01.dbf             104857600 ONLINE
所以透過這個案例說明對於一些資料檔案級別的操作還是需要謹慎。如果在10gR2的早期版本會直接觸發bug,在11g ADG的場景裡還是會有一些意料之外的情況，畢竟主備有別。有些操作還是存在著一些細微的差別。如果主備庫的路徑不同，那麼還是開啟standby_file_management為auto，不要等到問題發生再修復。主庫做offline之類的操作，對於備庫是敏感的。