dataguard中需要注意的一些数据文件操作(r8笔记第21天)

因为最近需要做一个测试，就顺手搭建了一套简单的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之类的操作，对于备库是敏感的。