數(shù)據(jù)庫恢復(fù)一例(2)
2024-07-21 02:07:16
供稿：網(wǎng)友
最近通過做實(shí)驗總結(jié)出一種數(shù)據(jù)庫恢復(fù)方法，對今后的工作很有幫助：
數(shù)據(jù)庫為非歸檔狀態(tài)，只有一周前的數(shù)據(jù)文件的備份，無redolog，歸檔日志和controlfile的備份，此種情況一但數(shù)據(jù)庫出故障只能做不完全恢復(fù)，會丟失一周前做備份時到出故障那一時候的所有數(shù)據(jù)，具體恢復(fù)方法如下:
操作系統(tǒng)為solaris8,內(nèi)存2g，2顆cpu.
實(shí)驗步驟：
$sqlplus /nolog
sql>connect / as sysdba
sql> archive log list
database log mode no archive mode
automatic archival enabled
archive destination /opt/oracle/arch/orcl
oldest online log sequence 895
current log sequence 897
sql> select * from v$logfile;
group# status
---------- -------
member
--------------------------------------------------------------------------------
3
/opt/oracle/db04/oradata/orcl/redo03.log
2
/opt/oracle/db03/oradata/orcl/redo02.log
1
/opt/oracle/db02/oradata/orcl/redo01.log
sql> select * from v$controlfile;
status
-------
name
--------------------------------------------------------------------------------
/opt/oracle/db02/oradata/orcl/control01.ctl
/opt/oracle/db03/oradata/orcl/control02.ctl
/opt/oracle/db04/oradata/orcl/control03.ctl
sql>alter database backup controlfile to trace;
(備份控制文件,此時會在$oracle_base/admin/orcl/udump目錄里生成trace文件)
sql> shutdown immediate (關(guān)閉當(dāng)前數(shù)據(jù)庫)
database closed.
database dismounted.
oracle instance shut down.
sql> quit
disconnected
模擬數(shù)據(jù)丟失：
刪除當(dāng)前所有的數(shù)據(jù)文件,控制文件和redolog文件:
$rm -rf ?/opt/oracle/db02/oradata/orcl/*
$rm /opt/oracle/db03/oradata/orcl/redo02.log
$rm /opt/oracle/db03/oradata/orcl/control02.ctl
$rm /opt/oracle/db04/oradata/orcl/redo03.log
$rm /opt/oracle/db04/oradata/orcl/control03.ctl
把一周前備份的數(shù)據(jù)文件copy回來，目錄結(jié)構(gòu)和以前一樣，但這時因為沒有redolog和controlfile文件，數(shù)據(jù)庫只能啟動到nomount狀態(tài):
編輯udump目錄下生成的orcl_ora_7140.trc文件，把里面的創(chuàng)建controlfile的那部分內(nèi)容粘貼下來放在sql里執(zhí)行:
（這里要注意一定要用resetlogs方式重建控制文件，resetlogs之后會生成新的redolog,并且把當(dāng)前redofile的sequence置為1,否則創(chuàng)建控制文件會失敗)：
sql>startup nomount
sql> create controlfile reuse database "orcl" resetlogs noarchivelog
2 maxlogfiles 32
3 maxlogmembers 2
4 maxdatafiles 254
5 maxinstances 8
6 maxloghistory 907
7 logfile
8 group 1 '/opt/oracle/db02/oradata/orcl/redo01.log' size 50000k,
9 group 2 '/opt/oracle/db03/oradata/orcl/redo02.log' size 50000k,
10 group 3 '/opt/oracle/db04/oradata/orcl/redo03.log' size 50000k
11 datafile
12 '/opt/oracle/db02/oradata/orcl/system01.dbf',
13 '/opt/oracle/db02/oradata/orcl/tools01.dbf',
14 '/opt/oracle/db02/oradata/orcl/rbs01.dbf',
15 '/opt/oracle/db02/oradata/orcl/temp01.dbf',
16 '/opt/oracle/db02/oradata/orcl/users01.dbf',
17 '/opt/oracle/db02/oradata/orcl/indx01.dbf',
18 '/opt/oracle/db02/oradata/orcl/drsys01.dbf',
19 '/opt/oracle/db02/oradata/orcl/wacos.dbf',
20 '/opt/oracle/db02/oradata/orcl/wacos01.dbf',
21 '/opt/oracle/db02/oradata/orcl/wacos02.dbf',
22 '/opt/oracle/db02/oradata/orcl/wacos03.dbf',
23 '/opt/oracle/db02/oradata/orcl/wacos04.dbf',
24 '/opt/oracle/db02/oradata/orcl/wacos05.dbf',
25 '/opt/oracle/db02/oradata/orcl/wacos06.dbf',
26 '/opt/oracle/db02/oradata/orcl/nms.dbf',
27 '/opt/oracle/db02/oradata/orcl/test.dbf'
28 character set we8iso8859p1;
control file created.
sql> alter database open resetlogs;(以resetlogs方式打開數(shù)據(jù)庫)
database altered.
sql> select status from v$instance; (檢查數(shù)據(jù)庫的狀態(tài))
status
-------
open
sql> select * from v$logfile; (檢查logfile的狀態(tài))
group# status
---------- -------
member
--------------------------------------------------------------------------------
3
/opt/oracle/db04/oradata/orcl/redo03.log
2
/opt/oracle/db03/oradata/orcl/redo02.log
1
/opt/oracle/db02/oradata/orcl/redo01.log
sql> select * from v$controlfile; (檢查控制文件的狀態(tài))
status
-------
name
--------------------------------------------------------------------------------
/opt/oracle/db02/oradata/orcl/control01.ctl
/opt/oracle/db03/oradata/orcl/control02.ctl
/opt/oracle/db04/oradata/orcl/control03.ctl
