【備份恢復】 控制檔案之版本不一致 之恢復操作

 版本不一致
1) 模擬錯誤

[oracle@wang ORA11GR2]$ pwd

/u01/app/oracle/oradata/ORA11GR2

[oracle@wang ORA11GR2]$ ls control*

control01.ctl  control02.ctl

[oracle@wang ORA11GR2]$ cp control02.ctl  control02.ctl.bak

[oracle@wang ORA11GR2]$ ls control*

control01.ctl  control02.ctl  control02.ctl.bak

[oracle@wang ORA11GR2]$

 

2) 以 sysdba 身份登入 sqlplus，手動觸發一下檢查點（這步可執行可不執行，執行的目的是，讓控
制檔案快速的版本不一致，當然，就算不執行，也是不一致的）

[oracle@wang ORA11GR2]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 23:00:00 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ORA11GR2>alter system checkpoint;

 

System altered.

 

3)刪除當前 2 號控制檔案，並將之前備份的控制檔案改為 2 號控制檔案

SYS@ORA11GR2>host ls control*

control01.ctl  control02.ctl  control02.ctl.bak

 

SYS@ORA11GR2>ho rm control02.ctl

 

SYS@ORA11GR2>ho ls control*

control01.ctl  control02.ctl.bak

 

SYS@ORA11GR2>! mv control02.ctl.bak control02.ctl

 

SYS@ORA11GR2> ! ls control*

control01.ctl  control02.ctl

 

4) 再次執行檢查點，執行正常（雖然執行 alter system checkpoint;是完全檢查點，但 oracle實際上並未真正同步 SCN，還在“排隊中”），建立表空間，報 ORA-03113 錯誤。注意： 此時， sqlplus 已經斷開連線，資料庫 down 了下來

 

SYS@ORA11GR2>create tablespace ts_control datafile   2  '/u01/app/oracle/oradata/ORA11GR2/ts_control.dbf' size 10m;

ERROR:

ORA-03114: not connected to ORACLE

 

create tablespace ts_control datafile

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 2964

Session ID: 39 Serial number: 367

 

檢視alert日誌：

[oracle@wang trace]$ pwd

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace

[oracle@wang trace]$ tail -100f alert_ORA11GR2.log

 

注 在建立表空間報錯時， alert 日誌有如下輸出

------------------------------------------------------------------------------
Thu Sep 22 23:03:11 2016

Errors in file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_m000_3187.trc:

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/ORA11GR2/control02.ctl'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Thu Sep 22 23:07:32 2016

create tablespace ts_control datafile

'/u01/app/oracle/oradata/ORA11GR2/ts_control.dbf' size 10m

********************* ATTENTION: ********************

 The controlfile header block returned by the OS

 has a sequence number that is too old.

 The controlfile might be corrupted.

 PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE

 without following the steps below.

 RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE

 TO THE DATABASE, if the controlfile is truly corrupted.

 In order to re-start the instance safely,

 please do the following:

 (1) Save all copies of the controlfile for later

     analysis and contact your OS vendor and Oracle support.

 (2) Mount the instance and issue:

     ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

 (3) Unmount the instance.

 (4) Use the script in the trace file to

     RE-CREATE THE CONTROLFILE and open the database.

*****************************************************

USER (ospid: 2964): terminating the instance

Thu Sep 22 23:07:32 2016

System state dump requested by (instance=1, osid=2964), summary=[abnormal instance termination].

System State dumped to trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_diag_28867_20160922230732.trc

Dumping diagnostic data in directory=[cdmp_20160922230732], requested by (instance=1, osid=2964), summary=[abnormal instance termination].

Instance terminated by USER, pid = 2964

5) 退出 sqlplus，重新登入
注意： 此時提示，連線到一個空閒例項

[oracle@wang backup]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 23:19:50 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

6) 啟動資料庫，此時提示，控制檔案版本不統一

SYS@ORA11GR2>startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

ORA-00214: control file '/u01/app/oracle/oradata/ORA11GR2/control01.ctl'

version 2057 inconsistent with file

'/u01/app/oracle/oradata/ORA11GR2/control02.ctl' version 2055

 

7) 回到 linux 系統，刪除版本低的 2號控制檔案， 透過版本比較高的 1 號控制檔案複製建立一個2 號控制檔案

[oracle@wang backup]$ cd /u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2]$ ls cont*

control01.ctl  control02.ctl

[oracle@wang ORA11GR2]$ rm control02.ctl

[oracle@wang ORA11GR2]$ ls cont*

control01.ctl

[oracle@wang ORA11GR2]$ cp control01.ctl control02.ctl

[oracle@wang ORA11GR2]$ ls cont*

control01.ctl  control02.ctl

[oracle@wang ORA11GR2]$

 

8) 回到 sqlplus，將資料庫啟動到 mount 狀態下， mount 成功，說明控制檔案版本已經統一， open資料庫

[oracle@wang ORA11GR2]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 22 23:24:41 2016

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SYS@ORA11GR2>select status from v$instance;

 

STATUS

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

STARTED

 

SYS@ORA11GR2>alter database mount;

 

Database altered.

 

SYS@ORA11GR2>alter database open;

 

Database altered.

 

恢復成功！！！！！！！！！！！！！！！！！