Database open error ora-01555:snapshot too old

Original Blog link address: Database open error ora-01555:snapshot too old

Today in Dongguan on the honeymoon, a student said he managed the test library problems, can not open, we first to see what the problem:

Recovery of Online Redo log:thread 1 Group 4 Seq 4 Reading mem 0 mem# 0:/onlinelog/shr/redo04.logcompleted Redo applica tion of 0.00MBCompleted crash recovery at Thread 1:logseq 4, block 3, SCN 7755957 0 data blocks read, 0 data blocks Writt En, 0 redo k-bytes readthread 1 advanced to log sequence 5 (thread open) thread 1 opened at log sequence 5 current log# 5 seq# 5 mem# 0:/onlinelog/shr/redo05.logsuccessful Open of Redo Thread 1MTTR advisory is disabled because FAST_START_MTTR_ TARGET is not setthu June 13:31:35 2014smon:enabling cache recoveryORA-01555 caused by SQL statement below (SQL Id:4kr WUZ0CTQXDT, SCN:0X0000.007658BA): Select CTime, Mtime, stime from obj$ where obj# =: 1Errors in file/oracle/diag/rdbms/sh R/shr/trace/shr_ora_5262.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 6 with name "_syssmu6_1263032392$" T Oo Smallerrors in File/oracLe/diag/rdbms/shr/shr/trace/shr_ora_5262.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 6 with Name "_syssmu6_1263032392$" too smallerror 704 happened during DB Open, shutting down Databaseuser (ospid:5262): terminating t He instance due to error 704Instance terminated by USER, PID = 5262ora-1092 signalled During:alter DATABASE OPEN...OPIODR Aborting process unknown ospid (5262) as a result of Ora-1092thu June, 13:31:37 2014ora-1092:opitsk aborting process

From the above error, the database open failed because of an ora-01555 error when Oracle executes recursive SQL during the bootstrap phase.
In this way, the bootstrap process cannot continue, which results in the database not being open. We can see the following SQL statements for the error:
Select CTime, Mtime, stime from obj$ where obj# =: 1

This is very familiar with the SQL that you will find by tracing the Oracle open process through 10046 trace.

In response to this error, perhaps some people think that the problem of rollback segment, in fact, it is not, in this case, push the SCN can be very smooth to the database open.

But here's the problem: the brother's database is Oracle 11.2.0.4, which does not support the traditional 10015 event approach.

Let's solve the problem by Oradebug:

sql> conn/as Sysdbaconnec Ted to an idle instance. Sql> Startup Mountoracle instance started. Total System Global area 4275781632 bytesfixed size 2260088 bytesvariable size 989856648 byte Sdatabase buffers 3271557120 Bytesredo buffers 12107776 bytesdatabase mounted. Sql>sql> oradebug Poke 0x06001ae70 4 0x859afaora-00074:no process has been specifiedsql> Oradebug SetmypidStatem ENT processed. sql> oradebug dumpvar SGA kcsgscn_kcslf kcsgscn_ [06001ae70, 06001aea0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001ab50 00000000sql> oradebug poke 0x06001ae70 4 0x859AFABEFORE: [06001 AE70, 06001ae74) = 00000000AFTER: [06001ae70, 06001ae74) = 00859afasql> ALTER DATABASE open; Database altered. Sql>

Here is a brief explanation, 4 is the length, 0x859afa is 16, I am on the basis of the original v$datafile_header.checkpoint_change#
Plus 1000000 gets the value.

We can see that the database has been successfully opened. Finally, I looked at alert log and found that there was a ora-00600 4194 error.

Thu June 14:48:43 2014Dumping diagnostic data in directory=[cdmp_20140619144843], requested by (Instance=1, osid=9140 ( Mmon)), summary=[incident=132122]. Exception [TYPE:SIGSEGV, Address not mapped to object] [addr:0x3d6c3836] [Pc:0x97f4df6, Kgegpa () +40] [flags:0x0, Count: 1]exception [TYPE:SIGSEGV, Address not mapped to object] [addr:0x3d6c3836] [pc:0x97f386a, Kgebse () +776] [flags:0x2, Coun T:2]thu June 14:48:46 2014Flush retried for XCB 0x159c668c8, PMD 0x15870d270errors in file/oracle/diag/rdbms/shr/shr/t RACE/SHR_PMON_9112.TRC (incident=132017): ora-00600:internal error code, arguments: [4194], [], [], [], [], [], [], [], [ ], [], [], []incident details in:/oracle/diag/rdbms/shr/shr/incident/incdir_132017/shr_pmon_9112_i132017.trcuse Adrci or support Workbench to the incident. See Note 411.1 at My Oracle support for error and packaging details. Thu June 14:48:47 2014Errors in File/oracle/diag/rdbms/shr/shr/trace/shr_ora_9268.trc (incident=132209): ORA-00600: InterNAL error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []ora-00001:unique constraint (SYSTEM. Unq_pairs) violatedincident details in:/oracle/diag/rdbms/shr/shr/incident/incdir_132209/shr_ora_9268_ I132209.trcuse Adrci or support Workbench to the incident. See Note 411.1 at My Oracle support for error and packaging details. Errors in file/oracle/diag/rdbms/shr/shr/trace/shr_pmon_9112.trc:ora-00600:internal error code, arguments: [4194], [] , [], [], [], [], [], [], [], [], [], []pmon (ospid:9112): Terminating the instance due to error 472System State dump req Uested by (instance=1, osid=9112 (Pmon)), summary=[abnormal instance termination]. System state dumped to trace file/oracle/diag/rdbms/shr/shr/trace/shr_diag_9122_20140619144848.trcdumping diagnostic Data in directory=[cdmp_20140619144848], requested by (Instance=1, osid=9112 (Pmon)), summary=[abnormal instance Termination]. Instance terminated by Pmon, PID = 9112

This is a very common mistake, the simplest way is to resolve it by rebuilding undo or processing a rollback segment, here we use the simplest way:

Modified undo_management=manual undo_tablespace= ' system ' sql> l 1* create pfile= '/tmp/1.ora ' from spfilesql> startup MoU NT pfile= '/tmp/1.ora '; ORACLE instance started. Total System Global area 4275781632 bytesfixed size 2260088 bytesvariable size 989856648 byte Sdatabase buffers 3271557120 Bytesredo buffers 12107776 bytesdatabase mounted. sql> ALTER DATABASE open; Database altered. Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------/oracle/oradata/shr/ system01.dbf/oracle/oradata/shr/sysaux01.dbf/oracle/oradata/shr/undotbs01.dbf/oracle/oradata/shr/users01.dbf/ oradata/shr/jy_shr01.dbf/oradata/shr/jy_shr02.dbf/oradata/shr/jy_shr03.dbf/oradata/shr/eas_d_stand01.dbf/ Oradata/shr/eas_d_stand02.dbf/oradata/shr/eas_d_stand03.dbf/backup/eas/eas_d_stand04.dbf NAME--------------------------------------------------------------------------------/backup/eas/eas_d_ Stand05.dbf/backUP/EAS/EAS_D_STAND06.DBF rows selected. Sql> show parameter undo NAME TYPE VALUE--------------------------------------- --------------------------------------undo_management String manualundo_retention Integer 900undo_tablespace string systemsql> create undo tablespace undotbs2 Dat Afile '/oracle/oradata/shr/undotbs2_01.dbf ' size 200m; Tablespace created. sql> shutdown Immediate

After you rebuild undo, stop the library and modify the undo parameter to open the database successfully, as follows:

Sql> create SPFile from pfile= '/tmp/1.ora '; File created. Sql> Startup Mountoracle instance started. Total System Global area 4275781632 bytesfixed size 2260088 bytesvariable size 989856648 bytesdatabase buffers< c3/>3271557120 Bytesredo buffers 12107776 bytesdatabase mounted. sql> ALTER DATABASE open; Database altered. Sql>

Alert log no longer throws any errors after processing is complete.