[20171225]没有备份数据文件的恢复.txt
--//别人问的问题,增加了数据文件没有备份,如何恢复,实际上很简单,因为当前控制文件有记录建立时间只要从建立数据文件开始的--//归档日志都存在恢复是没有任何问题的.我仅仅介绍rman的恢复:1.环境:SCOTT@book> @&r/ver1PORT_STRING VERSION BANNER------------------------------ -------------- --------------------------------------------------------------------------------x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionCREATE TABLESPACE SUGAR DATAFILE '/mnt/ramdisk/book/sugar01.dbf' SIZE 6M AUTOEXTEND OFFLOGGINGONLINEEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTOFLASHBACK ON;SCOTT@book> create table tx tablespace sugar as select * from dba_objects where rownum<=1000;Table created.SCOTT@book> delete from tx where rownum<=10 ;10 rows deleted.SCOTT@book> commit ;Commit complete.SCOTT@book> select count(*) from tx ; COUNT(*)---------- 990SCOTT@book> alter system archive log current ;System altered.SCOTT@book> alter system archive log current ;System altered.SCOTT@book> alter system archive log current ;System altered.SCOTT@book> alter system archive log current ;System altered.2.测试:--//人为删除数据文件:$ rm /mnt/ramdisk/book/sugar01.dbf/bin/rm: remove regular file `/mnt/ramdisk/book/sugar01.dbf'? ySCOTT@book> select count(*) from tx ; COUNT(*)---------- 990SCOTT@book> alter system flush buffer_cache;System altered.SCOTT@book> select count(*) from tx ;select count(*) from tx *ERROR at line 1:ORA-01116: error in opening database file 7ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3SCOTT@book> SELECT file#, CREATION_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file#=7;FILE# CREATION_TIME CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS NAME----- ------------------- ------------------ ------------------- --------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- -------------------------------------------------- 7 2017-12-25 15:53:23 13277924712 2017-12-25 15:54:52 0 13277925346 2017-12-25 16:00:21 0 0 0 RECOVER /mnt/ramdisk/book/sugar01.dbf--//在控制文件里面记录了建立数据文件的时间(2017-12-25 15:53:23).只要这些归档在问题就不大.RMAN> report schema ;Report of database schema for database with db_unique_name BOOKList of Permanent Datafiles===========================File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 760 SYSTEM *** /mnt/ramdisk/book/system01.dbf2 940 SYSAUX *** /mnt/ramdisk/book/sysaux01.dbf3 865 UNDOTBS1 *** /mnt/ramdisk/book/undotbs01.dbf4 128 USERS *** /mnt/ramdisk/book/users01.dbf5 346 EXAMPLE *** /mnt/ramdisk/book/example01.dbf6 6 TEA *** /mnt/ramdisk/book/tea01.dbf7 0 SUGAR *** /mnt/ramdisk/book/sugar01.dbfList of Temporary Files=======================File Size(MB) Tablespace Maxsize(MB) Tempfile Name---- -------- -------------------- ----------- --------------------1 414 TEMP 32767 /mnt/ramdisk/book/temp01.dbfRMAN> list failure ;List of Database Failures=========================Failure ID Priority Status Time Detected Summary---------- -------- --------- ------------------- -------71648 HIGH OPEN 2017-12-25 15:56:41 One or more non-system datafiles are missingRMAN> list failure 71648 detail;List of Database Failures=========================Failure ID Priority Status Time Detected Summary---------- -------- --------- ------------------- -------71648 HIGH OPEN 2017-12-25 15:56:41 One or more non-system datafiles are missing Impact: See impact for individual child failures List of child failures for parent failure ID 71648 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 86741 HIGH OPEN 2017-12-25 15:56:41 Datafile 7: '/mnt/ramdisk/book/sugar01.dbf' is missing Impact: Some objects in tablespace SUGAR might be unavailableRMAN> advise failure ;List of Database Failures=========================Failure ID Priority Status Time Detected Summary---------- -------- --------- ------------------- -------71648 HIGH OPEN 2017-12-25 15:56:41 One or more non-system datafiles are missing Impact: See impact for individual child failures List of child failures for parent failure ID 71648 Failure ID Priority Status Time Detected Summary ---------- -------- --------- ------------------- ------- 86741 HIGH OPEN 2017-12-25 15:56:41 Datafile 7: '/mnt/ramdisk/book/sugar01.dbf' is missing Impact: Some objects in tablespace SUGAR might be unavailableanalyzing automatic repair options; this may take some timeallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=119 device type=DISKallocated channel: ORA_DISK_2channel ORA_DISK_2: SID=132 device type=DISKallocated channel: ORA_DISK_3channel ORA_DISK_3: SID=161 device type=DISKanalyzing automatic repair options completeMandatory Manual Actions========================no manual actions availableOptional Manual Actions=======================1. If file /mnt/ramdisk/book/sugar01.dbf was unintentionally renamed or moved, restore it2. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. Then perform a Data Guard role change (failover). Available standbys: bookdg.Automated Repair Options========================Option Repair Description------ ------------------1 Restore and recover datafile 7 Strategy: The repair includes complete media recovery with no data loss Repair script: /u01/app/oracle/diag/rdbms/book/book/hm/reco_1003154902.hm$ cat /u01/app/oracle/diag/rdbms/book/book/hm/reco_1003154902.hm # restore and recover datafile sql 'alter database datafile 7 offline'; restore datafile 7; recover datafile 7; sql 'alter database datafile 7 online';--//按照命令文本介绍执行如下:RMAN> sql 'alter database datafile 7 offline';sql statement: alter database datafile 7 offlineRMAN> restore datafile 7;Starting restore at 2017-12-25 16:00:26using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3creating datafile file number=7 name=/mnt/ramdisk/book/sugar01.dbf~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~restore not done; all files read only, offline, or already restoredFinished restore at 2017-12-25 16:00:26--//注意看下划线内容,数据文件建立.RMAN> recover datafile 7;Starting recover at 2017-12-25 16:05:20using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3starting media recoveryarchived log for thread 1 with sequence 797 is already on disk as file /u01/app/oracle/archivelog/book/1_797_896605872.dbfarchived log for thread 1 with sequence 798 is already on disk as file /u01/app/oracle/archivelog/book/1_798_896605872.dbfarchived log for thread 1 with sequence 799 is already on disk as file /u01/app/oracle/archivelog/book/1_799_896605872.dbfarchived log for thread 1 with sequence 800 is already on disk as file /u01/app/oracle/archivelog/book/1_800_896605872.dbfarchived log file name=/u01/app/oracle/archivelog/book/1_797_896605872.dbf thread=1 sequence=797archived log file name=/u01/app/oracle/archivelog/book/1_798_896605872.dbf thread=1 sequence=798media recovery complete, elapsed time: 00:00:00Finished recover at 2017-12-25 16:05:21RMAN> sql 'alter database datafile 7 online';sql statement: alter database datafile 7 onlineSCOTT@book> select count(*) from tx ; COUNT(*)------------ 990--//OK,数据没有丢失.3.再介绍sqlplus来恢复:--//重复操作:$ rm /mnt/ramdisk/book/sugar01.dbf/bin/rm: remove regular file `/mnt/ramdisk/book/sugar01.dbf'? ySCOTT@book> alter system flush buffer_cache;System altered.SCOTT@book> select count(*) from tx ;select count(*) from tx*ERROR at line 1:ORA-01116: error in opening database file 7ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'ORA-27041: unable to open fileLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3--//在sqlplus也可以执行,建立数据文件:SCOTT@book> alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf';alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf'*ERROR at line 1:ORA-01182: cannot create database file 7 - file is in use or recoveryORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'SCOTT@book> alter database datafile 7 offline ;Database altered.SCOTT@book> alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf';Database altered.SCOTT@book> recover datafile 7;ORA-00279: change 13277924104 generated at 12/25/2017 15:53:23 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_797_896605872.dbfORA-00280: change 13277924104 for thread 1 is in sequence #797Specify log: {<RET>=suggested | filename | AUTO | CANCEL}autoORA-00279: change 13277924687 generated at 12/25/2017 15:54:45 needed for thread 1ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_798_896605872.dbfORA-00280: change 13277924687 for thread 1 is in sequence #798Log applied.Media recovery complete.SCOTT@book> alter database datafile 7 online ;Database altered.SCOTT@book> select count(*) from tx ; COUNT(*)---------- 990--//只要概念清晰,这样的恢复很容易完成.