Search This Blog

Total Pageviews

Wednesday 21 September 2011

Oracle Active undo segment from system datafile for recovery

Oracle Active undo segment
Oracle Active rollback segment

oracle@amd-0:/opt/app/oracle/oradata/orcl> strings system01.dbf | grep _SYSSMU|cut -d $ -f 1 |sort -u
and substr(drs.segment_name,1,7) != '_SYSSMU'
D' and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU10_3550978943
_SYSSMU10_3904554333
_SYSSMU11_286947212
_SYSSMU12_3068564564
_SYSSMU13_2761193625
_SYSSMU1_3780397527
_SYSSMU14_2421411996
_SYSSMU15_1683924174
_SYSSMU16_2313212396
_SYSSMU17_2041439332
_SYSSMU1_783380902
_SYSSMU18_2800789714
_SYSSMU19_53723967
_SYSSMU20_3850939844
_SYSSMU2_2232571081
_SYSSMU2_3138176977
_SYSSMU3_1645411166
_SYSSMU3_2097677531
_SYSSMU4_1152005954
_SYSSMU4_870421980
_SYSSMU5_1527469038
_SYSSMU5_2525172762
_SYSSMU6_2443381498
_SYSSMU6_3753507049
_SYSSMU7_1260614213
_SYSSMU7_3286610060
_SYSSMU8_2012382730
_SYSSMU8_2806087761
_SYSSMU9_1424341975
_SYSSMU9_973944058





Edit Init file

# parameter undo_management and undo_tablespace

add this parameter :


UNDO_MANAGEMENT=MANUAL
_ALLOW_RESETLOGS_CORRUPTION = TRUE
_ALLOW_ERROR_SIMULATION = TRUE
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1,_SYSSMU2,_SYSSMU3, ...)



===========





- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE




SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M;

* Now the database already startup with Manual undo management.

* Create new UNDO Tablespace

SQL> Create UNDO tablespace NEW_UNDOTS datafile '/u02/undo01.dbf' size 2048M;

* Take offline the OLD Undo Tablespace :


SQL> alter tablespace OLD_UNDOTS offline;

* Take online the NEW Undo Tablespace :

SQL> alter tablespace NEW_UNDOTS ;

* Shutdown the database :

SQL> shutdown immediate;

* Edit the initMYDB.ora :

+ Remark the parameter :

- UNDO_MANAGEMENT=MANUAL
- _ALLOW_RESETLOGS_CORRUPTION = TRUE
- _ALLOW_ERROR_SIMULATION = TRUE

+ Add and edit the parameter :

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=NEW_UNDOTS

* Startup the database :

SQL> startup

* The database will startup with the NEW Undo tablespace, change the default undo tablespace :

SQL> alter system set undo_tablespace=NEW_UNDOTS;

* Then we can drop the OLD Undo tablespace :

SQL> drop tablespace OLD_UNDOTS including contents and datafiles;

Oracle DBA

anuj blog Archive