ORACLE備份&恢復案例(4)

4.2.2 RMAN備份方案

RMAN也可以進行聯機備份，而且備份與恢復方法將比OS備份更簡單可靠。

1、連線資料庫，建立測試表並插入記錄

SQL*Plus: Release 8.1.6.0.0 - Production on Tue May 6 13:46:32 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

SQL> connect internal/password as sysdba;

Connected.

SQL> create table test(a int) tablespace users;

Table created

SQL> insert into test values(1);

1 row inserted

SQL> commit;

Commit complete

2、備份資料庫表空間users

C:>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN> run{

2> allocate channel c1 type disk;

3> backup tag 'tsuser' format 'd:backuptsuser_%u_%s_%p'

4> tablespace users;

5> release channel c1;

6> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=16 devtype=DISK

RMAN-03022: compiling command: backup

RMAN-03025: performing implicit partial resync of recovery catalog

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03023: executing command: backup

RMAN-08008: channel c1: starting full datafile backupset

RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03

RMAN-08010: channel c1: specifying datafile(s) in backupset

RMAN-08522: input datafile fno=00003 name=D:ORACLEORADATATESTUSER01.DBF

RMAN-08013: channel c1: piece 1 created

RMAN-08503: piece handle=D:BACKUPTSUSER_05EN93AC_5_1 comment=NONE

RMAN-08525: backup set complete, elapsed time: 00:00:01

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

3、繼續在測試表中插入記錄

SQL> insert into test values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test;

A

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

1

2

SQL> alter system switch logfile;

System altered.

SQL>r

1* alter system switch logfile;

System altered.

4、關閉資料庫，模擬丟失資料檔案

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down

C:>del D:ORACLEORADATATESTUSER01.DBF

5、啟動資料庫，檢查錯誤

SQL> startup

ORACLE instance started.

Total System Global Area 102020364 bytes

Fixed Size 70924 bytes

Variable Size 85487616 bytes

Database Buffers 16384000 bytes

Redo Buffers 77824 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: 'D:ORACLEORADATATESTUSER01.DBF'

6、先開啟資料庫

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

7、恢復該表空間

恢復指令碼可以是恢復單個資料檔案

run{

allocate channel c1 type disk;

restore datafile 3;

recover datafile 3;

sql 'alter database datafile 3 online';

release channel c1;

}

也可以是,恢復表空間

run{

allocate channel c1 type disk;

restore tablespace users;

recover tablespace users;

sql 'alter database datafile 3 online';

release channel c1;

}

過程如下：

C:>rman

Recovery Manager: Release 8.1.6.0.0 - Production

RMAN> connect rcvcat rman/rman@back

RMAN-06008: connected to recovery catalog database

RMAN> connect target internal/virpure

RMAN-06005: connected to target database: TEST (DBID=1788174720)

RMAN> run{

2> allocate channel c1 type disk;

3> restore datafile 3;

4> recover datafile 3;

5> sql 'alter database datafile 3 online';

6> release channel c1;

7> }

RMAN-03022: compiling command: allocate

RMAN-03023: executing command: allocate

RMAN-08030: allocated channel: c1

RMAN-08500: channel c1: sid=13 devtype=DISK

RMAN-03022: compiling command: restore

RMAN-03022: compiling command: IRESTORE

RMAN-03023: executing command: IRESTORE

RMAN-08016: channel c1: starting datafile backupset restore

RMAN-08502: set_count=5 set_stamp=494177612 creation_time=16-MAY-03

RMAN-08089: channel c1: specifying datafile(s) to restore from backup set

RMAN-08523: restoring datafile 00003 to D:ORACLEORADATATESTUSER01.DBF

RMAN-08023: channel c1: restored backup piece 1

RMAN-08511: piece handle=D:BACKUPTSUSER_05EN93AC_5_1 tag=TSUSER params=NULL

RMAN-08024: channel c1: restore complete

RMAN-03023: executing command: partial resync

RMAN-08003: starting partial resync of recovery catalog

RMAN-08005: partial resync complete

RMAN-03022: compiling command: recover

RMAN-03022: compiling command: recover(1)

RMAN-03022: compiling command: recover(2)

RMAN-03022: compiling command: recover(3)

RMAN-03023: executing command: recover(3)

RMAN-08054: starting media recovery

RMAN-03022: compiling command: recover(4)

RMAN-06050: archivelog thread 1 sequence 332 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00332.ARC

RMAN-06050: archivelog thread 1 sequence 333 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00333.ARC

RMAN-06050: archivelog thread 1 sequence 334 is already on disk as file D:ORACLEORADATATESTARCHIVETESTT001S00334.ARC

RMAN-03023: executing command: recover(4)

RMAN-08515: archivelog filename=D:ORACLEORADATATESTARCHIVETESTT001S00332.ARC thread=1 sequence=332

RMAN-08055: media recovery complete

RMAN-03022: compiling command: sql

RMAN-06162: sql statement: alter database datafile 3 online

RMAN-03023: executing command: sql

RMAN-03022: compiling command: release

RMAN-03023: executing command: release

RMAN-08031: released channel: c1

RMAN>

8、檢查資料是否完整

SQL> alter database open;

Database altered.

SQL> select * from test;

A

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

1

2

說明：

1、RMAN也可以實現單個表空間或資料檔案的恢復，恢復過程可以在mount下或open方式下，如果在open方式下恢復，可以減少down機時間

2、如果損壞的是一個資料檔案，建議offline並在open方式下恢復

3、這裡可以看到，RMAN進行資料檔案與表空間恢復的時候，程式碼都比較簡單，而且能保證備份與恢復的可靠性，所以建議採用RMAN的備份與恢復

[@more@]