Oracle: Que sucede cuando se borra un punto de restauración de la base de datos.

En muchas ocasiones ejecutamos cosas, pero no nos preguntamos que es lo que la base de datos realiza cuando envíamos un comando a correr.

He dejando algunas publicaciones sobre los puntos de restauración por acá y quiero que observen que es lo que sucede cuando uno borra un RESTORE POINT.

Es suficiente que cuando ejecutemos el comando, procedamos a ejecutar el ADRCI - Interpretador de comandos del repositorio de diagnóstio automático- y revisemos que es lo que la base de datos hace por debajo a la hora de hacer un "DROP RESTORE POINT ".

Para los contenedores de base de datos que utilizando un UNDO compartido, una operación de Flasback Database a un punto de restauración, es más rápido que un operación de Flashback Database a un SCN.

Los puntos de restauración de PDB pueden ser puntos de restauración normales o puntos de restauración garantizados. Un punto de restauración de PDB garantizado garantiza que puede realizar una operación de flashback para el PDB en ese punto de restauración.

Las principales diferencias entre el registro de flashback normal y el registro para puntos de restauración garantizados están relacionados con cuándo se registran los bloques y si los registros se pueden eliminar en respuesta a la presión del espacio en el área de recuperación rápida. Estas diferencias afectan el uso del espacio para los registros y el rendimiento de la base de datos.

Documentación oficial: https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/using-flasback-database-restore-points.html#GUID-34C48821-B29D-4A2E-94DE-CD97CFA7784E

Veamos como queda registrado el "DROP RESTORE POINT" en la bitácora de la base de datos.

SQL> @ver_restore_point.sql

SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
-------------- --- -------------------- -------------------------------------------------- --- ---------- 
91126983012 YES 03/03/2023 17:19 RESTORE_POINT_PDB1_PRE_EOD_POINT_03032023_1719 YES 34.1796875 PDB1
91098657483 YES 02/03/2023 15:33 RESTORE_POINT_PDB1_PRE_EOD_POINT_02032023_1533 YES 13.1835938 PDB1

SQL> drop restore point RESTORE_POINT_PDB1_PRE_EOD_POINT_02032023_1533 for pluggable database pdb1;

Restore point dropped.

SQL> @ver_restore_point.sql

SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
-------------- --- -------------------- ------------------------------------------------- 
91126983012 YES 03/03/2023 17:19 RESTORE_POINT_PDB1_PRE_EOD_POINT_03032023_1719 YES 34.1796875 PDB1

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

[oracle@serverdb_01_prod scripts]$ adrci

ADRCI: Release 19.0.0.0.0 - Production on Mon Mar 6 10:49:49 2023
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

ADR base = "/opt/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/cdb/cdb
diag/clients/user_oracle/host_3013543445_110
diag/clients/user_oracle/host_667749374_110
diag/clients/user_oracle/host_667749374_82
diag/tnslsnr/serverdb_01_prod/listener
adrci> set home diag/rdbms/cdb/cdb
adrci> show alert

ADR Home = /opt/app/oracle/diag/rdbms/cdb/cdb:
*************************************************************************

Output the results to file: /tmp/alert_3682_14022_cdb_1.ado

Current log# 3 seq# 9679 mem# 0: /opt/app/oracle/oradata/CDB/REDO_MEMBER_02/redo1a.rdo

Current log# 3 seq# 9679 mem# 1: /opt/app/oracle/oradata/CDB/REDO_MEMBER_01/redo1b.rdo

2023-03-06 10:40:54.109000 -06:00

ARC0 (PID:10954): Archived Log entry 9666 added for T-1.S-9678 ID 0x84c72c70 LAD:1

2023-03-06 10:42:13.062000 -06:00

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/foreign_archivelog/PDB1/2023_03_06/o1_mf_1_9679_l0d5z1n9_.arc

2023-03-06 10:44:11.159000 -06:00

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/foreign_archivelog/PDB1/2023_03_06/o1_mf_1_9679_l0d62t17_.arc

2023-03-06 10:46:11.909000 -06:00

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/foreign_archivelog/PDB1/2023_03_06/o1_mf_1_9679_l0d66lh9_.arc

2023-03-06 10:46:52.760000 -06:00

Drop guaranteed restore point RESTORE_POINT_PDB1_PRE_EOD_POINT_02032023_1533

Guaranteed restore point RESTORE_POINT_PDB1_PRE_EOD_POINT_02032023_1533 dropped

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzw5hsb5_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzw9soc7_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzwgskb5_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzwlkgsw_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzwlntgw_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzzlq5qb_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzwn9ccn_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzwqy2lw_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzx03lbw_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzt6wq0o_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztbooh8_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztjt9y5_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztphpqw_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztvn926_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_l00hr3ho_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztw3dpf_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztw82lw_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kztxsmcn_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzv1ndpf_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzv7lc5n_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzvf50ml_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzvlxg9w_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzyf3b4x_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzvqpkl7_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzvqqwon_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzvs2os4_.flb

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/flashback/o1_mf_kzvzdy7x_.flb

2023-03-06 10:48:12.689000 -06:00

Deleted Oracle managed file /opt/app/oracle/oradata/CDB/fast_recovery_area/CDB/foreign_archivelog/PDB1/2023_03_06/o1_mf_1_9679_l0d6bc40_.arc