problem with recovering standby database

[Former Member]

Hi,

We are having a dr server where we are applying archive logs from the production system. We are getting the below message while giving the command 'recover standby database'

-

---

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

ORA-01196: file 1 is inconsistent due to a failed media recovery session

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

-

---

I afraid if we need to activate the database it wont work.

Kindly suggest what has to done in this case.

Thanks and Regards,

Amit Jana.

[Former Member]

Hi,

Thanks to all for your help.

I am closing this message.

Thanks and Regards,

Amit Jana

[Former Member]

Dear Stefan,

Thank you for the reply.

The 1st step you have mentioned

"1) Stop the recovery on the standby site & shutdown the database"

Please confirm if the below command will work fine to stop recovery.

SQL> recover managed standby database cancel;

Also please clarifiy, if I set the parameter STANDBY_FILE_MANAGEMENT = AUTO in initSID.ora, all the changes in primary database will be reflected automatically?

Thanks and Regards,

Amit Jana.

[stefan_koehler]

Hello Amit,

> Please confirm if the below command will work fine to stop recovery.

> SQL> recover managed standby database cancel;

You are right.. this is the command to stop the recovery.

> Also please clarifiy, if I set the parameter STANDBY_FILE_MANAGEMENT = AUTO in initSID.ora, all the changes in primary database will be reflected automatically?

Regarding to the oracle documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm#i1010428

- If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file (SPFILE) to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.

- If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.

But you also have some limitations, if you set STANDBY_FILE_MANAGEMENT to AUTO - the following commands are not allowed (for example):

- ALTER DATABASE RENAME

- ALTER DATABASE ADD/DROP LOGFILE

- ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER

- ALTER DATABASE CREATE DATAFILE AS

Regards

Stefan

[Former Member]

Hi,

Thanks for your advices. The dr server restoration and recovery has been successful.

I have followed the below steps

1. Move the backup index file from production to SAP backup folder of DR.

2 . Do restoration.

3. Create a standy control file in production and move it to DR.

4. Move the archivelogs to the oraarch of DR.

5. Mount the database in standby mode.

6. Recover standby database.

-

---

one more question : when I add a datafile to some tablespace in production system... do i need to create a standby control file and move it to dr other than creating the same data file manually in DR server?

[stefan_koehler]

Hello Amit,

> when I add a datafile to some tablespace in production system... do i need to create a standby control file and move it to dr other than creating the same data file manually in DR server?

The control file of the standby database has to be recreated when the control file on the primary database is changed. This is generally caused when altering the physical structure of the primary database like when adding datafiles and/or tablespaces.

Do the following steps to add a datafile to the standby:

1) Stop the recovery on the standby site & shutdown the database

2) Add the datafile on the productive site

3) Create a new standby controlfile on the productive site (alter database create standby controlfile as)

4) Archive the current online redologs of the productive site

5) Copy the new created standby controlfile & archivelogs to the standby site

6) Startup the standby database

7) Add the datafile to the standby site

😎 Recover your standby site

Regards

Stefan

[Former Member]

Thank you for your suggestion.

Clarify me one thing ie .. after restoring the sr server from a online backup what will be steps for recovery?

Thanks and regards,

Amit Jana.

[former_member193294]

Well, you have to give the command on SQL+ > recover database using backup controlfile until cancel; and start recovering the required archived redo log files.

ORACLE is very clever to tell you exactly till the last file is needed in order to bring the database online.

Once the recover is completed you submit the command >alter database open resetlogs;

to open your database.

Brdgs,

Loukas

[stefan_koehler]

Hello,

>> Well, you have to give the command on SQL+ > recover database using backup controlfile until cancel; and start recovering the required archived redo log files.

Please don't execute this in a standby environment!

These are the steps to recover a standby (physical) database:

1) Recover your standby database until the point you want (you can also apply the online redologs)

2) After your recovery have finished execute the following commands:

> SQL> shutdown immediate;

> SQL> STARTUP NOMOUNT;

> SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

> SQL> alter database activate standby database;

3) Check your applied data

> SQL> SELECT * FROM V$THREAD;

4) If it is ok ... open your database

> SQL> alter database open;

Have you done it like i described or how have you done your recovery, etc.?

Regards

Stefan

[former_member193294]

Hi Amit,

I would suggest you to restore once again the redo logs. There might has been some redo which were not restored or restored properly.

The whole DB restore once again would be your ultimate solution.

Brgds,

Loukas

[Former Member]

ORA-01196: file string is inconsistent due to a failed media recovery session

Cause: The file was being recovered but the recovery did not terminate normally. This left the file in an inconsistent state. No more recovery was successfully completed on this file.

Action: Either apply more logs until the file is consistent or restore the backup again and repeat recovery.

See below notes

Note 4162 - Missing "end backup"

Note 4161 - Complete Recovery