本次试验分为以下步骤
1 删除表空间
2 检查恢复
3 还原数据文件
4 不完全恢复数据库
5 以重制日志选项打开数据库
6 确认数据库恢复

step1 删除表空间

插入时间戳数据作为参考
insert into snow.date_log values(sysdate,'snow1'); commit;

select * from snow.date_log order by create_time;

CREATE_TIME         NAME
------------------- ------------------------------
2014-08-01 10:26:35 --
2014-08-01 10:27:35 --
2014-08-01 10:28:16 snow1 <==数据插入的时间

切换3次以上日志，强制产生归档
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

drop tablespace indx including contents and datafilee;
出错信息为表空间中有主键约束，无法删除
ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1ORA-02429: cannot drop index used for enforcement of unique/primary key

执行SQL语句产生一条删除约束的命令
select 'alter table ' ||owner||'.'||table_name|| ' drop constraint ' ||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U' , 'P')
   and (index_owner, index_name) in
       ( select owner, segment_name
          from dba_segments
         where tablespace_name = 'INDX' );

alter table SNOW.DATE_LOG drop constraint CREATE_DATE_PK ;

drop tablespace indx including contents and datafilee;

此时alert日志会记录删除表空间的信息
Fri Aug 01 10:29:06 2014
Archived Log entry 24 added for thread 1 sequence 4 ID 0xb581bdfe dest 1:
Fri Aug 01 10:29:18 2014
Thread 1 advanced to log sequence 6 (LGWR switch)
Current log# 3 seq# 6 mem# 0: /oradata/PRACTICE/redo03.log
Fri Aug 01 10:29:18 2014 <==删除表空间前最后一次日志切换时间，不完全恢复时将要引用该时间
Archived Log entry 25 added for thread 1 sequence 5 ID 0xb581bdfe dest 1: <==最后一次切换日志序列号

step2 检查恢复
alter system switch logfile;

找出在重做日志序列号为3的第一个变动序号，需要使用v$log_history
select sequence#,first_change# from v$log_history where sequence#=5;

SEQUENCE# FIRST_CHANGE#
---------- -------------
         5       1077795
         5       1299130

step3 还原数据文件

还原所有的数据文件，但是保留现在正使用的控制文件，本实验的关键点在此

shutdown immediate;
cp /backup/closed_backup/*.dbf /oradata/PRACTICE/

step4 不完全恢复数据库
startup mount;

基于时间的恢复，时间点引用 “Fri Aug 01 10:29:18 2014”
alter database recover automatic until time '2014-08-01 10:29:18';

基于SCN的恢复
RECOVER DATABASE UNTIL CHANGE 1300637;

基于取消的恢复
alter database recover until cancel using backup controlfile;

step5 以重制日志选项打开数据库
alter database open resetlogs;

step6 确认数据库恢复

查看INDX表空间是否被恢复
select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                                                                            STATUS
------------------------------------------------------------------------------------------ ----------
SYSTEM                                                                                     ONLINE
SYSAUX                                                                                     ONLINE
UNDOTBS1                                                                                   ONLINE
TEMP                                                                                       ONLINE
USERS                                                                                      ONLINE
EXAMPLE                                                                                    ONLINE
TOOLS                                                                                      ONLINE
INDX                                                                                       ONLINE

查看日志文件是否有报错
Fri Aug 01 10:42:35 2014
Errors in file /u01/app/oracle/diag/rdbms/practice/PRACTICE/trace/PRACTICE_j001_8206.trc:
ORA-12012: error on auto execute of job 24
ORA-00376: file 7 cannot be read at this time
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007’ <==7号数据文件有错
ORA-06512: at "SNOW.CREATE_DATE_LOG_ROW", line 4
ORA-06512: at line 1

col tablespace_name for a10
col file_name for a60

查看数据库文件数据字典
select file_id,tablespace_name,file_name,online_status from dba_data_files;

   FILE_ID TABLESPACE FILE_NAME                                                    ONLINE_STATUS
---------- ---------- ------------------------------------------------------------ ---------------------
         4 USERS      /oradata/PRACTICE/users01.dbf                                ONLINE
         3 UNDOTBS1   /oradata/PRACTICE/undotbs01.dbf                              ONLINE
         2 SYSAUX     /oradata/PRACTICE/sysaux01.dbf                               ONLINE
         1 SYSTEM     /oradata/PRACTICE/system01.dbf                               SYSTEM
         5 EXAMPLE    /oradata/PRACTICE/example01.dbf                              ONLINE
         6 TOOLS      /oradata/PRACTICE/tools01.dbf                                ONLINE
         7 INDX       /u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007         RECOVER
         8 USERS      /oradata/PRACTICE/users02.dbf                                ONLINE

由于我们使用的是当前的控制文件，已经“不认识”被删除的表空间INDX和所包含的数据文件datafile7。从冷备复制回来的数据文件datafile7在恢复过程中无法识别，就被创建到默认的/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007，为了可以正常启动数据库。

手工重置数据文件的路径，让控制文件重新识别
ALTER TABLESPACE indx RENAME DATAFILE '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007' to '/oradata/PRACTICE/indx.dbf';

恢复数据文件
recover datafile 7;
ORA-00279: change 1298436 generated at 08/01/2014 10:21:00 needed for thread 1
ORA-00289: suggestion : /archive/1_1_854446453.arc
ORA-00280: change 1298436 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299046 generated at 08/01/2014 10:26:58 needed for thread 1
ORA-00289: suggestion : /archive/1_2_854446453.arc
ORA-00280: change 1299046 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299121 generated at 08/01/2014 10:28:54 needed for thread 1
ORA-00289: suggestion : /archive/1_3_854446453.arc
ORA-00280: change 1299121 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299127 generated at 08/01/2014 10:29:03 needed for thread 1
ORA-00289: suggestion : /archive/1_4_854446453.arc
ORA-00280: change 1299127 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1299130 generated at 08/01/2014 10:29:06 needed for thread 1
ORA-00289: suggestion : /archive/1_5_854446453.arc
ORA-00280: change 1299130 for thread 1 is in sequence #5

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

恢复后将数据文件置为online状态

alter database datafile 7 online;

select file_id,tablespace_name,file_name,online_status from dba_data_files;

   FILE_ID TABLESPACE FILE_NAME                                                    ONLINE_STATUS
---------- ---------- ------------------------------------------------------------ ---------------------
         4 USERS      /oradata/PRACTICE/users01.dbf                                ONLINE
         3 UNDOTBS1   /oradata/PRACTICE/undotbs01.dbf                              ONLINE
         2 SYSAUX     /oradata/PRACTICE/sysaux01.dbf                               ONLINE
         1 SYSTEM     /oradata/PRACTICE/system01.dbf                               SYSTEM
         5 EXAMPLE    /oradata/PRACTICE/example01.dbf                              ONLINE
         6 TOOLS      /oradata/PRACTICE/tools01.dbf                                ONLINE
         7 INDX       /oradata/PRACTICE/indx.dbf                                   ONLINE
         8 USERS      /oradata/PRACTICE/users02.dbf                                ONLINE

查看数据恢复是否成功
select * from snow.date_log order by create_time;

CREATE_TIME         NAME
------------------- --------------------
2014-08-01 10:20:20 --
2014-08-01 10:21:35 --
2014-08-01 10:22:35 --
2014-08-01 10:23:35 --
2014-08-01 10:24:35 --
2014-08-01 10:25:35 --
2014-08-01 10:26:35 --
2014-08-01 10:27:35 --
2014-08-01 10:28:16 snow1

查看被删除的约束是否恢复成功
col INDEX_NAME for a20
col TABLE_NAME for a20
elect index_name,table_name from user_indexes;

INDEX_NAME           TABLE_NAME
-------------------- --------------------
CREATE_DATE_PK       DATE_LOG

ITPub博客

ORA-01110: data file 7: '/u01/app/oracle/product/11.2.0/db_1/dbs/MISSING00007'

最新文章