Home » Server Options » Data Guard » Log shipping not happening from Primary DB to Standby DB (Oracle Ora 11.2.0.4.0 Windows 2008 R2 SP1)
Log shipping not happening from Primary DB to Standby DB [message #656856] Thu, 20 October 2016 08:21 Go to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Hi Experts,

We have using SAP PI system with Oracle as database, which is having both Primary and Standby DB.

Recently there was a failover between primary and standby, the failover is caused due to sudden reboot of primary db.
Here the problem is we had switched over, but standby db is not getting logs from Primary no shipping was happening and it was showing like Control file corrupted so we had recovered the DB and control file from primary db.
Now no log shipping is happening between primary and standby after recovering also.

Regards,
Mythili G

Re: Log shipping not happening from Primary DB to Standby DB [message #656857 is a reply to message #656856] Thu, 20 October 2016 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

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

alert_SID.log files on both Primary & Standby should contain clues about the nature of the failure
Re: Log shipping not happening from Primary DB to Standby DB [message #656861 is a reply to message #656857] Thu, 20 October 2016 08:35 Go to previous messageGo to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Primary
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 5 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 6 CLOSING
ARCH ARCH 7 CLOSING
ARCH ARCH 30 CLOSING
ARCH ARCH 31 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 13 CLOSING
ARCH ARCH 14 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 15 CLOSING
ARCH ARCH 16 CLOSING
ARCH ARCH 17 CLOSING
ARCH ARCH 18 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 22 CLOSING
ARCH ARCH 23 CLOSING
ARCH ARCH 24 CLOSING
ARCH ARCH 25 CLOSING
ARCH ARCH 26 CLOSING

30 rows selected.
Standby
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STA

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
MRP0 N/A 6 WAIT_FOR_GAP

31 rows selected.

In Primary there should be LNS Process in writing status, but after failover , LNS is not coming
Re: Log shipping not happening from Primary DB to Standby DB [message #656862 is a reply to message #656856] Thu, 20 October 2016 08:36 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
You need to be a bit more precise: did you failover or switchover? If failover, yopu'll need to reinstate the old primary as a standby.
Either way, an obvious first thing to try is

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
Re: Log shipping not happening from Primary DB to Standby DB [message #656872 is a reply to message #656862] Fri, 21 October 2016 01:19 Go to previous messageGo to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Hi..
Thanks for your reply.

Actually my primary DB server was rebooted by the networks team without informing to my team at the time of reboot standby has became as primary no idea what happened in between, when we switched over to normal primary and standby log shipping is not happening and we had also restored the standby DB.

After giving this command "ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;"
whether any restart of the DB is required?

Regards,
Mythili G.
Re: Log shipping not happening from Primary DB to Standby DB [message #656873 is a reply to message #656872] Fri, 21 October 2016 01:31 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
Do you understand the difference between "failover" and "switchover"? Your subsequent actions may be dependent on the difference.

As to implementing my suggestion, I am surprised that you have not tried it, or at least researched it if you don't trust me (no reason why you should trust me Smile ). The purpose of standby is to minimize data loss, and right now you vulnerable to a degree of loss that is continually increasing. This is a potentially disastrous situation that you really should address with some urgency.
Re: Log shipping not happening from Primary DB to Standby DB [message #656877 is a reply to message #656873] Fri, 21 October 2016 02:00 Go to previous messageGo to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
It is not like i don't trust the solution given by you, this is the production system i just want to confirm if any restart of the db is required after executing this command.
If we need to do any restart we have to plan it after the business hours.

Regards,
Mythili G.
Re: Log shipping not happening from Primary DB to Standby DB [message #656878 is a reply to message #656877] Fri, 21 October 2016 02:10 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
SAP on Data Guard would usually be considered an important environment. Your client should consider buying some consulting support to sort out the current problem and some training so that you can administer it in future. If you tell them that their mission-critical and very expensive system is vulnerable to loss, I am sure they will do this.

For my immediate suggestion of setting parameter, you can check whether the parameter is static or dynamic. HAve you read up on what it actually does yet?
http://docs.oracle.com/database/121/REFRN/GUID-983A9C52-3046-4286-AEA7-800741EE0561.htm#REFRN10087
Re: Log shipping not happening from Primary DB to Standby DB [message #656880 is a reply to message #656878] Fri, 21 October 2016 04:21 Go to previous messageGo to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Thank you John, i had gone through the link which you has sent.

Will take the approval of my manager and implement the solution shared by you.
Hope it will fix my issue.

Regards,
Mythili G
Re: Log shipping not happening from Primary DB to Standby DB [message #656977 is a reply to message #656880] Tue, 25 October 2016 00:07 Go to previous messageGo to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Hi John,

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; has worked and the logs are shipping now.

but there is a huge gap MRP process is in waiting status only. I has checked for the gap is


THREAD# LowGap# HighGap#
---------- ---------- ----------
1 7 70

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from v$database;

NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ -------------------- ---------------- --------------------
PI3 PI3_p READ WRITE PRIMARY UNRESOLVABLE GAP

how to resolve this unresolvable gap.

FAL parameter is active but i could see error as below.
Trace file E:\ORACLE\PI3\SAPTRACE\diag\rdbms\pi3_p\pi3\trace\pi3_fal_4260.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 8 - type 8664, 8 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:11730M/16383M, Ph+PgF:35156M/41381M
VM name : VMWare Version (6)
Instance name: pi3
Redo thread mounted by this instance: 1
Oracle process number: 71
Windows thread id: 4260, image: ORACLE.EXE (SHAD)


*** 2016-10-25 05:35:31.449
*** SESSION ID:(354.5267) 2016-10-25 05:35:31.449
*** CLIENT ID:() 2016-10-25 05:35:31.449
*** SERVICE NAME:(PI3_p) 2016-10-25 05:35:31.449
*** MODULE NAME:(ORACLE.EXE) 2016-10-25 05:35:31.449
*** ACTION NAME:() 2016-10-25 05:35:31.449

FAL Redo Shipping Client Established Network Login
Failed to queue the whole gap
GAP - thread 1 sequence 6-70
DBID 1471204172 branch 925468071

Regards,
Mythili G
Re: Log shipping not happening from Primary DB to Standby DB [message #656978 is a reply to message #656977] Tue, 25 October 2016 00:31 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
THe necessary archive logs are missing. So drop the standby and create it again. Next time you could use the Data Guard Broker to automate all of this.
Re: Log shipping not happening from Primary DB to Standby DB [message #657018 is a reply to message #656978] Wed, 26 October 2016 04:57 Go to previous messageGo to next message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Hi John,

Thank you for the solution, now both the logs are getting shipped.

I have one more query,

[color=royalblue]DGMGRL> show configuration

Configuration - dgconfigpi3

Protection Mode: MaxPerformance
Databases:
pi3_p - Primary database
Warning: ORA-16829: fast-start failover configuration is lagging

pi3_s - (*) Physical standby database (disabled)
ORA-16795: the standby database needs to be re-created


Fast-Start Failover: ENABLED

Configuration Status:
WARNING

is this anything like configuration file curroupted?
How to proceed on this?

Regards,
Mythili G.
Re: Log shipping not happening from Primary DB to Standby DB [message #657027 is a reply to message #657018] Wed, 26 October 2016 07:12 Go to previous messageGo to next message
John Watson
Messages: 7149
Registered: January 2010
Location: Global Village
Senior Member
Mythili, you have to learn to read. I said this,
Quote:
drop the standby and create it again
Data Guard Broker says this,
Quote:
the standby database needs to be re-created
Does that give you an idea of what you should do?

However, there is something you should do first. You should inform your employer that his very expensive and (presumably) mission critical database is, right now, vulnerable to a loss of data and downtime in the event of any problems. You should also explain that you are not at the moment competent to deal with this, and that he should hire a consultant to sort out the mess while you attend a course to upgrade your skills. In the meantime, you might want to consider shutting down the database. You are operating in a risky situation.
Re: Log shipping not happening from Primary DB to Standby DB [message #657350 is a reply to message #657027] Mon, 07 November 2016 06:23 Go to previous message
Mythili Gopisetty
Messages: 8
Registered: October 2016
Junior Member
Dear John,

Thank you for helping out, the solutions which you has given has worked.

My primary and standby DB both are in sync now.

Regards,
Mythili G.
Previous Topic: Unable to duplicate datafile of "SYSTEM" tablespace
Next Topic: Activate 2nd standby database as primary
Goto Forum:
  


Current Time: Tue Nov 21 04:23:25 CST 2017

Total time taken to generate the page: 0.01932 seconds