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.