表空間正在熱備份時關閉例項重啟報錯的重現和解決

還不算暈發表於2014-11-11

最近一個客戶的庫在OPEN時報錯需要恢復，發現原因為當時一個表空間正在熱備份-->ALTER TABLESPACE TEST1 BEGIN BACKUP; 然後例項異常關閉(可能為ABORT或KILL SMON等程式，這裡據說為儲存直接關閉導致)，然後重啟時遇到此錯誤。

在ORACLE 10.2.0.1及11.2.0.4版本中重現了此錯誤，在這兩個版本中同樣的情況但是報錯資訊不太一樣，具體情況如下:

10.2.0.1.0 版本表空間正在熱備份時關閉例項重啟報錯的重現和解決:

SQL> select * from v$version where rownum=1;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

檢視此時資料檔案的狀態:

SQL> select tablespace_name,STATUS from dba_tablespaces;

set linesize 200

set pagesize 200

col file_name for a50

select file_name,tablespace_name,status from dba_data_files;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS ONLINE

SYSAUX ONLINE

TEMPTS1 ONLINE

TEMP1 ONLINE

TEMP2 ONLINE

EXAMPLE ONLINE

INDX ONLINE

TOOLS ONLINE

USERS ONLINE

OLTP ONLINE

REGISTRATION ONLINE

TEST1 ONLINE

TEST2 ONLINE

TEST3 ONLINE

15 rows selected.

SQL> SQL> SQL> SQL>

FILE_NAME TABLESPACE_NAME STATUS

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

/u01/app/PROD/disk1/system01.dbf SYSTEM AVAILABLE

/u01/app/PROD/disk1/undotbs01.dbf UNDOTBS AVAILABLE

/u01/app/PROD/disk1/sysaux01.dbf SYSAUX AVAILABLE

/u01/app/PROD/disk1/example.dbf EXAMPLE AVAILABLE

/u01/app/PROD/disk1/indx.dbf INDX AVAILABLE

/u01/app/PROD/disk1/tools.dbf TOOLS AVAILABLE

/u01/app/PROD/disk1/users.dbf USERS AVAILABLE

/u01/app/PROD/disk1/oltp.dbf OLTP AVAILABLE

/u01/app/PROD/disk1/REGISTRATION.dbf REGISTRATION AVAILABLE

/u01/app/PROD/disk1/test1.dbf TEST1 AVAILABLE

/u01/app/PROD/disk1/test2.dbf TEST2 AVAILABLE

/u01/app/PROD/disk1/test3.dbf TEST3 AVAILABLE

12 rows selected.

########################################################3

-->發出熱備份表空間的命令:

SQL> ALTER TABLESPACE TEST1 BEGIN BACKUP;

Tablespace altered.

-->查詢此時資料檔案狀態:

SQL> select tablespace_name,STATUS from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS ONLINE

SYSAUX ONLINE

TEMPTS1 ONLINE

TEMP1 ONLINE

TEMP2 ONLINE

EXAMPLE ONLINE

INDX ONLINE

TOOLS ONLINE

USERS ONLINE

OLTP ONLINE

REGISTRATION ONLINE

TEST1 ONLINE

TEST2 ONLINE

TEST3 ONLINE

15 rows selected.

SQL> set linesize 200

SQL> set pagesize 200

SQL> col file_name for a50

SQL> select file_name,file_id,tablespace_name,status from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME STATUS

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

/u01/app/PROD/disk1/system01.dbf 1 SYSTEM AVAILABLE

/u01/app/PROD/disk1/undotbs01.dbf 2 UNDOTBS AVAILABLE

/u01/app/PROD/disk1/sysaux01.dbf 3 SYSAUX AVAILABLE

/u01/app/PROD/disk1/example.dbf 4 EXAMPLE AVAILABLE

/u01/app/PROD/disk1/indx.dbf 5 INDX AVAILABLE

/u01/app/PROD/disk1/tools.dbf 6 TOOLS AVAILABLE

/u01/app/PROD/disk1/users.dbf 7 USERS AVAILABLE

/u01/app/PROD/disk1/oltp.dbf 8 OLTP AVAILABLE

/u01/app/PROD/disk1/REGISTRATION.dbf 9 REGISTRATION AVAILABLE

/u01/app/PROD/disk1/test1.dbf 10 TEST1 AVAILABLE

/u01/app/PROD/disk1/test2.dbf 11 TEST2 AVAILABLE

/u01/app/PROD/disk1/test3.dbf 12 TEST3 AVAILABLE

12 rows selected.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 0

7 NOT ACTIVE 0

8 NOT ACTIVE 0

9 NOT ACTIVE 0

10 ACTIVE 195848 2014/11/11 22:12:07

11 NOT ACTIVE 0

12 NOT ACTIVE 0

12 rows selected.

-->可以看到此時有一個資料檔案處於ACTIVE狀態，結合dba_data_files中資訊，此檔案屬於下在熱備份的TEST1表空間。

此時，新開一個會話，KILL掉SMON程式，或者使用SHUTDOWN ABORT命令關閉資料庫。shutdown immediate關閉會提示如下:

SQL> shutdown immediate;

ORA-01149: cannot shutdown - file 10 has online backup set

ORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf'

--此時ALERT日誌提示

Tue Nov 11 22:50:55 2014

Shutting down instance: further logons disabled

###################################################

KILL例項程式，重新啟動報錯如下:

SQL> startup

ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 1220360 bytes

Variable Size 159383800 bytes

Database Buffers 360710144 bytes

Redo Buffers 2973696 bytes

Database mounted.

ORA-01113: file 10 needs media recovery

ORA-01110: data file 10: '/u01/app/PROD/disk1/test1.dbf'

ALERT日誌中的相關資訊:

Tue Nov 11 22:15:44 2014

ALTER DATABASE OPEN

ORA-1113 signalled during: ALTER DATABASE OPEN...

此時檢視相關檢視，10號資料檔案--TEST1表空間的仍處於活動狀態--:

SQL> select * from v$backup where STATUS='ACTIVE';

FILE# STATUS CHANGE# TIME

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

10 ACTIVE 195848 2014/11/11 22:12:07 -->這裡的時間是開始發出熱備份命令BEGIN BACKUP的時間及當時SCN。

此時，可以使用兩種命令來解決:

1.ALTER DATABASE RECOVER datafile 10;

2.ALTER TABLESPACE TEST1 END BACKUP;

#####################################################

-->如下是解決及OPEN資料庫及驗證資料檔案狀態;

SQL> ALTER DATABASE RECOVER datafile 10;

Database altered.

SQL> select * from v$backup where STATUS='ACTIVE';

no rows selected

SQL> alter database open;

Database altered.

ALERT日誌資訊:

Tue Nov 11 22:15:44 2014

ALTER DATABASE OPEN

ORA-1113 signalled during: ALTER DATABASE OPEN...

Tue Nov 11 22:17:35 2014

ALTER DATABASE RECOVER datafile 10

Tue Nov 11 22:17:35 2014

Media Recovery Start

Tue Nov 11 22:17:35 2014

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

Mem# 0 errs 0: /u01/app/PROD/disk1/redo05.log

Mem# 1 errs 0: /u01/app/PROD/disk2/redo05b.log

Tue Nov 11 22:17:36 2014

Media Recovery Complete (PROD)

Completed: ALTER DATABASE RECOVER datafile 10

檢查資料庫相關狀態:

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 0

7 NOT ACTIVE 0

8 NOT ACTIVE 0

9 NOT ACTIVE 0

10 NOT ACTIVE 195848 2014/11/11 22:12:07

11 NOT ACTIVE 0

12 NOT ACTIVE 0

12 rows selected.

SQL> select tablespace_name,STATUS from dba_tablespaces;

set linesize 200

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS ONLINE

SYSAUX ONLINE

TEMPTS1 ONLINE

TEMP1 ONLINE

TEMP2 ONLINE

EXAMPLE ONLINE

INDX ONLINE

TOOLS ONLINE

USERS ONLINE

OLTP ONLINE

REGISTRATION ONLINE

TEST1 ONLINE

TEST2 ONLINE

TEST3 ONLINE

15 rows selected.

SQL> SQL> set pagesize 200

SQL> col file_name for a50

SQL> select file_name,file_id,tablespace_name,status from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME STATUS

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

/u01/app/PROD/disk1/system01.dbf 1 SYSTEM AVAILABLE

/u01/app/PROD/disk1/undotbs01.dbf 2 UNDOTBS AVAILABLE

/u01/app/PROD/disk1/sysaux01.dbf 3 SYSAUX AVAILABLE

/u01/app/PROD/disk1/example.dbf 4 EXAMPLE AVAILABLE

/u01/app/PROD/disk1/indx.dbf 5 INDX AVAILABLE

/u01/app/PROD/disk1/tools.dbf 6 TOOLS AVAILABLE

/u01/app/PROD/disk1/users.dbf 7 USERS AVAILABLE

/u01/app/PROD/disk1/oltp.dbf 8 OLTP AVAILABLE

/u01/app/PROD/disk1/REGISTRATION.dbf 9 REGISTRATION AVAILABLE

/u01/app/PROD/disk1/test1.dbf 10 TEST1 AVAILABLE

/u01/app/PROD/disk1/test2.dbf 11 TEST2 AVAILABLE

/u01/app/PROD/disk1/test3.dbf 12 TEST3 AVAILABLE

#############################################################################################

11.2.0.4.0 版本表空間正在熱備份時關閉例項重啟報錯的重現和解決:

-->11G此問題的報錯資訊比10G更清楚，根據報錯資訊就有了基本的判斷方向。

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 1149659 2014/11/11 21:49:55

7 NOT ACTIVE 0

7 rows selected.

SQL> select sysdate from dual;

SYSDATE

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

2014/11/11 22:05:26

SQL> ALTER TABLESPACE TEST1 BEGIN BACKUP;

Tablespace altered.

SHUTDOWN ABORT例項並重啟:

SQL> startup

ORACLE instance started.

Total System Global Area 418484224 bytes

Fixed Size 1365040 bytes

Variable Size 322964432 bytes

Database Buffers 88080384 bytes

Redo Buffers 6074368 bytes

Database mounted.

ORA-10873: file 6 needs to be either taken out of backup mode or media

recovered

ORA-01110: data file 6: '/u01/app/oracle/oradata/bys1/test01.dbf'

此時ALERT日誌的報錯資訊:

Completed: ALTER DATABASE MOUNT

Tue Nov 11 22:06:36 2014

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/bys1/bys1/trace/bys1_ora_3910.trc:

ORA-10873: file 6 needs to be either taken out of backup mode or media recovered

ORA-01110: data file 6: '/u01/app/oracle/oradata/bys1/test01.dbf'

ORA-10873 signalled during: ALTER DATABASE OPEN...

Tue Nov 11 22:06:36 2014

Checker run found 1 new persistent data failures

Tue Nov 11 22:21:36 2014

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 ACTIVE 1171509 2014/11/11 22:05:40

7 NOT ACTIVE 0

7 rows selected.

END BACKUP或RECOVER DATAFILE即可

SQL> ALTER TABLESPACE TEST1 end BACKUP;

Tablespace altered.

ALERT日誌:

Tue Nov 11 23:00:56 2014

ALTER TABLESPACE TEST1 end BACKUP

Completed: ALTER TABLESPACE TEST1 end BACKUP

Tue Nov 11 23:01:23 2014

RECOVER DATAFILE時的相關日誌:

Tue Nov 11 21:51:02 2014

Checker run found 1 new persistent data failures

Tue Nov 11 22:00:46 2014

ALTER DATABASE RECOVER datafile 6

Media Recovery Start

Serial Media Recovery started

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

Mem# 0: /u01/app/oracle/oradata/bys1/redo03.log

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

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

Media Recovery Complete (bys1)

Completed: ALTER DATABASE RECOVER datafile 6

Tue Nov 11 22:01:01 2014

alter database open

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 0

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

6 NOT ACTIVE 1171509 2014/11/11 22:05:40

7 NOT ACTIVE 0

7 rows selected.

基於表空間的熱備份指令碼
2013-12-24
指令碼
PHP 實現平滑關閉/重啟
2022-05-12
PHP
openstack環境-解決windows例項重啟後比當前時間晚8小時
2018-12-19
Windows
【物理熱備】（下）備份恢復系統表空間手工備份恢復
2016-10-16
ORA-600(2662)錯誤的重現和解決（二）
2017-07-17
ORA-600(2662)錯誤的重現和解決（一）
2017-07-17
undo表空間出現壞塊導致資料庫重啟問題解決
2016-12-02
資料庫
修改系統時間導致RAC環境的一個例項重啟
2007-05-25
nginx啟動，重啟，關閉命令
2017-06-22
Nginx
nginx的啟動、關閉和平滑重啟（=）
2015-03-09
Nginx
恢復案例：熱備期間例項故障解決
2005-01-06
nginx 開啟、關閉、重啟常用操作
2020-04-02
Nginx
centos下nginx啟動、重啟、關閉
2018-06-27
CentOSNginx
MySQL伺服器異常關閉，重啟後，無法啟動----因為磁碟空間滿
2014-08-05
MySql伺服器
2 Day DBA-管理Oracle例項-關於例項的啟動和關閉-關於例項關閉
2014-01-27
Oracle
Go 如何實現熱重啟
2020-09-09
Go
nginx關閉/重啟/啟動的操作方法
2020-04-06
Nginx
Android應用關閉，重啟
2020-04-04
Android
配置單例項自動重啟
2015-06-11
單例
Oracle - 資料庫的例項、表空間、使用者、表之間關係
2014-09-18
Oracle資料庫
win10 自動重啟關閉方法_win10自動重啟怎麼關閉
2020-06-07
Win10
Oracle例項的啟動和關閉
2011-03-18
Oracle
系統表空間資料檔案丟失，無備份，無重啟，通過控制程式碼恢復
2013-01-25
RabbitMQ叢集重啟報錯
2020-10-28
MQ
MySQL 遷移表空間，備份單表
2021-05-31
MySql
[oracle]undo表空間出錯，導致資料庫例項無法開啟
2010-08-31
Oracle資料庫
Mac系統中mysql的啟動關閉重啟以及如何解決kill程式不斷重啟問題
2020-10-12
MacMySql
Oracle rman 備份與恢復臨時表空間的檔案問題解決
2016-01-27
Oracle
【ASK_ORACLE】RAC節點自動重啟但日誌裡未報錯的原因和解決方法
2022-05-19
Oracle
2 Day DBA-管理Oracle例項-關於例項的啟動和關閉-關於例項啟動
2014-01-27
Oracle
golang程式優雅關閉與重啟
2019-03-03
Golang
歸檔模式下聯機熱備份某個表空間步驟
2013-10-27
模式
啟動ASM 例項報錯
2009-02-26
ASM
2 Day DBA-管理Oracle例項-關閉和啟動Oracle例項-使用OEMDC關閉和啟動Oracle例項
2014-01-27
Oracle
Linux系統最近重啟時間、錯誤日誌
2010-12-23
Linux
2.5.9.2 重寫預設表空間型別
2020-03-05
型別
MySQL 叢集的安全關閉和重啟方法
2016-05-15
MySql
對read only表空間進行熱備份和使用備份的controlfile進行恢復時的一點總結！
2008-06-24

表空間正在熱備份時關閉例項重啟報錯的重現和解決

10.2.0.1.0 版本表空間正在熱備份時關閉例項重啟報錯的重現和解決:

-->發出熱備份表空間的命令:

KILL例項程式，重新啟動報錯如下:

此時，可以使用兩種命令來解決:

11.2.0.4.0 版本表空間正在熱備份時關閉例項重啟報錯的重現和解決:

SHUTDOWN ABORT例項並重啟:

END BACKUP或RECOVER DATAFILE即可

相關文章