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

1. The cause of the matter

Received a phone call, Sunday accidental power outage, database can not be up, reported REDO CRASH,ora-00333 error.

The related environment is as follows: RAC oracle_11.2.0.3, no backup, open archiving.

2 treatment

As soon as you arrive at the scene, since the other party does not have a backup, make a cold backup; oracle 11g, just copy the datafile,logfile,controlfile (if necessary spfile.ora) in the asm. The sql is spelled as follows:

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

Select'cp'| | member |'/ databak/logfile/' fromv$ logfile unionselect'cp'| | name |'/ databak/controlfile/' fromv$controlfile

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

2.1 modify implied parameters

Since you have cold backup, you can have parameters (if you don't have cold backup, change this parameter, then there will be no other expert to play with you)

SQL > create pfile='/databak/pfile.ora' from spfile

Modify pfile.ora by adding the following section

_ allow_resetlogs_corruption=TRUE

* .undo_management='MANUAL'

* .rollback_segments='SYSTEM'

Someone may ask, "if you resetlogs, it has nothing to do with undo, but usually there is something wrong with redo, which means that there is a transaction that has not been completed, and the undo must be inconsistent, so just change the undo."

2.2 opening the library

It's over. Restart.

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. There is no cancel; here. You will be glad to see that the bad redo has been cleared, you can cheer and get up, but the world is colorful, and it will break your happiness right away:

ORA-01555caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0db2.73a0c8cd): select ctime,mtime Stime from obj$ where 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 number 20 with name "_ SYSSMU20_3214617278 $" too small

At a glance here, it seems to have something to do with undo, but don't worry about it. Take a closer look.

Select ctime,mtime, stime from obj$ where obj# =: 1 this statement looks familiar, is not the earliest initialization of the statement, then I guess there is something wrong with SCN.

SQL > Selectcurrent_scn from vested database

For 0.HOHO.

Here are two ways to solve the problem:

1 set 10046trace

SQL > oradebug setmypidStatement processed.SQL > oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12Statement processed.SQL > oradebug TRACEFILE_NAME

2 oradebugpoke to promote scn

3 set the implied parameter _ minimum_giga_scn

I use the third one here:

Selectksppinm,ksppdesc from x$ksppi whereksppinm like'% giga%'KSPPINM KSPPDESC----_minimum_giga_scn Minimum SCN to start with in 2 ^ 30 unitsselectto _ char (checkpoint_change# '9999999999999999') from v$database TO_CHAR (CHECKPO- 15060095276784selectdistinct (to_char (checkpoint_change#,'99999999999999')) from vested datafile headers; (TO_CHAR (CHECKP- 15060095276784SQL > select15060095276784/1024/1024/1024 from dual; 15060095276784)

Modify pfile to modify:

_ minimum_giga_scn=14026

Now start the database, smooth open, but there are a bunch of JOB errors, estimated to be running JOB when the power is off. This explains why the power outage on Sunday will bring down the database redo.

SQL > alter system setjob_queue_processes=0

Export all do a logical export.

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

An error was also reported during the export, saying that the rollback segment was not available and forgot to change undo to real undo.

SQL > create undotablespace undotbs3 datafile'+ordata (datafile) 'size 8G

Change pfile to undotbs3; and restart again, OK.

2.3 MOS _ ALLOW_RESETLOGS_CORRUPTION description

DB_Parameter _ ALLOW_RESETLOGS_CORRUPTION

= =

This documentation has 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 an in-consistent Database with no

Other alternative but to rebuild thecomplete Database. This parameter could

Be used when we realize that there are nostardard options available and are

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

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

1. Customer does not have a good backup.

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

There is no possibility for reproduction of the lost data.

3. The customer has to be ready to exportthe full database and import it

Back after creating a new one.

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

Come up.

5. Oracle does not support the databaseafter using this parameter for

Recovery.

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

Message have been tried.

To put it simply, there is no 100% guarantee for the parameter _ ALLOW_RESETLOGS_CORRUPTION. If your redo is broken, you can use it to OPEN the library, and after using this parameter, you do not support rman, only export.

3 Summary:

There is nothing to say, there is an element of luck in it, if the datafile block has a ring block, it will be even more troublesome, if one piece is broken, it will not be over, do not play with the power, make the UPS battery time a little longer, add a power outage alarm, save a heart, and then have time to build a dataguard.

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.