Configuring the ARCHIVELOG DELETION POLICY on Data Guard environment

Backup & Recovery, High Availability / 13/07/2022

Neste artigo, vamos explorar uma opção de configuração de política de deleção de archivelogs em ambiente Data Guard.

Configuração de nosso ambiente laboratório:

[oracle@fornix1 BACKUP]$ dgmgrl sys/oracle@CORTEX as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 05:15:13 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> SHOW CONFIGURATION;
 
Configuration - cortex
 
  Protection Mode: MaxPerformance
  Members:
  cortex   - Primary database
    cortexdr - Physical standby database
 
Fast-Start Failover:  Disabled
 
Configuration Status:
SUCCESS   (status updated 62 seconds ago)
 
DGMGRL> SHOW DATABASE CORTEX;
 
Database - cortex
 
  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    cortex
 
Database Status:
SUCCESS
 
DGMGRL> SHOW DATABASE CORTEXDR;
 
Database - cortexdr
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 13.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    CORTEXDR
 
Database Status:
SUCCESS
 
DGMGRL>

Uma estratégia que podemos adotar no ambiente Primary é configurar a política abaixo, que permite o delete dos archivelog que já foram enviados para todos os ambientes Standby:

[oracle@fornix1 BACKUP]$ rman target sys/oracle@CORTEX CATALOG CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:16:17 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> configure ARCHIVELOG DELETION POLICY to SHIPPED TO ALL STANDBY;
 
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
 
RMAN>

Para validar essa abordagem, vamos interromper os processos de transporte e Redo Apply do nosso ambiente:

[oracle@fornix1 BACKUP]$ dgmgrl sys/oracle@CORTEX as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 05:19:16 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-OFF;
Succeeded.
DGMGRL> EDIT DATABASE CORTEX SET STATE='TRANSPORT-OFF';
Succeeded.
DGMGRL>

Vamos gerar alguns archives novos no primary:

[oracle@fornix1 BACKUP]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 29 05:20:29 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL>

Ao tentar deletar os archivelogs, um erro é apresentado pelo RMAN:

[oracle@fornix1 BACKUP]$ rman target sys/oracle@CORTEX CATALOG CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:29:56 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL ;
 
 
starting full resync of recovery catalog
full resync complete
 
resyncing from database with DB_UNIQUE_NAME CORTEXDR
DELETE ARCHIVELOG ALL;
starting resync of recovery catalog
resync complete
 
RMAN>
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 device type=DISK
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_11.300.1076476837 thread=1 sequence=11
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_12.299.1076476841 thread=1 sequence=12
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_13.301.1076477377 thread=1 sequence=13
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_14.302.1076477379 thread=1 sequence=14
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_15.303.1076477379 thread=1 sequence=15
 
RMAN>

Habilitando novamente os serviços de Transport e Redo Apply:

[oracle@fornix1 BACKUP]$ dgmgrl sys/oracle@CORTEX as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 05:31:39 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortex"
Connected as SYSDBA.
DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-ON;
Succeeded.
DGMGRL> EDIT DATABASE CORTEX SET STATE=TRANSPORT-ON;
Succeeded.
DGMGRL>

Agora conseguimos realizar o delete dos archivelogs:

[oracle@fornix1 BACKUP]$ rman target sys/oracle@CORTEX CATALOG CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:32:50 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> DELETE ARCHIVELOG ALL;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
List of Archived Log Copies for database with db_unique_name CORTEX
=====================================================================
 
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
354     1    11      A 29-JUN-21
        Name: +DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_11.300.1076476837
 
355     1    12      A 29-JUN-21
        Name: +DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_12.299.1076476841
 
372     1    13      A 29-JUN-21
        Name: +DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_13.301.1076477377
 
373     1    14      A 29-JUN-21
        Name: +DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_14.302.1076477379
 
374     1    15      A 29-JUN-21
        Name: +DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_15.303.1076477379
 
381     1    16      A 29-JUN-21
        Name: +DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_16.304.1076477535
 
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted archived log
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_11.300.1076476837 RECID=140 STAMP=1076476837
deleted archived log
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_12.299.1076476841 RECID=141 STAMP=1076476840
deleted archived log
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_13.301.1076477377 RECID=142 STAMP=1076477377
deleted archived log
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_14.302.1076477379 RECID=143 STAMP=1076477378
deleted archived log
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_15.303.1076477379 RECID=144 STAMP=1076477379
deleted archived log
archived log file name=+DG_RECO/CORTEX/ARCHIVELOG/2021_06_29/thread_1_seq_16.304.1076477535 RECID=145 STAMP=1076477536
Deleted 6 objects
 
 
RMAN>

Já do lado do Standby Database, podemos aplicar a política abaixo, que apenas permite o delete dos archivelogs quandos os mesmos já foram aplicados no Standby:

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:42:04 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
 
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored
 
RMAN>

Recebendo mensagem de erro ao tentar deletar o archivelog, pois os mesmos ainda não haviam sido aplicados no Standby:

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:44:55 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> RESYNC CATALOG FROM DB_UNIQUE_NAME ALL ;
 
 
resyncing from database with DB_UNIQUE_NAME CORTEX
starting full resync of recovery catalog
full resync complete
 
starting resync of recovery catalog
resync complete
 
RMAN> DELETE ARCHIVELOG ALL;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=86 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_17.338.1076478291 thread=1 sequence=17
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_18.343.1076478291 thread=1 sequence=18
 
RMAN>

Após alguns segundos, já é possível deletá-los:

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:47:13 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> DELETE ARCHIVELOG ALL;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=88 device type=DISK
List of Archived Log Copies for database with db_unique_name CORTEXDR
=====================================================================
 
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
412     1    17      A 29-JUN-21
        Name: +DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_17.338.1076478291
 
413     1    18      A 29-JUN-21
        Name: +DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_18.343.1076478291
 
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted archived log
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_17.338.1076478291 RECID=67 STAMP=1076478290
deleted archived log
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_18.343.1076478291 RECID=68 STAMP=1076478291
Deleted 2 objects

Vamos baixar o processo de Redo Apply:

[oracle@fornix2 BACKUP]$ dgmgrl sys/oracle@CORTEXDR as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 05:49:45 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-OFF;
Succeeded.
DGMGRL>

Gerando archives no Primary:

[oracle@fornix1 BACKUP]$ sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 29 05:50:17 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> ALTER SYSTEM SWITCH LOGFILE;
 
System altered.
 
SQL> /
 
System altered.
 
SQL>

Tentando deletar archives no standby:

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:50:53 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> DELETE ARCHIVELOG ALL;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_19.343.1076478625 thread=1 sequence=19
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_20.338.1076478627 thread=1 sequence=20
 
RMAN>

Subindo serviço de Redo Apply:

[oracle@fornix2 BACKUP]$ dgmgrl sys/oracle@CORTEXDR as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 29 05:52:02 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected to "cortexDR"
Connected as SYSDBA.
DGMGRL> EDIT DATABASE CORTEXDR SET STATE=APPLY-ON;
Succeeded.
DGMGRL> SHOW DATABASE CORTEXDR;
 
Database - cortexdr
 
  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    CORTEXDR
 
Database Status:
SUCCESS

Deletando archivelogs:

[oracle@fornix2 BACKUP]$ rman target sys/oracle@CORTEXDR catalog CAT/CAT@CORTEX
 
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Jun 29 05:56:13 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: CORTEX (DBID=548968087)
connected to recovery catalog database
 
RMAN> DELETE ARCHIVELOG ALL;
 
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=86 device type=DISK
List of Archived Log Copies for database with db_unique_name CORTEXDR
=====================================================================
 
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
429     1    19      A 29-JUN-21
        Name: +DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_19.343.1076478625
 
430     1    20      A 29-JUN-21
        Name: +DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_20.338.1076478627
 
 
Do you really want to delete the above objects (enter YES or NO)? Y
deleted archived log
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_19.343.1076478625 RECID=69 STAMP=1076478626
deleted archived log
archived log file name=+DG_RECO/CORTEXDR/ARCHIVELOG/2021_06_29/thread_1_seq_20.338.1076478627 RECID=70 STAMP=1076478626
Deleted 2 objects
 
 
RMAN>

Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish