standby添加文件错误的解决方法

作者 space6212 00:58 | Permalink 静态链接网址 | Comments 最新回复 (0) | Trackback 引用 (1) | Oracle技术
这篇文章主要讨论在不同standby_file_management设置下，当主库添加文件时，备库不能相应的创建文件时的解决方法。

一、如果standby_file_management=auto
--主库
[oracle@primary u01]$ pwd
/u01
[oracle@primary u01]$ ls
archivelog backup data newdata newdata2 newdata3 oracle
[oracle@primary u01]$ ls /u03

--备库
[oracle@standby u01]$ pwd
/u01
[oracle@standby u01]$ ls
archivelog backup newdata2 newdata3 oracle
[oracle@standby u01]$ ls /u03
ls: /u03: No such file or directory

--在备库上没有主库/u03和/u01/data目录。

SQL> show parameter db_file_name_convert

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/newdata/, /u02/

SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO

SQL> alter database recover managed standby database disconnect from session;

Database altered.

下面我们在主库上添加表空间。
--主库执行
SQL> create tablespace ts1 datafile '/u01/data/ts1.dbf' size 10m;

Tablespace created.

SQL> create tablespace ts3 datafile '/u03/ts3.dbf' size 10m;

Tablespace created.

SQL> create table ts1(id int) tablespace ts1;

Table created.

SQL> create table ts3(id int) tablespace ts3;

Table created.

SQL> alter system switch logfile;

System altered.


--查看备库的alert文件
Media Recovery Log /u01/archivelog/1_65_625009216.arc
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Recovery created file /u01/data/ts1.dbf
Successfully added datafile 10 to media recovery
Datafile #10: '/u01/data/ts1.dbf'
WARNING: File being created with same name as in Primary
Existing file may be overwritten
File #11 added to control file as 'UNNAMED00011'.
Originally created as:
'/u03/ts3.dbf'
Recovery was unable to create the file as:
'/u03/ts3.dbf'
MRP0: Background Media Recovery terminated with error 1119
Tue Jun 19 01:34:22 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_mrp0_3492.trc:
ORA-01119: error in creating database file '/u03/ts3.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Linux Error: 13: Permission denied
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Tue Jun 19 01:34:25 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_mrp0_3492.trc:
ORA-01119: error in creating database file '/u03/ts3.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Linux Error: 13: Permission denied
Tue Jun 19 01:34:25 2007
MRP0: Background Media Recovery process shutdown (primary)

从alert信息可以看出，如果standby_file_management=AUTO，oracle会尝试创建与主库新加文件一样的目录和文件，如果有对应的权限和空间，则文件会添加成功，如/u01/data/ts1.dbf；否则就会失败，如/u03/ts3.dbf。

--此时备库的数据文件的状态
SQL> select name,status from v$datafile;

NAME STATUS
-------------------------------------------------- -------
/u01/oracle/oradata/primary/system01.dbf SYSTEM
/u01/oracle/oradata/primary/undotbs01.dbf ONLINE
/u01/oracle/oradata/primary/sysaux01.dbf ONLINE
/u01/oracle/oradata/primary/users01.dbf ONLINE
/u01/oracle/oradata/primary/test01.dbf ONLINE
/u02/test201.dbf ONLINE
/u01/oracle/oradata/primary/test3.dbf ONLINE
/u01/newdata2/test401.dbf ONLINE
/u01/newdata3/test5.dbf ONLINE
/u01/data/ts1.dbf RECOVER
/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00011 RECOVER

此时有两种解决方法：
1、创建与主库相同的目录并授权
2、设置db_file_name_convert

我们这里第一种方法（两种方法解决步骤大部分是一样的），在备库上建立对应目录并授权：
[root@standby ~]# mkdir /u03
[root@standby ~]# chown -R oracle:oinstall /u03

对/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00011进行改名，改名前，必须设置standby_file_management=manual。
SQL> alter system set standby_file_management=manual;

System altered.

--修改数据文件名称和路径
SQL> alter database create datafile '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00011' as '/u03/ts3.dbf';

Database altered.

--重新把standby_file_management设置成AUTO
SQL> alter system set standby_file_management=auto;

System altered.

--由于添加文件把恢复进程中断，再次启动恢复进程
SQL> alter database recover managed standby database disconnect from session;

Database altered.

此时备库alert文件的信息如下：
alter database recover managed standby database disconnect from session
Tue Jun 19 01:52:43 2007
Attempt to start background Managed Standby Recovery process (primary)
MRP0 started with pid=19, OS id=3518
Tue Jun 19 01:52:44 2007
MRP0: Background Managed Standby Recovery process started (primary)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Media Recovery Log /u01/archivelog/1_65_625009216.arc
Media Recovery Log /u01/archivelog/1_66_625009216.arc
Tue Jun 19 01:52:50 2007
Completed: alter database recover managed standby database disconnect from session

可以看出，redo apply已经正常了，此时的文件状态：
SQL> select name,status from v$datafile;

NAME STATUS
-------------------------------------------------- -------
/u01/oracle/oradata/primary/system01.dbf SYSTEM
/u01/oracle/oradata/primary/undotbs01.dbf ONLINE
/u01/oracle/oradata/primary/sysaux01.dbf ONLINE
/u01/oracle/oradata/primary/users01.dbf ONLINE
/u01/oracle/oradata/primary/test01.dbf ONLINE
/u02/test201.dbf ONLINE
/u01/oracle/oradata/primary/test3.dbf ONLINE
/u01/newdata2/test401.dbf ONLINE
/u01/newdata3/test5.dbf ONLINE
/u01/data/ts1.dbf ONLINE
/u03/ts3.dbf ONLINE

一切正常。


二、如果standby_file_management=manual
备库参数：
SQL> show parameter standby_file_management

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL

--主库执行
SQL> create tablespace ts5 datafile '/u01/data/ts5.dbf' size 10m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

--此时备库数据文件状态
SQL> select name,status from v$datafile;

NAME STATUS
-------------------------------------------------- -------
/u01/oracle/oradata/primary/system01.dbf SYSTEM
/u01/oracle/oradata/primary/undotbs01.dbf ONLINE
/u01/oracle/oradata/primary/sysaux01.dbf ONLINE
/u01/oracle/oradata/primary/users01.dbf ONLINE
/u01/oracle/oradata/primary/test01.dbf ONLINE
/u02/test201.dbf ONLINE
/u01/oracle/oradata/primary/test3.dbf ONLINE
/u01/newdata2/test401.dbf ONLINE
/u01/newdata3/test5.dbf ONLINE
/u01/data/ts1.dbf ONLINE
/u03/ts3.dbf ONLINE
/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00012 RECOVER

查看备库alert文件信息：
Media Recovery Log /u01/archivelog/1_67_625009216.arc
File #12 added to control file as 'UNNAMED00012' because
the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL
The file should be manually created to continue.
Errors with log /u01/archivelog/1_67_625009216.arc
MRP0: Background Media Recovery terminated with error 1274
Tue Jun 19 02:09:02 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_mrp0_3518.trc:
ORA-01274: cannot add datafile '/u01/data/ts5.dbf' - file could not be created
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Tue Jun 19 02:09:02 2007
Errors in file /u01/oracle/admin/primary/bdump/primary_mrp0_3518.trc:
ORA-01274: cannot add datafile '/u01/data/ts5.dbf' - file could not be created
Tue Jun 19 02:09:02 2007
MRP0: Background Media Recovery process shutdown (primary)

在这种情况下有两种解决方法：
1：手工创建这个文件
2：手工从主数据库热备这个文件过去，并重新创建standby控制文件

这里简单起见，用第一种方法。

--在备库创建文件
SQL> alter database create datafile '/u01/oracle/product/10.2.0/db_1/dbs/UNNAMED00012' as '/u01/data/ts5.dbf';

Database altered.

--重启恢复进程
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select name,status from v$datafile;

NAME STATUS
-------------------------------------------------- -------
/u01/oracle/oradata/primary/system01.dbf SYSTEM
/u01/oracle/oradata/primary/undotbs01.dbf ONLINE
/u01/oracle/oradata/primary/sysaux01.dbf ONLINE
/u01/oracle/oradata/primary/users01.dbf ONLINE
/u01/oracle/oradata/primary/test01.dbf ONLINE
/u02/test201.dbf ONLINE
/u01/oracle/oradata/primary/test3.dbf ONLINE
/u01/newdata2/test401.dbf ONLINE
/u01/newdata3/test5.dbf ONLINE
/u01/data/ts1.dbf ONLINE
/u03/ts3.dbf ONLINE

NAME STATUS
-------------------------------------------------- -------
/u01/data/ts5.dbf ONLINE

可以看到，数据库已经正常。