Setting up Standby Database Error

[Former Member]

Hi Gurus,

I am trying to setup an Oracle Standby Database with a delay of few hours behind the PROD database, but running into problems. Hopefully someone here can help.

This is what I have done:

The necessary parameters in the initSID.sap file of the Standby database are set, like primary_db etc.

I have restored the most recent backup of my Production database(10.2.0.2) on my standby server.

Then from the primary server, I have given the command:

alter database create standby controlfile as '/oracle/<SID>/102_64/dbs/cntrl<SID>.con'

and copied this controlfile to a dbs folder on standby server.

Then I am giving this comands:

sqlplus "/ as sysdba"

startup nomount

alter database mount standby database

Then I am trying to Test a recovery till the end of the backup time:

recover standby database using backup controlfile until time '2009-01-26:05:06:08';

Here, after applying all necessary logs, I get this error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/oracle/SID/sapdata1/system_1/system.data1'

I also tried running the brarchive command directly :

brarchive -m 60 -u / -c -d stage -f

to restore with a delay a second time after restore again,

but that fails too with same error.

What am I doing wrong here? I have a fellign this has to something to do with the control file.

Please suggest

Cyrus

[former_member259363]

Hi cyrus & all

i am new born in SAP basis and completely unaware with standby database.

currently we setup one standby database and its going fine, but the issue is we are

doing rcp manually from primary system to standby system.

could you plz tell me what necessary steps required so that redo logs may move (restore) automatically or

in some delay from production system.

i couldnt find what changes are required in init<sid>.sap or any other.

plz guide me

Best Regards

deepak

Edited by: Genus Power on Dec 29, 2011 7:32 AM

[Former Member]

Hi,

Use the below set of queries :

1) startup nomount;

2) alter database mount standby database exclusive;

3) recover standby database;

Step 3 is the way to recover the standby database.

[Former Member]

Hi All,

Thanks for your replies.

I have followed the steps and did this:

1) Recover database again

2) startup nomount

3) alter database mount standby database;

4) recover standby database until time '2009-01-XX:XX:XX:XX';

It said Recovery completed but gave the same error as before and,

When I try to activate standby database, I get similar error:

SQL> alter database activate standby database;

alter database activate standby database

***

ERROR at line 1:

ORA-01195: online backup of file 1 needs more recovery to be consistent

ORA-01110: data file 1: '/oracle/ZM1/sapdata1/system_1/system.data1'

[stefan_koehler]

Hello Cryus,

When I try to activate standby database, I get similar error:
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/ZM1/sapdata1/system_1/system.data1'

That is a completely different error.

Now just to get sure - you have restored an ONLINE backup of the database, right?

How long did you recover - did you recover at a point after the end of the ONLINE backup?

It seems like you have restored an online backup, but did not recover the database until the database was in a non-backup mode.

Regards

Stefan

[Former Member]

Hi Stefan,

You are right.

This error was completely unrelated to the Standby creation.

I found out from Oracle documentation that the standby controlfile generated from primary db has to be genererated after the backup so it has to have a later timestamp. I was doing thios but immediately after the backup finishes and maybe the timestamp was same.

This time I waited for about 1/2 hr and generated it and the restore went fine.

Then I directly started the process:

brarchive -m 360 -u / -c -f -d stage

and it works fine and restores the logs.

Now, I will check to see if the Database changes in primary db(new datafiles, resizes ..) are correctly being propogated to the standby server.

Thanks

Cyrus

Edited by: cyrus brocha on Jan 28, 2009 5:01 PM

[Former Member]

Hi All,

The Standby database is running like a champ.

All changes in the primary DB are being made automatically in the Standby DB as expected.

So, no problems to report.

I have noticed that datafile additions come up in the alert_SID.log file of Standby DB as entries from brarchive but there are no entries for datafile resizes.

The resize did happen though, just not sure if the standby db reports it anywhere?

If anyone can throw some light on this, it would be great!

Thanks

Cyrus

[stefan_koehler]

Hello Cyrus,

> recover standby database using backup controlfile until time '2009-01-26:05:06:08';

It is not a backup controlfile, it is a standby controlfile. You have created the correct control file, but executing the wrong recover command.

After your recovery you don't have to perform a RESETLOGS.. just use "alter database activate standby database;"

Please check the procedure here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/rman.htm#sthref1320

I would also suggest to use the Oracle Data Guard features to apply the logs / switchover, etc. or using the Data Guard Broker to make it easier.

Regards

Stefan