Processing of Oracle cold and Hot Backup

I. Summary
There are three standard modes of backup and recovery for Oracle, broadly divided into two broad categories, backup recovery (physical), and import and export (logically),

Backup recovery can be divided into non-archive mode (Nonarchivelog-style) and archive mode (Archivelog-style) according to the working mode of the database.

In general, we refer to non-archive mode as cold backup, while the corresponding archive mode is called hot backup.

1. Logical Backup-cold backup

With export, data can be extracted from the database, and the extracted data can be sent back to the Oracle database using import.

2. Physical Backup-cold backup

A cold backup database is a backup of all critical files, including data files, control files, and online redo log files, to be copied to another location after the database is closed.

In addition, cold backups can include backups of parameter files and password files, but these two backups can be selected as needed.

Cold backup is actually a physical backup, a process of backing up a database physical file.

Because cold backups are backed up with all database files except redo logs, they are also a full database backup.

3. Physical Backup-Hot backup

A hot backup is a way to back up a database using archive log mode, in the case of a database run.

Hot backup requires the database to operate in Archive log mode and requires a large amount of file space.

Once the database is in archive log mode, it can be backed up, and when the backup is performed, it can only be done at the data file level or table space.

4. Backup Method Classification
   
   Second, logical backup
   Now let's introduce the way of logical backup, using export to extract the data from the database, using import to send the extracted data back to the Oracle database.

5. Rationale: The export and import provided by Oracle have three different modes of operation (that is, the data output (in) type of the backup):

(1). Table mode (T) to export the specified table to a backup;

(2). The full library method exports all objects in the database;

(3). User mode (U) can export all data objects corresponding to the specified user;

2. In the import and export backup method, there is a very powerful way to do that is incremental export/import, but it must be the system to complete the incremental import and export, and can only be implemented for the entire database.

Incremental export can also be divided into three categories:

(1). Full incremental export (complete export) This way the entire database file will be exported and backed up;
Exp system/Password @ database name Inctype=complete file= Store directory \yyyymmdd.dmp (for convenience of retrieval and subsequent queries, we usually name the backup file as a date or other character with a definite meaning)
(2). Incremental incremental Export (Incremental export) This method will only back up the results that were changed after the last backup;

Exp system/Password @ database name Inctype=incremental file= Store directory \yyyymmdd.dmp

(3). Cumulative incremental export (cumulate export) This way, is to export the database changes since the last full incremental export information;

Exp system/Password @ database name inctype=cumulative file= Store directory \yyyymmdd.dmp

3. In general, what DBAs have to do is to follow the criteria that the enterprise assigns or is accustomed to (if it is a standard of its own, suggest to write a plan description),

In general, we use the generally accepted approach to incremental backups daily:

Mon: Full Backup (A)
Tue: Incremental Export (B)
Wed: Incremental Export (C)
Thu: Incremental Export (D)
Fri: Cumulative Export (E)
Sat: Incremental Export (F)
Sun: Incremental Export (G)

4. In this way, we can guarantee the completeness of the weekly data, as well as the speedy and maximum data loss when recovering.

When recovering, assuming that the accident occurred on weekends, DBAs can recover the database in such a step:

The first step: Create database with the command to regenerate the data structure;

Step Two: Create an additional rollback that is large enough.

Step Three: Fully incremental import a:
IMP system/password @ database name Inctype=restore full=y file=a

Fourth Step: Cumulative incremental Import E:

IMP system/password @ database name Inctype=restore full=y file=e

Fifth step: The most recent incremental import F:

IMP system/password @ database name Inctype=restore full=y file=f

Typically, a DBA's import-export backup is done, and as long as the science is regularly backed up, the loss of data can be minimized to provide a more reliable service.

In addition, DBAs are best to make a more detailed documentation of each backup, making the recovery of the database more reliable.

Three, cold backup
A cold backup occurs when a database problem is only restored to the point at which the backup was made, and the backup finishes to the database when there is a problem requiring recovery, all new data in this time period, modification records cannot be recovered.

Hot backup can only be done in archive mode, cold backup does not require archiving mode, archiving mode will have a certain impact on system performance, especially disk I/O, but with respect to the benefits and security of archiving, I believe most DBAs will choose the archive mode.

1. Advantages:

Just copy the file and it's a very fast backup method.
Simply copy the file back and you can restore it to a point in time.
The combination of a database archiving pattern allows the database to recover well.
Less maintenance, but relatively high security.
2. Disadvantages:

The database must be turned off during a cold database backup.
When a cold backup is used alone, the database can only complete recovery based on a point in time.
If disk space is limited, cold backups can only copy backup data to other external storage, such as tape, at a slower rate.
Cold backups cannot be resumed on a per-table or per-user basis.
3. Basic process

When a database can be temporarily turned off, we need to transfer its data-related files at this stable moment to a secure area,

When the database is compromised, copy the backed-up database-related files from the security zone back to their original location.

This completes a quick and secure data transfer.

This is called a cold backup because the database does not provide a shutdown state for the service.

Cold backup has a lot of good features, such as the above image we mentioned, fast, convenient, and efficient. A complete cold backup step should be:

Step1. First close the database (shutdown normal)

Step2. Copy related files to security area (copy all data files, log files, control files, parameter files, password files, etc. (including paths) of the database using operating system commands)

Step3. Restart the database (startup), the above steps we can use a script to complete the operation:

Su–oracle < Sqlplus/nolog

Connect/as SYSDBA

Shutdown immediate;

!CP file backup location (all logs, data, control and parameter files);

Startup

Exit

Step4. In this way, we have completed a cold backup, make sure that you have the appropriate permissions for these corresponding directories, including the target folder to write to.

Step5. Recovery time, relatively simple, we stopped the database, copy the file back to the appropriate location, restart the database can be, of course, can also be completed with a script.

Four, hot backup

Hot backup supports backup with no downtime for the database, and cold backups are copies of the tablespace that must be copied using the operating system's commands after the database shutdown.

Hot backup is suitable for important databases, such as 7x24 uninterrupted production systems, while ensuring that the database can be recovered to any point in time when a crash occurs, with no loss of data.

1. Advantages:

Backup at table space or data file level with short backup times.
Can reach a second level of recovery (to a point in time).
Recovery is possible for almost all database entities.
Recovery is fast and in most cases resumes when the database is still working.
The database is still available at backup time.
2. Disadvantages:

It is difficult to maintain, so be careful not to allow "failure to end".
If the hot backup is unsuccessful, the resulting results are not available for point-in-time recovery.
No mistakes, otherwise serious consequences.

3. Basic process

When we need to make a high-precision backup, and our database cannot be stopped (a little traffic),

In this case, we need a backup in the archive mode, which is the hot backup discussed below.

Hot backup can back up the table space level and user-level data very precisely, because it is based on the time axis of the archived log to back up the recovery, theoretically can revert to the previous operation, or even the previous one second operation. The steps are as follows:

Step1. View V$database to see if the database is in archive mode: sql> select Log_mode from V$database;

If not archive mode

The set database runs in archive mode: Sql>shutdown immediate

Sql>startup Mount

sql> ALTER DATABASE Archivelog;

sql> ALTER DATABASE open;

If Automaticarchival is displayed as "Enabled," the database is archived automatically. Otherwise, you need to archive it manually, or modify the archive to an automatic archive, such as:

Normal shutdown database, add the following parameters in the parameter file Init.ora

Sql>shutdown Immediate

Modify Init.ora:

Log_archive_start=true

Log_archive_dest1=oracle_home/admin/o816/arch (Archive day value storage location can be defined by itself)

Sql>startup

Then, restart the database, at which point the Oracle database will work in archive mode with an automatic archive.

Where parameter log_archive_dest1 is the path to the specified archive log file, it is recommended to have a different hard disk than the Oracle database file, reducing disk I/O contention on the one hand, and also avoiding file loss after the hard disk where the database file resides.

The archive path can also be directly designated as a physical storage device such as tape, but may take into account factors such as read-write speed, writable conditions, and performance.

Note: When the database is in archive mode, make sure that the specified archive path is writable, otherwise the database will hang until all archived information can be archived.

In addition, to create a valid backup, when the database is created, it must perform a cold backup of a full database, that is, the database needs to be run in the archive mode, and then gracefully shut down the database, back up all the database composition files.

This backup is the basis for the entire backup because it provides a copy of all the database files. (reflects the cooperative relationship between cold backup and hot backup, and strong ability)

Step2. To back up the Tablespace file:

(1). First, modify the tablespace file for backup mode ALTER tablespace tablespace_name begin backup;

(2). Then, copy the Tablespace file to the security Zone! CP Tablespace_name D_path;

(3). Finally, turn off the backup mode of the tablespace to ALTER tablespace tablespace_name end backup;

Step3. Backup of archived log files:

Stop archiving process--back up archived log files--Start the archive process

If the log documents are much more, we write them into a file as a reference for recovery: $ files ls <归档文件路径>/arch*.dbf ; export files

Step4. Backup control Files:

sql> ALTER DATABASE backup Controlfile to ' controlfile_back_name (generally in 2004-11-20 way) ' reuse;

Of course, we can also write the above as a script, when necessary to execute it:

Script Example:

Su–oracle < Sqlplus/nolog

Connect/as SYSDBA

ALTER tablespace tablespace_name BEGIN BACKUP

! CP Tablespace_name D_path

ALTER tablespace tablespace_name END BACKUP

　　

ALTER DATABASE backup Controlfile to ' controlfile_back_name ' reuse;

!files ls <归档文件路径>/arch*.dbf ; export files

Recovery of a hot backup, the recovery requirement for an archive database requires not only a valid log backup but also a valid full-database backup under the archive.

Archived backups can theoretically have no data loss, but the requirements for both the hardware and the operator are relatively high.

A full-Library physical backup is also important when we use archiving to back up.

Database recovery under archive requires all logs to be intact from the full backup to the point of failure.

Recovery steps: Log_archive_dest_1

Shutdown database.

Place the full backed up data file in the original system directory.

Place all archived logs of the full backup to the point of failure at the location specified by the parameter log_archive_dest_1.

Log in to an empty instance using Sqlplus. (Connect/as sysdba)

Then the startup mount

Set AutoRecovery on

Recover database;

ALTER DATABASE open;

Processing of Oracle cold and Hot Backup