一 表空间讲解
在上一篇文章(RMAN备份与恢复之控制文件丢失),我们讲解了控制文件丢失并且RMAN中有备份怎么处理,这篇文章讲解可脱机数据文件丢失的处理。
表空间是一种逻辑结构,表空间下可以存放数据文件。表空间有系统表空间(SYSTEM)、辅助表空间(SYSAUX)、用户表空间(USERS)、回退表空间(UNDOTBS1)、临时表空间(TEMP)、测试表空间(EXAMPLE)、数据表空间。其中系统表空间用于存储数据字典的信息,不能脱机,不能只读,也不能改名。辅助表空间用于存储一些工具以及一些常用的组件,10G开始引入,不能脱机,可以只读,可以改名。用户表空间用于存储用户数据的表空间,可以脱机,可以只读,可以改名。回退表空间用于存储DML操作的事务信息,不能脱机,不能只读,可以改名。临时表空间用于存放临时数据,不能脱机,不能只读,可以改名。数据表空间用于存放自定义数据,项目中我们一般创建数据表空间,数据存放在数据表空间,这是一种很好的做法,切忌把数据放在默认的USERS表空间。
至于有些表空间可以脱机,有些不可以。那是因为数据库在运行期间需要向这些表空间写入数据,比如系统表空间、辅助表空间、回退表空间、临时表空间,而用户表空间、测试表空间、数据表空间不同,只要没有对这些表空间进行操作,是可以脱机的。
二 备份与恢复可脱机数据文件讲解
备份与恢复可脱机数据文件,首先我们要使用RMAN做一个备份全库的操作,然后模拟USERS表空间丢失,此时我们查看数据会提示数据文件找不到。因为USERS表空间可以脱机,所以我们可以在SQL Plus中使USERS表空间离线,切记要先离线再进行下面的操作。因为在RMAN中我们有备份,所以我们可以在RMAN中对USERS表空间对应的数据文件编号进行还原,实际上后台就行进行了一个拷贝操作。还原成功后,我们需要对USERS表空间对应的数据文件编号进行恢复,等待恢复完成后,再在SQL Plus中使USERS表空间在线,所有的操作完成后,USERS表空间备份与恢复完成,我们可以对数据进行访问,测试是否恢复成功。需要注意的是,使表空间在线、离线、还原和恢复数据文件一些列的操作切记不要把编号弄错了,做这些操作应该确认USERS表空间的编号,默认是4号,但是由于后期的修改,USERS表空间数据文件的编号很不能不是4号。作为DBA,需要时刻保持谨慎!
三 模拟
Step1,RMAN中备份全库,并在SQL Plus确定数据的正确性
RMAN> BACKUP DATABASE; Starting backup at 12-DEC-13 using channel ORA_DISK_1 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/oracle/oradata/justdb/system01.dbf input datafile file number=00002 name=/u01/oracle/oradata/justdb/sysaux01.dbf input datafile file number=00003 name=/u01/oracle/oradata/justdb/undotbs01.dbf input datafile file number=00004 name=/u01/oracle/oradata/justdb/users01.dbf channel ORA_DISK_1: starting piece 1 at 12-DEC-13 channel ORA_DISK_1: finished piece 1 at 12-DEC-13 piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp tag=TAG20131212T095816 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25 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 12-DEC-13 channel ORA_DISK_1: finished piece 1 at 12-DEC-13 piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp tag=TAG20131212T095816 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 12-DEC-13 [oracle@orcl backupset]$ ll 2013_12_12/ total 1025556 -rw------- 1 oracle oinstall 9830400 Dec 12 09:58 o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp -rw------- 1 oracle oinstall 1040334848 Dec 12 09:58 o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp RMAN> LIST BACKUP; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 14 Full 992.13M DISK 00:00:19 12-DEC-13 BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20131212T095816 Piece Name: /u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp List of Datafiles in backup set 14 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/system01.dbf 2 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/sysaux01.dbf 3 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/undotbs01.dbf 4 Full 1088247 12-DEC-13 /u01/oracle/oradata/justdb/users01.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 15 Full 9.36M DISK 00:00:01 12-DEC-13 BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20131212T095816 Piece Name: /u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_ncsnf_TAG20131212T095816_9bl62lw2_.bkp SPFILE Included: Modification time: 12-DEC-13 SPFILE db_unique_name: JUSTDB Control File Included: Ckp SCN: 1088267 Ckp time: 12-DEC-13 SQL> CONN SCOTT/tiger; Connected. SQL> SELECT * FROM tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BONUS TABLE DEPT TABLE EMP TABLE SALGRADE TABLE
Step2,SQL Plus中删除USERS表空间,模拟USERS表空间丢失
SQL> HO mv /u01/oracle/oradata/justdb/users01.dbf /opt/learn/
Step 3,scott用户查看数据失败
SQL> CONN SCOTT/tiger; Connected. SQL> DESC dept; ERROR: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/u01/oracle/oradata/justdb/users01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3
Step4,SQL Plus中使第4号数据文件离线
SQL> CONN / AS SYSDBA; Connected. SQL> ALTER DATABASE DATAFILE 4 OFFLINE; Database altered.
Step5,RMAN中还原4号数据文件
RMAN> RESTORE DATAFILE 4; Starting restore at 12-DEC-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1 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 00004 to /u01/oracle/oradata/justdb/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp channel ORA_DISK_1: piece handle=/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/o1_mf_nnndf_TAG20131212T095816_9bl61rrn_.bkp tag=TAG20131212T095816 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 12-DEC-13
Step6,RMAN中恢复4号文件
RMAN> RECOVER DATAFILE 4; Starting recover at 12-DEC-13 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 12-DEC-13
Step7,SQL Plus中使4号数据文件在线
SQL> ALTER DATABASE DATAFILE 4 ONLINE; Database altered.
Step8,SQL Plus中查看数据
SQL> CONN SCOTT/tiger; Connected. SQL> DESC dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> SELECT * FROM dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
四 相关文章
我的邮箱:wgbno27@gmail.com 新浪微博:@jutdb 微信公众平台:JustOracle(微信号:justoracle) 数据库技术交流群:336882565(加群时验证 From CSDN XXX) All is well 2014年1月15日 By Larry Wen
![]() | ![]() ![]() |
@Wentasy 博文仅供参考,欢迎大家来访。如有错误之处,希望批评指正。原创博文如需转载请注明出处,谢谢 :) [CSDN博客] |