Listo, veamos como funciona esta bien conocida característica en bases de datos Oracle, pero esta vez en la última versión en producción liberada.
Empecemos por conectarnos a la base de datos
[oracle@lab1 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 5 19:46:35 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> show sga
Total System Global Area 1912599952 bytes
Fixed Size 8897936 bytes
Variable Size 436207616 bytes
Database Buffers 1459617792 bytes
Redo Buffers 7876608 bytes
Pueden apreciar, que la base de datos, esta en estado OPEN y que se encuentra bajo el rol de instancia primaria.
SQL> select instance_name, status, logins, INSTANCE_ROLE from v$instance;
INSTANCE_NAME STATUS LOGINS INSTANCE_ROLE
---------------- ------------ ---------- ------------------
lab1 OPEN ALLOWED PRIMARY_INSTANCE
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1912599952 bytes
Fixed Size 8897936 bytes
Variable Size 436207616 bytes
Database Buffers 1459617792 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> select instance_name, status, logins, INSTANCE_ROLE from v$instance;
INSTANCE_NAME STATUS LOGINS INSTANCE_ROLE
---------------- ------------ ---------- ------------------
lab1 MOUNTED ALLOWED PRIMARY_INSTANCE
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/product/19.3.0/dbs/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
Verificamos que la funcionalidad se encuentra encendida y que no nos haga falta algo más. En este caso, nos hace falta configurar el área de Fast Recovery.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> ^C
SQL> alter system set db_recovery_file_dest_size=5G scope=both;
System altered.
SQL> host
[oracle@lab1 ~]$ mkdir /opt/app/oracle/fast_recovery_area
[oracle@lab1 ~]$ exit
exit
SQL> alter system set db_recovery_file_dest='/opt/app/oracle/fast_recovery_area' scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> host ls -la /opt/app/oracle/fast_recovery_area
total 4
drwxr-xr-x. 3 oracle oinstall 18 Nov 5 20:45 .
drwxr-xr-x. 10 oracle oinstall 4096 Nov 5 20:41 ..
drwxr-x---. 3 oracle oinstall 23 Nov 5 20:45 LAB1
SQL> host ls -la /opt/app/oracle/fast_recovery_area/LAB1
total 0
drwxr-x---. 3 oracle oinstall 23 Nov 5 20:45 .
drwxr-xr-x. 3 oracle oinstall 18 Nov 5 20:45 ..
drwxr-x---. 2 oracle oinstall 60 Nov 5 20:45 flashback
SQL> host ls -la /opt/app/oracle/fast_recovery_area/LAB1/flashback
total 409616
drwxr-x---. 2 oracle oinstall 60 Nov 5 20:45 .
drwxr-x---. 3 oracle oinstall 23 Nov 5 20:45 ..
-rw-r-----. 1 oracle oinstall 209723392 Nov 5 20:45 o1_mf_gw4dxt5o_.flb
-rw-r-----. 1 oracle oinstall 209723392 Nov 5 20:45 o1_mf_gw4dy1kf_.flb
SQL> show sga
Total System Global Area 1912599952 bytes
Fixed Size 8897936 bytes
Variable Size 436207616 bytes
Database Buffers 1459617792 bytes
Redo Buffers 7876608 bytes
SQL> select instance_name, status, logins, INSTANCE_ROLE from v$instance;
INSTANCE_NAME STATUS LOGINS INSTANCE_ROLE
---------------- ------------ ---------- ------------------
lab1 MOUNTED ALLOWED PRIMARY_INSTANCE
SQL> alter database open;
Database altered.
SQL> alter user hr account unlock;
User altered.
SQL> alter user hr identified by hr;
User altered.
SQL> connect hr/hr
Connected.
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> show user
USER is "HR"
SQL> create table employees_drop as select * from employees;
Table created.
SQL> select count(*) from employees_drop;
COUNT(*)
----------
107
Verificamos la existencia de la tabla en la vista de catálogo del usuario.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
EMPLOYEES_DROP TABLE
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
12 rows selected.
SQL> drop table EMPLOYEES_DROP;
Table dropped.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
BIN$lqXBbrnuY8DgUwEA TABLE
AApjnQ==$0
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
12 rows selected.
SQL> flashback table EMPLOYEES_DROP to before drop;
Flashback complete.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
EMPLOYEES_DROP TABLE
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
12 rows selected.
SQL> drop table EMPLOYEES_DROP purge;
Table dropped.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
11 rows selected.
SQL> flashback table EMPLOYEES_DROP to before drop;
flashback table EMPLOYEES_DROP to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
Rápida y sencillamente, hemos configurado y probado la características de FLASHBACK a nivel de tabla en una base de datos Oracle versión 19c.
[oracle@lab1 ~]$ sqlplus /nolog
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 5 19:46:35 2019
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> show sga
Total System Global Area 1912599952 bytes
Fixed Size 8897936 bytes
Variable Size 436207616 bytes
Database Buffers 1459617792 bytes
Redo Buffers 7876608 bytes
Pueden apreciar, que la base de datos, esta en estado OPEN y que se encuentra bajo el rol de instancia primaria.
SQL> select instance_name, status, logins, INSTANCE_ROLE from v$instance;
INSTANCE_NAME STATUS LOGINS INSTANCE_ROLE
---------------- ------------ ---------- ------------------
lab1 OPEN ALLOWED PRIMARY_INSTANCE
Al igual que en las anteriores versiones, no es posible poner la base de datos en modo archivelog, sin tener la instancia en estado MOUNT.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance
Bajamos la instancia de base de datos de modo consistente y únicamente la montamos para activar el modo archivelog.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1912599952 bytes
Fixed Size 8897936 bytes
Variable Size 436207616 bytes
Database Buffers 1459617792 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> select instance_name, status, logins, INSTANCE_ROLE from v$instance;
INSTANCE_NAME STATUS LOGINS INSTANCE_ROLE
---------------- ------------ ---------- ------------------
lab1 MOUNTED ALLOWED PRIMARY_INSTANCE
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/app/oracle/product/19.3.0/dbs/arch
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
Verificado que la base de datos esta en modo archivelog, vamos a force el registro de logging para todas las operaciones en la base de datos.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
Verificamos que la funcionalidad se encuentra encendida y que no nos haga falta algo más. En este caso, nos hace falta configurar el área de Fast Recovery.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- -------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> ^C
Configuramos el parámetro del tamaño de FAST RECOVERY AREA y lo hacemos persistente en el archivo de parámetros.
SQL> alter system set db_recovery_file_dest_size=5G scope=both;
System altered.
SQL> host
[oracle@lab1 ~]$ mkdir /opt/app/oracle/fast_recovery_area
[oracle@lab1 ~]$ exit
exit
SQL> alter system set db_recovery_file_dest='/opt/app/oracle/fast_recovery_area' scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> host ls -la /opt/app/oracle/fast_recovery_area
total 4
drwxr-xr-x. 3 oracle oinstall 18 Nov 5 20:45 .
drwxr-xr-x. 10 oracle oinstall 4096 Nov 5 20:41 ..
drwxr-x---. 3 oracle oinstall 23 Nov 5 20:45 LAB1
SQL> host ls -la /opt/app/oracle/fast_recovery_area/LAB1
total 0
drwxr-x---. 3 oracle oinstall 23 Nov 5 20:45 .
drwxr-xr-x. 3 oracle oinstall 18 Nov 5 20:45 ..
drwxr-x---. 2 oracle oinstall 60 Nov 5 20:45 flashback
SQL> host ls -la /opt/app/oracle/fast_recovery_area/LAB1/flashback
total 409616
drwxr-x---. 2 oracle oinstall 60 Nov 5 20:45 .
drwxr-x---. 3 oracle oinstall 23 Nov 5 20:45 ..
-rw-r-----. 1 oracle oinstall 209723392 Nov 5 20:45 o1_mf_gw4dxt5o_.flb
-rw-r-----. 1 oracle oinstall 209723392 Nov 5 20:45 o1_mf_gw4dy1kf_.flb
SQL> show sga
Total System Global Area 1912599952 bytes
Fixed Size 8897936 bytes
Variable Size 436207616 bytes
Database Buffers 1459617792 bytes
Redo Buffers 7876608 bytes
SQL> select instance_name, status, logins, INSTANCE_ROLE from v$instance;
INSTANCE_NAME STATUS LOGINS INSTANCE_ROLE
---------------- ------------ ---------- ------------------
lab1 MOUNTED ALLOWED PRIMARY_INSTANCE
Vamos a abrir la base de datos y hacer algunas pruebas con el usuario HR de ejemplo.
SQL> alter database open;
Database altered.
SQL> alter user hr account unlock;
User altered.
SQL> alter user hr identified by hr;
User altered.
SQL> connect hr/hr
Connected.
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> show user
USER is "HR"
Vamos a crear una tabla para la prueba de la habilitación del FLASHBACK RECOVERY.
SQL> create table employees_drop as select * from employees;
Table created.
SQL> select count(*) from employees_drop;
COUNT(*)
----------
107
Verificamos la existencia de la tabla en la vista de catálogo del usuario.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
EMPLOYEES_DROP TABLE
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
12 rows selected.
Borramos la tabla.
SQL> drop table EMPLOYEES_DROP;
Table dropped.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
BIN$lqXBbrnuY8DgUwEA TABLE
AApjnQ==$0
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
12 rows selected.
Y ahora probamos que podamos recuperar la tabla con el comando FLASHBACK.
SQL> flashback table EMPLOYEES_DROP to before drop;
Flashback complete.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
EMPLOYEES_DROP TABLE
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
12 rows selected.
Recuerde que si borramos la tabla agregando la cláusula PURGE, no será posible realizar una recuperación de la tabla borrada.
SQL> drop table EMPLOYEES_DROP purge;
Table dropped.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
-------------------- -----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
11 rows selected.
SQL> flashback table EMPLOYEES_DROP to before drop;
flashback table EMPLOYEES_DROP to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
Rápida y sencillamente, hemos configurado y probado la características de FLASHBACK a nivel de tabla en una base de datos Oracle versión 19c.