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 Go to next message
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 #684464 is a reply to message #684463] Fri, 11 June 2021 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

These rows are from the control file and are deleted only when space for this section (AL) is needed (or when the control file is recreated).

[Updated on: Fri, 11 June 2021 00:22]

Report message to a moderator

Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684500 is a reply to message #684464] Wed, 16 June 2021 10:18 Go to previous messageGo to next message
LaFilipina
Messages: 51
Registered: May 2007
Location: spain
Member
Michel Cadot wrote on Fri, 11 June 2021 00:22

These rows are from the control file and are deleted only when space for this section (AL) is needed (or when the control file is recreated).

Thanks, Michel. Is there anything I can do manually to solve this? This is production database and needs to be up 24/7.
Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684501 is a reply to message #684500] Wed, 16 June 2021 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no harm to have these rows.
What is your actual problem?

Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684502 is a reply to message #684501] Wed, 16 June 2021 17:37 Go to previous messageGo to next message
LaFilipina
Messages: 51
Registered: May 2007
Location: spain
Member
Michel Cadot wrote on Wed, 16 June 2021 11:12

There is no harm to have these rows.
What is your actual problem?

Hi Michel,

In a non-dataguard environment, it shouldn't do no harm. But in our environment, data guard is trying to find that archive log to replicate to the physical standby database and causes the data guard to break after a while.

Thanks.
Re: Old Archivelog Entries in v$archived_log coming from inactive instance and not getting deleted [message #684503 is a reply to message #684502] Thu, 17 June 2021 01:34 Go to previous messageGo to next message
John Watson
Messages: 8624
Registered: January 2010
Location: Global Village
Senior Member
Can you remove the archivelogs from the RMAN repository?

DELETE FORCE ARCHIVE LOG....

or perhaps

DELETE FORCE FOREIGN ARCHIVELOG...

should do it.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
LaFilipina
Messages: 51
Registered: May 2007
Location: spain
Member
John Watson wrote on Thu, 17 June 2021 01:34
Can 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 Go to previous message
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!
Previous Topic: Automatic database startup issues after switchiver (split from hijacked topic)
Goto Forum:
  


Current Time: Sun Oct 24 23:21:38 CDT 2021