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