Simple way how to use Database Point-in-Time Recovery

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)

 




 

Related posts

Leave a Comment