Post System Copy - Applying Archive Logs

Hello,

We have just completed a successfully Homogenous system copy of our ECC 6.0 Production system, using a Oracle Backup-Restore method.

The story so far -

1. Whole offline DB Backup of PRD system on 09-March-2009.

2. PRD system up and running for end users on 10-March-2009

3. Backup restored on system PRDnew on 11-March-2009

4. testing of PRDnew completed on 12-March-2009.

Now, we need to apply the changes (archive logs) of 10th, 11th and 12th March of PRD to PRDnew.

I wanted to know, how should I go about it?

Our OS - AIX 5.3

Our DB - Oracle 10.2.0.2

Kindly advise.

SAP Managed Tags:
NW AS Java Administrator (NWA)

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

Gautam,

First you need to recover the database upto point in time, or specific SCN/LSN by using recover database command and finally open the database with open resetlogs or norestlogs.

I gather from your post that you are not using BRTOOLS.

Hope this helps.

Manoj

Edited by: Manoj Chintawar on Mar 12, 2009 9:04 AM

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hi Gautam,

Is the PRDnew database is opened using resetlogs ?? If yes,you cannot use the logs of PRD system as the resetlogs will change the logs numbers to start from 0.

Regards,

Kalyan

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

You mean the Archivelog mode?

Its set to No right now in PRDnew ....... No Archivelog

So should I change it to 'yes' and then apply the logs?

Hi Gautam,

I am not saying about the archive log mode.

First tell me whether the database is open or not.If its open how did you open it after restoring and recovering the backup.

By using ALTER DATABASE OPEN RESETLOGS

ALTER DATABASE OPEN NORESETLOGS

Regards,

Kalyan

Hello Kalyan,

We used alter database open resetlogs;

So, how do we proceed now?

Hi,

> We used alter database open resetlogs;

Since you used open resetlogs....the log sequence number will be changed i.e starts with 0 but the actual system will have sequence numbers different and so we cannot apply those systems logs to this system as the log sequence number is changed to 0.

Check the current sequence number using the command

SQL> select * from v$log;

Regards,

Kalyan

Hello kalyan,

This is what I get when I run your query -

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

--- -

FIRST_CHANGE# FIRST_TIME

1 1 693 52428800 2 YES INACTIVE

56055706 12-MAR-09

2 1 694 52428800 2 NO CURRENT

56079974 12-MAR-09

3 1 692 52428800 2 YES INACTIVE

56034584 12-MAR-09

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

--- -

FIRST_CHANGE# FIRST_TIME

4 1 691 52428800 2 YES INACTIVE

56016964 12-MAR-09

Could you please guide me with the entire process that needs to be done now?

Thanks.

Hi,

Is this the output in PRD system or PRDnew??

Regards,

Kalyan

sorry this is for PRDnew -

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

--- -

FIRST_CHANGE# FIRST_TIME

1 1 03 52428800 2 YES INACTIVE

7039531 12-MAR-09

2 1 04 52428800 2 NO CURRENT

7040772 12-MAR-09

3 1 02 52428800 2 YES INACTIVE

7038481 12-MAR-09

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS

--- -

FIRST_CHANGE# FIRST_TIME

4 1 01 52428800 2 YES INACTIVE

7037061 12-MAR-09

Hi,

You can see the sequence number on PRD are 692,693,694 and those on PRDnew are 02,03,04 which are changed since you opened Database with resetlogs.

Now you cannot apply those logs(692,693,694...) on PRDnew.

Finally as said earlier there is no way you can apply those logs to PRDnew.

Hope you got it clear.

Regards,

Kalyan

Gautam,

Here are the commands to use. You may need them in future. Sorry I did not write in detail in my last reply.

recover (automatic) database (one of the following option)

automatic will automatically applies archived and redo log files

Option until time 'yyyy-mm-dd:HH:MM:SS'

or cancel (manually)

or until change number

using backup controlfile

finally open the database with resetlogs

hope this helps.

Manoj Chintawar

Hi Manoj,

Gautam already opened the database with resetlogs option ...now how can he apply the logs

Regards,

Kalyan

Ok, so it appears we have screwed the restore.

So, this time I will restore with - alter database open noresetlogs;

So, in that the sequence number of PRDnew would also be 692,693,694.

Then what should I do? How should I apply 695, 696, etc....?

Could you provide the sql command for applying those.

Thanks.

Kalyan Sir,

That is why I have written it for his future use and avoid this problem again. Please read my post carefully.

Hope this is clear now.

Manoj Chintawar

Hi Gautam,

What you actually want ...a system for DR site or a system with the current production data for testing.

If you want for DR site...You should keep the server(PRDnew) in mount state only and apply the logs every time they got generated in PRD system.

Regards,

Kalyan

Kalyan,

I want PRDnew to be my new Production system to be open to end users, because it is on a server that has more resources.

Now, I would request you to help me applying the new log files 695, 696, etc. to PRDnew after the restore.

Could you provide me the process and sql statements. The one's provided by Manoj are not that clear to me.

Thanks.

Hello Gautam,

You restore the PRDnew system with PRD backup and then apply redologs on the PRDnew system

For this , you need to open the DB of PRDnew in mount state

SQL> startup mount

SQL> recover database; (after you copy the offline redo logs generated after PRD backup to oraorch directory of PRDnew)

After all logs are applied then only you need to open the database.

SQL> Alter database open resetlogs; ( Once the resetlogs is used the next logs in PRD will not be applied)

Regards,

Kalyan

So,

SQL> recover database;

is that the complete command? From manoj's reply above, I see he had provided options, but they are not quite clear to me.

Could elaborate on the options (with actual usage in SQL syntax).

Lets say I want to apply the following 2 oraarch files -

PRDarch1_4436_669761918.dbf

PRDarch1_4437_669761918.dbf

So,

1. I would copy them to /oracle/PRDnew/oraarch

2. Rename them to -

PRDnewarch1_4436_669761918.dbf

PRDnewarch1_4437_669761918.dbf

3. SQL> recover database

Right?

Hi,

Have you changed the SID of PRDnew system.??

Regards,

Kalyan

Gautam,

The commands provided earlier are the complete commands. You can use one of the option to recover the database. For example if you want to do the point in time recovery upto 11 Mar 09 11:00 am then provide that time as a option.

Remember you need to open the database with reset logs after it has recovered. Please refer to earlier post for the sequence after mounting the database.

Hope this helps.

Manoj Chintawar

Yes, SID is changed.

Kindly confirm if the sequence of steps and syntax provided by me, in the above example is ok.

Also, please elaborate on the recover database options in this case.

Thanks.

Manoj,

I believe you are saying the command would be -

SQL> recover automatic database cancel;

So what would happen if I do this? What does manually mean?

SQL> recover automatic database until change number;

What's this change number?

Hi,

SQL> recover automatic database cancel;

So what would happen if I do this? What does manually mean?

When the RECOVER DATABASE UNTIL CANCEL command is

executed, it recovers the database until it cannot find a log file. When you are prompted for the file name, enter CANCEL, and the recovery stops at that point in time.

SQL> recover automatic database until change number;

What's this change number?

This could be system change number SCN or LSN.

And third option is to recover until time. I would recommend this since you have these details with you.

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE UNTIL TIME '2009-03-12:11:00:00';

SQL> ALTER DATABASE OPEN RESETLOGS;

Hope this is clear

Manoj Chintawar

Ok,

So, Manoj,

I have these 2 oraarch files from my source system - PRD

PRDarch1_4436_669761918.dbf

PRDarch1_4437_669761918.dbf

I copy these files to my target system - ZRD, that was restored from PRD.

1. I copy them in /oracle/ZRD/oraarch

2. I rename them to -

ZRDarch1_4436_669761918.dbf

ZRDarch1_4437_669761918.dbf

3. I execute - SQL> recover automatic database cancel;

In this case it will automatically apply the 2 log files? and then prompt me for file name

4. I input CANCEL

Are these steps correct?

Gautam,

yes, it should apply the logs automatically. Please use the command syntax as suggested before.

Also, please use the sequence of commands as suggested before. This remains the same irrespective of option used in recover database until

Hope this helps.

Manoj

Edited by: Manoj Chintawar on Mar 12, 2009 1:22 PM

Manoj,

Could you elaborate on -

1. Change number SCN and LSN - and how do I obtain these number

2. time option - is this the time on the source system(in this case PRD) when the log files were created?

Thanks.

One more new doubt,

While creating the control file, I used -

CREATE CONTROLFILE REUSE SET DATABASE "ZRD" RESETLOGS ARCHIVELOG

Is this fine or Should I use - *CREATE CONTROLFILE REUSE SET DATABASE "ZRD" NORESETLOGS ARCHIVELOG*

Hi,

Could you elaborate on - 

1. Change number SCN and LSN - and how do I obtain these number
2. time option - is this the time on the source system(in this case PRD) when the log files were created?

1. System change number or Log Sequence number. You can find out from the production system. You have to find out this number just before backup was taken. So that you can do the recovery upto this number.

try to use command

select current_scn from v$database

to find out current SCN number.

I think it will be easy for you to carry out time based recovery as suggested earlier as you know the backup times.

hope this is clear now.

Manoj

Manoj, Kalyan,

Thanks a bunch for your help so far.

One more doubt -

While creating the control file, I used -

CREATE CONTROLFILE REUSE SET DATABASE "ZRD" RESETLOGS ARCHIVELOG

Is this fine or Should I use - CREATE CONTROLFILE REUSE SET DATABASE "ZRD" NORESETLOGS ARCHIVELOG

Hi Gautam

> While creating the control file, I used -

> CREATE CONTROLFILE REUSE SET DATABASE "ZRD" RESETLOGS ARCHIVELOG

This can be used.

Best wishes

Regards,

Kalyan

Gautam,

Clause resetlogs is good. Congrats!!

Manoj

Ask a Question

Top Q&A Solution Author

User

Count