Configuring the ARCHIVELOG DELETION POLICY on Data Guard environment

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[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:

1
2
3
4
5
6
7
8
9
10
11
12
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
[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.