資料庫恢複一例(2)

來源:互聯網
上載者:User
恢複|資料|資料庫
最近通過做實驗總結出一種資料庫恢複方法,對今後的工作很有協助:

資料庫為非歸檔狀態,只有一周前的資料檔案的備份,無redolog,歸檔日誌和controlfile的備份,此種情況一但資料庫出故障只能做不完全恢複,會丟失一周前做備份時到出故障那一時候的所有資料,具體恢複方法如下:

作業系統為solaris8,記憶體2G,2顆CPU.

實驗步驟:
$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 (關閉當前資料庫)
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit
Disconnected

類比資料丟失:

刪除當前所有的資料檔案,控制檔案和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

把一周前備份的資料檔案copy回來,目錄結構和以前一樣,但這時因為沒有redolog和controlfile檔案,資料庫只能啟動到nomount狀態:


編輯udump目錄下產生的orcl_ora_7140.trc檔案,把裡面的建立controlfile的那部分內容粘貼下來放在SQL裡執行:
(這裡要注意一定要用resetlogs方式重建控制檔案,resetlogs之後會產生新的redolog,並且把當前redofile的sequence置為1,否則建立控制檔案會失敗):
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方式開啟資料庫)
Database altered.

SQL> select status from v$instance; (檢查資料庫的狀態)
STATUS
-------
OPEN

SQL> select * from v$logfile; (檢查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


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。