Se adjunta algunos comandos para crear y monitorear la creacion de un Punto de Restauración.
## tiempo en minutos 1440 = 1 dia
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> SELECT NAME,CDB,LOG_MODE,FLASHBACK_ON FROM V$DATABASE;
NAME CDB LOG_MODE FLASHBACK_ON
--------- --- ------------ ------------------
CDB1 YES ARCHIVELOG YES
## RP Normal
SQL> CREATE RESTORE POINT BEFORE_RP;
## RP Gatantizado, independiente del tiempo programado
SQL> CREATE RESTORE POINT BEFORE_RP GUARANTEE Flashback DATABASE;
## Borrado de RP
SQL> DROP RESTORE POINT BEFORE_RP;
## consulta scn
SQL> select current_scn,current_timestamp from v$database;
*****************
## consulta Restore Point
set pagesize 200 linesize 200
col NAME format a30
col TIME format a40
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/1024/1024 "Size GB" FROM V$RESTORE_POINT;
## Query resumen para consultar utilizacion de Restore Point
set pagesize 200 linesize 200
col NAME format a30
SELECT max(NAME) NAME
, max(LIMIT) LIMIT
, max(USED) USED
, max(Reclaimable) Reclaimable
, max(Used_Percent) Used_Percent
, max(Free_Percent) Free_Percent
, max(NVL(restore_point,0)) Restore_Point
, round(100.0*max(NVL(restore_point,0))/max(LIMIT),1) Restore_Point_Percent
FROM
(select name
, round(SPACE_LIMIT/1024/1024) LIMIT
, round(SPACE_USED/1024/1024) USED
, round(SPACE_RECLAIMABLE/1024/1024) Reclaimable
, round(100.0*space_used/space_limit,1) Used_Percent
, round(CASE WHEN space_limit > 0 AND space_limit >= space_used AND space_used >= space_reclaimable THEN 100.00 * (space_limit - (space_used - space_reclaimable))/space_limit ELSE NULL END,1) Free_Percent,
to_number(null) restore_point
FROM V$RECOVERY_FILE_DEST
UNION ALL
SELECT '' name
, to_number(null) limit
, to_number(null) USED
, to_number(null) Reclaimable
, to_number(null) Used_Percent
, to_number(null) Free_Percent
, round(sum(STORAGE_SIZE)/1024/1024) restore_point
FROM V$RESTORE_POINT)
/
