Oracle System Move/Copy Question

[Former Member]

I'm using HP-UX/Oracle/ECC 6.0 and doing a system copy/build using an Online backup

We have one unused server where someone previously installed oracle/SAP <SID> = QAS. No one uses this server but SAP starts fine there.

I was told to wipe out this QAS server and install DEV over it using a copy of the DEV server <SID> = DEV . Basically we are moving our dev server.

What I did was as follows:

1)Uninstall SAP instances on QAS server using sapinst. Deleted all QAS datafiles and log files from OS level (we no longer need QAS database). Oracle binaries and init*.ora/sap files do exist and I kept them along with the SAP kernels in order to use brtools, sqlplus etc.

2) Next I took a full online backup + archive log backups of DEV database. And also took a JAVA Export to use during SAP install. Yes its a dual stack.

3) Restored DEV backup over QAS server using brrestore. Renamed all data and log file folders to DEV and Restoration was successful

4) Controlfile creation was also successful - target <SID> set to "DEV"

The idea is to recover the database with DEV and then reinstall SAP. I am trying to avoid a complte Oracle reinstall from scratch but thats the last option.

Question 1> Is this the right approach ? I know this works for sql server but not sure in Oracle as there may be older <SID> QAS -- still hanging there somewhere. I already saw QAS in the listener.ora and listener wont start.

Next I did the following as per the document:

SQL> recover database using backup controlfile until cancel;

I am having trouble doing a recovery even after I applied over 150 archive log files from START of backup until current time. Did not apply online redo logs. Database will mount but not open. Did " alter database open resetlogs" but getting error "file 1 needs more recovery to be consistent". Also did a ALLOWRESETLOGS_CORRUPTION=TRUE in init.ora file but that did not help. I did rename the init* files to DEV.

Question 2> Assuming this is the right approach how do I get over this recovery failure issue ? I do have several DEV backups available both Online and offline and can wipe out the data and restore again if needed.

[Former Member]

> Question 2> Assuming this is the right approach how do I get over this recovery failure issue ? I do have several DEV backups available both Online and offline and can wipe out the data and restore again if needed.

Hello,

If you do not have any compulsion of point in time restore (i.e. if do not have compulsion of real time status of target by applying redo logs) then I would suggest you proceed for another restore from the 'offline' backup and do not apply the redo logs then. This is much easier way to carry out the things.

This is when you need the target system data only upto the time of source system's offline backup.

Overall approach is right,

1. Restore the ABAP stack

2. Restore the Java stack using export/import method.

However, once if you can go through the standard system copy guide then you will get more clear picture.

Thanks

[Former Member]

Thank You. I'll try with the offline backup next and yes you are right I don't care about the point in time recovery. Just need it to recover somehow. Have a few more questions:

1) On the target the old SID - QAS still exists in all the *.ora and *.sap files like listener.ora, init.ora etc..I only modified the init<SID>.sap to the target SID just to use brrestore but not the rest. How does that get changed to the new SID - DEV ? During recovery or during SAP install ? How about all the Oracle environment variables..Do I have to Uninstall Oracle here ? I'm worried there are old SIDs hanging out there somewhere. Is there a list of places ? I'd rather do the changes myself than uninstall and reinstall.

2) Second question is after the restore is done (offline or online) I create the controlfile. Now do you do "alter database open resetlogs" after or before you do the "recover database using backup controlfile" ?

[Former Member]

Hello,

1. After the restore, next high level task is to start the Listener (apart from other db related tasks in a system copy). So, you need to verify and modify tnsnames.ora and listener.ora in directory $ORACLE_HOME/network/admin with new SID.

Rest of the files related to Oracle environment, you need to achieve that update by updating the environment files in the 'home' directory of orasid user. For e.g. .cshrc, .dbenv.sh, .dbenv.csh, .sapenv.csh etc.

Note that you need to execute ls -latr to list those files in /home/orasid

2. "recover database using backup controlfile" step is needed only when you want to apply Redo logs.

So if you want to apply redo logs, then "recover database using backup controlfile" step should be executed before 'Opening' the database i.e. before "alter database open resetlogs".

In case of online backup restore, it is mandatory to recover the database with 'some' redo logs but in case of offline bakcup if you go to apply redo logs then you can 'cancel' it just after the command "recover database using backup controlfile" (in case of offline backup, option 'CANCEL' can be executed just after "recover database using backup controlfile" to stop applying the redo logs - it is just similar to the case when you don't 'want' to apply the logs)

However, in case of offline backup restore you can just eliminate the step "recover database using backup controlfile" and go directly with "alter database open resetlogs".

Thanks

[Former Member]

Great. I'll try today with last nights backup and let you know what happens.

Edited by: Basab Biswas on Dec 14, 2011 4:34 PM

[Former Member]

Hello

After a fresh restore using offline backup

-Started the listener.

-startup nomount

-created controlfile

success

alter database open resetlogs;

media file 1 needs recovery

This is weird. I just did a full restore with an offline backup. The backup is good as it was used earlier to restore another database and was working perfectly fine.

Am I missing any steps ? The target <SID> is same as source SID. Just server is different

[Former Member]

Hello,

Is ULIMIT for orasid and sidadm UNLIMITED ? If not, try to set it to unlimited for once.

What is the full error message you get when you do 'alter database open resetlogs;' ?

After a fresh restore using offline backup

-Started the listener.

-startup nomount

-created controlfile

success

Now after creation of controlfile, do this -

1. shutdown

2. set autorecovery off

3. startup mount

3. recover database using backup controlfile until cancel;

When it asks the 'response', type CANCEL and enter. This is to cancel the recovery via redo logs.

4. Now do, alter database open resetlogs;

Thanks

[Former Member]

Thanks

I forgot to mention one important thing:

The target oracle was at 10.2.0.1 whereas the source is at 10.2.0.2

This is interesting:

Right after restoring/copying the source database (10.2.0.2) on the target(10.2.0.1) it said database needs to open in upgrade mode. That makes sense.

-So I applied patch 10.2.0.5 on target (I could not find patch 10.2.0.2)

-Ran catupgrd script and ran other scripts based on readme.htm

-Upgrade successful -10.2.0.5

-restart database

-create controlfile -success

-alter database open resetlogs - success

everything was fine and database was open and I could query the SAP tables from SQL

Now I ran sapinst and it messed up the control file

-I restored again using the same OFFLINE backup fro source (10.2.0.2) on the target (now on 10.2.0.5)

-create controlfile - success

-alter database open resetlogs; - FAIL

[Former Member]

Hi

This is resolved now. The issue was due to different Oracle patches. But even with different patch levels the restore is possible by following the below steps:

1) Restore Database

2) start listener

3) startup upgrade and then run @?/rdbms/admin/catupgrd.sql (This step ensures that the system database is at same patch level as the oracle binaries) This upgrade info was getting deleted by the restore from source every time

4) create controlfile

5) shutdown and startup mount

6) alter database open resetlogs;

Thank you