Database open error ORA-01555: snapshot too old

Original blog link address: Database open error ORA-01555: snapshot too old

During his honeymoon in Dongguan today, a student said that the test library he managed had a problem and could not be opened. Let's first look at the problem:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 4 Reading mem 0 Mem# 0: /onlinelog/shr/redo04.logCompleted redo application of 0.00MBCompleted crash recovery at Thread 1: logseq 4, block 3, scn 7755957 0 data blocks read, 0 data blocks written, 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 Jun 19 13:31:35 2014SMON: enabling cache recoveryORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.007658ba):select ctime, mtime, stime from obj$ where obj# = :1Errors 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 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 the 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 Jun 19 13:31:37 2014ORA-1092 : opitsk aborting process

From the above error, the reason why the database fails to open is that Oracle encountered a ora-01555 error when executing recursive SQL In the bootstrap stage,
In this way, the bootstrap process cannot continue, and the database cannot be opened. We can see that the error SQL statement is as follows:
Select ctime, mtime, stime from obj $ where obj # =: 1

This is a very familiar SQL statement. You will find this SQL statement when tracking Oracle open through 10046 trace.

For this error, some people may think it is a rollback segment problem. In fact, it is not. In this case, the SCN can smoothly open the database.

But there is a problem here: the brother's database is Oracle 11.2.0.4 and does not support the traditional 10015 event method.

Here we solve this problem through oradebug:

SQL> conn /as sysdbaConnected to an idle instance.SQL> startup mountORACLE instance started. Total System Global Area 4275781632 bytesFixed Size 2260088 bytesVariable Size 989856648 bytesDatabase Buffers 3271557120 bytesRedo Buffers 12107776 bytesDatabase mounted.SQL>SQL> oradebug poke 0x06001AE70 4 0x859AFAORA-00074: no process has been specifiedSQL> oradebug setmypidStatement 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: [06001AE70, 06001AE74) = 00000000AFTER: [06001AE70, 06001AE74) = 00859AFASQL> alter database open; Database altered. SQL>

Here, we will briefly explain that 4 is the length, and 0x859AFA is in hexadecimal notation. Based on the original v $ datafile_header.checkpoint_change #,
Add up to 1000000 to get this value.

We can see that the database is successfully opened. Finally, observe alert log found that there is a ora-00600 4194 error.

Thu Jun 19 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, count: 2]Thu Jun 19 14:48:46 2014Flush retried for xcb 0x159c668c8, pmd 0x15870d270Errors in file /oracle/diag/rdbms/shr/shr/trace/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 package the incident.See Note 411.1 at My Oracle Support for error and packaging details.Thu Jun 19 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 package 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 requested 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 error. The simplest method is to reconstruct the undo or process the rollback segment. Here we use the simplest method:

Modify undo_management = manual undo_tablespace = 'system' SQL> l 1 * create pfile = '/tmp/1. ora 'from spfileSQL> startup mount pfile = '/tmp/1. ora '; ORACLE instance started. total System Global Area 4275781632 bytesFixed Size 2260088 bytesVariable Size 989856648 bytesDatabase Buffers 3271557120 bytesRedo Buffers 12107776 bytesDatabase mounted. SQL> alter database open; Database altered. SQL> select name from v $ datafile; NAME users/oracle/oradata/shr/system01.dbf/oracle/oradata/shr/sysaux01.dbf/oracle/oradata/shr/users/oracle/oradata/shr/users01.dbf/oradata/shr/users /oradata/shr/logs/oradata/shr/eas_d_stand01.dbf/oradata/shr/logs/oradata/shr/eas_d_stand03.dbf/backup/eas/upload NAME logs/backup /eas/eas_d_stand05.dbf/backup/eas/eas_d_stand06.dbf 13 rows selected. SQL> show parameter undo NAME TYPE VALUE exceed ----------- choose undo_management string MANUALundo_retention integer 900undo_tablespace string SYSTEMSQL> create undo tablespace undotbs2 datafile '/oracle/oradata/shr/comment 'size 200 m; tablespace created. SQL> shutdown immediate

After undo is rebuilt, stop the database and modify the undo parameter to open the database 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 3271557120 bytesRedo Buffers 12107776 bytesDatabase mounted.SQL> alter database open; Database altered. SQL>

After processing, alert log will not throw any errors.