Forum Stats

  • 3,824,996 Users
  • 2,260,452 Discussions
  • 7,896,378 Comments

Discussions

Where do archivelogs go when backedup/deleted

4187794
4187794 Member Posts: 23
edited Mar 12, 2020 11:24AM in Recovery Manager (RMAN)

Considering the command: backup archivelog all delete all input

Here's the deal: Yesterday, I woke up to 2 prod databases being hung. I found that the volume labeled  "archivelog" was full. Looking in archivelog, i see folders for each database, and the two prod databases chopped full of dbf files.

I have found after some research, that RMAN > "crosscheck archivelog all", followed by the above command, after some time - frees up all the space down to about 15% once I do the two main databases. Then, I can shutdown and startup each, and we are okay.

It fills back up at a rate of 4% per hour. So, after about 20 hours, it got full and locked up again, so I did the same this morning to get us back online.


I am wondering where these files go, physically, when they are backed up before deleting. I do not see them on my backup volumn, i see full and incremental backups but nothing like this. So, I am just trying to understand, and this is not easy.

Note: Our DBA has fallen into coma, it has been 2months. I am trying to keep us afloat, without prior Oracle knowledge.

Here is the config for main db:

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name P005C are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u1/rman/p005c/%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 3 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 FORMAT   '/u1/rman/p005c/incr_%d_set%s_piece%p_%T_%U';

CONFIGURE MAXSETSIZE TO 50 G;

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 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/f5/app/oracle/product/19.3/dbhome_1/dbs/snapcf_p005c.f'; # default

Looking for some insight while I try to figure out how or why this started happening Coincidentally, or not, this started the night after the size of 5 dbf datafiles were increased due to tablespace being out of space and users not being able to upload documents in one of our apex portals. Looking at those - they have barely added to their 'used' stat. 

I understand these archivelogs store transactional changes not data itself?

Thank you for any help,

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 12, 2020 10:15AM

    "Where do we begin?"

    4187794 wrote:Considering the command: backup archivelog all delete all inputHere's the deal: Yesterday, I woke up to 2 prod databases being hung. I found that the volume labeled "archivelog" was full. Looking in archivelog, i see folders for each database, and the two prod databases chopped full of dbf files. 

    What does it mean for a database to be "chopped full of dbf files"?  Do you mean the 'folders' (directories) named after each database were "chopped full of dbf files"?  Either way, the term 'dbf file' really has no meaning.  'dbf' is just part of a file name and does not impose any special properties on the file.  Since the files that actually make up the database are typically named 'dbf', I prefer to name all other files (including archivelogs) something else, like 'arc'.  But perhaps you name both your data files and archivelog files 'dbf'.  Connect to the database with sqlplus and issue the command 'show parameter log_archive_format'.  Copy the command and result, and paste into your next message.

    BACKUP ARCHIVELOG ... DELETE INPUT writes the archivelogs into an rman backup piece, then once safely backed up, deletes the archivelog itself.

    I have found after some research, that RMAN > "crosscheck archivelog all", followed by the above command, after some time - frees up all the space down to about 15% once I do the two main databases. Then, I can shutdown and startup each, and we are okay.

    CROSSCHECK ARCHIVELOG ALL has nothing to do with backups.  When an archivelog is written, a record of that fully qualified file name is written to the repository in the database control file.  CROSSCHECK simply reads those control file records and checks that the file is still there. If it is found to have gone missing (perhaps someone manually deleted it with an OS command) then the record in the control file is marked 'expired'.  A later DELETE EXPIRED command will delete the expired records from the control file.  It cannot delete the files themselves because, by definition, an 'expired' file no longer exists to be deleted.

    It fills back up at a rate of 4% per hour. So, after about 20 hours, it got full and locked up again, so I did the same this morning to get us back online. 
    I am wondering where these files go, physically, when they are backed up before deleting. I do not see them on my backup volumn, i see full and incremental backups but nothing like this. So, I am just trying to understand, and this is not easy.

    As I said above, they are written into an rman backup file (backup piece, part of a backup set).  You won't see the archivelogs, as such, in that directory.  But if you issue an rman 'list backup', you will see them listed.

    Note: Our DBA has fallen into coma, it has been 2months. I am trying to keep us afloat, without prior Oracle knowledge. Here is the config for main db:RMAN> show all;using target database control file instead of recovery catalogRMAN configuration parameters for database with db_unique_name P005C are:CONFIGURE RETENTION POLICY TO REDUNDANCY 2;CONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO DISK; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u1/rman/p005c/%F';CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # defaultCONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u1/rman/p005c/incr_%d_set%s_piece%p_%T_%U';CONFIGURE MAXSETSIZE TO 50 G;CONFIGURE ENCRYPTION FOR DATABASE OFF; # defaultCONFIGURE ENCRYPTION ALGORITHM 'AES128'; # defaultCONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # defaultCONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # defaultCONFIGURE SNAPSHOT CONTROLFILE NAME TO '/f5/app/oracle/product/19.3/dbhome_1/dbs/snapcf_p005c.f'; # defaultLooking for some insight while I try to figure out how or why this started happening Coincidentally, or not, this started the night after the size of 5 dbf datafiles were increased due to tablespace being out of space and users not being able to upload documents in one of our apex portals. Looking at those - they have barely added to their 'used' stat. 

    Disk is cheap.  Perhaps you should consider adding more?

    Or, alternatively, schedule a job to run frequently (hourly?) that backs up only the archivelogs.  This is pretty common practice in high-volume installations - run a daily backup of the database, and an hourly backup of just the archivelogs - including the 'delete all input' option.  The database backup could also include an archivelog backup.

    I understand these archivelogs store transactional changes not data itself? 

    Correct. Archivelogs contain change vectors.

  • 4187794
    4187794 Member Posts: 23
    edited Mar 12, 2020 11:20AM

    Hey thanks for replying! Ok, let's see.. Let me read through all of this and give you replies. I will say real quick, I work for an agency with contracts and disk space is anything but cheap, its a monthly cost too, but adding space is okay. It's that I don't want to add it to find out we have an overwhelming problem and that space will too fill up quick and we'll find that this was just a bandaide. That said, we are increasing from 200 to 300gb (for this 1 of 8 SAN mount points, it holds only the archivelogs) , so i don't have to wake up at 3am to fix things, every day while we figure this out.. some buffer room.

    I will update again today with replies to your points. Thank you!

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 12, 2020 11:24AM
    4187794 wrote:Hey thanks for replying! Ok, let's see.. Let me read through all of this and give you replies. I will say real quick, I work for an agency with contracts and disk space is anything but cheap, its a monthly cost too, but adding space is okay. It's that I don't want to add it to find out we have an overwhelming problem and that space will too fill up quick and we'll find that this was just a bandaide. That said, we are increasing from 200 to 300gb (for this 1 of 8 SAN mount points, it holds only the archivelogs) , so i don't have to wake up at 3am to fix things, every day while we figure this out.. some buffer room.I will update again today with replies to your points. Thank you!

    So look at the alternative -- more frequent archivelog-only backups with DELETE ALL INPUT.  I trust the backups are not going to the same mount-point as the archivelogs themselves?