July 21, 2012

From 11.2.0.2.5, oracle don't support ADJUST SCN any more

Today, after incomplete recovery and opened a DB, i decided to increasing SCN for the DB.
I set "_allow_error_simulation=true", and use:

alter session set events 'IMMEDIATE trace name adjust_scn level 4490';
ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 4490';
at open/mount state for serveral times, but the SCN didn't get changed!

The alert.log shew that oracle even didn't try to turn the SCN.
Very wiered.

Then i decide to try "_minimum_giga_scn" once, i added "_minimum_giga_scn=7000" into init.ora and then startup the DB:

SYS @ apci > startup
ORA-01078: failure in processing system parameters
LRM-00101: unknown parameter name '_minimum_giga_scn'

Kidding? I removed the parameter and again i can startup DB normal.
Let's query the hidden parameter:

SYS @ apci > select ksppinm from x$ksppi where ksppinm like '%giga%';
no rows selected

Let's check different version databases:
10.2.0.4, no problem as we expected:
SYS @ 10gDB> select ksppinm from x$ksppi where ksppinm like '%giga%';
KSPPINM
--------------------------------------------------------------------------------
_minimum_giga_scn

11.2.0.2.4, parameter still there:
SYS @ XXDB >select ksppinm from x$ksppi where ksppinm like '%giga%';
KSPPINM
--------------------------------------------------------------------------------
_minimum_giga_scn

11.2.0.2.5, from this version the parameter disapper:
SQL>  select ksppinm from x$ksppi where ksppinm like '%giga%';
no rows selected

11.2.0.2.6:
SYS @ DB1 > select ksppinm from x$ksppi where ksppinm like '%giga%';
no rows selected

11.2.0.3:
SYS @ DB2 > select ksppinm from x$ksppi where ksppinm like '%giga%';
no rows selected

We can see oracle remove the ADJUST SCN function since 11.2.0.2.5, so we can't use oracle's function to adjust SCN in future.
In there any alternative way?

Yes, we can do first increasing the checkpoint_change# value in datafile header block to a appopraite value, and then re-create controlfile.
So the new controlfile will read our new value as current SCN.

Below is the step:

SYS @ test8 > select current_scn from v$database;
CURRENT_SCN
-----------
    4324729

BBED> set offset 484
        OFFSET          484

BBED> dump
 File: /d735/data02/oracle/test8/data/system01.dbf (1)
 Block: 1                Offsets:  484 to  995           Dba:0x00400001
------------------------------------------------------------------------
 3ffe4100 00000000 59d60a2f 01000000 01000000 62010000 1000db8a 02000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> modify /x dddddddd
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
 File: /d735/data02/oracle/test8/data/system01.dbf (1)
 Block: 1                Offsets:  484 to  995           Dba:0x00400001
------------------------------------------------------------------------
 dddddddd 00000000 59d60a2f 01000000 01000000 62010000 1000db8a 02000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

BBED> sum apply;
Check value for File 1, Block 1:
current = 0x5036, required = 0x5036

SYS @ test8 > startup nomount;
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2230080 bytes
Variable Size             318769344 bytes
Database Buffers          322961408 bytes
Redo Buffers               86753280 bytes
SYS @ test8 > CREATE CONTROLFILE REUSE DATABASE "TEST8" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 200
  3      MAXLOGMEMBERS 4
 ...........
 ...........
 29    '/d735/data02/oracle/test8/data/user03_new.dbf'
 30  CHARACTER SET UTF8
 31  ;
Control file created.

SYS @ test8 > alter database open resetlogs;
Database altered.

SYS @ test8 > select current_scn from v$database;
CURRENT_SCN
-----------
 3722305178

Done.

1 Comments:

Anonymous said...

thanks for sharing.

Post a Comment