ORA-600 [2662] block SCN is ahead of current SCN processing method description

 

1. ORA-600 [2662] Description

For ORA-600 [2662] errors, the previous blog has instructions:

ORA-600 [2662] "Block SCN is ahead of current SCN" [ID 28929.1]

Http://blog.csdn.net/tianlesoftware/article/details/6106130

 

For a description of the parameters of the ORA-600, refer to my blog:

Description of parameters of ORA-600

Http://blog.csdn.net/tianlesoftware/article/details/6645809

 

Simply put, when the SCN in the block is greater than the current SCN, The ORA-600 [2662] error will be reported.

 

The cause of this problem is as follows:

(1) doing an open resetlogs with_allow_resetlogs_uption Enabled

(2) a hardware problem, like a faultycontroller, resulting in a failed write to the control file or the redo logs

(3) Restoring parts of the database frombackup and not doing the appropriate recovery

(4) restoring a control file and not doinga recover database using backup controlfile

(5) Having _ disable_logging set duringcrash recovery

(6) problems with the DLM in a parallelserver Environment

(7) A bug

 

 

Ii. Solution

(1) If the scns in the error are very close, attempting a startup several timeswill bump up the dscn every time we open the database even if open fails. thedatabase will open when dscn = SCN.

(2) youcan bump the SCN either on open or while the database is open using <event: adjust_scn> (see

Note: 30681.1 ).

 

Beaware that you shoshould rebuild the database if you use this option. once this has occurred you wowould normally want to rebuild thedatabase via exp/rebuild/IMP as there is no guarantee that some other blocksare not ahead of time.

 

Another rebuild description:

Forcing the database to startup using_allow_resetlogs_uption:

This parameter is uninitialized ented andunsupported. Specified shocould only be done as a lastresort. Usually when a database is opened with either the resetlogs or noresetlogsoption, the status and checkpoint structures in all file headers
Of alldata files are checked to make sure that they are consistent. Once this is checked, the redo logs are zeroedout in case of resetlogs.
When parameter is set, the file header checks arebypassed. This means that we do not make sure that the files are consistent andopen the database. It will potentially cause some lost data and lost dataintegrity.

Thedatabase shocould be rebuilt since data and the data dictionary cocould be composed uptin ways that are not immediately detectable. this cocould lead to future outagesor put the database in a state where it cannot be salvaged at all. there is noguarantee
That this will work.

When the _ allow_resetlogs_uption parameter is set, the file header is not checked, so that the consistency of files cannot be guaranteed, which may cause data loss. In this case, it is to rebuild the database. That is: Export dB, re-create instance, import data.

 

2.1 Method 1

The parameter format for the ORA-600 is as follows:

Arg [a] current SCN wrap

Arg [B] current SCN Base

Arg [c] dependent scnwrap

Arg [d] dependent SCN Base

Arg [e] Where present this is the DBA wherethe dependent SCN came from.

When the difference between the dependent SCN and the current SCN is small, you can shut down the database and startup the database to enlarge the current SCN. When the current SCN is greater than the dependent SCN, you can start the database normally.

 

2.2 Method 2: Use adjust_scn event

If the difference between dependent SCN and current SCN is large, it is unrealistic to increase the SCN by restarting the database multiple times. In this case, the adjust_scn event can be used to increase the current SCN. However, to use this method, you need to rebuild the database, that is, exp and imp.

 

For more information about how to use adjust_scn event, see my blog. This document is not found on Mos, So I resend it.

Event adjust_scn description [30681.1]

Http://blog.csdn.net/tianlesoftware/article/details/6645633

 

 

2.2.1 usage instructions when the database is not opened

Take a backup.

You can use event 10015 to trigger an adjust_scnon database open:

Startup Mount;

Alter session set events '10015 trace nameadjust_scn level 1 ';

(NB: You can only use immediate here onanopen database. if the database is only mounted use the 10015 trigger to adjustscn, otherwise you get ora 600 [2251], [65535], [4294967295])

Alter database open;

If you get an ora 600: 2256 shutdown, use ahigher level and reopen.

 

Do ** not * set this event in init. ora or the instance will crash as soon as SMON orpmon try to do any clean up. Always use it with the "alter session" command.

-- Do not set this parameter to the init. ora file. Otherwise, when the SMON or pmon process is cleanup, the DB will crash.

 

2.2.2 database open status usage

You can increase the SCN thus:

Alter session set events 'immediate tracename adjust_scn level 1 ';

Level:

Level1 is usually sufficient-it raises the SCN to 1 billion (1024x1024*1024), level 2 raises it to 2 billionetc...

-- Note the level1 value. 1 indicates that the SCN is increased by 0.1 billion, and 2 indicates 0.2 billion.

 

If you try to raise the SCN to a level lessthan or equal to its current setting you will get <oeri: 2256>-seebelow.

IE: The Event steps the SCN to knownlevels. You cannot use the same level twice.

 

Calculating a level from600 errors:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To get a level for adjust_scn:

A) Determine the target SCN:

Ora-600 [2662] See <oeri: 2662> use target> = blocksscn

Ora-600 [2256] See <oeri: 2256> use target> = currentscn

B) Multiply the target wrap number by 4. This will give you the level to use in the adjust_scn to get the correct wrapnumber.

C) Next, add the following value to the levelto get the desired base value as well:

Add to level base

~~~~~~~~~~~~ ~~~~~~~~~~~~

0 0

1 1073741824 (1024*1024*1024 ),

2 2147483648 (2*1024*1024*1024 ),

3 3221225472 (3*1024*1024*1024 ),

 

Note:

In Oracle 9i, you can directly use the alter session command to add SCN. However, in Oracle 10 Gb, you also need to modify the implicit parameter _ allow_error_simulation and set this parameter to true to truly improve SCN.

To view implicit parameters, you can use the following view:

Oracle all_parameters View

Http://blog.csdn.net/tianlesoftware/article/details/6641281

 

Iii. adjust_scn example

DB version information:

Sys @ dave2 (DB2)> select * from V $ versionwhere rownum = 1;

Banner

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

Oracle Database 10g enterprise editionrelease 10.2.0.1.0-Prod

 

3.1 dB open status

Sys @ dave2 (DB2)> select current_scn fromv $ database;

Current_scn

-----------

4304475

 

Sys @ dave2 (DB2)> select open_mode fromv $ database;

Open_mode

----------

Read Write

 

Sys @ dave2 (DB2)> alter session set events 'immediate trace name adjust_scn level 1 ';

 

Session altered.

 

Sys @ dave2 (DB2)> select current_scn fromv $ database;

 

Current_scn

-----------

4304523

 

Note that the SCN does not increase significantly here. Let's set _ allow_error_simulation.

 

Sys @ dave2 (DB2)> alter system set "_ allow_error_simulation" = true scope = spfile;

System altered.

Sys @ dave2 (DB2)> startup force

Oracle instance started.

Total system global area 239075328 bytes

Fixed size 1218724 bytes

Variable Size 79693660 bytes

Database buffers 155189248 bytes

Redo buffers 2973696 bytes

Database mounted.

Database opened.

Sys @ dave2 (DB2)>

 

Use adjust_scn event again:

Sys @ dave2 (DB2)> alter session set events 'immediate trace name adjust_scn level 1 ';

Session altered.

Sys @ dave2 (DB2)> select current_scn fromv $ database;

Current_scn

-----------

1073742111

-- This SCN is successfully added.

 

3.2 dB not open status

Here the _ allow_error_simulation parameter has been modified, so we will not modify it again. We will directly start the database to mount and increase the SCN value when using adjust_scn.

 

Sys @ dave2 (DB2)> shutdown immediate

Database closed.

Database dismounted.

Oracle instance shut down.

 

Sys @ dave2 (DB2)> startup Mount

Oracle instance started.

Total system global area 239075328 bytes

Fixed size 1218724 bytes

Variable Size 79693660 bytes

Database buffers 155189248 bytes

Redo buffers 2973696 bytes

Database mounted.

 

Sys @ dave2 (DB2)> select current_scnfrom v $ database;

Current_scn

-----------

0

-- If the database does not have open, this command cannot be used.

 

Sys @ dave2 (DB2)> altersession set events '10015 trace name adjust_scn Level 2 ';

Session altered.

 

Sys @ dave2 (DB2)> select current_scn fromv $ database;

Current_scn

-----------

0

 

Sys @ dave2 (DB2)> alterdatabase open;

Database altered.

 

Sys @ dave2 (DB2)> select current_scn from V $ database;

Current_scn

-----------

2147483746

-- SCN has been added

 

-- Finally, check the value of the _ allow_error_simulation parameter:

Sys @ dave2 (DB2)> select name, value fromall_parameters where name = '_ allow_error_simulation ';

NAME value

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

_ Allow_error_simulation true

 

 

Do not forget to rebuild the database.

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 chat group: 40132017 chat group 2: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.