Recovery of undo tablespace loss under RAC

Source: Internet
Author: User
Test environment: System: LINUX-64 Database: 10.2.0.1 2 node RAC: RACDB1, RACDB2 storage used ASM

Test environment: System: LINUX-64 Database: 10.2.0.1 2 node RAC: RACDB1, RACDB2 storage used ASM

Test environment:
System: LINUX-64
Database: 10.2.0.1
Two-node RAC: RACDB1, ASM used by RACDB2 Storage

(1) Insert data without submitting
RACDB1> insert into xuhm. test3 values (4, 'A ');

There is an active transaction.
RACDB1> select usn, xacts from v $ rollstat;

USN XACTS
--------------------
0 0
1 0
2 0
3 0
4 1
5 0
6 0
7 0
8 0
9 0
10 0

(2) shut down the database and delete the UNDO tablespace of racdb1.
RACDB1> shutdown abort;
RACDB2> shutdown abort;

ASMCMD> rm UNDOTBS1.260.794232647

(3) Enable the database
RACDB1> startup
Oracle instance started.

Total System Global Area 184549376 bytes
Fixed Size 2019448 bytes
Variable Size 121638792 bytes
Database Buffers 58720256 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2-see DBWR trace file
ORA-01110: data file 2: '+ RAC_DISK/racdb/datafile/undotbs1.260.794232647'

RACDB2> startup
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 2019448 bytes
Variable Size 155193224 bytes
Database Buffers 25165824 bytes
Redo Buffers 2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2-see DBWR trace file
ORA-01110: data file 2: '+ RAC_DISK/racdb/datafile/undotbs1.260.794232647'

RACDB2> shutdown immediate

(4) The file has to be processed offline because it is lost.
RACDB1> alter database datafile '+ RAC_DISK/racdb/datafile/undotbs1.260.794232647' offline drop;


(5) Open the database
RACDB1> alter database open;
The database cannot be opened. The following error is returned when you view the alert Log:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '+ RAC_DISK/racdb/datafile/undotbs1.260.794232647'
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604.
Fri Sep 28 20:32:29 2012
Errors in file/u01/app/oracle/admin/RACDB/bdump/racdb1_lms0_9732.trc:
ORA-00604: error occurred at recursive SQL level
Fri Sep 28 20:32:29 2012
Errors in file/u01/app/oracle/admin/RACDB/bdump/racdb1_lmon_9728.trc:

You need to modify the following parameters: note that _ uploupted_rollback_segments must be used here, And _ offline_rollback_segments cannot be used. Otherwise, you still cannot open the database.
In the pfile file.
RACDB1.undo _ management = 'manual'
RACDB1.undo _ tablespace = 'undo2'
RACDB1. _ javasupted_rollback_segments = ('_ SYSSMU1 $', '_ SYSSMU2 $', '_ SYSSMU3 $', '_ SYSSMU4 $', '_ SYSSMU5 $ ', '_ SYSSMU6 $', '_ SYSSMU7 $', '_ SYSSMU8 $', '_ SYSSMU9 $', '_ SYSSMU10 $ ')

RACDB1> startup pfile = '/u01/pfile ';
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 2019448 bytes
Variable Size 121638792 bytes
Database Buffers 58720256 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.

(6) Delete rollback segments
RACDB1> SELECT segment_name, status FROM DBA_ROLLBACK_SEGS where status 'offline ';

SEGMENT_NAME STATUS
----------------------------------------------
SYSTEM ONLINE
_ SYSSMU1 $ NEEDS RECOVERY
_ SYSSMU2 $ NEEDS RECOVERY
_ SYSSMU3 $ NEEDS RECOVERY
_ SYSSMU4 $ NEEDS RECOVERY
_ SYSSMU5 $ NEEDS RECOVERY
_ SYSSMU6 $ NEEDS RECOVERY
_ SYSSMU7 $ NEEDS RECOVERY
_ SYSSMU8 $ NEEDS RECOVERY
_ SYSSMU9 $ NEEDS RECOVERY
_ SYSSMU10 $ NEEDS RECOVERY

11 rows selected.

RACDB1> drop rollback segment "_ SYSSMU1 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU2 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU3 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU4 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU5 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU6 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU7 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU8 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU9 $ ";

Rollback segment dropped.

RACDB1> drop rollback segment "_ SYSSMU10 $ ";

Rollback segment dropped.

(7) Delete the old undo tablespace and create a new undo tablespace.
RACDB1> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

RACDB1> create undo tablespace undo2;

Tablespace created.

(8) modify the spfile Parameters
RACDB1> shutdown immediate
RACDB1> startup mount;
RACDB1> alter system set undo_management = auto scope = spfile sid = 'racdb1 ';
RACDB1> alter system set undo_tablespace = UNDO2 scope = spfile sid = 'racdb1 ';
RACDB1> shutdown immediate
RACDB1> startup
RACDB1> show parameter undo

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDO2


(9) view the final recovery result
RACDB1> select * from xuhm. test3;

ID NA
------------
4 aa
2 xu
3 li
-- 4. The unsubmitted library of aa is processed as a submission.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.