Oracle Standby Recovery: ORA-01194 ORA-01110

[Former Member]

Hi All, we are installing an Disaster Recovery Server using Oracle Standby.

Oracle: 10G

OS: Windows 2003

SAP: ECC 6.0

We have a mirror server with the same characteristics of the Production Server.

We are using a OFFLINE Backup of the production system and we Generate a Standby Control file from the production system.

After that we recovered the database in the Mirror Server (the mirror server has SAP + Oracle Installed)

We start the database in the following form:

sqlplus /nolog

connect / as sysdba

startup nomount

alter database mount standby database;

until here all fine...

After that we become to apply the offline redolog:

recover standby database until cancel;

We used the option AUTO

At the end of the archive redolog it gives the following error:

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

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'F:\ORACLE\MIS\SAPDATA1\SYSTEM_1\SYSTEM.DATA1'

We try with the following command to activate the Standby database from Standby to productive (firts we need to test if SAP is running fine).

ALTER DATABASE ACTIVATE STANDBY DATABASE;

I can't understand why the SYSTEM.DATA1 data file has problems!! i don't have a online backup to recover it.

...

...

Please

Some tips.

Best Regards,

Erick Ilarraza

[Former Member · Accepted solution]

_*

DID U EXECUTE STEP SEVEN

Creation of Disaster Recovery Standby Server*_

For creation of disaster recovery standby server we need to recover database on stand alone Machine with the following procedure:

Backup Recovery Procedure:

"Use the designated standby Server in consultation with HOD-IT / System Administrator, for restoration."

"1. If required, install fresh Windows NT system. Create user name <instancename>adm (in our case instancename is C11) and assign administrator group to this user. Also, create other users and user groups as per the installation guide of SAP. "

Login to the server operating system using SIDADM user.

2. Create Logical disk Drives as per the drive structure of Live Server as recorded under filename
SERVE

"3. Restore last NT backup of SAP Live server under original drive names on standby server excluding the C drive containing the NT Operating system. Restore SAP and Oracle registry entries of Production server, which shall be available from
bestdata\sapbackups\registry\HOSTNAME\"

OR

"Restore last online backup of production server. The corresponding log files should be first taken from
bestdata\sapbackups\logs and Copy last summary log file ""back<sid>.log"" and detail log file ""<name>.and"" to SAPBACKUP directory on recovery server. Use following command to restore the last online backup ""BRRESTORE -m full -p <profilefile>"". "

"3. Start database instance using SAPDBA and SAP services using SAP server manager and check if SAP startup is successful. If yes, proceed with step 5 otherwise, check logs to analyze error and resolve."

4. Disable archive logs through SAPDBA.

"5. Stop SAP services and database instance. Stop NT service ""Protected Storage"""

6. After recovery we need to add following Entry of Primary database In tnsnames.ora file on disaster recovery server:

46blive=

(DESCRIPTION=

(ADDRESS=

(PROTOCOL=TCP)

(Host=10.10.100.XX)

(Port=1521))

(CONNECT_DATA=

(SID = C11)))

NOTE ; XX= LIVE SERVER IP

"7. After adding entry on tnsnames.ora file, create standby control file for disaster recovery server on Live database through following command: (THIS ACTIVITY DONE IN LIVE SERVER BEFORE THAT RENAME STANDBY)"

C:>sqlplus /nolog

Sqlplus> connect / as sysdba

sqlplus> Alter database create standby control file as ‘standby’;

"This command creates Control file in Oracle_home\database directory, copy this file to Concern directory on standby server as CTRLC11.Ctl. (In current scenario I:>oracle\C11\origlogA,J:>\oracle\C11\Saparch and K:\oracle\C11\sapdata1 directories.) "

8. After copying Control Files Goto oracle Server Manager and give following procedure:

sqlplus> Startup nomount.

sqlplus> Alter database mount standby database;

9. Exit from Oracle Server Manager.

10. Before copying offline redo logs Copy init<Instance Name>.SAP to CopyArch.SAP and add the following parameter in CopyArch.SAP file on Production Server:

BACKUP_DEV_TYPE = DISK

ARCHIVE_FUNCTION = SAVE

"ARCHIVE_COPY_DIR = Target_drive\oracle\<Instance_name\saparch (This is the target drive of standby server where archives are copied, normally the archive directory on standby server)"

VOLUME_ARCHIVE = SCRATCH

11. Before Applying offline redo logs Copy init<Instance Name>.SAP to ApplyArch.SAP and add the following parameter in CopyArch.SAP file on Production Server:

BACKUP_DEV_TYPE = DISK

ARCHIVE_FUNCTION = SAVE

ARCHIVE_COPY_DIR = Target_drive\oracle\<Instance_name\saparch

VOLUME_ARCHIVE = SCRATCH

BACKUP_ROOT_DIRECTORY = %SAPBACKUP%

Primary DB = @Instance_name (Presently it is C11)

"12. Before start copying we need to map remote drive where we want to copy offline archive logs, for mapping the drive following are the procedure:"

Logon to SAP Live instance ->run transaction sm36 Create Scheduled Job named MAP_DRIVE_COMPUTER_NAME with following step.

"External Command: net use, "

"Command Parameter x:
COMPUTER_NAME\jdrive PASSWORD /USER: computer_name\Instance_name+adm /PERSISTENT:YES, "

Target Server <Computer_name>

13. For copy the archive log to disaster recovery system do the following:

Logon to SAP Live instance ->run transaction sm36 Create Scheduled Job named BRARCHIVE_FILL_COPY with following step

"External Command BRARCHIVE, "

"Command Parameter –s –c –f –p copyarch.sap, "

Target Server 46BLIVE.

[Former Member]

Hi

The one of the reason for getting the error is

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

is that backup from which u r recovering from is not good. Please try a different backup for restoration.

With regards

Sudha

[Former Member]

Hi all!

Thank a lot for your reply!

The problem is related with the backup OFFLINE, we saw the ALERT_SID.log file and the oracle instance never was shutdown

In consecuence the Backup OFFLINE is inconsistent.

The Backup was started by legato. But we are having problems with this utility.

Thanks a lot for your help.

Best Regards,

Erick ILarraza

[Former Member]

Hi,

sql>Alter system archive log current;

It will create oraarch logs,small one ....copy it to target.

Apply those logs in the target system,the error will go off....

Steps

1.startup nomount

2.alter database mount standby database exclusive;

3.copy the logs after backup from primary to target system

4.recover standby database;

....

Award points if helpful

Supriyo

Edited by: supriyo choudhury on May 17, 2008 4:29 PM