User Managed backup

User Managed Backup --- Oracle

User Managed Backup

STEP:1

1. Physical Backup

1. Cold Backup (Consistent Backup)
2. Hot Backup (Inconsistent Backup)

2. Logical Backup

1. Export / Import
2. Expdp /Impdp (available on oracle10g onwards)

Now we see the physical backup.


Cold Backup: (Consistent Backup)

A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent.

Why you say cold backup is consistent backup?


After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery.We had taken backup datafile, logfile, controlfile, parameter file & password file.

Cold Backup Steps:

1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2.Taken the password file & parameter file backup ( Optional)
3.Alter system switch logfile;
4.Shutdown immediate
5.Copy all the data files /control files /log files using os command & placed in backup path.
6.Startup the database.

Hot Backup (Inconsistent backup)

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.

Why you say Hot backup is inconsistent backup?

While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.

Hot Backup Steps:

1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path.
3.Once copied the datafile, release the tablespace from begin backup mode to end backup
4.Repeat the steps 1-3 for all your tablespaces.
5.Taken the controlfile backup

Alter Database backup controlfile to ‘/u01/backup/control01.ctl’; ---> à Binary format

Below one is readable format. (Used for cloning)

Alter Database backup controlfile to trace;
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

6.Backup all your archive log files between the previous backup and the new backup as well.
7.Taken the password file & parameter file backup ( Optional)

Example:
steps:

2.Alter tablespace system begin backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)

3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;


Hot Backup internal operations:

Note: While I am trying to write “during hot backup what are the internal operations going on?” I have referred several notes, but below one is very clear and nice one.
Reference: http://knol.google.com/k/franck-pachot/oracle-begin-backupend-backup/17uabcrki6uux/3#

During backup mode, for each datafile in the tablespace, here is what happens:

1- When BEGIN BACKUP is issued:

·The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for a recovery.
·A checkpoint is done for the tablespace, so that in case of recovery, no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.

2- During backup mode:
·The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup. Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs. This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a 'backup' SCN)
·Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behavior that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.


That means that everything goes as normal except for two operations:
- at checkpoint the datafile header SCN is not updated
- when updating a block, the first time it is updated since it came in the buffer cache, the whole before image of the block is recorded in redo
- direct path writes do not go through the buffer cache, but they always write full blocks and then full block is written to redo log (if not in nologging)

3- When END BACKUP is issued:
·A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
·The hot backup flag in the datafile headers is unset.
·The header SCN is written with the current one.

STEP:2

User Managed Backup Terminology

(Operating system command are used to make backups when database is closed or open in this terminology)

Whole database backup refer to a backup of all data file, control file and log file of the database. Whole database backup can be perform when database open or closed.

The backup takes when database is closed called consistent backup. (Because database file header are consistent with the control file and when restore completely the database can be opened without any recovery.)

The backup takes when database is opened and operational called inconsistent backup. (Because database file header are not consistent with the control file.)

Important:

1. If Database is in No Archive log mode, We can not perform user managed hot backup / inconsistent backup

2. If Database is in No Archive log mode and we don’t want to change mode then we can only perform clod backup/consistent backup

3. If Database is in No Archive log mode, we can perform only Restore and not possible to recover database to the point of failure. Means, restore to the point of the last backup

4. If Database is in Archive log mode , then we can recover to the point of failure

How to know Database file location information

For Data File > select * from V$database
For Control File > select * from v$controlfile
For Log File > select * from v$logfile

How to Know all data files and there respective table space

SQL>
SELECT T.NAME TABLESPACE,F.NAME DATAFILE
FROM V$TABLESPACE T, V$DATAFILE F
WHERE T.TS# = F.TS#
ORADER BY T.NAME;

How to Make a consistent whole Database Backup

· Shutdown the database.
· Backup all data file, control file and log file by using an operating system command.
· We can also include password file and parameter file.
· Restart the oracle database/Instance.

How to make a inconsistent whole database backup

Requirement for inconstant database backup:

· The database is set to ARCHIVELOG mode.
· You ensure that the online redo logs are archived, either by enabling the Oracle automatic archiving (ARCn) process.

Making a Backup of an Online teblespace or Data file

· Set the datafile or tablespace is backup mode by issuing following command:

SQL> ALTER TABLESPACE BEGIN BACKUP;

(Note: This prevent the sequence number in the datafile header from changing.)

· Use an operating system backup utility to copy all database in the tablespace to backup storage.
Copy c:\datafile_path e:\datafilepath

· After the datafile of the tablespace have been backed up, set them into mode by issuing the following command:

· SQL> ALTER TABLESPACE END BACKUP;

· Repeat these steps for all tablespaces.

· Archive the unarchive redo logs;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

· Manual Control File Backups

Creating a binary image:

ALTER DATABASE BACKUP CONTROLFILE TO 'control.bak';

Creating a taxt trace file:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

· Backing Up the Initilization Parameter File

CREATE PFILE FROM SPFILE;( For Default location)
CREATE PFILE ='C:\BACKUP\INIT.ORA' FROM SPFILE;

Mechanism of Open database backup

When a datafile is placed in backup mode, more redo log entries may be generated because the log writer writes block image of changes block of the datafile in backup mode to the redo log instead of just the row information

How to check Backup Status Information (When performing open database)

select * from v$backup; (view to determine which file are in backup mode, when alter tablespace begin backup command is issued the status change to ACTIVE.)

How to verify backup

Use to ensure that a backup database or datafile is valid before a restore.

$dbv file='path of file location' start=1 logfile='enter path for log file generation'

Backup Issue with Logging and nologging Option

Tablespace, table, index may be use to set to NOLOGGING mode for Faster load of data when using direct load operation like SQL LOADER. (Because the redo logs do not contain the values that were inserted when the table was in NOLOGGING mode)

 

STEP:3

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:

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

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

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. 

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

IF You Want More Explanation means 

Click Here