{"id":132,"date":"2014-02-28T14:01:02","date_gmt":"2014-02-28T13:01:02","guid":{"rendered":"https:\/\/www.carajandb.com\/2014\/02\/28\/table-recovery-en\/"},"modified":"2014-02-28T14:01:02","modified_gmt":"2014-02-28T13:01:02","slug":"table-recovery-en","status":"publish","type":"post","link":"https:\/\/carajandb.com\/en\/2014\/02\/28\/table-recovery-en\/","title":{"rendered":"Table Recovery"},"content":{"rendered":"

Very many customers are using export \/ import or data pump as a backup solution in case they are losing a table or some content for several years now. But as the database size increases the size of the export and more problematic the time needed for the export increases as well.<\/p>\n

Most recently one of my customers was facing the problem that the time window he specified for the ordinary backup, the export and some batches was no longer sufficient. Instead the batch run hasn\u2019t finished when the users came back to work in the morning.<\/p>\n

So the question is: how can the time of the export being reduced?<\/p>\n

The answer is pretty simple: don\u2019t do an export \u2013 that saves a lot of time!<\/p>\n

No way \u2013 said the customer! While ago we had a problem with the batch process so we had to restore some tables with the data from the export.<\/p>\n

<\/p>\n

Examples how to recover one or more table(s)<\/h2>\n

Flashback Database<\/h3>\n

If flashback database is enabled you can simply rewind your database to an older point in time.<\/p>\n

This solution helps if it\u2019s possible to rewind the entire database. If there are multiple applications running on the same database this might not be feasible. And the amount of flashback logs needs to be calculated as well. For some situations it might be sufficient to use a guaranteed restore point. This feature is available even if flashback database is not enabled and can help when new applications or versions are rolled out. For a certain amount of time you can rewind the database to that specific point in time and if the application runs fine you can just remove the restore point and don\u2019t have to care about the flashback logs.<\/p>\n

Use a Standby database<\/h3>\n

If Data Guard or another solution is available as a disaster recovery solution you can simply stop the redo apply on the standby database. Than you can open the databases read only (or with a guaranteed restore point even read write) and unload the table or have a look at the table data. You only need to verify that your standby database hasn\u2019t applied the faulty batch already. But this can be achieved if you stop the apply right before the batch run. After the run is successfully completed you start the apply again. In the rare event that a disaster occurs right at that time you can still switch to the standby. It will probably take some few minutes more before the database has recovered all archived logs.<\/p>\n

Use an RMAN backup<\/h3>\n

With Oracle 12c table recovery is a “new” feature for RMAN. That\u2019s indeed helpful and can eliminate the need for an export in many cases. But how does it work?<\/p>\n

Unfortunately there is still no way to directly grab an object out of the rman backup. Instead with the table restore an auxiliary database is being created with the basic tablespaces SYSTEM, SYSAUX, and UNDO and with the tablespace holding the table to be recovered.<\/p>\n

Example for RMAN table restore:<\/h4>\n

RMAN> RECOVER TABLE demo.personen_drop<\/span>
      UNTIL TIME “to_date(‘31.05.2013 09:30:00′,’DD.MM.YYYY HH24:MI:SS’)” <\/span>
      AUXILIARY DESTINATION ‘\/u03\/orabackup\/recover’;<\/span><\/p>\n

The auxiliary destination is mandatory because that\u2019s the path for the data files. Per default the table will be created so if the table still exists the recovery will fail. To avoid this failure, maybe you just want to compare both tables the following options can be specified:<\/p>\n