Logical Backup strategy in large Oracle Databases

backupdatapumpdeleteoracle

in Oracle databases, I would like to know if it is advisable to make logical backups with exp or expdp in large databases with large volumes of data, greater than terabyte, I have seen several systems with Oracle that have large sizes and only perform physical backups With RMAN, I wonder what the strategy would be if a table was deleted for some reason …? What do you do in such cases? What would be a save strategy in these large Oracle?

Best Answer

With RMAN you can do point-in-time recovery (if properly) configured, so you could restore the database to the moment before the drop. Also, you can even restore just a single table from a backup. Other than that you have the recyclebin from which you can recover dropped tables and you have also Flashback Database. So to recover from a dropped table there are plenty of options - without an export.

In my career I have only once used daily exports as part of the backup strategy, but we stopped that once you could restore a single table from backup.

Regards, Daniel

Related Solutions

Centralize Oracle RMAN scripts in one server

Forget copying binaries around. I would integrate remote RMAN executions into your normal backup application/solution (i.e. filesystem backup to fire a script) for the sake of consistency. Especially scheduling of backups would be easier.

Re: "In this case parameterfile.par must exist in SRV_PROD_1". Not a problem if you dig into RMAN's own CREATE SCRIPT syntax - you can store the run{ } script inside your catalog.

Re: "rman.log will be created also on SRV_PROD_1". Not a problem if you take a look at catalog schema contests - surely there is one large table per each catalog that holds the logs anyway (I can't remember if it's ROUT or RMAN_OUT?).

Oracle – Testing on Logical Standby Database

My procedures seem valid... I was able to test the logical DB, then bring it back into an exact copy Logical Standby and re-enable SQL Apply without issues.

However, there was a minor change to my procedures: flashback database requires that the DB is in mount state and that alter database open requires resetlogs or noresetlogs.

Therefore, the amended procedures are as follows:

PRIMARY
alter system set log_archive_dest_state_3=defer scope=both;

STANDBY
alter database stop logical standby apply;
alter database guard none;
alter database flashback on;
create restore point before_testing guarantee flashback database;

*AFTER TESTING IS DONE*

STANDBY
shutdown immediate;
startup mount;
flashback database to restore point 'before_testing';
alter database open resetlogs;
alter database guard all;
alter database start logical standby apply immediate;
drop restore point before_testing;
alter database flashback off;

PRIMARY
alter system set log_archive_dest_state_3=enable scope=both;

Best Answer

Related Solutions

Centralize Oracle RMAN scripts in one server

Oracle – Testing on Logical Standby Database

Related Question