Oracle 备份与恢复学习笔记（9

第九章： Rman的完全恢复

案例3：

-----------恢复表空间（open状态）

1）测试环境

07:47:00 SQL> insert into emp1 select * from emp where rownum <3;

2 rows created.

07:47:14 SQL> commit;

Commit complete.

07:47:17 SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

07:47:20 SQL> conn /as sysdba

Connected.

07:47:23 SQL>

[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf

07:47:23 SQL> alter system flush buffer_cache;

System altered.

07:47:41 SQL> conn scott/tiger

Connected.

07:47:44 SQL>

07:47:44 SQL> select * from emp1;

select * from emp1

ERROR at line 1:

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

07:47:48 SQL>

2）恢复表空间

RMAN> run {

2> sql'alter database datafile 2 offline';

3> restore tablespace users;

4> recover tablespace users;

5> sql 'alter database datafile 2 online';

6> }

3）验证

07:47:48 SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

07:50:31 SQL>

案例4：

---------------新建表空间没有备份，datafile 被破坏

1）测试环境

07:52:16 SQL> create tablespace lx

07:52:24 2 datafile '/u01/app/oracle/oradata/prod/lx01.dbf' size 10m;

Tablespace created.

07:52:38 SQL> conn scott/tiger

Connected.

07:52:40 SQL>

07:52:40 SQL> create table lx01 (id int) tablespace lx;

Table created.

07:52:49 SQL> insert into lx01 values (1);

1 row created.

07:52:55 SQL> insert into lx01 values (2);

1 row created.

07:52:57 SQL> insert into lx01 values (3);

1 row created.

07:52:59 SQL> commit;

Commit complete.

07:53:00 SQL> select * from lx01;

----------

07:53:03 SQL>

07:53:03 SQL> conn /as sysdba

Connected.

07:53:16 SQL>

07:53:16 SQL> shutdown abort

ORACLE instance shut down.

07:53:19 SQL>

[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/lx01.dbf

2）启动database

07:53:19 SQL> startup

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 79693200 bytes

Database Buffers 230686720 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 10: '/u01/app/oracle/oradata/prod/lx01.dbf'

07:54:15 SQL> select file#,error from v$recover_file;

FILE# ERROR

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

10 FILE NOT FOUND

07:54:25 SQL>

3）利用rman 恢复

RMAN> run {

2> sql 'alter database datafile 10 offline';

3> alter database open;

4> restore datafile 10;

5> recover datafile 10;

6> sql'alter database datafile 10 online';

7> }

using target database control file instead of recovery catalog

sql statement: alter database datafile 10 offline

database opened

Starting restore at 18-AUG-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

creating datafile fno=10 name=/u01/app/oracle/oradata/prod/lx01.dbf ；自动建立新的lx01.dbf 文件

restore not done; all files readonly, offline, or already restored

Finished restore at 18-AUG-11

Starting recover at 18-AUG-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 18-AUG-11

sql statement: alter database datafile 10 online

RMAN>

案例5：

--------利用image （镜像备份）恢复数据

1）建立镜像备份

RMAN> delete backup;

RMAN> list backup;

RMAN> backup as copy datafile 2 format '/disk1/rman/prod/users_%s.bak';

RMAN> list copy of datafile 2;

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

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

19 2 A 18-AUG-11 1383523 18-AUG-11 /disk1/rman/prod/users_38.bak

2）测试环境

07:59:17 SQL> conn scott/tiger

Connected.

07:59:20 SQL>

07:59:20 SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

07:59:27 SQL> insert into emp1 select * from emp1;

2 rows created.

07:59:46 SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

07:59:48 SQL> commit;

Commit complete.

07:59:49 SQL> conn /as sysdba

Connected.

07:59:52 SQL>

07:59:52 SQL> shutdown abort

ORACLE instance shut down.

[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf

3）启动数据库

07:59:56 SQL> startup

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 79693200 bytes

Database Buffers 230686720 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'

08:00:37 SQL> select file#,error from v$recover_file;

FILE# ERROR

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

2 FILE NOT FOUND

08:00:48 SQL>

4）利用rman恢复

[oracle@work ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 18 08:01:15 2011

connected to target database: PROD (DBID=170319990, not open)

RMAN> run {

2> restore datafile 2;

3> recover datafile 2;

4> alter database open;

5> }

Starting restore at 18-AUG-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: restoring datafile 00002

input datafile copy recid=19 stamp=759484683 filename=/disk1/rman/prod/users_38.bak

destination for restore of datafile 00002: /u01/app/oracle/oradata/prod/users01.dbf

channel ORA_DISK_1: copied datafile copy of datafile 00002

output filename=/u01/app/oracle/oradata/prod/users01.dbf recid=20 stamp=759484903

Finished restore at 18-AUG-11

Starting recover at 18-AUG-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 18-AUG-11

database opened

RMAN>

5）验证：

08:02:54 SQL> select * from scott.emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

08:02:59 SQL>

案例6：

----------利用增量备份进行恢复

1）建立增量备份

-------利用image 备份作为增量备份的0 级备份

RMAN> copy datafile 2 to '/disk1/rman/prod/users_%s.bak';

2）建立2 级差异备份

--------测试环境

08:05:52 SQL> conn scott/tiger

Connected.

08:05:58 SQL>

08:05:58 SQL> insert into emp1 select * from emp1;

4 rows created.

08:06:00 SQL> /

8 rows created.

08:06:01 SQL> commit;

Commit complete.

08:06:03 SQL>

RMAN> backup incremental level 2 format '/disk1/rman/prod/users_2_%s.bak' datafile 2;

RMAN> list backup of datafile 2;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

37 Incr 2 528.00K DISK 00:00:00 18-AUG-11

BP Key: 37 Status: AVAILABLE Compressed: NO Tag: TAG20110818T080650

Piece Name: /disk1/rman/prod/users_2_42.bak

List of Datafiles in backup set 37

File LV Type Ckp SCN Ckp Time Name

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

2 2 Incr 1404665 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf

3）建立 2级差异备份

08:06:03 SQL> insert into emp1 select * from emp1;

16 rows created.

08:07:49 SQL> /

32 rows created.

08:07:51 SQL> commit;

Commit complete.

08:07:53 SQL>

RMAN> backup incremental level 2 format '/disk1/rman/prod/users_2_%s.bak' datafile 2;

4）建立累计增量备份

08:07:53 SQL> insert into emp1 select * from emp1;

64 rows created.

08:08:55 SQL> /

128 rows created.

08:08:55 SQL> commit;

Commit complete.

08:08:57 SQL>

RMAN> backup incremental level 1 cumulative format '/disk1/rman/prod/users_1cu_%s.bak' datafile 2;

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

41 Incr 1 528.00K DISK 00:00:00 18-AUG-11

BP Key: 41 Status: AVAILABLE Compressed: NO Tag: TAG20110818T080942

Piece Name: /disk1/rman/prod/users_1cu_46.bak

List of Datafiles in backup set 41

File LV Type Ckp SCN Ckp Time Name

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

2 1 Incr 1404815 18-AUG-11 /u01/app/oracle/oradata/prod/users01.dbf

--------------恢复

1）测试环境

08:08:57 SQL> select count(*) from emp1;

COUNT(*)

----------

256

08:10:33 SQL> shutdown abort

[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/users01.dbf

2）启动数据库

08:10:52 SQL> startup

ORACLE instance started.

Total System Global Area 314572800 bytes

Fixed Size 1219184 bytes

Variable Size 79693200 bytes

Database Buffers 230686720 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 2: '/u01/app/oracle/oradata/prod/users01.dbf'

08:11:35 SQL> select file#,error from v$recover_file;

FILE# ERROR

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

2 FILE NOT FOUND

3）恢复数据文件

RMAN> run {

2> restore datafile 2;

3> recover datafile 2;

4> alter database open;

5> }

Starting restore at 18-AUG-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u01/app/oracle/oradata/prod/users01.dbf ；先用0级restore

channel ORA_DISK_1: reading from backup piece /disk1/rman/prod/users_1cu_46.bak ；再用累积备份restore

channel ORA_DISK_1: restored backup piece 1

piece handle=/disk1/rman/prod/users_1cu_46.bak tag=TAG20110818T080942

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 18-AUG-11

Starting recover at 18-AUG-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 18-AUG-11

database opened

4）验证

08:14:01 SQL> select count(*) from scott.emp1;

COUNT(*)

----------

256

08:14:08 SQL>

----------------将数据文件恢复到新的位置

01:27:08 SQL> startup

ORACLE instance started.

Total System Global Area 440401920 bytes

Fixed Size 1219904 bytes

Variable Size 213910208 bytes

Database Buffers 222298112 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 7: '/u01/app/oracle/oradata/test/lxtbs2.dbf'

01:27:17 SQL> select file# ,error from v$recover_file;

FILE# ERROR

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

7 FILE NOT FOUND

01:27:25 SQL>

[oracle@work ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 25 01:27:39 2011

connected to target database: TEST (DBID=2062097024, not open)

run {

startup force mount;

set newname for datafile 7 to '/disk1/oradata/test/lxtbs2.dbf';

restore datafile 7;

switch datafile 7;

recover datafile 7;

alter database open;

8> }

Oracle instance started

database mounted

Total System Global Area 440401920 bytes

Fixed Size 1219904 bytes

Variable Size 213910208 bytes

Database Buffers 222298112 bytes

Redo Buffers 2973696 bytes

executing command: SET NEWNAME

Starting restore at 25-OCT-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=37 devtype=DISK

datafile 7 is already restored to file /disk1/oradata/test/lxtbs2.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 25-OCT-11

datafile 7 switched to datafile copy

input datafile copy recid=14 stamp=765423007 filename=/disk1/oradata/test/lxtbs2.dbf

Starting recover at 25-OCT-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 25-OCT-11

database opened

RMAN>

本文转自客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/792514，如需转载请自行联系原作者