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之类的操作，对于备库是敏感的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1995336/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1995336/