Home » Server Options » Data Guard » switchover fails becoz of ORA-12514: TNS:listener does not currently (10.2.0.4.0, Microsoft Windows IA (32-bit))
switchover fails becoz of ORA-12514: TNS:listener does not currently [message #519269] Wed, 10 August 2011 11:28 Go to next message
kytemanaic
Messages: 45
Registered: February 2009
Member
Hi

here's my rdbms version and os:

SYS@cta>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SYS@cta>select platform_id, platform_name from v$database;

PLATFORM_ID
-----------
PLATFORM_NAME
----------------------------------------------------------------

          7
Microsoft Windows IA (32-bit)


I've perform switch over twice but it always fails.Sad

my configuration

primary:ctastandby
physical standby:cta

here's my dgmgrl configuration for cta

DGMGRL> show database verbose 'cta';

Database
  Name:            cta
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    cta

  Properties:
    InitialConnectIdentifier        = 'cta_primary'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'ORACLE_TEST'
    SidName                         = 'cta'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE_TEST)
(PORT=1521))'
    StandbyArchiveLocation          = 'D:\Ora102\cta\arc1'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%s_%t_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "cta":
SUCCESS



here's my dgmgrl for ctastd

DGMGRL> show database verbose 'ctastd';

Database
  Name:            ctastd
  Role:            PRIMARY
  Enabled:         YES
  Intended State:  ONLINE
  Instance(s):
    ctastd

  Properties:
    InitialConnectIdentifier        = 'cta_standby'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    Dependency                      = ''
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '180'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'STANDBY'
    SidName                         = 'ctastd'
    LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(POR
T=1521))'
    StandbyArchiveLocation          = 'D:\Ora102\cta\arc1'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%s_%t_%r.arc'
    LatestLog                       = '(monitor)'
    TopWaitEvents                   = '(monitor)'

Current status for "ctastd":
SUCCESS



here's what happen for switchover in dgmgrl


DGMGRL> switchover to 'cta';
Performing switchover NOW, please wait...
Operation requires shutdown of instance "ctastd" on database "ctastd"
Shutting down instance "ctastd"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "cta" on database "cta"
Shutting down instance "cta"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "ctastd" on database "ctastd"
Starting instance "ctastd"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "ctastd"
You must start instance "ctastd" manually
Operation requires startup of instance "cta" on database "cta"
You must start instance "cta" manually
Switchover succeeded, new primary is "cta"
DGMGRL>


as it is very obviously that if fails coz I need to startup cta and ctastd manually.

from

http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/install.htm#BABECEJC

Quote:


To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain. For example, in the LISTENER.ORA file:

LISTENER = (DESCRIPTION =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
(PORT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
(ORACLE_HOME=oracle_home)))





so here's my listener.ora for cta


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\ora102)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = CTA_DGMGRL)
      (ORACLE_HOME = C:\ora102)
      (SID_NAME = CTA)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_test)(PORT = 1521))
    )
  )




here's my listener.ora for ctastd


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = c:\ora102)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ctastd_dgmgrl)
      (ORACLE_HOME = C:\ora102)
      (SID_NAME = ctastd)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
  )




since the error in dgmgrl state that it might be due to tnsname.ora, so I will also show my tnsnames.ora which is the same in both cta and ctastd

CTA_PRIMARY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_test)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cta)
    )
  )

CTA_STANDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CTASTD)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )



here's my alert log


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
MRP0 started with pid=25, OS id=4040
Wed Aug 10 14:47:15 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 3892
RFS[3]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Successfully opened standby log 4: 'D:\ORA102\CTA\REDO04.LOG'
Wed Aug 10 14:47:16 2011
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 2 processes
Wed Aug 10 14:47:17 2011
Waiting for all non-current ORLs to be archived...
Media Recovery Log D:\ORA102\CTA\ARC1\181_1_718625845.ARC
Wed Aug 10 14:47:17 2011
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Wed Aug 10 14:47:18 2011
Media Recovery Log D:\ORA102\CTA\ARC1\182_1_718625845.ARC
Media Recovery Waiting for thread 1 sequence 183 (in transit)
Wed Aug 10 14:47:18 2011
Recovery of Online Redo Log: Thread 1 Group 4 Seq 183 Reading mem 0
  Mem# 0: D:\ORA102\CTA\REDO04.LOG
Media Recovery Waiting for thread 1 sequence 184
Wed Aug 10 14:47:24 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[4]: Assigned to RFS process 4044
RFS[4]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Successfully opened standby log 4: 'D:\ORA102\CTA\REDO04.LOG'
Wed Aug 10 14:47:29 2011
Recovery of Online Redo Log: Thread 1 Group 4 Seq 184 Reading mem 0
  Mem# 0: D:\ORA102\CTA\REDO04.LOG
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET log_archive_dest_1='location="D:\Ora102\cta\arc1"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='cta';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='cta';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET standby_archive_dest='D:\Ora102\cta\arc1' SCOPE=BOTH SID='cta';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='cta';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET log_archive_format='%s_%t_%r.arc' SCOPE=SPFILE SID='cta';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=STANDBY)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ctastd_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Wed Aug 10 14:47:31 2011
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=ORACLE_TEST)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=cta_XPT)(INSTANCE_NAME=cta)(SERVER=dedicated)))' SCOPE=BOTH;
Wed Aug 10 14:47:31 2011
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
MRP0: Background Media Recovery cancelled with status 16037
Wed Aug 10 14:47:31 2011
Errors in file d:\ora102\cta\bdump\cta_mrp0_4040.trc:
ORA-16037: user requested cancel of managed recovery operation


tracefile for d:\ora102\cta\bdump\cta_mrp0_4040.trc:

*** SERVICE NAME:() 2011-08-10 14:47:11.984
*** SESSION ID:(141.1) 2011-08-10 14:47:11.984
*** 2011-08-10 14:47:11.984 62692 kcrr.c
MRP0: Background Managed Standby Recovery process started
*** 2011-08-10 14:47:16.984 1118 krsm.c
Managed Recovery: Initialization posted.
*** 2011-08-10 14:47:16.984 62692 kcrr.c
Managed Standby Recovery starting Real Time Apply
Recovery target incarnation = 2, activation ID = 245492625
Influx buffer limit = 7808 (50% x 15616)
Successfully allocated 2 recovery slaves
Using 545 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 508181665 logseq 181 block 1923
*** 2011-08-10 14:47:17.218
Media Recovery add redo thread 1
*** 2011-08-10 14:47:17.218 1118 krsm.c
Managed Recovery: Active posted.
*** 2011-08-10 14:47:17.437
Media Recovery Log D:\ORA102\CTA\ARC1\181_1_718625845.ARC
*** 2011-08-10 14:47:18.015
Media Recovery Log D:\ORA102\CTA\ARC1\182_1_718625845.ARC
*** 2011-08-10 14:47:18.390
Media Recovery of Online Log [Thread=1, Seq=183]
*** 2011-08-10 14:47:18.390
Recovery of Online Redo Log: Thread 1 Group 4 Seq 183 Reading mem 0
MRP: Prodding archiver at standby for thread 1 seq 183
*** 2011-08-10 14:47:24.499 62692 kcrr.c
Media Recovery Waiting for thread 1 sequence 184
*** 2011-08-10 14:47:29.499
Media Recovery of Online Log [Thread=1, Seq=184]
*** 2011-08-10 14:47:29.499
Recovery of Online Redo Log: Thread 1 Group 4 Seq 184 Reading mem 0
*** 2011-08-10 14:47:31.499 62692 kcrr.c
MRP0: Background Media Recovery cancelled with status 16037
ORA-16037: user requested cancel of managed recovery operation
*** 2011-08-10 14:47:31.499 62692 kcrr.c
Managed Standby Recovery not using Real Time Apply
MRP: Prodding archiver at standby for thread 1 seq 184
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 66Kb in 14.28s => 0.00 Mb/sec
Total physical reads: 66Kb
Longest record: 2Kb, moves: 0/224 (0%)
Change moves: 66/298 (22%), moved: 0Mb
Longest LWN: 8Kb, moves: 0/44 (0%), moved: 0Mb
Last redo scn: 0x0000.1e4a4464 (508183652)
----------------------------------------------
*** 2011-08-10 14:47:31.499
Media Recovery drop redo thread 1
*** 2011-08-10 14:47:34.077 1118 krsm.c
Managed Recovery: Not Active posted.
ORA-16037: user requested cancel of managed recovery operation
*** 2011-08-10 14:47:34.109 62692 kcrr.c
MRP0: Background Media Recovery process shutdown
*** 2011-08-10 14:47:34.109 1118 krsm.c


here's my next part of oracle alert log

Shutting down Data Guard Broker processes
Wed Aug 10 14:53:54 2011
Completed: Data Guard Broker shutdown
Wed Aug 10 14:53:56 2011
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Aug 10 14:53:56 2011
Errors in file d:\ora102\cta\bdump\cta_ora_1248.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [__VInfreq__skgmdetach+200] [PC:0x438C30C] [ADDR:0x5EB2708] [UNABLE_TO_READ] []

Wed Aug 10 14:53:56 2011
Errors in file d:\ora102\cta\bdump\cta_ora_2792.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [__VInfreq__skgmdetach+200] [PC:0x438C30C] [ADDR:0x5EB2740] [UNABLE_TO_READ] []

Wed Aug 10 14:53:56 2011
Errors in file d:\ora102\cta\bdump\cta_ora_1396.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [__VInfreq__skgmdetach+200] [PC:0x438C30C] [ADDR:0x5EB2698] [UNABLE_TO_READ] []

Wed Aug 10 14:53:56 2011
Errors in file d:\ora102\cta\bdump\cta_ora_2848.trc:
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [__VInfreq__skgmdetach+200] [PC:0x438C30C] [ADDR:0x5EB26D0] [UNABLE_TO_READ] []



here's the log for 1396.trc


ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [__VInfreq__skgmdetach+200] [PC:0x438C30C] [ADDR:0x5EB2698] [UNABLE_TO_READ] []
Current SQL information unavailable - no SGA.
check trace file c:\ora102\rdbms\trace\cta_ora_0.trc for preloading .sym file messages
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
__VInfreq__skgmdeta           00000000             
ch+200                                             
_ksmdsgi+153         CALLrel  _skgmdetach+0        694FC14 1C1F30 5EB2680 1C8AA4
                                                   1D3558 0 1CF9B0 1D3558 0
_ksmdsg+30           CALLrel  _ksmdsgi+0           1
_ksudlp+972          CALLrel  _ksmdsg+0            
_opidcl+426          CALLrel  _ksudlp+0            6D250734 2
_opidrv+1017         CALLrel  _opidcl+0            1C8990 0
_sou2o+45            CALLrel  _opidrv+0            32 4 694FEA4
_opimai_real+227     CALLrel  _sou2o+0             694FE98 32 4 694FEA4
_opimai+92           CALLrel  _opimai_real+0       3 694FED0
_BackgroundThreadSt  CALLrel  _opimai+0            
art@4+495                                          
7C80B508             CALLreg  00000000             



although this might required an oracle support, it might not be so since data guard broker is down such access error log is still possible.

next will be my drcta log


DG 2011-08-10-14:46:45        0 2 0 NSV1: Failed to connect to remote database ctastd. Error is ORA-12514
DG 2011-08-10-14:46:45        0 2 0 NSV1: Failed to send message to site ctastd. Error code is ORA-12514.
DG 2011-08-10-14:46:48        0 2 758818002 DMON: cta unable to contact primary for DRC version check
DG 2011-08-10-14:46:48        0 2 758818002 DMON: status ORA-12514: 


next will be my drcta log will be the next portion of drcta.log

DG 2011-08-10-14:52:50        0 2 0 NSV1: Failed to connect to remote database ctastd. Error is ORA-12518
DG 2011-08-10-14:52:50        0 2 0 NSV1: Failed to send message to site ctastd. Error code is ORA-12518.
DG 2011-08-10-14:52:50  1000000 3 758818057 DMON: Database ctastd unable to answer a PROBE, status=ORA-12518. Retry later.
DG 2011-08-10-14:53:06  1000000 3 758818057 DMON: Entered rfm_get_chief_lock() for CTL_SWITCH, reason 0
DG 2011-08-10-14:53:06  1000000 3 758818057 DMON: chief lock convert for switchover



I don't really understand why the above fails, reason being since data guard is working in the first place, ,i.e that will mean that there should not be any connection issues or errors with tnsname.ora, which puzzles me.

have gone through troubleshooting steps in http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/troubleshooting.htm#i1009813

but seems that does not help me to resolve the issue.

thanks a lot!

can anyone kindly enlighten me how to trobleshoot this issues?

thanks
Re: switchover fails becoz of ORA-12514: TNS:listener does not currently [message #519272 is a reply to message #519269] Wed, 10 August 2011 11:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10615
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

(GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)

As docos say, you need to provide the domain information.
Re: switchover fails becoz of ORA-12514: TNS:listener does not currently [message #519304 is a reply to message #519272] Wed, 10 August 2011 22:37 Go to previous messageGo to next message
kytemanaic
Messages: 45
Registered: February 2009
Member
Problem is db_domain is empty.
Re: switchover fails becoz of ORA-12514: TNS:listener does not currently [message #519306 is a reply to message #519304] Wed, 10 August 2011 23:05 Go to previous messageGo to next message
BlackSwan
Messages: 22554
Registered: January 2009
Senior Member
>Problem is db_domain is empty.
is COPY & PASTE broken for you?
Re: switchover fails becoz of ORA-12514: TNS:listener does not currently [message #519320 is a reply to message #519304] Thu, 11 August 2011 01:30 Go to previous message
John Watson
Messages: 4411
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Problem is db_domain is empty.
Could it be that even though the parameter isn't set, the database's global name includes a domain?
select * from global_name;
I don't know what would happen of they are mis-matched.
Previous Topic: Last Archived log's information.
Next Topic: Why new physical standby database cannot received redo log after fail o ver
Goto Forum:
  


Current Time: Fri Aug 01 02:15:43 CDT 2014

Total time taken to generate the page: 0.10560 seconds