In Oracle : Cold Backup in Oracle (User Managed Backup)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> Cold Backup in Oracle (User Managed Backup)

 

 Cold Backup in Oracle (User Managed Backup)

 

 

Here are the main topics for this article:

 

  1.  User Managed Backup Overview

  2.  How could I take an online TABLESPACE level backup ?

  3.  Which are the files which could be backed up ?

  4.  How could I take an online FILE level backup ?

  5.  TABLESPACE Recovery

  6.  DATAFILE Recovery

  7.  Closed Database Backup

  8.  Open Database Backup

  9.  Backup the CONTROL file

10.  Perform Cleanup after a Failed Online Backup

 

 

1. User Managed Backup Overview

 

A user-managed backup is made by performing a physical copy of data files using the OS commands. These copies are moved to a separate location using OS commands. The user maintains a record of the backups. For the recovery operation we have to move back (or to the new location of the database) the files and perform the recovery.  

The user-managed backups could be take at the following levels:

  • Data file level

  • Tablespace level

  • Database level 

 


2. How could I take an online TABLESPACE level backup ?

  • the database should be in ARCHIVELOG mode

  • put the tablespace in "Begin Backup" mode  (example:  ALTER TABLESPACE users BEGIN BACKUP;  )

  • copy the physical files associated with this tablespace on another location using OS commands

  • put the tablespace in "End Backup" mode  (example:  ALTER TABLESPACE users END BACKUP;  )

  • Archive the unachieved redo logs so that the redo required to recover the tablespace backups is archived                                    (  SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;  )

  • Take a backup of all archived redo log files generated between Begin Backup and End Backup using OS commands

NOTES: 

  • Many tablespaces could be backed up in parallel. However, online redo logs can grow large if multiple users are updating these tablespaces because the redo must contain a copy of each changed data block. Oracle doesn't recommend this.

  • When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files. When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups. To fix this problem, simply switch to RMAN backups.

  • If the tablespace is in READ ONLY mode, we don't need to put the tablespace in Backup Mode. 

 


  3. Which are the files which could be backed up ?

 

    SELECT name FROM v$datafile;

    SELECT member FROM v$logfile;

    SELECT name FROM v$controlfile;

 

    To view which file correspond to which tablespace you can run:

 SELECT t.NAME "Tablespace", f.NAME "Datafile"

 FROM       V$TABLESPACE t,

                    V$DATAFILE f

 WHERE    t.TS# = f.TS#

 ORDER BY t.NAME; 

 

 

4. How could I take an online FILE level backup ?

  • the database should be in ARCHIVELOG mode

  • put the datafile in OFFLINE mode  (example:  ALTER DATABASE DATAFILE 'C:\oradata\file1.dbf' OFFLINE;  )

  • copy the physical file on another location using OS commands

  • put the datafile in ONLINE mode  (example:  ALTER DATABASE DATAFILE 'C:\oradata\file1.dbf' ONLINE;  )

  • backup the control file as the database has gone through structural changes 

 

5. TABLESPACE Recovery

 

This is done with SQL> RECOVER TABLESPACE command. It is possible to perform a tablespace recovery while the rest of the database is online. 

 

The prerequisites for a tablespace recovery are:

  • The tablespace must be OFFLINE (the database could be online)

  • Only COMPLETE recovery is possible

  • SYSTEM tablespace never can be recovered because is online all the time.

 

6. DATAFILE Recovery

 

This is done with SQL> RECOVER DATAFILE command. It is possible to perform a data file  recovery while the rest of the database is online or offline. 

 

The prerequisites for a data file recovery are:

  • The data file must be OFFLINE (the database could be online)

  • SYSTEM data files never can be recovered because SYSTEM tablespace is online all the time.

 

7. Closed Database Backup

 

If the database is down (shutdown IMMEDIATE, NORMAL) was used, we have only to copy the data files, redo log files and control files to a new location. This kind of backup is used for a database in NOARCHIVELOG which is not used for a 24x7 business. 

 

 

8. Open Database Backup

 

An Open Database Backup is a backup taken when the database is up and running. This is done by putting the tablespace in Backup mode and copying the data files and control files. All the latest archived log files must be copied as well. The V$BACKUP and V$DATAFILE_HEADER should be queried after the database backup to see if all the data files are in online mode.   

 

 

9. Backup the CONTROL file

 

Backup the binary file:  

ALTER DATABASE BACKUP CONTROLFILE TO 'C:\backups\control1.bkp';

 

Generate the script to recreate the control file:

 ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS 'C:\backups\control1.txt';

or

 ALTER DATABASE BACKUP CONTROLFILE TO TRACE      ( the file will be created in USER_DUMP_DEST )

 

 

10.  Perform Cleanup after a Failed Online Backup

 

An Online Backup failure can occurs if:

  • An instance failure occurs

  • An OS failure occurs

  • The database is accidentally shut down.

The database could be stopped by using shutdown abort. See the picture below:

 

Cold Backup in Oracle

 

When the database is brought up a media recovery is needed and the database will be in mount state:

 

Cold Backup in Oracle

 

In mount state we can query the files to see what is happening (optional):

 

Cold Backup in Oracle

 

In Oracle 9i and + we can use RECOVER DATABASE to do an automatic recovery (the database will be consistent again and the tablespace will not be in Backup mode). After this recovery a new backup could be taken. 

 

Cold Backup in Oracle

 

 

To see the status of the files during the online user-managed backups the following select could be used:

       SELECT decode(b.status, 'ACTIVE', 'BACKUP_IS_RUNNING',b.status) "Backup_Status",

                     b.time  "Backup_Start_Time",

                     fh.status "File status",

                     fh.tablespace_name "Tablespace_Name",

                     fh.name "File_Name"

       FROM v$backup b,

                  v$datafile_header fh

       WHERE b.file#=fh.file#;

 

In-Oracle.com  -> Oracle DBA -> Oracle Database Backup and Recovery

-> Cold Backup in Oracle (User Managed Backup)

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.