Oracle: Archivelog mode physical backup

Title:

Oracle: Archivelog mode physical backup

Author:

Douglas O’Leary <dkoleary@olearycomputers.com>

Description:

Oracle: Archivelog mode physical backup

Date created:

07/21/2010

Date updated:

07/21/2010

Disclaimer:

Standard: Use the information that follows at your own risk. If you screw up a system, don’t blame it on me…

If your database is in archivelog mode, and you want to run a physical backup, follow this process:

1. ID location for:

   1. oracle datafiles

   2. redo logs

   3. archive log directory

2. Shutdown the database

3. Backup all datafiles

4. Restart the database

5. Force an online redo log switch using alter system switch logfile. Once the logs have been archived, back up all archived redo logs.

6. Create a backup of the control file using the alter database backup controlfile to trace and alter database backup controlfile to ‘${file}’ commands

Can do all of the recovery options that are enabled by archivelog mode; however, you do it all manually.

So, using my little database as an example:

1. ID file locations:

   1. datafiles:

      SQL> select file_name, tablespace_name from dba_data_files
        2  order by file_name;
      
      FILE_NAME                                TABLESPACE_NAME
      ---------------------------------------- --------------------
      /oracle/oradata/oci1/example01.dbf       EXAMPLE
      /oracle/oradata/oci1/sysaux01.dbf        SYSAUX
      /oracle/oradata/oci1/system01.dbf        SYSTEM
      /oracle/oradata/oci1/undotbs01.dbf       UNDOTBS1
      /oracle/oradata/oci1/users01.dbf         USERS
      

   2. Redologs:

      SQL> select group#, member from v$logfile
        2  order by member;
      
          GROUP# MEMBER
      ---------- ---------------------------------------------
               1 /oracle/oradata/oci1/redo01.log
               1 /oracle/oradata/oci1/redo01a.log
               2 /oracle/oradata/oci1/redo02.log
               2 /oracle/oradata/oci1/redo02a.log
               3 /oracle/oradata/oci1/redo03.log
               3 /oracle/oradata/oci1/redo03a.log
      

   3. Archive log directory:

      SQL> archive log list
      Database log mode              Archive Mode
      Automatic archival             Enabled
      Archive destination            USE_DB_RECOVERY_FILE_DEST
      Oldest online log sequence     409
      Next log sequence to archive   411
      Current log sequence           411
      SQL> show parameter db_recovery_file_dest
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      db_recovery_file_dest                string      /oracle/flash_recovery_area
      db_recovery_file_dest_size           big integer 20G
      

2. SQL> shutdown immediate

3. Backup datafiles:

   $ tar -czvf ../dol_backup/100720_ocidb.tgz $(cat oci_files )
   tar: Removing leading `/' from member names
   /oracle/oradata/oci1/example01.dbf
   /oracle/oradata/oci1/sysaux01.dbf
   /oracle/oradata/oci1/system01.dbf
   /oracle/oradata/oci1/temp01.dbf
   /oracle/oradata/oci1/undotbs01.dbf
   /oracle/oradata/oci1/users01.dbf
   SQL> startup
   

4. Force log switch:

   SQL> select group#, members, archived, status from v$log;
   
       GROUP#    MEMBERS ARC STATUS
   ---------- ---------- --- ----------------
            1          2 YES INACTIVE
            2          2 NO  CURRENT
            3          2 YES INACTIVE
   SQL> alter system switch logfile;
   
   System altered.
   
   SQL> select group#, members, archived, status from v$log;
   
       GROUP#    MEMBERS ARC STATUS
   ---------- ---------- --- ----------------
            1          2 YES INACTIVE
            2          2 YES ACTIVE
            3          2 NO  CURRENT
   

5. Back up control file:

   SQL> alter database backup controlfile to trace
   SQL> alter database backup controlfile to '/home/oracle/dol_backup/100720_controlfile'
   

So, now I should be able to blow that puppy away and recreate it using the steps in the recovery checkilst

//Later that same day

Ok, honestly, it’s the next day. Curiously enough, this process didn’t work for me. When I tried it, the recover database command failed saying I had to use the recover database using backup controlfile. I entered auto, but the last archive log wasn’t present, for some reason. So, I recovered the database until cancel at which point, I was able to open the database but had to resetlogs.

My next effort, was to back up the entire oracle data directory and all the archive logs to one tgz file. When I blew everything away and restored it, I was able to start the database right up again - without database recovery (which makes sense).