1, Basic knowledge

1. Role of backup and restore:

Backup mainly plays the role of data disaster recovery. It is an effective means to ensure data security in case of database failure; By restoring the backup set data, the data can be restored to the state before the database failure.

2. Basic principles of backup, restore and recovery:

In Dameng database, data is stored in the physical data file of the database, and page is the basic unit of data storage. Any operation on DM database is basically the operation on a data file page, so the essence of backup is to copy and save the effective pages in the data file to the backup set, The effective data page includes the description page of the data file and the data page allocated for use; When the database server is running, the operation of the database will not be reflected in the data file immediately, but will be written to the archive log in the form of log. Therefore, when the database is still running, the archive log generated during the backup process needs to be saved to the backup set, After that, you can restore through the archive log in the backup set.

Restore is the reverse process of backup, which is the process of rewriting the effective data pages in the backup data set to the database file; Recovery is to restore the database state to any specified point in time by redoing the archived log, including the state at the end of backup. The following figure shows the schematic diagram of backup, restore and recovery:

3. Type of backup

(1) The difference between logical backup and physical backup

Logical backup uses DM database export data tool dexp to export the data of objects (Library level, schema level and table level) dmp file backup mode, so the backup of logical backup focuses on the content of data, and does not care about the physical storage location of data;

Physical backup is to scan the database files directly, copy and save the allocated and used data pages to the backup set. Therefore, physical backup selects effective data pages for backup according to the description of the database file system, and does not care about the contents of the data pages.

(2) Online and offline backup

The backup operation of the data server when it is running and can normally provide database services is called online backup, also known as hot backup; During the hot standby period, in order to ensure that the consistency of data is not destroyed, it is necessary to back up the REDO logs generated during this period. Therefore, the hot standby can only be carried out on the database configured with local archiving and enabled; Hot standby can execute relevant SQL statements after connecting to the database instance through the client tool; You can also configure jobs to automatically and regularly backup, which is more convenient to use and does not affect the normal operation of the database. Therefore, hot backup is the most commonly used backup means.

Backup when the database is closed is called offline backup, also known as cold backup; Cold standby is a backup that can only be performed when the database is closed, otherwise the system will report an error, so it is necessary to check whether the database is closed before cold standby;

(3) Data backup and archive log backup

By content, backup can be divided into data backup and archive log backup; Data backup is mainly to backup the contents of data files (including library level backup, table space backup and table backup). Archive log backup is a continuous backup operation of archive log files, which does not involve how to save data content. Archive log backup can be performed under hot or cold standby conditions.

(4) Full backup and incremental backup

According to the integrity of backup data, there can be full backup and incremental backup. Full backup is the backup of all valid data pages containing the specified library (or table space). Therefore, the backup set generated by backup is large and the backup time is relatively long. Incremental backup is to back up the data pages of new data on the original specific backup set (base backup), so it can effectively reduce the space occupied by the backup set and reduce the backup time; When using incremental backup for restore operation, the base backup is required to be complete. How to make an incremental backup, a complete backup is used as the base backup in the end. The tools have different requirements for backup. There are two kinds of incremental backup of DM database: differential incremental backup and cumulative incremental backup; Differential incremental backup requires that the base backup can be a full backup set or an incremental backup set; Cumulative incremental backup can only be based on full backup.

2, Backup and restore practice

1. Online backup

(1) Archive configuration: (pre preparation)

Method 1:

Enter the bin directory of the installation path and start the DMAP service and database service:

./DmAPService start    # Start DMAP service;

./DmService start      # Start the database service;

Enable database archiving mode:

./disql SYSDBA/SYSDBA@192.168.222.133:5236        # Enter disql;

alter database mount;                    # Switch the database to mount mode;

alter database add archivelog 'dest=/home/dmdba/dmarch,TYPE=local,FILE_SIZE=1024 SPACE_LIMIT=102400';

alter database archivelog;

alter database open;

Method 2:

And DM Ini in the same directory, modify or create a dmarch INI file;

vim /home/dmdba/dmdbms/data/DAMENG/dmarch.ini

Add the following:

[ARCHIVE_LOCAL1]

ARCH_TYPE=local

ARCH_DEST=/home/dmdba/dmarch

ARCH_FILE_SIZE=1024

ARCH_SPACE_LIMIT=102400

Modify DM Ini setting parameters:

ARCH_INI=1                 # Open the archive log;

Open database:

./DmService start            # Open database service

ps -ef | grep dmserver       # Check the process and installation directory of the database;

(2) Full database backup

./disql SYSDBA/SYSDBA@192.168.222.133:5236   # Enter the disql tool;

backup database full backupset '/home/dmdba/dmbakup/dbfull';

select * from v$backupset;          # Viewing database backup set files

(3) Other object backup statements

backup tablespace tablespace_name full backupset '/home/dmdba/dmbakup/tbsp';  # Table space backup;
backup table user_name.table_name full backupset '/home/dmdba/dmbakup/tb';    # Table backup;

(4) To back up and restore a database using RMAN tools:

./dmrman         # Enter the RMAN tool:

backup database full backupset '/home/dmdba/dmbakup/dbfull';    # Backup library;

check backupset '/home/dmdba/dmbakup/dbfull';    # Verify the legality of the backup set;

restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmbakup/dbfull ';        # Restore the database;

2. Offline backup

(1) Offline backup database:

In the bin directory of the installation path, close the database service and start the DMAP service:

./DmService stop 		# Shut down the database service;

./DmAPService start		# Start DMAP service;

ps -ef | grep dmap		# Check whether the DMAP service is started;

./dmrman 				# Enter RMAN tool;

Backup database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' backupset '/home/dmdba/dmbakup/dbfull';		# Perform full database backup;

(2) Restore database:

After starting the DMAP service, enter the RMAN tool:

./dmrman				# Enter RMAN tool;

Execute in sequence:

Restore database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from backupset '/home/dmdba/dmbakup/dbfull';		# Execute the restore statement;

Recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' from 	backupset '/home/dmdba/dmbakup/dbfull';		# Execute the recover statement;

Recover database '/home/dmdba/dmdbms/data/DAMENG/dm.ini' update db_magic;	# Execute recover update db_magic statement;