Solved: Unable to open oracle database after recovery

Dear All,

I am working on system refresh. getting error during restore.

Unable to fix. need suggestion and help to fix the same.

After executing command SQL > recover database using backup controfile until cancel;

I applied the logs. and it shows SQL > media recovery completed.

But, when I execute SQL > alter database open resetlogs; I am getting below errors.

Need some help.

Regards

Ahmad.

SAP Managed Tags:
Oracle Database

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (1)

Hi A S,

I think I have encountered it before but i forgot how i solved it. As far I remembered, can you consider the controlfile? please check the controlfile if the same with datafiles folders.

SQL>STARTUP MOUNT

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\CONTROL.SQL';

Open CONTROL.SQL in notepad

and may I know steps/procedure on how you restore the datafiles? possible that there's something wrong in your restoration.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi James,

I have already tried with startup mount;

SQL>STARTUP MOUNT;

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\CONTROL.SQL';

I am trying to restore offline FS backup from Prod to Qa.

After copying sapdata and redologs, created control files and executed below command.

SQL> Alter database open resetlogs; (since recovery not required for offline backup)

It was asking to recover, so executed below command:

SQL> recover database using backup controlfile until cancel;

It was asking for redologs file sequence to complete the recovery, hence i have provided the redologs which was copied already to qa during sapdata copy.

Media was recovered from redolog file that i provided.

now after media recovery, i am trying to execute below command for resetlogs.

SQL> alter database open resetlogs;

after the above error, i checked the alert.SID.log for more information, found the below error ORA-00600 error.

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Thanks

Ahmad

Hi A S,

Sorry but im not yet familiar with OFFLINE FS BACKUP. I only knew was OFFLINE BACKUP produced by BRBACKUP. I dont know what is the different between the two but all I can surely share with you is that OFFLINE BACKUP does not need recover database using backup controlfile until cancel. With OFFLINE BACKUP you can direct to open your database ALTER DATABASE OPEN after the restoration.

another question... you said from PRD to QA?

in the QA server does the SAP already installed? IF YES maybe they have different schema/OPS$ so that why you always disconnected whenever you try to open your database.

Hi James,

Once the database recovery and open resetlogs completed, then only we have to drop the existing ops$ and recreate new for QA.

create ops$ not required again for NW740, ssfs password needs to be change / update for before sap start.

Here in my case database not opened, hence unable to proceed further post activities.

thanks

Ahmad

Dear All,

Finally, the issue/error: ORA-00600: internal error code, arguments: [4194], [], [], has been fixed with below solution:

After analysis we found that there is a mismatch has been detected between Redo records and rollback (Undo) records. This error (ora-00600) is reported when the validation fails.

So we have to create a new UNDO tablespace and drop the old one which is corrupted.

Below are the workaround steps to overcome the above issue:

BEFORE PERFORMING BELOW STEPS TAKE A BACKUP OF PFILE /SPFILE FROM THE BELOW LOCATION:

BATCFS4>cd /oracle/<SID>/11204_64/dbs

BATCFS4> cp spfile<SID>.ora spfile<SID>.ora.orig

Start the database with mount state.

SQL>startup mount;

SQL> show parameter undo;

SQL>alter system set undo_management=’MANUAL’ SCOPE=SPFILE;

Now UNDO_MANAGEMENT set from AUTO to MANUAL.

SHUTDOWN THE DATABASE

SQL> SHUTDOWN IMMEDIATE;

START THE DATABSE USING STARTUP Only not mount;

SQL> STARTUP;

Now database is opened without any issues.

Now create a new UNDO TABLESPACE (PSAPUNDO1)

SQL> create UNDO tablespace PSAPUNDO1 datafile '/oracle/<SID>/sapdata6/undo_1/undo01.data1' size 1024M

Now change the undo_tablespace to newly created UNDO tablespace (PSAPUNDO1)

SQL> ALTER SYSTEM SET undo_tablespace = 'PSAPUNDO1' scope=spfile;

Now change the UNDO_MANAGEMENT from MANUAL to AUTO

SQL> alter system set undo_management='AUTO' scope=spfile;

Now bounce the database

SQL> shutdown immediate;

SQL> startup;

Now go and check the alert<SID>.log for any errors.

batcfs4> tail -400 /oracle/<SID>/saptrace/diag/rdbms/<SID>/<SID>/trace/alert_<SID>.log

So, there is no errors found in alert<SID>.log

Now drop the old UNDO tablespace (PSAPUNDO)

SQL> drop tablespace PSAPUNDO including contents and datafiles;

Now create the new PSAPUNDO tablespace in Target system according to source system PSAPUNDO size.

Before creating, we should know the Source system PSAPUNDO data files sizes. Go to source system, execute the below command:

SQL> set line 1000;

SQL> col file_name for a60;

SQL> select FILE_NAME, bytes/1024/1024, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like 'PSAPUNDO%';

FILE_NAME BYTES/1024/1024 TABLESPACE_NAME

------------------------------------------------------------ --------------- ------------------------------

/oracle/<SID>/sapdata6/undo_1/undo.data1 2000 PSAPUNDO

/oracle/<SID>/sapdata9/undo_2/undo.data2 500 PSAPUNDO

/oracle/<SID>/sapdata9/undo_3/undo.data3 1024 PSAPUNDO

/oracle/<SID>/sapdata9/undo_4/undo.data4 1024 PSAPUNDO

/oracle/<SID>/sapdata9/undo_5/undo.data5 1024 PSAPUNDO

/oracle/<SID>/sapdata9/undo_6/undo.data6 1024 PSAPUNDO

6 rows selected.

SQL>

Now go to Target system and create new PSAPUNDO tablespace according to source datefile size.

SQL> create UNDO tablespace PSAPUNDO datafile '/oracle/<SID>/sapdata6/undo_1/undo.data1' size 2000M

SQL> alter tablespace PSAPUNDO add datafile '/oracle/<SID>/sapdata9/undo_2/undo.data2' size 500M

SQL> alter tablespace PSAPUNDO add datafile '/oracle/<SID>/sapdata9/undo_3/undo.data3' size 1024M

SQL> alter tablespace PSAPUNDO add datafile '/oracle/<SID>/sapdata9/undo_4/undo.data4' size 1024M;

SQL> alter tablespace PSAPUNDO add datafile '/oracle/<SID>/sapdata9/undo_5/undo.data5' size 1024M;

SQL> alter tablespace PSAPUNDO add datafile '/oracle/<SID>/sapdata9/undo_6/undo.data6' size 1024M;

Now change the undo_tablespace to newly created PSAPUNDO.

SQL>show parameter undo;

SQL>alter system set undo_tablespace='PSAPUNDO' scope=both;

Now drop the TABLESPACE PSAPUNDO1

SQL> drop tablespace PSAPUNDO1 including contents and datafiles;

bounce the database instance

SQL>shutdown immediate;

SQL>startup;

Answers (2)

Hi A.,

the ORA-0600 is an internal oracle error and the reason for ora-03113. This error can be the result of several things.

1. Did you use the right oracle-binaries for the db? For example: You use the binaries for ORACLE 11.2.0.2 and you restored a backup of a db running on ORACLE 10.

2. Is the NFS ok? Could yuo please ask your OS admins.

Please post

/oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_q001_20595.trc

Best regards

Willi Eimler

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Willi Eimler,

Thanks for your reply, yes both the DB source and target are same version only.

Refreshing production data into quality.

Please find the last 100 lines of log file (/oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_q001_20595.trc)

BS1_q001_20595.trc:

*** ktuc_diag_dmp: dump of buffer cache for rdba 0x4d8049b0

Dump of buffer cache at level 4 for tsn=1 rdba=1300253104

BH (0xc0000002fbf35f10) file#: 310 rdba: 0x4d8049b0 (310/18864) class: 32 ba: 0xc0000002fab14000

set: 24 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0

dbwrid: 0 obj: -1 objn: 0 tsn: 1 afn: 310 hint: f

hash: [0xc00000041497e4b0,0xc00000041497e4b0] lru: [0xc0000002fbf36138,0xc0000002fbf35ec8]

obj-flags: object_ckpt_list

ckptq: [0xc000000417a61a20,0xc0000002fbf363f0] fileq: [0xc000000417a640e0,0xc000000417a640e0] objq: [0xc0000002fbf36508,0xc00000040d4a0920] objaq: [0xc0000002fbf36518,0xc00000040d4a0900]

use: [NULL] wait: [NULL]

st: XCURRENT md: NULL fpin: 'kcbwh2: kcbchg1' tch: 1 txn: 0xc0000004107ca9f8

flags: buffer_dirty mod_started private

change state: NOT_ACTIVE_YET

change count: 1

LRBA: [0x5.33.0] LSCN: [0x3c.9f299465] HSCN: [0x3c.9f299465] HSUB: [65535]

*** ktuc_diag_dmp: dump of redo for rdba 0x4d8049b0

DUMP REDO

Opcodes *.*

DBAs (file#, block#):

(310, 18864) .

SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff

**NOTE: Only Dumping Redo less then 12 hours**

Times: 02/24/2016 09:16:29 thru eternity

Initial buffer sizes: read 1024K, overflow 820K, change 805K

Thread 1 low checkpoint scn: 0xffff.ffffffff

Thread 1 low checkpoint scn: 0x003c.9f28a843

SCN Start Scan Point: scn: 0x003c.9f28a843 (260368279619)

Initial buffer sizes: read 1024K, overflow 820K, change 805K

INCARNATION:

START: scn: 0x003c.9f2859d4 (260368259540) Timestamp: 02/24/2016 17:03:32

END: scn: 0xffff.ffffffff

descrip:"Thread 0001, Seq# 0000000002, SCN 0x003c9f28a843-0x003c9f28f6e5"

REDO RECORD - Thread:1 RBA: 0x000002.00000002.0010 LEN: 0x0078 VLD: 0x05

SCN: 0x003c.9f28a845 SUBSCN: 1 02/24/2016 17:08:14

(LWN RBA: 0x000002.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x003c.9f28a843)

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.3 ENC:0

descrip:"Thread 0001, Seq# 0000000003, SCN 0x003c9f28f6e5-0x003c9f29457d"

REDO RECORD - Thread:1 RBA: 0x000003.00000002.0010 LEN: 0x0078 VLD: 0x05

SCN: 0x003c.9f28f6e7 SUBSCN: 1 02/24/2016 17:17:59

(LWN RBA: 0x000003.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x003c.9f28f6e5)

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.3 ENC:0

descrip:"Thread 0001, Seq# 0000000004, SCN 0x003c9f29457d-0x003c9f299406"

REDO RECORD - Thread:1 RBA: 0x000004.00000002.0010 LEN: 0x0078 VLD: 0x05

SCN: 0x003c.9f29457f SUBSCN: 1 02/24/2016 17:25:28

(LWN RBA: 0x000004.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x003c.9f29457d)

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.3 ENC:0

descrip:"Thread 0001, Seq# 0000000005, SCN 0x003c9f299406-0xffffffffffff"

REDO RECORD - Thread:1 RBA: 0x000005.00000002.0010 LEN: 0x0078 VLD: 0x05

SCN: 0x003c.9f299408 SUBSCN: 1 02/24/2016 21:16:04

(LWN RBA: 0x000005.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x003c.9f299406)

CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.3 ENC:0

END OF DUMP REDO

OPIRIP: Uncaught error 600. Error stack:

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

thanks

Ahmad

Hi Ahmad,

Please try below things to see if that helps..

Stop DB

Delete shared memory ipcrm.

Reboot the OS.

Drop and re-create tablespace PSAPROLL.

Also, check below sapnote

694155 - Error due to corrupt redo logs

Regards,

Prithviraj.

Hi Prithviraj,

Thanks for your reply, i have already did clean restart and tried but no luck.

I will check the sap note.

thanks

Ahmad.

Hi,

you are getting an ORA-600 with an "undocumented" parameter.

open an SAP incident as there is a non public solution that might apply here

Hi fidel,

Yes i have already raised message to SAP for Ora-600

Awaiting for update from SAP, i am also meanwhile checking for possibilities.

Thanks

ahmad.

Hi All,

I have a doubt, i was checking the opatch patch version between QA / Production, but i noticed that one patch is not found in production when compare to quality.

Quality:

Production:

in quality patch is 11.2.0.4.4

in prod patch is 11.2.0.4.3

Is the above difference, impact our system refresh from prod to qa. ?

But OPatch version in both qa/prod are same only. any suggestion will help me here, please share your inputs.

Quality:

Production:

Thanks

Ahmad

HI,

The last version 11.2.0.3.4 the last 4 is platform specific release number.its only fix the secuirity and bug fixes. I am not sure its affecting the bacup restore. try he last solution.

SQL> Shutdown Immediate;

SQL>connect / as sysdba

SQL>recover database until cancel using backup controlfile;

Type 'cancel' here.

Then open the database with resetlogs

SQL>alter database open resetlogs;

Thanks,

Brindavan M

Hi Brindavan,

Database already completed media recovery.

If i use below command, it will show that recovery not required.

recover database until cancel using backup controlfile;

thanks

ahmad

Hi ,

There must be a communication error(Network issues) with client server,check the "alert.log" files more on this issue".

check for network problems and review the SQL*Net the server process might be dead and whether a trace file was generated at failure time.

Webserver or listener process is not running

Missing entry to the "tnsnames.ora" file check the port number.

Network issues

Problems with connection pooling (shared servers, MTS)

Thanks,

Brindavan M

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Brindavan,

Thanks for your reply.

I see some errors in alert.log

Errors:

=====

Mem# 0: /oracle/BS1/origlogA/log_g11m1.dbf

Mem# 1: /oracle/BS1/mirrlogA/log_g11m2.dbf

Block recovery completed at rba 5.52.16, scn 60.2670302313

Errors in file /oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_q001_20595.trc (incident=84472):

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Flush retried for xcb 0xc0000004107ca9f8, pmd 0xc000000412a820f8

Block recovery from logseq 5, block 51 to scn 260368340069

Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0

Mem# 0: /oracle/BS1/origlogA/log_g11m1.dbf

Mem# 1: /oracle/BS1/mirrlogA/log_g11m2.dbf

Block recovery completed at rba 5.52.16, scn 60.2670302313

Errors in file /oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_q001_20595.trc (incident=84473):

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Flush retried for xcb 0xc0000004107ca9f8, pmd 0xc000000412a820f8

Block recovery from logseq 5, block 51 to scn 260368340069

Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0

Mem# 0: /oracle/BS1/origlogA/log_g11m1.dbf

Mem# 1: /oracle/BS1/mirrlogA/log_g11m2.dbf

Block recovery completed at rba 5.52.16, scn 60.2670302313

Errors in file /oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_q001_20595.trc (incident=84474):

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

opidrv aborting process Q001 ospid (20595) as a result of ORA-600

Wed Feb 24 21:16:32 2016

Flush retried for xcb 0xc0000004107ca9f8, pmd 0xc000000412a820f8

Block recovery from logseq 5, block 51 to scn 260368340069

Recovery of Online Redo Log: Thread 1 Group 1 Seq 5 Reading mem 0

Mem# 0: /oracle/BS1/origlogA/log_g11m1.dbf

Mem# 1: /oracle/BS1/mirrlogA/log_g11m2.dbf

Block recovery completed at rba 5.52.16, scn 60.2670302313

Errors in file /oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_pmon_20523.trc (incident=84275):

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

Use 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/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_pmon_20523.trc:

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

PMON (ospid: 20523): terminating the instance due to error 472

Wed Feb 24 21:16:34 2016

System state dump requested by (instance=1, osid=20523 (PMON)), summary=[abnormal instance termination].

System State dumped to trace file /oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_diag_20531_20160224211634.trc

Dumping diagnostic data in directory=[cdmp_20160224211634], requested by (instance=1, osid=20523 (PMON)), summary=[abnormal instance termination].

Instance terminated by PMON, pid = 20523

thanks

Ahmad

In the above alert.log file, i just noticed that errors found in below trace file.

/oracle/BS1/saptrace/diag/rdbms/bs1/BS1/trace/BS1_q001_20595.trc

error 472 detected in background process

ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

kjzduptcctx: Notifying DIAG for crash event

----- Abridged Call Stack Trace -----

ksedsts()+592<-kjzdssdmp()+720<-kjzduptcctx()+512<-kjzdicrshnfy()+160<-$cold_ksuitm()+5808<-$cold_ksbrdp()+2768<-opirip()+1312<-opidrv()+1152<-sou2o()+256<-opimai_real()+352<-ssthrdmain()+608<-main()+3

36<-main_opd_entry()+80

----- End of Abridged Call Stack Trace -----

*** 2016-02-24 21:16:33.251

PMON (ospid: 20523): terminating the instance due to error 472

ksuitm: waiting up to [5] seconds before killing DIAG(20531)

thanks

Ahmad.

Hi ,

Let abort the database and try to restart the systema no oracle process should not run. clean the shared memory as well.

Then start the database with mount mode and then do the resetlog to open the database.

Thanks,

Brindavan M

I tried reboot server but still same error.

thanks

Ahmad

Message was edited by: A S

Hi ,

I said that start the database with mount mode, then do the resetlog once the restlog done open the database.

SQL> startup mount;

SQL> Alter database open resetlog;

SQL> Alter database open;

After all this done when you try to opening the database and if you get error again then do the below step.

Loginto orasid and then excute the below command.

SQL> relink all

Then try to open the database.

Thanks,

Brindavan

hi Brindavan,

I tried but still same error. I have also noticed that database is starting in using "startup mount" but after executing "alter database open" databse is going down (shutdown).

Thanks

Ahmad

HI,

Have you done the database resetlog? once the database done the restlog then open the database.

Thanks,

Brindavan

Hi Brindavan,

after media recovery compete, we can't execute resetlog. please see the below.

Thanks

Ahmad

Some of the replies are amazing.

Could you check whether the source system Oracle version and the target system version are on same level, including the patch level. Eg: 11.2.0.3

If the systems are not on the same release/patch level then you cannot do a backup and restore.

Hi Reagan Benjamin,

Yes i have verified the same already.

Prod:

QA:

thanks

ahmad

Ask a Question

Top Q&A Solution Author

User

Count

Unable to open oracle database after recovery

Accepted Solutions (1)

Accepted Solutions (1)

Answers (2)

Answers (2)

Re: Couldn't create an instance of Cloud Identity ...

Re: Couldn't create an instance of Cloud Identity ...

Re: SAP CRYSTAL REPORT 2020 support ODBC DRIVER 18...

PowerDesigner 16.7.7.5 release notes is being crea...

Re: Crystal Reports text clipping issue. PDF creat...