standby新增檔案錯誤的解決方法
這篇文章主要討論在不同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
可以看到，資料庫已經正常。
一、如果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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- rac 環境新增資料檔案誤新增到檔案系統，正確解決方法
- duplicate standby database 報ORA-05507錯誤解決方法Database
- Larabel遷移檔案時報SQLSTATE[42000]錯誤的解決方法SQL
- Linux中常見的檔案讀寫錯誤問題及解決方法！Linux
- 錯誤新增表空間的資料檔案
- undefined reference to錯誤的解決方法Undefined
- PHP錯誤“Thisfilehasexpired”的解決方法PHP
- webstorm中express專案bin/www檔案型別顯示錯誤解決方法WebORMExpress型別
- 錯誤Namenodeisinsafemode的解決方法
- JAVA web ServletFileUpload檔案上傳遇到大量50+錯誤的解決方法JavaWebServlet
- vsftpd 錯誤：530 and 500 錯誤解決方法FTP
- AFNetworkingErrorDomain 錯誤解決方法ErrorAI
- 非歸檔模式下資料檔案被誤刪的解決方法模式
- dbfread報錯ValueError錯誤解決方法Error
- 解決Mac檔案共享出錯、不起作用的方法Mac
- 刪除大量檔案Argument list too long錯誤解決
- Windows、Linux下檔案操作(寫、刪除)錯誤的產生原因、及解決方法WindowsLinux
- FileUpload上傳多檔案時出現“無法訪問已關閉的檔案”錯誤的解決方法
- MYSQL中 TYPE=MyISAM 錯誤的解決方法MySql
- Mac上搭建chromedriver的錯誤解決方法MacChrome
- Windows下make clean指令錯誤[錯誤碼2](系統找不到指定檔案)的解決方案Windows
- HTTP 錯誤 500.19- Internal Server Error 錯誤解決方法HTTPServerError
- 【Oracle】ORA-00054 錯誤解決方法Oracle
- Android錯誤解決方法集錦Android
- hadoop日常錯誤解決方法整理Hadoop
- ORA-04098錯誤解決方法
- ORA-25154錯誤解決方法
- npm WARN unmet dependency錯誤解決方法NPM
- ORA-01950錯誤的解決方法
- Ajax中“Sys未定義”錯誤的解決方法
- MAC將 /etc/sudoers檔案修改錯後的幾種解決方法Mac
- 歸檔和非歸檔模式下ORA-01145錯誤的解決方法模式
- git錯誤：物件檔案為空（object-file-is-empty）解決方案Git物件Object
- 寬頻連線錯誤691解決方法
- ORA-14452錯誤及解決方法
- ubuntu網路卡啟動錯誤解決方法Ubuntu
- IMP-00098 錯誤及解決方法
- steam磁碟寫入錯誤怎麼解決 steam磁碟寫入錯誤解決方法大全