RMAN, preventing to delete backups, needed to go back to 10 days ago on test database

orasiya

Dear friends;

Getting backup with RMAN in my production database. I have to go back to the backup 10 days ago in the test database in order to do recovery on  truncated table.(using backupset at production)

How to  copy/move all of the required  backupset / backup / spfile / controlfile  pieces for specific date or  tag  to  another location (+RECOC/Backup10daysego_All/) to prevent from deleting by RMAN automatically.

best regrads

siyavus

CONFIGURE RETENTION POLICY TO REDUNDANCY 15;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 32 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 32 G;

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 30 DAYS;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECOC/orcl/CONTROLFILE/snapcf_orcl.f';

Dude!

RMAN does not delete anything unless you tell RMAN to do so. If you use FRA, then Oracle will start removing obsolete backups when you fall under 15 % free space.

You can move RMAN backups anywhere you like, but you will need to re-catalog the location. You need to restore the controlfile and spfile, and recatalog your RMAN backups using the RMAN "catalog start with /path" command. RMAN does not have commands to move backupsets arround. The location of backupsets is stored in the controlfile of the database. You can copy backups using the OS command, and the ASMCMD utility to copy them to an ASM diskgroup.

If the controlfile and spfile backup is outside FRA, or you have no controlfile autobackup, or no RMAN catalog, then you need to specify the the RMAN backup set that contains the backup.

Example:

Dude!

Btw, how do you know backups were deleted, and did not simply age out in the controlfile?

CONFIGURE RETENTION POLICY TO REDUNDANCY 15;

What is your setting of the control_file_record_keep_time parameter? By default it is 7 days, after which reusable RMAN records are potentially overwritten with new data, also depending on how much space for these records was previously allocated resp. required.

Again, RMAN does not delete anything unless instructed, regardless of whatever retention policy. People often assume setting the retention policy to e.g. 15 days, means everything older than 15 will be deleted - that's not the purpose of the retention policy and not how it works.

If you need to maintain RMAN records for archiving purpose, you should setup a RMAN catalog database. To move backupsets around in ASM Diskgroups, use the ASMCMD utility.

orasiya

Dear oracle dba Dude;

As you specified, "You can move RMAN backups anywhere you like, but you will need to re-catalog the location. )"

but how do I exactly move / copy  the backup pieces / Backup set to another safe location compeletly?

best regards

orasiya

Dear Dude,

Thank your valuable response

after truncaated event , I  changed  some setting as

CONFIGURE RETENTION POLICY TO REDUNDANCY 100;

change control_file_record_keep_time to 30 for  all nodes

regards

Dude!

Dear Dude,Thank your valuable response after truncaated event , I  changed  some setting as CONFIGURE RETENTION POLICY TO REDUNDANCY 100;  change control_file_record_keep_time to 30 for  all nodes

With this new setting, you potentially run into two troubles:

a) after 30 days, older backups will become unknown to RMAN, and you probably run out of disk space for no apparent reason.

b) Unless you manage to make more than 100 redundant backups within 30 days, RMAN delete obsolete will never find obsolete data.

Those two combined is a recipe for failure.

Better, set the redundancy to a time frame that you require to be able to restore and recover your database. Any backups to meet this goal will not become obsolete. Install a RMAN catalog database for long time storage of RMAN metadata (copied from the target controfile to the catalog database).

RMAN to my knowledge does not have a "move or copy backupset" command. You can use RMAN backup backupsets, but as I recall this will copy backupsets from disk to tape. Use the OS or ASMCMD utility. I would not store RMAN backups in an ASM diskgroup and use a standard filesystem or a mounted ASM volume (ACFS) instead. You can then use OS level backup software to backup the RMAN backups and restore them when required.

orasiya

Thanks Dude,

we use 18c Ent. Ed. database, could we use recover table under rman for sys.aud$

may I recover any table under sys, system or sysaux schemas  in 18c ?

RMAN>Recover table 'SYS'.'AUD$'  until time "to_date('06/01/2020 00:05:00,'mm/dd/yyyy hh24:mi:ss')"  auxiliary destination '/u01/app/oracle/rman'   remap table 'SYS'.'AUD$':AUD_Restored'; 

regards
 

orasiya

I get the following error,

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Jun 24 13:56:25 2020

Version 18.9.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

RECOVER TABLE 'SYS'.'AUD$'  UNTIL TIME "TO_DATE('01-01-2020 12:01', 'DD-MM-YYYY HH24:MI')"   AUXILIARY DESTINATION '/my_storage/aux_u01/auxi' REMAP TABLE 'SYS'.'AUD$':'AUD_PRE';

Starting recover at 24-JUN-20

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 06/24/2020 13:58:47

RMAN-05063: Cannot recover specified tables

RMAN-05056: Table SYS.AUD_PRE belongs to SYS

RMAN-05056: Table SYS.AUD$ belongs to SYS

Dude!

Sorry, I don't see what this has to do with the initial topic of this thread. If you wish to get answers to other questions, please start a new thread.

orasiya

ok Dude many thanks for your attention,

we started another the discussion for  Recover Table command under RMAN.

best regards