基於歸檔的熱備份完全恢復

nathanzhn發表於2014-02-19

原理與基於歸檔的冷備份完全恢復基本相同，場景為某資料檔案損壞或丟失，需要做歸檔模式下的完全恢復：

完全恢復的條件：

有最近的一次資料檔案備份

控制檔案和聯機日誌檔案沒有損壞

從備份時間開始到資料檔案損壞時的所有歸檔日誌檔案完好

一、            首先做熱備份

Dfd

SYS@PROD>alter database begin backup;

 

Database altered.

SYS@PROD>! cp /u01/app/oracle/oradata/PROD/disk1/system001.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/example1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/indx1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/tools1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/users1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf /home/oracle/hotbackup/20140125-1

cp /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf /home/oracle/hotbackup/20140125-1

SYS@PROD>alter database end backup;

 

Database altered.

二、            以下三-六為四種恢復場景及其對應的恢復步驟，主要故障為表空間tbs_tommie對應的資料檔案tbs_tommie01.dbf被刪除了，資料庫啟動報錯

 [oracle@odd-oelr4u8 disk1]$ mv tbs_tommie01.dbf tbs_tommie01.dbf.bak

SYS@PROD>startup

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              75498896 bytes

Database Buffers          234881024 bytes

Redo Buffers                2973696 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

 

三、            關閉資料庫時恢復

a)        在資料庫mount狀態下恢復資料庫檔案，該方法可以恢復system、undo 或整個資料庫。

b)        恢復步驟：

                        i.              還原所有歸檔日誌-歸檔日誌可能還在歸檔目錄下，也可能被備份到了其他目錄，而歸檔目錄下的歸檔已被清除，此時需要還原

                      ii.              裝入資料庫-startup mount

                    iii.              還原損壞資料檔案-cp 某datafile

                     iv.              恢復資料檔案（資料庫）-recover AUTOMATIC DATAFILE ‘.dbf’或recover database;

                       v.              開啟資料庫-alter database open;

Fdd

SYS@PROD>select open_mode from v$database;

 

OPEN_MODE

--------------------

MOUNTED

 

[oracle@odd-oelr4u8 disk1]$ cp /home/oracle/hotbackup/20140125-1/tbs_tommie01.dbf /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf

SYS@PROD>recover datafile 9;

Media recovery complete.

 

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>conn timmie/timmie

Connected.

TIMMIE@PROD>select * from t1;

 

         A

----------

         1

         2

         3

四、            資料庫在開啟狀態下恢復

a)        在資料庫open 狀態下恢復表空間的資料檔案，該方法不可以恢復system、undo或整個資料庫。

b)        恢復步驟：

                        i.              還原所有歸檔日誌

                      ii.              將表空間或資料檔案離線

                    iii.              還原損壞資料檔案

                     iv.              恢復表空間或資料庫檔案

                       v.              資料檔案或表空間聯機

                     vi.              開啟資料庫

    [oracle@odd-oelr4u8 disk1]$ rm tbs_tommie01.dbf

建表報錯資料檔案找不到了

TIMMIE@PROD>create table t2(b int);

create table t2(b int)

*

ERROR at line 1:

ORA-01116: error in opening database file 9

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

[oracle@odd-oelr4u8 disk1]$ mv tbs_tommie01.dbf.bak2 tbs_tommie01.dbf

SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf' offline;

或

SYS@PROD>alter tablespace tbs_tommie offline;

 

SYS@PROD>recover datafile 9;

或

SYS@PROD>recover tablespace tbs_tommie;

Media recovery complete.

SYS@PROD>alter database datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf' online;

 

或

SYS@PROD>alter tablespace tbs_tommie online;

SYS@PROD>conn timmie/timmie

Connected.

TIMMIE@PROD>create table t2(b int);

 

Table created.

五、            在資料庫開啟後恢復

a)        在資料庫mount狀態下使損壞資料檔案離線，將資料庫開啟後再進行恢復

b)        恢復步驟：

                        i.              還原所有歸檔日誌

                      ii.              裝入資料庫

                    iii.              資料檔案離線

                     iv.              開啟資料庫

                       v.              還原損壞資料檔案

                     vi.              恢復資料庫檔案

                   vii.              資料檔案聯機

TIMMIE@PROD>conn / as sysdba

Connected.

SYS@PROD>shutdown immediate

ORA-01116: error in opening database file 9

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@PROD>shutdown abort

ORACLE instance shut down.

SYS@PROD>startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

 

SYS@PROD>alter database datafile 9 offline;

 

Database altered.

 

SYS@PROD>alter tablespace tbs_tommie offline;

alter tablespace tbs_tommie offline

*

ERROR at line 1:

ORA-01109: database not open

 

 

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>alter database datafile 9 online;

alter database datafile 9 online

*

ERROR at line 1:

ORA-01113: file 9 needs media recovery

ORA-01110: data file 9: '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf'

 

 

SYS@PROD>recover datafie 9 ;

ORA-00905: missing keyword

 

 

SYS@PROD>recover datafile 9 ;

Media recovery complete.

SYS@PROD>alter database datafile 9 online;

 

Database altered.

六、            恢復沒有備份的資料檔案

a)        如果資料檔案沒有備份，而從建立該表空間以來所有日誌都存在，可以在開啟資料庫的狀態下進行恢復。

b)        恢復步驟：

                        i.              還原所有歸檔日誌

                      ii.              將表空間或資料檔案離線

                    iii.              建立新的資料檔案

                     iv.              恢復資料庫檔案

                       v.              資料表空間聯機

–該場景為關機後，資料檔案丟失，然後重建，則不需要資料檔案離線和聯機的步驟

 

SYS@PROD>create tablespace tbs_amy datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' size 10m;

 

Tablespace created.

 

SYS@PROD>create user amy identified by amy default tablespace tbs_amy;

 

User created.

 

SYS@PROD>grant connect,resource to amy;

 

Grant succeeded.

 

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>create table t_amy01(a int);

 

Table created.

 

 

AMY@PROD>insert into t_amy01 values(10);

 

1 row created.

 

AMY@PROD>commit;

 

Commit complete.

 

AMY@PROD>conn / as sysdba

Connected.

SYS@PROD>alter system switch logfile;

 

System altered.

n  資料10進入歸檔

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>insert into t_amy01 values(20);

 

1 row created.

 

AMY@PROD>commit;

 

Commit complete.

n  資料20在聯機日誌裡

AMY@PROD>conn / as sysdba

Connected.

SYS@PROD>shutdown immediate –該場景為關機後，資料檔案丟失，然後重建

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@PROD>startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@PROD>alter database create datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' as '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf';

 

Database altered.

 

SYS@PROD>recover datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf';

Media recovery complete.

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>select * from t_amy01;

 

         A

----------

        10

            20

 

--該場景為資料庫開啟時資料檔案丟失且無備份，然後進行恢復

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>create table t_amy02(b int);

create table t_amy02(b int)

*

ERROR at line 1:

ORA-01116: error in opening database file 10

ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

 

 

AMY@PROD>conn / as sysdba

Connected.

SYS@PROD>shutdown immediate

ORA-01116: error in opening database file 10

ORA-01110: data file 10: '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SYS@PROD>alter database datafile 10 offline;

 

Database altered.

 

SYS@PROD>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@PROD>startup mount

ORACLE instance started.

 

Total System Global Area  314572800 bytes

Fixed Size                  1219184 bytes

Variable Size              79693200 bytes

Database Buffers          230686720 bytes

Redo Buffers                2973696 bytes

Database mounted.

SYS@PROD>alter database create datafile '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf' as '/u01/app/oracle/oradata/PROD/disk1/tbs_amy01.dbf';

 

Database altered.

 

SYS@PROD>recover datafile 10;

Media recovery complete.

SYS@PROD>alter database open;

 

Database altered.

 

SYS@PROD>alter database datafile 10 online;

 

Database altered.

 

SYS@PROD>conn amy/amy

Connected.

AMY@PROD>create table t_amy02(b int);

 

Table created.

理解：ORA-01178: file 9 created before last CREATE CONTROLFILE, cannot recreate

在操作datafile 9，表空間tbs_tommie對應的資料檔案tbs_tommie01.dbf的時候出現過這個錯誤，導致無法create datafile。總結一下應該是該datafile 9建立後做過create controlfile的操作。因此如果恢復過資料庫，最好還是馬上做一下全庫備份。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26521853/viewspace-1083933/，如需轉載，請註明出處，否則將追究法律責任。

相關文章