資料庫CONCEPT （二）

kewin發表於2011-09-19

資料庫

資料庫CONCEPT （二）

-UNDO 表空間研究

Kevin Zou

2011-9-19

在一個資料庫中，只能有一個UNDO 表空間。有兩種方式來設定的預設的UNDO 表空間：

1）在資料庫啟動過程中，設定初始化引數來設定；

2）通過ALTER SYSTEM SET UNDO_TABLESPACE 來修改，但這個方法少用；

設定預設UNDO 表空間的引數：undo_tablespace

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

第二方法的例子：

SQL> create undo tablespace undo02 datafile 'D:\ORACLE\ORADATA\TEST\undo02.dbf'

size 10M;

表空間已建立。

SQL> select TABLESPACE_NAME, CONTENTS,

2 EXTENT_MANAGEMENT, ALLOCATION_TYPE,

3 SEGMENT_SPACE_MANAGEMENT

4 from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME CONTENTS EXTENT_MAN ALLOCATIO SEGMEN

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

UNDOTBS1 UNDO LOCAL SYSTEM MANUAL

UNDO02 UNDO LOCAL SYSTEM MANUAL

SQL> alter system set undo_tablespace ='UNDO02';

系統已更改。

SQL> show parameter undo_tablespace

NAME TYPE VALUE

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

undo_tablespace string UNDO02

如果UNDO 表空間中有事務LOG用來事務恢復的，那就不能刪除該UNDO 表空間；

在一個SESSION中：

SQL> create undo tablespace undo02 datafile 'D:\ORACLE\ORADATA\TEST\undo02.dbf'

size 5M;

表空間已建立。

SQL> alter system set undo_tablespace ='UNDO02';

系統已更改。

在一個新的session中：

SQL> insert into t values('1','2','3');

已建立 1 行。

回到第一個SESSION中：

SQL> alter system set undo_tablespace ='UNDOTBS1';

系統已更改。

SQL> DROP tablespace undo02;

DROP tablespace undo02

第 1 行出現錯誤:

ORA-30013: 還原表空間 'UNDO02' 當前正在使用中

SQL> select roll.SEGMENT_NAME, rs.CURBLK, rs.status

2 from DBA_ROLLBACK_SEGS roll, V$ROLLSTAT rs

3 where roll.tablespace_name='UNDO02' AND roll.SEGMENT_ID = rs.USN;

SEGMENT_NAME CURBLK STATUS

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

_SYSSMU20$ 1 PENDING OFFLINE

在有事務的視窗提交事務,再回到第一個視窗上查詢（這個可能需要等幾秒中才能看到變化：

SQL> select roll.SEGMENT_NAME, rs.CURBLK, rs.status

2 from DBA_ROLLBACK_SEGS roll, V$ROLLSTAT rs

3 where roll.tablespace_name='UNDO02' AND roll.SEGMENT_ID = rs.USN

4 ;

未選定行

SELECT * FROM DBA_ROLLBACK_SEGS

_SYSSMU20$ PUBLIC UNDO02 20

7 153 131072 2 32765

OFFLINE

這時UNDO02上的ROLLBACK 段已經OFFLINE，這時可以刪除UNDO02 表空間：

SQL> drop tablespace undo02 including contents and datafiles;

表空間已刪除。

可能在刪除UNDO 表空間時，遇到這樣的錯誤提示：

ORA-01548: active rollback segment 或者 Undo segment shows status as needs recovery.

造成這樣的原因是：在UNDO 表空間的DATAFILE已經OFFLINE，因為OFFLINE的資料檔案事務不能回滾；或者是UNDO 段自身的任何原因；

例子：

SQL> create undo tablespace undo02 datafile 'D:\ORACLE\ORADATA\TEST\undo03.dbf'

size 10M;

表空間已建立。

SQL> alter system set undo_tablespace ='undo02';

系統已更改。

這時新開啟一個SESSION，執行下面的操作：

SQL> insert into t values('1','2','3');

已建立 1 行。

回到第一個SESSION的視窗：

SQL> alter database datafile 'D:\ORACLE\ORADATA\TEST\undo03.dbf' offline;

alter database datafile 'D:\ORACLE\ORADATA\TEST\undo03.dbf' offline

第 1 行出現錯誤:

ORA-00603: ORACLE 伺服器會話因致命錯誤而終止

SQL> select * from v$instance;

ERROR:

ORA-03114: 未連線到 ORALCE

這時會話被提出ORACLE，但是ORACLE還是在執行中。

SQL> conn /as sysdba

已連線。

如果用非SYS使用者登入會報錯：

SQL> conn kk/kk

ERROR:

ORA-24315: 非法的屬性型別

在執行INSERT 語句的視窗中執行：

SQL> conn /as sysdba

ERROR:

ORA-24313: 使用者已獲得證明

SQL> select count(*) from t;

SP2-0640: 未連線

新開一個視窗用SYSDBA登入，沒有發現有異常。

oracle@sjfdcpdb11:~> oerr ora 24313

24313, 00000, "user already authenticated"

// *Cause: A user has already been authenticated on this service handle.

// *Action: Terminate the service context before using it for another user.

oracle@sjfdcpdb11:~> oerr ora 24315

24315, 00000, "illegal attribute type"

// *Cause: An illegal attribute type was specified for the handle.

// *Action: Consult user manual to specify an attribute valid for this handle.

檢視檔案的狀態：

NAME STATUS

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

D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF SYSTEM

D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\USERS01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\KK01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\B01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\UNDO03.DBF RECOVER

可以看到UNDO03.DBF需要做RECOVER。

SQL> select count(*) from t;

select count(*) from t

第 1 行出現錯誤:

ORA-00376: 此時無法讀取檔案 7

ORA-01110: 資料檔案 7: 'D:\ORACLE\ORADATA\TEST\UNDO03.DBF'

因為在UNDO03.DBF offline前，對TBALE T進行插入的操作，而且這個TRANSACTION 沒有提交。所以要用到UNDO 表空間來做一致性讀。但由於UNDO 資料檔案的OFFLINE，這時的讀取操作失敗。

可以正常對其他物件讀取。

SQL> select count(*) from dba_objects;

COUNT(*)

----------

49792

這時整個資料庫不接受新的事務，只能做查詢。

SQL> insert into t values('1','2','3');

insert into t values('1','2','3')

第 1 行出現錯誤:

ORA-00376: 此時無法讀取檔案 7

ORA-01110: 資料檔案 7: 'D:\ORACLE\ORADATA\TEST\UNDO03.DBF'

如果這個檔案需要做RECOVER，那可以按照下面的步驟來操作：

從檔案頭找到最後的change number。

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

FILE# CHECKPOINT_CHANGE#

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

1 2106236

2 2106236

3 2106236

4 2106236

5 2106236

6 2106236

7 2111552

從之前的輸出，我們可知道undo03.dbf的檔案號為7.

查詢下需要哪些ARCHIVEDLOG 來做恢復：

SQL> Select sequence#,thread#,name from v$archived_log where 2111552 between first_change# and next_change# ;

未選定行

這說明這個change還沒有歸檔。

可以直接把檔案ONLINE：

SQL> conn /as sysdba

已連線。

SQL> select name, status from v$datafile;

NAME STATUS

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

D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF SYSTEM

D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\USERS01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\KK01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\B01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\UNDO03.DBF OFFLINE

已選擇7行。

SQL> alter database datafile 'D:\ORACLE\ORADATA\TEST\UNDO03.DBF' online;

資料庫已更改。

SQL> select name, status from v$datafile;

NAME STATUS

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

D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF SYSTEM

D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\USERS01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\KK01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\B01.DBF ONLINE

D:\ORACLE\ORADATA\TEST\UNDO03.DBF ONLINE

這時系統恢復正常。

檢視下log：

SMON/PMON都發現了UNDO03.DBF檔案異常，首先是SMON寫到LOG中，然後PMON開始一直跟蹤，知道問題解決。

alter database datafile 'D:\ORACLE\ORADATA\TEST\undo03.dbf' offline

Mon Sep 19 11:37:56 2011

ORA-376 signalled during: alter database datafile 'D:\ORACLE\ORADATA\TEST\undo03.dbf' offline...

Mon Sep 19 11:37:56 2011

Errors in file d:\oracle\product\admin\test\udump\test_ora_4872.trc:

ORA-00376: 此時無法讀取檔案 7

ORA-01110: 資料檔案 7: 'D:\ORACLE\ORADATA\TEST\UNDO03.DBF'

ORA-00376: 此時無法讀取檔案 7

ORA-01110: 資料檔案 7: 'D:\ORACLE\ORADATA\TEST\UNDO03.DBF'

ORA-00376: 此時無法讀取檔案 7

ORA-01110: 資料檔案 7: 'D:\ORACLE\ORADATA\TEST\UNDO03.DBF'

Mon Sep 19 11:37:56 2011

Errors in file d:\oracle\product\admin\test\bdump\test_smon_2648.trc:

ORA-00376: file 7 cannot be read at this time