ITPub博客

首页 > Linux操作系统 > Linux操作系统 > standby添加文件错误的解决方法

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

原创 Linux操作系统 作者:space6212 时间:2019-05-03 20:21:07 0 删除 编辑
这篇文章主要讨论在不同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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63821/,如需转载,请注明出处,否则将追究法律责任。

上一篇: in、exists与索引
请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    122
  • 访问量
    89816