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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- undo檔案丟失或損壞
- UNDO 表空間檔案損壞的恢復
- Oracle 無備份情況下undo檔案損壞處理Oracle
- 陣列櫃故障造成控制檔案損壞，資料檔案損壞陣列
- 一個控制檔案損壞
- UNDO表空間損壞的處理
- 資料檔案損壞、丟失
- Oracle 控制檔案損壞解決方案Oracle
- 重做日誌檔案損壞測試
- RMAN學習-資料檔案損壞
- 損壞控制檔案的恢復方法
- SQL Server ldf 檔案損壞恢復SQLServer
- 控制檔案損壞重建實驗（上）
- 控制檔案損壞重建實驗（下）
- [20150619]undo檔案損壞或者丟失的恢復1
- [20150619]undo檔案損壞或者丟失的恢復2
- [20150619]undo檔案損壞或者丟失的恢復3
- undo表空間損壞的處理過程
- Oracle資料庫UNDO損壞後的恢復Oracle資料庫
- system資料檔案頭損壞修復
- 聯機日誌檔案損壞問題
- 恢復之單個控制檔案損壞
- 單個控制檔案損壞的恢復
- pg 檔案塊損壞的修復措施。
- 當前聯機日誌檔案損壞
- 線上日誌檔案損壞恢復方法
- rman 恢復---歸檔丟失and資料檔案損壞
- 【BBED】 SYSTEM檔案頭損壞的恢復(4)
- 控制檔案損壞，丟失其中一個
- 資料檔案丟失損壞的恢復--
- 某個控制檔案損壞的恢復案例
- system表空間檔案損壞----完全恢復
- REDO檔案丟失或者損壞的恢復
- 單個控制檔案損壞的解決方法
- 一次控制檔案損壞的恢復
- Undo和Current Online Redo損壞的處理方法
- Current online Redo 和 Undo 損壞的處理方法
- 沒有備份的情況下處理undo損壞