Anybody Knows, How is configuration Oracle Fail Over (Data Guard)

[Former Member]

Hello Guys,

I'm try to do a standby configuration in a custumer but I'm having a problem in configuration.

in this parameter if DB_UNIQUE_NAME is the same of both return errors if not the same is correct.

Other doubt is about Listener.ora how can i configuration the listener for two hostnames the same form to tnsnames.ora.

If anybody can help me ..or if anybody have one file step by step please send me.

Thanks

Osvaldo Antonio dos Santos

Consultant SAP BASIS

Brazil - São Paulo

[Former Member]

Thank I create a Doc for implemantation of Standby Fell free to ask me .

[Former Member]

Hi Stefan,

Let me see if I understand in the case of DB_UNIQUE_NAME that I have to use in parameter "DEST" is the same name that will have in TNSNAMES.

For example :

if in parameter :

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1 ,STDBY1)'

LOG_ARCHIVE_DEST_2= 'SERVICE=STDB_CON VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=<b>STDBY1'</b>

In TNSNAME will have

STDBY1.WORD= .....

only for this parameter the oracle will be to find witch connect will have to do in TNSNAME ?

Imagine this situation and tell me if is correct please.

<b>initPRD.ora</b>

db_name= <b>Primary</b>

db_unique_name= <b>Primary</b>

log_archive_config='DG_CONFIG=(‘<b>Primary’,StandBy’</b>)

log_archive_dest_1='LOCATION=E:\oracle\Primary\oraarch\ DELAY = 20 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) <b>DB_UNIQUE_NAME=’Primary’</b>

log_archive_dest_2='SERVICE= Standby LGWR ASYNC DELAY = 20 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

<b>DB_UNIQUE_NAME= Standby '</b>

log_archive_dest_state_1 = <b>enable</b>

log_archive_dest_state_2 = <b>enable</b>

remote_login_passwordfile='<b>exclusive'</b>

log_archive_max_processes = <b>30</b>

fal_client = <b>'Primary'</b>

fal_server = <b>'Standby'</b>

standby_archive_dest=<b>'F:\oracle\Standby\oraarch'</b>

does TNSNAMES will have to be this form ?

1. Generated by Oracle configuration tools.

<b>Primary.Word</b> =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))

)

(CONNECT_DATA =

(SID = primary)<b> =====> real name of database</b>

)

)

<b>Standby.WORLD</b> =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.121)(PORT = 1521))

)

(CONNECT_DATA =

(SID = standby) <b> =====> real name of database</b>

)

)

Is correct this configuration ??

Now we go to doubt of Listner.

I have one Listener in Primary and other in standby and wicth one has your owner service in windows.

My doubt in Primary I how Can I do to Primary see Standby.

I will have to create other service in windows indicate the standby host or I have to write in Primary listener informations about standby and his hostname.

Stefan Thank you for your help and your patience.

Regards

Osvaldo

[stefan_koehler]

Hello Osvaldo,

so lets start again.

At first let's just clarify one thing... the db_unique_name must not match the db_name. The db_name must be the same that you used in the "CREATE DATABASE" statement... the db_unique_name is only an identifier for the database in your dataguard environment.

I am using your values for my example ... the productive database is called PRO and the standby has the same name... so let's start.

-

---

Productive Site

initPRO.ora:

db_name = PRO

db_unique_name = PRIMARY

log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'

log_archive_dest_1='LOCATION=E:\oracle\Primary\oraarch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY'

log_archive_dest_2='SERVICE=SRV_STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY'

tnsnames.ora:

PRO.Word =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.153)(PORT = 1521))

)

(CONNECT_DATA =

(SID = PRO)

)

)

SRV_STANDBY.World

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.121)(PORT = 1521))

)

(CONNECT_DATA =

(SID = PRO)

)

)

Standby Site

initPRO.ora:

db_name = PRO

db_unique_name = STANDBY

-

---

>> My doubt in Primary I how Can I do to Primary see Standby.

By adding the Oracle Net service to the tnsnames.ora file... so that the prod database knows the service like in my example from above. You have one listener running on the host 192.168.0.153 for the productive database ... and your listener for the standby database runs on 192.168.0.121. Both listeners are providing the Net service PRO .. but one is for prod and one for standby.

Btw. there is a great documentation about that topic:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_transport.htm#g1296022

Regards

Stefan

[Former Member]

<b>OK don't worry I understand, so let me see :</b>

1) Oracle Version

<b>===> 10g</b>

2) What is the SQL Net Service name fro prod and standby database?

<b>===> server = PRD and standby = PRD buth call PRD</b>

3) What is the service name for the instances that are published by the listener? (do you register your databases with pmon??)

<b>====> I don't understand your point and I don't know pmon. but I need to put in the TNSNAMES.ORA

for exemple in file tnsname in server PRD i have to put information about standby PRD and one another, this problem, how I can

to put the same name in TNSNAMES.ora ?</b>

For the beginning lets clarify some configuration points:

<b>1) log_archive_config</b>

- send Indicates that the database, when in primary database mode, can send redo logs to the standby database (default).

- receive Indicates that when running in standby mode, the database can receive redo logs from the primary database (default).

<b>======> yes I know but I can not put log_archive_config=DG_CONFIG(PRD,PRD) oracle doens't permit this return error.</b>

So in your case its correct.

<b>======> In the answear below I dont't Understand because I have one service in witch server ?</b>

2) log_archive_dest_2

Your parameter settings are wrong. I copy and paste from the official documentation:

- You identify remote destinations by specifying the SERVICE attribute with a valid Oracle Net service name (SERVICE=net_service_name) that identifies the remote Oracle database instance to which the redo data will be sent.

So in your case:

LOG_ARCHIVE_DEST_2='SERVICE=<SQLNET_REMOTE_SERVICENAME> VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME='<REMOTE_DATABASE_NAME>'

>> Other doubt is about Listener.ora how can i configuration the listener for two hostnames the same form to tnsnames.ora.

I don't understand this point. Your primary and standby database has one listener running and you have to specifiy each sqlnet service name in the tnsnames.ora to reach the databases by the SERVICE option in log_archive_dest_<n>.

If you have registered your database with pmon, you will have an extra service for standby (data guard) databases.

<b>===> In this case I'm confuse because has one listener for wicth server correct ? I dont need epecify informatin about the other

server, in this case how can I do that ?</b>

Regards

Osvaldo Antonio dos Santos

[stefan_koehler]

Hello Osvaldo,

<b>===> server = PRD and standby = PRD buth call PRD</b>

Ok maybe the two listeners (one on prod and one on standby) are providing these service names.. but what is the name in your tnsnames.ora? You can not have both databases with the same network name in the tnsnames.ora.

<b>I don't understand your point and I don't know pmon. but I need to put in the TNSNAMES.ORA</b>

Yes you need one entry for your prod and one for your standby database. And these entries are used in the log_archive_dest_<n> parameter.

<b>======> yes I know but I can not put log_archive_config=DG_CONFIG(PRD,PRD) oracle doens't permit this return error.</b>

And this is correct. You need to have some different names. Lets make a little example.

You need to specify the parameter DB_UNIQUE_NAME on your prod and standby database. This name must be unique in your whole data guard landscape.

So let's say:

Prod database ==> DB_UNIQUE_NAME = PROD1

Standy database ==> DB_UNIQUE_NAME = STBY1

Then you have to put the names into log_archive_config as the following:

log_archive_config=DG_CONFIG(PROD1,STBY1)

Please have a look at my link again:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_arch_dest_param.htm#i104031

There is an example of that with a great explanation.

<b>===> In this case I'm confuse because has one listener for wicth server correct ? I dont need epecify informatin about the other server, in this case how can I do that ?</b>

Ok lets start from the beginning ... all parameter values are just some dummy names.

You have one listener on every server (one for prod and one for standby).

Your prod database has the DB_UNIQUE_NAME PROD1 and your standby has STBY1.You also have an entry in tnsnames.ora for your standby database called STDB_CON.

So your configuration has to look like.

LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD1 ,STDBY1)'
LOG_ARCHIVE_DEST_2= 'SERVICE=STDB_CON VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY1'

Regards

Stefan

[Former Member]

This message was moderated.

[stefan_koehler]

Hello Osvaldo,

sorry i have no msn messenger.... i am also not able to read or write portuguese.

I have only Skype and maybe you can post the answers to my questions from above... so everyone would benefit here in the forum.

>> I want to clarify some doubts I need this information very much and I see that you are expert in oracle fail over.

No i am not really an expert in oracle data guard... i have just implemented and tested some things around..

Regards

Stefan

[stefan_koehler]

Hello Osvaldo,

to be honest... i am not able to follow you (or your steps)... it is a little bit confuse.

At first i wanna post the "official" oracle documentation for the parameters:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_arch_dest_param.htm#i104031

Now we need some more information to help you:

1) Oracle Version

2) What is the SQL Net Service name fro prod and standby database?

3) What is the service name for the instances that are published by the listener? (do you register your databases with pmon??)

For the beginning lets clarify some configuration points:

<b>1) log_archive_config</b>

- send Indicates that the database, when in primary database mode, can send redo logs to the standby database (default).

- receive Indicates that when running in standby mode, the database can receive redo logs from the primary database (default).

So in your case its correct.

<b>2) log_archive_dest_2 </b>

Your parameter settings are wrong. I copy and paste from the official documentation:

- You identify remote destinations by specifying the SERVICE attribute with a valid Oracle Net service name (SERVICE=net_service_name) that identifies the remote Oracle database instance to which the redo data will be sent.

So in your case:

LOG_ARCHIVE_DEST_2='SERVICE=<SQLNET_REMOTE_SERVICENAME> VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME='<REMOTE_DATABASE_NAME>'

>> Other doubt is about Listener.ora how can i configuration the listener for two hostnames the same form to tnsnames.ora.

I don't understand this point. Your primary and standby database has one listener running and you have to specifiy each sqlnet service name in the tnsnames.ora to reach the databases by the SERVICE option in log_archive_dest_<n>.

If you have registered your database with pmon, you will have an extra service for standby (data guard) databases.

Regards

Stefan

[markus_doehr2]

Check

http://service.sap.com/instguides

The official installation guide contains separate section for cluster installations.

--

Markus