What is a User-managed backup in oracle?

User-managed backup is accomplished with whatever operating system facilities happen to be available and SQL*Plus commands. These may be as simple as the copy commands on windows or cp on Unix/Linux.  The significance of archive log mode for the backup technique available is the same for user-managed backup as for server-managed backup in archive log mode, a backup must be closed and whole in archive log mode, it can be open and partial. User-managed backup & recovery consists of a user copying all the files to the backup destination (without using RMAN)

The importance of User-managed backup is mention below:-

Before you start a user managed backup, you will need to know first:

  • Where all your datafiles are located, and their names;
  • Where your archivelogs are located;
  • And finally where your controlfile are located.

Cold Backup

A cold backup, that is, one done with the database in a shutdown state, provides a complete copy of the database that can be restored exactly.  During a cold backup, the database is closed and not available to users.  All files of the database are copied (image copy).  The datafiles do not change during the copy so the database is in sync upon restore.A Cold backup, that is, one done with the database in a shutdown state, provides a complete copy of the database which can be restored exactly. The disadvantage of an offline backup is that it cannot be done if the database needs to be run 24/7. Additionally, you can only recover the database up to the point when the last backup was made unless the database is running in ARCHIVELOG mode.

Note:-

Backing up online redo log files is not advised in all cases, except when performing cold backup with the database running in NOARCHIVELOG mode. If you make a cold backup in ARCHIVELOG mode do not backup redo log files. There is a chance that you may accidentally overwrite your real online redo logs, preventing you from doing a complete recovery. If your database is running in ARCHIVELOG mode, when you perform cold backup you should also backup archive logs that exist.

Before performing a cold backup, you need to know the location of the files that need to be backed up. Because the database structure changes day to day as more files get added or moved between directories, it is always better to query the database to get the physical structure of database before making a cold backup.

Steps of  Cold Backup:-

Step 1—Generating File List

An offline backup consists of physically copying the following files:

  • Data files
  • Control files
  • Init.ora and config.ora files.
  • To get the structure of the database, query the following dynamic data dictionary tables:-
  • V$datafile Lists all the data files used in the database

SELECT * FROM V$Datafile;

  • Backup the control file and perform a trace of the control file using

ALTER  DATABASE  BACKUP  CONTROLFILE ‘/u02/backup/control.ctl’;

ALTER  DATABASE  BACKUP  CONTROLFILE TO TRACE;

  • Init.ora and config.ora Located under $ORACLE_HOME/dbs directory

 

 

Step 2—Shut down the database

$ su oracle

$sqlplus  “/as sysdba”

sql> shutdown

Step 3—Perform a backup

In the first step, you generated a list of files to be backed up. To back up the files, you can use the Unix copy command (cp) to copy it to a backup location, as shown in the following code. You have to copy all files that you generated in Step 1.

$cp  /u01/oracle/users01.dbf  /u02/backup

You can perform the backup of the Init.ora and config.ora files as follows:

$cp $ORACLE_HOME/dbs/int.ora  /u02/backup

$cp $ORACLE_HOME/dbs/config.ora  /u02/backup

Step 4—Start the database

After the backup is complete, you can start the database as follows:

$ su oracle

$sqlplus “/ sysdba”

sql> startup

 

 

Hot Backup

Hot backup is entirely the opposite and is usually taken when the database has to run all the time due to critical requirements. Because of this, there may be changes in the data files while the backup is being taken. While taking the backup, if the database remains open and available to users then this kind of backup is referred to as a hot backup. The image copy is made for all the files. As, the database is in use the entire time, so there might be changes made when a backup is taking place. These changes are available in log files so the database can be kept in sync

Steps for Hot Backup

 

Step 1:- Prerequisites

$Select log_mode from v$database;

If the database is not in archive log mode then put the database in the archive log mode

sql>shut immediate;

sql>startup mount;

sql>alter database archive log ;

sql>alter database open;

Step 2: – Taking a hot backup

Now that we have prepared our database for a hot backup, we can go ahead with actually backing up the files. Follow the following steps to take hot backup of the tablespaces

  •  Find out the number of tablespaces associated with the database

sql>Select tablespace_name from dba_tablespaces;

 

  • Find out if the tablespaces are ready for hot backup

sql>select * from v$backup;

If the output says not active then it is not in hot backup mode

  •  Put the tablespaces in hot backup mode

sql>Alter database begin backup;

sql>Select * from v$backup;

Step 3:

Copy the tablespace files on the hard drive to the backup location.

Step 4: – 

Put the tablespaces out of the backup mode

Step 2: – Taking a hot backup

Verify that the tablespaces indeed have come out of the backup mode

sql>Select * from v$backup;

  •  Switch the archive log

sql>Alter system archive log current;

  • Backup the control file

Note- Don’t use the operating system’s copy command to do this

sql> Alter database backup controlfile to ‘/u01/app/oracle/backup/controlfile.ctl’;

  • Copy the archive logs to the backup location

Backup of the database finished.

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.