一、问题描述与分析
测试环境有套DG，当然我的个人环境是两台虚拟机，之前单独打开主库机器做过create tablespace 的操作并导入数据，后继再打开备库时，发现归档日志在未能够接收到备库，当时使用了命令alter database recover managed standby database; 去应用日志后报错如下（代码1），我们不难发现是由于data file 6文件不存在导致的。处理思路也就来了，如果我们在备库上建立对应的表空间，问题应该就可以得到解决了。
代码1：
SYS@sbdb >alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/db/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/db/dbs/UNNAMED00006'
二、实验与处理方法
1.备库检查状态
[oracle@suzzy ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 28 13:31:16 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@sbdb >select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
2. 备库日志应用情况，发现179号日志开始未被应用（操作系统检查未见179-186号日志）
SYS@sbdb >select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
14 YES
15 YES
16 YES
17 YES
18 YES
… ...
176 YES
177 YES
178 YES
SEQUENCE# APPLIED
---------- ---------
179 NO
180 NO
181 NO
182 NO
183 NO
184 NO
185 NO
186 NO
173 rows selected.
3. 利用命令来启动应用日志加有disconnect 参数（日志仍然未被应用，系统中仍未接收到归档日志）
SYS@sbdb >alter database recover managed standby database disconnect;
Database altered.
SYS@sbdb >select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
---------- ---------
14 YES
15 YES
16 YES
17 YES
18 YES
… ...
176 YES
177 YES
178 YES
SEQUENCE# APPLIED
---------- ---------
179 NO
180 NO
181 NO
182 NO
183 NO
184 NO
185 NO
186 NO
173 rows selected.
4. 通过命令来启动应用日志未加disconnect参数（6号数据文件出现问题）
SYS@sbdb >alter database recover managed standby database;
alter database recover managed standby database
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/db/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/oracle/product/10.2.0/db/dbs/UNNAMED00006'
5. 检查主、备库的表空间与数据文件（表空间两边一致，数据文件在备库未能够成功建立）此处参数standby_file_management是可以控制在备库自动按照新路径建立数据文件，分别可以设置为MANUAL/AUTO。这点非常重要。如果要想模拟此报错，也可以将该参数设置为MANUAL后在主库建立新表空间，这样备库就会出现这个问题。
主库：
SYS@orcl >select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /oracle/oradata/orcl/system01.dbf
2 /oracle/oradata/orcl/sysaux01.dbf
3 /oracle/oradata/orcl/undotbs01.dbf
4 /oracle/oradata/orcl/users01.dbf
5 /oracle/oradata/orcl/example01.dbf
6 /oracle/oradata/orcl/sl_01.dbf
6 rows selected.
SYS@orcl >select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
SL
7 rows selected.
备库：
SYS@sbdb >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/sbdb/system01.dbf
/oracle/oradata/sbdb/sysaux01.dbf
/oracle/oradata/sbdb/undotbs01.dbf
/oracle/oradata/sbdb/users01.dbf
/oracle/oradata/sbdb/example01.dbf
/oracle/product/10.2.0/db/dbs/UNNAMED00006
6 rows selected.
SYS@sbdb >select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
SL
7 rows selected.
6. 故障处理，将standby_file_management参数设置为MANUAL
SYS@sbdb >alter system set standby_file_management=manual;
System altered.
7. 建立正确路径的数据文件，将虚拟出来的文件路径变更正确
SYS@sbdb >alter database create datafile '/oracle/product/10.2.0/db/dbs/UNNAMED00006' as '/oracle/oradata/sbdb/sl_01.dbf';
Database altered.
8. 将standby_file_management参数设置回AUTO
SYS@sbdb >alter system set standby_file_management=auto;
System altered.
9. 恢复备库日志应用
SYS@sbdb >recover managed standby database disconnect;
Media recovery complete.
10. 检查备库日志应用情况（此时日志已经被应用），故障解决
SYS@sbdb >select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
15 YES
17 YES
16 YES
18 YES
19 YES
20 YES
… ...
177 YES
173 YES
171 YES
178 YES
SEQUENCE# APPLIED
---------- ---------
179 YES
180 YES
181 YES
182 YES
183 YES
184 YES
185 YES
186 YES
187 YES
174 rows selected.
11. 验证数据，经过下面验证，6号SL表空间在备库恢复正常，数据与主库一致
主库：
SYS@orcl >select file_id,file_name,tablespace_name from dba_data_files where file_id=6;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------
6 /oracle/oradata/orcl/sl_01.dbf SL
SYS@orcl >select username,default_tablespace from dba_users where username='DEVSLSC';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
DEVSLSC SL
SYS@orcl >conn devslsc/oracle
Connected.
DEVSLSC@orcl >select count(*) from cat;
COUNT(*)
----------
251
DEVSLSC@orcl >select count(*) from T_AC_MENU;
COUNT(*)
----------
113
备库：
SYS@sbdb >select file_id,file_name,tablespace_name from dba_data_files where file_id=6;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------
6 /oracle/oradata/sbdb/sl_01.dbf SL
SYS@sbdb >select username,default_tablespace from dba_users where username='DEVSLSC';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
DEVSLSC SL
SYS@sbdb >conn devslsc/oracle
Connected.
DEVSLSC@sbdb >select count(*) from cat;
COUNT(*)
----------
251
DEVSLSC@sbdb >select count(*) from T_AC_MENU;
COUNT(*)
----------
113
三、总结
Data Guard是我们DBA们的一件法定，是给我们最后的一道防线，所以不可忽视。上述问题如果在standby_file_management参数设置成AUTO（默认就是AUTO），往往是不会出视该问题。由于这里我用的是两台虚拟机，在备机暂定状态对主库进行了创建表空间所导至的。大家在生产，测试环境的真机中不会碰到。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26148431/viewspace-1818046/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
