Skip to main content

DB - Flashback Database

EnglishVersion


This post is about using flashback technology for recover a database, using as example, how to recover a table after truncate.


Truncate is used to clean the tables, it also cleans the table segments.
To recover trucated data requires to .. recover the database.

Important note : flashback database is a must in case of failover in data guard operations.


Flashback requirements


It have some requirements for flashback database
- The DB needs to be in archive log mode -the traditional archive redo logs
- Feature flashback ON - it will activate the stream the logs / flashback logs

For all this work the database needs to be mounted in a consitant maner
To help with demo I will also create a RESTORE POINT with GUARANTEE FLASHBACK DATABASE


-- As '/ as sysdba',

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 499125840 bytes
Database Buffers 343932928 bytes
Redo Buffers 5132288 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open ;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> CREATE RESTORE POINT before_truncate GUARANTEE FLASHBACK DATABASE;
Restore point created.


Truncate the Table


I will connect as HR demo user and reuse the table regions_4_query created for the DB - Flashback Query post



-- As 'developer',

SQL> conn hr/hr
Connected.

SQL> select * from hr.regions_4_query;

REGION_ID REGION_NAME
---------- -------------------------
2 Americas
3 Asia
4 Middle East and Africa
1 Europe

SQL> truncate table regions_4_query;
Table truncated.

SQL> select * from regions_4_query;
no rows selected


Recover the Table's Data


After truncate the data - the developer noticed was not great idea .. he needs the data back
As DBA it has two options the tradional restore/recover or the flashback the database.
In bellow in this post are the commands for the second option


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Database mounted.

SQL> flashback database to restore point BEFORE_TRUNCATE;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SQL> select * from hr.regions_4_query;

REGION_ID REGION_NAME
---------- -------------------------
2 Americas
3 Asia
4 Middle East and Africa
1 Europe



Thank you for reading.
hope this post was helpful. Rogerio
Labels:

Comments

Post a Comment

Popular posts from this blog

DB - How to monitor Oracle datapump jobs

These days there is lot of work around database migrations not only but mainly to cloud providers. One of the ways to migrate Oracle databases is using data pump (expdp/impdp). In general works fine, it allows an easy way to bring the database even different OS. It is always good to monitor the progress, to know where we are and estimate how long we are from the end. In this post, I am sharing some queries to help with database monitoring progress. The queries can be easily tailed to various scenarios. Starting with a simple one - the track the number of objects loaded during an import operation. When there is a massive amount of errors during the import. Drop and run the import again. The bellow query can be useful also to monitor the number of objects if we are dropping the users. Here, I’m making the assumption there were no other database activity in progress on the last 24 hours. select owner, count(0) from dba_objects where owner in ( select username from dba_users where created...
Post a Comment
Read more

VM - Partilhar Pastas Windows - Linux

Neste post escrevo como partilhar pastas entre Windows (Host) e uma VM Guest, neste caso Linux. Ha algumas formas de fazer isto, esta parece me a mais facil. Seleccionar a Pasta Windows Na consola Virtual Box > Devices > Shared Folder Seleccionar a pasta windows para partilhar no Linux   O   Montar a pasta Windows na VM Ha duas opcoes : 1) Reiniciar a VM (Auto-mount) 2) Linha de comando [root@host ~]# mkdir -p /u01/stage/win_ebs_sw [root@host ~]# chmod 777 /u01/stage/win_ebs_sw [root@host ~]# mount -t vboxsf Oracle_EBS /u01/stage/win_ebs_sw/ [root@host ~]# ls /u01/stage/win_ebs_sw ebs_weblogic_webtier [root@host ~]# df -h /u01/stage/win_ebs_sw Filesystem Size Used Avail Use% Mounted on Oracle_EBS 895G 494G 401G 56% /u01/stage/win_ebs_sw Guardar a Configuracao no /etc/fstab para tornar as alteracoes permanentes. [root@host ~]# echo "Oracle_EBS /u01/stage/win_ebs_sw vboxsf defaults 0 0" >> /etc/fstab [root@host ~]# cat /etc/fstab # /etc/...
Post a Comment
Read more

DB – How to monitor Oracle database long ops ?

As DBAs, some questions we are often asked and to be fair we ask ourselfs when we are doing massive operations, are : Is the database doing something? How long the database takes to complete the task? There a data dictionary view, GV$SESSION_LONGOPS whom is really usefull for monitoring and to provide estimated time to get the task done. Starting with a first example, a query for general propose . set lines 240 col message form a100 col opname form a50 select * from ( select round(l.sofar/l.totalwork*100,2) as complete, --l.* l.sid, --l.opname, l.message, l.start_time, l.time_remaining/60 "minuts remaining", l.elapsed_seconds from gv$session_longops l where totalwork !=0 ) where complete < 100; The view is also usefull on specific operations such as Oracle Recovery Manager (RMAN) jobs monitoring, this query returns information about backup,restore and recovery. select sid, serial#,opname, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complet...
Post a Comment
Read more