Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to deal with Oracle ORA-03113 ORA-600 failure". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. Failure phenomenon (1) start phenomenon SQL > startup;ORA-03113 end-of-file on communication channelSQL > startup nomount; # you can nomount successfully SQL > alter database mount;ORA-03113 end-of-file on communication channel

# from the above phenomenon, according to the database startup process, the basic positioning is that there is a problem with the control file.

(2) alert log phenomenon Wed Jul 2910: 17:07 2020ALTER DATABASE MOUNTUSER (ospid: 3784): terminating the instanceSystem state dump requested by (instance=1, osid=3784), summary= [abnormal instance termination]. System State dumped to trace file E:\ APP\ ADMINISTRATOR\ diag\ rdbms\ dzwl\ dzwl\ trace\ dzwl_diag_2708.trcDumping diagnostic data in directory= [cdmp _ 20200729101712], requested by (instance=1, osid=3784), summary= [abnormal instance termination]. Instance terminated by USER, pid = 37842. Malfunction analysis

# if there is a problem, by asking the on-site personnel, the server has power outage, restart and other operations, and most of the trace files do not have clear information

# in the alert log, the metadata metadata of the trm tracking file of the mmon process on the previous day was corrupted due to a power failure.

# but it has nothing to do with this failure, but it can also be seen that the file is corrupted due to the power outage, followed by the startup process of 10046 trace

# sure enough, it is found that the seq number of the file header record of the control file content is not consistent with the bhcsq which is presumed to be the block header record of the control file.

# troubleshooting steps:

10046 track PARSING IN CURSOR # 79065416 len=20 dep=0 uid=0 oct=35 lid=0 tim=8397179226 hv=1913505115 ad='7ff8f1ab3f40' sqlid='fr02x8dt0vjav'alter database mountEND OF STMT...WAIT # 79065416: nam='control file sequential read' ela= 147file#=0 block#=16 blocks=1 obj#=-1 tim=8401282794Error: kccpb_sanity_check_2Control file sequence number mismatchmakers fhcsq: 3714bhcsq: 3717Fhcsq: 3717Fhcsq. Troubleshooting (1) try to have a good control file

Due to the configuration of flashback, the flashback area control file and the data file directory control file are used to try to see if there are intact control files, and it is found that there are problems with the control files.

(2) there is no backup, only control files can be rebuilt.

Edit the create control file statement:

CREATE CONTROLFILE REUSE DATABASE "DZWL" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXDATAFILES 100 MAXINSTANCES 2 MAXLOGHISTORY 453 LOGFILE GROUP 1m E:\ app\ Administrator\ oradata\ DZWL\ REDO01.LOG' SIZE 50m, GROUP 2cm E:\ app\ Administrator\ oradata\ DZWL\ REDO02.LOG' SIZE 50m GROUP 3roomE:\ app\ Administrator\ oradata\ DZWL\ REDO03.LOG' SIZE 50m DATAFILE'E:\ app\ Administrator\ oradata\ DZWL\ DATASYN01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ DZWL.DBF', 'E:\ app\ Administrator\ oradata\ DZWL\ DZWL2019A.DBF','E:\ app\ Administrator\ oradata\ DZWL\ DZWL2019B.DBF' 'e:\ app\ Administrator\ oradata\ DZWL\ DZWL2020A.DBF','E:\ app\ Administrator\ oradata\ DZWL\ DZWL2020B.DBF','E:\ app\ Administrator\ oradata\ DZWL\ DZWL2021A_01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ DZWL2021B_01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ EXAMPLE01.DBF' 'e:\ app\ Administrator\ oradata\ DZWL\ MT01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ SYSAUX01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ SYSTEM01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ UNDOTBS01.DBF','E:\ app\ Administrator\ oradata\ DZWL\ USERS01.DBF' CHARACTER SET ZHS16GBK 4. Normal mount,open returns error ORA-600 again [4193]

Database can be normal mount,open phase, error ORA-600 [4193], undo tablespace problems.

It is solved by the following Mos document:

ORA-600 [4193] error solution

This workaround applies to Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2] with no platform restrictions.

Reason:

1, it may be an internal error caused by the same UNDO block for two different transactions.

2, ORA-600 [4193] / ORA-600 [4194] for new transactions

3, ORA-600 [4137] for a transaction rollback

Solution:

Create a new UNDO tablespace and check for segments that have not been rolled back.

1. Create a pfile file

Create pfile='E:\ pfile.txt' from spfile

Windows platform is under database by default, and linux is under dbs.

two。 Close the instance

Shutdown immediate

3. Edit pfile file add parameters

Undo_management = manualevent = '10513 trace name context forever, level 2' # will prohibit the smon process from performing transaction rollback operations in order to successfully open the database and skip the rollback step

4. Start the database with pfile

Startup restrict pfile=

5. Check whether all UNDO segments are offline status, and system segments must be online

Select tablespace_name,status, segment_name from dba_rollback_segs where status! = 'OFFLINE'

6. Create a new UNDO tablespace

Create undo tablespace UNDOTBS2 datafile'D:\ oradata\ undo02' size 2000m

7. Delete the old UNDO tablespace

Drop tablespace UNDOTBS1 including contents and datafiles

8. Close the instance

Shutdown immediate

9. Boot to mount state

Startup mount

10. Modify parameters

Alter system set undo_tablespace = 'UNDOTBS2' scope=spfile

11. Close the instance

Shutdown immediate

twelve。 Start the database normally

This is the end of startup's "how to deal with Oracle ORA-03113 ORA-600 failures". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.