整庫(whole)備份-非一致性整庫備份

--檢視當前所有聯機資料檔案是否處於備份狀態

sys@TESTDB11>select * from v$backup;

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE                  0

         5 NOT ACTIVE                  0

         6 NOT ACTIVE            2611963 08-AUG-13

6 rows selected.

--檢視USERS表空間的資料檔案

sys@TESTDB11>select file_id, file_name from dba_data_files where tablespace_name = 'USERS';

   FILE_ID FILE_NAME

---------- --------------------------------------------------

         4 /u01/app/oracle/oradata/TestDB11/users01.dbf

--讓USERS表空間進入備份狀態

sys@TESTDB11>alter tablespace users begin backup;

Tablespace altered.

-- 4號檔案的狀態改變為ACTIVE,即進入了備份狀態

sys@TESTDB11>select * from v$backup;

     FILE#  STATUS                CHANGE#    TIME

---------- ------------------ ---------- ---------

         1   NOT ACTIVE                     0

         2   NOT ACTIVE                     0

         3   NOT ACTIVE                     0

         4   ACTIVE                   2652321  09-AUG-13

         5   NOT ACTIVE                     0

         6   NOT ACTIVE              2611963  08-AUG-13

6 rows selected.

--建立備份目錄

[oracle@S1011:/backup]$ mkdir /pooldisk02/bacup01/inconsistent

[oracle@S1011:/backup]$ cp /u01/app/oracle/oradata/TestDB11/users01.dbf /pooldisk02/bacup01/inconsistent

sys@TESTDB11>alter tablespace users end backup;

Tablespace altered.

sys@TESTDB11>select * from v$backup;

     FILE# STATUS                CHANGE# TIME

---------- ------------------ ---------- ---------

         1 NOT ACTIVE                  0

         2 NOT ACTIVE                  0

         3 NOT ACTIVE                  0

         4 NOT ACTIVE            2652321 09-AUG-13

         5 NOT ACTIVE                  0

         6 NOT ACTIVE            2611963 08-AUG-13

6 rows selected.

--建立非一致備份指令碼/export/home/oracle/backup_script/backup02.sql

set serveroutput on

set feedback off

spool /tmp/backup.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/backup01/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/backup.sql

--backup control file

alter database backup controlfile to '/pooldisk02/backup01/inconsistent/control01.bak' reuse;

--執行/export/home/oracle/backup_script/backup02.sql進行備份

sys@TESTDB11>@/export/home/oracle/backup_script/backup02.sql

alter tablespace SYSTEM begin backup;

!cp /u01/app/oracle/oradata/TestDB11/system01.dbf /backup/inconsistent_backup/

alter tablespace SYSTEM end backup;

alter tablespace SYSAUX begin backup;

!cp /u01/app/oracle/oradata/TestDB11/sysaux01.dbf /backup/inconsistent_backup/

alter tablespace SYSAUX end backup;

alter tablespace UNDOTBS1 begin backup;

!cp /u01/app/oracle/oradata/TestDB11/undotbs01.dbf /backup/inconsistent_backup/

alter tablespace UNDOTBS1 end backup;

alter tablespace USERS begin backup;

!cp /u01/app/oracle/oradata/TestDB11/users01.dbf /backup/inconsistent_backup/

alter tablespace USERS end backup;

alter tablespace EXAMPLE begin backup;

!cp /u01/app/oracle/oradata/TestDB11/example01.dbf /backup/inconsistent_backup/

alter tablespace EXAMPLE end backup;

alter tablespace ROTBS begin backup;

!cp /u01/app/oracle/oradata/TestDB11/rotbs01.dbf /backup/inconsistent_backup/

alter tablespace ROTBS 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.