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


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