Error during a system copy in phase Backup/Restore database

[joo_migueldimas]

Hello,

First I have to say one thing...I hope this message is not eliminated this time as it had been the last I wrote!

Database: Oracle 11.2.0.1

O.S.: Linux RHEL X86_64

I´m facing with an error during a system copy (with copy datafiles method between source and target server) in phase 12 - "Backup/Restore database". I checked sapinst.log and YES I searched the error before write this message! I will post here the sapinst.log:

...
ERROR 2011-02-25 00:18:27.682
MUT-03025  Caught ESAPinstException in module call: .

ERROR 2011-02-25 00:18:28.951
FCO-00011  The step RunControlScript with step key |NW_ABAP_OneHost|ind|ind|ind|ind|0|0|NW_Onehost_System|ind|ind|ind|ind|onehost|0|NW_CreateDBandLoad|ind|ind|ind|ind|createdbandload|0|NW_CreateDB|ind|ind|ind|ind|createdb|0|NW_OraDBCheck|ind|ind|ind|ind|0|0|NW_OraDBMain|ind|ind|ind|ind|0|0|NW_OraDBOBR|ind|ind|ind|ind|obr|0|NW_OraOBR|ind|ind|ind|ind|5|0|RunControlScript was executed with status ERROR ( Last error reported by the step: SQL statement or script failed. DIAGNOSIS: Error message: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size		    2215464 bytes
Variable Size		 1174405592 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    4214784 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: '/oracle/W01/sapdata1/system_1/system.data1'


ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/W01/sapdata1/temp_1/temp.data1'
*
ERROR at line 1:
ORA-01109: database not open

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size		    2215464 bytes
Variable Size		 1174405592 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    4214784 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

Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
. SOLUTION: For more information, see ora_sql_results.log and the Oracle documentation.).

INFO 2011-02-25 00:18:30.555
Creating file /tmp/sapinst_instdir/NW04S/LM/COPY/ORA/SYSTEM/CENTRAL/AS-ABAP/__instana_tmp.xml.

Can you help me please?!

Joao Dimas - Portugal

[Former Member · Accepted solution]

Hi,

try this :

SQL>recover database using backup controlfile;

then it will show you similar like this :

ORA-00279: change 775140 generated at 05/09/2007 07:00:38 needed for thread 1
ORA-00289: suggestion : /u04/oracle/product/10.2.0/db_1/dbs/arch1_5_621627183.dbf
ORA-00280: change 775140 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

if you have complete sets of Oracle archivelogs (Oracle offline redologs), put it on it's original location (as stated on initSID.ora) then answer AUTO. Oracle will try to apply archivelog (Offline redolog).

if you are reaching the last archivelog (Oracle offline redologs) or no more archivelog (Oracle offline redologs) can be applied, type CANCEL.

until reached this point (similar like this) :

Log applied.
Media recovery complete.

then, try to bring the database to the OPEN state normally

SQL> alter database open;

if this is success, it is good.

but if you see this error :

ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

you have to use this command :

SQL> alter database open resetlogs;
Database altered.

it should be done.

hope it help you.

rgds,

Alfonsus Guritno

[Former Member · Accepted solution]

Hi,

If you have not got the offline redo logs, try using the online redo's. E.G OriglogA and 'B'

So, as Alfonsus has detailed with recover wth backup controfile when asked for the specific change, put in the path to the Origlogs with the *.dbf file and enter.

E.G /<oracle_path>/OriglogA/<dbf filename> and enter.

Continue through the sets until the recovery is completed. Hopefully!

Mark

[joo_migueldimas · Accepted solution]

Hello Alfonsus Guritno,

Thanks for your help... but everything you said, I had already done what you said two days ago ... but without success as you can see below... what I did was:

The starting point was this:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

After I used, as you said also, the following command:

SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/W01/sapdata1/system_1/system.data1'

So... after the previous error I tried to recover database with the command:

SQL>recover database using backup controlfile;

ORA-00279: change 47119128 generated at 02/24/2011 22:01:43 needed for thread 1
ORA-00289: suggestion : /oracle/W01/oraarch/W01arch1_1_743983147.dbf
ORA-00280: change 47119128 for thread 1 is in sequence #1


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

The problem now is this... I don´t have that archive file!! The source system (D01) it is in state of NOArchivelogs!

As I said in first message here, I used the oracle specific method, I stopped the source system (D01), after I generated the control.sql file and after this I stopped the database of D01 and I transfer all the datafiles and relogs and all other files that is mentioned in system copy guide at chapter "Restoring the Database Files on the Target System Manually" I copied all that directories from source (D01) to the target system (W01) by SCP protocol!

So... after all this procedure... I don´t know why this error happened because I did everything that is mentioned in guide/notes! Why it needed an archive log file if I didn´t used the backup online procedure! The source database was STOPPED when I transfer the datafiles!

Ahh other thing... when I tried to use the noresetlogs it show the following error:

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open

So... it will not let me choose another option!

Kind regards

João Dimas - Portugal

[mohammed_anish · Accepted solution]

Hi,

Came across a similar problem as you are having while trying to open the database. Have look at the following link..

http://dbakerber.wordpress.com/2008/12/15/oracle-support/

Thought it might help you. Good luck.

Regards,

Anish

Edited by: Mohammed Anish on Feb 28, 2011 3:19 PM

[mohammed_anish]

Hi,

To resolve the error, all that the database needs are some archive logs to be applied to get all the files in a consistent state.If the backup you used to restore is an 'Online backuo", you would have taken backup of the archived logs also right after the online backup.

You can find out from the source system, which one was the last archive log it backed up from DB12 logs. After you find the sequence number (archive log number), restore/copy those archive logs on the target system. and issue the command

sql> RECOVER DATABASE UNTIL SEQUENCE <log sequence number>;

once this is complete, you can open the database without any problems. This is the method I used when i had an issue in system copy.

Regards,

Anish

[Former Member]

1. shutdown oracle --> shutdown immeidate

2. startup nomount

3. alter database mount

It will give u a media recovery erro

4. RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

5.alter database open resetlogs;

If the above doesn't work or if it is complaining about a datafile reocvery then issue

After stope 3 -->

1. alter database end backup;

2. recover datafile datafile name

3. alter database open resetlogs

Regards

Srikanth M.

[Former Member]

As you have oracle I would suggest you to use orabrcopy tool to prepare control.sql file and move it to sapinst dir as suggested in the system copy guide.

[joo_migueldimas]

Hi,

I had already used that tool in first phase of preparation before run the sapinst wizard!

After I moved the datafiles from one server to another I ran the sapinst and now I ´m facing with this error!

...
ERROR 2011-02-26 18:28:37.129
FCO-00011  The step RunControlScript with step key |NW_ABAP_OneHost|ind|ind|ind|ind|0|0|NW_Onehost_System|ind|ind|ind|ind|onehost|0|NW_CreateDBandLoad|ind|ind|ind|ind|createdbandload|0|NW_CreateDB|ind|ind|ind|ind|createdb|0|NW_OraDBCheck|ind|ind|ind|ind|0|0|NW_OraDBMain|ind|ind|ind|ind|0|0|NW_OraDBOBR|ind|ind|ind|ind|obr|0|NW_OraOBR|ind|ind|ind|ind|5|0|RunControlScript was executed with status ERROR ( Last error reported by the step: SQL statement or script failed. DIAGNOSIS: Error message: ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size		    2215464 bytes
Variable Size		 1174405592 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    4214784 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: '/oracle/W01/sapdata1/system_1/system.data1'


ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/W01/sapdata1/temp_1/temp.data1'
*
ERROR at line 1:
ORA-01109: database not open

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size		    2215464 bytes
Variable Size		 1174405592 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    4214784 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

Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
. SOLUTION: For more information, see ora_sql_results.log and the Oracle documentation.).

INFO 2011-02-26 18:28:38.175
Creating file /tmp/sapinst_instdir/NW04S/LM/COPY/ORA/SYSTEM/CENTRAL/AS-ABAP/__instana_tmp.xml.

I don´t know how to solve this...

Kind regards,

João Dimas - Portugal

[Former Member]

Hi João Dimas,

Few questions...

1. Which backup you are using ONLINE or OFFLINE ?

2. Have you also restored archive logs ?

Normaly you restore and recover database using controlfile. Sometime it require archive logs for complete recovery or using CANCEL clause in recovery syntax. If you try to recover database normaly and it doesn't find require archive log, it will through this error "ORA-01194: file 1 needs more recovery to be consistent" for "system.data1".

I would suggest, recover database in this case manually.

SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Provide archive logs (you can use AUTO option)

After applying all available archive logs, press CANCEL;

It should show you message "Recovery complete"

Then open database with RESETLOGS

SQL> ALTER DATABASE OPEN RESETLOGS;

It should open database.

Once done

Just create a file "CONTROL.SQL" in SAP temperory installation directory and add below lines.

STARTUP
EXIT

Then retry installation, it should solve your problem.

Regards.

Rajesh Narkhede

[joo_migueldimas]

Hi,

I checkd the file ora_sql_results and it show this:

...
2011-02-26, 18:27:53 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 11.2.0.1.0 Production on Sat Feb 26 18:27:58 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

Connected to an idle instance.
ORACLE instance shut down.
Connected to an idle instance.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size		    2215464 bytes
Variable Size		 1174405592 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    4214784 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: '/oracle/W01/sapdata1/system_1/system.data1'


ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/W01/sapdata1/temp_1/temp.data1'
*
ERROR at line 1:
ORA-01109: database not open

ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size		    2215464 bytes
Variable Size		 1174405592 bytes
Database Buffers	 1140850688 bytes
Redo Buffers		    4214784 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

Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SAPINST: End of output of SQL executing program /oracle/W01/112_64/bin/sqlplus.

SAPINST found errors.
SAPINST The current process environment may be found in sapinst_ora_environment.log.

2011-02-26, 18:28:36 SAPINST ORACLE stop logging

Can you help me please?

Thank you,

João Dimas - Portugal

[Former Member]

Hi,

1. 1. startup database on mount state #

SQL>startup mount

ORACLE instance started.

Total System Global Area ... bytes

Fixed Size ... bytes

Variable Size ... bytes

Database Buffers ... bytes

Redo Buffers ... bytes

Database mounted.

1. 2. recover datafile #

SQL>recover datafile '/oracle/W01/sapdata1/system_1/system.data1';

Media recovery complete.

#3. bring your database to the state open #

SQL>alter database open;

if on this state database cannot be altered to open state, because of another datafile need to be recovered, back to the step 2 above and after that goto step 3.

hope it help you.

rgds,

Alfonsus Guritno

[joo_migueldimas]

Hi Alfonsus Guritno,

I did what you recommended but when I did the step 2 it show me the following error:

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size                  2215464 bytes
Variable Size            1174405592 bytes
Database Buffers         1140850688 bytes
Redo Buffers                4214784 bytes
Database mounted.


SQL> recover datafile '/oracle/W01/sapdata1/system_1/system.data1';
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Can you help me please?

Thank you,

Joao Dimas - Portugal

[Former Member]

Hi,

In that case try my option....

Mark

[joo_migueldimas]

Hello Mark Norman,

I did exactly what you suggested and it works, but after shutdown the database and start it with mount.

First I recover the database using controlfile and I used the path oforiglogA/file and not the archivelog as it asked:

SQL> recover database using backup controlfile;

ORA-00279: change 47119128 generated at 02/24/2011 22:01:43 needed for
thread 1
ORA-00289: suggestion : /oracle/W01/oraarch/W01arch1_1_743983147.dbf
ORA-00280: change 47119128 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/W01/origlogA/log_g11m1.dbf
Log applied.
Media recovery complete.

After that, I run the command:

SQL> alter database open resetlogs;
Database altered

I performed a shutdown:
SQL> shutdown immediate;
...

And then I start the database in mount mode:

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 2321686528 bytes
Fixed Size 2215464 bytes
Variable Size 1174405592 bytes
Database Buffers 1140850688 bytes
Redo Buffers 4214784 bytes
Database mounted.

At that point, I typed "retry" the sapinst and this error disappeared!

But now I have another error at phase 18 - Perform post-load activities

I will open another post, if it is justified.

Thank you very mutch for your help!

Best regards,

João Dimas - Portugal