How to get table back once it is deleted

 flashback table <table> to timestamp <when it was good>;


flashback table orders to timestamp systimestamp - interval '1' hour;


alter table <table> enable row movement;

select * from <table> as of timestamp systimestamp - interval '1' hour;


select * from <table> as of scn 1234567;


insert into table
  select * from <table> as of timestamp sysdate – interval '1' hour
  where <conditions to find the rows>;

select * from <table> as of timestamp sysdate – interval '1' hour
minus
select * from <table>;

insert into <table>
  select * from <table> as of timestamp sysdate – interval '1' hour
  minus
  select * from <table>;

update <table> cur
set (col1, col2, col3) = (
  select col1, col2, col3 from <table>
    as of timestamp systimestamp – interval '1' hour old
  where cur.primary_key = old.primary_key
)
where <rows to update>;

Flashback has some limitation :

  • Oracle only ensures you can query as far back as the value of your “undo_retention” parameter.
  • Oracle is unable to query across many forms of DDL. So if you change a table's structure there's a good chance Flashback Query will fail.

alter system set undo_retention = 86400 scope = both;


create flashback archive <archive> tablespace <tablespace> retention 1 year;

alter table <table> flashback archive <archive>;

  • Being able to query across DDL
  • Having a larger window of time to recover data
flashback table <table> to before drop;

select * from recyclebin;

drop table <table> purge;

purge table <table>;

purge recyclebin;

To use Flashback Database you need to do some initial setup. There are two ways to enable this:

  • Enable Flashback Database
  • Create a restore point


Firstly, your database must be running in archivelog mode. Assuming this is the case, the process for enabling it is:

  1. Configure the fast recovery area
  2. Set the DB_flashback_retention_target parameter (optional)
  3. Enable Flashback Database

alter system set DB_RECOVERY_FILE_DEST = '/u01/oradata/recovery_area' scope=both;
alter system set DB_RECOVERY_FILE_DEST_SIZE = 10G scope=both;

1
alter system set DB_flashback_retention_target = 10080 scope=both;
alter database flashback on;
create restore point <restore_point> guarantee flashback database;

Two key use cases for this are:

  • An extra safety net for database releases
  • Reverting test databases to a known state


Comments

Post a Comment

Popular posts from this blog

renewal of DP-300 exam questions

Image
  According to Microsoft SQL Server configuration best practices, what should you do to configure cach According to Microsoft SQL Server configuration best practices, what should you do to configure caching on volumes that host log files? Select only one answer. Enable read caching only. Enable write caching only. Enable read and write caching. Disable all caching. You plan to deploy Microsoft SQL Server 2019 to an Azure virtual machine that has eight CPUs and four disks. You expect that SQL Server will run tempdb heavy-workloads. You need to optimize the performance of tempdb. What should you do? Select only one answer. Store tempdb in eight files of the same size. Store tempdb in four files. Configure one of the files to have the size matching the amount of memory allocated to SQL Server. Enable trace flag 1117. Enable trace flag 1118. : You plan to implement SQL Server on an Azure virtual machine that has managed disks. You need to ensure that the virtual machine disks hosting t...
Read more

DP-300 dumps

Image
 Question #1 Topic 1 You have 20 Azure SQL databases provisioned by using the vCore purchasing model. You plan to create an Azure SQL Database elastic pool and add the 20 databases. Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point. A. total size of all the databases B. geo-replication support C. number of concurrently peaking databases * peak CPU utilization per database D. maximum number of concurrent sessions for all the databases E. total number of databases * average CPU utilization per database Correct Answer: ACE  CE: Estimate the vCores needed for the pool as follows: For vCore-based purchasing model: MAX(<Total number of DBs X average vCore utilization per DB>, <Number of concurrently peaking DBs X Peak vCore utilization per DB) A: Estimate the storage space needed for the pool by adding the number of bytes needed for ...
Read more

DP-300(30-60 questions)

Image
  You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked. You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1. You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements: ✑ Ensure that all traffic to the public endpoint of SqlSrv1 is blocked. ✑ Minimize the possibility of VM1 exfiltrating data stored in SqlDb1.  What should you create on VNet1?  A. a VPN gateway  B. a service endpoint  C. a private link  D. an ExpressRoute gateway  Correct Answer: C  You have 40 Azure SQL databases, each for a different customer. All the databases reside on the same Azure SQL Database server. You need to ensure that each customer can only connect to and access their respective database. Which two actions should you perform? Each correct answer presents part of the solution.  NOTE: Each correct selection is worth one point. A. Imp...
Read more