场景描述:
如果使用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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1153194/