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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1153194/