HELP Please --> Recover Database

[Former Member]

Hi all,

We lost some files in our old Production System (containing history) due to hardware problems (motherboard, disks, etc). The main files that we lost are the following:

/oracle/PMP/sapdata8/btabi_2/; .../btabi_3/ and .../btabi_4/*

/oracle/PMP/origlogB/*

/oracle/PMP/origlogA/*

The question is: It is possible to bring the database and SAP up and recreate those indexes? If yes, please i will be very grateful for any type of help.

We dont have any recent backup and the database was in NOARQUIVEMODE (very long story to post here).

Our system details is:

HP-UX 11.0

SAP R/3 4.5B

Oracle 8.1.7.4

The situation is very critical because we need this Database urgently.

Please, any kind of help will be appreciated.

Regards,

Arshad

[Former Member]

Hi,

Production is runnig noarchivelog ????...hard to believe...

No way other than restoring ur recent backup..IF ur having QAS with same data as that of PRD..u can recover the tablespace alone..

regards

Umesh K

[Former Member]

Hi Umesh,

Unfortunately it is true, our old DBA left us in this situation. This database is not the actual production, the actual production is in archivelog. We have recently (about 7months) migrated from that database to a new one due to new customizations, so in the new machine we only have transported "balances". They still need to consult the old database for the old transactions.

How can i recover that tablespace alone?

Regards,

Arshad

[former_member204746]

sqlplus "/ as sysdba"

startup mount

alter database backup controlfile to trace as "tmp.sql"

shutdown immediate;

edit tmp.sql and take out BTABI entries that refers to the deleted files

edit tmp.sql and take out LOG entries that refers to the deleted files

sqlplus "/ as sysdba"

startup mount

@tmp.sql

now, go in SAPDBA and do a complete reorg of tablespace PSAPBTABI

at the end, you will need to re-create to LOG files. check SAP notes.

I DID NOT test these commands out, so be cereful using them.

[Former Member]

Hi Eric,

I have tried but without success. I'm having following problem:

-

---

SVRMGR> @new_control.sql

ORACLE instance started.

Total System Global Area 542841320 bytes

Fixed Size 104936 bytes

Variable Size 236388352 bytes

Database Buffers 306012160 bytes

Redo Buffers 335872 bytes

Database mounted.

CREATE CONTROLFILE REUSE DATABASE "PMP" RESETLOGS NOARCHIVELOG

*

ORA-01503: CREATE CONTROLFILE failed

ORA-01100: database already mounted

ALTER DATABASE OPEN

*

ORA-01113: file 68 needs media recovery

ORA-01110: data file 68: '/oracle/PMP/sapdata8/btabi_2/btabi.data2'

SVRMGR>

-

---

I have followed all steps. Any other sugestion?

Regards,

Arshad

[former_member204746]

1. run:

shutdown immediate;

replace

STARTUP MOUNT

with

STARTUP NOMOUNT

try again

[Former Member]

Hi,

read some backup/restore guides before proceeding further,

guides for homogenous system copies will help u out..

Regards

Umesh K

[Former Member]

Hi,

The controlfile is created but I'm facing another problems like:

-

---

SVRMGR> alter database open resetlogs;

alter database open resetlogs

*

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/PMP/sapdata1/system_1/system.data1'

SVRMGR>

-

---

We have another copy of this database (a litle older) but with some (1 or 2) corrupted blocks. When i run DBVERIFY it gives the following error:

-

---

DBVERIFY: Release 8.1.7.4.0 - Production on Thu May 10 14:27:03 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = btabd.data15

Page 112009 is marked corrupt

***

Corrupt block relative dba: 0x1f41b589 (file 0, block 112009)

Completely zero block found during dbv:

Page 112010 is marked corrupt

***

Corrupt block relative dba: 0x1f41b58a (file 0, block 112010)

Completely zero block found during dbv:

......

DBVERIFY - Verification complete

Total Pages Examined : 261119

Total Pages Processed (Data) : 19712

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 1

Total Pages Empty : 92295

Total Pages Marked Corrupt : 149111

Total Pages Influx : 0

-

---

The question is: It is possible to fix those corrupted blocks? Sorry for "mixing" this two situations here, we need to know and decide which is the reliable solution.

Regards,

Arshad

[former_member204746]

use commands:

recover;

if it does not work:

recover using backup controlfile;

then:

alter database open resetlogs;

[Former Member]

Hi,

Now, i'm having following error while trying to open the database.

File alert.log

-

---

Updating 8.1.7.4.0 NLS parameters in sys.props$

-- adding 8.1.7.4.0 NLS parameters.

Mon May 21 11:02:51 2007

Errors in file /oracle/PMP/saptrace/background/smon_11789_pmp.trc:

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

]

Completed: alter database open

Mon May 21 11:02:57 2007

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

Mem# 0 errs 0: /oracle/PMP/origlogA/log_g13m1.dbf

Mem# 1 errs 0: /oracle/PMP/mirrlogA/log_g13m2.dbf

Mon May 21 11:02:57 2007

Errors in file /oracle/PMP/saptrace/background/smon_11789_pmp.trc:

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

]

SMON: terminating instance due to error 600

Instance terminated by SMON, pid = 11789

-

---

Any help will be appreciated.

Regards,

Arshad

[Former Member]

Hi Arshad,

Are you opening database using "resetlogs" option.

From the logs, database is trying to recover online redolog files, it should not do that when you perform recovery using backup control file and open database using resetlogs.

Regards,

Nisch

[Former Member]

Hi Nisch,

I'm opening the database normaly.

When i try to open resetlogs it gives me an error:

-

---

SVRMGR> alter database open resetlogs

2> ;

alter database open resetlogs

*

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

-

---

I've done resetlogs when created new controlfile.

What procedure do you recommend?

Regards,

Arshad

[Former Member]

Hi again,

I've tried the following procedure:

-

---

SVRMGR> startup mount

ORACLE instance started.

Total System Global Area 542841320 bytes

Fixed Size 104936 bytes

Variable Size 236388352 bytes

Database Buffers 306012160 bytes

Redo Buffers 335872 bytes

Database mounted.

SVRMGR> recover database until cancel

ORA-00279: change 418940074 generated at 05/22/2007 09:50:18 needed for thread 1

ORA-00289: suggestion : /oracle/PMP/saparch/PMParch1_1.dbf

ORA-00280: change 418940074 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

CANCEL

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/PMP/sapdata1/system_1/system.data1'

SVRMGR> alter database open

2> ;

alter database open

*

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SVRMGR> alter database open resetlogs;

Statement processed.

SVRMGR>

-

---

But still having the error in Oracle LOG file and the database shuts down:

-

---

Tue May 22 09:52:04 2007

alter database open resetlogs

Tue May 22 09:52:04 2007

RESETLOGS is being done without consistency checks. This may result

in a corrupted database. The database should be recreated.

RESETLOGS after incomplete recovery UNTIL CHANGE 418940074

Tue May 22 09:52:09 2007

Thread 1 opened at log sequence 1

Current log# 11 seq# 1 mem# 0: /oracle/PMP/origlogA/log_g11m1.dbf

Successful open of redo thread 1.

Tue May 22 09:52:09 2007

ARC0: media recovery disabled

Tue May 22 09:52:09 2007

SMON: enabling cache recovery

Tue May 22 09:52:10 2007

Dictionary check beginning

Dictionary check complete

Tue May 22 09:52:14 2007

SMON: enabling tx recovery

Tue May 22 09:52:14 2007

Updating 8.1.7.4.0 NLS parameters in sys.props$

-- adding 8.1.7.4.0 NLS parameters.

Tue May 22 09:52:14 2007

Errors in file /oracle/PMP/saptrace/background/smon_7645_pmp.trc:

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

]

Completed: alter database open resetlogs

Tue May 22 09:52:20 2007

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

Mem# 0 errs 0: /oracle/PMP/origlogA/log_g11m1.dbf

Tue May 22 09:52:20 2007

Errors in file /oracle/PMP/saptrace/background/smon_7645_pmp.trc:

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

]

SMON: terminating instance due to error 600

Instance terminated by SMON, pid = 7645

-

---

What can i do now?

Regards,

Arshad

[Former Member]

Hi Arshad,

You have done well so far, except you did not use "using backup controlfile" to perform your recovery. The recovery you performed, looked at your current controlfile and assumed the scn's that are recorded in current controlfile to perform the recovery process.

When you recover using 'backup controlfile' all you tell oracle is not to rely on the

controlfile to lead the recovery. What it will do is go to the datafiles and get the oldest sequence number and request that archive and open it in a consistant state.

Hope this helps.

Regards,

Nisch

[Former Member]

Hi,

I've tried the following procedure but the problems persists:

-

---

SVRMGR> recover database until cancel using backup controlfile;

ORA-00279: change 418940076 generated at 05/22/2007 09:52:08 needed for thread 1

ORA-00289: suggestion : /oracle/PMP/saparch/PMParch1_1.dbf

ORA-00280: change 418940076 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/oracle/PMP/saparch/PMParch1_1.dbf'

ORA-27037: unable to obtain file status

HP-UX Error: 2: No such file or directory

Additional information: 3

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/PMP/sapdata1/system_1/system.data1'

SVRMGR> alter database open

2> ;

alter database open

*

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SVRMGR> alter database open resetlogs

2> ;

Statement processed.

SVRMGR>

-

---

alert.log

-

---

.....

WARNING! Recovering data file 134 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 135 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

WARNING! Recovering data file 136 from a fuzzy file. If not the current file

it might be an online backup taken without entering the begin backup command.

Media Recovery Log

ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel usi...

Thu May 24 10:56:51 2007

ALTER DATABASE RECOVER CONTINUE DEFAULT

Media Recovery Log /oracle/PMP/saparch/PMParch1_1.dbf

ORA-308 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...

Thu May 24 10:56:51 2007

ALTER DATABASE RECOVER CANCEL

ORA-1547 signalled during: ALTER DATABASE RECOVER CANCEL...

Thu May 24 10:57:13 2007

alter database open

Thu May 24 10:57:13 2007

ORA-1589 signalled during: alter database open

...

Thu May 24 10:57:23 2007

alter database open resetlogs

RESETLOGS is being done without consistency checks. This may result

in a corrupted database. The database should be recreated.

RESETLOGS after incomplete recovery UNTIL CHANGE 418940076

Online log 12 of thread 1 was previously cleared

Online log 13 of thread 1 was previously cleared

Online log 14 of thread 1 was previously cleared

Thu May 24 10:57:26 2007

Thread 1 opened at log sequence 1

Current log# 11 seq# 1 mem# 0: /oracle/PMP/origlogA/log_g11m1.dbf

Successful open of redo thread 1.

Thu May 24 10:57:26 2007

ARC0: media recovery disabled

Thu May 24 10:57:26 2007

SMON: enabling cache recovery

Thu May 24 10:57:26 2007

Dictionary check beginning

Dictionary check complete

Thu May 24 10:57:31 2007

SMON: enabling tx recovery

Thu May 24 10:57:31 2007

Updating 8.1.7.4.0 NLS parameters in sys.props$

-- adding 8.1.7.4.0 NLS parameters.

Thu May 24 10:57:31 2007

Errors in file /oracle/PMP/saptrace/background/smon_16297_pmp.trc:

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

]

Completed: alter database open resetlogs

Thu May 24 10:57:36 2007

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

Mem# 0 errs 0: /oracle/PMP/origlogA/log_g11m1.dbf

Thu May 24 10:57:36 2007

Errors in file /oracle/PMP/saptrace/background/smon_16297_pmp.trc:

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

]

SMON: terminating instance due to error 600

Instance terminated by SMON, pid = 16297

-

---

Is there any other solution for this except "restore"?

I have tried to follow the following procedure but without success:

http://www.dbspecialists.com/presentations/missing_logs.html

Regards,

Arshad

[former_member204746]

check /oracle/PMP/saptrace/background/smon_16297_pmp.trc

[Former Member]

Hi all,

We have successfully opened the database. We have recreated the missed tablespace "PSAPBTABI" and now we are creating all missed index. The SAP system is up and running. Any other trouble re-creating the indexes i will open another separate "message".

Thanx for all support.

Regards,

Arshad