Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
RMAN, preventing to delete backups, needed to go back to 10 days ago on test database

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';
Answers
-
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:
-
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.
-
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
-
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
-
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.
-
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
-
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
-
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.
-
ok Dude many thanks for your attention,
we started another the discussion for Recover Table command under RMAN.
best regards