Home » Server Options » Data Guard » primary don't want be open after standby configuration
primary don't want be open after standby configuration [message #516561] Tue, 19 July 2011 05:09 Go to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
Hi,

I did standby before and it works wighout any disturbations and even configuration seems to me not very complicated - but it was before.
now i am trying to do standby configuration once again and i don't know where I have corruption in my thinking. :/

there is database named DESK.

two physical serwers:
sczepl-db07 - primary
r00979 - standby

on each for this serwer there is listener listening on port 1521, and on each is tnsnames configured properly:

for sczepl-db07
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sczepl-db07)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\oracle\ora92)
      (PROGRAM = extproc)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = desk_prm)
      (ORACLE_HOME = C:\oracle\ora92)
      (SID_NAME = desk)
    )      
  )

tns
----------

desk_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = r00979)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_std)
    )
  )

desk_prm =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sczepl-db07)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_prm)
    )
  )


for r00979:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = d:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = desk_std)
      (ORACLE_HOME = d:\oracle\ora92)
      (SID_NAME = desk)
    )
  )

LISTENER =
  (DESCRIPTION =
	(ADDRESS_LIST =
	    (ADDRESS = (PROTOCOL = TCP)(HOST = r00979)(PORT = 1521))
	)
  )

tns
-------

desk_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = r00979)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_std)
    )
  )

desk_prm =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = sczepl-db07)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = desk_prm)
    )
  )


after shudown primary system i copied every data files to the same directory on standby serwer, created pfile from primary and standby controlfile (everything like is written on many publications) and copied them to the standby.

so service for standby is working properly and init configuration looks like this:

background_dump_dest='d:\oracle\admin\desk\bdump'
core_dump_dest='d:\oracle\admin\desk\cdump'
user_dump_dest='d:\oracle\admin\desk\udump'
FAL_SERVER=desk_prm
FAL_CLIENT=desk_std
*.compatible='9.2.0.0.0'
*.control_files='r:\oracle\oradata\desk\STANDBY.CTL'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='desk'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='desk'
*.java_pool_size=0
*.job_queue_processes=4
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=R:\oracle\oradata\DESK\arch'
*.log_archive_format='log_DESK_T%TS%S.arc'
*.log_archive_start=true
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'


standby redologs was added for this database.

so lets start our standby server.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> startup nomount pfile='d:\oracle\ora92\database\initdesk.ora'
Instancja ORACLE zosta│a uruchomiona.

Total System Global Area  101785012 bytes
Fixed Size                   454068 bytes
Variable Size              75497472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

Baza danych zosta│a zmieniona.

SQL> alter database recover managed standby database disconnect from session;

Baza danych zosta│a zmieniona.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL>



and it looks like it works.

lets try connection from primary serwer to standby - for checking purposes.

C:\oracle\ora92\bin>sqlplus "sys/sys123@desk_std as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jul 19 11:51:24 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production



SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select host_name from v$instance;


HOST_NAME
----------------------------------------------------------------
R00979

SQL>


so this is prove that connection primary <---> standby works. i can assure you that connection from standby to primary is also working well.

so lets back to the primary db.

SQL> show parameter log_archive_dest_

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
log_archive_dest_1                   string      LOCATION=R:\oracle\oradata\DE
                                                 K\arch
log_archive_dest_10                  string
log_archive_dest_2                   string      SERVICE=desk_std LGWR SYNC AF
                                                 IRM
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string
log_archive_dest_8                   string

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
log_archive_dest_9                   string
log_archive_dest_state_1             string      enable
log_archive_dest_state_10            string      enable
log_archive_dest_state_2             string      enable
log_archive_dest_state_3             string      enable
log_archive_dest_state_4             string      enable
log_archive_dest_state_5             string      enable
log_archive_dest_state_6             string      enable
log_archive_dest_state_7             string      enable
log_archive_dest_state_8             string      enable
log_archive_dest_state_9             string      enable
SQL> 


so second destrination parameter is made for the standby purposes and I AM SURE that desk_std alias proper which i proved before.

so lets make this primary db protected and open it.

SQL> ALTER DATABASE SET STANDBY DATABASE PROTECTED;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL>


and alert log says:

ALTER DATABASE SET STANDBY DATABASE PROTECTED
Tue Jul 19 11:56:54 2011
Completed: ALTER DATABASE SET STANDBY DATABASE PROTECTED
Tue Jul 19 11:57:09 2011
alter database open
Tue Jul 19 11:57:09 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LNS0 started with pid=13
Tue Jul 19 11:57:19 2011
LGWR: Error 12154 verifying archivelog destination LOG_ARCHIVE_DEST_2
LGWR: Continuing...
Tue Jul 19 11:57:19 2011
Errors in file c:\oracle\ora92\admin\desk\bdump\desk_lgwr_3692.trc:
ORA-12154: TNS:could not resolve service name





Dump file c:\oracle\ora92\admin\desk\bdump\desk_lgwr_3692.trc
Tue Jul 19 11:57:09 2011
ORACLE V9.2.0.7.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Windows 2000 Version 5.2 Service Pack 2, CPU type 586
Instance name: desk

Redo thread mounted by this instance: 1

Oracle process number: 5

Windows thread id: 3692, image: ORACLE.EXE


*** SESSION ID:(4.1) 2011-07-19 11:57:09.605
Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
*** 2011-07-19 11:57:09.605 43609 kcrr.c
Initializing NetServer for dest=desk_std
Initializing PGA storage for Netserver communication
Allocating a brand new NetServer
Allocated NetServer 0
	 Starting NetServer
NetServer 0 has been started.Subscribing to KSR Channel 0
success!
Indicating recv buffer for KSR Channel 0
success
Waiting for Netserver 0 to initialize itself
*** 2011-07-19 11:57:12.620 43935 kcrr.c
    Netserver 0 has been initialized
LGWR performing a channel reset to ignore previous responses
LGWR connecting as publisher to KSR Channel 0
LGWR-NS 0 initialized for destination=desk_std
*** 2011-07-19 11:57:12.620 44380 kcrr.c
Making upiahm request to NetServer 0
Waiting for NetServer to respond to upiahm
*** 2011-07-19 11:57:15.057 45465 kcrr.c
Making upidhs request to NetServer 0 (hst 0x03FCEAC0)
NetServer pid:3740
*** 2011-07-19 11:57:19.057 45661 kcrr.c
  upidhs done status 12154
*** 2011-07-19 11:57:19.057 44566 kcrr.c
   upiahm connect done status is 12154 
Error 12154 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'desk_std'
*** 2011-07-19 11:57:19.057
LGWR: Error 12154 verifying archivelog destination LOG_ARCHIVE_DEST_2
Continuing...
ORA-12154: TNS:could not resolve service name
Standby database verification failed:16072
error 16072 detected in background process
ORA-16072: a minimum of one standby database destination is required



so why primary don't see standby db and claims that ther is no standby service? i proved that there is connection primary <--> standby.

where is mistake? i am out of any ideas. i am fighting with this for 2 days.




Re: primary don't want be open after standby configuration [message #516562 is a reply to message #516561] Tue, 19 July 2011 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.

Post tnsnames.ora from both servers.

Regards
Michel

Re: primary don't want be open after standby configuration [message #516564 is a reply to message #516562] Tue, 19 July 2011 05:58 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
Hi Michael,

i read you suggestions about tnsnames.ora in the other post from orafaq forum which concerned the same issue with dataguard like my issue, and I checked my TNS configuration 100 times.

both tnsnames for each two serwers are in my previous post on the begining.

i can only add sqlnet.ora which contans this lines and is the same for boths serviers.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.AUTHENTICATION_SERVICES = (NTS)
SQLNET.EXPIRE_TIME=2


i can't understand situation when I am doing connection through sqlplus from primary to standby server (tnsping works also - but this is only prove that listener for standby is working), and protected primary database claims that can't resolve name for standby server - magic.

to be completely sure i tryied to make database link from primary to mounted standby - yes, i know that this is without sense doing such thinks but my intension was be perfectly sure that primary db see standby from inside throught dblink.

SQL> ALTER DATABASE SET STANDBY DATABASE unPROTECTED;

Database altered.

SQL> alter database open;

Database altered.

SQL> create public database link pio_link
  2  using 'desk_std';

Database link created.

SQL> select sysdate from dual@pio_link;
select sysdate from dual@pio_link
                         *
ERROR at line 1:
ORA-02068: following severe error from PIO_LINK
ORA-01033: ORACLE initialization or shutdown in progress


SQL> create public database link pio_link_dummy
  2  using 'desk_std_dummy';

Database link created.

SQL> select sysdate from dual@pio_link_dummy;
select sysdate from dual@pio_link_dummy
                         *
ERROR at line 1:
ORA-12154: TNS:could not resolve service name


SQL>


so there wasn't

ORA-12154: TNS:could not resolve service name

but

ORA-01033: ORACLE initialization or shutdown in progress


Re: primary don't want be open after standby configuration [message #516565 is a reply to message #516564] Tue, 19 July 2011 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to set the archive_log_dest_2 to the complete connection description string instead of the service name this will allow us to prevent from an error in tnsnames.ora, sqlnet.ora files or pointers.

Regards
Michel
Re: primary don't want be open after standby configuration [message #516567 is a reply to message #516565] Tue, 19 July 2011 06:26 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
will you help me? how should i do this

SQL> alter system set log_archive_dest_2='SERVICE=(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std))) LGWR SYNC AFFIRM' scope=both;
alter system set log_archive_dest_2='SERVICE=(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std))) LGWR SYNC AFFIRM' scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_2" not allowed with SPFILE


SQL> alter system set log_archive_dest_2='SERVICE=desk_std LGWR SYNC AFFIRM' scope=both;

System altered.

SQL>

C:\oracle\ora92\bin>sqlplus "sys/sys123@'(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))' as sysdba

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jul 19 13:25:25 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL>



so (as you can see) description connection works throught plsql but i can't set it as a system parameter.

[Updated on: Tue, 19 July 2011 06:51]

Report message to a moderator

Re: primary don't want be open after standby configuration [message #516571 is a reply to message #516567] Tue, 19 July 2011 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try (we have this kind of thing and it works, althought it is 10.2):
alter system set log_archive_dest_2=
'SERVICE="(description=(address_list=(address=(protocol=TCP)(host=r00979)(port=1521)))(connect_data=(service_name=desk_std)))", LGWR SYNC AFFIRM' 
scope=both;


Regards
Michel

[Edit: typo]

[Updated on: Tue, 19 July 2011 07:09]

Report message to a moderator

Re: primary don't want be open after standby configuration [message #516578 is a reply to message #516571] Tue, 19 July 2011 07:08 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
IT WORKS!!!!! i will put more description after i play with this Smile

thanks Michael
Re: primary don't want be open after standby configuration [message #516594 is a reply to message #516578] Tue, 19 July 2011 08:14 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
so. once again i would like to thank you Michael. you spared me many hours of digging with this topic.

first: i didn't know that there should be coma before ", LGWR SYNC AFFIRM' " string. there was no examples with this coma inside this string in the examples from internet. this is also supprising why there should be coma, because if we are using alias (not whole connection description) there is no coma inside this string. so this was reason why i couldn't set this property in my system. problem solved.

after i set this, my protected primary db started properly and configuration with standby is working.

but second issue was why the oracle didn't resolve my connection from tns. i can't find reason why. everytnig seems be set properly. listener, tnsnames, sqlnet. i even tryed to set dblink between primary and standby using alias of standby database and everyting looks like oracle was resolving this alias (it was described in post with pio_link and pio_link_dummy).
there was two homes in this machine (for 9i and 10g) but i think that i did everthing to have 100% sure that oracle uses proper home directory.
Re: primary don't want be open after standby configuration [message #516602 is a reply to message #516594] Tue, 19 July 2011 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was why I said: "Try to set the archive_log_dest_2 to the complete connection description
string instead of the service name this will allow us to prevent from an error in tnsnames.ora,
sqlnet.ora files or pointers."

(pointers mean paths here, I thought there may be a TNS_ADMIN set somewhere or something like that,
2 homes is a good example to care of.)

Regards
Michel
Re: primary don't want be open after standby configuration [message #516605 is a reply to message #516602] Tue, 19 July 2011 08:39 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
i started to set tns_admin to proper path on primary server at the begining...

C:\Documents and Settings\piotr>set tns_admi
TNS_ADMIN=c:\oracle\ora92\network\admin\


...to be sure that primary database uses proper tnsnames.ora file.
but it didn't. nevermind. now it works in test environment so i can dig in it. production environment is only with one homes for each sever so i don't think, that there will be similar situation like in test.

Re: primary don't want be open after standby configuration [message #516615 is a reply to message #516605] Tue, 19 July 2011 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then you didn't change/set the instance value as its environment is the one of the associated service
that is what is in the registry and in system environment variables (just for yout information).

Regards
Michel
Re: primary don't want be open after standby configuration [message #516635 is a reply to message #516615] Tue, 19 July 2011 13:32 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
... but tns_admin was set at the beginig of my work. should i change any other system variable?

[Updated on: Tue, 19 July 2011 13:32]

Report message to a moderator

Re: primary don't want be open after standby configuration [message #516640 is a reply to message #516635] Tue, 19 July 2011 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but tns_admin was set at the beginig of my work.

But it is not set for the Windows service you start.

Regards
Michel
Re: primary don't want be open after standby configuration [message #516642 is a reply to message #516640] Tue, 19 July 2011 14:22 Go to previous messageGo to next message
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
Senior Member
i set this at system enviroment variable level so the value for this variable was visible for every session in system - even for services.

... but nevermind Michael. the think that it is working now is now important. Wink

[Updated on: Tue, 19 July 2011 15:02]

Report message to a moderator

Re: primary don't want be open after standby configuration [message #520420 is a reply to message #516561] Mon, 22 August 2011 07:50 Go to previous messageGo to next message
mrafferty
Messages: 4
Registered: August 2011
Location: Dublin
Junior Member
I have much the same problem as follows:

I have two production databases, one of which I have Dataguard setup and is working fine. The problem with the other is that when I go to start setting up dataguard, if any user attempts to connect to the Primary database using the servicename I have setup, the service appears to be sending them to the dataguard database incorrectly - they receive the usual message that database is currently shutdown.

When I stop implementing Dataguard, and shutdown the dataguard database, when users attempt to connect to the Primary database then no problem.

Question why is the service I have setup sending users incorrectly to the dataguard database? I have tnsnames.ora identical for both Primary and dataguard databases (with the names obviously different).

My db version is 11.1.0.7 running on a 2-node RAC cluster on linux platform.

Thanks
Re: primary don't want be open after standby configuration [message #520425 is a reply to message #520420] Mon, 22 August 2011 08:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Sounds to me as though you don't have your service registration set up correctly. I would usually create services for each possible role, and a startup trigger to make sure the database that is currently the primary registers one service name with the listeners, and the standby registers another. For instance,
exec dbms_service.create_service('prim','prim');
exec dbms_service.create_service('stby','stby');
create or replace trigger start_serv after startup on database
declare
vrole varchar2(30);
begin
select database_role into vrole from v$database;
if vrole='PRIMARY' 
then dbms_service.start_service('prim');
end if;
if vrole='PHYSICAL STANDBY' 
then dbms_service.start_service('stby');
end if;
end;
/

then your tnsnames entry will point to the service prim, and always get to the daabase that is currently the primary.
Re: primary don't want be open after standby configuration [message #520426 is a reply to message #520425] Mon, 22 August 2011 08:54 Go to previous messageGo to next message
mrafferty
Messages: 4
Registered: August 2011
Location: Dublin
Junior Member
I just have the one service setup on the Primary, so connections will go to the active instance. I do not have any services setup on the standby - trying to keep simple until I can get DataGuard going.
I do not understand why connections are going to the DataGuard database when I try attempt to get the DataGuard database started, but yet they resolve to the Primary database when the DataGuard is down.

Is there anywhere else besides tnsnames where these connections can be configured?

Thanks.
Re: primary don't want be open after standby configuration [message #520427 is a reply to message #520426] Mon, 22 August 2011 09:03 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Sine you haven't given any details of how your services are setup or of your tnsnames entries, I can't offer any more advice.
Re: primary don't want be open after standby configuration [message #520451 is a reply to message #516561] Mon, 22 August 2011 15:35 Go to previous messageGo to next message
mrafferty
Messages: 4
Registered: August 2011
Location: Dublin
Junior Member
tnsnames entries:
--Dataguard database
ORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv008)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcldg)
)
)

--Oracle Service
ORCL11G_SVC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = srv003-v)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = srv004-v)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl11g_svc)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
(RETRIES= 20)
(DELAY=1)
)
)
)

So when Dataguard database has started if I enter:
sqlplus user1/xyz123@ORCL11G_SVC to try and connect to Primary database, it tries to connect to the Dataguard database - why is this happening therefore?

Thanks

[Updated on: Mon, 22 August 2011 15:36]

Report message to a moderator

Re: primary don't want be open after standby configuration [message #520453 is a reply to message #520451] Mon, 22 August 2011 16:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
why is this happening therefore?
It is happening because you haven't set up your services the way I told you to. You still haven't given any information about how your services are set up, but try this:
select inst_id,name from gv$active_services;

on both the primary and the standby. You probably have the orcl11g_svc service running on both.
Detail of the local_listener and remote_listener parameters on both the primary and the standby will help explain how the connections are being made.

(and btw, please use code tags when you include things like tnsnames.ora listings, it makes them much easier to read)
Re: primary don't want be open after standby configuration [message #520541 is a reply to message #516561] Tue, 23 August 2011 06:06 Go to previous messageGo to next message
mrafferty
Messages: 4
Registered: August 2011
Location: Dublin
Junior Member
John, thats for your reponses to my issue.

Output from both databases below regarding content of the gv$active_service tables

As you can see the orcl11g_svc is not running on the DataGuard database. The strange thing is that as I said as the start I have two primary database but I can only seem to get one of the dataguard database working. I have both set up in exactly the same way and the services and the listener details all match for both sets of databases, so there would appear to be some small detail I am missing.

Is there some sort of trace facility I can use to see what path is being followed by a service perhaps.


** Primary **

SQL> select inst_id,name from gv$active_services;

INST_ID NAME
---------- ----------------------------------------------------------------
1 orcl11g_svc
1 orcl11gXDB
1 orcl11g
1 SYS$BACKGROUND
1 SYS$USERS


** DataGuard **

SQL> select inst_id,name from gv$active_services;

INST_ID NAME
---------- ----------------------------------------------------------------
1 SYS$BACKGROUND
1 SYS$USERS
Re: primary don't want be open after standby configuration [message #520564 is a reply to message #520541] Tue, 23 August 2011 08:28 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
First,Quote:
The strange thing is that as I said as the start I have two primary database but I can only seem to get one of the dataguard database working.
This makes no sense. Are you trying to send the redo from two primary databaes to one standby database? That's impossible. You can send the redo from one primary database to several standby databases, is that what you mean?

Second, you say you are using a RAC, but queries given show only one instance. Is it RAC, or not?

Third, to complete the diagnosis, we need to see how your service registration with listeners is configured, on both the primary and the standby:
select inst_id,name,value from gv$parameter where name in ('local_listener','remote_listener');

and then
lsnrctl status

for all the listeners.
[update: typo]

[Updated on: Tue, 23 August 2011 08:30]

Report message to a moderator

Previous Topic: will data guard fail over causes a new incarnation
Next Topic: How to add both online and redo log files to standby databas
Goto Forum:
  


Current Time: Thu Mar 28 04:25:46 CDT 2024