"Oracle" undo corruption, no backup unconventional recovery

Customer a test environment, the host abnormal power outage, after startup found that undo file corruption, unable to start, in the open stage error as follows:

Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/CDRDB/CDRDB/TRACE/CDRDB_ORA_4109.TRC:

Ora-01122:database File 3 failed verification check

Ora-01110:data file 3: '/U01/APP/ORACLE/ORADATA/CDRDB/UNDOTBS01.DBF '

Ora-01210:data file header is media corrupt

ORA-1122 signalled During:alter DATABASE OPEN ...

Because it was a test environment, there was no backup, but there was some data inside, so I tried using unconventional recovery methods.

Pre-cold backup of existing environment!!!!!!!!!!!!!!!!

To create a pfile file:

Create Pfile from SPFile;

Modify these two parameters in the Pfile

#*.undo_tablespace= ' UNDOTBS1 '

*.undo_management= MANUAL

Then start with this pfile:

[Email protected]>startup Force pfile= '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initcdrdb.ora ';

ORACLE instance started.

Total System Global area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 364906208 bytes

Database buffers 150994944 bytes

Redo buffers 5869568 bytes

Database mounted.

Ora-01122:database File 3 failed verification check

Ora-01110:data file 3: '/U01/APP/ORACLE/ORADATA/CDRDB/UNDOTBS01.DBF '

Ora-01210:data file header is media corrupt

Still error

Then try dropping this undo

[Email protected]>alter database datafile 3 offline drop;

Database altered.

Re-open the library after

[Email protected]>alter database open;

Database altered.

Create a new undo tablespace after UNDOTBS2

[email protected]>create undo tablespace undotbs2 DataFile '/u01/app/oracle/oradata/cdrdb/undotbs02_01.dbf ' size 100M;

Create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/cdrdb/undotbs02_01.dbf ' size 100M

*

ERROR at line 1:

Ora-00604:error occurred at recursive SQL level 1

Ora-01552:cannot Use System rollback segment for Non-system tablespace

' Data_ol '

Ora-06512:at Line 999

Ora-01552:cannot Use System rollback segment for Non-system tablespace

' Data_ol '

Generate an error as above

More confused why does creating undo affect the Data_ol tablespace, so do a 10046

[Email protected]>oradebug event 10046 Trace name context off

Statement processed.

[Email Protected]>oradebug tracefile_name

/u01/app/oracle/diag/rdbms/cdrdb/cdrdb/trace/cdrdb_ora_4279.trc

[Email protected] ~]$ TKPROF/U01/APP/ORACLE/DIAG/RDBMS/CDRDB/CDRDB/TRACE/CDRDB_ORA_4279.TRC

Output = 1.TRM

Looking at 1.TRM, we found the reason:

"OGG". Ddlreplication.dbqueried is NULL and then

SELECT Database_role,

Open_mode

Into Dbrole, Dbopenmode

From V$database;

"OGG". Ddlreplication.dbqueried: = TRUE;

END IF;

IF Not (

^@ (dbrole = ' PRIMARY ' OR dbrole = ' LOGICAL STANDBY ')

and Dbopenmode =

' READ WRITE '

)

Then

--Don't write any trace even though it

Should work as this is standby

"OGG"

. Ddlreplication.setctxinfo ( -1,-1,-1,-1,-1);

RETURN; --Don't use

Trigger if not read/write and Primary/logical_standby

END IF;

EXCEPTION

...... Slightly

This is because the library is configured with the Ogg DDL synchronization, which produces insert operations when DDL is generated, using the Data_ol table space.

Cause find ~ Run script close the library Ogg's DDL configuration

[Email protected]> @ddl_disable. sql

Trigger altered.

Then re-create the UNDOTBS2 table space

[email protected]>create undo tablespace undotbs2 DataFile '/u01/app/oracle/oradata/cdrdb/undotbs02_01.dbf ' size 1000M;

Tablespace created.

This is a smooth addition to success.

UNDOTBS2 Add, then close the database, modify the Pfile parameter

*.undo_tablespace= ' UNDOTBS2 '

*.undo_management=auto

Reboot the database with pfile boot

[Email protected]>startup pfile= '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initcdrdb.ora ';

ORACLE instance started.

Total System Global area 523108352 bytes

Fixed Size 1337632 bytes

Variable Size 364906208 bytes

Database buffers 150994944 bytes

Redo buffers 5869568 bytes

Database mounted.

Database opened.

The database starts successfully, but still has an error in many business table queries:

ERROR at line 1:

Ora-00376:file 3 cannot is read at this time

Ora-01110:data file 3: '/U01/APP/ORACLE/ORADATA/CDRDB/UNDOTBS01.DBF '

The original undotbs01.dbf is still required to roll back the uncommitted transaction when the database crashes

And then I tried to set event 10513 to mask the rollback of Smon.

[Email protected]>alter system set Events ' 10513 Trace name Context forever, Level 2 ';

System altered.

found that the previous error will be reported, it appears that you need to manually block the rollback section.

[Email protected]>select segment_name,status from Dba_rollback_segs;

Segment_name STATUS

------------------------------ ----------------

SYSTEM ONLINE

_syssmu10_4131489474$ NEEDS RECOVERY

_syssmu9_1735643689$ NEEDS RECOVERY

_syssmu8_3901294357$ NEEDS RECOVERY

_syssmu7_3517345427$ NEEDS RECOVERY

_syssmu6_2897970769$ NEEDS RECOVERY

_syssmu5_538557934$ NEEDS RECOVERY

_syssmu4_1003442803$ NEEDS RECOVERY

_syssmu3_1204390606$ NEEDS RECOVERY

_syssmu2_967517682$ NEEDS RECOVERY

_syssmu1_592353410$ NEEDS RECOVERY

Segment_name STATUS

------------------------------ ----------------

_syssmu30_244658789$ ONLINE

_syssmu29_1020880693$ ONLINE

_syssmu28_2912622077$ ONLINE

_syssmu27_747253598$ ONLINE

_syssmu26_560868814$ ONLINE

_syssmu25_1357066082$ ONLINE

_syssmu24_103440716$ ONLINE

_syssmu23_1006903361$ ONLINE

_syssmu22_2808190508$ ONLINE

_syssmu21_39626587$ ONLINE

Rows selected.

The needs recovery table is then added to the following two parameters to mask these rollback segments

_offline_rollback_segments/_corrupted_rollback_segments parameters

*._offline_rollback_segments= (_syssmu10_4131489474$,_syssmu9_1735643689$,_syssmu8_3901294357$,_syssmu7_ 3517345427$,_syssmu6_2897970769$,_syssmu5_538557934$,_syssmu4_1003442803$,_syssmu3_1204390606$,_syssmu2_ 967517682$,_syssmu1_592353410$)

*._corrupted_rollback_segments= (_syssmu10_4131489474$,_syssmu9_1735643689$,_syssmu8_3901294357$,_syssmu7_ 3517345427$,_syssmu6_2897970769$,_syssmu5_538557934$,_syssmu4_1003442803$,_syssmu3_1204390606$,_syssmu2_ 967517682$,_syssmu1_592353410$)

Use this pfile to start the database

Startup force pfile= '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initcdrdb.ora ';

After those test tables are queried, confirm can query, and then export to help them export data, all ok~

"Oracle" undo corruption, no backup unconventional recovery