Physical backup

• Reference learning articles

Official documents
A better article on the preparation
Online articles corresponding to official documents
dmrman physical offline backup article

on line backup

There are two ways to start archiving

• Online archive configuration
• Manual archive configuration

be careful
During online backup, closing and reopening the configured local archive will result in the loss of some logs in the archive file,
When checking the continuity of archive files during backup, an error will be reported. When such operations exist, if the user wants to avoid this error,
Before backup, you need to call checkpo int(100) to actively refresh the checkpoint.

Database backup

• Perform full backup

SQL> backup database full backupset '/dmdata/dmbak/db_full_bak_01';
executed successfully
used time: 00:00:04.132. Execute id is 501.

• Specify backup set path

SQL> backup database backupset '/dmdata/dmbak/db_bak_3_01';

• Specify the backup set name

SQL> backup database to WEEKLY_FULL_BAK backupset '/dmdata/dmbak/db_bak_3_02';

• Specifies the backup set description

SQL> backup database backupset '/dmdata/dmbak/db_bak_3_04' backupinfo 'full_backup';

• The specified backup slice size is 300M

SQL> backup database backupset '/dmdata/dmbak/db_bak_3_05' maxpiecesize 300;

• Specify a compression level of 5

SQL> backup database backupset '/dmdata/dmbak/db_bak_3_06' compressed level 5;

• The specified number of parallels is 8

SQL> backup database backupset '/dmdata/dmbak/db_bak_3_07' parallel 8;

• Incremental backup based on the backup set in / dmdata/dmbak directory

SQL> backup database increment with backupdir '/dmdata/dmbak' backupset '/dmdata/dmbak/db_increment_bak_02';

Tablespace backup

be careful

1. When the backup data exceeds the limit size, a new backup file will be generated. The new backup file name is the initial file name followed by the file number.
2. Table space backup is allowed only when the system is in archive mode.
3. Tablespace backup is not allowed in Mount state.
4. Table space backup is not allowed in MPP environment.

• Full backup of main tablespace

SQL> backup tablespace main full backupset '/dmdata/dmbak/ts_main_bak_01';

• Do incremental backup of main table space based on the backup set in / dmdata/dmbak directory (the default is differential incremental backup, which can be based on multiple backup sets)

SQL> backup tablespace main increment with backupdir '/dmdata/dmbak' backupset '/dmdata/dmbak/ts_main_increment_bak_02';

1. To create a CUMULATIVE incremental backup, you also need to specify the CUMULATIVE parameter. Otherwise, it defaults to differential incremental backup.
2. If the base backup is not in the default backup directory, the with backup dir parameter must be specified to search the base backup set.

• Cumulative incremental backup based on full backup only

SQL> backup tablespace main increment cumulative with backupdir '/dmdata/dmbak' backupset '/dmdata/dmbak/ts_main_increment_cumulative_bak_04';

Table backup

be careful

1. All table backups are online full backups
2. Archive logs do not need to be configured
3. No incremental backup

• Create tables and data backup

SQL> create table tab_01 (id int not null,name varchar not null);
SQL> insert into tab_01 values(1,'pdm');

• Backup tab_ Form 01

SQL> backup table tab_01 backupset '/dmdata/dmbak/tab_bak_01';

• View default backup path

select para_value，para_type from v$dm_ini t where para_name = 'BAK_PATH';

Directory backup management

sf_bakset_backup_dir_add

• Add directory

SQL> select sf_bakset_backup_dir_add('disk','/dmdata/dmbak');
SQL> select * from v$backupset_search_dirs;
LINEID     DIR             
---------- ----------------
1          /dmdata/dmbak
2          /dmdata/dmdb/bak

sf_bakset_backup_dir_remove

• Delete directory

SQL> select sf_bakset_backup_dir_remove('disk','/dmdata/dmbak');
SQL> select * from v$backupset_search_dirs;
LINEID     DIR             
---------- ----------------
1          /dmdata/dmdb/bak

sf_bakset_backup_dir_remove_all

• Delete all directories except the default directory

SQL> select * from v$backupset_search_dirs;
LINEID     DIR             
---------- ----------------
1          /dmdata/dmbak/a
2          /dmdata/dmbak/b
3          /dmdata/dmdb/bak

SQL> select sf_bakset_backup_dir_remove_all();
LINEID     SF_BAKSET_BACKUP_DIR_REMOVE_ALL()
---------- ---------------------------------
1          1

SQL> select * from v$backupset_search_dirs;
LINEID     DIR             
---------- ----------------
1          /dmdata/dmdb/bak

Backup set verification and deletion

sf_bakset_check

• Check the backup set and specify the path to the backup file

SQL> backup database full to db_full_bak_2_12_01 backupset '/dmdata/dmbak/db_full_bak_2_12_01';
SQL> select sf_bakset_check('disk','/dmdata/dmbak/db_full_bak_2_12_01');
LINEID     SF_BAKSET_CHECK('disk','/dmdata/dmbak/db_full_bak_2_12_01')
---------- -----------------------------------------------------------
1          1
SQL> select * from v$backupset_search_dirs;
SQL> select * from v$backupset;

sf_bakset_remove

• Deletes the backup set for the specified device type and the specified backup set directory

--The third parameter is 0 by default, and 1 represents associated deletion
--You need to add a directory, v$backupset To find the information of the backup set
SQL> select * from v$backupset_search_dirs;
1          /dmdata/dmbak
--Adding a parent directory eliminates the need for a single backup set to add a directory
SQL> backup database full to db_rac_bak_for_remove backupset '/dmdata/dmbak/db_rac_bak_for_remove';
SQL> backup database increment base on backupset '/dmdata/dmbak/db_rac_bak_for_remove' backupset '/dmdata/dmbak/db_rac_bak_for_remove_incr';
SQL> select sf_bakset_backup_dir_add('disk','/dmdata/dmbak/db_rac_bak_for_remove');
SQL> select sf_bakset_backup_dir_add('disk','/dmdata/dmbak/db_rac_bak_for_remove_incr');
SQL> select * from v$backupset;
SQL> select sf_bakset_remove('disk','/dmdata/dmbak/db_rac_bak_for_remove');
[-8202]:Be the base backupset of /dmdata/dmbak/db_rac_bak_for_remove_incr,cannot be removed.
SQL> select sf_bakset_remove('disk','/dmdata/dmbak/db_rac_bak_for_remove',1);
SQL> select * from v$backupset;

sf_bakset_remove_batch

• Batch delete all backup sets that meet the specified conditions

1. end_time: delete the end time of backup set generation, only delete end_ The backup set before time must be specified.
2. range: Specifies the level at which backups are deleted. 1 represents library level, 2 represents tablespace level, 3 represents table level, and 4 represents archive backup. If NULL is specified, the distinction between backup sets and backup levels is ignored.
3. obj_name: the name of the backup object in the backup set to be deleted. Only tablespace level and table level are valid. If it is a table level backup deletion, you need to specify the complete table name (mode. Table name). Otherwise, it will be considered to delete the table backup in the current mode of the session. If NULL is specified, the distinction between backup object names in the backup set is ignored.

SQL> backup database full to db_rac_full_bak_for_remove backupset '/dmdata/dmbak/db_rac_full_bak_for_remove';
SQL> backup tablespace main full to tab_main_full_bak_for_remove backupset '/dmdata/dmbak/tab_main_full_bak_for_remove';
--Confirm that the backup directory and backup set can be found
SQL> select * from v$backupset_search_dirs;
SQL> select * from v$backupset;
SQL> select sf_bakset_remove_batch('disk',now(),null,null);
SQL> select * from v$backupset;

sp_db_bakset_remove_batch

• Batch delete database backup sets before the specified time

1. SF needs to be used first_ BAKSET_ BACKUP_ DIR_ Add adds the backup set directory to be deleted, otherwise only the backup set under the default backup path will be deleted

SQL> backup database full to db_rac_full_bak_for_del backupset '/dmdata/dmbak/db_rac_full_bak_for_del';
SQL> select * from v$backupset_search_dirs;
SQL> select * from v$backupset;
SQL> call sp_db_bakset_remove_batch('disk',now());

sp_ts_bakset_remove_batch

• Batch delete the specified tablespace objects and tablespace backup sets before the specified time

1. The third parameter tablespace name must be uppercase

SQL> backup tablespace main full to tab_main_bak_full_for_del backupset '/dmdata/dmbak/tab_main_bak_full_for_del';
SQL> select * from v$backupset;
SQL> call sp_ts_bakset_remove_batch('disk',now(),'main');
DMSQL executed successfully
SQL> select * from v$backupset;
--Although the execution is successful, the query result still exists tab_main_bak_full_for_del Backup set
SQL> call sp_ts_bakset_remove_batch('disk',now(),'MAIN');
SQL> select * from v$backupset;
--The backup set has been deleted, indicating that the tablespace name should be capitalized

sp_tab_bakset_remove_batch

• Batch delete the specified table object and the table backup set before the specified time

1. sch_name: the name of the schema to which the table belongs.
2. tab_name: table name. As long as the schema name and table name are specified, it is considered that the target needs to be matched; If NULL is specified, all table backups that meet the conditions are considered to be deleted.

• After the operation is completed, be sure to check whether the result is executed successfully
• Parameters within double quotes are case sensitive

--Create test table
SQL> create table tab_for_del_batch(c1 int);
SQL> insert into tab_for_del_batch values (1);
SQL> commit;
SQL> select * from tab_for_del_batch;
--Create backup, delete backup, view backup set
SQL> backup table tab_for_del_batch to tab_bak_for_del_batch backupset '/dmdata/dmbak/tab_bak_for_del_batch';
SQL> select * from v$backupset;
SQL> sp_tab_bakset_remove_batch('disk',now(),'SYSDBA','TAB_FOR_DEL_BATCH');

sp_arch_bakset_remove_batch

• Batch delete archived backup sets before the specified time

SQL> backup archivelog to arch_bak_for_del_batch backupset '/dmdata/dmbak/arch_bak_for_del_batch';
SQL> select * from v$backupset_search_dirs;
SQL> select * from v$backupset;
SQL> sp_arch_bakset_remove_batch('disk',now());

offline backup

Start DMRMAN

[dmdba@dmoasys bin]$ pwd 
/home/dmdba/dmdbms/bin
[dmdba@dmoasys bin]$ ./dmrman
dmrman V8
RMAN> exit            
time used: 1.469(ms)

Backup database

• You need to set the database to shutdown state to execute

• Do everything

RMAN> backup database '/dmdata/dmdb/dm.ini' full backupset '/dmdata/dmbak/db_full_bak_01';
backup database '/dmdata/dmdb/dm.ini' full backupset '/dmdata/dmbak/db_full_bak_01';
file dm.key not found, use default license!
[-137]:DM server is running or exist other process which is operating the same database
SQL> shutdown immediate;
RMAN> BACKUP DATABASE '/dmdata/dmdb/dm.ini' FULL BACKUPSET '/dmdata/dmbak/db_full_bak_01';
BACKUP DATABASE '/dmdata/dmdb/dm.ini' FULL BACKUPSET '/dmdata/dmbak/db_full_bak_01';
Database mode = 0, oguid = 0
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
begin redo pwr log collect, last ckpt lsn: 51771 ...
redo pwr log collect finished
EP[0]'s cur_lsn[55923], file_lsn[55923]
Processing backupset /dmdata/dmbak/db_full_bak_01
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]                                 
backup successfully!
time used: 00:00:01.725

• When adding a backup, the data must be changed after the full backup

RMAN> backup database '/dmdata/dmdb/dm.ini' increment with backupdir '/dmdata/dmbak' backupset '/dmdata/dmbak/db_increment_bak_02';
redo pwr log collect finished
EP[0]'s cur_lsn[55923], file_lsn[55923]
[-8084]:BEGIN_LSN is less than END_LSN of base backupset
SQL> insert into tab_for_del_batch values (2);
RMAN> check backupset '/dmdata/dmbak/db_full_bak_01';
EP[0]'s cur_lsn[58609], file_lsn[58609]
Processing backupset /dmdata/dmbak/db_increment_bak_02
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]                                 
backup successfully!
time used: 00:00:01.181

be careful

1. Incremental backup refers to a backup (full backup or incremental backup) based on the specified library, which backs up all modified data pages since the backup. Offline incremental backup requires that the database must be operated between two backups, otherwise the backup will report an error.
2. The WITH BACKUPDIR parameter is used to search the base backup set.
3. The INCREMENT parameter indicates that the executed backup is incremental. This parameter cannot be omitted.

• Verifying a specific backup set

RMAN> check backupset '/dmdata/dmbak/db_full_bak_01';
check backupset '/dmdata/dmbak/db_full_bak_01';
[Percent:100.00%][Speed:0.00M/s][Cost:00:00:00][Remaining:00:00:00]                                 
check backupset successfully.
time used: 98.260(ms)

Backup set deletion

• You can delete only one backup set at a time

RMAN> backup database '/dmdata/dmdb/dm.ini' backupset '/dmdata/dmbak/db_bak_for_remove_01';
RMAN> remove backupset '/dmdata/dmbak/db_bak_for_remove_01';

• The backup set is in the default backup directory of the database, and the default directory is in DM Ini parameter BAK_PATH
• You can delete it as follows

RMAN> backup database '/dmdata/dmdb/dm.ini' backupset 'db_bak_for_remove_01';
RMAN> remove backupset 'db_bak_for_remove_01' database '/dmdata/dmdb/dm.ini';

• The backup set is the base backup of other backup sets, and all backup sets are in the default backup directory of the database
• Associated deletion

backup database '/dmdata/dmdb/dm.ini' backupset 'db_bak_for_remove_01';
backup database '/dmdata/dmdb/dm.ini' increment backupset 'db_bak_for_remove_01_incr';
remove backupset 'db_bak_for_remove_01_incr' database '/dmdata/dmdb/dm.ini' cascade;

• Bulk delete all backups

backup database '/dmdata/dmdb/dm.ini' backupset '/dmdata/dmbak/db_bak_for_remove_02';
remove backupsets with backupdir '/dmdata/dmbak';

24-hour free service hotline: 400 991 6599
Dameng technology community: https://eco.dameng.com