2 Replies Latest reply: Nov 8, 2012 11:00 AM by Levi Pereira RSS

    Understand All Pieces of Backup

    895327
      I'm trying to understand what all the pieces of my backup consist of and why they're placed where they are. I don't have a good / solid comprehension of what exactly is what file and where it should be / why it's needed.

      My Oracle database server is running 11.2.0.3. It has the following RMAN parameters:
      SQL> show parameter db_recovery;
      
      NAME                         TYPE      VALUE
      ------------------------------------ ----------- ------------------------------
      db_recovery_file_dest               string      /oracle/u01/app/oracle/backup
      db_recovery_file_dest_size          big integer 80G
      As well as these configuration settings for the RMAN utility:
      RMAN> show all;
      
      using target database control file instead of recovery catalog
      RMAN configuration parameters for database with db_unique_name CQDB are:
      CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
      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 '%F'; # default
      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 DEVICE TYPE DISK FORMAT   '/oracle/u01/app/oracle/backup/%U';
      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 ARCHIVELOG DELETION POLICY TO NONE; # default
      CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_cqdb.f'; # default
      Now when I perform a backup w/ the following command:
      RMAN>backup full as compressed backupset database plus archivelog delete input;
      Now to my understanding, the above backup command creates three seperate files, correct? Datafile (generally all the data stored in the RDBMS), controlfile (index structure), and archive logs. Am I correct in at least this understanding? The above command backs up those three files or am I wrong?

      When I look into the folder that RMAN is assigned for backups, I see:
      [oracle@cq backup]$ pwd
      /oracle/u01/app/oracle/backup
      [oracle@cq backup]$ ls -lh
      total 11G
      -rw-r----- 1 oracle oinstall 2.2G Oct 12 16:01 6onnivu8_1_1
      -rw-r----- 1 oracle oinstall 714M Oct 30 17:07 6qnp2ja2_1_1
      -rw-r----- 1 oracle oinstall 712M Oct 30 17:12 6rnp2jnb_1_1
      -rw-r----- 1 oracle oinstall 2.2G Oct 30 17:29 6snp2k0j_1_1
      -rw-r----- 1 oracle oinstall 1.4M Oct 30 17:29 6tnp2kv6_1_1
      -rw-r----- 1 oracle oinstall 673M Nov  7 17:04 6vnpnm6g_1_1
      -rw-r----- 1 oracle oinstall 2.3G Nov  7 17:25 70npnmhq_1_1
      -rw-r----- 1 oracle oinstall 2.1M Nov  7 17:25 71npnnp6_1_1
      -rw-r----- 1 oracle oinstall  63M Nov  8 08:53 73nppe3c_1_1
      -rw-r----- 1 oracle oinstall 2.3G Nov  8 09:10 74nppe49_1_1
      -rw-r----- 1 oracle oinstall 1.6M Nov  8 09:10 75nppf4f_1_1
      drwxr-x--- 4 oracle oinstall 4.0K Oct 12 16:01 CQDB
      Can anyone explain what these files are that vary in size from a a few MB's to a few GB's. Are the above my actual "data" files for the schemas?

      Then there's a 'CQDB' folder which is obviously my SID on this instance:

      When I go into the CQDB folder, I see an archivelog folder:
      [oracle@cq backup]$ cd CQDB/
      [oracle@cq CQDB]$ ls -l
      total 8
      drwxr-x--- 9 oracle oinstall 4096 Nov  8 09:10 archivelog
      drwxr-x--- 6 oracle oinstall 4096 Nov  8 09:10 autobackup
      [oracle@cq CQDB]$ cd archivelog/
      [oracle@cq archivelog]$ ls -l
      total 28
      drwxr-x--- 2 oracle oinstall 4096 Nov  7 17:04 2012_11_02
      drwxr-x--- 2 oracle oinstall 4096 Nov  7 17:04 2012_11_03
      drwxr-x--- 2 oracle oinstall 4096 Nov  7 17:04 2012_11_04
      drwxr-x--- 2 oracle oinstall 4096 Nov  7 17:04 2012_11_05
      drwxr-x--- 2 oracle oinstall 4096 Nov  7 17:04 2012_11_06
      drwxr-x--- 2 oracle oinstall 4096 Nov  8 08:53 2012_11_07
      drwxr-x--- 2 oracle oinstall 4096 Nov  8 09:10 2012_11_08
      [oracle@cq archivelog]$ cd 2012_11_08/
      [oracle@cq 2012_11_08]$ ls -l
      total 0
      It looks like my 'control file' is placed in the following separate location for some reason:
      [oracle@cq cqdb]$ pwd
      /oracle/u01/app/oracle/flash_recovery_area/cqdb
      [oracle@cq cqdb]$ ls -lah
      total 9.9M
      drwxr-x--- 2 oracle oinstall 4.0K Oct 12 11:33 .
      drwxr-xr-x 3 oracle oinstall 4.0K Oct 12 11:32 ..
      -rw-r----- 1 oracle oinstall 9.9M Nov  8 10:24 control02.ctl
      Can anyone please help me understand my RMAN backups and what's what and where?
        • 1. Re: Understand All Pieces of Backup
          mseberg
          Carlos;

          Is yesterday's question answered? If yes can you close it?
          RMAN>backup full as compressed backupset database plus archivelog delete input;
          No. Use this :
          backup full as compressed backupset database include current controlfile plus archivelog delete input;
          For this :
          Can anyone explain what these files are that vary in size from a a few MB's to a few GB's. Are the above my actual "data" files for the schemas?
          The files vary in size because one might just be a control file and another might be a group of data files. So to "data" files Mostly - yes.

          What I would do is buy a book - Check Amazon for Sam Alapati, or Darl Kuhn

          Best Regards

          mseberg
          • 2. Re: Understand All Pieces of Backup
            Levi Pereira
            You need use RMAN command LISTBACKUP to identify each backuppiece and your contents.
            SQL> show parameter db_recovery;
             
            NAME                         TYPE      VALUE
            ------------------------------------ ----------- ------------------------------
            db_recovery_file_dest               string      /oracle/u01/app/oracle/backup
            
            CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/oracle/u01/app/oracle/backup/%U';
            Although you have configured FRA on "/oracle/u01/app/oracle/backup" you are not using it to store your backup. Because you specifiy where you backup will be stored " '/oracle/u01/app/oracle/backup/%U'".

            If you want to use FRA I recommed you clear configuration on "CHANNEL DEVICE TYPE DISK".
            CONFIGURE CHANNEL DEVICE TYPE DISK  CLEAR;
            [oracle@cq CQDB]$ ls -l
            total 8
            drwxr-x--- 9 oracle oinstall 4096 Nov  8 09:10 archivelog
            drwxr-x--- 6 oracle oinstall 4096 Nov  8 09:10 autobackup
            [oracle@cq CQDB]$ cd archivelog/ 
            
            CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
            You are using FRA to store ARCHIVELOG and AUTOBACKUP only.
            Autobackup is stored on FRA since you are using "#default" configuration of location to store autobackup.
            Archivelog is stored on FRA since you have not set any parameter "log_archive_dest_*" of database.

            You can check it using command below.
            SQL> archive log list;
            Database log mode              Archive Mode
            Automatic archival             Enabled
            Archive destination            USE_DB_RECOVERY_FILE_DEST
            Now to my understanding, the above backup command creates three seperate files, correct? Datafile (generally all the data stored in the RDBMS), controlfile (index structure), and archive logs. Am I correct in at least this understanding? The above command backs up those three files or am I wrong?
            No, You are wrong.

            These files are only backuppiece of ARCHIVELOS and DATAFILES.

            When you issue "backup full... plus archivelog" Oracle RMAN backup the all archivelogs first, then all datafiles and at last all archivelogs generated (or Oracle force it) after backup of datafiles.

            So I can say:
            -rw-r----- 1 oracle oinstall  63M Nov  8 08:53 73nppe3c_1_1 #### Here is ARCHIVELOGS generated between you last backup until before start backup of datafile
            -rw-r----- 1 oracle oinstall 2.3G Nov  8 09:10 74nppe49_1_1 #### Here is only DATAFILES 
            -rw-r----- 1 oracle oinstall 1.6M Nov  8 09:10 75nppf4f_1_1 #### Here is ARCHIVELOGS generated during or after backup of datafile started
            My recommendation to a better understand:
            ### Configure your backup to store on FRA by issuing command below
            
            RMAN >  CONFIGURE CHANNEL DEVICE TYPE DISK  CLEAR; 
            
            ### Then change backup command
            
            RMAN> backup full 
                     as compressed backupset database
                     include current controlfile tag 'backup_full_datafiles'
                     plus archivelog tag 'backup_archivelogs'
                     delete input;
            
            ### as example below:
            
            Starting backup at 08-NOV-12
            
            ### Oracle force archive of  current log and backup all archvielog on DISK and remove it
            
            
            current log archived
            using target database control file instead of recovery catalog
            allocated channel: ORA_DISK_1
            channel ORA_DISK_1: SID=98 device type=DISK
            channel ORA_DISK_1: starting compressed archived log backup set
            channel ORA_DISK_1: specifying archived log(s) in backup set
            input archived log thread=1 sequence=435 RECID=384 STAMP=798820346
            channel ORA_DISK_1: starting piece 1 at 08-NOV-12
            channel ORA_DISK_1: finished piece 1 at 08-NOV-12
            piece handle=/u01/app/oracle/flash_recovery_area01/DBTST/backupset/2012_11_08/o1_mf_annnn_BACKUP_ARCHIVELOGS_89qqnwfb_.bkp tag=BACKUP_ARCHIVELOGS comment=NONE
            channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
            channel ORA_DISK_1: deleting archived log(s)
            archived log file name=/u01/app/oracle/flash_recovery_area01/DBTST/archivelog/2012_11_08/o1_mf_1_435_89qqntbc_.arc RECID=384 STAMP=798820346
            Finished backup at 08-NOV-12
            
            Starting backup at 08-NOV-12
            using channel ORA_DISK_1
            
            ### Oracle Started backup of ALL DATAFILES
            
            channel ORA_DISK_1: starting compressed full datafile backup set
            channel ORA_DISK_1: specifying datafile(s) in backup set
            input datafile file number=00001 name=+DS8000_DG/dbtst/datafile/system.291.793638779
            .
            .
            .
            input datafile file number=00004 name=+DS8000_DG/dbtst/datafile/users.293.793638849
            channel ORA_DISK_1: starting piece 1 at 08-NOV-12
            channel ORA_DISK_1: finished piece 1 at 08-NOV-12
            piece handle=/u01/app/oracle/flash_recovery_area01/DBTST/backupset/2012_11_08/o1_mf_nnndf_BACKUP_FULL_DATAFILE_89qqo002_.bkp tag=BACKUP_FULL_DATAFILES_CONTROL comment=NONE
            channel ORA_DISK_1: backup set complete, elapsed time: 00:06:55
            channel ORA_DISK_1: starting compressed full datafile backup set
            channel ORA_DISK_1: specifying datafile(s) in backup set
            
            ### ORACLE Finished Backup of DATAFILES and are Backup Current Controlfile 
            
            including current control file in backup set
            channel ORA_DISK_1: starting piece 1 at 08-NOV-12
            channel ORA_DISK_1: finished piece 1 at 08-NOV-12
            piece handle=/u01/app/oracle/flash_recovery_area01/DBTST/backupset/2012_11_08/o1_mf_ncnnf_BACKUP_FULL_DATAFILE_89qr1zqn_.bkp tag=BACKUP_FULL_DATAFILES_CONTROL comment=NONE
            channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
            Finished backup at 08-NOV-12
            
            
            Starting backup at 08-NOV-12
            
            ### Oracle backup force archive current log and backup all archivelog on DISK and remove it
            
            current log archived
            using channel ORA_DISK_1
            channel ORA_DISK_1: starting compressed archived log backup set
            channel ORA_DISK_1: specifying archived log(s) in backup set
            input archived log thread=1 sequence=436 RECID=385 STAMP=798820769
            channel ORA_DISK_1: starting piece 1 at 08-NOV-12
            channel ORA_DISK_1: finished piece 1 at 08-NOV-12
            piece handle=/u01/app/oracle/flash_recovery_area01/DBTST/backupset/2012_11_08/o1_mf_annnn_BACKUP_ARCHIVELOGS_89qr21ps_.bkp tag=BACKUP_ARCHIVELOGS comment=NONE
            channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
            channel ORA_DISK_1: deleting archived log(s)
            archived log file name=/u01/app/oracle/flash_recovery_area01/DBTST/archivelog/2012_11_08/o1_mf_1_436_89qr215y_.arc RECID=385 STAMP=798820769
            Finished backup at 08-NOV-12
            
            ### Autobackup have current controlfile and SPFILE
            
            Starting Control File and SPFILE Autobackup at 08-NOV-12
            piece handle=/u01/app/oracle/flash_recovery_area01/DBTST/autobackup/2012_11_08/o1_mf_s_798820771_89qr23qf_.bkp comment=NONE
            Finished Control File and SPFILE Autobackup at 08-NOV-12
            To restore this database I need:
            oracle@aix:/u01/app/oracle/flash_recovery_area01/DBTST>
            
            drwxr-x---    3 oracle   asmadmin       4096 Nov  8 14:39 backupset ### Here is my backup of ARCHIVELOG and DATAFILE and "included current controlfile"
            drwxr-x---    3 oracle   asmadmin       4096 Nov  8 14:39 archivelog ###  Here is archivelogs  (I don't need this directory) to restore
            drwxr-x---    5 oracle   asmadmin       4096 Nov  8 14:39 autobackup ### Here is AUTOBACKUP that include "current controfile" and "SPFILE"
            
            ### Here I have 2 backuppiece of ARCHIVELGO and 1 Backup piece of Controlfile and 1 backup piece of Datafiles  
            oracle@aix:/u01/app/oracle/flash_recovery_area01/DBTST/backupset/2012_11_08> ls -ltr
            -rw-r-----    1 oracle   asmadmin       4608 Nov  8 14:32 o1_mf_annnn_BACKUP_ARCHIVELOGS_89qqnwfb_.bkp ### archivelog
            -rw-r-----    1 oracle   asmadmin 1632272384 Nov  8 14:39 o1_mf_nnndf_BACKUP_FULL_DATAFILE_89qqo002_.bkp ## datafile
            -rw-r-----    1 oracle   asmadmin    1196032 Nov  8 14:39 o1_mf_ncnnf_BACKUP_FULL_DATAFILE_89qr1zqn_.bkp ## controlfile
            -rw-r-----    1 oracle   asmadmin     110592 Nov  8 14:39 o1_mf_annnn_BACKUP_ARCHIVELOGS_89qr21ps_.bkp ### archivelog
            
            ### here backup of autobackup
            oracle@aix:/u01/app/oracle/flash_recovery_area01/DBTST/autobackup/2012_11_08> ls -tlr
            total 15816
            -rw-r-----    1 oracle   asmadmin    8093696 Nov  8 14:39 o1_mf_s_798820771_89qr23qf_.bkp
            Really does not matter the filename, RMAN handle with this and restore right files, you will need catalog FRA and RMAN will find your files.