Oracle database Redo corrupt ora-00333 repair Codex

1.   cause of things

received the phone, Sunday accidental power outage, the database can not, reported REDO crash,ora-00333 error.

The relevant environment is as follows: RAC oracle_11.2.0.3, No backup , open archive.

2processing

one to the scene, since the other side did not backup, then do a cold backup;Oracle 11g , datafile,logfile,controlfile asm ( if necessary Spfile.ora) Copy it out on the go. The following SQL is spelled :

Select ' CP ' | | name | | '/databak/datafile/' fromv$datafile union

Select ' CP ' | | member| | '/databak/logfile/' from V$logfile unionselect ' cp ' | | name | | '/databak/controlfile/' fromv$controlfile;

go to the grid user , Asmcmd, execute it. Time, that depends on the size of your data file and the IO speed of your storage .

2.1Modifying implicit parameters

Because of the cold, can be parameters (if not cold, change this parameter, then there is no brick home to accompany you to play)

Sql>create pfile= '/databak/pfile.ora ' from SPFile;

Modify the Pfile.ora to add the following sections

_allow_resetlogs_corruption=true

*.undo_management= ' MANUAL '

*.rollback_segments= ' SYSTEM '

Some people may ask, you resetlogs, close undo what matter, but usually the thing is so,redo problem, that means that there is a matter not submitted, that undo must be inconsistent, So just change the undo too.

2.2Qicu

It's over, it's restarted.

Sql>shutdown immediate;

Sql>startup Mount Pfile= '/databak/pfile.ora '

Sql>recoverdatabase until cancel;

At this point the database will prompt you to enter the next logseq, thereis no, direct output cancel; Here you will be very happy to see that the bad redo was cleared, can cheer, up, but the world is rich and colorful, and immediately put your happy break:

ora-01555caused by SQL statement below (SQL Id:4krwuz0ctqxdt, SCN:0X0DB2.73A0C8CD): Select Ctime,mtime, stime from obj$ W Here obj# =: 1Errors in file/oracle/app/oracle/diag/rdbms/oradb/oradb1/trace/jmrk1_ora_4608.trc:errors in File/oracle /app/oracle/diag/rdbms/oradb/oradb1/trace/jmrk1_ora_4364.trc:ora-00704:bootstrap Process failureORA-00704: Bootstrap process failureora-00604:error occurred at recursive SQL level 1ora-01555:snapshot too old:rollback segment num ber with Name "_syssmu20_3214617278$" too small

here a look, seems to have a relationship with undo, but crossing, take a closer look

Select Ctime,mtime, stime from obj$ where obj# =: 1 The statement looks familiar, not the oldest statement, and the SCN is estimated to be problematic.

Sql>selectcurrent_scn from V$database;-------------------------------------------------------------------0

to be 0.HOHO.

Here are two ways to solve this:

1 Setting 10046trace

Sql> Oradebug Setmypid

Statement processed.

sql> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, Level 12

Statement processed.

Sql> Oradebug Tracefile_name

2 Oradebugpoke Propulsion SCN

3 Setting the implied parameter _MINIMUM_GIGA_SCN

I use the third type here:

selectksppinm,ksppdesc from x$ksppi   whereksppinm like  '%giga% ' KSPPINM                           Ksppdesc---------------------------------------------------------------------------_minimum_giga_scn               Minimum SCN to  Start with in 2^30unitsselectto_char (checkpoint_change#, ' 99999999999999 ')  from v$ Database To_char (Checkpo--------------- 15060095276784selectdistinct (To_char (checkpoint_change#, ' 99999999999999 '))   from v$datafile_header; (To_char (Checkp--------------- 15060095276784SQL>  select15060095276784/1024/1024/1024 from dual; 15060095276784/1024/1024/1024------------------- ----------                   14025.8067 

Modify pfile modification :

_minimum_giga_scn=14026

now start the database, smooth open, but there is a pile of job error, estimated power-down is running job. It also explains why the Sunday power-down will redo the database .

Sql> alter system setjob_queue_processes=0;

Export all to do a logical export.

EXPDP system/systemdirectory=full dumpfile=dump_%u.dmp logfile=export.log full=y parallel=4

The export also reported a mistake, said the rollback section is not available, forgot to change the undo to Real Undo ,

sql> Create Undotablespace undotbs3 datafile ' +ordata (datafile) ' Size 8G;

Modify pfile, change into UNDOTBS3; Restart again, OK.

2.3 MOS _allow_resetlogs_corruptionDescription

Db_parameter _allow_resetlogs_corruption

========================================

This documentation have been preparedavoiding the mention of the complex

Structures from the code and to simply Givean insight to the ' damage it could

Cause '. The usage of this parameter leads to a in-consistent Database with no

Other alternative rebuild Thecomplete Database. This parameter could

be used if we realize that there is nostardard options available and is

Convinced that the customer understands theimplications of using the Oracle ' s

Secret parameter. The factors to is considered are;--

1. Customer does not has a good backup.

2. A lot of time and money have beeninvested after the last good backup and

There is no possibility for reproduction of the lost data.

3. The customer have to is ready-to-exportthe full database and import it

Back after creating a new one.

4. There is no 100% guarantee this by usingthis parameter the database would

Come up.

5. Oracle does the databaseafter using this parameter for

Recovery.

6. All OPTIONS including the ones mentionedin the action part of the error

The message has been tried.

simply put, it is _allow_resetlogs_corruption This parameter does not have 100% guarantee, you redo bad can use him to OPEN the library, And after using this does not support recovery (Rman), only support export.

3Summary:

There is nothing to say, there is luck ingredient in the inside, if datafile block has ring blocks that would be more trouble, if bad a piece also will not have finished, nothing or do not play off the Power Bar, the UPS battery time to get a little longer, Add a power outage alarm, save a heart, and then there is empty to build a dataguard bar.

This article is from the "Snowhill" blog, make sure to keep this source http://snowhill.blog.51cto.com/339421/1836704

Oracle database Redo corrupt ora-00333 repair Codex