【此为"一森咖记"公众号——第35篇文章】
【前言】
早上来到公司，接到告知某个生产环境的oracle的DG同步库没有数据更新，让排查下。
【问题排查】
SYS@ethanDB> select status from v$instance;
STATUS
------------------------------------
OPEN
数据正常，接着排查日志应用状态
SYS@ethanDB> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
-------------------- --------------------
PHYSICAL STANDBY READ ONLY
擦，出情况了，日志没有应用，应为READ ONLY WITH APPLY；奇怪，一直在使用日志应用啊；先不管，先尝试拉下日志应用。
DG库中使用如下命令先把日志应用拉起来
SYS@ethanDB> alter database recover managed standby database disconnect from session using current logfile;
alter database recover managed standby database disconnect from session using current logfile
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
报错信息如上，接着查看alert日志,异常信息报错如下:
Standby Crash Recovery aborted due to error 1111.
Errors in file siebel/CRMSBLBAK/diag/diag/rdbms/crmsblbak/CRMSBLBAK/trace/CRMSBLBAK_ora_6657.trc:
ORA-01111: name for data file 547 is unknown - rename to correct file
ORA-01110: data file 547: '/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'
ORA-01157: cannot identify/lock data file 547 - see DBWR trace file
ORA-01111: name for data file 547 is unknown - rename to correct file
ORA-01110: data file 547: '/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'
Completed Standby Crash Recovery.
Errors in file siebel/CRMSBLBAK/diag/diag/rdbms/crmsblbak/CRMSBLBAK/trace/CRMSBLBAK_ora_6657.trc:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 547 - see DBWR trace file
ORA-01111: name for data file 547 is unknown - rename to correct file
ORA-01110: data file 547: '/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'
ORA-10458 signalled during: ALTER DATABASE OPEN...
Thu Aug 22 10:47:42 2019
Using STANDBY_ARCHIVE_DEST parameter default value as siebelarch
Thu Aug 22 10:47:44 2019
Primary database is in MAXIMUM PERFORMANCE mode
数据文件547不能创建，而且同步库中数据文件'/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'的名字也很奇怪，分别查看在主库和备库中547数据文件名称。
备库查看数据文件
SYS@ethanDB> select name,file# from v$datafile where file#=547;NAME--------------------------------------------------------------FILE#----------/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547547
主库查看文件文件547
SYS@CRMPRDSDB> select name,file# from v$datafile where file#=547;NAME--------------------------------------------------------------------------------FILE#----------/oradata/primary_dirs/indx0233.dbf547547 rows selected.
可以看到，故障时在备库中的547数据文件是'/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'，应为/oradata/primary_dirs/indx0233.dbf。
主备库之间的db_file_name_convert对应的转换关系为：'/oradata/primary_dirs','/oradata/standby_dirs'；
后来回忆了下，原因找到：因主库做了alter tablespace index02 add datafile '/oradata/primary_dirs/indx0233.dbf' size 10G, 而那时同步库的文件系统/oradata/standby_dirs剩余空间不到10G，所以导致备库数据文件创建不成功，备库停止服务。
【解决方案】
1. 存储扩容
首先必须要先扩展文件系统/siebel03大于10G以上，存储同事帮着扩展了20G。
2. 创建正确的数据文件
有之前的分析，可以得知，
'/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'
应该对应
'/oradata/standby_dirs/indx0233.dbf';
做如下操作：
1.sqlplus “/as sysdba”;
2.shutdown abort;
3.startup mount;
4.alter database datafile '/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547' offline drop;
5. alter database open;
6. alter system set standby_file_management=manual;
7.alter database create datafile
'/opt/oracle/product/11.2.0.4/db_1/dbs/UNNAMED00547'
as '/oradata/standby_dirs/indx0233.dbf';
3. 记住，standby_file_management一定要改回来
SYS@ethanDB> alter system set standby_file_management=auto;
SYS@ethanDB> show parameter standby_file_management
NAME TYPE
---------- -----------
VALUE
-------------
standby_file_management string
AUTO
4. 此刻需要重启下，否则应用日志还是如下报错
SYS@ethanDB> alter database recover managed standby database disconnect from session using current logfile;
alter database recover managed standby database disconnect from session using current logfile
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SYS@ethanDB> shu immediate
SYS@ethanDB> startup
Database opened.
5. 手动调下日志应用
SYS@ethanDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
注意：
11g和10g的日志应用命令有所不同：
alter database recover managed standby database disconnect from session using current logfile; (11g)
alter database recover managed standby database disconnect from session;(10g)
至此，整个排查和异常解决完毕。
【总结】
1. 主库在扩展表空间时，如果主库和备库的文件系统划分大小不一致，因主库文件系统可以观察大小，但在主库表空间扩展命令执行前，一定要留意备库的参数db_file_name_convert，看其所对应的文件系统是否空间充足；
2. 主备库在前期存储大小配置时，存储大小强烈建议设置为一样大小，减少不必要的麻烦。
【参考】
https://blog.csdn.net/ora_raymond/article/details/22744373
欢迎关注个人微信公众号；
长按以下二维码或公众号搜索“一森咖记”
往期精彩文章
=====================================
