Backup Methods for Oracle databases

1. Introduction

There are many backup methods for Oracle databases, regardless of the backup method used, to restore the system at the lowest possible time and cost in the event of a failure. For example, exporting database objects using the Export utility, using Oracle backup databases, using Oracle symmetric replication, using Oracle parallel servers, using Oracle Cold backup, and using various backup methods such as Oracle Hot backup have their pros and cons, Applicable to the occasion and the corresponding hardware and software requirements. In this paper, the Export utility exported the database objects, Oracle Cold backup, Oracle Hot backup three of the most basic backup methods to explore the details of the pros and cons of the application and applicable occasions, and give the automatic implementation of these backup scheme script files.

Comparison of 2, three backup schemes

Cold backup

Cold backup is one of Oracle's simplest backups; You must close the database before performing a cold backup, and then use the operating system utility or third-party tools to back up all related database files.

Pros: Can be backed up easily and quickly. Can be recovered simply and quickly. Simple execution.
Disadvantage: The database must be closed and point recovery cannot take place.

Hot backup
A hot backup is the process of backing up data when the database is running. The prerequisite for performing a hot backup is that the database is running in an archived log mode. Critical application Systems for 24x7 uninterrupted operation.

Advantage: The database can be open when you back up. A hot backup can be used for point recovery. Initializing the parameter file, the archive log is closed when the database is functioning, and can be copied with the operating system commands.
Cons: The execution process is complex. The test is difficult because the database is running continuously. Open files cannot be copied with the operating system utility. You must use the Ocopy tool provided by Oracle to copy open files. A hot backup can cause CPU, I/O overload, and should occur when the database is not too busy.

Export exported Database objects
Both cold and hot backups back up the physical database files and are therefore called physical backups. The export backup is a database object and is therefore referred to as a logical backup.

Pros: Ability to perform object or row recovery. Backup and restore faster. Ability to migrate databases across operating system platforms. The database can run continuously.
Cons: Export is not an alternative to cold backup and hot backup. Cold, hot backup protects the media from failure. Export backup can protect users or apply errors.

3, the implementation of cold backup program

3.1 Steps to a cold backup database
(1) Close the database;
(2) Back up all relevant database files: Initialize parameter files, control files (select name from V$controlfile is available; list all

control files), data files (select name from V$datafile; list all data files), redo logs (select Member from V$logfile; list all redo log files), Archived redo logs (available as select Sequence#,first_time from v$loghist; Lists the sequence number and generation time of all archived redo log files).
　
3.2 Script file for Cold backup database Coldbackup.bat
　
4, the implementation of the hot backup scheme

4.1 Prerequisites for a hot backup database: The database is running in archive mode
The redo log of the Oracle database records all activities that are performed on the database. LGWR background processes write 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, the first log is rewritten when the loop is written to the last log file. Therefore, the only way to recover from a database in non-archive mode is to use cold backups.
In archive mode, when the redo log is full, an arch background process reads all redo logs and writes them to the archive log. Therefore, you can use hot backup and point recovery. In archived log mode, if the archive log destination space is full, the database activity is temporarily stopped and the database can continue to run only after some space has been freed. In general, Background_dump_destination will produce a trace file to display the problem with the archive.
Oracle database installation runs in non-archive mode by default and can be converted from non-archive mode to archive mode by the following steps:
(1) Edit the parameter file Init.ora, set the following parameters
# Set up Database Auto-archive
Log_archive_start = True
# set up a directory of archived log files that must have been established beforehand and have plenty of space to use
log_archive_dest_1= "Location=%oracle_base%\ oradata\%oracle_sid%\archive"
# Format the archive log file name. %s represents the sequence 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) Perform database cold backup
When a database is running in archive mode, the database recovery process requires that a cold backup be used, and the archive log is required.
(4) Start the database normally and confirm that the database is running in archive mode
Sqlplus >; Startup
Sqlplus >; SELECT * from V$database; Its log_mode will show whether the database is in archive mode
Sqlplus >; Archive log list; Also shows whether the database is in archive mode
If the Oracle database is running in archive mode, you may need to pause the archive of the database when you perform database maintenance, and then restart the archive mode after maintenance is complete. You can convert from archive mode to non-archive mode by using the following steps:
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 in archive mode
Sqlplus >; Archive log list; Also shows whether the database is in archive mode
4.2 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) Copy the tablespace using Ocopy.exe and then place the tablespace in the end backup mode (Ocopy.exe cannot be used to copy the online database files directly).
(4) Perform steps 2 and 3 for each tablespace in the database (view dba_tablespaces and v$datafile to see which tablespaces and data files are in the database).
(5) To obtain the current log order number by executing the Archive log List command on the Sqlplus, the online sequence log from oldest online log sequence to present log redo should be part of the hot backup.
(6) Execute the ALTER system switch logfile on the sqlplus; command to force the log switch so that all logs are archived.
(7) using ALTER DATABASE backup controlfile to trace; command to get 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 for rebuilding the control file.
(8) Use Windows NT commands to copy archived log files from%log_archive_dest% to the backup directory.
4.3 script file for hot backup database Hotbackup.bat
　
5. Use export as a backup strategy

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

The following keywords are only available for transportable tablespaces
Transport_tablespace exporting the Transportable tablespace metadata (N)
Tablespaces List of tablespaces to be transferred
5.2 Export Backup Database script file Expbackup.bat

6. Automatic execution method of various backup strategies

Whether it's a cold backup, a hot backup, or an export backup, you can use the AT command to schedule and automate the backup strategy, whether it's a UNIX platform or a Windows platform. The AT command schedules commands and programs to run on a specific date and time, and the Dispatch service (schedule) must be run first on the Windows NT platform to use the AT command.
The AT command uses the following:
at [\\computername] [[ID] [/delete] |/delete [/YES]]
at [\\computername] time [/interactive]
[/every:date[,...] |/next:date[,...]] "Command"
\\COMPUTERNAME Specifies the remote computer. If this argument is omitted, the command is scheduled to run on the local computer.
ID is assigned to the identification number of the scheduled command.
/delete deletes a scheduled command. If the ID is omitted, all scheduled commands on the computer are deleted.
/yes does not require further confirmation, it is used with commands that delete all jobs.
TIME specifies when the command is run.
/interactive allows the job to interact with the user's desktop when it is logged on at run time.
/every:date[,...] Run the command every month or week on the specified date. If the date is omitted, the default is to run on the current day of the month.
/next:date[,...] Specifies that the command be run on the next specified date (for example, next Thursday). If the date is omitted, the default is to run on the current day of the month.
"Command" Prepares the Windows NT command or batch program to run.
Examples are as follows:
(1) Perform cold backup every Friday 19:00
At 19:00/every:f "Coldbak.cmd"
(2) Perform a hot backup every Tuesday 20:00
At 20:00/every:t "Coldbak.cmd"
(3) One or two, three, four or five 21:00 per week perform an export backup
At 20:00/every:m,t,w,th,f "Expbak.cmd"

Backup Methods for Oracle databases