使用物理備份恢複SYSTEM資料表空間

使用物理備份恢複SYSTEM資料表空間

只要存在有效備份，恢複SYSTEM資料表空間資料檔案丟失故障是比較容易的。這裡示範的是最基本的使用物理備份恢複SYSTEM資料表空間丟失的方法。

1.環境準備
我們在Oracle11g中進行測試，資料庫處於非歸檔狀態。

SQL>
 
SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL>

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 6

Current log sequence 8

SQL>

2.打tar包，進行物理備份
首先要弄清兩個概念：打包和壓縮。打包是指將一大堆檔案或目錄變成一個總的檔案；壓縮則是將一個大的檔案通過一些壓縮演算法變成一個小檔案。linux下最常用的打包程式就是tar了，使用tar程式打出來的包我們常稱為tar包，tar包檔案的命令通常都是以.tar結尾的。產生tar包後，就可以用其它的程式來進行壓縮。
 我們使用tar命令將HOEGH資料庫的物理檔案打tar包，命名為HOEGH.tar.gz。注意，物理備份必須是在資料庫關停階段進行。

[oracle@hoegh oradata]$ tar -zcvf HOEGH.tar.gz HOEGH
 
HOEGH/

HOEGH/redo03.log

HOEGH/temp01.dbf

HOEGH/control01.ctl

HOEGH/control02.ctl

HOEGH/system01.dbf

HOEGH/sysaux01.dbf

HOEGH/users01.dbf

HOEGH/undotbs01.dbf

HOEGH/example01.dbf

HOEGH/redo02.log

HOEGH/redo01.log
 
3.啟動資料庫，刪除system資料檔案
下面，我們來類比system資料檔案丟失的故障情境。
 首先，啟動資料庫。

SQL> startup
 
ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Database mounted.

Database opened.

SQL>
接下來，刪除system01.dbf資料檔案。

[oracle@hoegh HOEGH]$ rm system01.dbf
 
[oracle@hoegh HOEGH]$
 
4.重啟資料庫報錯ORA-01157和ORA-01110
刪除資料檔案後，我們重啟資料庫，資料庫在嘗試啟動到open狀態時，由於找不到system資料表空間的資料檔案，報錯。

SQL>
 
SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORACLE instance started.

Total System Global Area 941600768 bytes

Fixed Size 1348860 bytes

Variable Size 515902212 bytes

Database Buffers 419430400 bytes

Redo Buffers 4919296 bytes

Database mounted.

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

ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'

SQL>

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL>
我們看到，這個時候資料庫處於mount狀態。查看警示記錄檔，我們可以更清晰的看到整個過程。

[oracle@hoegh trace]$ pwd
 
/u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace

[oracle@hoegh trace]$

[oracle@hoegh trace]$ tailf alert_HOEGH.log

……

ALTER DATABASE MOUNT

Successful mount of redo thread 1, with mount id 2106090167

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Sat Jul 11 09:01:47 2015

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_dbw0_6016.trc:

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

ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_6135.trc:

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

ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'

ORA-1157 signalled during: ALTER DATABASE OPEN...
 
5.恢複資料檔案
我們需要把之前的資料備份恢複到資料庫當中，因此，首先我們就要解tar包，恢複之前備份的資料檔案；然後，將備份的system資料檔案拷貝到HOEGH資料檔案目錄當中。

[oracle@hoegh oradata]$ mkdir -p back
 
[oracle@hoegh oradata]$

[oracle@hoegh oradata]$ tar -zxvf HOEGH.tar.gz -C back/

HOEGH/

HOEGH/redo03.log

HOEGH/temp01.dbf

HOEGH/control01.ctl

HOEGH/control02.ctl

HOEGH/system01.dbf

HOEGH/sysaux01.dbf

HOEGH/users01.dbf

HOEGH/undotbs01.dbf

HOEGH/example01.dbf

HOEGH/redo02.log

HOEGH/redo01.log

[oracle@hoegh oradata]$

[oracle@hoegh oradata]$ cp back/HOEGH/system01.dbf HOEGH/

[oracle@hoegh oradata]$
 
6.恢複資料庫
首先，嘗試使用alter database open;命令開啟資料庫，我們看到系統提示需要進行介質恢複。
 接下來，使用recover database;命令恢複資料庫；
 最後，再次使用alter database open;命令開啟資料庫。

SQL>
 
SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'

SQL>

SQL> recover database;

Media recovery complete.

SQL>

SQL> alter database open;

Database altered.

SQL>

SQL> select status from v$instance;

STATUS

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

OPEN

SQL>
此時我們看到資料庫已經處於open狀態了，至此我們成功地使用物理備份恢複了之前“丟失”的system資料檔案。通過alert警示日誌我們再來看一下介質恢複以及開啟資料庫的整個過程。

Sat Jul 11 09:02:46 2015
 
alter database open

Errors in file /u01/app/oracle/diag/rdbms/HOEGH/HOEGH/trace/HOEGH_ora_6135.trc:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: \'/u01/app/oracle/oradata/HOEGH/system01.dbf\'

ORA-1113 signalled during: alter database open...

ALTER DATABASE RECOVER database

Media Recovery Start

 started logmerger process

Sat Jul 11 09:02:53 2015

 Recovering data file 1 from a fuzzy backup. It might be an online

backup taken without entering the begin backup command.

Parallel Media Recovery started with 2 slaves

Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/HOEGH/redo01.log

Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/HOEGH/redo02.log

Media Recovery Complete (HOEGH)

Completed: ALTER DATABASE RECOVER database

Sat Jul 11 09:03:23 2015

alter database open

Beginning crash recovery of 1 threads

 parallel recovery started with 2 processes

Started redo scan

Completed redo scan

 read 0 KB redo, 0 data blocks need recovery

Started redo application at

 Thread 1: logseq 8, block 878, scn 919739

Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/HOEGH/redo02.log

Completed redo application of 0.00MB

Completed crash recovery at

 Thread 1: logseq 8, block 878, scn 939740

 0 data blocks read, 0 data blocks written, 0 redo k-bytes read

Sat Jul 11 09:03:24 2015

Thread 1 advanced to log sequence 9 (thread open)

Thread 1 opened at log sequence 9

  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/HOEGH/redo03.log

Successful open of redo thread 1

MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set

Sat Jul 11 09:03:24 2015

SMON: enabling cache recovery

[6135] Successfully onlined Undo Tablespace 2.

Undo initialization finished serial:0 start:1328894 end:1328914 diff:20 (0 seconds)

Verifying file header compatibility for 11g tablespace encryption..

Verifying 11g file header compatibility for tablespace encryption completed

SMON: enabling tx recovery

Database Characterset is AL32UTF8

No Resource Manager plan active

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Sat Jul 11 09:03:24 2015

QMNC started with pid=22, OS id=6188

Completed: alter database open
從警示日誌我們看到，資料庫通過redo重做記錄檔完成了介質恢複；然後，檢查redo、undo等正常後，順利開啟資料庫。
 
備份第一
  備份是系統中需要考慮的最重要的事項，雖然它在系統的整個規劃、開發與測試過程中甚至占不到1%，看似不太重要且默默無聞的工作只有到恢複的時候才能真正體現出其重要性，任何資料的丟失與長時間的資料down機,都是不可以被接受的。當然，僅僅依靠物理備份做不到這一點，RMAN的功能更為強大。