冷備手工完全恢復

1. 手工完全恢復三種級別：
recover database：所有或大部分datafile丟失，一般是在mount狀態完成。
recover tablespace：非關鍵表空間損壞，表空間下某些資料檔案不能訪問，一般是在open下完成。
recover datafile：單一或少數資料檔案損壞，可以在mount或open 狀態完成。
四個關鍵檔案：1）system01.dbf, 2) undo tablespace，3）control file 4）current log file

2. 手工完全恢復前提: 1）有一套datafile全備, 2）使用當前控制檔案, 3）自上次備份以來的歸檔日誌和當前聯機日誌是完整的

3. 實驗1：（recover database ）

3.1 檢視資料庫當前狀態，準備好冷備。
SQL> select * from andy;

ID
----------
1

--生成冷備指令碼
SQL> select 'ho cp ' || name || ' /home/oracle/coldbak' from v$controlfile;

'HOCP'||NAME||'/HOME/ORACLE/COLDBAK'
--------------------------------------------------------------------------------
ho cp /home/oracle/app/oradata/orcl/control01.ctl /home/oracle/coldbak
ho cp /home/oracle/app/flash_recovery_area/orcl/control02.ctl /home/oracle/coldbak

SQL> select 'ho cp ' || name || ' /home/oracle/coldbak' from v$datafile;

'HOCP'||NAME||'/HOME/ORACLE/COLDBAK'
--------------------------------------------------------------------------------
ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak

6 rows selected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

--冷備
SQL> ho cp /home/oracle/app/oradata/orcl/control01.ctl /home/oracle/coldbak
SQL> ho cp /home/oracle/app/flash_recovery_area/orcl/control02.ctl /home/oracle/coldbak
SQL> ho cp /home/oracle/app/oradata/orcl/system01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/sysaux01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/undotbs01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/users01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/tbtb01.dbf /home/oracle/coldbak
ho cp /home/oracle/app/oradata/orcl/ogg01.dbf /home/oracle/coldbak

--檢查冷備
[oracle@11g coldbak]$ ll
total 1997776
-rw-r-----. 1 oracle oinstall 9748480 Dec 10 06:22 control01.ctl
-rw-r-----. 1 oracle oinstall 9748480 Dec 10 06:25 control02.ctl
-rw-r-----. 1 oracle oinstall 408748032 Dec 10 06:32 ogg01.dbf
-rw-r-----. 1 oracle oinstall 639639552 Dec 10 06:31 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 734011392 Dec 10 06:31 system01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Dec 10 06:32 tbtb01.dbf
-rw-r-----. 1 oracle oinstall 99622912 Dec 10 06:32 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 39329792 Dec 10 06:32 users01.dbf

SQL> startup;
ORACLE instance started.

SQL> insert into andy values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from andy;

ID
----------
1
2

3.2 模擬介質失敗，所有資料檔案丟失

[oracle@11g orcl]$ rm -rf *.dbf //資料庫在開啟的情況下就刪掉

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/home/oracle/app/oradata/orcl/system01.dbf'

3.3 完全恢復流程

--檢視需要恢復的檔案
SQL> select file#,error from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND

6 rows selected.

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1969481
2 1969481
3 1969481
4 1969481
5 1969481
6 1969481

6 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 0
2 0
3 0
4 0
5 0
6 0

6 rows selected.

a 首先還原所有資料檔案
[oracle@11g orcl]$ cp /home/oracle/coldbak/*.dbf /home/oracle/app/oradata/orcl/

b 恢復database
SQL> recover database;
Media recovery complete.

c 開啟資料庫
SQL> alter database open;

Database altered.

d 驗證
SQL> select * from andy;

ID
----------
1
2

實驗2： recover tablespace （狀態：database open)

說明：針對的是非關鍵表空間的損壞，基於表空間的完全恢復實際上還是對其下的datafile的恢復

模擬這種情形非常實用，通常是某個非關鍵表空間下的資料檔案受損，但並沒有造成Oracle崩潰, 我們只需針對個別有問題的tablespace去做單獨的線上恢復操作，也就是說恢復時資料庫整體是online的,而區域性表空間是offline的，資料庫不需要shutdown。

1)瞭解一下當前狀態，有個 LZY 表空間
SQL> col file_name for a60
SQL> select file_id,file_name,tablespace_name from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME
---------- ------------------------------------------------------------ -------------------------
4 /home/oracle/app/oradata/orcl/users01.dbf USERS
3 /home/oracle/app/oradata/orcl/undotbs01.dbf UNDOTBS1
2 /home/oracle/app/oradata/orcl/sysaux01.dbf SYSAUX
1 /home/oracle/app/oradata/orcl/system01.dbf SYSTEM
5 /home/oracle/app/oradata/orcl/tbtb01.dbf LZY
6 /home/oracle/app/oradata/orcl/ogg01.dbf GOLDGATE

6 rows selected.

2）準備實驗資料

SQL> create table andy.andydemo(id int) tablespace lzy;

Table created.

SQL> inset into andy.andydemo values(1);
SP2-0734: unknown command beginning "inset into..." - rest of line ignored.
SQL> insert into andy.andydemo values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from andy.andydemo;

ID
----------
1
3）模擬表空間損壞，資料庫open下，直接刪除表空間下的資料檔案

SQL> ho rm -rf /home/oracle/app/oradata/orcl/tbtb01.dbf

4）查證該表空間上的表不可訪問了

SQL> alter system flush buffer_cache; --清除data buffer

System altered.

SQL> select * from andydemo;
select * from andydemo
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/home/oracle/app/oradata/orcl/tbtb01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

5）看看scn的情況
SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1969484
2 1969484
3 1969484
4 1969484
5 1969484
6 1969484

6 rows selected.

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1969484
2 1969484
3 1969484
4 1969484
5 0
6 1969484

6 rows selected.

說明：
v$datafile是檢視來自控制檔案裡記錄的資料檔案scn資訊。
v$datafile_header是檢視資料檔案頭中資料檔案scn資訊。

6）表空間offline --immediate使表空間能立即離線，不等Oracle對任何資料檔案做檢查

SQL> alter tablespace lzy offline immediate;

Tablespace altered.

7）資料庫open下，使用備份還原這個表空間下的所有資料檔案。

SQL> ho cp /home/oracle/coldbak/tbtb01.dbf /home/oracle/app/oradata/orcl/

8）恢復tablespace

SQL> recover tablespace lzy;
Media recovery complete.

9）使表空間online

SQL> alter tablespace lzy online;

Tablespace altered. //注意：此時資料庫狀態一直是open的。

10) 檢驗

SQL> select * from andydemo;

ID
----------
1

實驗3：（recover datafile，database mount或open狀態）

恢復datafile, 同實驗2不同的是模擬UNDO檔案損壞: 因UNDO資料檔案也是關鍵檔案，所以只能在mount狀態下恢復。

1）模擬環境：

SQL> delete andy.andy; //注意：刪掉了andy並提交，老值在UNDO裡。
3 rows deleted.
SQL>commit；

2）在open 狀態下刪除datafile
SQL> ho rm -rf /home/oracle/app/oradata/orcl/undotbs01.dbf

SQL> insert into andy.andy values(4); /undo檔案已經不在了，dbwr未來得及重新整理，此條記錄在redo裡
SQL>commit；

3）關閉資料庫
SQL> shutdown abort;
ORACLE instance shut down. //abort埋下伏筆，等到完全恢復時會做UNDO回滾。

4) 啟動資料庫mount
SQL> startup;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2220200 bytes
Variable Size 729812824 bytes
Database Buffers 331350016 bytes
Redo Buffers 5554176 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/home/oracle/app/oradata/orcl/undotbs01.dbf'

5）還原並恢復UNDO資料檔案

SQL> ho cp /home/oracle/coldbak/undotbs01.dbf /home/oracle/app/oradata/orcl/undotbs01.dbf

SQL> recover datafile 3
Media recovery complete.

6）開啟資料庫（會完成UNDO表空間資料的回滾）

SQL> alter database open;

Database altered.

7) 驗證
SQL> select * from andy;

ID
----------
4

總結：有全冷備條件下三種級別恢復流程。（相應檔名視自己的環境而定）

recover database:

SQL> select file#,error from v$recover_file;
SQL> select file#,checkpoint_change# from v$datafile;
SQL> select file#,checkpoint_change# from v$datafile_header;
SQL> ho cp /home/oracle/coldbak/*.dbf /home/oracle/app/oradata/orcl/
SQL> recover database;
SQL> alter database open;

recover tablespace:

SQL> select file#,error from v$recover_file;
SQL> select file_id,file_name,tablespace_name from dba_data_files;
SQL> select file#,checkpoint_change# from v$datafile;
SQL> select file#,checkpoint_change# from v$datafile_header;
SQL> alter tablespace lzy offline immediate;
SQL> ho cp /home/oracle/coldbak/tbtb01.dbf /home/oracle/app/oradata/orcl/
SQL> recover tablespace lzy;
SQL> alter tablespace lzy online;

recover datafile:

SQL> select file#,error from v$recover_file;
SQL> select file#,checkpoint_change# from v$datafile;
SQL> select file#,checkpoint_change# from v$datafile_header;
SQL> ho cp /home/oracle/coldbak/undotbs01.dbf /home/oracle/app/oradata/orcl/undotbs01.dbf
SQL> recover datafile 3
SQL> alter database open;

OK，轉載請標明出處。

冷備手工完全恢復（recover database，recover tablespace，recover datafile）

相關文章