[20171225]没有备份数据文件的恢复.txt

--//别人问的问题,增加了数据文件没有备份,如何恢复,实际上很简单,因为当前控制文件有记录建立时间只要从建立数据文件开始的

--//归档日志都存在恢复是没有任何问题的.我仅仅介绍rman的恢复:

1.环境:

SCOTT@book> @&r/ver1 PORT_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 Production

CREATE TABLESPACE SUGAR DATAFILE

  '/mnt/ramdisk/book/sugar01.dbf' SIZE 6M AUTOEXTEND OFF LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK 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(*) ----------        990

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.

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'? y

SCOTT@book> select count(*) from tx ;

  COUNT(*) ----------        990

SCOTT@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 7 ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3

SCOTT@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 BOOK List of Permanent Datafiles =========================== File Size(MB) Tablespace           RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1    760      SYSTEM               ***     /mnt/ramdisk/book/system01.dbf 2    940      SYSAUX               ***     /mnt/ramdisk/book/sysaux01.dbf 3    865      UNDOTBS1             ***     /mnt/ramdisk/book/undotbs01.dbf 4    128      USERS                ***     /mnt/ramdisk/book/users01.dbf 5    346      EXAMPLE              ***     /mnt/ramdisk/book/example01.dbf 6    6        TEA                  ***     /mnt/ramdisk/book/tea01.dbf 7    0        SUGAR                ***     /mnt/ramdisk/book/sugar01.dbf

List of Temporary Files

======================= File Size(MB) Tablespace           Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1    414      TEMP                 32767       /mnt/ramdisk/book/temp01.dbf

RMAN> 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 missing

RMAN> 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 unavailable

RMAN> 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 unavailable

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=119 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=132 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=161 device type=DISK analyzing automatic repair options complete

Mandatory Manual Actions

======================== no manual actions available

Optional Manual Actions

======================= 1. If file /mnt/ramdisk/book/sugar01.dbf was unintentionally renamed or moved, restore it 2. 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 offline

RMAN> restore datafile 7;

Starting restore at 2017-12-25 16:00:26

using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3

creating datafile file number=7 name=/mnt/ramdisk/book/sugar01.dbf

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ restore not done; all files read only, offline, or already restored Finished restore at 2017-12-25 16:00:26

--//注意看下划线内容,数据文件建立.

RMAN> recover datafile 7; Starting recover at 2017-12-25 16:05:20 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 starting media recovery archived log for thread 1 with sequence 797 is already on disk as file /u01/app/oracle/archivelog/book/1_797_896605872.dbf archived log for thread 1 with sequence 798 is already on disk as file /u01/app/oracle/archivelog/book/1_798_896605872.dbf archived log for thread 1 with sequence 799 is already on disk as file /u01/app/oracle/archivelog/book/1_799_896605872.dbf archived log for thread 1 with sequence 800 is already on disk as file /u01/app/oracle/archivelog/book/1_800_896605872.dbf archived log file name=/u01/app/oracle/archivelog/book/1_797_896605872.dbf thread=1 sequence=797 archived log file name=/u01/app/oracle/archivelog/book/1_798_896605872.dbf thread=1 sequence=798 media recovery complete, elapsed time: 00:00:00 Finished recover at 2017-12-25 16:05:21

RMAN>  sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online

SCOTT@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'? y

SCOTT@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 7 ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional 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 recovery ORA-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 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_797_896605872.dbf ORA-00280: change 13277924104 for thread 1 is in sequence #797 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 13277924687 generated at 12/25/2017 15:54:45 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_798_896605872.dbf ORA-00280: change 13277924687 for thread 1 is in sequence #798 Log applied. Media recovery complete.

SCOTT@book> alter database datafile 7 online ;

Database altered.

SCOTT@book> select count(*) from tx ;

  COUNT(*)

----------        990

--//只要概念清晰,这样的恢复很容易完成.