System Copy Oracle DB restore

[Former Member]

Hello Experts,

we are restoring the Oralce 10g DB [Windows 2003]using Homogenious system copy.

Duting the Sapinst steps backup/restore dabase steps.

we receive  a following error message in the     sapinst.log.

Total System Global Area  775946240 bytes
Fixed Size      1299120 bytes
Variable Size    448793936 bytes
Database Buffers   322961408 bytes
Redo Buffers      2891776 bytes
Database mounted.

Database altered.

ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORA-01109: database not open

We donot have backup controlfile;<Archiive fle> to restore the control file.

Please advise me to recover the Oracle 10g database.

Thanks

Thirumalai

[Former Member]

Hi,

In addition to the amendments suggested bu Ruchit, change the noresetlogs to resetlogs in your control.sql.

Thanks,

Sanketh.

[Former Member]

Dear All,

MY source system has been crashed. We have only file copy backup of following folders.
We have to use this to rebuild a new system.

\usr
\oracle\<SID>\sapdata1->all the files exists with <cntrl\CNTRL<SID>.DBF>
\oracle\<SID>\sapdata2->all the files exists
\oracle\<SID>\sapdata3->all the files exists
\oracle\<SID>\sapdata4->all the files exists
\oracle\<SID>\saptrace->all the files exists
\oracle\<SID>\102 ->all the files exists
\oracle\<SID>\sapcheck ->File exists
\oracle\<SID>\sapreorg -> old file exists
\oracle\<SID>\saptrace->old file exists

\oracle\<SID>\mirrlogA ->Log file exists
\oracle\<SID>\mirrlogB ->Log file exists
\oracle\<SID>\origlogA ->CNTRL<SID>.DBF exists
\oracle\<SID>\origlogB->CNTRL<SID>.DBF exists

We donot have backup of following folders files.
\oracle\<SID>\saparch
oracle\SID\oraarch
oracle\SID\sapbackup

According to the system copy guide, I cannot perform any source system task since My source system crashed.
Using system copy guide "SAP NetWeaver 2004s Installation Guide" [sample control file txt exists] I created and adopted a control.sql file.

<Error>
now duing the Sapinst<Homo genious system copy> backup/restore dabase steps.
<ora_sql_results.log>

ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORACLE\<SID>RH\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
*
ERROR at line 1:
ORA-01109: database not open

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area  775946240 bytes
Fixed Size      1299120 bytes
Variable Size    448793936 bytes
Database Buffers   322961408 bytes
Redo Buffers      2891776 bytes
Database mounted.

Database altered.

ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

ORA-01109: database not open

Thanks & Regards
Thirumalai

[Former Member]

Hi Thiru,

This file level backup--was it taken when database was online?From the logs you have sent it seems so since it is asking for recovery.Since archivelog (orarch) wasn't backed up and this looks an online backup recovery isn't possible. It is a gone case as far as I see...Unless and until you are willing to take some risks..

I have used the parameter _ALLOW_RESETLOGS_CORRUPTION=TRUE in certain cases with varying succeess but it is not foolproof method not really a great recommendation.. But in your case where the system anyways seems to have been lost it might be the last resort. Seeting this parameter in pfile may probably allow you to open the database. But no assurance that future corruption issues won't surface. So probably aftr you have opened it you might want to export the DB and import it again ! The link below gives an idea:

http://practicalappsdba.wordpress.com/2008/04/01/how-to-recover-and-open-the-database-if-the-archive...

This being an Oracle specific link is talking abt Oracle export/import utilities (exp/imp or the datapump) however you might use SAPinst export/import !

However not a bad idea consulting SAP also before to move ahead !!

And if future don't take online backups without archivelogs. It is a useless backup !

Regards.

Ruchit.

[Former Member]

Hi Ruchit,

Thank you very much for understood the issue and your detailed expaination..

After the system crash when online,  hardware vendor tried to recover above files only..

yesterday i tried the  Option, [ _ALLOW_RESETLOGS_CORRUPTION=TRUE] and its failed in step3 as below -received the following resuts/error message. ]

http://www.oracledatabase12g.com/archives/how-to-force-the-database-open-internal-only.html

SQL> startup mount
ORACLE instance started.

Total System Global Area  775946240 bytes
Fixed Size                  1299120 bytes
Variable Size             448793936 bytes
Database Buffers          322961408 bytes
Redo Buffers                2891776 bytes
Database mounted.
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 547166850 generated at 12/01/2011 20:23:23 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\<SID>\ORAARCH\ARCHARC18743_0673549770.001
ORA-00280: change 547166850 for thread 1 is in sequence #18743

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: 'F:\ORACLE\<SID>\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

ORA-01112: media recovery not started

SQL>

SQL> ALTER DATABASE OPEN RESETLOGS
  2  ;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ERROR:
ORA-03114: not connected to ORACLE

SQL> ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1';

Session altered.

Suspecting that file 'F:\ORACLE\<SID>\SAPDATA4\SYSTEM_1\SYSTEM.DATA1' has some

issues. again the copying he file again from the backup drive.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Also i am supecting that the control which created by manually [Folder strcuture] correct or  not.

==========================================================
REM CONTROL.SQL
REM
REM SAP AG Walldorf
REM Systeme, Anwendungen und Produkte in der
Datenverarbeitung
REM
REM (C) Copyright SAP AG 2004
REM
==========================================================
==========
REM Generated at:
REM Fri Sep 17 08:33:25 CEST 2004
REM for target system <SID>
REM on
REM Windows 2000 5.0 x86
CONNECT / AS SYSDBA
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE
SET DATABASE "<SID>"
RESETLOGS
ARCHIVELOG
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 1022
MAXINSTANCES 50
MAXLOGHISTORY 65000
LOGFILE
GROUP 1 (
'F:\oracle\<SID>\origlogA\LOG_G11M1.DBF',
'F:\oracle\<SID>\mirrlogA\LOG_G11M2.DBF'
) SIZE 50M,
GROUP 2 (
'F:\oracle\<SID>\origlogB\LOG_G12M1.DBF',
'F:\oracle\<SID>\mirrlogB\LOG_G12M2.DBF'
) SIZE 50M,
GROUP 3 (
'F:\ORACLE\<SID>\ORIGLOGA\LOG_G13M1.DBF',
'F:\ORACLE\<SID>\MIRRLOGA\LOG_G13M2.DBF'
) SIZE 50M,
GROUP 4 (
'F:\ORACLE\<SID>\ORIGLOGB\LOG_G14M1.DBF',
'F:\ORACLE\<SID>\MIRRLOGB\LOG_G14M2.DBF'
) SIZE 50M
DATAFILE
'F:\ORACLE\<SID>\SAPDATA4\SYSTEM_1\SYSTEM.DATA1',
'E:\ORACLE\<SID>\SAPDATA1\SR3_1\SR3.DATA1',
'E:\ORACLE\<SID>\SAPDATA1\SR3_2\SR3.DATA2',
'E:\ORACLE\<SID>\SAPDATA1\SR3_3\SR3.DATA3',
'E:\ORACLE\<SID>\SAPDATA1\SR3_4\SR3.DATA4',
'E:\ORACLE\<SID>\SAPDATA1\SR3_5\SR3.DATA5',
'E:\ORACLE\<SID>\SAPDATA1\SR3_6\SR3.DATA6',
'E:\ORACLE\<SID>\SAPDATA1\SR3_7\SR3.DATA7',
'E:\ORACLE\<SID>\SAPDATA1\SR3_8\SR3.DATA8',
'E:\ORACLE\<SID>\SAPDATA1\SR3_9\SR3.DATA9',
'E:\ORACLE\<SID>\SAPDATA1\SR3_10\SR3.DATA10',
'E:\ORACLE\<SID>\SAPDATA2\SR3_11\SR3.DATA11',
'E:\ORACLE\<SID>\SAPDATA2\SR3_12\SR3.DATA12',
'E:\ORACLE\<SID>\SAPDATA2\SR3_13\SR3.DATA13',
'E:\ORACLE\<SID>\SAPDATA2\SR3_14\SR3.DATA14',
'E:\ORACLE\<SID>\SAPDATA2\SR3_15\SR3.DATA15',
'E:\ORACLE\<SID>\SAPDATA2\SR3_16\SR3.DATA16',
'E:\ORACLE\<SID>\SAPDATA2\SR3_17\SR3.DATA17',
'E:\ORACLE\<SID>\SAPDATA2\SR3_18\SR3.DATA18',
'E:\ORACLE\<SID>\SAPDATA2\SR3_19\SR3.DATA19',
'E:\ORACLE\<SID>\SAPDATA2\SR3_20\SR3.DATA20',
'F:\ORACLE\<SID>\SAPDATA3\SR3_21\SR3.DATA21',
'F:\ORACLE\<SID>\SAPDATA3\SR3_22\SR3.DATA22',
'F:\ORACLE\<SID>\SAPDATA3\SR3_23\SR3.DATA23',
'F:\ORACLE\<SID>\SAPDATA3\SR3_24\SR3.DATA24',
'F:\ORACLE\<SID>\SAPDATA4\SR3_25\SR3.DATA25',
'F:\ORACLE\<SID>\SAPDATA4\SR3_26\SR3.DATA26',
'F:\ORACLE\<SID>\SAPDATA4\SR3_27\SR3.DATA27',
'F:\ORACLE\<SID>\SAPDATA4\SR3_28\SR3.DATA28',
'F:\ORACLE\<SID>\SAPDATA4\SR3_29\SR3.DATA29',
'F:\ORACLE\<SID>\SAPDATA4\SR3_30\SR3.DATA30',
'F:\ORACLE\<SID>\SAPDATA4\SR3_31\SR3.DATA31',
'F:\ORACLE\<SID>\SAPDATA4\SR3_32\SR3.DATA32',
'F:\ORACLE\<SID>\SAPDATA4\SR3_33\SR3.DATA33',
'F:\ORACLE\<SID>\SAPDATA4\SR3_34\SR3.DATA34',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_1\SR3700.DATA1',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_2\SR3700.DATA2',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_3\SR3700.DATA3',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_4\SR3700.DATA4',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_5\SR3700.DATA5',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_6\SR3700.DATA6',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_7\SR3700.DATA7',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_8\SR3700.DATA8',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_9\SR3700.DATA9',
'E:\ORACLE\<SID>\SAPDATA1\SR3700_10\SR3700.DATA10',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_11\SR3700.DATA11',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_12\SR3700.DATA12',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_13\SR3700.DATA13',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_14\SR3700.DATA14',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_15\SR3700.DATA15',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_16\SR3700.DATA16',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_17\SR3700.DATA17',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_18\SR3700.DATA18',
'E:\ORACLE\<SID>\SAPDATA2\SR3700_19\SR3700.DATA19',
'F:\ORACLE\<SID>\SAPDATA3\SR3700_20\SR3700.DATA20',
'F:\ORACLE\<SID>\SAPDATA3\SR3700_21\SR3700.DATA21',
'F:\ORACLE\<SID>\SAPDATA3\SR3700_22\SR3700.DATA22',
'F:\ORACLE\<SID>\SAPDATA3\SR3700_23\SR3700.DATA23',
'F:\ORACLE\<SID>\SAPDATA3\SR3700_24\SR3700.DATA24',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_25\SR3700.DATA25',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_26\SR3700.DATA26',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_27\SR3700.DATA27',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_28\SR3700.DATA28',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_29\SR3700.DATA29',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_30\SR3700.DATA30',
'F:\ORACLE\<SID>\SAPDATA4\SR3700_31\SR3700.DATA31',
'E:\ORACLE\<SID>\SAPDATA1\SR3USR_1\SR3USR.DATA1'
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
'F:\ORACLE\<SID>\SAPDATA4\TEMP_1\TEMP.DATA1'
SIZE 350M REUSE AUTOEXTEND OFF;

Please confirm the format of control file is correct or not. if i miss something please let me know.

The following files which exists i have not included in the control,

Is it required to include any of one of following file into control.sql  additionallu?.

'F:\ORACLE\<SID>\SAPDATA4\sysaux_1

'F:\ORACLE\<SID>\SAPDATA4\sysaux_1

'F:\ORACLE\<SID>\SAPDATA4\Temp_1

'F:\ORACLE\<SID>\SAPDATA4\undo_1

Thanks
Thirumalai

[Former Member]

Hi Thirumalai,

A sample control.sql for reference:

--     Set #1. NORESETLOGS case

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- Additional logs may be required for media recovery of offline

-- Use this only if the current versions of all online logs are

-- available.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SID" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 (
    '/oracle/SID/origlogA/log_g11m1.dbf',
    '/oracle/SID/mirrlogA/log_g11m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 2 (
    '/oracle/SID/origlogB/log_g12m1.dbf',
    '/oracle/SID/mirrlogB/log_g12m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 3 (
    '/oracle/SID/origlogA/log_g13m1.dbf',
    '/oracle/SID/mirrlogA/log_g13m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 4 (
    '/oracle/SID/origlogB/log_g14m1.dbf',
    '/oracle/SID/mirrlogB/log_g14m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024

DATAFILE
  '/oracle/SID/sapdata1/system_1/system.data1',
  '/oracle/SID/sapdata1/sysaux_1/sysaux.data1',
  '/oracle/SID/sapdata1/undo_1/undo.data1',
  '/oracle/SID/sapdata2/sr3db_1/sr3db.data1',
  '/oracle/SID/sapdata2/sr3db_2/sr3db.data2'
CHARACTER SET UTF8
;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 28 DAYS');
RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/SID/sapdata1/temp_1/temp.data1'
     SIZE 2048M REUSE AUTOEXTEND ON NEXT 262144000  MAXSIZE 8192M;

--     Set #2. RESETLOGS case

-- The following commands will create a new control file and use it

-- to open the database.

-- Data used by Recovery Manager will be lost.

-- The contents of online logs will be lost and all backups will

-- be invalidated. Use this only if online logs are damaged.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SID" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 1 (
    '/oracle/SID/origlogA/log_g11m1.dbf',
    '/oracle/SID/mirrlogA/log_g11m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 2 (
    '/oracle/SID/origlogB/log_g12m1.dbf',
    '/oracle/SID/mirrlogB/log_g12m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 3 (
    '/oracle/SID/origlogA/log_g13m1.dbf',
    '/oracle/SID/mirrlogA/log_g13m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 4 (
    '/oracle/SID/origlogB/log_g14m1.dbf',
    '/oracle/SID/mirrlogB/log_g14m2.dbf'
  ) SIZE 500M BLOCKSIZE 1024
-- STANDBY LOGFILE

DATAFILE
  '/oracle/SID/sapdata1/system_1/system.data1',
  '/oracle/SID/sapdata1/sysaux_1/sysaux.data1',
  '/oracle/SID/sapdata1/undo_1/undo.data1',
  '/oracle/SID/sapdata2/sr3db_1/sr3db.data1',
  '/oracle/SID/sapdata2/sr3db_2/sr3db.data2'
CHARACTER SET UTF8
;
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','OFF');
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 28 DAYS');
RECOVER DATABASE USING BACKUP CONTROLFILE
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/SID/sapdata1/temp_1/temp.data1'
     SIZE 2048M REUSE AUTOEXTEND ON NEXT 262144000  MAXSIZE 8192M;

I think you should go for RESETLOGS case.

Regards,

Sonia

[Former Member]

Let's continue to the process step by step;

It seems that the control files are already exist on your system. So, you are planning to restore on to the same system, isn't it correct? If it is true and you created all the missing directories such as saparch, oraarch, sapbackup and apply the steps below, before you startup the database with "_ALLOW_RESETLOGS_CORRUPTION=TRUE" parameter;

As I understand that you saved mirrlog files on the system. So, you can apply the steps, below;

SQL>shutdown

SQL>startup mount

SQL>recover database using backup controlfile until cancel

When the system ask for the offline redolog file, enter MirrlogA at the first step. Then enter MirrlogB as a second file with full path.

Please find the example, below;

ORA-00279: change 5398973414 generated at 05/30/2012 07:01:00 needed for thread1
ORA-00289: suggestion : D:\ORACLE\PRD\ORAARCH\PRDARCH1_3192_748344841.DBF
ORA-00280: change 5398973414 for thread 1 is in sequence #3192

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

D:\oracle\PRD\mirrlogA\LOG_G11M2.DBF

Then

D:\oracle\PRD\mirrlogB\LOG_G12M2.DBF

Then

D:\oracle\PRD\mirrlogA\LOG_G13M2.DBF

Then

D:\oracle\PRD\mirrlogB\LOG_G14M2.DBF

...

Apply mirrlog files. After that execute, the command;

SQL>alter system open resetlogs;

If the procedure succeded, we can move forward.

Additionally, DON'T use "_ALLOW_RESETLOGS_CORRUPTION=TRUE" parameter without advanced know-how.

Best regards,

Orkun Gedik

[Former Member]

Hi Ruchit,

It seems that the control files are already exist on your system.?

Ans:No. After system crash, received the  USB HDD drive hardware vendor in  which contains files in drives C, E& F. Oracle data files exits in E and F drive. I copied files from USB HDD drive to our same system as similar E drive and f drive.

Accordinfg to the folder structure I have created a control.sql my own and adopted it.

So, you are planning to restore on to the same system, isn't it correct?

Ans: yes same system with HDD replaced [new one].

<I have USB HDD drive backup files separately which received from vendor>

Sure, let me follow the steps and will update you..

Thanks

Thirumalai.

[Former Member]

Hello Thiru,

You didn't get what I meant. Control files exist because the control.sql that you created (using sample from system copy guide) would have the statement "Create Controlfile ......" after STARTUP NOMOUNT. Please check it. So  when SAPinst executed control.sql it created the control files for you... Otherwise database won't get mounted which it already has !!

Regards.

Ruchit.

[Former Member]

Ok, if you have control files, you can copy to their original locations. Because of the directory structure and the database name is the same, you don't need to recreate controlfiles from the scratch.

So, I am waiting for your feedback about the result.

Good luck and best regards,

Orkun Gedik

[Former Member]

Hi Ruchit, Gedik,

I reran the Sapinst again,

Received following error messaage in the log file <ora_sql_results.log>,

Step:Backup/restore database [sapinst]

================================================================================
2012-05-30, 18:43:23 SAPINST ORACLE start logging for
CONNECT / AS SYSDBA
SHUTDOWN ABORT
@CONTROL.SQL
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE
EXIT

exit;
Output of SQL executing program:
SQL*Plus: Release 10.2.0.4.0 - Production on Wed May 30 18:43:24 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to an idle instance.
Connected to an idle instance.
ORACLE instance shut down.
SP2-0734: unknown command beginning "==========..." - rest of line ignored.
SP2-0042: unknown command "==========" - rest of line ignored.
SP2-0734: unknown command beginning "Datenverar..." - rest of line ignored.
SP2-0734: unknown command beginning "==========..." - rest of line ignored.
SP2-0042: unknown command "==========" - rest of line ignored.
Connected to an idle instance.
ORACLE instance started.
Total System Global Area  775946240 bytes
Fixed Size      1299120 bytes
Variable Size    448793936 bytes
Database Buffers   322961408 bytes
Redo Buffers      2891776 bytes
Control file created.
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORACLE\PRD\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE
*
ERROR at line 1:
ORA-01109: database not open
otal System Global Area  775946240 bytes
Fixed Size      1299120 bytes
Variable Size    448793936 bytes
Database Buffers   322961408 bytes
Redo Buffers      2891776 bytes
Database mounted.
Database altered.
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-01109: database not open

shall I run ALTER DATABASE OPEN RESETLOGS;

Thanks

Thirumalai

[Former Member]

Hi Ruchit, Gedik,

SQL> ALTER DATABASE OPEN RESETLOGS;

ALTER DATABASE OPEN RESETLOGS

*

ERROR at line 1:

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

ORA-01110: data file 1: 'F:\ORACLE\PRD\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

Thanks

Thirumalai

[Former Member]

Hi Thirumalai,

The database needs to be recovered. Could you read my previous message with the following statement, please?

SQL> recover database using backup controlfile until cancel

Best regards,

Orkun Gedik

[Former Member]

Hello Thiru,

Since this is an online backup recovery is must and for that you need archivelogs which you don't have !!

So basically your system is finished unless you try to corruption mode option and it works for you.

Regards

Ruchit.

[Former Member]

Hi Ruchit, Thank for your info.

Hi Gedik,

I applied the mirror log A and B as you mentioned.

'F:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF' =?> failed.

So I applied  F:\oracle\PRD\mirrlogA\LOG_G13M2.DBF again, now it says

Media recovery complete.

SQL> recover database using backup controlfile until cancel
ORA-00279: change 547166850 generated at 12/01/2011 20:23:23 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC18743_0673549770.001
ORA-00280: change 547166850 for thread 1 is in sequence #18743

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\PRD\mirrlogA\LOG_G11M2.DBF
ORA-00310: archived log contains sequence 18741; sequence 18743 required
ORA-00334: archived log: 'F:\ORACLE\PRD\MIRRLOGA\LOG_G11M2.DBF'

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: 'F:\ORACLE\PRD\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

SQL> recover database using backup controlfile until cancel
ORA-00279: change 547166850 generated at 12/01/2011 20:23:23 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC18743_0673549770.001
ORA-00280: change 547166850 for thread 1 is in sequence #18743

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\PRD\mirrlogB\LOG_G12M2.DBF
ORA-00310: archived log contains sequence 18742; sequence 18743 required
ORA-00334: archived log: 'F:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF'

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: 'F:\ORACLE\PRD\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

SQL> recover database using backup controlfile until cancel
ORA-00279: change 547166850 generated at 12/01/2011 20:23:23 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC18743_0673549770.001
ORA-00280: change 547166850 for thread 1 is in sequence #18743

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\PRD\mirrlogA\LOG_G13M2.DBF
Log applied.
Media recovery complete.
SQL>
SQL> recover database using backup controlfile until cancel
ORA-00279: change 547169911 generated at 12/01/2011 20:47:56 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC18743_0673549770.001
ORA-00280: change 547169911 for thread 1 is in sequence #18743

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\PRD\mirrlogB\LOG_G14M2.DBF
ORA-00310: archived log contains sequence 18740; sequence 18743 required
ORA-00334: archived log: 'F:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF'

SQL> recover database using backup controlfile until cancel
ORA-00279: change 547169911 generated at 12/01/2011 20:47:56 needed for thread
1
ORA-00289: suggestion : E:\ORACLE\PRD\ORAARCH\PRDARCHARC18743_0673549770.001
ORA-00280: change 547169911 for thread 1 is in sequence #18743

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\PRD\mirrlogA\LOG_G13M2.DBF
Log applied.
Media recovery complete.
SQL> alter system open resetlogs;
alter system open resetlogs
             *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL>

Thanks

thirumlalai

[Former Member]

Ok. Could you paste the trace file under "/oracle/<DBSID>/saptrace/usertrace", regarding the error?

Best regards,

Orkun Gedik

[Former Member]

Hi Gedik,

I dont find the error in the user trace last updated one by sort time wise.

previous 5th file has error message as below;

Dump file e:\oracle\PRD\saptrace\usertrace\PRD_ora_1456.trc
Wed May 30 19:54:39 2012
ORACLE V10.2.0.4.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:3373M/4094M, Ph+PgF:4696M/5975M, VA:1142M/2047M
Instance name: PRD

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: 1456, image: ORACLE.EXE (SHAD)

*** ACTION NAME:() 2012-05-30 19:54:39.828
*** MODULE NAME:(sqlplus.exe) 2012-05-30 19:54:39.828
*** SERVICE NAME:() 2012-05-30 19:54:39.828
*** SESSION ID:(85.3) 2012-05-30 19:54:39.828
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 19211 (50% x 38423)
Successfully allocated 7 recovery slaves
Using 20 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 547169914 logseq 1 block 2
*** 2012-05-30 19:54:41.406
Media Recovery add redo thread 1
*** 2012-05-30 19:54:49.609
Media Recovery Log F:\oracle\PRD\mirrlogB\LOG_G14M2.DBF
*** 2012-05-30 19:54:49.656
Media Recovery drop redo thread 1
*** 2012-05-30 20:01:47.609
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 19211 (50% x 38423)
Successfully allocated 7 recovery slaves
Using 20 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 547169914 logseq 1 block 2
*** 2012-05-30 20:01:49.187
Media Recovery add redo thread 1
*** 2012-05-30 20:01:57.343
Media Recovery Log F:\oracle\PRD\mirrlogA\LOG_G11M2.DBF
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 0Kb in 9.27s => 0.00 Mb/sec
Total physical reads: 4096Kb
Longest record: 0Kb, moves: 0/1 (0%)
Change moves: 0/1 (0%), moved: 0Mb
Longest LWN: 0Kb, moves: 0/1 (0%), moved: 0Mb
Last redo scn: 0x0000.209d267b (547169915)
----------------------------------------------
*** 2012-05-30 20:01:58.453
Media Recovery drop redo thread 1
File 1 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 4 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 5 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 6 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 7 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 8 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 9 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 10 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 11 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 12 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 13 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 14 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 15 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 16 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 17 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 18 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 19 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 20 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 21 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 22 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 23 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 24 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 25 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 26 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 27 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 28 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 29 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 30 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 31 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 32 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 33 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 34 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 35 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 36 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 37 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 38 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 39 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 40 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 41 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 42 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 43 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 44 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 45 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 46 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 47 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 48 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 49 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 50 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 51 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 52 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 53 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 54 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 55 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 56 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 57 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 58 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 59 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 60 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 61 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 62 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 63 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 64 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 65 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 66 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 67 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 69 (stop scn 547169916) completed recovery at checkpoint scn 547169916
File 70 (stop scn 547169916) completed recovery at checkpoint scn 547169916
*** 2012-05-30 20:02:10.718
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 19211 (50% x 38423)
Successfully allocated 7 recovery slaves
Using 20 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 547169916 logseq 1 block 3
*** 2012-05-30 20:02:12.343
Media Recovery add redo thread 1
*** 2012-05-30 20:02:24.718
Media Recovery Log F:\oracle\PRD\mirrlogB\LOG_G12M2.DBF
*** 2012-05-30 20:02:24.734
Media Recovery drop redo thread 1
*** 2012-05-30 20:02:40.828
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 19211 (50% x 38423)
Successfully allocated 7 recovery slaves
Using 20 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 547169916 logseq 1 block 3
*** 2012-05-30 20:02:42.328
Media Recovery add redo thread 1
*** 2012-05-30 20:02:48.296
Media Recovery Log F:\oracle\PRD\mirrlogA\LOG_G13M2.DBF
*** 2012-05-30 20:02:48.328
Media Recovery drop redo thread 1
*** 2012-05-30 20:03:21.312
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 19211 (50% x 38423)
Successfully allocated 7 recovery slaves
Using 20 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 547169916 logseq 1 block 3
*** 2012-05-30 20:03:22.937
Media Recovery add redo thread 1
*** 2012-05-30 20:03:27.046
Media Recovery Log F:\oracle\PRD\mirrlogB\LOG_G14M2.DBF
*** 2012-05-30 20:03:27.078
Media Recovery drop redo thread 1
Prior to RESETLOGS processing...
ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE start
*** 2012-05-30 20:03:34.062 3950 kcrr.c
Archiving online redo logfile F:\ORACLE\PRD\MIRRLOGA\LOG_G11M2.DBF
******************************************************
WARNING: Archival will be performed using 2 passes.
The first pass will attempt to determine the end-of-file
of the online redo logfile.  The end-of-file is determined
by identifying what is described as a "corrupt" block
header.  This will be reported as an ORA-00354 error.
However, this is not really a corrupt block - it is the
end of the redo data.
The second pass will then archive the online redo logfile
using the identified end-of-file information.
******************************************************
Identified logfile 1 as thread 1 sequence 1
Logfile 1 is current
... NOSWITCH ARCH archival in progress
... FINAL ARCH archival in progress
Identified logical end-of-file at block# 2 SCN 547169915
*** 2012-05-30 20:03:34.421 9802 kcrr.c
*** 2012-05-30 20:03:34.421 9824 kcrr.c
Changing kcrfhnab from 102401 to 3
*** 2012-05-30 20:03:35.031 3950 kcrr.c
Archiving online redo logfile F:\ORACLE\PRD\MIRRLOGB\LOG_G12M2.DBF
******************************************************
WARNING: Archival will be performed using 2 passes.
The first pass will attempt to determine the end-of-file
of the online redo logfile.  The end-of-file is determined
by identifying what is described as a "corrupt" block
header.  This will be reported as an ORA-00354 error.
However, this is not really a corrupt block - it is the
end of the redo data.
The second pass will then archive the online redo logfile
using the identified end-of-file information.
******************************************************
Logfile 2 is not used
*** 2012-05-30 20:03:35.046 3950 kcrr.c
Archiving online redo logfile F:\ORACLE\PRD\MIRRLOGA\LOG_G13M2.DBF
******************************************************
WARNING: Archival will be performed using 2 passes.
The first pass will attempt to determine the end-of-file
of the online redo logfile.  The end-of-file is determined
by identifying what is described as a "corrupt" block
header.  This will be reported as an ORA-00354 error.
However, this is not really a corrupt block - it is the
end of the redo data.
The second pass will then archive the online redo logfile
using the identified end-of-file information.
******************************************************
Logfile 3 is not used
*** 2012-05-30 20:03:35.078 3950 kcrr.c
Archiving online redo logfile F:\ORACLE\PRD\MIRRLOGB\LOG_G14M2.DBF
******************************************************
WARNING: Archival will be performed using 2 passes.
The first pass will attempt to determine the end-of-file
of the online redo logfile.  The end-of-file is determined
by identifying what is described as a "corrupt" block
header.  This will be reported as an ORA-00354 error.
However, this is not really a corrupt block - it is the
end of the redo data.
The second pass will then archive the online redo logfile
using the identified end-of-file information.
******************************************************
Logfile 4 is not used
ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE complete
*** 2012-05-30 20:03:35.421
Thread 1: Sequence reset to 1.
*** 2012-05-30 20:03:49.359
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option

last file in user trace:"/oracle/<DBSID>/saptrace/usertrace",

   PRD_ora_1296.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:3404M/4094M, Ph+PgF:4714M/5975M, VA:1172M/2047M
Instance name: srh

Redo thread mounted by this instance: 0 <none>

Oracle process number: 0

Windows thread id: 1296, image: ORACLE.EXE (SHAD)

Dynamic strand is set to TRUE
Running with 2 shared and 0 private strand(s). Zero-copy redo is FALSE

Thanks

Thirumalai

[Former Member]

Hi Gedik,

SQL>

ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'F:\ORACLE\PRD\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

SQL>

Thanks

Thirumalai

[Former Member]

Hi,

>> ORA-00704: bootstrap process failure

>> ORA-39700: database must be opened with UPGRADE option

Did you face with the system crash during the Oracle upgrade?

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

>> ORA-01110: data file 1: 'F:\ORACLE\PRD\SAPDATA4\SYSTEM_1\SYSTEM.DATA1'

Did you applied all the mirrlogs until you see the "Media recovery complete" message. If you see this message, try to execute "alter database open resetlogs;". If you face with the "ORA-01092: ORACLE instance terminated. Disconnection forced" check the traces again for the last error. And paste it here

Best regards,

Orkun Gedik

[Former Member]

Hi Gedik,

Media recovery completed now with in first mirrorlog file.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
F:\oracle\SRH\mirrlogA\LOG_G11M2.DBF
Log applied.
Media recovery complete.
SQL>

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

<trace Error>

*** 2012-05-31 11:26:48.796

Prior to RESETLOGS processing...

ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE start

*** 2012-05-31 11:26:48.796 3950 kcrr.c

Archiving online redo logfile F:\ORACLE\SRH\MIRRLOGA\LOG_G11M2.DBF

******************************************************

WARNING: Archival will be performed using 2 passes.

The first pass will attempt to determine the end-of-file

of the online redo logfile.  The end-of-file is determined

by identifying what is described as a "corrupt" block

header.  This will be reported as an ORA-00354 error.

However, this is not really a corrupt block - it is the

end of the redo data.

The second pass will then archive the online redo logfile

using the identified end-of-file information.

******************************************************

Identified logfile 1 as thread 1 sequence 1

Logfile 1 is current

... NOSWITCH ARCH archival in progress

... FINAL ARCH archival in progress

Identified logical end-of-file at block# 2 SCN 547169923

*** 2012-05-31 11:26:49.156 9802 kcrr.c

*** 2012-05-31 11:26:49.156 9824 kcrr.c

Changing kcrfhnab from 102401 to 3

*** 2012-05-31 11:26:49.718 3950 kcrr.c

Archiving online redo logfile F:\ORACLE\SRH\MIRRLOGB\LOG_G12M2.DBF

******************************************************

WARNING: Archival will be performed using 2 passes.

The first pass will attempt to determine the end-of-file

of the online redo logfile.  The end-of-file is determined

by identifying what is described as a "corrupt" block

header.  This will be reported as an ORA-00354 error.

However, this is not really a corrupt block - it is the

end of the redo data.

The second pass will then archive the online redo logfile

using the identified end-of-file information.

******************************************************

Logfile 2 is not used

*** 2012-05-31 11:26:49.765 3950 kcrr.c

Archiving online redo logfile F:\ORACLE\SRH\MIRRLOGA\LOG_G13M2.DBF

******************************************************

WARNING: Archival will be performed using 2 passes.

The first pass will attempt to determine the end-of-file

of the online redo logfile.  The end-of-file is determined

by identifying what is described as a "corrupt" block

header.  This will be reported as an ORA-00354 error.

However, this is not really a corrupt block - it is the

end of the redo data.

The second pass will then archive the online redo logfile

using the identified end-of-file information.

******************************************************

Logfile 3 is not used

*** 2012-05-31 11:26:49.796 3950 kcrr.c

Archiving online redo logfile F:\ORACLE\SRH\MIRRLOGB\LOG_G14M2.DBF

******************************************************

WARNING: Archival will be performed using 2 passes.

The first pass will attempt to determine the end-of-file

of the online redo logfile.  The end-of-file is determined

by identifying what is described as a "corrupt" block

header.  This will be reported as an ORA-00354 error.

However, this is not really a corrupt block - it is the

end of the redo data.

The second pass will then archive the online redo logfile

using the identified end-of-file information.

******************************************************

Logfile 4 is not used

ALTER SYSTEM ARCHIVE LOG ALL USING BACKUP CONTROLFILE complete

*** 2012-05-31 11:26:50.187

Thread 1: Sequence reset to 1.

*** 2012-05-31 11:27:02.750

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Thanks

Thirumalai

[Former Member]

Ok. You need to apply all the mirrlog files in order to reach last consistent state, as I noted in my previous message. If you don't apply all of the log files, you will not be able to save last updates on the database. This is the first issue.

>> ORA-00704: bootstrap process failure

>> ORA-39700: database must be opened with UPGRADE option

Apply the procedure with the following sequence;

SQL> STARTUP UPGRADE

SQL> @?/rdbms/admin/catupgrd.sql

SQL> SHUTDOWN

SQL> STARTUP MOUNT

SQL> ALTER DATABASE OPEN RESETLOGS;

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

I am performing a system copy of EHP7 NW 7.4 system on AIX/Oracle 11.2.0.4.

I am using TSM backup to restore and recover the database no brbackup/brrestore used.

I have successfully recoevered the target DB and it is open now.

So after performing that step I continued with SAPInst and got the below error as SAPInst is still trying to open the CONTROL.SQL and create control files, can you let me know what needs to be done to skip this and proceed further as the database is already open...

Error:

An error occurred while
processing option SAP Business Suite 7i 2013 Support Release 1 > EHP7 for
SAP ERP 6.0 ABAP Support Release 1 > Oracle > System Copy > Target
System > Standard System > Based on AS ABAP > Standard System( Last
error reported by the step: SQL statement or script failed. DIAGNOSIS: Error
message: ORA-01503: CREATE CONTROLFILE failed ORA-00200: control file could not
be created ORA-00202: control file: '/oracle/DH2/origlogA/cntrl/cntrlDH2.dbf'
ORA-27038: created file already exists Additional information: 1 ORA-01507:
database not mounted ORACLE instance shut down. ORA-32004: obsolete or
deprecated parameter(s) specified for RDBMS instance ORACLE instance started.
Total System Global Area 1737105408 bytes Fixed Size 2247072 bytes Variable
Size 872416864 bytes Database Buffers 855638016 bytes Redo Buffers 6803456
bytes Database mounted. Database altered. Database altered. Database closed.
Database dismounted. ORACLE instance shut down. Disconnected from Oracle
Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the
Partitioning, OLAP, Data Mining and Real Application Testing options .
SOLUTION: For more information, see ora_sql_results.log

Please help

[former_member185239]

Hi Balaji,

Create a CONTROL.SQL file in sapinst directory.

touch CONTROL.SQL

inside the CONTROL.SQL type exit; (exit with a semicolon)

Retry the sapinst.

With Regards

Ashutosh Chaturvedi

[Former Member]

Thanks a lot Ashutosh I did find this fix already and impletemented it too.

[Former Member]

Hi,

Can you replace the last

ALTER DATABASE OPEN with

ALTER DATABASE OPEN RESETLOGS in your control.sql and retry.

Regards,

Sonia

[former_member189725]

Please provide some more details on the issue .

Did you restore an offline database backup or was the restore done ?

If its yes , just "Alter database open resetlogs"

If you are doing a restore from online backup , then make sure you provide the archived redo logs for recovery .

Its always best to use SAP defined method of using 'orabrcopy'.

You can use the below docs for reference:

Unix/Linux

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f055cbd0-97f1-2a10-c09a-907b811de...

Windows

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/5019eaab-afdd-2c10-61a1-ac50f784c...

Regards

Ratnajit

[Former Member]

Dear All,

I sorry for the late reply.

Please find the following answers of your questions:

Did you provide the location of control.sql while running the sapinst?

Ans:yes. Withe help of sample control.sql in the system copy guide.

what type of backup you have used?
Ans: File copy backup

I suggest you use "orabrcopy"
Ans: I donot have Source system.

Without the control.sql you wont be able to make your DB open. You need to execute ORADBCOPY in your source system and copy the script to your target installation directory as per the installation guide.

Ans:I donot have Source system.I created a control.sql file with help control file txt samples exists in the system copy guide "SAP NetWeaver 2004s Installation Guide".

Thanks & Regards

Thirumalai

[former_member189725]

You need to adapt the control.sql file as required .

Was the file copy taken when the DB was offline ??

If so , then follow the restore procedure and use control.sql to generate the control file.

Regards

Ratnajit

[Former Member]

Hi Thirumalai,

Since your database is mounted it means that the control file has got created. Now from this control file you can easily create a trace file !! or what people are referring to as control.sql. Create it and then makes changes.

This trace would have statements for <SID> change and control file creation itself. You need to remove the control file creation step while make sure the <SID> statement is using SET <DBSID> instead of reset <DBSID>. Make other adaptations and ensure there is an alter database open (resetlogs/no restlogs) in your trace script. Then place it in the location with the same name as you gave for control.sql in SAPINST. Then try with SAPinst. If it does't work then try it manually.

Alternatively the easier way to add alter database open in the existing control.sql.

However this wouldn't be much of a concern.

Regards.

Ruchit.

[Former Member]

Hi,

>> what type of backup you have used?

>> Ans: File copy backup

It means that you have a source system. First of all, did you executed "alter database begin backup;" statement, before you copy the datafiles from the source and end backup with

"alter database end backup;"? Or Is the system offline, when you are copying the datafiles? In both case, you have a source system. So, if the source is a SAP system, you can apply the system copy procedure.

If your source system is crashed and you have only a offline backup/online backup+online redologs, you can create the control files, as I noted in my previous message.

Best regards,

Orkun Gedik

[Former Member]

Additionally, check the thread http://scn.sap.com/thread/1966832

We discussed this issues many times, before

Best regards,

Orkun Gedik

[Former Member]

Hi Thirumalai,

You can create control files, by using "orabrcopy". Follow the system copy guide;

https://service.sap.com/~sapidb/011000358700000416392008E.pdf

Or by the statement: "alter database backup controlfile to trace resetlogs;". Go to usertrace directory and open the trace file. Take the statement starting with "CREATE CONTROLFILE" and modify for your requirements.

I suggest you use "orabrcopy"

Best regards,

Orkun Gedik

[JPReyes]

I Agree with Orkun,

Without the control.sql you wont be able to make your DB open. You need to execute ORADBCOPY in your source system and copy the script to your target installation directory as per the installation guide.

Regards, Juan

[Former Member]

Hi Thirumalai,

what type of backup you have used?

Regards

Roman

[Former Member]

Hi,

At what stage is the error displayed.

Did you provide the location of control.sql while running the sapinst?

Regards,

Sonia