/* *************************** **********************************/

--修改非一致性备份脚本/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.