There are some situations where it is relevant for DBA to use FLASHBACK or Database Point-in-Time Recovery such as when a user modifies some data incorrectly or also when an upgrade failed and if it wishes to revert the previous situation of the database among several others possibilities. With this, in this post I will be explaining in a simple way how to use Database Point-in-Time Recovery. Therefore, I will be considering some requirements for this operation be possible:
– The database must be enabled in Archivelog mode:
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 13 Next log sequence to archive 15 Current log sequence 15 SQL> show parameters recov NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 20G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0
– The database must be enabled in FLASHBACK mode:
To check whether the FLASHBACK mode are enable:
SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ YES
If is not enabled:
SQL> ALTER DATABASE FLASHBACK ON; Database altered.
OK, now we can start the simulation:
- Creating the TESTE table in user BRUNORS: SQL> CREATE TABLE BRUNO.TESTE ( PID int, LName varchar(255), FName varchar(255), Address varchar(255), City varchar(255) ); 2 3 4 5 6 7 Table created.
– Checking the created table:
SQL> SET PAGES 2000 SQL> SET LINES 2000 SQL> select table_name, owner from dba_tables where table_name='TESTE'; TABLE_NAME OWNER -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- TESTE BRUNO
SQL> DESC BRUNO.TESTE Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PID NUMBER(38) LNAME VARCHAR2(255) FNAME VARCHAR2(255) ADDRESS VARCHAR2(255) CITY VARCHAR2(255) SQL>
– Creating the RESTORE POINT:
SQL> CREATE RESTORE POINT before_droptableTESTE; RESTORE POINT created.
– Checking existing RESTORE POINTs:
SQL> SET PAGES 2000 SQL> SET LINES 2000 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; 2 3 NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE -------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------- --------------------- --- ------------ BEFORE_UPGRADE 2329858 06-MAY-18 06.44.04.000000000 AM 4 NO 0 BEFORE_DROPTABLE 2330075 06-MAY-18 06.50.37.000000000 AM 4 NO 0 BEFORE_DROPTABLETESTE 2332642 06-MAY-18 07.01.49.000000000 AM 4 NO 0
– Deleting the created table:
SQL> DROP TABLE SYS.TESTE; Table dropped.
Now we will restart the process of re-creating the deleted table. To do this, you need to perform the SHUTDOWN of the database:
SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down.
– Initialize the database in MOUNT stage to do the FLASHBACK to the RESTORE POINT created:
SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 796917760 bytes Fixed Size 2929448 bytes Variable Size 545262808 bytes Database Buffers 243269632 bytes Redo Buffers 5455872 bytes Database mounted.
– Do the FLASHBACK for the RESTORE POINT created.
SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_DROPTABLETESTE; FLASHBACK complete.
– OPEN the database in RESETLOG MODE:
SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.
– Verify that the table now exists again in the database:
SQL> DESC BRUNO.TESTE Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PID NUMBER(38) LNAME VARCHAR2(255) FNAME VARCHAR2(255) ADDRESS VARCHAR2(255) CITY VARCHAR2(255)
Há algumas situações que é relevante para DBA utilizar o FLASHBACK ou Database Point-in-Time Recovery como por exemplo quando um usuário modifica algum dado incorretamente ou também quando um upgrade falhou e se deseja retonar a situação anterior do banco de dados , entre várias outras possibilidades. Com isso, neste post estarei mostrando de um modo simples como utilizar o Database Point-in-Time Recovery. Para isso, estarei considerando alguns requisitos para que eu possa realizar a operação:
– O banco de dados deve estar habilitado no modo de Archivelog mode:
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 13 Next log sequence to archive 15 Current log sequence 15 SQL> show parameters recov NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 20G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0
– O banco de dados deve estar habilitado no modo de FLASHBACK:
Para verificar se estar ou não:
SQL> SELECT FLASHBACK_ON FROM V$DATABASE; FLASHBACK_ON ------------------ YES
Caso não esteja:
SQL> ALTER DATABASE FLASHBACK ON; Database altered.
OK, agora podemos iniciar a simulação:
– Criando a tabela TESTE no usuário BRUNORS:
SQL> CREATE TABLE BRUNO.TESTE ( PID int, LName varchar(255), FName varchar(255), Address varchar(255), City varchar(255) ); 2 3 4 5 6 7 Table created.
– Checando a tabela criada:
SQL> SET PAGES 2000 SQL> SET LINES 2000 SQL> select table_name, owner from dba_tables where table_name='TESTE'; TABLE_NAME OWNER -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- TESTE BRUNO
SQL> DESC BRUNO.TESTE Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PID NUMBER(38) LNAME VARCHAR2(255) FNAME VARCHAR2(255) ADDRESS VARCHAR2(255) CITY VARCHAR2(255) SQL>
— Criando o RESTORE POINT:
SQL> CREATE RESTORE POINT before_droptableTESTE; RESTORE POINT created.
— Checando os RESTORE POINT existentes:
SQL> SET PAGES 2000 SQL> SET LINES 2000 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT; 2 3 NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE -------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------- --------------------- --- ------------ BEFORE_UPGRADE 2329858 06-MAY-18 06.44.04.000000000 AM 4 NO 0 BEFORE_DROPTABLE 2330075 06-MAY-18 06.50.37.000000000 AM 4 NO 0 BEFORE_DROPTABLETESTE 2332642 06-MAY-18 07.01.49.000000000 AM 4 NO 0
— Apagando a tabela criada:
SQL> DROP TABLE SYS.TESTE; Table dropped.
Agora iremos reiniciar o processo de recriação da tabela apagada. Para isso, é necessário efetuar o SHUTDOWN do banco de dados:
SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down.
– Inicialize o banco de dados em MOUNT para efetuar o FLASHBACK para o RESTORE POINT criado:
SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 796917760 bytes Fixed Size 2929448 bytes Variable Size 545262808 bytes Database Buffers 243269632 bytes Redo Buffers 5455872 bytes Database mounted.
– Efetue o FLASHBACK para o RESTORE POINT criado:
SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_DROPTABLETESTE; FLASHBACK complete.
– Abra o banco de dados em RESETLOG MODE:
SQL> ALTER DATABASE OPEN RESETLOGS; Database altered.
– Verifique que agora a tabela existe novamente no banco de dados:
SQL> DESC BRUNO.TESTE Name Null? Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PID NUMBER(38) LNAME VARCHAR2(255) FNAME VARCHAR2(255) ADDRESS VARCHAR2(255) CITY VARCHAR2(255)
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)