martes, 26 de julio de 2022

CONFIGURAR Y MONITOREAR FLASHBACK DATABASE CON RESTORE POINT




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)

/


No hay comentarios: