Oracle Data Guard usage for SAP Disaster Recovery setup (ECC-Oracle-Linux)

[Former Member]

Hi,

I want to setup Disaster Recovery for one of our SAP Production system, and details for that are as below,

SAP Product : ECC EHP5 / SAP Nw based on 702 SP level 10

Kernel : SAP KERNEL 7.20 64-BIT UNICODE / patch 300

OS : Red Hat Enterprise Linux Server release 5.7

Database :Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit

(Single Application server with CI+DB installation)

SAP has suggested below SCN links & notes, but these are not really helpful for technical purpose,

http://scn.sap.com/docs/DOC-7845

http://scn.sap.com/docs/DOC-15688

605062 - FAQ: Restore and recovery

96848 - Disaster recovery for SAP R/3 on ORACLE

1060696 - New BRRESTORE and BRCONNECT command options

What our management has decided is to use the Oracle Data Guard for setting DR configuration instead of any OS specific script (We are licensed to use oracle data guard), and for that I need help or possibly step by step guide with screenshot but I have not find it, However I have found one guide which can been checked at http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf 

Here I would like to know whether can I depend on this guide and can follow the mention steps of it. Or is there any better guide is available.

Also FYI, I have already completed fresh ECC system installation on DR server  and now remaining with backup restore of current PRD to DR PRD & Oracle Data Guard configuration.

Thank you for your guidance.

Regards,

Kiraang.

[Former Member · Accepted solution]

Hi Stefan,

I again need your suggestions, if you can put some lights on my below concerns

Before you check below concerns, I would like to tell you that I am going to use "ARCH" protection mode for data guard configuration, by this we will be loosing some of our data but will not face any performance issue. And hence I am not going to use any SWITCHOVER or FAILOVER or any other kind of functionality. As of now I am done with SQL*NET config & will go for DB restore once I will have some idea on my below concerns

1. As per guide I will create data guard configuration on primary db (page no. 23-25) i.e. creating DG configuration and adding secondary DB and finally enabling it with command on "DGMGRL> ENABLE CONFIGURATION", But do I need to repeat these action on standby database or it will automatically propagate there or no action is require at DR server? (In guide it is not mentioned, so suspecting I need not do anything at DR server)

2. Currently my DR server database role is "primary" whereas ideally it will be "secondary" after data aguard activation, If I will execute above ENABLE CONFIGURATION command on primary server, Will it do the necessary at DR server & will change the database role to secondary automatically?

3.If tomorrow I need to perform mock drill after enabling deployment of data guard and need to make DR SAP up, what all actions I need to perform ? I mean how can I stop the recovery process by command "DISABLE CONFIGURATION"? or I need to fire stop db recovery manual command (ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL) & need to  startup database manually.

4.Also last time I had ask you about GLOBAL_DBNAME parameter conflict, if you can tell me that too.

Thanks & Regards,

Kiraang.

[stefan_koehler · Accepted solution]

Ki Kiraang,

> I would like to tell you that I am going to use "ARCH" protection mode for data guard configuration

There is nothing like an ARCH protection mode. The protection modes are "Maximum Availability", "Maximum Performance" and "Maximum Protection". By the way the ARCH attribute is deprecated in Oracle 11g.

Oracle Documentation:

http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams123.htm#CHDJDFCJ

http://docs.oracle.com/cd/B28359_01/server.111/b28294/whatsnew.htm#CHDHGBHH (search for "The ARCH redo transport mode has been deprecated")

> And hence I am not going to use any SWITCHOVER or FAILOVER or any other kind of functionality.

Why do you configure and run Data Guard, if you never perform a SWITCHOVER or FAILOVER? Even in a DR scenario, you need to do a FAILOVER.

> But do I need to repeat these action on standby database or it will automatically propagate there or no action is require at DR server?

No. The Data Guard Broker configuration is usually in sync between primary and standby (you specify the duplex configuration files on both sites before).

> Currently my DR server database role is "primary"

Then you have done something wrong (like restore or DG configuration). There could be only one primary in a DG scenario.

> I mean how can I stop the recovery process by command "DISABLE CONFIGURATION"

No. You need to stop recovery and perform a role transition as well. A standby database is working "differently" than a primary database and so you need to switch the role in case of a DR scenario. (key word FAILOVER)

> Also last time I had ask you about GLOBAL_DBNAME parameter conflict

What kind of conflict? Every database in a DG scenario has a db_unique_name (and corresponding Data Guard Broker services).

Once again ... get some training ... Data Guard is for worst case scenarios and you need to know how it really works / handle it and not just by copy and paste a guide. This kind of IT operation is just ridiculous. I will also stop here as your system should be operated with the corresponding knowledge.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Fortunately I have now managed to setup my DR server and now everything is working fine such as SQL*NET configuration, redo log shipping, log apply etc.

After your post for "ARCH" mode, I left the plan to change it to "ARCH" mode and now using "ASYNC" mode ( default / have not changed anything). So far now my Dataguard is enabled and working as it should with "LGWR ASYNC" mode ( Max. performance). And after reading many threads I have also created standby redo logs on both the servers instead of mere DR server.

I have now also gone through with SWITCHOVER & FAILOVER functionality of data guard and have understood it, Apology for my earlier misunderstanding.

Now I need to do mock drill & need to test the functionality of data guard, hence I have prepared below plan. And require your valuable inputs if anything is not in flow or not correct,

The below will be performed out of business hours

1. I will take offline backup of PRD + control files ( Will also back-up control files of DR server)

2. I will test SWITCOVER functionality from both the servers ( switchover to prd_standby (from PRD) & switchover to prd_primary (from DR) respectively one after another)

3. I will make current PRD out of network (and will also shut SAP, not DB) to test Failover functionality

4. Afterwards I will execute Failover to prd_standby from DR ( and will up SAP over there),

5. Functional team will validate the latest data and SAP performance

6. Once test will be successful, I will again restore offline backup of PRD on DR & will mount it

7. I will now put PRD on network and will remove data guard configuration, and will again create new configuration and will make redo log shipping enable as it is working now.

Though above plan looks simple to execute, but I want you to validate it for me, and besides that I also want to know about little bit about standby redo logs archival process. Currently as stated before I have created online standby redo log on both the servers and need to know that how archive will be generated for it.

Kindly guide me on this, so that I can perform mock drill & data guard functionality successfully.

Thanks & Regards,

Kiraang.

[stefan_koehler · Accepted solution]

Hi Kiraang,

> that I also want to know about little bit about standby redo logs archival process.

The archival process of redo log files / groups (regardless if standby or online) is always the same. Usually the archive log deletion policy is adjusted according to the requirements and implementation.

Oracle Documentation: http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta010.htm#CHDIFEEE

The plan itself is valid, but is there a technical reason why you don't just reinstate the former primary database?

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Thanks for validating my plan, regarding "reinstate" command , In the guide it is mentioned that it is not possible when using physical standby database. ( Pg. 37).

And about your comment on online & standby redo log files, your link is helpful for detail technical explanation but little complex. So in my case currently archive are generated under "/oracle/PRD/oraarch" directory as standard, and if I am not getting you wrong then you are saying that archive will be generated under that directory only and we do not require separate storage for standby archives for eg. "/oracle/PRD/standbyarch" (here I have doubt for standby archive storage)

Kindly review this & let me know whether separate storage require for standby redo logs or they will be stored in same directory which is "/oralce/PRD/orarch".

Thanks & Regards,

Kiraang.

[stefan_koehler · Accepted solution]

Hi Kiraang,

> In the guide it is mentioned that it is not possible when using physical standby database

Not quite sure which guide you are talking about, but this statement is just wrong (about physical standby databases). There are some restrictions, but nothing in general about physical standby databases.

Oracle Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e17023/sofo.htm#CHDIBEBC

> and if I am not getting you wrong then you are saying that archive will be generated under that directory only and we do not require separate storage for standby archives

Exactly. There was a parameter called "STANDBY_ARCHIVE_DEST" in the past,  which was used for archive log retrieval from the primary database. But it is deprecated as well.

Oracle Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e25513/initparams249.htm#i1134342

But you still need to care about the generated archive logs on standby database (e.g. automatic deletion by policy).

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Regarding “reinstate” command it is mentioned in the below guide which I am using from starting,

http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf

Besides that at http://docs.oracle.com/cd/E11882_01/server.112/e17023/cli.htm

(6.10 Scenario 9: Reinstating a Failed Primary Database)

It is mentioned for flashback database which I am not using, Anyway for me it seems that I should stay out of that & will not use this.

Regarding standby redo logs, I am still in some doubts, actually my concern was, As archives are generated from online redo logs so similarly archives will be generated for standby redo logs so where those will be saved & how can we distinguish them ( I am assuming they can not be differentiated as you said earlier that they are considered one & same). So as of now I will now concentrate to execute my drill plan and will check for any unexpected situation during that time, if any.

FYI, I now have below online redo logs which stores archive in /oracle/PRD/oraarch (not sure of standby redo logs)

Thanks & Regards,

Kiraang.

[stefan_koehler · Accepted solution]

Hi Kiraang,

> It is mentioned for flashback database which I am not using, Anyway for me it seems that I should stay out of that & will not use this.

You need to read the documentation carefully. You need to enable flashback database (logs), if you want to reinstate the database without a restore. But you can restore the database and re-instate it (the documentation differs between reinstate and reenable, but both functions were consolidated into the Data Guard Broker command "REINSTATE" in 11.2.0.2 or so). You don't need to reconfigure Data Guard and so on, if you would use this function. Reconfiguration is even more problematic in higher protection modes.

> As archives are generated from online redo logs so similarly archives will be generated for standby redo logs so where those will be saved & how can we distinguish them

The generated archive logs on standby site are stored in the configured archive log location (the same "procedure" as on the primary). The names can differ (e.g. if you are using flash recovery area, which is recommended in DG scenarios for several reasons), but the content is unique.

> FYI, I now have below online redo logs which stores archive in /oracle/PRD/oraarch (not sure of standby redo logs)

It seems like you still don't get the concept of redo transport mechanism from primary to standby and the handling by/after redo apply.

However good luck with your tests, but it does not look like a professional setup and handling to me (hopefully this is one of your own systems and not a client one).

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Finally I made my client SAP DR mock drill successful, thanks for all your inputs through out this post.

I also used "Reinstate" command as you suggested, its possible with physical standby database as oppose it is mentioned in the guide that it is not possible. I enabled flashback database before performing drill, and after drill I turned it off, which made me to use "Reinstate" command without any issue.

It was fantastic experience while performing drill for SAP DR, and of course to learn from you.

Your suggestions & points were really up to the mark which led me to perform successful drill without any issues.

Once again I would like to thank you very much for your support. I will now close this thread.

Have a nice days ahead!!

Best Regards,

Kiraang.

[Former Member]

Hi,

As per the guide I have set listener.ora & tnsanames.ora file at below location

/oracle/PRD/11203/network/admin

/sapmnt/PRD/profile/oracle

I have also used the parameter NAMES.DIRECTORY_PATH= (TNSNAMES) in sqlnet.ora file.

And I am also able to start the listener but when I going for starting my SAP system its failing.

Trans.log of R3trans is as below,

***LOG BY0=>ORA-12154: TNS:could not resolve the connect identifier specified

I have kept my listener name as "LISTENER_PRD", Below are the file details,

FYI, chemprod is for current PRD server, chemproddr is for DR server

Kindly let me know what is missing in these files.

Thanks & Regards,

Kiraang.

[Former Member]

Hi,

what i have found that if I use PRD.WORLD (the first line of tnsnames.ora) then I am able to start my DR SAP, but as per guide I need to maintained PRD_hostname.WORLD in tnsnames.ora file because there are two connect identifier one for original PRD & one for DR PRD so I can not use PRD.WORLD domain for both of them and have to use according to guide which is PRD_hostname.WORLD

Any suggestions will be really appreciated.

Thanks & Regards,

Kiraang.

[stefan_koehler]

Hi Kiraang,

i am sorry, but you have not understood the concepts as already assumed / mentioned.

You already referred to a guide ( http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf ), that explains the needed service in detail (page 49 to 51). This one should be used for application connect. I can not see such an address list in your posted configuration.

You have just configured the SQL*Net stuff for communication between PRIM and STDBY database (and what about Data Guard Broker services or do you handle it manually by SQL commands/scripts?), but the application connect (including trigger) is completely missing. This "special" configuration is needed in case of a fail-/switchover, so that SAP can re-connect without a TNS reconfiguration.

You are also mixing the two TNS environments (for database "/oracle/PRD/11203/network/admin/" and application "/sapmnt/PRD/profile/oracle"). The database environment should only include the needed services (for DG communication and maybe DGB communication) and the application environment should include the timeout configuration.

Regards

Stefan

[Former Member]

Hi,

I am doing the configuration step by step first SQL*NET config on DR server & after on PRD server, then database paremeter activation & DB restore, and finally I will activate data guard configuration. But unfortunately I am stuck in this SQL*NET config.

Let me explain you little more, as per guide I have change the both listener & tnsnames files ( tnsnames resides at /sapmnt/PRD/profile/oracle also, so there too I have edited). Since guide doesn't have mentioned anything I have kept the tnsnames.ora file contain same at both the location i.e. at /oracle/PRD/11203/network/admin/ and at /sapmnt/PRD/profile/oracle. Are you suspecting for tnsnames content that they should be different?

Also the thing is my listener always start with whatever connect identifier I specify in tnsnames.ora but R3trans fails with the message "TNS:could not resolve the connect identifier specified"

However as I said before if I keep PRD.WORLD then everything works fine ( R3trans retruns 0, SAP gets start)

Here I am suspecting that it obtains the value from environment variable dbs_ora_tnsname = 'PRD' (but not sure) which is why listener identify the connect identifier maintained in tnsnames.ora

And as per guide there are two connect identifier one is for PRD & one is for DR server and they both are distinguished with PRD_chemproddr.WORLD & PRD_chemprod.WORLD inside tnsnames.ora (as per my case)

so i guess I need to maintain that but there seems some technique or way as you are saying in your last para, kindly tell me what is that

Thanks.

Kiraang.

[stefan_koehler]

Hi Kiraang,

> Since guide doesn't have mentioned anything

... and that is what i have mentioned by "could not be implemented by just following a screenshot guide".

> Are you suspecting for tnsnames content that they should be different?

Yes (but not only tnsnames.ora). The SQL*Net configuration in ORACLE_HOME is used by Oracle (which basically means starting the listener, DG communication and so on). The SQL*Net configuration in the separate TNS_ADMIN (/sapmnt/PRD/profile/oracle) is usually used by the application only and should include the "load balancing / fail over" configuration for re-connect.

You usually also use different listeners (one for application communication and one for data guard). This is usually used for splitting the application and DG network traffic and using dynamic registration (for Data Guard Broker if applicable).

> However as I said before if I keep PRD.WORLD then everything works fine ( R3trans retruns 0, SAP gets start)

The application identifier should never change (and yes it is determined by dbs_ora_tnsname) as you configure it with a custom address list. It is basically the same topic (application and database SQL*Net configuration). However the net service name is meaningless ... the important one is the used service name (see database trigger for service in the white paper).

> And as per guide there are two connect identifier one is for PRD & one is for DR server

Yes, these are used for Data Guard (like redo transmission and so on).  You need to use dynamic registration as well (and not only the static listener configuration), if you use Data Guard Broker.

> But unfortunately I am stuck in this SQL*NET config.

Ok, you don't need to worry about R3trans right now as you are stuck at the first step. Right now you are doing the configuration for Oracle Data Guard only (not the application connect!).

Regards

Stefan

[Former Member]

Hi,

Thanks for detailing me, your explanation is very good which made me know many things & ultimately led me to the resolution of mine tnsnames issue. And now I got the desire result i.e. now I am able to start my DR SAP PRD system (R3trans results 0) and also able to do tnsping as per the guide ( which was the first step for me)

I reverted tnsnames.ora to its original state at /sapmnt/PRD/profile/oracle and only maintained tnsnames.ora at /oracle/PRD/11203/network/admin. And now everything is working fine as they should.

However there is one concern related to listener.ora file, if we check in guide there is one line as  GLOBAL_DBNAME=o11_south_DGMGRL (page no. 15) which is conflicting as per Annexure there it is mentioned as GLOBAL_DBNAME=o11_oracle-vm2_DGMGRL (page no. 56).

Can you figure it out which is correct one

For now I have kept GLOBAL_DBNAME=o11_south_DGMGRL / prd_standby_DGMGRL

As of now my DR server is ready with DB parameters & SQL*NET configuration, now I will do the same on my PRD system and then will move for DB restore.

Thanks once again. Your post made my day

Regards,

Kiraang.

[stefan_koehler]

Hi Kiraang,

> What our management has decided is to use the Oracle Data Guard for setting DR configuration instead of any OS specific script, and for that I need help or possibly step by step guide with screenshot

Why not getting some training about Oracle Data Guard first, if your management has decided to use it? Oracle Data Guard is a powerful HA solution and could not be implemented by just following "a screenshot guide". You need to think about different kind of settings (like LGWR sync/async, ARC and so on), the possible tools (DG Broker or not) and all the other stuff and verify them with your business requests and its impacts.

Please get some training or an experienced consultant - seriously.

Regards

Stefan

[Former Member]

Hi Stefan,

I know all this configuration requires in depth technical knowledge & hands on but unfortunately we BASIS people always need to work on diverse products without having much knowledge and same is the case is with me. But I have to do this, if you can share some ready content then it will be very much helpful to me as I have already searched many forums but none is accurately fulfilling my DR set up requirement.

Regards,

Kiraang.

[former_member188883]

Hi Kiraang,

The link mentioned by you is a good link to perform Oracle dataguard configuration.

http://www.oracle.com/us/solutions/sap/wp-ora4sap-dataguard11g-303811.pdf

Based on the scenarios described you may choose the best suitable for your environment.

Additional useful links are shared for your reference below.

http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i67520

http://www.slideshare.net/satishbabugunukula/data-guard-architecture-setup

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

Thanks for sharing content related to DR setup, I will now need to read and take the best option.  I will update on this thread as I will be progressing.

Thanks & Regards,

Kiraang.