on 05-16-2007 10:51 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
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
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
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
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
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
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
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
User | Count |
---|---|
78 | |
9 | |
9 | |
7 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.