資料庫啟動時丟失資料檔案模擬實驗

還不算暈發表於2013-10-10

1.查詢資料庫狀態,MOUNTED

SYS@ bys001>select status,startup_time from v$instance;
STATUS       STARTUP_TIME
------------ -------------------
MOUNTED      2013/07/16 16:54:36

2.重命令一個資料檔案,模擬檔案丟失。

SYS@ bys001>host
[oracle@oel-01 oradata]$ cd bys001/
[oracle@oel-01 bys001]$ ls
bys_flashback.dbf  redo01.log    test1.dbf
catalog1.dbf       redo02.log    test1_undo.dbf
control01.ctl      redo03.log    undotbs01.dbf
example01.dbf      sysaux01.dbf  users01.dbf
redo01a.log        system01.dbf  users01.dbf2
redo01a.logbak     temp01.dbf

[oracle@oel-01 bys001]$ mv users01.dbf users01.dbf.bak  
[oracle@oel-01 bys001]$ ls
bys_flashback.dbf  redo01.log    temp01.dbf
catalog1.dbf       redo02.log    test1.dbf
control01.ctl      redo03.log    test1_undo.dbf
example01.dbf      sysaux01.dbf  undotbs01.dbf
redo01a.log        system01.dbf  users01.dbf.bak
檢視日誌:
[oracle@oel-01 ~]$ cat alert_bys001.log                                                                                           
alter database mount
Tue Jul 16 21:58:24 2013
Successful mount of redo thread 1, with mount id 2045113404
Allocated 3981204 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Tue Jul 16 21:58:24 2013
RVWR started with pid=20, OS id=4553
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Tue Jul 16 22:08:53 2013
Checker run found 1 new persistent data failures
   ---系統發現了,執行檢查發現1個新的永續性資料失敗
                    
with mount id 2045113404     mount id  在正常MOUNT資料庫過程中,資料庫計算MOUNT ID並記錄在
控制檔案中,然後開始啟動心跳heartbeat,每3秒更新一次控制檔案。
 heartbeat表示例項已經被特定例程MOUNT,此屬性主要用於RAC環境。在單例項環境中同樣存在。

使用ASM時,還會增加一個ASM例項的心跳事件。


SYS@ bys001>select event#,name from v$event_name where name like '%heart%';
    EVENT# NAME
---------- ----------------------------------------
        75 heartbeat monitor sleep
       380 ASM mount : wait for heartbeat
       563 control file heartbeat
                                                                                                                                
SYS@ bys001>select name from v$datafile;
NAME
----------------------------------------
/u01/app/oracle/oradata/bys001/system01.dbf
/u01/app/oracle/oradata/bys001/sysaux01.dbf
/u01/app/oracle/oradata/bys001/undotbs01.dbf
/u01/app/oracle/oradata/bys001/users01.dbf
/u01/app/oracle/oradata/bys001/example01.dbf
/u01/app/oracle/oradata/bys001/catalog1.dbf
/u01/app/oracle/oradata/bys001/test1.dbf
/u01/app/oracle/oradata/bys001/bys_flashback.dbf
8 rows selected.

3.開啟資料庫,提示找不到資料檔案。

SYS@ bys001>alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/bys001/users01.dbf'

日誌中的資訊:
Tue Jul 16 22:11:18 2013
alter database open
Tue Jul 16 22:11:18 2013
Errors in file /u01/app/oracle/diag/rdbms/bys001/bys001/trace/bys001_dbw0_3837.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/bys001/users01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory

Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/bys001/bys001/trace/bys001_ora_4540.trc:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/bys001/users01.dbf'
ORA-1157 signalled during: alter database open...
[oracle@oel-01 ~]$


SYS@ bys001>select status from v$instance;
STATUS
------------
MOUNTED
SYS@ bys001>col error for a20
SYS@ bys001>select * from v$recover_file;
     FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- -------------------
         1 ONLINE  ONLINE  FILE NOT FOUND                0


4.重新移回原檔案,資料庫可以正常開啟

[oracle@oel-01 bys001]$ pwd
/u01/app/oracle/oradata/bys001
[oracle@oel-01 bys001]$ ls
bys_flashback.dbf  control01.ctl  redo01a.log  redo02.log  sysaux01.dbf  temp01.dbf  test1_undo.dbf  users01.dbf.bak
catalog1.dbf       example01.dbf  redo01.log   redo03.log  system01.dbf  test1.dbf   undotbs01.dbf
[oracle@oel-01 bys001]$ mv users01.dbf.bak users01.dbf

SYS@ bys001>alter database open;
Database altered.
SYS@ bys001>select status from v$instance;
STATUS
------------
OPEN
SYS@ bys001>select * from v$recover_file;
no rows selected


[oracle@oel-01 ~]$ tail -n 100 alert_bys001.log                                                                                      

Tue Jul 16 22:19:30 2013
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 49, block 25811, scn 2241514
Recovery of Online Redo Log: Thread 1 Group 1 Seq 49 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/bys001/redo01.log
  Mem# 1: /u01/app/oracle/oradata/bys001/redo01a.log
Completed redo application of 0.00MB
Completed crash recovery at

 Thread 1: logseq 49, block 25811, scn 2261515
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Tue Jul 16 22:19:31 2013
LGWR: STARTING ARCH PROCESSES
Tue Jul 16 22:19:32 2013
ARC0 started with pid=21, OS id=4743
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Tue Jul 16 22:19:33 2013
ARC1 started with pid=22, OS id=4746
Thread 1 advanced to log sequence 50 (thread open)
Tue Jul 16 22:19:33 2013
ARC2 started with pid=23, OS id=4748
ARC1: Archival started
ARC2: Archival started
Tue Jul 16 22:19:33 2013
ARC3 started with pid=24, OS id=4750
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 50
  Current log# 2 seq# 50 mem# 0: /u01/app/oracle/oradata/bys001/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Jul 16 22:19:33 2013
SMON: enabling cache recovery
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
Starting background process FBDA
Tue Jul 16 22:19:40 2013
FBDA started with pid=25, OS id=4752
Tue Jul 16 22:19:40 2013
replication_dependency_tracking turned off (no async multimaster replication found)
Archived Log entry 141 added for thread 1 sequence 49 ID 0x79c75bdb dest 1:
Starting background process QMNC
Tue Jul 16 22:19:44 2013
QMNC started with pid=26, OS id=4754
Completed: alter database open

相關文章