restore database check readonly
在預設情況下,即使丟失了只讀的資料檔案,RMAN也不會在執行完全資料庫還原操作時還原只讀的資料檔案。
要在完全恢復期間還原只讀的資料檔案,就必須在restore命令中使用check readonly引數,如下所示:
RMAN> restore database check readonly;
需要注意的是,執行recover tablespace或recover datafile命令時,RMAN的工作情況是不一樣的。使用
上述任何一種recover命令時,不管表空間是否為只讀狀態都會執行恢復操作。
==============================================================================================
==============================================================================================
建立恢復只讀表空間測試環境:
SQL> alter tablespace ts read only;
Tablespace altered.
SQL>
RMAN> configure backup optimization on; 歸檔檔案只備份一次,只讀表空間備份2次
RMAN> backup database plus archivelog;
RMAN> backup database plus archivelog;
RMAN> backup database plus archivelog;
Starting backup at 07-APR-10
using channel ORA_DISK_1
skipping datafile 5; already backed up 2 time(s)
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/zydev/system01.dbf
input datafile fno=00002 name=/u02/oradata/zydev/undotbs01.dbf
input datafile fno=00003 name=/u02/oradata/zydev/sysaux01.dbf
input datafile fno=00004 name=/u02/oradata/zydev/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-APR-10
channel ORA_DISK_1: finished piece 1 at 07-APR-10
... 省略 ...
SQL> shutdown abort; -- 關閉後,刪除相關資料檔案
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 385876536 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u02/oradata/zydev/sysaux01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
執行恢復:
恢復1. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN和sqlplus匯合操作。
恢復2. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN操作
恢復3. 恢復所有的需要的資料檔案,然後開啟資料庫
恢復4. restore database check readonly,恢復所有的資料庫檔案,然後開啟資料庫
恢復5. 一般處理
#####################################################################
恢復1. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN和sqlplus匯合操作。
RMAN> restore database; -- 這裡的只讀表空間的資料檔案不進行恢復
RMAN> recover database;
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/07/2010 19:06:14
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/zydev/ts01.dbf'
RMAN>
SQL> alter database datafile '/u02/oradata/zydev/ts01.dbf' offline;
SQL> alter database open;
退出rman連線,重新聯入
RMAN> restore datafile '/u02/oradata/zydev/ts01.dbf';
RMAN> recover datafile '/u02/oradata/zydev/ts01.dbf';
如果不重新進行連線,將會遭遇如下錯誤:
RMAN> restore datafile '/u02/oradata/zydev/ts01.dbf';
Starting restore at 07-APR-10
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/07/2010 19:36:49
RMAN-20021: database not set
RMAN-06010: error while looking up datafile: 5
RMAN>
SQL> alter database datafile '/u02/oradata/zydev/ts01.dbf' online;
SQL> alter database open;
SQL>
#####################################################################
恢復2. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN操作
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/07/2010 19:30:55
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/zydev/ts01.dbf'
RMAN> sql 'alter database datafile 5 offline';
RMAN> alter database open;
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> sql 'alter database datafile 5 online';
#####################################################################
恢復3. 恢復所有的需要的資料檔案,然後開啟資料庫
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/07/2010 19:48:31
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/zydev/ts01.dbf'
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> alter database open;
#####################################################################
恢復4. restore database check readonly,恢復所有的資料庫檔案,然後開啟資料庫
RMAN> restore database check readonly;
Starting restore at 07-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u02/oradata/zydev/ts01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T184537_5vrrll39_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T184537_5vrrll39_.bkp tag=TAG20100407T184537
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/zydev/system01.dbf
restoring datafile 00002 to /u02/oradata/zydev/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/zydev/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/zydev/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T185426_5vrs331q_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T185426_5vrs331q_.bkp tag=TAG20100407T185426
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 07-APR-10
RMAN> recover database;
RMAN> alter database open;
#####################################################################
恢復5. 一般處理
SQL> set linesize 180
SQL> col name format a50
SQL> col error format a20
SQL> select file#, name , error from v$dbfile JOIN v$recover_file USING (file#);
FILE# NAME ERROR
---------- -------------------------------------------------- --------------------
4 /u02/oradata/zydev/users01.dbf FILE NOT FOUND
5 /u02/oradata/zydev/ts01.dbf FILE NOT FOUND
SQL>
RMAN> restore datafile 4,5;
RMAN> recover datafile 4,5;
RMAN> alter database open;
source:http://ctchina.blog.sohu.com/147863974.html
要在完全恢復期間還原只讀的資料檔案,就必須在restore命令中使用check readonly引數,如下所示:
RMAN> restore database check readonly;
需要注意的是,執行recover tablespace或recover datafile命令時,RMAN的工作情況是不一樣的。使用
上述任何一種recover命令時,不管表空間是否為只讀狀態都會執行恢復操作。
==============================================================================================
==============================================================================================
建立恢復只讀表空間測試環境:
SQL> alter tablespace ts read only;
Tablespace altered.
SQL>
RMAN> configure backup optimization on; 歸檔檔案只備份一次,只讀表空間備份2次
RMAN> backup database plus archivelog;
RMAN> backup database plus archivelog;
RMAN> backup database plus archivelog;
Starting backup at 07-APR-10
using channel ORA_DISK_1
skipping datafile 5; already backed up 2 time(s)
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/zydev/system01.dbf
input datafile fno=00002 name=/u02/oradata/zydev/undotbs01.dbf
input datafile fno=00003 name=/u02/oradata/zydev/sysaux01.dbf
input datafile fno=00004 name=/u02/oradata/zydev/users01.dbf
channel ORA_DISK_1: starting piece 1 at 07-APR-10
channel ORA_DISK_1: finished piece 1 at 07-APR-10
... 省略 ...
SQL> shutdown abort; -- 關閉後,刪除相關資料檔案
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 385876536 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u02/oradata/zydev/sysaux01.dbf'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
執行恢復:
恢復1. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN和sqlplus匯合操作。
恢復2. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN操作
恢復3. 恢復所有的需要的資料檔案,然後開啟資料庫
恢復4. restore database check readonly,恢復所有的資料庫檔案,然後開啟資料庫
恢復5. 一般處理
#####################################################################
恢復1. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN和sqlplus匯合操作。
RMAN> restore database; -- 這裡的只讀表空間的資料檔案不進行恢復
RMAN> recover database;
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/07/2010 19:06:14
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/zydev/ts01.dbf'
RMAN>
SQL> alter database datafile '/u02/oradata/zydev/ts01.dbf' offline;
SQL> alter database open;
退出rman連線,重新聯入
RMAN> restore datafile '/u02/oradata/zydev/ts01.dbf';
RMAN> recover datafile '/u02/oradata/zydev/ts01.dbf';
如果不重新進行連線,將會遭遇如下錯誤:
RMAN> restore datafile '/u02/oradata/zydev/ts01.dbf';
Starting restore at 07-APR-10
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/07/2010 19:36:49
RMAN-20021: database not set
RMAN-06010: error while looking up datafile: 5
RMAN>
SQL> alter database datafile '/u02/oradata/zydev/ts01.dbf' online;
SQL> alter database open;
SQL>
#####################################################################
恢復2. 將只讀表空間資料檔案離線,先行開啟資料庫,由RMAN操作
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/07/2010 19:30:55
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/zydev/ts01.dbf'
RMAN> sql 'alter database datafile 5 offline';
RMAN> alter database open;
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> sql 'alter database datafile 5 online';
#####################################################################
恢復3. 恢復所有的需要的資料檔案,然後開啟資料庫
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/07/2010 19:48:31
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u02/oradata/zydev/ts01.dbf'
RMAN> restore datafile 5;
RMAN> recover datafile 5;
RMAN> alter database open;
#####################################################################
恢復4. restore database check readonly,恢復所有的資料庫檔案,然後開啟資料庫
RMAN> restore database check readonly;
Starting restore at 07-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /u02/oradata/zydev/ts01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T184537_5vrrll39_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T184537_5vrrll39_.bkp tag=TAG20100407T184537
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/zydev/system01.dbf
restoring datafile 00002 to /u02/oradata/zydev/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/zydev/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/zydev/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T185426_5vrs331q_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ZYDEV/backupset/2010_04_07/o1_mf_nnndf_TAG20100407T185426_5vrs331q_.bkp tag=TAG20100407T185426
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 07-APR-10
RMAN> recover database;
RMAN> alter database open;
#####################################################################
恢復5. 一般處理
SQL> set linesize 180
SQL> col name format a50
SQL> col error format a20
SQL> select file#, name , error from v$dbfile JOIN v$recover_file USING (file#);
FILE# NAME ERROR
---------- -------------------------------------------------- --------------------
4 /u02/oradata/zydev/users01.dbf FILE NOT FOUND
5 /u02/oradata/zydev/ts01.dbf FILE NOT FOUND
SQL>
RMAN> restore datafile 4,5;
RMAN> recover datafile 4,5;
RMAN> alter database open;
source:http://ctchina.blog.sohu.com/147863974.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/27036311/viewspace-1425105/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Check database status in RACDatabase
- Cold backup and restore the entire databaseRESTDatabase
- How to Perform a Health Check on the DatabaseORMDatabase
- How to check Database corrupt BlockDatabaseBloC
- check database patch with opatch toolsDatabase
- Crunchy PostgreSQL database restore via pgo commandSQLDatabaseRESTGo
- rman restore database(spfile,controlfile, datafile)RESTDatabase
- restore database報錯ora-19573RESTDatabase
- How to Restore the Database Using AMDU after Diskgroup CorruptionRESTDatabase
- How to restore and recover a database from an RMAN backup_881395.1RESTDatabase
- Manually Backup - Restore or Clone a Database to Another Node [ID 562556.1]RESTDatabase
- RMAN restore validate database報ORA-19693RESTDatabase
- How to check whether the current database in using Oracle optionsDatabaseOracle
- Query to Check SP/Table/Trigger Exists in Database or notDatabase
- How to Create or Remove Restore Point on Standby database (文件 ID 1672977.1)REMRESTDatabase
- HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another NoRESTDatabase
- PL/SQL package SYS.DBMS_BACKUP_RESTORE version 19.16.00.00 in TARGET database isSQLPackageRESTDatabase
- Restore standby database controlfile in ASM (refreshing standby controlfile)RESTDatabaseASM
- RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)RESTDatabaseORMROSSQLServerError
- 【健康檢查】How to Perform a Health Check on the Database (Doc ID 122669.1)ORMDatabase
- SQLSERVER 2012從2000還原失敗:Restore of database failedSQLServerRESTDatabaseAI
- Oracle OCP 1Z0 053 Q390(Flashback database&Named restore point)OracleDatabaseREST
- <input> readonly屬性
- guarantee restore points-Flashback after RMAN restoreREST
- canvas restore()CanvasREST
- RESTORE POINTREST
- restore database報RMAN-06026和RMAN-06023錯誤RESTDatabase
- HowTo Restore RMAN Disk backups of RAC Database to Single Instance On Another Node [ID 415579.1]RESTDatabase
- jquery-disabled和readonlyjQuery
- readonly和disabled區別
- Oracle OCP 1Z0 053 Q430(Transfer Database&DBMS_TDB.CHECK_DB)OracleDatabase
- Restore ArchivelogRESTHive
- onclick="return check()" 和 onclick="check()" 區別
- 資料庫恢復(database restore)之兵不血刃——半小時恢復客戶資料庫資料庫DatabaseREST
- db_recovery_file_dest_size, v$flashback_database_logfile,v$restore_point引發的血案.DatabaseREST
- c#之readonly小記C#
- Readonly和Disabled的區別
- DOCKER特性 - LIVE RESTOREDockerREST