Performing User-Managed Database-18.4、Restoring Datafiles and Archived Redo Logs

18.4、Restoring Datafiles and Archived Redo Logs
如果介质失败损坏一个或多个数据文件，恢复损坏的文件前必须还原数据文件。如果还原的位置不是原始位置，必须在控制文件显示数据文件的新位置。
在裸设备上还原数据文件的过程和在文件系统上是相同的。

模拟1、在testtbs01中创建表，插入数据，然后切换日志，备份归档日志，testtbs01的某个数据文件被损坏，对应归档日志删除，还原和恢复
（1）在testtbs01中创建表并插入数据
SQL>
create table boss_new_test
( id number(5) constraint id_pk primary key,
lastname varchar2(10) constraint ln_nn not null,
firstname varchar2(10) not null unique,
userid varchar2(8) constraint ui_uq unique,
startdate date default sysdate,
user_type varchar2(4) constraint ut_ck check (user_type in('in','out'))
)
initrans 1 maxtrans 255
pctfree 20 pctused 50
storage(initial 64m next 8m pctincrease 0)
tablespace testtbs01;

SQL> insert into boss_new_test values(1,'aaa','a','00001',sysdate,'in');
SQL> commit;

（2）创建存储过程插入数据

（3）查看检查点
SQL> set linesize 120
SQL> col "文件名" for a40
SQL>
select
  ts.name "表空间名"
  , df.file# "文件号"
  , df.checkpoint_change# "检查点"
  , df.name "文件名"
from v$tablespace ts,v$datafile df
where ts.ts#=df.ts# and ts.name='TESTTBS01'
order by df.file#;

表空间名                           文件号     检查点 文件名
------------------------------ ---------- ---------- ----------------------------------------
TESTTBS01                               6     566111 /oracle/oradata/boss/testtbs01_01.dbf

SQL> select dbid,name,log_mode,checkpoint_change# from v$database;

      DBID NAME      LOG_MODE     CHECKPOINT_CHANGE#
---------- --------- ------------ ------------------
1375601832 BOSS      ARCHIVELOG               566111

SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header where file#=6;

     FILE# NAME                                     STATUS  CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
         6 /oracle/oradata/boss/testtbs01_01.dbf    ONLINE              566111 NO

SQL> select group#,members,sequence#,archived,status,first_change# from v$log;

    GROUP#    MEMBERS  SEQUENCE# ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
         1          1          5 YES INACTIVE                534657
         2          1          6 NO  CURRENT               563694
         3          1          4 YES INACTIVE                514244
（4）进行日志切换，数据文件头scn和数据库scn并未变化
SQL> alter system switch logfile;
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;

    GROUP#    MEMBERS  SEQUENCE# ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
         1          1          5 YES INACTIVE                534657
         2          1          6 YES ACTIVE                  563694
         3          1          7 NO  CURRENT                 569439

SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header where file#=6;

     FILE# NAME                                     STATUS  CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
         6 /oracle/oradata/boss/testtbs01_01.dbf    ONLINE              566111 NO
SQL> select dbid,name,log_mode,checkpoint_change# from v$database;

      DBID NAME                                     LOG_MODE     CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------------ ------------------
1375601832 BOSS                                     ARCHIVELOG               566111

SQL> alter system checkpoint;

SQL> select group#,members,sequence#,archived,status,first_change# from v$log;

    GROUP#    MEMBERS  SEQUENCE# ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
         1          1          5 YES INACTIVE                534657
         2          1          6 YES INACTIVE                563694
         3          1          7 NO  CURRENT                 569439

（5）备份新的归档日志
# cp -rf o1_mf_1_6_9rx90qq2_.arc /oradata/bossbak/test_ar_rec

（6）删除testtbs01的某个数据文件，删除o1_mf_1_6_9rx90qq2_.arc归档
# rm -rf testtbs01_01.dbf
# pwd
/oracle/flash_recovery_area/BOSS/archivelog/2014_06_04
# rm -rf o1_mf_1_6_9rx90qq2_.arc

（7）
SQL> alter system flush buffer_cache;

SQL> select * from boss_new_test;

        ID LASTNAME   FIRSTNAME  USERID   STARTDATE    USER
---------- ---------- ---------- -------- ------------ ----
         2 aa         a          00002    04-JUN-14    in
         3 bb         b          00003    04-JUN-14    in
         4 cc         c          00004    04-JUN-14    in
         5 dd         d          00005    04-JUN-14    in
         6 ff         f          00006    04-JUN-14    in
SQL> create table boss_new_test1
  2  ( id number(5) constraint id_pk primary key,
  3  lastname varchar2(10) constraint ln_nn not null,
  4  firstname varchar2(10) not null unique,
  5  userid varchar2(8) constraint ui_uq unique,
  6  startdate date default sysdate,
  7  user_type varchar2(4) constraint ut_ck check (user_type in('in','out'))
  8  )
  9  initrans 1 maxtrans 255
 10  pctfree 20 pctused 50
 11  storage(initial 64m next 8m pctincrease 0)
 12  tablespace testtbs01;
user_type varchar2(4) constraint ut_ck check (user_type in('in','out'))
                                              *
ERROR at line 7:
ORA-01565: error in identifying file '/oracle/oradata/boss/testtbs01_01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> shutdown immediate;

SQL> startup open;
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/oradata/boss/testtbs01_01.dbf'

# cd $ORACLE_BASE/admin/boss/bdump

（8）还原和恢复数据文件
SQL> set linesize 150

# mkdir -p /oracle/oradata/boss/others
# cp -rf /oradata/bossbak/testtbs01_01.dbf /oracle/oradata/boss/others

SQL> select
  2    ts.name "表空间名"
  3    , df.file# "文件号"
  4    , df.checkpoint_change# "检查点"
  5    , df.name "文件名"
  6    , df.status "在线状态"
  7    , rf.error "恢复原因"
  8    , rf.change# "系统变更号"
  9    , rf.time
 10    from v$tablespace ts,v$datafile df,v$recover_file rf
 11  where ts.ts#=df.ts# and df.file#=rf.file#
 12  order by df.file#;

表空间名                           文件号     检查点 文件名                                   在线状  恢复原因           系统变更号 TIME
------------------------------ ---------- ---------- ---------------------------------------- ------- ------------------ ---------- ------------
TESTTBS01                               6     570729 /oracle/oradata/boss/testtbs01_01.dbf    ONLINE  FILE NOT FOUND              0
##离线数据文件
SQL> alter database datafile '/oracle/oradata/boss/testtbs01_01.dbf' offline;
##还原数据文件
SQL> recover datafile '/oracle/oradata/boss/testtbs01_01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 6: '/oracle/oradata/boss/testtbs01_01.dbf'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/oradata/boss/testtbs01_01.dbf'

SQL> alter database rename file '/oracle/oradata/boss/testtbs01_01.dbf' to '/oracle/oradata/boss/others/testtbs01_01.dbf';
alter database rename file '/oracle/oradata/boss/testtbs01_01.dbf' to '/oracle/oradata/boss/others/testtbs01_01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file '/oracle/oradata/boss/others/testtbs01_01.dbf' not found
ORA-01110: data file 6: '/oracle/oradata/boss/testtbs01_01.dbf'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
Additional information: 2

# chown -R oracle:oinstall /oracle
# chown -R oracle:oinstall /oradata

SQL> alter database rename file '/oracle/oradata/boss/testtbs01_01.dbf' to '/oracle/oradata/boss/others/testtbs01_01.dbf';
##重做日志恢复
SQL> recover datafile 6;
Media recovery complete.

SQL> alter database datafile '/oracle/oradata/boss/others/testtbs01_01.dbf' online;

SQL> alter database open;
SQL> select * from boss_new_test;

        ID LASTNAME   FIRSTNAME  USERID   STARTDATE    USER
---------- ---------- ---------- -------- ------------ ----
         2 aa         a          00002    04-JUN-14    in
         3 bb         b          00003    04-JUN-14    in
         4 cc         c          00004    04-JUN-14    in
         5 dd         d          00005    04-JUN-14    in
         6 ff         f          00006    04-JUN-14    in

二次测试：
（1）导出表boss_new_test
$ exp \"sys/a as sysdba\" file=/oradata/bossbak/boss_new_test_20140604.dmp log=/oradata/bossbak/expboss_new_test.log tables=boss_new_test

（2）删除表boss_new_test
SQL> truncate table boss_new_test;
SQL> drop table boss_new_test;
SQL> purge table boss_new_test;

（3）SQL> drop tablespace testtbs01 including contents and datafiles;

(4)
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;

    GROUP#    MEMBERS  SEQUENCE# ARC STATUS           FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
         1          1         11 YES INACTIVE                572850
         2          1         12 YES INACTIVE                572852
         3          1         13 NO  CURRENT                 572854

SQL>  alter system switch logfile;

SQL>  alter system switch logfile;

SQL>  alter system switch logfile;
 alter system switch logfile;
 alter system switch logfile;
 alter system switch logfile;

（5）备份新的归档日志
$ cp -rf *.arc /oradata/bossbak/test_ar_rec
$ ll
总用量 1724
-rw-r----- 1 oracle oinstall 1735168 6月   4 15:05 o1_mf_1_13_9rxk4vq9_.arc
-rw-r----- 1 oracle oinstall    1024 6月   4 15:05 o1_mf_1_14_9rxk4x7j_.arc
-rw-r----- 1 oracle oinstall    5632 6月   4 15:05 o1_mf_1_15_9rxk52tx_.arc
-rw-r----- 1 oracle oinstall    1024 6月   4 15:05 o1_mf_1_16_9rxk52wv_.arc
-rw-r----- 1 oracle oinstall    1024 6月   4 15:05 o1_mf_1_17_9rxk57y7_.arc
-rw-r----- 1 oracle oinstall    1024 6月   4 15:05 o1_mf_1_18_9rxk5818_.arc

（6）删除testtbs01的某个数据文件，删除所有归档
$ rm -rf testtbs01_01.dbf
$ cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04
$ rm -rf *

（7）
SQL> create table boss_new_test1
  2  ( id number(5) constraint id_pk primary key,
  3  lastname varchar2(10) constraint ln_nn not null,
  4  firstname varchar2(10) not null unique,
  5  userid varchar2(8) constraint ui_uq unique,
  6  startdate date default sysdate,
  7  user_type varchar2(4) constraint ut_ck check (user_type in('in','out'))
  8  )
  9  initrans 1 maxtrans 255
 10  pctfree 20 pctused 50
 11  storage(initial 64m next 8m pctincrease 0)
 12  tablespace testtbs01;

user_type varchar2(4) constraint ut_ck check (user_type in('in','out'))
                                              *
ERROR at line 7:
ORA-01565: error in identifying file '/oracle/oradata/boss/testtbs01_01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header where file#=6;

     FILE# NAME                                     STATUS  CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
         6 /oracle/oradata/boss/testtbs01_01.dbf    ONLINE              575477 NO
SQL> alter system checkpoint;

SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header where file#=6;

     FILE# NAME                                     STATUS  CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
         6 /oracle/oradata/boss/testtbs01_01.dbf    ONLINE              575795 NO

SQL> shutdown immediate;

SQL> startup open;
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/oracle/oradata/boss/testtbs01_01.dbf'

（8）
$ cp -rf /oradata/bossbak/testtbs01_01.dbf /oracle/oradata/boss/
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header where file#=6;

     FILE# NAME                                     STATUS  CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
         6 /oracle/oradata/boss/testtbs01_01.dbf    OFFLINE             575015 YES

SQL> select dbid,name,log_mode,checkpoint_change# from v$database;

      DBID NAME                                     LOG_MODE     CHECKPOINT_CHANGE#
---------- ---------------------------------------- ------------ ------------------
1375601832 BOSS                                     ARCHIVELOG               576285

SQL> select
  2    ts.name "表空间名"
  3    , df.file# "文件号"
  4    , df.checkpoint_change# "检查点"
  5    , df.name "文件名"
  6  from v$tablespace ts,v$datafile df
  7  where ts.ts#=df.ts# and ts.name='TESTTBS01'
  8  order by df.file#;

表空间名                           文件号     检查点 文件名
------------------------------ ---------- ---------- ----------------------------------------
TESTTBS01                               6     576285 /oracle/oradata/boss/testtbs01_01.dbf

##多次日志切换，只能从归档日志恢复
SQL> recover datafile '/oracle/oradata/boss/testtbs01_01.dbf';
ORA-00279: change 575015 generated at 06/04/2014 14:55:59 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_13_%u_.arc
ORA-00280: change 575015 for thread 1 is in sequence #13

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

##拷贝所需归档日志，输入auto
$ cp -rf /oradata/bossbak/test_ar_rec/*.arc /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04

SQL> recover datafile '/oracle/oradata/boss/testtbs01_01.dbf';
ORA-00279: change 575015 generated at 06/04/2014 14:55:59 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_13_%u_.arc
ORA-00280: change 575015 for thread 1 is in sequence #13

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto 
ORA-00279: change 575465 generated at 06/04/2014 15:02:51 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_14_%u_.arc
ORA-00280: change 575465 for thread 1 is in sequence #14
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_13_9rxk4vq9_.arc' no longer needed
for this recovery

ORA-00279: change 575467 generated at 06/04/2014 15:02:53 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_15_%u_.arc
ORA-00280: change 575467 for thread 1 is in sequence #15
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_14_9rxk4x7j_.arc' no longer needed
for this recovery

ORA-00279: change 575470 generated at 06/04/2014 15:02:58 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_16_%u_.arc
ORA-00280: change 575470 for thread 1 is in sequence #16
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_04/o1_mf_1_15_9rxk52tx_.arc' no longer needed
for this recovery

Log applied.
Media recovery complete.

SQL> alter database datafile '/oracle/oradata/boss/testtbs01_01.dbf' online;

SQL> alter database open;

SQL> select * from boss_new_test;

        ID LASTNAME   FIRSTNAME  USERID   STARTDATE    USER
---------- ---------- ---------- -------- ------------ ----
         1 aaa        a          00001    04-JUN-14    in
         2 bbb        b          00002    04-JUN-14    in
         3 ccc        c          00003    04-JUN-14    in
         4 ddd        d          00004    04-JUN-14    in
         5 eee        e          00005    04-JUN-14    in
         6 fff        f          00006    04-JUN-14    in
总结：数据文件恢复成功后，数据库scn、数据文件scn是相同的，它们和数据文件头scn不一定相同，但是数据文件头scn>=数据库scn