How to Backup Oracle database

1. Introduction


There are many ways to backup Oracle databases, regardless of which backup method is used, and the purpose of the backup is to recover the system as little time and cost as possible after a failure. For example, using the Export utility to export database objects, using Oracle to back up databases, using Oracle symmetric replication, using Oracle parallel servers, using Oracle Cold backup, and using Oracle Hot backup have their advantages and disadvantages, Applicable occasions and the corresponding hardware and software requirements. This article mainly uses the export utility to derive the database object, the Oracle cold backup, the Oracle hot backup these three most basic backup methods to carry on the detailed discussion, the analysis respective pros and cons and the suitable occasion, and gives the script file which automatically executes these backup plans.


2, three kinds of backup scenarios comparison


Cold backup

Cold backup is the simplest form of Oracle backup, you must shut down the database before you perform a cold backup, and then use the operating system utility or Third-party tools to back up all the related database files.


Advantages: Simple and quick backup. Can be recovered simply and quickly. Simple to perform.
Disadvantage: The database must be shut down and no point recovery can be made.

Hot backup
Hot backup is the process of data backup when the database is running. The prerequisite for performing a hot backup is that the database runs in the archived log mode. It is suitable for the key application system of 24x7 uninterrupted operation.


Benefits: The database can be turned on while the backup is in. Hot backup can be used for point recovery. Initialization parameter files, archive logs are turned off when the database is normal, and copies of operating system commands are available.
Disadvantage: The execution process is complex. The test is difficult because the database runs uninterrupted. You cannot copy an open file with the operating system utility. You must use the Ocopy tool provided by Oracle to copy open files. Hot backups can cause CPU, I/O overload, and should be done when the database is not too busy.

Export exported Database objects
Both cold and hot backups are backed up by physical database files and are called physical backups. While export backs up the database objects, it is called a logical backup.


Benefits: Can perform object or line recovery. Backup and restore faster. Ability to migrate databases across operating system platforms. The database can run all the time.
Disadvantage: Export is not a substitute for cold backup and hot backup. Cold, hot backup protects media from failure. Export backups can protect users or apply errors.

3, the implementation of cold backup plan


3.1 Cold Backup Database steps
(1) closing the database;
(2) Back up all relevant database files: Initialize parameter file, control file (available select name from V$controlfile; list all

control files), data files (available select name from V$datafile; list all data files), redo logs (available with select Member from V$logfile; list all redo log files), Archived redo logs (available in select Sequence#,first_time from v$loghist; Lists the sequence number and generation time of all archived redo log files).


3.2 Cold backup script files for the database Coldbackup.bat


4, the implementation of the hot backup scheme


4.1 Prerequisites for a hot backup database: Database running in archive mode
The redo log of the Oracle database records all activities performed on the database. LGWR The background process writes these log files in a circular fashion, from the first redo log to the next until the last of the group, and then from the first log.
In non-archive mode, when the loop is written to the last log file, the first log is rewritten. Therefore, the only database recovery method in non-archive mode is to use a cold backup.
In archive mode, when the redo log is full, an arch background process reads all the redo logs and writes them to the archive log. Therefore, you can use hot backup and point recovery. In archive log mode, if the archive log destination space is full, the database activity is temporarily halted and the database cannot continue until some space is freed. Typically, background_dump_destination will produce a trace file to display problems with the archive.
The Oracle database installation defaults to run in non-archive mode and can be converted from a non-archive mode to an archive mode by using the following steps:
(1) Edit parameter file Init.ora, set the following parameters
# Set up Database automatic archiving
Log_archive_start = True
# set up a directory of archived log files that must have been established in advance and have plenty of space to exploit
log_archive_dest_1= "Location=%oracle_base%\ oradata\%oracle_sid%\archive"
# Set the format of the archive log file name. %s represents the order number, and%t represents the thread number.
Log_archive_format = "%oracle_sid%%t%s.arc"
(2) Run the following command on the Sqlplus
Sqlplus>;connect Sys/qazwsx as Sysdba
Sqlplus>;shutdown immediate;
Sqlplus>;startup Mount Exclusive;
Sqlplus>;alter database Archivelog;
Sqlplus>;alter database open;
Sqlplus>;shutdown immediate;
(3) Execute database Cold backup
When the database is running in archive mode, the archive log is required when the database recovery process requires a cold backup.
(4) Start the database normally and confirm that the database is running in the archive mode
Sqlplus >; Startup
Sqlplus >; SELECT * from V$database; Its log_mode will show whether the database is archived mode
Sqlplus >; Archive log list; Also shows whether the database is archived mode
If the Oracle database is running in archive mode, it may be necessary to suspend the archiving of the database when performing database maintenance, and then restart the archive mode after the maintenance is complete. The following steps allow you to convert from archive mode to non-archive mode:
Sqlplus>;connect Sys/qazwsx as Sysdba
Sqlplus>;shutdown immediate;
Sqlplus>;startup Mount Exclusive;
Sqlplus>;alter database Noarchivelog;
Sqlplus>;alter database open;
Sqlplus >; SELECT * from V$database; Its log_mode will show whether the database is archived mode
Sqlplus >; Archive log list; Also shows whether the database is archived mode
4.2 The steps for a hot backup database
(1) Copy the Init.ora file to the backup directory (the parameter file is turned off after the database is started).
(2) Place a table space that needs to be backed up in the start Backup mode.
(3) Use Ocopy.exe to copy the tablespace, and then place the tablespace in the end backup mode (Ocopy.exe cannot be used for direct copy of the online database file).
(4) Perform steps 2 and 3 for each tablespace in the database (you can view what tablespaces and data files are in the database through view dba_tablespaces and V$datafile).
(5) To obtain the current log sequence number by executing the Archive log List command on the Sqlplus, the online sequence log from the oldest online log sequence to the present log redo should be part of a hot backup.
(6) Execute ALTER SYSTEM switch logfile command on Sqlplus to force log switching so that all logs are archived.
(7) Use ALTER DATABASE backup controlfile to trace; command to obtain a backup of the control file, you can find the latest trace file in the%oracle_base%\admin\%oracle_sid%\udump directory , which has all the commands to rebuild the control file.
(8) Copy archived log files from%log_archive_dest% to the backup directory using Windows NT commands.
4.3 The script file for the hot backup database Hotbackup.bat


5. Use Export as backup strategy


5.1 Export Command Options description
The EXP Tools for Oracle databases provide tables, users, full database, tablespace four levels of export, and export the specified database content to one or more Oracle binaries that are only allowed to be read with IMP tools. IMP's command options can be consulted by IMP help=y.
You can control how the export runs by entering the EXP command and various arguments. To specify a parameter, you can use the keyword:
Format: EXP keyword=value or keyword= (value1,value2,..., Valuen)
Example: EXP scott/tiger grants=y tables= (emp,dept,mgr)
or tables= (T1 1,t1 2) If T1 is a partitioned table
USERID must be the first parameter in the command line.

The following keywords are only available for use in a table space that can be transferred