记一次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