oracle備份技術之oracle非一緻性備份（熱備份）

（1）編寫熱備份腳本（雙重spool）

set feedback off pagesize 0 heading off verify off linesize 100 trimspool on echo off time off

define bakdir='/disk1/backup/anny/hot_bak'

define bakscp='/disk1/backup/anny/hot_cmd.sql'

define spo='&bakdir/hot_bak.lst'

prompt ***spooling to &bakscp

set serveroutput on

spool &bakscp

prompt spool &spo

prompt alter system switch logfile;;

declare

cursor cur_tablespace is

select tablespace_name from dba_tablespaces where status <>'READ ONLY' and contents  not like '%TEMP%';

cursor  cur_datafile (tn varchar2) is

select file_name from dba_data_files where tablespace_name=tn;

begin

for ct in cur_tablespace loop

dbms_output.put_line('alter tablespace '||ct.tablespace_name ||' begin backup; ');

for cd in cur_datafile(ct.tablespace_name) loop

dbms_output.put_line('host cp '||cd.file_name||' &bakdir');

end loop;

dbms_output.put_line('alter tablespace '||ct.tablespace_name||' end backup;');

end loop;

end;

/

prompt archive log list;;

prompt spool off;;

spool off;

@&bakscp          ——一般先不執行該腳本，要先檢查有沒有錯

（2）執行上面的腳本，會生成一個/disk1/backup/anny/hot_cmd.sql腳本，用來進行冷備份的

[[email protected] anny]$cat hot_cmd.sql

spool /disk1/backup/anny/hot_bak/hot_bak.lst

alter system switch logfile;

alter tablespace SYSTEM begin backup;

host cp /u01/app/oracle/oradata/anny/system01.dbf /disk1/backup/anny/hot_bak

alter tablespace SYSTEM end backup;

alter tablespace SYSAUX begin backup;

host cp /u01/app/oracle/oradata/anny/sysaux01.dbf /disk1/backup/anny/hot_bak

alter tablespace SYSAUX end backup;

alter tablespace USERS begin backup;

host cp /u01/app/oracle/oradata/anny/user01.dbf /disk1/backup/anny/hot_bak

alter tablespace USERS end backup;

alter tablespace TEXT begin backup;

host cp /u01/app/oracle/oradata/anny/text01.dbf /disk1/backup/anny/hot_bak

alter tablespace TEXT end backup;

alter tablespace LX01 begin backup;

host cp /u01/app/oracle/oradata/anny/lx01.dbf /disk1/backup/anny/hot_bak

alter tablespace LX01 end backup;

alter tablespace UNDOTBS begin backup;

host cp /u01/app/oracle/oradata/anny/undotbs01.dbf /disk1/backup/anny/hot_bak

alter tablespace UNDOTBS end backup;

alter tablespace LX02 begin backup;

host cp /u01/app/oracle/oradata/anny/lx02.dbf /disk1/backup/anny/hot_bak

alter tablespace LX02 end backup;

alter tablespace LX03 begin backup;

host cp /u01/app/oracle/oradata/anny/lx03.dbf /disk1/backup/anny/hot_bak

alter tablespace LX03 end backup;

alter tablespace LX04 begin backup;

host cp /u01/app/oracle/oradata/anny/lx04.dbf /disk1/backup/anny/hot_bak

alter tablespace LX04 end backup;

alter tablespace INDEXES begin backup;

host cp /u01/app/oracle/oradata/anny/index01.dbf /disk1/backup/anny/hot_bak

alter tablespace INDEXES end backup;

archive log list;

spool off;

（3）執行上面的腳本hot_cmd.sql，又會生成一個清單檔案hot_bak.lst，以下是清單檔案的内容，也就是執行hot_cmd.sql的結果！

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /disk1/arch/anny

Oldest online log sequence     17

Next log sequence to archive   20

Current log sequence           20

***********************************至此，備份成功************************************

——檢視原始檔案的大小

——手工備份會備份database裡datafile的所有資料塊

SQL> col name for a50

SQL> select file#,name,bytes/1024/1024 "Size" from v$datafile;

1 /u01/app/oracle/oradata/anny/system01.dbf                 325

2 /u01/app/oracle/oradata/anny/lx02.dbf                      10

3 /u01/app/oracle/oradata/anny/sysaux01.dbf                 325

4 /u01/app/oracle/oradata/anny/user01.dbf                   200

5 /u01/app/oracle/oradata/anny/text01.dbf                   100

6 /u01/app/oracle/oradata/anny/lx01.dbf                      10

7 /u01/app/oracle/oradata/anny/undotbs01.dbf                110

8 /u01/app/oracle/oradata/anny/lx03.dbf                      10

9 /u01/app/oracle/oradata/anny/lx04.dbf                      10

10 /u01/app/oracle/oradata/anny/index01.dbf                  100

——檢視備份後檔案大小

[[email protected] hot_bak]$ls -lht

×üêy 2459138

-rw-r--r--   1 oracle   oinstall     243  3   19è  16:32 hot_bak.lst

-rw-r-----   1 oracle   oinstall    100M  3   19è  16:32 index01.dbf

-rw-r-----   1 oracle   oinstall     10M  3   19è  16:32 lx04.dbf

-rw-r-----   1 oracle   oinstall     10M  3   19è  16:32 lx03.dbf

-rw-r-----   1 oracle   oinstall     10M  3   19è  16:32 lx02.dbf

-rw-r-----   1 oracle   oinstall    110M  3   19è  16:32 undotbs01.dbf

-rw-r-----   1 oracle   oinstall     10M  3   19è  16:32 lx01.dbf

-rw-r-----   1 oracle   oinstall    100M  3   19è  16:32 text01.dbf

-rw-r-----   1 oracle   oinstall    200M  3   19è  16:32 user01.dbf

-rw-r-----   1 oracle   oinstall    325M  3   19è  16:32 sysaux01.dbf

-rw-r-----   1 oracle   oinstall    325M  3   19è  16:32 system01.dbf

通過對比，手工備份後大小和原檔案大小幾乎相等，RMAN備份不一樣，隻備份已經使用的塊。

oracle視訊教程請關注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html

轉載于:https://blog.51cto.com/19880614/1205323