第九章： 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;
ID
----------
1
2
3
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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>