Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2882365
  • 博文数量: 200
  • 博客积分: 2413
  • 博客等级: 大尉
  • 技术积分: 3067
  • 用 户 组: 普通用户
  • 注册时间: 2011-04-01 22:07
文章分类

全部博文(200)

文章存档

2018年(2)

2017年(8)

2016年(35)

2015年(14)

2014年(20)

2013年(24)

2012年(53)

2011年(44)

分类: Oracle

2014-03-04 15:56:19

此解决办法适用于 9.2.0.1 to 12.1.0.1

环境:

rac standby 10.2.0.5.0

=========
问题描述:
=========
备库日志不应用。

alert日志:
Mon Jun 03 10:00:18 CST 2014
File #11 added to control file as 'UNNAMED00011' 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
Mon Jun 03 10:00:19 CST 2014
Errors in file /app/oracle/admin/bus-pri/bdump/bus-pri2_mrp0_8889.trc:
ORA-01274: cannot add datafile '+DG1/bus-pri/datafile/order_data.373.841226363' - file could not be created
Mon Jun 03 10:00:19 CST 2014
Managed Standby Recovery not using Real Time Apply
Mon Jun 03 10:00:21 CST 2014
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Mon Jun 03 10:00:21 CST 2014
Errors in file /app/oracle/admin/bus-pri/bdump/bus-pri2_mrp0_8889.trc:
ORA-01274: cannot add datafile '+DG1/bus-pri/datafile/order_data.373.841226363' - file could not be created
Mon Jun 03 10:00:21 CST 2014
MRP0: Background Media Recovery process shutdown (bus-pri2)


主备库数据文件情况:
备库:
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DG1/bus-standby/datafile/system.261.797439009
+DG1/bus-standby/datafile/undotbs1.263.797439141
+DG1/bus-standby/datafile/sysaux.266.797439145
+DG1/bus-standby/datafile/users.267.797439145
+DG1/bus-standby/datafile/undotbs2.258.797439005
+DG1/bus-standby/datafile/order_data.264.797439143
+DG1/bus-standby/datafile/oggdata.262.797439011
+DG1/bus-standby/datafile/order_data.259.797439007
+DG1/bus-standby/datafile/order_data.260.797439009
+DG1/bus-standby/datafile/order_data.265.797439145
/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00011 <----------新增数据文件,路径不对

11 rows selected.


主库:
SQL> select name from v$datafile;

NAME
------------------------------------------------------------
+DG1/bus-pri/datafile/system.326.784810829
+DG1/bus-pri/datafile/undotbs1.321.784810829
+DG1/bus-pri/datafile/sysaux.323.784810829
+DG1/bus-pri/datafile/users.319.784810829
+DG1/bus-pri/datafile/undotbs2.420.784810925
+DG1/bus-pri/datafile/order_data.285.788180617
+DG1/bus-pri/datafile/oggdata.302.788192935
+DG1/bus-pri/datafile/order_data.284.796403117
+DG1/bus-pri/datafile/order_data.298.796403169
+DG1/bus-pri/datafile/order_data.372.796403215
+DG1/bus-pri/datafile/order_data.373.841226363

11 rows selected.

=========
错误原因:
=========
?Standy_file_management is set to MANUAL
?PriJuny & Physical Standby are having different file structures and DB_FILE_NAME_CONVERT is not set according to the Directory Structures in PriJuny and Standby
?Insufficient Space or wrong Permissions on the Standby Database to create the Datafile


Standy_file_management参数设置成了MANUAL。MANUAL:主库添加/删除数据文件,必须手动在备库添加/删除;AUTO:主库添加/删除数据文件,备库自动添加/删除。
主库和备库的数据文件存放路径不同,并且没有设置DB_FILE_NAME_CONVERT参数。
备库存储没有足够的空间或者权限去创建数据文件。


==========
解决办法:
==========
1.备库添加db_file_name_convert和log_file_name_convert参数
!!RAC集群修改db_file_name_convert和log_file_name_convert一定要注意。由于只能在pfile中添加这两个参数,会涉及到重启数据库。
非RAC数据库正常操作,但是RAC必须把所有节点关闭,修改pfile参数,然后再启动所有节点。

在备库第一个节点创建pfile
create pfile='/tmp/init_temp.ora' from spfile;

添加convert参数
$ vim /tmp/init_temp.ora
*.db_file_name_convert='+DG1/bus-pri','+DG1/bus-standby'
*.log_file_name_convert='+DG1/bus-pri','+DG1/bus-standby'

关闭rac standby的两个节点

在rac的第1节点用pfile启动数据库,用pfile创建spfile;用新的spfile启动数据库到mount
sqlplus / as sysdba
startup nomount pfile='/tmp/init_temp.ora';
create spfile='+DG1/bus-standby/spfilebus-standby.ora' from pfile='/tmp/init_temp.ora';
shutdown immediate;
startup mount

启动第2节点到mount
startup mount

检查convert参数
SQL> show parameter convert
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      +DG1/bus-pri, +DG1/bus-standby
log_file_name_convert                string      +DG1/bus-pri, +DG1/bus-standby

检查rac状态
SQL> select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from gv$instance;


2.确定存在UNNAMED数据文件
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DG1/bus-standby/datafile/system.261.797439009
+DG1/bus-standby/datafile/undotbs1.263.797439141
+DG1/bus-standby/datafile/sysaux.266.797439145
+DG1/bus-standby/datafile/users.267.797439145
+DG1/bus-standby/datafile/undotbs2.258.797439005
+DG1/bus-standby/datafile/order_data.264.797439143
+DG1/bus-standby/datafile/oggdata.262.797439011
+DG1/bus-standby/datafile/order_data.259.797439007
+DG1/bus-standby/datafile/order_data.260.797439009
+DG1/bus-standby/datafile/order_data.265.797439145
/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00011 <------------------
 

3.把UNNAMED数据文件放到正确路径
alter database create datafile '/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00011' as '+DG1/bus-standby/datafile/order_data';


4.确定UNNAMED数据文件恢复正常
SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DG1/bus-standby/datafile/system.261.797439009
+DG1/bus-standby/datafile/undotbs1.263.797439141
+DG1/bus-standby/datafile/sysaux.266.797439145
+DG1/bus-standby/datafile/users.267.797439145
+DG1/bus-standby/datafile/undotbs2.258.797439005
+DG1/bus-standby/datafile/order_data.264.797439143
+DG1/bus-standby/datafile/oggdata.262.797439011
+DG1/bus-standby/datafile/order_data.259.797439007
+DG1/bus-standby/datafile/order_data.260.797439009
+DG1/bus-standby/datafile/order_data.265.797439145
+DG1/bus-standby/datafile/order_data <----------------正常了


5.STANDBY_FILE_MANAGMENT 改为auto
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO scope=both;


6.开启日志实时应用
alter database recover managed standby database using current logfile disconnect;


7.确认MRP进程启动
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#,BLOCKS,PID FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS        PID
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0      16986
ARCH      CONNECTED             0          0          0          0      16988
RFS       IDLE                  0          0          0          0      17143
RFS       IDLE                  0          0          0          0      17249
MRP0      APPLYING_LOG          1       6875     386601     395716      25088


参照 Background Media Recovery terminated with ORA-1274 after adding a Datafile (文档 ID 739618.1) 


阅读(4242) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~