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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Larabel遷移檔案時報SQLSTATE[42000]錯誤的解決方法SQL
- Linux中常見的檔案讀寫錯誤問題及解決方法！Linux
- JAVA web ServletFileUpload檔案上傳遇到大量50+錯誤的解決方法JavaWebServlet
- undefined reference to錯誤的解決方法Undefined
- webstorm中express專案bin/www檔案型別顯示錯誤解決方法WebORMExpress型別
- PbootCMS 404 錯誤解決方法boot
- dbfread報錯ValueError錯誤解決方法Error
- 刪除大量檔案Argument list too long錯誤解決
- 解決Mac檔案共享出錯、不起作用的方法Mac
- dedecms提示500錯誤解決方法
- HTTP 錯誤 500.19- Internal Server Error 錯誤解決方法HTTPServerError
- nginx出現403錯誤的解決方法Nginx
- Mac上搭建chromedriver的錯誤解決方法MacChrome
- MYSQL中 TYPE=MyISAM 錯誤的解決方法MySql
- idea執行java專案main方法報build failure錯誤的解決方法IdeaJavaAIUI
- Windows下make clean指令錯誤[錯誤碼2](系統找不到指定檔案)的解決方案Windows
- win10 檔案系統錯誤(-2145103860)怎麼解決Win10
- IDEA專案已新增jar包，pom檔案，打包Maven卻一直報錯的幾種解決方法IdeaJARMaven
- standby上增加tempfile報錯ORA-00604,ORA-16000解決方法
- Nginx報504 gateway timeout錯誤的解決方法NginxGateway
- Linux yum提示Loaded plugins錯誤的解決方法LinuxPlugin
- win7系統無法在AE渲染寫入檔案提示錯誤-1610153459的解決方法Win7
- steam磁碟寫入錯誤怎麼解決 steam磁碟寫入錯誤解決方法大全
- .gitignore檔案配置以及gitee提交報Push rejected...錯誤解決Gitee
- 主庫到standby報錯解決：Error 12154 received logging on to the standby ORA-12154Error
- 印表機提示列印錯誤怎麼解決 印表機狀態錯誤的方法
- 織夢提示dedecms error warning錯誤的解決方法Error
- 解決java5 發行錯誤最好的方法Java
- ORA-06550錯誤程式碼的解決方法QB
- mysql 發生系統錯誤1067的解決方法MySql
- Win10電腦打不開檔案提示“錯誤2755”的原因和解決方法Win10
- Ocelot錯誤解決
- Mac修復多個檔案錯誤許可權的方法？Mac
- 資料庫連線錯誤的原因及解決方法資料庫
- 解決伺服器返回錯誤的方法和步驟伺服器
- Python: 安裝 sklearn 包出現錯誤的解決方法Python
- PHP & Linux: libsodium.so.23 錯誤解決方法PHPLinux
- ORACLE匯入遇到ORACLE錯誤959解決方法Oracle