Oracle Gestión de Puntos de Restauración 19c - Procedimiento para borrado de Restore Points.

Una de las características que más me gusta de la base de datos, para temas de cumplir con un respaldo o forma de devolver a un punto "X" la base de datos, de la manera más rápida y sencilla y sin que me tome mucho tiempo o ventana de mantenimiento, es el RESTORE POINT.

Una base de datos Oracle, puede almacenar máximo 2048 puntos de restauración. Estos se retienen en la base de datos al menos por el número de días especificado en el parámetro CONTROL_FILE_RECORD_KEEP_TIME, siempre y cuando, el almacenamiento disponible en el servidor o ambiente en donde corre la base de datos, así lo permita.

En mi caso, mi política es retener solamente los dos últimos puntos de restauración creados, producto de una tarea programada que tengo que se ejecuta cada 24 horas.

De igual manera, podrían existir otros RESTORE POINTS intermedios, que se hayan creado como acción preventiva.

A continuación, les comparto algunos scripts que tengo para la gestión de los puntos de restauración y un procedimiento, cuyo cursor podría contener tan solo el campo de "NAME", pero que he dejado igual para temas que luego me gustaría depurar como el hecho de validar si el punto de restauración es garantizado o no, si es a nivel de un PDB específico o si es a nivel de contenedor.

El procedimiento que he nombrado "REMOVER_RESTORE_POINTS" te permite borrar todos los puntos de restauración que excedan los 2 días de creación a partir del momento de su ejecución en una tarea programada.

login as: opc

Authenticating with public key "rsa-key-20200507"
Passphrase for key "rsa-key-20200507":
Last login: Wed Apr 5 12:02:05 2023 from 10.100.128.143

[opc@serverdb_01_prod ~]$ sudo su - oracle
Last login: Wed Apr 5 12:02:34 CST 2023 on pts/0
[oracle@serverdb_01_prod ~]$ cd
[oracle@serverdb_01_prod ~]$ ./ver

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 7 09:41:50 2023
Version 19.10.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.

SQL> Connected.
SQL>
SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
-------------- --- -------------------- --------------------------------------------------

92652974693 YES 06/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_06042023_1840 YES 7.32421875 PDB1
92612252094 YES 05/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_05042023_1840 YES 13.671875 PDB1
92569926332 YES 04/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_04042023_1840 YES 13.671875 PDB1
92527420013 YES 03/04/2023 18:34 RESTORE_POINT_PDB1_PRE_EOD_POINT_03042023_1834 YES 13.671875 PDB1
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

[oracle@serverdb_01_prod ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 7 09:41:53 2023
Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> host more ver
sqlplus /nolog <<EOF
connect / as sysdba;
@./scripts/ver_restore_point.sql;
EOF

SQL> host more ./scripts/ver_restore_point.sql
col TIME_FMT format a20
col TIME_RPOINT format a20
col name format a50
col pdb_name format a20
col SCN format 9999999999999
set linesize 200

select scn, GUARANTEE_FLASHBACK_DATABASE GUARANTEE,
to_char(TIME,'dd/mm/yyyy hh24:mi') TIME_FMT, name, PDB_RESTORE_POINT,
storage_size/1024/1024/1024 GB_SIZE, pdb_name
from v$restore_point a, dba_pdbs
where dba_pdbs.pdb_id=a.CON_ID
order by time desc
/

SQL> select scn, GUARANTEE_FLASHBACK_DATABASE GUARANTEE,
to_char(TIME,'dd/mm/yyyy hh24:mi') TIME_FMT, name, PDB_RESTORE_POINT,
storage_size/1024/1024/1024 GB_SIZE, pdb_name
from v$restore_point a, dba_pdbs
where dba_pdbs.pdb_id=a.CON_ID
and time < sysdate-1
order by time desc
/

SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
---------- --- ---------------- ------------------------------------------------------- --- ----------
9.2612E+10 YES 05/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_05042023_1840 YES 13.671875 PDB1
9.2570E+10 YES 04/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_04042023_1840 YES 13.671875 PDB1
9.2527E+10 YES 03/04/2023 18:34 RESTORE_POINT_PDB1_PRE_EOD_POINT_03042023_1834 YES 13.671875 PDB1

SQL> @./scripts/ver_restore_point.sql
SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
-------------- --- -------------------- -------------------------------------------------- --- ----------
92652974693 YES 06/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_06042023_1840 YES 8.30078125 PDB1
92612252094 YES 05/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_05042023_1840 YES 13.671875 PDB1
92569926332 YES 04/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_04042023_1840 YES 13.671875 PDB1
92527420013 YES 03/04/2023 18:34 RESTORE_POINT_PDB1_PRE_EOD_POINT_03042023_1834 YES 13.671875 PDB1

SQL> select scn, GUARANTEE_FLASHBACK_DATABASE GUARANTEE,
to_char(TIME,'dd/mm/yyyy hh24:mi') TIME_FMT, name, PDB_RESTORE_POINT,
storage_size/1024/1024/1024 GB_SIZE, pdb_name
from v$restore_point a, dba_pdbs
where dba_pdbs.pdb_id=a.CON_ID
and time < sysdate-2
order by time desc
/

SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
-------------- --- -------------------- -------------------------------------------------- --- ----------
92569926332 YES 04/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_04042023_1840 YES 13.671875 PDB1
92527420013 YES 03/04/2023 18:34 RESTORE_POINT_PDB1_PRE_EOD_POINT_03042023_1834 YES 13.671875 PDB1

SQL> host more borrar_restore_point.sql
CREATE OR REPLACE PROCEDURE REMOVER_RESTORE_POINTS AS
-- Este procedimiento se encarga de remover los puntos de restauración mayores a 48 horas de su creación
-- Procedimiento creado el 07/04/2023
-- Ronald Vargas, DBA
-- Base de datos producción
string_restore_point_name v$restore_point.name%TYPE;

string_execute varchar2(300);
cursor c_restore_points is
select scn, GUARANTEE_FLASHBACK_DATABASE GUARANTEE,
to_char(TIME,'dd/mm/yyyy hh24:mi') TIME_FMT, name, PDB_RESTORE_POINT,
storage_size/1024/1024/1024 GB_SIZE, pdb_name
from v$restore_point a, dba_pdbs
where dba_pdbs.pdb_id=a.CON_ID
and time < sysdate-2
order by time desc;

r_restore_point c_restore_points%ROWTYPE;

BEGIN
OPEN c_restore_points;
LOOP
FETCH c_restore_points INTO r_restore_point;
EXIT WHEN c_restore_points%NOTFOUND;

string_execute :='DROP RESTORE POINT '||r_restore_point.name ||' FOR PLUGGABLE DATABASE '|| r_restore_point.pdb_name;

execute immediate string_execute;
END LOOP;
CLOSE c_restore_points;
END;
/

SQL> @borrar_restore_point.sql
PL/SQL procedure successfully completed.

SQL> @./scripts/ver_restore_point.sql
SCN GUA TIME_FMT NAME PDB GB_SIZE PDB_NAME
-------------- --- -------------------- --------------------------------------------------
92652974693 YES 06/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_06042023_1840 YES 8.30078125 PDB1
92612252094 YES 05/04/2023 18:40 RESTORE_POINT_PDB1_PRE_EOD_OFIC002_05042023_1840 YES 13.671875 PDB1