SYSAUX表空間管理及恢復

分類： Oracle 備份恢復 2010-08-13 20:36 530人閱讀評論(0) 舉報

--================================

-- SYSAUX表空間管理及恢復

--================================

SYSAUX表空間是在10g之後引入的一個新的表空間，主要用於減輕對SYSTEM表空間的壓力而作為SYSTEM表空間的輔助表空間。

原來存放於SYSTEM表空間的很多元件以及一些資料庫後設資料在10g中被移植到SYSAUX表空間。

SYSAUX表空間在正常的資料庫操作中不能被刪除，或重新命名，也不支援可移動表空間功能，但可以離線。如果SYSAUX表空間

失效，比如發生介質故障後有些資料庫的功能會隨之失效。

本文先描述一下SYSAUX表空間的管理特性，最後演示SYSAUX表空間丟失後的恢復過程

關於表空間及資料檔案請參考：Oracle 表空間與資料檔案

關於Oracle體系結構請參考：Oracle例項和Oracle資料庫(Oracle體系結構)

一、SYSAUX表空間的內容

可以從檢視V$SYSAUX_OCCUPANTS中獲得SYSAUX的相關資訊

SQL> col occupant_name format a30

SQL> col occupant_desc format a40

SQL> col schema_name format a15

SQL> set linesize 200

SQL> select occupant_name,occupant_desc,schema_name,space_usage_kbytes/1024

2 from v$sysaux_occupants;

OCCUPANT_NAME OCCUPANT_DESC SCHEMA_NAME SPACE_USAGE_KBYTES/1024

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

LOGMNR LogMiner SYSTEM 5.9375

LOGSTDBY Logical Standby SYSTEM .875

STREAMS Oracle Streams SYS .5

XDB XDB XDB 48.5625

AO Analytical Workspace Object Table SYS 19.6875

XSOQHIST OLAP API History Tables SYS 19.6875

XSAMD OLAP Catalog OLAPSYS 15.5625

SM/AWR Server Manageability - Automatic Workloa SYS 34.6875

d Repository

----------部分結果省略---------------

二、SYSAUX的特性

1.不能被刪除

SQL> drop tablespace sysaux;

drop tablespace sysaux

*

ERROR at line 1:

ORA-13501: Cannot drop SYSAUX tablespace

SQL> drop tablespace sysaux including contents and datafiles;

drop tablespace sysaux including contents and datafiles

*

ERROR at line 1:

ORA-13501: Cannot drop SYSAUX tablespace

2.不能被重新命名

SQL> alter tablespace sysaux rename to sysaux_2;

alter tablespace sysaux rename to sysaux_2

*

ERROR at line 1:

ORA-13502: Cannot rename SYSAUX tablespace

3.不能置為只讀

SQL> alter tablespace sysaux read only;

alter tablespace sysaux read only

*

ERROR at line 1:

ORA-13505: SYSAUX tablespace can not be made read only

4.可以被離線

SQL> alter tablespace sysaux offline;

Tablespace altered.

SQL> alter tablespace sysaux online;

Tablespace altered.

三、冷備模式下恢復SYSAUX表空間(系統已經被冷備份且處於非歸檔模式下)

1.冷備以來控制檔案沒有被重建,也沒有執行resetlogs,則可以使用備份還原，然後使用忽略一致性驗證引數來恢復

2.否則只能離線sysaux資料檔案，然後以表形式匯出資料，再新建的資料庫中,把匯出的資料導回。

3.全備資料庫

SQL> select log_mode from v$database; --檢視資料的歸檔狀態為非歸檔模式

LOG_MODE

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

NOARCHIVELOG

SQL> ho ls /u01/app/oracle/coolbak --檢視冷備路徑下備份的檔案

control01.ctl orapworcl redo2b.rdo system01.dbf users01.dbf

control02.ctl redo1a.rdo redo3a.rdo tbs1_1.dbf

example01.dbf redo1b.rdo redo3b.rdo tbs1_2.dbf

initorcl.ora redo2a.rdo sysaux01.dbf undotbs01.dbf

SQL> ho rm /u01/app/oracle/coolbak/* --將冷備路徑下先前的備份檔案刪除 */

SQL> ho cat /tmp/tmpbak2.sql --檢視冷備指令碼

set feedback off

set heading off

set verify off

set trimspool off

set pagesize 0

set linesize 200

define dir = '/u01/app/oracle/coolbak'

define script. = '/tmp/coolbak.sql'

spool &script

select 'ho cp ' || name || ' &dir' from v$controlfile

union all

select 'ho cp ' || name || ' &dir' from v$datafile

union all

select 'ho cp ' || member || ' &dir' from v$logfile

union all

select 'ho cp ' || name || ' &dir' from v$tempfile

/

create pfile = '&dir/initorcl.ora' from spfile;

ho cp /u01/app/oracle/10g/dbs/orapworcl &dir

spool off

shutdown immediate

start &script

ho rm &script

startup

SQL> start /tmp/tmpbak2.sql; --執行冷備指令碼，完畢後將自動完成冷備份並啟動例項

SQL> col file_name format a60

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf

SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf

SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf

USERS /u01/app/oracle/oradata/orcl/users01.dbf

EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf

TBS1 /u01/app/oracle/oradata/orcl/tbs1_1.dbf

TBS1 /u01/app/oracle/oradata/orcl/tbs1_2.dbf

SQL> ho rm /u01/app/oracle/oradata/orcl/sysaux01.dbf --刪除sysaux表空間的資料檔案

SQL> startup --啟動時收到了關於資料檔案sysaux01的錯誤提示

ORACLE instance started.

Total System Global Area 251658240 bytes

Fixed Size 1218796 bytes

Variable Size 79693588 bytes

Database Buffers 167772160 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

--檢視告警日誌資訊

SQL> ho tail -n 10 $ORACLE_BASE/admin/orcl/bdump/alert_orcl.log

Mon Aug 9 13:14:22 2010

ALTER DATABASE OPEN

Mon Aug 9 13:14:22 2010

Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_4056.trc:

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

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

--根據告警日誌資訊檢視跟蹤檔案orcl_dbw0_4056.trc

SQL> ho cat /u01/app/oracle/admin/orcl/bdump/orcl_dbw0_4056.trc | more

/u01/app/oracle/admin/orcl/bdump/orcl_dbw0_4056.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

ORACLE_HOME = /u01/app/oracle/10g

System name: Linux

Node name: robinson.com

Release: 2.6.18-164.el5xen

Version: #1 SMP Tue Aug 18 16:06:30 EDT 2009

Machine: i686

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 5

Unix process pid: 4056, image: oracle@robinson.com (DBW0)

*** SERVICE NAME:() 2010-08-09 13:14:22.046

*** SESSION ID:(167.1) 2010-08-09 13:14:22.046

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> ho ls /u01/app/oracle/oradata/orcl/sysaux01.dbf --sysaux01.dbf在系統中不存在，即丟失

ls: /u01/app/oracle/oradata/orcl/sysaux01.dbf: No such file or directory

SQL> ho ls -l /u01/app/oracle/coolbak/sysau*

-rw------- 1 oracle oinstall 304095232 Aug 9 13:05 /u01/app/oracle/coolbak/sysaux01.dbf

--從備份中還原sysaux表空間的資料檔案

SQL> ho cp /u01/app/oracle/coolbak/sysaux01.dbf /u01/app/oracle/oradata/orcl/

SQL> recover database; --進行介質恢復

Media recovery complete.

SQL> alter database open; --將資料庫切換到open狀態

SQL> select * from dual; --資料庫已正常使用

X

四、使用RMAN備份及還原sysaux表空間

--在會話session1中檢視歸檔資訊

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 2

Next log sequence to archive 4

Current log sequence 4 --當前log sequence 為4

--開啟另外一個會話session2並使用rman備份sysaux表空間

RMAN> backup tablespace sysaux;

Starting backup at 13-AUG-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=147 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: starting piece 1 at 13-AUG-10

channel ORA_DISK_1: finished piece 1 at 13-AUG-10

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset

/2010_08_13/o1_mf_nnndf_TAG20100813T102959_669ck93v_.bkp tag=TAG20100813T102959 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:11

Finished backup at 13-AUG-10

--在session1中刪除sysaux01.dbf

SQL> ho rm $ORACLE_BASE/oradata/orcl/sysaux01.dbf;

--對tb_test表插入一些記錄並提交

SQL> select * from tb_test;

no rows selected

SQL> insert into tb_test select * from all_objects;

49835 rows created.

SQL> commit;

Commit complete.

--關閉例項並重新啟動後出現錯誤提示

SQL> startup

ORACLE instance started.

Total System Global Area 469762048 bytes

Fixed Size 1220048 bytes

Variable Size 109052464 bytes

Database Buffers 356515840 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

--在session2中使用rman來進行恢復sysaux表空間，需要使用rman重新連線資料庫

RMAN> restore tablespace sysaux;

Starting restore at 13-AUG-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/

backupset/2010_08_13/o1_mf_nnndf_TAG20100813T102959_669ck93v_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2010_08_13/

o1_mf_nnndf_TAG20100813T102959_669ck93v_.bkp tag=TAG20100813T102959

channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 13-AUG-10

--在會話session1中將database open ,提示需要執行介質恢復

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 3 needs media recovery

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

--執行介質恢復並將資料庫open

SQL> recover datafile '/u01/app/oracle/oradata/orcl/sysaux01.dbf';

Media recovery complete.

SQL> alter database open;

Database altered.

--已提交的事務保持一致

SQL> select count(1) from tb_test;

COUNT(1)

----------

49835

五、熱備模式下還原sysaux表空間

1.未手動實現歸檔、且未發生日誌切換時的處理

--將sysaux表空間置於熱備模式

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

--執行DML操作，從tb_test刪除記錄，熱備模式不影響正常操作

SQL> delete from tb_test;

2 rows deleted.

SQL> commit;

Commit complete.

--對sysaux表空間進行熱備

SQL> ho cp $ORACLE_BASE/oradata/orcl/sysaux01.dbf /u01/app/oracle/hotbak

--熱備後再次執行DML操作，即查詢新的記錄到tb_test

SQL> insert into tb_test select * from dba_objects where rownum < 3;

2 rows created.

SQL> commit;

Commit complete.

--關閉sysaux表空間的備份模式

SQL> alter tablespace sysaux end backup;

Tablespace altered.

--再次執行DML插入兩條記錄到tb_test

SQL> insert into tb_test select * from dba_objects where rownum < 3;

2 rows created.

SQL> commit;

Commit complete.

--此時刪除sysaux01.dbf檔案

SQL> ho rm $ORACLE_BASE/oradata/orcl/sysaux01.dbf

--關閉並重新啟動例項

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 469762048 bytes

Fixed Size 1220048 bytes

Variable Size 117441072 bytes

Database Buffers 348127232 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/orcl/sysaux01.dbf'

--還原sysaux01.dbf

SQL> ho cp $ORACLE_BASE/hotbak/sysaux01.dbf $ORACLE_BASE/oradata/orcl/

--恢復sysaux01.dbf並將資料庫置於open狀態

SQL> recover datafile 3;

Media recovery complete.

SQL> alter database open;

Database altered.

--已提交的資料保持了一致性

SQL> select count(1) from tb_test;

COUNT(1)

----------

4

--SYSAUX表空間已為可用狀態

SQL> select file_name,tablespace_name,status from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUS

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

/u01/app/oracle/oradata/orcl/users01.dbf USERS AVAILABLE

/u01/app/oracle/oradata/orcl/sysaux01.dbf SYSAUX AVAILABLE

/u01/app/oracle/oradata/orcl/undotbs01.dbf UNDOTBS1 AVAILABLE

/u01/app/oracle/oradata/orcl/system01.dbf SYSTEM AVAILABLE

/u01/app/oracle/oradata/orcl/example01.dbf EXAMPLE AVAILABLE

2.手動實現日誌歸檔後的處理

--檢視是否處於歸檔模式及當前歸檔的詳細資訊

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4

Next log sequence to archive 6

Current log sequence 6

--以下處理步驟與前面類似，省略描述

SQL> alter tablespace sysaux begin backup;

Tablespace altered.

SQL> ho cp $ORACLE_BASE/oradata/orcl/sysaux01.dbf $ORACLE_BASE/hotbak;

SQL> alter tablespace sysaux end backup;

Tablespace altered.

SQL> insert into tb_test select * from dba_objects;

50318 rows created.

SQL> commit;

Commit complete.

SQL> ho rm $ORACLE_BASE/oradata/orcl/sysaux01.dbf;

SQL> delete from tb_test;

50322 rows deleted.

SQL> commit;

Commit complete.

--備份sysaux01.dbf以後再執行了一些DML操作後，對日誌進行歸檔

SQL> alter system archive log current;

System altered.

--關閉例項並重新啟動例項後未錯誤任何錯誤提示

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 469762048 bytes

Fixed Size 1220048 bytes

Variable Size 117441072 bytes

Database Buffers 348127232 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

--檢視告警日誌提示replication_dependency_tracking功能被關閉及XDB$SCHEMA不可訪問

SQL> ho tail -n 30 $ORACLE_BASE/admin/orcl/bdump/alert_orcl.log

Database Characterset is AL32UTF8

replication_dependency_tracking turned off (no async multimaster replication found)

Starting background process QMNC

Fri Aug 13 12:56:24 2010

ARC2: Archival started

ARC1: STARTING ARCH PROCESSES COMPLETE

ARC1: Becoming the heartbeat ARCH

XDB UNINITIALIZED: XDB$SCHEMA not accessible

QMNC started with pid=19, OS id=4308

Fri Aug 13 12:56:25 2010

db_recovery_file_dest_size of 2048 MB is 17.52% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files, and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

ORA-376 encountered when generating server alert SMG-3600

Fri Aug 13 12:56:26 2010

Completed: ALTER DATABASE OPEN

--dba_tablespaces檢視中依然顯示的是online

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

--v$datafile 檢視中顯示為recover狀態

SQL> select name,file#,status from v$datafile;

NAME FILE# STATUS

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

/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM

/u01/app/oracle/oradata/orcl/undotbs01.dbf 2 ONLINE

/u01/app/oracle/oradata/orcl/sysaux01.dbf 3 RECOVER

/u01/app/oracle/oradata/orcl/users01.dbf 4 ONLINE

/u01/app/oracle/oradata/orcl/example01.dbf 5 ONLINE

--還原sysaux01.dbf並將資料庫啟動到mount狀態

SQL> ho cp $ORACLE_BASE/hotbak/sysaux01.dbf $ORACLE_BASE/oradata/orcl/

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 469762048 bytes

Fixed Size 1220048 bytes

Variable Size 117441072 bytes

Database Buffers 348127232 bytes

Redo Buffers 2973696 bytes

Database mounted.

--還原sysaux表空間

SQL> recover tablespace sysaux;

Media recovery complete.

SQL> alter database open;

Database altered.

--sysaux01.dbf變為offline狀態

SQL> col name format a50

SQL> select name,file#,status from v$datafile;

NAME FILE# STATUS

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

/u01/app/oracle/oradata/orcl/system01.dbf 1 SYSTEM

/u01/app/oracle/oradata/orcl/undotbs01.dbf 2 ONLINE

/u01/app/oracle/oradata/orcl/sysaux01.dbf 3 OFFLINE

/u01/app/oracle/oradata/orcl/users01.dbf 4 ONLINE

/u01/app/oracle/oradata/orcl/example01.dbf 5 ONLINE

--將sysaux表空間聯機

SQL> alter tablespace sysaux online;

Tablespace altered.

六、總結

1.在系統啟動時出現的相關提示建議先檢視告警日誌及跟蹤日誌以便進一步確認問題所在。

2.對於SYSAUX表空間的丟失，先還原，再執行介質恢復，有可能需要將其聯機。前提是需要先備份。

3.在備份期間或SYSAUX表空間丟失以後，不影響事務處理，且能恢復已提交的事務，當且僅當歸檔日誌或聯機日誌存在時。

4.若SYSAUX表空間丟失後,表空間遷移,基於SCHEMA匯入匯出,OEM等功能不可使用,但不影響未涉及到SYSAUX表空間功能的正常使用。

5.若SYSAUX表空間丟失後,發生了日誌切換，或手動日誌歸檔，或系統自動歸檔，下次重新啟動資料庫將不會收到錯誤提示。

可以參見第五點、第2小點中的：手動實現日誌歸檔後的處理

在冷備模式下，當處於歸檔模式的情況下實現日誌切換，手動或自動歸檔也發生類似的情況。這個未給出演示。

6.對於上述小點中丟失SYSAUX可以檢視dba_data_files,dba_tablespaces,v$datafile中資料檔案的狀態資訊

其中dba_data_files,dba_tablespaces屬於資料字典，可能與實際情況有些偏差

v$datafile為實時的資料資訊，可以據此對資料庫實現相關操作

7.對於不可恢復的情況,可以將隱藏引數 _allow_resetlogs_corruption_ 置為true,並使用alter database open resetlogs開啟。

8.使用alter database open resetlogs開啟資料庫有應當關閉_allow_resetlogs_corruption_引數。

9.對於使用alter database open resetlogs開啟的資料庫應當立即進行全備資料庫。

10.如果在未備份的情況下丟失了SYSAUX表空間，則可以將其離線，然後將資料匯出，並匯入到新的資料庫。

SYSAUX表空間管理及恢復

相關文章