ORACLE使用RMAN对SYSTEM表空间进行介质恢复

注意：数据库应该支持在归档模式。

1.对SYSTEMG表空间做一个备份

[oracle@bys001 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Sep 17 10:42:18 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BYS1 (DBID=3957527513)
RMAN> list backup;
using target database control file instead of recovery catalog
specification does not match any backup in the repository

RMAN> backup tablespace system;
Starting backup at 17-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/oradata/bys1/system01.dbf
channel ORA_DISK_1: starting piece 1 at 17-SEP-13
channel ORA_DISK_1: finished piece 1 at 17-SEP-13
piece handle=/u01/flash_recovery_area/BYS1/backupset/2013_09_17/o1_mf_nnndf_TAG20130917T104438_93hjjqph_.bkp tag=TAG20130917T104438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-SEP-13
channel ORA_DISK_1: finished piece 1 at 17-SEP-13
piece handle=/u01/flash_recovery_area/BYS1/backupset/2013_09_17/o1_mf_ncsnf_TAG20130917T104438_93hjok2q_.bkp tag=TAG20130917T104438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 17-SEP-13
RMAN> exit
##################################################################################################

2.创建一个表空间，并在此表空间上建表

BYS@bys1>select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
BYS@bys1>select * from user_role_privs;
USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
BYS                            DBA                            NO  YES NO

建表空间和表
BYS@bys1>create tablespace rmantest datafile '/u01/oradata/bys1/rmantest.dbf' size 10m;
BYS@bys1>create table test5 tablespace rmantest as select * from dba_objects where 1=0;
col file_name for a40
BYS@bys1>select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files;
FILE_NAME                                TABLESPACE_NAME                         M
---------------------------------------- ------------------------------ ----------
/u01/oradata/bys1/example01.dbf          EXAMPLE                               100
/u01/oradata/bys1/rmantest.dbf           RMANTEST                               10
/u01/oradata/bys1/sysaux01.dbf           SYSAUX                                620
/u01/oradata/bys1/system01.dbf           SYSTEM                                690
/u01/oradata/bys1/temp01.dbf             TEMP                                  771
/u01/oradata/bys1/undotbs01.dbf          UNDOTBS1                              125
/u01/oradata/bys1/users01.dbf            USERS                             1703.75
BYS@bys1>select table_name,tablespace_name from user_tables where table_name='TEST5';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEST5                          RMANTEST
BYS@bys1>select df.tablespace_name,df.sum_df_m as space_m,df.sum_df_m-fs.sum_fs_m as used_m,fs.sum_fs_m as free_space,to_char(trunc((df.sum_df_m-fs.sum_fs_m)/df.sum_df_m,2)*100) as "used_%" from (select tablespace_name,sum(bytes/1024/1024) as sum_df_m from dba_data_files group by tablespace_name) df,(select tablespace_name,sum(bytes/1024/1024) as sum_fs_m from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name  and df.tablespace_name='RMANTEST';
TABLESPACE_NAME                   SPACE_M     USED_M FREE_SPACE used_%
------------------------------ ---------- ---------- ---------- ----------
RMANTEST                               10     1.0625     8.9375 10
BYS@bys1>exit
################################################################

3.模拟SYSTEM表空间故障--这里是把SYSTEM表空间的数据文件改名

发现此时数据库还是正常运行，并且可以做DML操作。但是DDL或涉及数据字典表的操作会报错。--也验证了删除系统表空间的数据文件数据库并不会SHUTDOWN.
使用shutdown immediate;关闭数据库时因为需要向数据文件中写入CHECKPOING相关信息，所以报错。使用ABORT选项关闭数据库。
此时打开数据库会报错，无法锁定数据文件。此时数据库是MOUNT状态
[oracle@bys001 bys1]$ pwd
/u01/oradata/bys1
[oracle@bys001 bys1]$ ls
control01.ctl  redo01.log  redo03.log    sysaux01.dbf  temp01.dbf     users01.dbf
example01.dbf  redo02.log  rmantest.dbf  system01.dbf  undotbs01.dbf
[oracle@bys001 bys1]$ mv system01.dbf system01.dbfa
[oracle@bys001 bys1]$ ls
control01.ctl  redo01.log  redo03.log    sysaux01.dbf   temp01.dbf     users01.dbf
example01.dbf  redo02.log  rmantest.dbf  system01.dbfa  undotbs01.dbf
[oracle@bys001 bys1]$ sqlplus / as sysdba

SYS@bys1>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@bys1>conn bys/bys
Connected.
BYS@bys1>select * from test5;
no rows selected
BYS@bys1>insert into test5 select * from dba_objects where rownum<5;
4 rows created.
BYS@bys1>commit;

Commit complete.

此时查询新建的表不报错：

BYS@bys1>select count(*) from test5;
  COUNT(*)
----------

         4

建表时报错--涉及数据字典，数据字典在系统表空间

BYS@bys1>create table test6 as select * from emp;
create table test6 as select * from emp
                                    *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys1/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys1/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


BYS@bys1>select * from tab;
select * from tab
              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys1/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys1/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

BYS@bys1>conn / as sysdba
Connected.
SYS@bys1>shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/oradata/bys1/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@bys1>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@bys1>shutdown abort;
ORACLE instance shut down.

SYS@bys1>startup;
ORACLE instance started.
Total System Global Area  631914496 bytes
Fixed Size                  1338364 bytes
Variable Size             260047876 bytes
Database Buffers          364904448 bytes
Redo Buffers                5623808 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/oradata/bys1/system01.dbf'

SYS@bys1>select status from v$instance;
STATUS
------------
MOUNTED
######################################################

4，使用RMAN恢复系统表空间

RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    596.67M    DISK        00:02:19     17-SEP-13      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20130917T104438
        Piece Name: /u01/flash_recovery_area/BYS1/backupset/2013_09_17/o1_mf_nnndf_TAG20130917T104438_93hjjqph_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1646291    17-SEP-13 /u01/oradata/bys1/system01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.64M      DISK        00:00:10     17-SEP-13      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20130917T104438
        Piece Name: /u01/flash_recovery_area/BYS1/backupset/2013_09_17/o1_mf_ncsnf_TAG20130917T104438_93hjok2q_.bkp
  SPFILE Included: Modification time: 16-SEP-13
  SPFILE db_unique_name: BYS1
  Control File Included: Ckp SCN: 1646360      Ckp time: 17-SEP-13

RMAN> restore tablespace system;

Starting restore at 17-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/bys1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/flash_recovery_area/BYS1/backupset/2013_09_17/o1_mf_nnndf_TAG20130917T104438_93hjjqph_.bkp
channel ORA_DISK_1: piece handle=/u01/flash_recovery_area/BYS1/backupset/2013_09_17/o1_mf_nnndf_TAG20130917T104438_93hjjqph_.bkp tag=TAG20130917T104438
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 17-SEP-13

RMAN> recover tablespace system;
Starting recover at 17-SEP-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 17-SEP-13

RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
########################################################################

6.登陆数据库，查看数据是否正常

[oracle@bys001 bys1]$ sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@bys1>select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SYS@bys1>conn bys/bys
Connected.
可以看到，备份的SYSTEM表空间之后的创建表空间、建表插入数据的操作产生的数据都正常。
BYS@bys1>select count(*) from test5;
  COUNT(*)
----------
         4
BYS@bys1>col file_name for a40
BYS@bys1>select file_name,tablespace_name,bytes/1024/1024 m from dba_data_files union select file_name,tablespace_name,bytes/1024/1024 m from dba_temp_files;
FILE_NAME                                TABLESPACE_NAME                         M
---------------------------------------- ------------------------------ ----------
/u01/oradata/bys1/example01.dbf          EXAMPLE                               100
/u01/oradata/bys1/rmantest.dbf           RMANTEST                               10
/u01/oradata/bys1/sysaux01.dbf           SYSAUX                                620
/u01/oradata/bys1/system01.dbf           SYSTEM                                690
/u01/oradata/bys1/temp01.dbf             TEMP                                  771
/u01/oradata/bys1/undotbs01.dbf          UNDOTBS1                              125
/u01/oradata/bys1/users01.dbf            USERS                             1703.75