Home » Server Options » Data Guard » Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted (Oracle 12.1)
Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684463] |
Thu, 10 June 2021 16:51 |
LaFilipina
Messages: 51 Registered: May 2007 Location: spain
|
Member |
|
|
Hi,
We have an Oracle 12.1 RAC One Node database where primary database is running on GRID 12.1 cluster and the standby database running on Oracle 12.1 and GRID 19c cluster.
We have a RAC OneNode database with a physical standby database. It was running as db_1 (instance 1) and was relocated to the other node as db_2 (instance 2).
For some reason, there are still archive log entries in v$archived_log view that were already applied to the standby database and were generated from when the database was still running in instance 1. These applied archive logs are more than 2 weeks old now and I suspect that since the database is running as instance 2, these archivelog entries in v$archived_log is not getting deleted. The 'DELETED' column indicates that they are not yet deleted BUT the physical file are already deleted (checked in the archive log destination and no archivelog files in there that are dated as the completion time of these archivelogs in question in v$archived_log.
Because of this inconsistency, I suspect that the logical standby creation on this database is hanging on the part where the command alter database recover managed standby until change is issued.
Is there a way to tell the database of data guard that these records should be marked as deleted or get removed from v$archived_log?
This command also gives a suspect result:
SQL> select thread#, max(sequence#) from v$archived_log where APPLIED='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 1445207
2 2650537
I don't suppose I should see both thread# in there when the database is running as RAC OneNode and as instance 2.
Please give some advice.
Thanks.
|
|
|
|
|
|
|
|
Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684504 is a reply to message #684502] |
Thu, 17 June 2021 02:42 |
|
Michel Cadot
Messages: 68732 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Your output gives AL that are already applied, I don't see why your standby db is trying to fetch them.
Post the result of the following statements executed on the standby database (using SQL*Plus):
col inst_id format 999 heading INST
select inst_id, thread#, low_sequence#, high_sequence#
from gv$archive_gap
order by inst_id, thread#
/
Set lines 125
Col timest format a14 heading TIMESTAMP
Col message format a90 trunc
Col severity format a07 trunc
Col facility format a03
Select inst_id, to_char(timestamp, 'DD/MM HH24:MI:SS') timest,
decode(facility,
'Crash Recovery', 'CR',
'Data Guard', 'DG',
'Fetch Archive Log', 'FAL',
'Log Apply Services', 'LAS',
'Log Transport Services', 'LTS',
'Network Services', 'NS',
'Remote File Server', 'RFS',
'Role Management Services', 'RMS',
substr(facility,1,3)
) facility,
message, severity
from gv$dataguard_status
where severity >= 3 and timestamp >= trunc(sysdate)
order by inst_id, timestamp
/
Don't forget to format your output as explained in How to use [code] tags and make your code easier to read.
|
|
|
Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684506 is a reply to message #684503] |
Thu, 17 June 2021 11:06 |
LaFilipina
Messages: 51 Registered: May 2007 Location: spain
|
Member |
|
|
John Watson wrote on Thu, 17 June 2021 01:34Can you remove the archivelogs from the RMAN repository?
DELETE FORCE ARCHIVE LOG....
or perhaps
DELETE FORCE FOREIGN ARCHIVELOG...
should do it.
I tried to check if the generated archive logs are still in the repository but they aren't there anymore. Those 'orphaned logs' from thread 1 (inactive instance) are from May 23rd.
I've attached how the v$archived_log look like. Our instance is running on thread 2 right now
Check the result of the script below too:
SQL> select thread#, max(sequence#) from v$archived_log group by thread# order by thread#;
THREAD# MAX(SEQUENCE#)
---------- --------------
1 1445213
2 2665222
That shouldn't show thread 1 I believe as this is RAC OneNode
|
|
|
Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684507 is a reply to message #684504] |
Thu, 17 June 2021 11:08 |
LaFilipina
Messages: 51 Registered: May 2007 Location: spain
|
Member |
|
|
Michel Cadot wrote on Thu, 17 June 2021 02:42
Your output gives AL that are already applied, I don't see why your standby db is trying to fetch them.
Post the result of the following statements executed on the standby database (using SQL*Plus):
col inst_id format 999 heading INST
select inst_id, thread#, low_sequence#, high_sequence#
from gv$archive_gap
order by inst_id, thread#
/
Set lines 125
Col timest format a14 heading TIMESTAMP
Col message format a90 trunc
Col severity format a07 trunc
Col facility format a03
Select inst_id, to_char(timestamp, 'DD/MM HH24:MI:SS') timest,
decode(facility,
'Crash Recovery', 'CR',
'Data Guard', 'DG',
'Fetch Archive Log', 'FAL',
'Log Apply Services', 'LAS',
'Log Transport Services', 'LTS',
'Network Services', 'NS',
'Remote File Server', 'RFS',
'Role Management Services', 'RMS',
substr(facility,1,3)
) facility,
message, severity
from gv$dataguard_status
where severity >= 3 and timestamp >= trunc(sysdate)
order by inst_id, timestamp
/
Don't forget to format your output as explained in How to use [code] tags and make your code easier to read.
Hi,
Thank you for reply. I had to remove the standby database and cleaned up the whole data guard configuration as our production database was choking. Archivelog files were not being deleted and was filling up the archive log destination in ASM.
I am going to try to create the standby database today again and once I've done that, I will post the requested info.
Thanks!
|
|
|
Goto Forum:
Current Time: Sun Feb 02 12:06:34 CST 2025
|