記一次ORACLE 8I standby增加資料檔案操作

ORACLE 8I standby增加資料檔案與10G後略有不同。
在10G及以後，DATAGUARD架構下在主庫增加資料檔案後，STANDBY_FILE_MANAGEMENT=AUTO時，備庫會自動在DB_FILE_NAME_CONVERT指定的或者預設的與主庫相同的位置建立同樣的資料檔案，不需要手動干預。
在ORACLE 8I standby中，主庫增加資料檔案後，待包含此資訊的REDO在備庫應用時，備庫控制檔案根據REDO來記錄了資料檔案的資訊，然後進行恢復，此時因為無資料檔案，會報錯如下：
Fri Oct 30 22:15:24 2015
ALTER DATABASE RECOVER  managed standby database  
Fri Oct 30 22:15:24 2015
Media Recovery Start: Managed Standby Recovery
Media Recovery Log
Media Recovery Log /u04/oradata/prod/arch/arch_1_401603.arc
Fri Oct 30 22:15:26 2015
Errors in file /oracle/8.1.7/admin/prod/bdump/dbw0_45278_prod.trc:
ORA-01157: cannot identify/lock data file 393 - see DBWR trace file
ORA-01110: data file 393: '/u02/oradata/prod/fndd63.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Media Recovery failed with error 1670
ORA-283 signalled during: ALTER DATABASE RECOVER  managed standby database  ...
此時需要在備庫手動進行資料檔案的新增。
--注意如果一次新增了多個資料檔案，則需要執行多次ALTER DATABASE RECOVER  managed standby database ，因為每次使用此語句，應用REDO時後新增資料檔案資訊到控制檔案，然後恢復時會發現無此資料檔案，就停止恢復了。
如果是建立了多個資料檔案，則REDO中後面的資料檔案資訊還未應用，因此需要重複此過程多次。
-----
ORACLE 8I standby中主庫新增表空間時與此同理，同樣是主庫進行新增操作，然後備庫應用了傳來的REDO，控制檔案中有表空間及資料檔案資訊，然後恢復時發現找不到資料檔案，然後報錯並停止恢復。此時同樣手動建立資料檔案：ALTER DATABASE CREATE DATAFILE ‘filename;,然後重新啟動日誌應用即可。

下面是一次ORACLE 8I standby中主庫增加資料檔案的過程：

1.主庫增加資料檔案並切換REDO LOG：

SQL> alter tablespace FNDXX add datafile '/u03/oradata/prod/FNDXX_26.dbf' size 20480m;

Tablespace altered.

SQL> alter tablespace FNDX add datafile '/u03/oradata/prod/fndx31.dbf' size 10240m;

Tablespace altered.

SQL> alter tablespace FNDD add datafile '/u02/oradata/prod/fndd63.dbf' size 20480m;

Tablespace altered.
SQL> alter system switch logfile;

System altered.

2.檢視此時的主庫ALERT日誌：

Fri Oct 30 21:59:24 2015
alter tablespace FNDXX add datafile '/u03/oradata/prod/FNDXX_26.dbf' size 20480m
Fri Oct 30 22:01:09 2015
Completed: alter tablespace FNDXX add datafile '/u03/oradata/
Fri Oct 30 22:01:23 2015
alter tablespace FNDX add datafile '/u03/oradata/prod/fndx31.dbf' size 10240m
Fri Oct 30 22:02:16 2015
Completed: alter tablespace FNDX add datafile '/u03/oradata/p
Fri Oct 30 22:02:49 2015
alter tablespace FNDD add datafile '/u02/oradata/prod/fndd63.dbf' size 20480m
Fri Oct 30 22:04:36 2015
Completed: alter tablespace FNDD add datafile '/u02/oradata/p
Fri Oct 30 22:06:09 2015
Thread 1 advanced to log sequence 401604
  Current log# 7 seq# 401604 mem# 0: /u03/oradata/prod/redo7a
Fri Oct 30 22:06:09 2015
ARC1: Beginning to archive log# 6 seq# 401603
ARC1: Completed archiving log# 6 seq# 401603

3.檢視備庫狀態

Fri Oct 30 21:54:23 2015
Media Recovery Log /u04/oradata/prod/arch/arch_1_401602.arc
Fri Oct 30 21:54:35 2015
Media Recovery Waiting for thread 1 seq# 401603
Fri Oct 30 22:06:20 2015
Media Recovery Log /u04/oradata/prod/arch/arch_1_401603.arc
Fri Oct 30 22:06:24 2015
Errors in file /oracle/8.1.7/admin/prod/bdump/dbw0_45278_prod.trc:
ORA-01157: cannot identify/lock data file 391 - see DBWR trace file
ORA-01110: data file 391: '/u03/oradata/prod/FNDXX_26.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Media Recovery failed with error 1670
ORA-283 signalled during: ALTER DATABASE RECOVER  managed standby database  ...

4.備庫手動新增資料檔案，並啟動日誌應用，重複多次。

在上一步備庫日誌中可以看到因為無法找到資料檔案，日誌恢復應用已經停止。此時資料庫是MOUNT狀態：
SQL> select status from v$instance;

STATUS
-------
MOUNTED


手動建立資料檔案即可--類似10G及以後的資料庫執行中普通資料檔案丟失，直接重建資料檔案（空的）並應用日誌來恢復即可。
在此此操作中，因為新增多個資料檔案，需要反覆操作多次，如下：
SQL> ALTER DATABASE CREATE DATAFILE '/u03/oradata/prod/FNDXX_26.dbf';

Database altered.

SQL> ALTER DATABASE CREATE DATAFILE '/u03/oradata/prod/fndx31.dbf';   
ALTER DATABASE CREATE DATAFILE '/u03/oradata/prod/fndx31.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile or tempfile    ------此時關於此資訊的REDO還未應用。
'/u03/oradata/prod/fndx31.dbf'


SQL> recover managed standby database;
ORA-00283: recovery session canceled due to errors
ORA-01670: new datafile 392 needed for standby database recovery
ORA-01157: cannot identify/lock data file 392 - see DBWR trace file
ORA-01110: data file 392: '/u03/oradata/prod/fndx31.dbf'


SQL> ALTER DATABASE CREATE DATAFILE '/u03/oradata/prod/fndx31.dbf';

Database altered.

SQL> recover managed standby database;
ORA-00283: recovery session canceled due to errors
ORA-01670: new datafile 393 needed for standby database recovery
ORA-01157: cannot identify/lock data file 393 - see DBWR trace file
ORA-01110: data file 393: '/u02/oradata/prod/fndd63.dbf'


SQL> ALTER DATABASE CREATE DATAFILE '/u02/oradata/prod/fndd63.dbf';

Database altered.

SQL> recover managed standby database;

5.此時ORACLE 8I standby架構已經恢復正常，通常主、備的日誌進行觀察。

主庫：--手動切換日誌
Fri Oct 30 22:18:08 2015
Thread 1 advanced to log sequence 401605
  Current log# 8 seq# 401605 mem# 0: /u04/oradata/prod/redo8a
Fri Oct 30 22:18:08 2015
ARC3: Beginning to archive log# 7 seq# 401604
ARC3: Completed archiving log# 7 seq# 401604
Fri Oct 30 22:32:57 2015
Thread 1 advanced to log sequence 401606
  Current log# 1 seq# 401606 mem# 0: /u01/oradata/prod/redo1a
Fri Oct 30 22:32:57 2015
ARC1: Beginning to archive log# 8 seq# 401605
ARC1: Completed archiving log# 8 seq# 401605
Fri Oct 30 22:33:09 2015
Thread 1 advanced to log sequence 401607
  Current log# 2 seq# 401607 mem# 0: /u02/oradata/prod/redo2a
Fri Oct 30 22:33:09 2015
ARC3: Beginning to archive log# 1 seq# 401606
ARC3: Completed archiving log# 1 seq# 401606

備庫已經在同步應用REDO日誌：
Fri Oct 30 22:17:45 2015
ALTER DATABASE RECOVER  managed standby database  
Media Recovery Start: Managed Standby Recovery
Media Recovery Log
Media Recovery Log /u04/oradata/prod/arch/arch_1_401603.arc
Media Recovery Waiting for thread 1 seq# 401604
Fri Oct 30 22:18:18 2015
Media Recovery Log /u04/oradata/prod/arch/arch_1_401604.arc
Fri Oct 30 22:18:30 2015
Media Recovery Waiting for thread 1 seq# 401605
Fri Oct 30 22:33:15 2015
Media Recovery Log /u04/oradata/prod/arch/arch_1_401605.arc
Fri Oct 30 22:33:34 2015
Media Recovery Log /u04/oradata/prod/arch/arch_1_401606.arc
Media Recovery Waiting for thread 1 seq# 401607