ORA -00604 andORA - 01555 while opening the database

[KVS Raju]

Hi,

Iam getting following errors when iam opening a database, OSS - HP10.20 Unix
Oracle : 7.3.2
Could you please help me on this.
==================================
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 4 with name "R03" too small
=========================================
SVRMGR> startup mount
ORACLE instance started.
Total System Global Area 57981232 bytes
Fixed Size 38904 bytes
Variable Size 42672440 bytes
Database Buffers 14745600 bytes
Redo Buffers 524288 bytes
Database mounted.
SVRMGR> alter database open;
alter database open
*
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 4 with name "R03" too small

[Graham Cameron_1]

Ora-1555 is a very unusual error to encounter at startup.
You usually see this when running long queries against a moving database.

Was the database shut down cleanly ?

I think you are coing to need help from Oracle, do you have a support contract ?
(Even if you do, you are running quite an old version of Oracle).

-- Graham

[twang]

The System Change Number (SCN) is not synchronized. A transaction has been trapped without the ability to rollback (rollback tablespace dropped), causing a commit to take place without finishing the transaction.
1. There should be an offline rollback segment in the system tablespace that is not the system rollback segment. To find the name and status of this rollback segment, use the following select statement (logged into the system as internal): SELECT SEGMENT_NAME, TABLESPACE_NAME, OWNER, STATUS FROM DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = 'SYSTEM'; SEGMENT_NAME TABLESPACE_NAME OWNER STATUS -------------------------------- --------------------------------- --------- -------------------- SYSTEM SYSTEM SYS ONLINE RB_TEMP SYSTEM SYS OFFLINE The rollback segment named SYSTEM should be online. The second rollback segment should be offline. Bring the second rollback segment online: ALTER ROLLBACK SEGMENT ONLINE; Statement Processed. 2. Now, try to log on as user SYSTEM. A new error should occur when logging on as SYSTEM: ORA-600 with arguments of [2662] [a] [b] [c] [d] [] [] This error tells you that the SCNs are no longer synchronized. This is a serious internal error.

[T G Manikandan]

I am not sure for what purpose this database is used.
Snapshot too old error occurs mainly due to small rollback segments.

It could also be that the rollback segment is corrupted.

Large transactions would really require a rollback segment of big size.

To resolve this open your initialization file
init.ora

check for the lines rollback segments.
Remove the rollback segment name R03.

Then startup your database.

After you open the database you can create a rollback segment of bigger size and make that rollback segment online and then replace R03 with that rollback segment in the init.ora file

Revert

[T G Manikandan]

Also revert what errors are posted in the alert.log file for the database.

Infact before you do anything it step would help analyzing the problem

[Brian Crabtree]

Try opening up your init.ora file, and commenting the "rollback_segments" line out. This should allow you to open up the database, then look and see what the rollback segment information is listed in the dba_rollback_segs view.

Thanks,

Brian

[KVS Raju]

Hi Friends.

Thanks for your information.
The problem has been solved in the following way

SVRMGR> startup mount
SVRMGR> alter session set events '10015 trace name adjust_scn level 1';
SVRMGR> alter database open resetlogs;


Best Regards
Kvs Raju

[twang]

Hi,
Just to remind you taking a cold backup immediately after "alter database open resetlogs;".
good luck
twang