Oracle 备份与恢复学习笔记(9_2)

第九章: 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>










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/792514,如需转载请自行联系原作者
上一篇:Oracle 备份与恢复学习笔记(12)


下一篇:Spring Cloud Alibaba 七天训练营(四)分布式服务调用