undo 檔案損壞
新年假期結束，由於公司斷電，一臺測試機器的undo 損壞。
現將undo 損壞修復的過程重現
現將undo 損壞修復的過程重現
實驗1
SQL> startup;
ORACLE instance started.
SQL> startup;
ORACLE instance started.
Total System Global Area 296456192 bytes
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
Database opened.
SQL> select * from v$tablespace;
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
Database opened.
SQL> select * from v$tablespace;
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
1
SYSAUX
YES NO YES
SYSAUX
YES NO YES
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
2
UNDOTBS1
YES NO YES
UNDOTBS1
YES NO YES
4
USERS
USERS
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES NO YES
3
TEMP
NO NO YES
TEMP
NO NO YES
undo 檔案
SQL> select file#,name,bytes/1024/1024 as size_m from v$datafile where ts#=2;
SQL> select file#,name,bytes/1024/1024 as size_m from v$datafile where ts#=2;
FILE#
----------
NAME
--------------------------------------------------------------------------------
SIZE_M
----------
3
/opt/oracle/oradata/mos5200/undotbs01.dbf
30
----------
NAME
--------------------------------------------------------------------------------
SIZE_M
----------
3
/opt/oracle/oradata/mos5200/undotbs01.dbf
30
SQL> create table b as select * from dba_objects;
Table created.
SQL> delete b;
74510 rows deleted.
SQL> commit;
Commit complete.
模擬正常關閉的過程
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
刪除undo 檔案
.
SQL> ! rm /opt/oracle/oradata/mos5200/undotbs01.dbf
.
SQL> ! rm /opt/oracle/oradata/mos5200/undotbs01.dbf
SQL> startup;
ORACLE instance started.
ORACLE instance started.
Total System Global Area 296456192 bytes
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/mos5200/undotbs01.dbf'
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/mos5200/undotbs01.dbf'
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
------------------------------------
MOUNTED
SQL> alter database datafile 3 offline drop;
Database altered.
SQL> alter database open;
Database altered.
建立新的undo 檔案
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/opt/oracle/oradata/mos5200/undotbs02.dbf' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;
Tablespace created.
SQL> select * from v$tablespace;
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
1
SYSAUX
YES NO YES
SYSAUX
YES NO YES
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
2
UNDOTBS1
YES NO YES
UNDOTBS1
YES NO YES
4
USERS
USERS
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES NO YES
3
TEMP
NO NO YES
TEMP
NO NO YES
5
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
UNDOTBS2
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
UNDOTBS2
YES NO YES
6 rows selected.
修改 預設的undo 檔案
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' scope=both;
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' scope=both;
System altered.
刪除原來的undo 檔案
SQL> drop tablespace undotbs1 including contents and datafiles;
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> create table c as select * from dba_objects;
Table created.
SQL> delete c;
74511 rows deleted.
SQL> commit;
Commit complete.
資料庫正常開啟
資料庫crash的情況
SQL> select * from v$tablespace;
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
1
SYSAUX
YES NO YES
SYSAUX
YES NO YES
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
4
USERS
YES NO YES
USERS
YES NO YES
3
TEMP
TEMP
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
NO NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
NO NO YES
5
UNDOTBS2
YES NO YES
UNDOTBS2
YES NO YES
SQL> select file#,name,bytes/1024/1024 as size_m from v$datafile where ts#=5;
FILE#
----------
NAME
--------------------------------------------------------------------------------
SIZE_M
----------
5
/opt/oracle/oradata/mos5200/undotbs02.dbf
20
----------
NAME
--------------------------------------------------------------------------------
SIZE_M
----------
5
/opt/oracle/oradata/mos5200/undotbs02.dbf
20
SQL> ! mv /opt/oracle/oradata/mos5200/undotbs02.dbf /opt/oracle/oradata/mos5200/undotbs02.dbfbak
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 296456192 bytes
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/mos5200/undotbs02.dbf'
Fixed Size 2227784 bytes
Variable Size 109052344 bytes
Database Buffers 180355072 bytes
Redo Buffers 4820992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/mos5200/undotbs02.dbf'
SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
------------------------------------
MOUNTED
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile 5 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/opt/oracle/oradata/mos5200/undotbs1.dbf' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;
Tablespace created.
SQL> select * from v$tablespace;
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
0
SYSTEM
YES NO YES
1
SYSAUX
YES NO YES
SYSAUX
YES NO YES
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
2
UNDOTBS1
YES NO YES
UNDOTBS1
YES NO YES
4
USERS
USERS
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES NO YES
3
TEMP
NO NO YES
TEMP
NO NO YES
5
TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
UNDOTBS2
YES NO YES
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
UNDOTBS2
YES NO YES
6 rows selected.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS1' scope=both;
System altered.
SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_3413854694$' found, terminate
dropping tablespace
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_3413854694$' found, terminate
dropping tablespace
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM
ONLINE
SYSTEM
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM
ONLINE
SYSTEM
_SYSSMU10_1978125664$
ONLINE
UNDOTBS1
ONLINE
UNDOTBS1
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU9_2456786981$
ONLINE
UNDOTBS1
ONLINE
UNDOTBS1
_SYSSMU8_3126487489$
ONLINE
ONLINE
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
_SYSSMU7_400204927$
ONLINE
UNDOTBS1
ONLINE
UNDOTBS1
_SYSSMU6_2414423743$
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
ONLINE
UNDOTBS1
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
ONLINE
UNDOTBS1
_SYSSMU5_1452140786$
ONLINE
UNDOTBS1
ONLINE
UNDOTBS1
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU4_367532967$
ONLINE
UNDOTBS1
_SYSSMU3_3664551637$
ONLINE
UNDOTBS1
ONLINE
UNDOTBS1
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU2_2288434006$
ONLINE
UNDOTBS1
ONLINE
UNDOTBS1
_SYSSMU1_311825917$
ONLINE
ONLINE
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
_SYSSMU20_2437224713$
NEEDS RECOVERY
UNDOTBS2
NEEDS RECOVERY
UNDOTBS2
_SYSSMU19_2890471014$
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
NEEDS RECOVERY
UNDOTBS2
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
NEEDS RECOVERY
UNDOTBS2
_SYSSMU18_307737647$
NEEDS RECOVERY
UNDOTBS2
NEEDS RECOVERY
UNDOTBS2
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU17_1202841468$
NEEDS RECOVERY
UNDOTBS2
_SYSSMU16_1800710874$
NEEDS RECOVERY
UNDOTBS2
NEEDS RECOVERY
UNDOTBS2
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU15_3867174456$
NEEDS RECOVERY
UNDOTBS2
NEEDS RECOVERY
UNDOTBS2
_SYSSMU14_3835933986$
NEEDS RECOVERY
NEEDS RECOVERY
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS2
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS2
_SYSSMU13_3025482366$
NEEDS RECOVERY
UNDOTBS2
NEEDS RECOVERY
UNDOTBS2
_SYSSMU12_2662135872$
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
NEEDS RECOVERY
UNDOTBS2
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
NEEDS RECOVERY
UNDOTBS2
_SYSSMU11_3413854694$
NEEDS RECOVERY
UNDOTBS2
NEEDS RECOVERY
UNDOTBS2
21 rows selected.
SQL> set wrap off
SQL> set linesize 200
SQL> select segment_name,status from dba_rollback_segs;
SQL> set linesize 200
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------
SYSTEM ONLINE
_SYSSMU10_1978125664$ ONLINE
_SYSSMU9_2456786981$ ONLINE
_SYSSMU8_3126487489$ ONLINE
_SYSSMU7_400204927$ ONLINE
_SYSSMU6_2414423743$ ONLINE
_SYSSMU5_1452140786$ ONLINE
_SYSSMU4_367532967$ ONLINE
_SYSSMU3_3664551637$ ONLINE
_SYSSMU2_2288434006$ ONLINE
_SYSSMU1_311825917$ ONLINE
------------------------------------------------------------------------------------------ ------------------------------------------------
SYSTEM ONLINE
_SYSSMU10_1978125664$ ONLINE
_SYSSMU9_2456786981$ ONLINE
_SYSSMU8_3126487489$ ONLINE
_SYSSMU7_400204927$ ONLINE
_SYSSMU6_2414423743$ ONLINE
_SYSSMU5_1452140786$ ONLINE
_SYSSMU4_367532967$ ONLINE
_SYSSMU3_3664551637$ ONLINE
_SYSSMU2_2288434006$ ONLINE
_SYSSMU1_311825917$ ONLINE
SEGMENT_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------
_SYSSMU20_2437224713$ NEEDS RECOVERY
_SYSSMU19_2890471014$ NEEDS RECOVERY
_SYSSMU18_307737647$ NEEDS RECOVERY
_SYSSMU17_1202841468$ NEEDS RECOVERY
_SYSSMU16_1800710874$ NEEDS RECOVERY
_SYSSMU15_3867174456$ NEEDS RECOVERY
_SYSSMU14_3835933986$ NEEDS RECOVERY
_SYSSMU13_3025482366$ NEEDS RECOVERY
_SYSSMU12_2662135872$ NEEDS RECOVERY
_SYSSMU11_3413854694$ NEEDS RECOVERY
------------------------------------------------------------------------------------------ ------------------------------------------------
_SYSSMU20_2437224713$ NEEDS RECOVERY
_SYSSMU19_2890471014$ NEEDS RECOVERY
_SYSSMU18_307737647$ NEEDS RECOVERY
_SYSSMU17_1202841468$ NEEDS RECOVERY
_SYSSMU16_1800710874$ NEEDS RECOVERY
_SYSSMU15_3867174456$ NEEDS RECOVERY
_SYSSMU14_3835933986$ NEEDS RECOVERY
_SYSSMU13_3025482366$ NEEDS RECOVERY
_SYSSMU12_2662135872$ NEEDS RECOVERY
_SYSSMU11_3413854694$ NEEDS RECOVERY
21 rows selected.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU10_1978125664$ ONLINE UNDOTBS1
_SYSSMU9_2456786981$ ONLINE UNDOTBS1
_SYSSMU8_3126487489$ ONLINE UNDOTBS1
_SYSSMU7_400204927$ ONLINE UNDOTBS1
_SYSSMU6_2414423743$ ONLINE UNDOTBS1
_SYSSMU5_1452140786$ ONLINE UNDOTBS1
_SYSSMU4_367532967$ ONLINE UNDOTBS1
_SYSSMU3_3664551637$ ONLINE UNDOTBS1
_SYSSMU2_2288434006$ ONLINE UNDOTBS1
_SYSSMU1_311825917$ ONLINE UNDOTBS1
------------------------------------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
SYSTEM ONLINE SYSTEM
_SYSSMU10_1978125664$ ONLINE UNDOTBS1
_SYSSMU9_2456786981$ ONLINE UNDOTBS1
_SYSSMU8_3126487489$ ONLINE UNDOTBS1
_SYSSMU7_400204927$ ONLINE UNDOTBS1
_SYSSMU6_2414423743$ ONLINE UNDOTBS1
_SYSSMU5_1452140786$ ONLINE UNDOTBS1
_SYSSMU4_367532967$ ONLINE UNDOTBS1
_SYSSMU3_3664551637$ ONLINE UNDOTBS1
_SYSSMU2_2288434006$ ONLINE UNDOTBS1
_SYSSMU1_311825917$ ONLINE UNDOTBS1
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
_SYSSMU20_2437224713$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19_2890471014$ NEEDS RECOVERY UNDOTBS2
_SYSSMU18_307737647$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17_1202841468$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16_1800710874$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15_3867174456$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14_3835933986$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13_3025482366$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12_2662135872$ NEEDS RECOVERY UNDOTBS2
_SYSSMU11_3413854694$ NEEDS RECOVERY UNDOTBS2
------------------------------------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
_SYSSMU20_2437224713$ NEEDS RECOVERY UNDOTBS2
_SYSSMU19_2890471014$ NEEDS RECOVERY UNDOTBS2
_SYSSMU18_307737647$ NEEDS RECOVERY UNDOTBS2
_SYSSMU17_1202841468$ NEEDS RECOVERY UNDOTBS2
_SYSSMU16_1800710874$ NEEDS RECOVERY UNDOTBS2
_SYSSMU15_3867174456$ NEEDS RECOVERY UNDOTBS2
_SYSSMU14_3835933986$ NEEDS RECOVERY UNDOTBS2
_SYSSMU13_3025482366$ NEEDS RECOVERY UNDOTBS2
_SYSSMU12_2662135872$ NEEDS RECOVERY UNDOTBS2
_SYSSMU11_3413854694$ NEEDS RECOVERY UNDOTBS2
21 rows selected.
SQL> create pfile='/opt/oracle/pfile.ora' from spfile;
SQL> create pfile='/opt/oracle/pfile.ora' from spfile;
File created.
SQL> host
:~> cd /opt/oracle/
> ls
admin cfgtoollogs checkpoints diag oradata oraInventory pfile.ora product
> vi pfile.ora
mos5200.__db_cache_size=180355072
mos5200.__java_pool_size=4194304
mos5200.__large_pool_size=4194304
mos5200.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
mos5200.__pga_aggregate_target=113246208
mos5200.__sga_target=297795584
mos5200.__shared_io_pool_size=0
mos5200.__shared_pool_size=100663296
mos5200.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/mos5200/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/mos5200/control01.ctl','/opt/oracle/oradata/mos5200/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='mos5200'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mos5200XDB)'
*.open_cursors=300
*.pga_aggregate_target=113246208
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=294649856
*.undo_tablespace='UNDOTBS1'
~
:~> cd /opt/oracle/
> ls
admin cfgtoollogs checkpoints diag oradata oraInventory pfile.ora product
> vi pfile.ora
mos5200.__db_cache_size=180355072
mos5200.__java_pool_size=4194304
mos5200.__large_pool_size=4194304
mos5200.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
mos5200.__pga_aggregate_target=113246208
mos5200.__sga_target=297795584
mos5200.__shared_io_pool_size=0
mos5200.__shared_pool_size=100663296
mos5200.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/mos5200/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/mos5200/control01.ctl','/opt/oracle/oradata/mos5200/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='mos5200'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mos5200XDB)'
*.open_cursors=300
*.pga_aggregate_target=113246208
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=294649856
*.undo_tablespace='UNDOTBS1'
~
-------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30152793/viewspace-1718763/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 無備份情況下undo檔案損壞處理Oracle
- 控制檔案損壞處理
- Oracle 控制檔案損壞解決方案Oracle
- system資料檔案頭損壞修復
- linux檔案系統損壞如何修復Linux
- InterBase資料庫檔案損壞的修復方法資料庫
- u盤檔案損壞怎麼恢復資料 u盤恢復損壞資料的有效方法
- 修復損壞的gzip壓縮檔案之原理篇
- win10引導記錄損壞怎麼辦 win10引導二進位制檔案損壞Win10
- SQL Anywhere db檔案損壞修復 DB檔案修復 DB資料庫修復SQL資料庫
- u盤檔案損壞怎麼恢復資料 u盤損壞無法讀取怎麼恢復資料
- PostgreSQL DBA(30) - Backup&Recovery#3(資料檔案損壞恢復)SQL
- ibdata1檔案損壞時恢復InnoDB單表測試
- 什麼是硬碟邏輯損壞和檔案系統錯誤硬碟
- 用 Python 批量檢查 sqlite/db3 檔案是否損壞（qbit）PythonSQLite
- 故障分析 | MySQL 備份檔案靜默損壞一例分析MySql
- 檔案或目錄損壞且無法讀取怎麼辦?
- MongoDB 資料檔案損壞修復救命repair與致命危險MongoDBAI
- Win10系統損壞的cbs.log檔案如何修復Win10
- oracle控制檔案的損壞或完全丟失的恢復辦法Oracle
- freemarker生成複雜樣式圖片並無檔案損壞的excelExcel
- 圖解gzip壓縮檔案底層結構及檔案損壞的修復方法圖解
- win10不管解壓什麼都損壞怎麼辦 win10檔案損壞無法解壓解決方法Win10
- 【RMAN】如果控制檔案損壞那麼如何恢復？恢復控制檔案的方式有哪幾種？
- 雙擊時它說“檔案或目錄損壞且無法讀取"
- 修復損壞的gz或tar.gz壓縮檔案之方法篇
- 2.7.10 恢復丟失或損壞的伺服器引數檔案(SPFILE)伺服器
- 解決Reiserfs檔案系統損壞的問題我們是認真的
- 記記憶體條硬體損壞藍色畫面的 dump 檔案分析記憶體
- 一次意外斷電導致mysql檔案損壞，啟動異常MySql
- Vsan分散式檔案系統邏輯架構損壞恢復過程分散式架構
- maven編專案編譯後在target下的zip檔案損壞無法開啟Maven編譯
- win10系統下bootres.dll檔案損壞了怎麼解決Win10boot
- 記錄一次 HotPE 導致的檔案系統損壞及修復
- linux檔案系統損壞？你只需學會這個方法就能完美修復Linux
- 學習這篇Oracle資料庫檔案壞塊損壞的恢復方法,擴充你的知識面Oracle資料庫
- e460安裝win10系統提示檔案損壞怎麼修復Win10
- 令人頭大： Mac提示應用程式無法開啟，檔案損壞怎麼辦？Mac
- ipa包用企業籤做簽名後提示描述檔案損壞解決方法