Oracle Database Backup Method

1. Introduction

There are many backup methods for Oracle databases. No matter which method is used, the purpose of backup is to restore the system at the lowest possible time and cost after a fault occurs. 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, using Oracle hot backup, and other backup methods all have their advantages and disadvantages. applicable scenarios and corresponding software and hardware requirements. This article mainly discusses in detail the three basic backup methods of exporting database objects, Oracle cold backup, and Oracle Hot Backup Using the export utility, and analyzes their respective advantages and disadvantages and applicable occasions, the script files that automatically execute these backup schemes are provided.

2. Comparison of the three backup solutions

Cold backup

Cold backup is the simplest type of backup in Oracle. You must close the database before performing cold backup. Then, you can use the operating system utility or a third-party tool to back up all related database files.

Advantage: simple and quick backup. It can be restored easily and quickly. Simple execution.
Disadvantage: the database must be closed and cannot be restored.

Hot Backup
Hot Backup is the process of backing up data when the database is running. The premise for performing hot backup is that the database runs in the archive log mode. It is applicable to key application systems that run 24x7 without interruption.

Advantage: the database can be opened during Backup. Hot Backup can be used for point recovery. The initialization parameter files and archive logs are closed when the database is running normally. You can copy them using the operating system commands.
Disadvantage: the execution process is complex. It is difficult to test because the database runs continuously. You cannot use an operating system utility to copy open files. You must use the ocopy tool provided by Oracle to copy open files. Hot Backup may cause CPU and I/O overload, which should be performed when the database is not busy.

Export database objects through Export
Both cold backup and Hot Backup back up physical database files, which are called physical backup. Export is a database object, so it is called logical backup.

Advantage: it can restore objects or rows. Faster backup and recovery. Ability to migrate databases across operating system platforms. The database can always run.
Disadvantage: export is not an alternative tool for cold backup and hot backup. Cold and Hot Backup can protect the media from being invalid. Export backup can protect user or application errors.

3. Implementation of the cold backup solution

3.1 cold backup database procedure
(1) shut down the database;
(2) back up all relevant database files: Initialize parameter files and control files (select name from v $ controlfile is available; list all

Control files), data files (available select name from v $ datafile; list all data files), Redo logs (available select member from v $ logfile; list all redo log files) and archive Redo logs (select sequence #, first_time from v $ loghist; list the sequence numbers and generation times of all archived redo log files ).

3.2 coldbackup. bat, the script file of the cold backup database

4. Implementation of the hot backup solution

4.1 prerequisites for a hot backup database: the database runs in the archive mode.
The redo log of the Oracle database records all activities performed on the database. The LGWR background process writes these log files cyclically, from the first redo log to the next one, until the last one in the group, and then starts from the first log.
In non-archive mode, the first log is overwritten after the last log file is written cyclically. Therefore, the only way to recover a database in non-archive mode is to use cold backup.
In archive mode, when the redo log is full, an ARCH background process reads all the redo logs and writes them to archive logs. Therefore, hot backup and point recovery can be used. In archive log mode, if the target space for archiving logs is full, the database activity will be temporarily stopped. The database can continue to run only after some space is released. Generally, background_dump_destination generates a trace file to display archiving issues.
Oracle Database Installation runs in non-archive mode by default. Follow these steps to switch from non-archive mode to archive mode:
(1) edit the parameter file init. ora and set the following parameters:
# Set automatic database Archiving
Log_archive_start = true
# Set the directory for archiving log files, which must have been created in advance and has a large amount of available space
Log_archive_dest_1 = "location = % oracle_base % \ oradata \ % oracle_sid % \ archive"
# Set the format of the archive log file name. % S indicates the sequence number, and % t indicates the thread number.
Log_archive_format = "% oracle_sid % T % S. ARC"
(2) run the following command on 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 cold Database Backup
When the database is running in archive mode and the database recovery process requires cold backup, archiving logs are 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 displays whether the database is in archive mode.
Sqlplus>; archive log list; whether the database archive mode is displayed
If the Oracle database runs in archive mode, you may need to suspend the archiving of the database during database maintenance. After the maintenance is complete, restart archive mode. To switch from archive to non-archive, follow these 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 displays whether the database is in archive mode.
Sqlplus>; archive log list; whether the database archive mode is displayed
4.2 hot backup database procedure
(1) copy the init. ora file to the backup directory (the parameter file is closed after the database is started ).
(2) place a tablespace to be backed up in the Start backup mode.
(3rd) use the ocopy.exebeibeibei table space, and then place the table space in the simplified foreign model (ocopy.exe cannot be used to directly copy online database files ).
(4) Perform steps 2 and 3 on each tablespace in the database (you can view the tablespace and data files in the database through dba_tablespaces and v $ datafile ).
(5) run the archive log list Command on sqlplus to obtain the sequence number of the current log. The online redo logs from oldest online log sequence to current log sequence should be part of hot backup.
(6) execute the alter system switch logfile command on sqlplus to force log switching so that all logs are archived.
(7) run the 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, all commands for recreating the control file are provided.
(8) use the windows nt command to copy the archived log file from % log_archive_dest % to the backup directory.
4.3 hot backup database script file hotbackup. bat

5. Use export as the backup policy

5.1 export Command Options
The exp tool of Oracle database provides four levels of export Methods: tables, users, full database, and tablespace. It exports the specified database content to one or more oracle binary files, this file can only be read by imp. The Command Options of imp can be viewed by imp help = y.
You can control the "Export" running mode by entering the EXP command and various independent variables. To specify parameters, you can use the Keyword:
Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)
Instance: exp scott/tiger grants = y tables = (EMP, DEPT, MGR)
Or TABLES = (T11, T12). If T1 is a partitioned table
USERID must be the first parameter in the command line.

The following keywords are only used for table spaces that can be transferred.
TRANSPORT_TABLESPACE export the table space metadata that can be transferred (N)
Table space list of TABLESPACES to be transmitted
5.2 export: backup database script file expbackup. bat

6. Automatic execution of various backup policies

Whether it is cold backup, hot backup, or export backup, you can use the at command to regularly and automatically execute the preceding backup policy on both unix and windows platforms. The AT command is scheduled to run commands and programs on a specific date and time. On the windows nt platform, you must first run the scheduling service (schedule) to use the at command.
The AT command is used as follows:
AT [\ computername] [[id] [/DELETE] |/DELETE [/YES]
AT [\ computername] time [/INTERACTIVE]
[/EVERY: date [,...] |/NEXT: date [,...] "command"
\ Computername specifies the remote computer. If this parameter is omitted, the command is scheduled to run on the local computer.
Id: id of the scheduled command.
/Delete: delete a scheduled command. If the id is omitted, all scheduled commands on the computer will be deleted.
/Yes is used together with the command to delete all jobs without further confirmation.
Time specifies the time when the command is run.
/Interactive allows a job to interact with the user desktop that was logged on at runtime.
/Every: date [,...] Run the command on the specified date every month or every week. If the date is omitted, the task runs on the current day of each month by default.
/Next: date [,...] specifies to run the command on the next specified date (the following Thursday. If the date is omitted, the task runs on the current day of each month by default.
"Command" is the Windows NT command or batch processing program to be run.
Example:
(1) perform cold backup at every Friday
At 19:00/every: F "coldbak. cmd"
(2) perform hot backup at every Tuesday
At 20:00/every: T "coldbak. cmd"
(3) execute an export backup every Monday, two, three, four, and five.
At 20:00/every: M, T, W, Th, F "expbak. cmd"