丟失所有控制文件，還原所有數據文件，不還原控制文件，recoverd

說明：創建表test03，切換日志，創建表test04，切換日志，刪除所有數據文件，還原備份的所有數據文件，不需要還原控制文件；test03的歸檔日志沒有丟失，test04的歸檔日志丟失，不能恢復test04 1、數據庫所有控制文件丟失 2、還原備份之前的控制文件，數據文

說明：創建表test03，切換日志，創建表test04，切換日志，刪除所有數據文件，還原備份的所有數據文件，不需要還原控制文件；test03的歸檔日志沒有丟失，test04的歸檔日志丟失，不能恢復test04
1、數據庫所有控制文件丟失 2、還原備份之前的控制文件，數據文件沒做還原 3、until cancel using bakup controlfile跑部分歸檔日志
（1）一致性的全備
SQL> shutdown immediate;

# mkdir -p /oradata/bossbak/20140609allbackup
# cp -rf $ORACLE_BASE/oradata/boss/*.dbf /oradata/bossbak/20140609allbackup
# cp -rf $ORACLE_BASE/oradata/boss/*.ctl /oradata/bossbak/20140609allbackup
# cp -rf $ORACLE_HOME/dbs/spfileboss.ora /oradata/bossbak/20140609
# cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/
# rm -rf *

（2）查看數據庫的信息
SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ ONLY

SQL> select table_name,status,tablespace_name from user_tables where tablespace_name like 'TESTTBS%';

TABLE_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
TEST01 VALID TESTTBS01
BOSS_NEW_TEST VALID TESTTBS01

（3）創建表test03，插入數據
SQL> create table test03 (id number, name varchar2(30)) tablespace testtbs01;
SQL> insert into test03 values(1, '11111');
SQL> insert into test03 values(2,'22222');
SQL> insert into test03 values(3,'33333');
SQL> insert into test03 values(1, '11111');
SQL> insert into test03 values(2,'22222');
SQL> insert into test03 values(3,'33333');
SQL> commit;

（4）進行日志切換
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 2 YES INACTIVE 675131
2 1 4 NO CURRENT 700583
3 1 3 YES ACTIVE 699759
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> create table test04 (id number, name varchar2(30)) tablespace testtbs01;
SQL> insert into test04 values(1, '11111');
SQL> insert into test04 values(2,'22222');
SQL> insert into test04 values(3,'33333');
SQL> commit;

SQL> alter system switch logfile;
SQL> alter system switch logfile;
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 8 YES INACTIVE 704201
2 1 7 YES INACTIVE 704199
3 1 9 NO CURRENT 704204

（5）備份全備后所有的歸檔日志
# pwd
/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09
# cp -rf *.arc /oradata/bossbak/20140609

（6）刪除全部歸檔，刪除全部數據文件
# rm -rf *.arc

（7）關閉數據庫
SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup open;
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
（8）還原備份的數據文件，不需要恢復控制文件
$ cp -rf *.dbf /oracle/oradata/boss

SQL> col "文件名" for a40
SQL> set linesize 150
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#;
#####最近一次數據庫正常啟動后的scn
表空間名 文件號 檢查點 文件名 在線狀 恢復原因 系統變更號 TIME
------------------------------ ---------- ---------- ---------------------------------------- ------- ------------------ ---------- ------------
SYSTEM 1 704204 /oracle/oradata/boss/system01.dbf SYSTEM FILE NOT FOUND 0
UNDOTBS1 2 704204 /oracle/oradata/boss/undotbs01.dbf ONLINE FILE NOT FOUND 0
SYSAUX 3 704204 /oracle/oradata/boss/sysaux01.dbf ONLINE FILE NOT FOUND 0
USERS 4 704204 /oracle/oradata/boss/users01.dbf ONLINE FILE NOT FOUND 0
EXAMPLE 5 704204 /oracle/oradata/boss/example01.dbf ONLINE FILE NOT FOUND 0
TESTTBS01 6 704204 /oracle/oradata/boss/testtbs01_01.dbf ONLINE FILE NOT FOUND 0
TESTTBS01 7 704204 /oracle/oradata/boss/testtbs01_02.dbf ONLINE FILE NOT FOUND 0
TESTTBS02 8 652783 /oracle/oradata/boss/testtbs02_01.dbf OFFLINE OFFLINE NORMAL 0
TESTTBS03 9 652799 /oracle/oradata/boss/testtbs03_01.dbf ONLINE FILE NOT FOUND 0

SQL> col name for a40
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header;

FILE# NAME STATUS CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
1 ONLINE 0
2 ONLINE 0
3 ONLINE 0
4 ONLINE 0
5 ONLINE 0
6 ONLINE 0
7 ONLINE 0
8 OFFLINE 0
9 ONLINE 0

（9）復制部分的歸檔日志，基于終止恢復
# cp -rf o1_mf_1_3_9sbbrfsx_.arc o1_mf_1_4_9sbbsn5j_.arc /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/

# cat boss_dbw0_8622.trc
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
ORA-27041: unable to open file
Linux Error: 13: Permission denied

SQL> col name for a40;
SQL> set linesize 120;
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header;

FILE# NAME STATUS CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
1 /oracle/oradata/boss/system01.dbf ONLINE 700222 YES
2 /oracle/oradata/boss/undotbs01.dbf ONLINE 700222 YES
3 /oracle/oradata/boss/sysaux01.dbf ONLINE 700222 YES
4 /oracle/oradata/boss/users01.dbf ONLINE 700222 YES
5 /oracle/oradata/boss/example01.dbf ONLINE 700222 YES
6 /oracle/oradata/boss/testtbs01_01.dbf ONLINE 700222 YES
7 /oracle/oradata/boss/testtbs01_02.dbf ONLINE 700222 YES
8 OFFLINE 0
9 /oracle/oradata/boss/testtbs03_01.dbf ONLINE 652799

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'

SQL> recover database until cancel;
ORA-00279: change 700222 generated at 06/09/2014 11:16:10 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_%u_.arc
ORA-00280: change 700222 for thread 1 is in sequence #3

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

# ll
總用量 1047064
-rw-r----- 1 oracle oinstall 7094272 6月 9 11:21 control02.ctl
-rw-r----- 1 oracle oinstall 7094272 6月 9 11:21 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 6月 9 11:19 example01.dbf
-rw-r----- 1 oracle oinstall 622080 6月 9 12:38 o1_mf_1_3_9sbbrfsx_.arc
-rw-r----- 1 oracle oinstall 11264 6月 9 12:38 o1_mf_1_4_9sbbsn5j_.arc
-rw-r----- 1 oracle oinstall 2560 6月 9 12:38 o1_mf_1_5_9sbbst96_.arc
-rw-r----- 1 oracle oinstall 4336640 6月 9 12:38 o1_mf_1_6_9sbgg5or_.arc
-rw-r----- 1 oracle oinstall 1024 6月 9 12:38 o1_mf_1_7_9sbgg862_.arc
-rw-r----- 1 oracle oinstall 2560 6月 9 12:38 o1_mf_1_8_9sbggfm7_.arc

# cp -rf o1_mf_1_3_9sbbrfsx_.arc o1_mf_1_4_9sbbsn5j_.arc /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/

SQL> recover database until cancel;
ORA-00279: change 700222 generated at 06/09/2014 11:16:10 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_%u_.arc
ORA-00280: change 700222 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 700583 generated at 06/09/2014 11:33:33 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_4_%u_.arc
ORA-00280: change 700583 for thread 1 is in sequence #4
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_9sbbrfsx_.arc' no longer needed
for this recovery

ORA-00279: change 700603 generated at 06/09/2014 11:34:12 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_%u_.arc
ORA-00280: change 700603 for thread 1 is in sequence #5
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_4_9sbbsn5j_.arc' no longer needed
for this recovery

ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_9sbbst96_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> recover database until cancel;
ORA-00279: change 700603 generated at 06/09/2014 11:34:12 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_%u_.arc
ORA-00280: change 700603 for thread 1 is in sequence #5

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

SQL> select * from test03;

ID NAME
---------- ----------------------------------------
1 11111
2 22222
3 33333

SQL> select * from test04;
select * from test04
*
ERROR at line 1:
ORA-00942: table or view does not exist

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com