Oracle資料庫恢複一例

oracle9i復原段資料表空間丟失後的處理方法:

用隱含參數恢複資料庫的例子：

具體操作步驟如下：

首先把初init.ora檔案裡自動管理改為手工管理,然後加入隱含參數:
#undo_management=AUTO
undo_tablespace=UNDOTBS
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL>startup mount （資料庫啟動到mount狀態)
SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF' offline drop；
Database altered.

SQL>alter database open;
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ---------
undo_management string MANUAL
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS

SQL> drop tablespace undotbs including contents;
Tablespace dropped.

重建undotbs資料表空間:
SQL> create undo tablespace undotbs datafile 'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
size 100M;
Tablespace created.

SQL> shutdown immediate (關閉資料庫)
Database closed.
Database dismounted.
ORACLE instance shut down.

編輯init.ora初始化參數檔案,去掉隱含參數，設定
undo_management=AUTO
undo_tablespace=UNDOTBS
儲存init.ora檔案，然後執行
SQL> startup mount
ORACLE instance mounted.
Total System Global Area 114061244 bytes
Fixed Size 282556 bytes
Variable Size 79691776 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.

SQL>alter database datafile 'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF' online;
Database altered.

SQL>alter database open;
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS

最近通過做實驗總結出一種資料庫恢複方法，對今後的工作很有協助：

資料庫為非歸檔狀態，只有一周前的資料檔案的備份，無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

本文為轉載