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.
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