Home » Server Options » Data Guard » no output from v$archived_log in standby (oracle 11.2.0.1 EE, RHEL 5)
icon5.gif  no output from v$archived_log in standby [message #650455] Mon, 25 April 2016 12:54 Go to next message
AKDB
Messages: 3
Registered: April 2016
Location: kolkata
Junior Member
Hello,

I am trying to configure physical standby on rhel 5, oracle 11GR2 EE. My standby db is not in sync with primary. While i try to troubleshoot, I do not get output from v$archived_log view in standby.

On Primary:
SQL> SELECT sequence#, first_time, next_time
FROM v$archived_log
ORDER BY sequence#; 2 3

SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
47 11-DEC-2015 18:25:48 11-DEC-2015 18:53:39
48 11-DEC-2015 18:53:39 18-DEC-2015 13:05:08
49 18-DEC-2015 13:05:08 18-DEC-2015 20:51:21
50 18-DEC-2015 20:51:21 18-DEC-2015 20:51:22
51 18-DEC-2015 20:51:22 19-DEC-2015 18:29:58
52 19-DEC-2015 18:29:58 19-DEC-2015 18:30:01
53 19-DEC-2015 18:30:01 19-DEC-2015 18:52:13
54 19-DEC-2015 18:52:13 19-DEC-2015 18:54:53
55 19-DEC-2015 18:54:53 19-DEC-2015 20:22:31
56 19-DEC-2015 20:22:31 23-DEC-2015 01:24:16
57 23-DEC-2015 01:24:16 23-DEC-2015 19:14:52

SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------------- --------------------
58 23-DEC-2015 19:14:52 23-DEC-2015 19:18:48
59 23-DEC-2015 19:18:48 23-DEC-2015 19:32:53
60 23-DEC-2015 19:32:53 23-DEC-2015 19:32:55
61 23-DEC-2015 19:32:55 23-DEC-2015 19:32:57
62 23-DEC-2015 19:32:57 25-APR-2016 21:35:45
63 25-APR-2016 21:35:45 25-APR-2016 21:54:34


On standby:
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#; 2 3

no rows selected

Could someone suggest me please what am I doing wrong? Thanks for your time.
Re: no output from v$archived_log in standby [message #650456 is a reply to message #650455] Mon, 25 April 2016 13:25 Go to previous messageGo to next message
BlackSwan
Messages: 25721
Registered: January 2009
Location: SoCal
Senior Member
>62 23-DEC-2015 19:32:57 25-APR-2016 21:35:45
>63 25-APR-2016 21:35:45 25-APR-2016 21:54:34

was this DB down & offline for about 4 months?

what clues exist within alert_SID.log file for both PRIMARY & STANDBY?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: no output from v$archived_log in standby [message #650457 is a reply to message #650456] Mon, 25 April 2016 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 65207
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And when was created the standby?

Re: no output from v$archived_log in standby [message #650593 is a reply to message #650456] Thu, 28 April 2016 03:11 Go to previous messageGo to next message
AKDB
Messages: 3
Registered: April 2016
Location: kolkata
Junior Member
Hello Blackswan,

Yes the primary and the standby db was down for almost 4 months. In this dataguard setup - opl@vm2 (primary) and opl2@vm3 (standby).

The alert log of primary db is filled with heartbeat failure.

Alert log of Primary db (opl)
--------------------------------------------------------------------------------------
OCIServerAttach failed -1
.. Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
OCIServerAttach failed -1
.. Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
OCIServerAttach failed -1
.. Detailed OCI error val is 12541 and errmsg is 'ORA-12541: TNS:no listener
'
*** 2016-04-28 11:37:44.391 4132 krsh.c
Error 12541 received logging on to the standby
*** 2016-04-28 11:37:44.391 4132 krsh.c
Check whether the listener is up and running.
*** 2016-04-28 11:37:44.391 869 krsu.c
Error 12541 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'opl2'
Error 12541 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'opl2'
ORA-12541: TNS:no listener
*** 2016-04-28 11:37:44.391 4132 krsh.c
PING[ARC1]: Heartbeat failed to connect to standby 'opl2'. Error is 12541.
-------------------------------------------------------------------------------------

I am able to do tnsping the standby db from primary and vice versa:

[oracle@vm2 ~]$ tnsping opl2

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-APR-2016 13:22:39

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vm3.astron.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = opl2)))
OK (90 msec)

[oracle@vm3 opl]$ tnsping opl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 28-APR-2016 13:23:54

Copyright (c) 1997, 2009, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = vm2.astron.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = opl)))
OK (80 msec)

listeners on both the servers are up.

Alert log at standby:
----------------------

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (opl)
Mon Apr 25 21:56:53 2016
MRP0 started with pid=24, OS id=6473
MRP0: Background Managed Standby Recovery process started (opl)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 54
Completed: alter database recover managed standby database disconnect from session
Re: no output from v$archived_log in standby [message #650594 is a reply to message #650457] Thu, 28 April 2016 03:16 Go to previous message
AKDB
Messages: 3
Registered: April 2016
Location: kolkata
Junior Member
Hello Michel Cadot,

On 11-Dec-15, the standby has created:

SQL> select db_unique_name, created, database_role from v$database;

DB_UNIQUE_NAME CREATED DATABASE_ROLE
------------------------------ --------- ----------------
opl2 11-DEC-15 PHYSICAL STANDBY
Previous Topic: Client connections slow in failover situation
Next Topic: Converting ASM to NonASM Standby
Goto Forum:
  


Current Time: Fri Nov 24 12:47:54 CST 2017

Total time taken to generate the page: 0.02011 seconds