Shulou(Shulou.com)06/01 Report--

Oracle Undo tablespace recovery

System environment:

Operating system: RedHat EL55

Oracle: Oracle 11gR2

After Oracle 9i, undo tablespace is used to manage undo data to realize the automatic management of undo. This case demonstrates how to restore undo table space after being destroyed; if there is a backup, it is very easy to restore through backup, but in the case of no backup, it needs to be restored by unconventional means, hehe.

1. Case application environment

Undo tablespace undo segments:

14:34:44 SYS@ prod > select segment_name,segment_type,tablespace_name from dba_segments14:35:03 2 where tablespace_name='UNDOTBS1' SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME--_ SYSSMU10_3550978943 $TYPE2 UNDO UNDOTBS1_SYSSMU9_1424341975 $TYPE2 UNDO UNDOTBS1_SYSSMU8_2012382730 $TYPE2 UNDO UNDOTBS1_SYSSMU7 _ 3286610060 $TYPE2 UNDO UNDOTBS1_SYSSMU6_2443381498 $TYPE2 UNDO UNDOTBS1_SYSSMU5_1527469038 $TYPE2 UNDO UNDOTBS1_SYSSMU4_1152005954 $TYPE2 UNDO UNDOTBS1_SYSSMU3_2097677531 $TYPE2 UNDO UNDOTBS1_SYSSMU2_2232571081 $TYPE2 UNDO UNDOTBS1_SYSSMU1_3780397527 $TYPE2 UNDO UNDOTBS110 rows selected.Elapsed: 000.19

Simulate the application environment:

14:43:16 SYS@ prod > select count (*) from scott.emp1; COUNT (*)-1Elapsed: 00from scott.emp1; COUNT 00.0614 from scott.emp1; COUNT 4354 SYS@ prod > insert into scott.emp1 select * from scott.emp;14 rows created.Elapsed: 0000Groupe 00.0814PUBG 4404 SYS@ prod > commit;Commit complete.Elapsed: 00VOOOOO314FOR 4406 SYS@ prod > select count (*) from scott.emp1 COUNT (*)-15Elapsed: 00 SYS@ prod 00.0114 insert into scott.emp1 select 44 SYS@ prod > insert into scott.emp1 select * from scott.emp where rownum select count (*) from scott.emp1; COUNT (*)-18Elapsed: before closing the library, the transaction is not committed!

Open a new session, simulate a power outage, and shut down the database abnormally:

[oracle@rh7 dbs] $sqlplus'/ as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 14:46:11 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:46:12 SYS@ prod > shutdown abort

ORACLE instance shut down.

Undo tablesapce datafile is corrupted:

[oracle@rh7 dbs] $rm / u01/app/oracle/oradata/prod/undotbs01.dbf

2. The database reported an error during Open database:

[oracle@rh7 prod] $sqlplus'/ as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 26 14:47:26 2014Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to an idle instance.14:47:26 SYS@ prod > startupORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 775948320 bytesDatabase Buffers 54525952 bytesRedo Buffers 2412544 bytesDatabase mounted.ORA-01157: cannot identify/lock data file 3-see DBWR trace fileORA-01110: data file 3:'/ u01 App/oracle/diag/rdbms/prod/prod/trace/prod_dbw0_7481.trc:ORA-01157: cannot identify/lock data file 3-see DBWR trace fileORA-01110: data file 3:'/ u01/app/oracle/oradata/prod/undotbs01.dbf'ORA-27037: unable to obtain file statusLinux-x86_64 Error: 2: No such file or directoryAdditional information: 3Errors in file / u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_7503.trc:ORA-01157: cannot identify / lock data file 3-see DBWR trace fileORA-01110: data file 3:'/ u01/app/oracle/oradata/prod/undotbs01.dbf'ORA-1157 signalled during: ALTER DATABASE OPEN...14:47:37 SYS@ prod > show parameter undoNAME TYPE VALUE -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS114:48:18 SYS@ prod >

3. Repair without backup:

Create a pfile:

14:48:18 SYS@ prod > create pfile from spfile

File created.

Elapsed: 00:00:00.09

View the undo segments used by undo tablespace:

[oracle@rh7 dbs] $cd / u01/app/oracle/oradata/prod/

[oracle@rh7 prod] $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

Note: the undo segment in use is found by system01.dbf, and the above is sorted by usage time. Note that only those that are at the top of the list (the same rollback segment) are selected; by default, 10 undo segments will be used for each undo tablespace.

Edit pfile, add Oracle implicit parameters, and skip undo segments detection:

[oracle@rh7 dbs] $cat initprod.ora

* .undo_tablespace='SYSTEM'

* .undo_management='MANUAL'

*. _ corrupted_rollback_segments= (_ SYSSMU10_3550978943 $, _ SYSSMU9_1424341975 $, _ SYSSMU8_2012382730 $, _ SYSSMU7_3286610060 $, _ SYSSMU6_2443381498 $, _ SYSSMU5_1527469038 $, _ SYSSMU4_1152005954 $, _ SYSSMU3_2097677531 $, _ SYSSMU2_2232571081 $, _ SYSSMU1_3780397527 $)

*. _ allow_resetlogs_corruption=true

Start instance through pfile:

14:56:33 SYS@ prod > startup force pfile=$ORACLE_HOME/dbs/initprod.ora

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2217952 bytes

Variable Size 775948320 bytes

Database Buffers 54525952 bytes

Redo Buffers 2412544 bytes

Database mounted.

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

ORA-01110: data file 3:'/ u01 * app *

14:56:52 SYS@ prod > alter database datafile 3 offline drop

Database altered.

14:57:11 SYS@ prod > alter database open

Database altered.

Elapsed: 00:00:05.25

14:57:23 SYS@ prod >

Alarm log:

Alter database openBeginning crash recovery of 1 threadsStarted redo scanCompleted redo scan read 157 KB redo, 43 data blocks need recoveryStarted redo application at Thread 1: logseq 3, block 451Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0 Mem# 0: / dsk1/oradata/prod/redo03a.log Mem# 1: / dsk2/oradata/prod/redo03b.logCompleted redo application of 0.04MBCompleted crash recovery at Thread 1: logseq 3, block 766, scn 1878512 43 data blocks read, 43 data blocks written Redo k-bytes readThu Jun 26 14:57:19 2014LGWR: STARTING ARCH PROCESSESThu Jun 26 14:57:19 2014ARC0 started with pid=20, OS id=7638 ARC0: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETEARC0: STARTING ARCH PROCESSESThread 1 advanced to log sequence 4 (thread open) Thu Jun 26 14:57:20 2014ARC1 started with pid=21 OS id=7641 Thread 1 opened at log sequence 4 Current log# 1 seq# 4 mem# 0: / dsk1/oradata/prod/redo01a.log Current log# 1 seq# 4 mem# 1: / dsk2/oradata/prod/redo01b.logSuccessful open of redo thread 1MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setThu Jun 26 14:57:20 2014SMON: enabling cache recoveryThu Jun 26 14:57:21 2014ARC2 started with pid=22 OS id=7643 ARC1: Archival startedARC2: Archival startedThu Jun 26 14:57:21 2014ARC2: Becoming the'no FAL' ARCHARC2: Becoming the'no SRL' ARCHARC1: Becoming the heartbeat ARCHARC3 started with pid=23 OS id=7645 Archived Log entry 31 added for thread 1 sequence 3 ID 0xfaf1e60 dest 2:Thu Jun 26 14:57:21 2014Verifying file header compatibility for 11g tablespace encryption..Verifying 11g file header compatibility for tablespace encryption completedSMON: enabling tx recoveryDatabase Characterset is ZHS16GBKNo Resource Manager plan activereplication_dependency_tracking turned off (no async multimaster replication found) ARC3: Archival startedARC0: STARTING ARCH PROCESSES COMPLETEStarting background process QMNCThu Jun 26 14:57:23 2014QMNC started with pid=24, OS id=7647 Completed: alter database openThu Jun 26 14:57:26 2014Starting background process CJQ0Thu Jun 26 14:57:26 2014CJQ0 started with pid=27, OS id=7661

4. Create a new undo tablespace

14:59:27 SYS@ prod > show parameter undoNAME TYPE VALUE---undo_management String MANUALundo_retention integer 900undo_tablespace string SYSTEM14:59:34 SYS@ prod > create undo tablespace undotbs214:59:53 2 datafile'/ u01 size 100m15:00:09 autoextend on 15:01:28 SYS@ prod > create spfile from pfile;File created.Elapsed: 15:01:28 SYS@ prod > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.15:02:39 SYS@ prod > startupORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 775948320 bytesDatabase Buffers 54525952 bytesRedo Buffers 2412544 bytesDatabase mounted.Database opened.15:03:36 SYS@ prod > alter system set undo_management=auto scope=spfile System altered.Elapsed: 00 alter system set undo_tablespace=undotbs2 scope=spfile;System altered.Elapsed 0015 00.0915 SYS@ prod 09 SYS@ prod > startup force ORACLE instance started.Total System Global Area 835104768 bytesFixed Size 2217952 bytesVariable Size 775948320 bytesDatabase Buffers 54525952 bytesRedo Buffers 2412544 bytesDatabase mounted.Database opened.15:04:28 SYS@ prod > show parameter undoNAME TYPE VALUE-- -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS215:04:36 SYS@ prod > select usn Name from v$rollname USN NAME- 0 SYSTEM 21 _ SYSSMU21_2312338076 $22 _ SYSSMU22_3375463809 $23 _ SYSSMU23_4084707454 $24 _ SYSSMU24_386518199 $25 _ SYSSMU25_2810228709 $26 _ SYSSMU26_2968904537 $27 _ SYSSMU27_3269963619 $28 _ SYSSMU28_707429450 $29 _ SYSSMU29_2754652023 $30 _ SYSSMU30_1737877121 $11 rows selected.Elapsed: 00 SYS@ prod 00.0515 rows selected.Elapsed 0444 SYS@ prod > create pfile from spfile File created. Remove the implied parameters from pfile: [oracle@rh7 dbs] $cat initprod.ora prod.__db_cache_size=16777216prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment#*._allow_resetlogs_corruption=true#*._corrupted_rollback_segments='_SYSSMU10_3550978943 $','_ SYSSMU9_1424341975 $','_ SYSSMU8_2012382730 $','_ SYSSMU7_3286610060 $','_ SYSSMU6_2443381498 $','_ SYSSMU5_1527469038 $','_ SYSSMU4_1152005954 $','_ SYSSMU3_2097677531 $','_ SYSSMU2_2232571081 $' '_ SYSSMU1_3780397527 $' * .audit _ file_dest='/u01/app/oracle/admin/prod/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/u01/app/oracle/oradata/prod/control01.ctl' '/ dsk1/oradata/prod/control02.ctl'#Restore Controlfile*.db_16k_cache_size=25165824*.db_block_size=8192*.db_cache_size=16777216*.db_domain=''*.db_keep_cache_size=0*.db_name='prod'*.db_recycle_cache_size=12582912*.diagnostic_dest='/u01/app/oracle'*.dispatchers=' (PROTOCOL=TCP) (SERVICE=prodXDB)' * .log _ archive_dest_1='location=/dsk4/arch_prod'*.log_archive_ Dest_2='location=/dsk4/arch2'*.log_archive_dest_state_1='DEFER'*.log_archive_format='arch_%t_%s_%r.log'*.memory_target=0*.O7_DICTIONARY_ACCESSIBILITY=TRUE*.open_cursors=300*.processes=150*.remote_login_passwordfile='EXCLUSIVE'*.sga_max_size=838860800*.shared_pool_reserved_size=12582912*.shared_pool_size=200886080*.undo_management='AUTO'*.undo_tablespace='UNDOTBS2' recreates spfile:15:04:44 SYS@ prod > create spfile from pfile File created.

@ at this point, undo tablespace recovery is complete!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.