asked
Archive log over 300GB DB never backed up
I have an Oracle DB which has never been backed up, along with an archive log directory with over 300 GB of space used, I need to know if I can take an RMAN backup without the archive logs included and end up with a valid recovery point. I also need to delete the archive logs in order to have enough space to hold a backup. Regards
I am not sure what you meant by the recovery point? If you mean that you would be able to restore the database till you did the backup then yes, but only if it will be a consistent backup (one taken after the database has been shut down normally and cleanly). This would mean that restoring a consistent whole database backup without applying redo logs OR media recovery. Hence, one can restore even a year-old consistent backup of a database without performing media recovery and without Oracle performing instance recovery. Otherwise, If you want POINT-IN-TIME recovery, then it might not be possible. If I were you, I would indeed take full backup with option **PLUS ARCHIVELOG DELETE ALL INPUT** (Automatic deletion of Archived logs) to some other media. The backup size would be huge but this would have indeed put me in a much comfortable position. If this is not possible, to be on safe side, you should take the consistent backup (AKA cold backup), and restore it on another server (since looks like you are running out of space on current server). This would need a bit of maintenance, but it is better than living with no database to work with. Interestingly, what would be your backup strategy after this backup? If you would delete all the archived log files without initializing the log SCN (RESET LOGS), you would only be left with cold backups to work with. Which would mean no POINT-IN-TIME recovery. So what are you plans? [This][1] could be helpful if you want a clone [1]:
http://www.shutdownabort.com/quickguides/clone_cold.php
Thanks for the reply, just FYI I am not an Oracle DBA, I work with SQL Server and DB2, I inherited the server because I have a little Unix experience and that's it. I will be shutting the instance down to do the cold backup, the App does not have a 24/7 availability requirement, thankfully. I plan to do a cold backup every night and include the logs for that day but I need a baseline to start with, and I won't have enough space to hold the DB and the logs even after I delete all the archives that have accumulated. There is a process in place now which shutsdown the instance and does O/S copies of all directories associated with the instance and store them on a TSM server, but does not cleanup anything afterwards. That is the main reason I would like to start using RMAN to manage the space more efficiently as well as making backup and restore relatively painless. I have successfully copied all the files from the TSM server down to a development server, produced and RMAN backup of the DB and successfully restored and recovered the DB a couple of days later. I did notice that the temp TS was not copied and restored by RMAN, not sure how much of a problem that will be.
As far as TEMP is concerned, it is not meant be to be backed-up. Take it the same as TEMPDB in the SQL. But my only concern is, were you be able to do a POINT-IN-TIME recovery? I do not think so. (If you do not have the Archived Redo Logs which you have deleted OR manually initialized the logs after the consistent backup). Being an SQL DBA, you and I both know that POINT-IN-TIME recovery is a must, no matter 24x7 availability is required or not. Hope I am making sense.
Archivelogs without an initial backup to roll forward from are pretty useless. If you delete them, it will not remove them from the RMAN catalog (or controlfiles if you are not using a catalog) and you will have to login to RMAN and perform a crosscheck to determine what exists and what doesn't. Check here for a related reply about getting your backups working:
http://ask.sqlservercentral.com/questions/83734/oracle-backups-or-lack-there-of?page=1#83747
Yes, this is as equal to running in NOARCHIVELOG mode. This is where I wanted the OP to reset the logs, take a backup again, and all will be set to go with HOT BACKUPS, INCREMENTAL BACKUPS etc.
@Usman, I don't understand why you would want to "reset the logs". By this I assume you want to open the database with the RESETLOGS option. I can't see any reason for doing this. The poster is not running in the equivalent to NOARCHIVELOG mode (although I kunderstand what you mean with his shutdowns). He is in ARCHIVELOG mode, and can perform a transactionally consistent backup with the database fully active, right now. No shutdown or resets needed. Let's not potentially confuse an inexperienced person with the use of analogy. The poster just needs to login and perform a backup, and throw away their archive log from BEFORE the backup. rman target system/pwd nocatalog backup database; They then need to setup a regular RMAN backup. They probably need to look at how the backups are named, as the default "%U" substitution name gives a cryptically named file. I would also highly recommend using the TAG="label" to help with identifying backups. They MUST also backup their controlfiles (use the autobackup option in RMAN) and look into using a catalog. Here's a start for some RMAN config parameters - examine their cause in the manuals: CONFIGURE RETENTION POLICY TO REDUNDANCY 3; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\app\oracle\orabackup\%d\%d_CF_%F'; CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'D:\app\oracle\orabackup\%d\%d_DB_D-%I_%T_%t_%s-%p'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default Neil.
@ChandlerDBA By equivalent to NOARCHIVELOG, I meant that if you are taking only cold backups, then no point in running the ARCHIVELOG. Would you be able to do POINT-IN-TIME RECOVERY with cold backups? I guess if possible it would be too hard. So if it is not equivalent then it is as near to NOARCHIVELOG mode to me. Secondly, if there is no backup done since from the very start, how would you go with deleting all the archive logs without backing them up, then try to take the hot backups? AFAIK, the backup taken would be dependent upon all the archive logs if you would be requiring point-in-time recovery. Please, correct me if I am wrong.
@Usman You can just go in and delete the archive logs off disk. They aren't needed really. Delete them, go into rman and crosscheck them so rman knows they have been deleted (this is not best practice, but this is not a normal situation and needs to be remediated first before going to best practice.) rman target system/pwd nocatalog list archivelog all; crosscheck archivelog all; list archivelog all; report obsolete; delete obsolete; list expired backup; delete expired backup; run { sql 'alter system archive log current'; backup database plus archivelog delete all input tag="First Backup"; } crosscheck backup; list backup summary; list backup; @Chandlerdba p.s. Sorry for the different account but I have techincal issues with the other one and no word from the Admins so it is being abandoned.
Neil, Thanks for taking up your time to assist me with this issue, I am certainly in an uncomfortable position. I agree with the points you have made in your previous posts, as my options are limited I will have to move on with a quick fix for the present and implement best practices going forward. My current plan is to remove all archive logs prior to 2012 to free up enough space for a backup (image copy) of the production database and hopefully to relieve the constant growth I am experiencing in the FRA. I have had to increase it's size from 10 GB to 20 GB which puzzled me due to the retention policy being configured as redundancy 1 (default), along with not having specified a DB_FLASHBACK_RETENTION_TARGET initialization parameter which defaults to 1 day, I can't figure out why the FRA still contains all logs that have been moved to the LOG_ARCHIVE_DEST_n directory. I also recently discovered the Flashback Database feature is not even activated for this database, I'm guessing that is also something wrong with this instance. Sorry for the ramble but as I stated earlier, I'm out of my league and short on time to resolve this problem (week at the most) so please correct the following scenario if I am at fault: 1. Delete all logs prior to 2012 with O/S commands (FRA and Archived) 2. Log into RMAN and connect to target, shutdown and then startup mount; 3. Run CROSSCHECK ARCHIVELOG ALL; - to cleanup the controlfile 4. Run REPORT OBSOLETE; - to show what will be removed 5. Run DELETE OBSOLETE; 6. Run sql 'ALTER SYSTEM ARCHIVE LOG CURRENT'; - I think I read I need to do this before shutdown 7. Run BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT TAG="First Full BK" 8. Run CROSSCHECK BACKUP; When I alter the database open, should I also specify RESETLOGS? I really appreciate your help. Regards
In my previous post I listed the steps I believe I need to take in order to crate a baseline recovery point, after further investigation I am thinking I will delete all logs present on the day I execute this process because none of the prior logs will be of any use anyway, as they aren't now. then I will log into RMAN, run the commands to clear the RMAN repository of all references to prior logs/backups,(which there are no backups), then archive the current log, backup the database plus archivelog - correct me if I am wrong but there should only be one, and then crosscheck the backup. I would still like to know if it will be necessary to specify 'resetlogs' when I alter the database open.
