on 08-04-2015 12:34 AM
Hello friends,
I am new to dataguard concept and I am facing the above issue on a system .
Please find the details below.
--------------------------------------------------------------
DGMGRL> show configuration
Configuration - SID
Protection Mode: MaxAvailability
Databases:
SID_HOST1 - Primary database
SID_HOST2 - Physical standby database
Error: ORA-16766: Redo Apply is stopped
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
----------------------------------------------------------------------
DGMGRL> show database SID_STDBYHOST
Database - SID_STDBYHOST
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 11 hours 6 minutes 15 seconds (computed 0 seconds ago)
Apply Rate: 18.35 MByte/s
Real Time Query: OFF
Instance(s): SID
Database Error(s):
ORA-16766: Redo Apply is stopped
Database Status:
ERROR
-------------------------------------------------------------------
DGMGRL> show database verbose SID_STDBYHOST
Database - SID_HOSTSTDBY
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 11 hours 8 minutes 22 seconds (computed 0 seconds ago)
Apply Rate: 18.35 MByte/s
Real Time Query: OFF
Instance(s)
SID
Database Error(s):
ORA-16766: Redo Apply is stopped
Properties:
DGConnectIdentifier = 'sid_stdbyhost'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '14'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = 'sid_primaryhost'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'SID'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=STDBYHOST)(PORT=1519))(CONNECT_DATA=(SERVICE_NAME=SID_HOST_DGMGRL)(INSTANCE_NAME=SID)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/oracle/SID/oraarch/SIDarch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
ERROR
pLEASE FIND OTHER DETAILS in the screenshots attached.
Please help me in making thye DB's consistent.
As per this blog , I found out that
alter database recover managed standby database disconnect from session;
could help me.
Please get back to me.
Thanks and regards,
Uday.
Hi,
first, you must check the alert log. (alert_<SID>.log from primary and standby)
it could be new datafile cannot be created or an archivelog not copied to the destination.
if it is just an archivelog not copied, start sql command on standby :
select sequence#,applied from v$archived_log order by sequence#;
copy the missing archivelog (make sure to have same owner and group access setting to these files)
and start those commands :
>rman target /
>CATALOG START WITH '/oracle/<SID>/oraarch/';
accept and quit.
and restart apply
>dgmgrl
DGMGRL> connect sys@<standby>;
DGMGRL> edit database '<standby>' set state='APPLY-OFF';
DGMGRL> edit database '<standby>' set state='APPLY-ON';
check alert log in parallel.
Regards,
Rodolphe ALT
http://moncv.altr-consulting.com
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Rudolph,
Thanks for the reply.
1.) As shown below the status now is :
------------------------------
Protection Mode: MaxAvailability
Databases:
SID_PRIMARYHOST - Primary database
Error: ORA-16810: multiple errors or warnings detected for the database
SID_SECHOST - Physical standby database
Warning: ORA-16792: configurable property value is inconsistent with database setting
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
--------------------------------------
2.) Also, the command
select sequence#,applied from v$archived_log order by sequence#;
PRIMARY gave many rows and there are quite a few with status NO which means they are not applied.
In standby , the command gave lesser sequence numbers ALL with YES status.
There is a file with RECOVER status which in STANDBY BUT NOT IN PRIMARY.
-------------standby
SQL> SELECT DISTINCT STATUS,count(1) from v$datafile group by status;
STATUS COUNT(1)
-----------------
ONLINE 442
RECOVER 1
SYSTEM 1
-----------------PRIMARY
SQL> SELECT DISTINCT STATUS,count(1)from v$datafile group by status;
STATUS COUNT(1)
------- ----------
ONLINE 445
SYSTEM 1
------------------------------------------------
I came to know that this warning "ORA-16792: configurable property value is inconsistent with database setting" goes with restart of the broker.
--> alter system set dg_broker_start=false scope=both;
--> alter system set dg_broker_start=true scope=both;
Please help me in solving , more importantly UNDERSTANDING WAHT'S GOING ON.
Thanks in advance,
Uday
Ok, now we will try to repair the issue about recover datafile,
please start this command :
SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);
and could you please check the alert log on both systems.
some errors from these alert log can help me to understand major problem.
Hello Rudolph,
Unfortunately the alert log file on both primary and standby are not up to date , i mean, the last log in the files on both hosts are dated 27th july.
Plesae find the output of the command below.
-------------------------------------------------------------
SQL> SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
FROM V$DATAFILE_HEADER
WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL); 2 3
FILE# STATUS
---------- -------
ERROR REC
----------------------------------------------------------------- ---
TABLESPACE_NAME
------------------------------
NAME
--------------------------------------------------------------------------------
220 ONLINE
FILE MISSING
-----------------------------------------------------------------
Thanks again.
Uday.
Just to add , the last update in the alert log file for 27th july is when someone in our team enabled FSFO --> primary went down --> SAP did not come up due to problems with HA config problems.
----------------standby
Sat Jun 27 18:40:00 2015
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 2
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sat Jun 27 18:40:03 2015
Stopping background process VKTM
Sat Jun 27 18:40:06 2015
Instance shutdown complete
-----------------------------primary
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sat Jun 27 18:18:43 2015
Stopping background process VKTM
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Sat Jun 27 18:18:46 2015
Instance shutdown complete
---------------------------------------------------------------
Regards,
Uday
1.
Then on the Primary find that file:
SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;
Back on the Standby find the file:
SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;
2.
from oracle trc files, search keyword "ORA-01110" or "UNNAMED".
The issue is probably STANDBY_FILE_MANAGEMENT related, but could be a disk space or incorrect path issue too.
3.
from alert.log, I understand oracle db primary and standby are shutdown.
right ?
if oracle db is open, these alert log cannot be right.
Hello Rudolph,
---------------------------------------------------------------------------------------------------------------------standby
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;
FILE#
----------
NAME
--------------------------------------------------------------------------------
220
/oracle/SID/112_64/dbs/UNNAMED00220
--------------------------------------------------------------------------------------------------------------------PRIMARY
SQL> SELECT FILE#,NAME FROM V$DATAFILE WHERE FILE#=220;
FILE#
----------
NAME
--------------------------------------------------------------------------------
220
/oracle/SID/sapdata28/sr3740_1/sr3740.data1
------------------------------------------------------------------------------------------------------------------------
--> The primary database is up now - so is SAP ; We had issue on 27th july when system went down because FSFO was enabled and SAP couldnt start on standby due to Heartbeat issues (FSFO was in status DISABLED before that)
--------------------------------------------------------------------------
-------------------------------Log from drc_SID.log
08/04/2015 18:21:55
Error: The actual protection level 'RESYNCHRONIZATION' is different from the configured protection mode 'MAXIMUM AVAILABILITY'.
Found unresolvable gap to database SID_STANDBY.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration SID Warning ORA-16607
Primary Database SID_PRIMARY Error ORA-16810
Physical Standby Database SID_STANDBY Warning ORA-16792
08/04/2015 18:22:01
Error: The actual protection level 'RESYNCHRONIZATION' is different from the configured protection mode 'MAXIMUM AVAILABILITY'.
Found unresolvable gap to database SID_STANDBY.
------------------------------------------
ALSO these are some lines from LGWR TRACE FILE.
--------------------
ORA-16198: LGWR received timedout error from KSR
ERROR: Received error on receiving channel message 16198
Error 16198 detaching RFS from standby instance at host 'sid_standby'
*** 2015-08-03 17:53:07.737 7160 krsu.c
Making upidhs request to NSS2 (ocis 0x7ff09fa25a88). Begin time is <08/03/2015 17:52:37> and NET_TIMEOUT <30> seconds
NetServer pid:20168
*** 2015-08-03 17:53:07.737 4329 krsh.c
LGWR: Error 16198 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'sid_standby'
krsl_reap_io: ignoring detach error 16198
*** 2015-08-03 17:53:07.737 2917 krsi.c
krsi_dst_fail: dest:2 err:16198 force:0 blast:1
------------------------------------------------
Plesae find the alert log attached.
Uday
ok, thanks.
now I can identify the reason of recover status file.
you cannot continue to apply archivelog to standby if this issue is not solved.
as wrote previously :
The issue is probably STANDBY_FILE_MANAGEMENT related, but could be a disk space or incorrect path issue too.
from oracle trc files, you must have the explication about this unamed file. Do you know if oracle parameter "standby_file_management" was set to AUTO ?
(it is better)
to solve this issue :
make sure the filesystem have freespace avalaible to standby.
create manually folder with ora<sid> user :
mkdir -p /oracle/SID/sapdata28/sr3740_1/
DGMGRL> edit database <standby> set state='APPLY-OFF';
SQL> select name from v$datafile where name like '%AMED%';
SQL> alter system set standby_file_management='MANUAL';
SQL> alter database create datafile '/oracle/SID/112_64/dbs/UNNAMED00220' as '/oracle/SID/sapdata28/sr3740_1/sr3740.data1';
SQL> alter system set standby_file_management='AUTO';
DGMGRL> edit database <standby> set state='APPLY-ON';
DGMGRL> show configuration;
check alert log to follow the next activity.
and next to enable FSFO, try to follow this tuto.
Regards,
Rodolphe
Hello Rudolph,
As shown below, the parameter is AUTO now.
-------------------
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
------------------
The oraarch is filled up completely ; rest are ok.
I will get back to you with the output in some time.
Thanks Rudolphe.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.