Oracle 11g 資料庫恢復：場景12: 兩套備份,節省還原時間
場景描述：
如果使用alter database rename file讓控制檔案指向備份的資料檔案，就可以不用進行還原，從而節省時間。
但之後，這個備份檔案就不是備份了，而是實際使用的資料檔案了。
通常是在具有2個備份的情況下，再做這個動作。
|
/* *************************** **********************************/ --修改非一致性備份指令碼/export/home/oracle/backu02.sql, 修改備份目錄 set serveroutput on set feedback off spool /tmp/backup02.sql
DECLARE CURSOR cur_df(p_ts VARCHAR2) IS SELECT file_name FROM dba_data_files WHERE tablespace_name = p_ts; v_backup_dir VARCHAR2(50) := '/pooldisk02/backup02/inconsistent/'; BEGIN FOR r_ts IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents != 'TEMPORARY' AND status = 'ONLINE') LOOP dbms_output.put_line('alter tablespace ' || r_ts.tablespace_name || ' begin backup;');
FOR r_df IN cur_df(r_ts.tablespace_name) LOOP dbms_output.put_line('!cp ' || r_df.file_name || ' ' || v_backup_dir); END LOOP;
dbms_output.put_line('alter tablespace ' || r_ts.tablespace_name || ' end backup;'); END LOOP; END; /
spool off set feedback on @/tmp/backup02.sql --backup control file alter database backup controlfile to '/pooldisk02/backup02/inconsistent_backup/control01.bak' reuse;
--執行備份 sys@TESTDB11>@/export/home/oracle/backup_cript/backup02.sql SP2-0310: unable to open file "/export/home/oracle/backup_cript/backup02.sql" sys@TESTDB11>@/export/home/oracle/backup_script/backup02.sql alter tablespace SYSTEM begin backup; !cp /oradata/TestDB11/system01.dbf /pooldisk02/backup02/inconsistent_backup/ alter tablespace SYSTEM end backup; alter tablespace SYSAUX begin backup; !cp /oradata/TestDB11/sysaux01.dbf /pooldisk02/backup02/inconsistent_backup/ alter tablespace SYSAUX end backup; alter tablespace UNDOTBS1 begin backup; !cp /oradata/TestDB11/undotbs01.dbf /pooldisk02/backup02/inconsistent_backup/ alter tablespace UNDOTBS1 end backup; alter tablespace USERS begin backup; !cp /oradata/TestDB11/users01.dbf /pooldisk02/backup02/inconsistent_backup/ alter tablespace USERS end backup; alter tablespace EXAMPLE begin backup; !cp /oradata/TestDB11/example01.dbf /pooldisk02/backup02/inconsistent_backup/ alter tablespace EXAMPLE end backup; alter tablespace NEWUNDOTBS begin backup; !cp /oradata/TestDB11/newundotbs01.dbf /pooldisk02/backup02/inconsistent_backup/ alter tablespace NEWUNDOTBS end backup;
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Database altered.
--檢視當前的資料檔案 sys@TESTDB11>select name from v$datafile;
NAME ---------------------------------------------------------------------------------------------------------------------------------------------------------------- /oradata/TestDB11/system01.dbf /oradata/TestDB11/sysaux01.dbf /oradata/TestDB11/undotbs01.dbf /oradata/TestDB11/users01.dbf /oradata/TestDB11/example01.dbf /oradata/TestDB11/newundotbs01.dbf
6 rows selected.
--檢視當前存在的備份（2份) sys@TESTDB11>!ls /backup/inconsistent control01.bak example01.dbf newundotbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
sys@TESTDB11>!ls /pooldisk02/backup02/inconsistent example01.dbf newundotbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
sys@TESTDB11>alter database backup controlfile to '/pooldisk02/backup02/inconsistent_backup/control01.bak' reuse;
Database altered.
sys@TESTDB11>!ls /pooldisk02/backup02/inconsistent_backup control01.bak example01.dbf newundotbs01.dbf sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf
--資料檔案損壞 sys@TESTDB11>!rm /oradata/TestDB11/*.dbf
--確定在控制檔案中重名稱檔案需要執行的命令 sys@TESTDB11>select length('/oradata/TestDB11/') from dual;
LENGTH('/ORADATA/TESTDB11/') ---------------------------- 18
1 row selected.
sys@TESTDB11>select substr(name, 19) from v$datafile;
SUBSTR(NAME,19) ---------------------------------------------------------------------------------------------------------------------------------------------------------------- system01.dbf sysaux01.dbf undotbs01.dbf users01.dbf example01.dbf newundotbs01.dbf
6 rows selected.
sys@TESTDB11>select 'alter database rename file ''' || name || ''' to ''/backup/inconsistent_backup/' || 2 substr(name, 19) || ''';' from v$datafile;
'ALTERDATABASERENAMEFILE'''||NAME||'''TO''/BACKUP/INCONSISTENT_BACKUP/'||SUBSTR(NAME,19)||''';' ---------------------------------------------------------------------------------------------------------------------------------------------------------------- alter database rename file '/oradata/TestDB11/system01.dbf' to '/backup/inconsistent_backup/system01.dbf'; alter database rename file '/oradata/TestDB11/sysaux01.dbf' to '/backup/inconsistent_backup/sysaux01.dbf'; alter database rename file '/oradata/TestDB11/undotbs01.dbf' to '/backup/inconsistent_backup/undotbs01.dbf'; alter database rename file '/oradata/TestDB11/users01.dbf' to '/backup/inconsistent_backup/users01.dbf'; alter database rename file '/oradata/TestDB11/example01.dbf' to '/backup/inconsistent_backup/example01.dbf'; alter database rename file '/oradata/TestDB11/newundotbs01.dbf' to '/backup/inconsistent_backup/newundotbs01.dbf';
6 rows selected.
--改變控制檔案中記錄的資料檔案的位置 alter database rename file '/oradata/TestDB11/system01.dbf' to '/backup/inconsistent_backup/system01.dbf'; alter database rename file '/oradata/TestDB11/sysaux01.dbf' to '/backup/inconsistent_backup/sysaux01.dbf'; alter database rename file '/oradata/TestDB11/undotbs01.dbf' to '/backup/inconsistent_backup/undotbs01.dbf'; alter database rename file '/oradata/TestDB11/users01.dbf' to '/backup/inconsistent_backup/users01.dbf'; alter database rename file '/oradata/TestDB11/example01.dbf' to '/backup/inconsistent_backup/example01.dbf'; alter database rename file '/oradata/TestDB11/newundotbs01.dbf' to '/backup/inconsistent_backup/newundotbs01.dbf';
sys@TESTDB11>select name from v$datafile;
NAME ---------------------------------------------------------------------------------------------------------------------------------------------------------------- /backup/inconsistent_backup/system01.dbf /backup/inconsistent_backup/sysaux01.dbf /backup/inconsistent_backup/undotbs01.dbf /backup/inconsistent_backup/users01.dbf /backup/inconsistent_backup/example01.dbf /backup/inconsistent_backup/newundotbs01.dbf
6 rows selected.
sys@TESTDB11>select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME ---------- ------- ------- --------------- ---------- --------- 1 ONLINE ONLINE 2497257 11-AUG-13 2 ONLINE ONLINE 2497284 11-AUG-13 3 ONLINE ONLINE 2497319 11-AUG-13 4 ONLINE ONLINE 2497336 11-AUG-13 5 ONLINE ONLINE 2497356 11-AUG-13 6 ONLINE ONLINE 2497374 11-AUG-13
6 rows selected.
--介質恢復，開庫 sys@TESTDB11>recover database; ORA-00279: change 2497257 generated at 08/11/2013 06:33:14 needed for thread 1 ORA-00289: suggestion : /archive2/1_87_813665348.dbf ORA-00280: change 2497257 for thread 1 is in sequence #87
Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 2498091 generated at 08/11/2013 06:36:43 needed for thread 1 ORA-00289: suggestion : /archive2/1_88_813665348.dbf ORA-00280: change 2498091 for thread 1 is in sequence #88
ORA-00279: change 2499018 generated at 08/11/2013 06:36:58 needed for thread 1 ORA-00289: suggestion : /archive2/1_89_813665348.dbf ORA-00280: change 2499018 for thread 1 is in sequence #89
ORA-00279: change 2500184 generated at 08/11/2013 06:53:19 needed for thread 1 ORA-00289: suggestion : /archive2/1_90_813665348.dbf ORA-00280: change 2500184 for thread 1 is in sequence #90
ORA-00279: change 2500225 generated at 08/11/2013 06:53:37 needed for thread 1 ORA-00289: suggestion : /archive2/1_91_813665348.dbf ORA-00280: change 2500225 for thread 1 is in sequence #91
ORA-00279: change 2500229 generated at 08/11/2013 06:53:41 needed for thread 1 ORA-00289: suggestion : /archive2/1_92_813665348.dbf ORA-00280: change 2500229 for thread 1 is in sequence #92
ORA-00279: change 2500233 generated at 08/11/2013 06:53:44 needed for thread 1 ORA-00289: suggestion : /archive2/1_93_813665348.dbf ORA-00280: change 2500233 for thread 1 is in sequence #93
Log applied. Media recovery complete.
sys@TESTDB11>alter database open;
Database altered. |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1153194/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 11g資料庫恢復：場景10：新建表空間沒有備份Oracle資料庫
- SQL Server 資料庫備份還原和資料恢復SQLServer資料庫資料恢復
- Oracle 11g 資料庫恢復：場景12續：將資料檔案恢復回原來正確的位置Oracle資料庫
- 「Oracle」Oracle 資料庫備份還原Oracle資料庫
- 【備份恢復】在 ARCHIVELOG 模式下執行資料庫還原和恢復操作（源庫備份源庫恢復）Hive模式資料庫
- 【備份恢復】下：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- 【備份恢復】上：還原和恢復資料庫（即將一個庫還原到另外一個庫）資料庫
- Oracle12c多租戶資料庫備份與恢復 - 備份表空間Oracle資料庫
- Oracle 11g RAN恢復-表空間在只讀時做了資料庫的備份Oracle資料庫
- 【備份恢復】從備份恢復資料庫資料庫
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- oracle資料庫的備份與恢復Oracle資料庫
- oracle資料恢復還原Oracle資料恢復
- 表空間級資料庫備份恢復資料庫
- 備份與恢復系列 九 丟失表空間資料檔案的還原與恢復
- 備份與恢復：polardb資料庫備份與恢復資料庫
- Oracle資料庫備份與恢復之RMANOracle資料庫
- Oracle資料庫的備份與恢復（轉）Oracle資料庫
- Oracle 資料庫的備份與恢復（轉）Oracle資料庫
- Oracle 11g 資料庫恢復-場景7：部分檔案損壞Oracle資料庫
- 【備份恢復】Oracle 資料備份與恢復微實踐Oracle
- 【備份恢復】閃回資料庫（四）基於可靠還原點閃回資料庫資料庫
- Oracle 11g資料庫恢復：場景11：資料檔案損壞,不能恢復到原來的位置, 恢復到新的路徑Oracle資料庫
- Oracle資料庫備份還原詳解XKUSOracle資料庫
- exp/imp備份與還原oracle資料庫Oracle資料庫
- Oracle12c多租戶資料庫備份與恢復 - 僅備份一個PDB資料庫Oracle資料庫
- Oracle 12c PDB的資料備份恢復Oracle
- Oracle12c多租戶資料庫備份與恢復 - 恢復一個PDBOracle資料庫
- Oracle12c多租戶資料庫備份與恢復 - 單獨恢復CDBOracle資料庫
- MSSQL 備份資料庫還原SQL資料庫
- Oracle12c多租戶資料庫備份與恢復 - 僅僅備份CDBOracle資料庫
- 【備份恢復】閃回資料庫（三）基於時間戳閃回資料庫資料庫時間戳
- oracle資料還原與備份Oracle
- 【備份恢復】noarchive模式下使用增量備份恢復資料庫Hive模式資料庫
- Oracle 11g 資料庫恢復-場景5：部分檔案損壞恢復,開庫狀態,低可用恢復方式Oracle資料庫
- Oracle 11g 資料庫恢復-場景4：部分檔案損壞恢復,開庫狀態, 高可用恢復方式Oracle資料庫
- [資料庫] Navicat for MySQL定時備份資料庫及資料恢復資料庫MySql資料恢復
- Oracle資料庫備份與恢復之三：OS備份/使用者管理的備份與恢復Oracle資料庫