ORACLE使用RMAN对SYSTEM表空间进行介质恢复注意：数据库应该支持在归档模式。1.对SYSTEMG表空间做一个备份[o
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
