Oracle 8.0.6 - How to reset (zero) SCN ?

[Wojciech Kozikowski]

Hi,

I'm newbie in Oracle, so please forgive if my question is a stupid kind...

I have Oracle 8.0.6 and due to bug 1083739 (and high SCN value)
I'm not able to restore db with RMAN. One of the workarounds is
to reset (zero) SCN.

My question: Is it possible to zero SCN without export/import db ?
Has someone tried this and know how to do that ? Are there any
tools available ?

Could you help ?

TIA,
x54pds@yahoo.com

[Graham Cameron_1]

Your question is in no way stupid.

SCN (system change number) is fundamental to oracle - every transaction is given a unique id - and it is stored in many places, control files, datafiles, redo log entries etc.

I wouldn't consider doing anything to change or reset SCNs these without expert help from Oracle. Get it wrong and you say goodbye to your database.

Contact Oracle via your support contract, and if you don't have one, get one (although theit first piece of advice is going to be to upgrade to a current version!!)

Sorry I can't be more helpful, but even for a non-newbie, interfering with SCN is perilous.

[malay boy]

There are only one way to reset the SCN-incomplete recovery which result you need to do this command :

alter database resetlogs;

The above command will reset the SCN.

Normal circumstances no way you can reset the SCN.

regards
mB

[twang]

I remember there is a paper on bug#1083739,

see following:
Problem Description
-------------------

During an RMAN restore operation on databases with high SCN numbers, it is
possible to encounter error(s) in the alert log similar to:

RMAN> list copy of datafile 193;

RMAN-03022: compiling command: list
RMAN-06210: List of Datafile Copies
RMAN-06211: Key File S Completion time Ckp SCN Ckp time Name
RMAN-06212: ------- ---- - --------------- ---------- --------------- ------
RMAN-06213: 162500 193 A 2001-07-27-17.05.53 5670462510313 2001-07-15-00.16.09 /staging01/abmp_rec/pcsf0008_dat01.dbf

RMAN> allocate channel for delete type disk;

RMAN-03022: compiling command: change
RMAN-03026: error recovery releasing channel resources
RMAN-00569: ================error message stack follows================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: change
RMAN-06003: ORACLE error from target database: ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_BACKUP_RESTORE", line 1343
ORA-06512: at line 1
RMAN-06097: text of failing SQL statement: begin sys.dbms_backup_restore . deleteDataFileCopy
( recid => :recid , stamp => :stamp , fname => :fname , dfnumber => :dfnumber , resetlogs_change
=> :rstscn:rstscn_i , creation_change => :crescn , checkpoint_change => :ckpscn:ckpscn_i
, blksize => :blksize , no_delete => :no_delete ) ; end ;

This can be caused by BUG 1083739 HIGH SCN CAUSES CHANGE CONTROLFILECOPY .. DELETE TO FAIL.

Solution Description
--------------------

In some scenarios it may be possible to workaround this error by bypassing use
of the RMAN recovery catalog. This is a difficult operation and should
only be attempted with the assistance of Oracle Support. Oracle cannot
guarantee that this method will be successful.

First you will need to identify the db_key value for the database to be
restored. Use SQL*Plus to connect to your recovery catalog database and
run the following query to determine the db_key. If you have unique
database names, then it should be easy to pick out the db_key for
the database you need to recover.

SQL> SELECT * from rc_database where name= 'DB_NAME_HERE';


Now identify the backup pieces necessary for recovery using the following
SQL statement against the recovery catalog. You will need to supply the start
date for the backup to be restored from and the db_key.

(1) Connect to the recovery catalog first

SQL>connect rman/rman@rcat

(2) SQL> select a.file#,b.handle from rc_backup_datafile a, rc_backup_piece b
where to_char(b.start_time,'DD-MON-YY') = ?DESIRED_DATE' and a.file#=1
and a.db_key=YOUR_DB_KEY and a.bs_key = b.bs_key;

To recover the backup pieces, follow instructions in NOTE 60545.1 How to Extract
Controlfiles, Datafiles, and Archived Logs from SMR Backupsets

Explanation
------------

BUG 1083739 can be encountered when trying to recover databases with high
SCN numbers using RMAN. A possible workaround is to bypass the recovery catalog.

[Massimo Bianchi]

Hi, a little note to Malan Boy hint:

"
There are only one way to reset the SCN-incomplete recovery which result you need to do this command :

alter database resetlogs;

The above command will reset the SCN.

Normal circumstances no way you can reset the SCN.

regards
mB
"


The resetlog will only reset the archive log number and sequence, not the internal SCN.

I don'nt know if it is possible, it's better to ask directly to Oracle, as previously suggested.

HTH,
Massimo

[Yogeeraj_1]

hi,

quite difficult to help on that issue. I would be preferrable that you contact Oracle Support and create a TAR.

This is really critical problem if you are not able to do the restore.

Metalink engineers have helped me a lot on Backup and recovery (using RMAN) issues in the past...

I fear that they may request you to upgrade to 8.1.7.4.

Good luck!

regards
Yogeeraj