Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to execute the SQL statement for archival backup". In the daily operation, I believe many people have doubts about how to execute the SQL statement for archival backup. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to execute the SQL statement for archival backup". Next, please follow the editor to study!

1. Overview

Use the backup statement in the DIsql tool to back up the archive log. The premise of using archived backup is: first, the db_ magic value of the archived file must be the same as that of the library; second, the server must be configured with archiving; third, the archived logs must be continuous, and if discontiguous occurs, the previous one will be ignored and only the latest consecutive parts will be backed up. If no archives within the specified range are collected, they are not backed up. Archive files are often switched during online backups, and the last archive is always empty, so the last archive is not backed up.

The archive can be backed up by entering the following in DIsql:

SQL > select * from v$dm_ini where para_name='BAK_PATH' LINEID PARA_NAME PARA_VALUE MIN_VALUE MAX_VALUE MPP_CHK SESS_VALUE FILE_VALUE DESCRIPTION PARA_TYPE -1 BAK_PATH / dm_home/dmdba/dmdbms/data/jydm/bak NULL NULL N / dm_home/dmdba/dmdbms/data/jydm/bak / dm_home/dmdba/dmdbms/data/jydm/bak backup file path READ ONLYused time: 7.311 (ms). Execute id is 1219.SQL > backup archivelog all to archivelog_backup_2020052901 backupset 'archivelog_backup_2020052901';backup archivelog all to archivelog_backup_2020052901 backupset' archivelog_backup_2020052901';: Pipe connect failure.used time: 2.618 (ms). Execute id is 0.

To use dmap in Oracle Linux7.1, you need to start the DmAPService service and dmap execution code manually. When you start the DmAPService service in Redhat, you will start dmap at the same time.

[root@shard1 bak] # systemctl start DmAPService [dmdba@shard1 ~] $cd / dm_home/dmdba/dmdbms/ [dmdba @ shard1 bin] $. / dmapdmap V7.1.6.46-Build (2018.02.08-89107) ENTdmap is readySQL > backup archivelog all to archivelog_backup_2020052901 backupset 'archivelog_backup_2020052901';executed successfullyused time: 00 archivelog_backup_2020052901';executed successfullyused time: 00 cd 34.482. Execute id is 1234.SQL > select * from v$backupset where backup_path='/dm_home/dmdba/dmdbms/data/jydm/bak/archivelog_backup_2020052901' LINEID DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME OBJECT_ID BASE_NAME BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END_LSN BKP_NUM DBF_NUM PARALLEL_NUM DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE-- -- 1 DISK-30479448-1 ARCHIVELOG_BACKUP_2020052901 / dm_home/dmdba/dmdbms/data/jydm/bak/archivelog_backup_2020052901 304ARCHIVE-1 2020-05-29 18 purse 44 purl 25.000480 00 10 33554432 8236220 15088441 1 9 10 0 15088441 117507596 0used time: 00:00:01.010. Execute id is 1266.SQL > select * from v$parameter where name='BAK_USE_AP' LINEID ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION-- -1541 BAK_USE_AP SYS 1 1 1 backup use assistant plus-in 0:use sub process 1:use AP; 2:not use AP. Default 1.used time: 6.765 (ms). Execute id is 1252.SQL > select * from v$parameter where name='BAK_POLICY' LINEID ID NAME TYPE VALUE SYS_VALUE FILE_VALUE DESCRIPTION--1 7 BAK_POLICY SYS 0 0 0 backup patternused time: 7.405 (ms). Execute id is 1253.

The backup set "archivelog_backup_2020052901" is generated to the default backup path. If you want to set other backup options, refer to the backup archive syntax below.

The syntax is as follows:

BACKUP [ALL | [FROM LSN] | [UNTIL LSN] | [LSN BETWEEN AND] | [FROM TIME''] | [UNTIL TIME''] | [TIME BETWEEN'' > AND'']] [] [DELETE INPUT] [TO

] BACKUPSET ['

'] [DEVICE TYPE

[PARMS'

']] [BACKUPINFO'

'] [MAXPIECESIZE

] [IDENTIFIED BY

[WITH ENCRYPTION] [ENCRYPT WITH

]] [COMPRESSED [LEVEL

] [WITHOUT LOG] [TRACE FILE''] [TRACE LEVEL] [TASK THREAD

] [PARALLEL [

]]

ALL: back up all archives

FROM LSN: specifies the starting lsn of the backup.

UNTIL LSN: specifies the expiration lsn of the backup. The valid LSN range of the archive log (starting lsn, ending lsn) can be viewed through V$ARCH_FILE or by analyzing the results of the log through the dmclvt tool. If the user is unable to determine the exact lsn, you can also specify a fuzzy LSN value with a range of values (1: 9223372036854775807). As long as the specified FROM LSN and UNTIL LSN overlap with the valid LSN range, the full log file containing the overlap is backed up.

FROM TIME: specifies the point in time when the backup starts.

UNTILTIME: specifies the point in time for the backup to expire.

BETWEEN... AND...: specify the backup interval. When a specified range is specified, only the archived files within the specified range are backed up. Search and filter. Search filtering is limited to all archive backup sets that can be found based on the criteria specified by the backup. 1) num TIMES, with a value range of 0room2147483647, which means that if the archive file has been backed up for num times, it will not be backed up again; otherwise, it will not be backed up. For example, num=3, archive files that have been backed up three times are considered to be no longer backed up. If num=0, it is considered that nothing needs to be backed up. 2) the archive files that have not been backed up are backed up at the time specified by SINCE TIME 'datetime_String',. 3) if neither of the above is specified, back up all unbacked up archive log files.

DELETE INPUT: used to specify whether to delete the archive operation after the backup is complete.

TO: specifies the name of the generated backup. If not specified, the system generates randomly. The default backup name format is: ARCH_ backup time. Where the backup time is the system time to start the backup.

BACKUPSET: specifies the current backup set generation path, or generates the backup set in the default backup path if specified as a relative path. If you do not specify a specific backup set path, the default archive backup set directory is generated with the agreed archive backup set naming rules under the default backup path. Archive backup default backup set directory name generation rule: ARCH_LOG_ time, such as

ARCH_LOG_20160518_143057_123456 . Indicates that the backup set is an archived backup set generated at 14:30:57 123456 milliseconds on May 18, 2016.

DEVICE TYPE: refers to the type of media on which the backup set is stored, which supports DISK and TAPE. The default is DISK. DISK means to store backup sets to disk, and TAPE means to store to tape.

PARMS: valid only if the media type is TAPE.

BACKUPINFO: description of the backup. The maximum is 256 bytes.

MAXPIECESIZE: maximum backup slice file size limit, in M units, the minimum 128MMaget 32-bit system, the maximum 2GMAG 64-bit system, the maximum 128G.

IDENTIFIED BY: specifies the encrypted password for backup. Passwords should be enclosed in double quotation marks to prevent some special characters from failing syntax checking. The password setting rules follow the password policy specified by the ini parameter pwd_policy.

WITH ENCRYPTION: specify the encryption type. 0 means no encryption and no encryption of the backup file; 1 means simple encryption and sets a password for the backup file, but the contents of the file are still stored in plaintext; 2 means full data encryption, complete encryption of the backup file, and backup file is stored in ciphertext.

ENCRYPT WITH: encryption algorithm. By default, the algorithm is AES256_CFB.

Encryption algorithms include:

DES_ECB 、 DES_CBC 、 DES_CFB 、 DES_OFB 、 DESEDE_ECB 、 DESEDE_CBC 、 DESEDE_CFB 、 DESEDE_OFB 、 AES128_ECB 、 AES128_CBC 、 AES128_CFB 、 AES128_OFB 、 AES192_ECB 、 AES192_CBC 、 AES192_CFB 、 AES192_OFB 、 AES256_ECB 、 AES256_CBC 、 AES256_CFB 、 AES256_OFB 、 RC4

COMPRESSED: value range: 0-9. 0 means no compression, 1 means level 1 compression, and 9 represents level 9 compression. The higher the compression level, the slower the compression, but the higher the compression ratio. If not specified, but COMPRESSED is specified, the default is 1; otherwise, the default is 0.

WITHOUT LOG: it's just syntax support, and it doesn't work.

TRACE FILE: specifies the generated TRACE file. When TRACE is enabled, but TRACE FILE is not specified, the DM_SBTTRACE_ year and month .log file is generated by default in the log directory of the DM database system; if the relative path is used, it is generated in the same level directory of the execution code. If specified by the user, the specified file cannot be an existing file, otherwise an error will be reported, nor can it be an ASM file.

TRACE LEVEL: valid values 1, 2. Default 1 means that TRACE is not enabled. If TRACE FILE is specified, a TRACE file will be generated, but TRACE information will not be written. Enable TRACE for 2 and write TRACE-related content.

TASK THREAD: the number of data processing threads in the backup process. Value range: 0: 64. Default is 4. If specified as 0, it is adjusted to 1; if the specified number exceeds the current system host cores, it is adjusted to the current host cores. Number of threads (TASK THREAD) * number of parallelism (PARALLEL) must not exceed 512.

PARALLEL: specifies the number of parallel backups, with a value range of 0,128. If not specified, the default is 4, and either 0 or 1 is considered a non-parallel backup. Parallel backups do not support backups with TAPE media. Number of threads (TASK THREAD) * number of parallelism (PARALLEL) must not exceed 512.

two。 Set backup options

The common backup options for archive backup are setting backup name, setting backup set path, specifying media parameters, adding backup description, and so on, in the same way as database backup.

3. Backup Archive

Archive backup copy all the archive files under the specified archive directory to the backup set, and record the attributes of each archive file, file size, LSN interval, etc. There is no incremental backup for archive backup

The theory of. Archive backups can be used when you need to save the archive of the library.

The complete backup archive steps are as follows:

1) for configuration archiving, refer to Section 3.1.

2) the database is in OPEN or MOUNT state.

3) enter the backup database statement in DIsql.

For example, back up the archive log through LSN BETWEEN... AND... To specify the start and end LSN.

First, determine the LSN range.

SQL > select ARCH_LSN, CLSN, PATH from V$ARCH_FILE LINEID ARCH_LSN CLSN PATH-- -1 00 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529193925775_0.log2 8236220 8493250 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200407140301179_0.log3 8493250 9294927 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200407225606099_0.log4 9294928 11047946 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200410180147714_0.log5 11047947 11054313 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200425175952636_0.log6 11054313 12906866 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200509201513282_0.log7 12906867 13775969 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200513203613043_0.log8 13775970 14461220 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200526003546144_0.log9 14461221 14942037 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200526003632028_0.log10 14942037 15088441 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200528230749046_0.log11 15088442 15092081 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529184351533_0.log12 15092082 15092086 / dm_home/dmdba/dmdbms/data/arch/ARCHIVE_LOCAL1_20200529193755551_0.log12 rows got

Select the starting LSN and ending LSN of the backup from the query results. For example, 15092082 15092086

Second, backup and archiving.

SQL > backup archivelog lsn between 15092082 and 15092086 backupset 'arch_backup_lsn_15092082_15092086';executed successfullyused time: 0000 and 01.174. Execute id is 1316.SQL > select * from v$backupset where backup_path='/dm_home/dmdba/dmdbms/data/jydm/bak/arch_backup_lsn_15092082_15092086' LINEID DEVICE_TYPE BACKUP_ID PARENT_ID BACKUP_NAME BACKUP_PATH TYPE LEVEL RANGE# OBJECT_NAME OBJECT_ID BASE_NAME BACKUP_TIME DESC# ENCRYPT_TYPE COMPRESS_LEVEL WITHOUT_LOG USE_PWR PKG_SIZE BEGIN_LSN END _ LSN BKP_NUM DBF_NUM PARALLEL_NUM DDL_CLONE MPP_FLAG MIN_TRX_START_LSN MIN_EXEC_VER CUMULATIVE-- - -- -- 1 DISK-1385444837-1 ARCH_20200529_194216_000768 / dm_home/dmdba/dmdbms/data/jydm/bak/arch_backup_lsn_15092082_15092086 3 0 4 ARCHIVE-1 2020-05-29 19 dm_home/dmdba/dmdbms/data/jydm/bak/arch_backup_lsn_15092082_15092086 42purl 17.000861 00 1 0 33554432 15092082 15092086 11 1 00 15092086 117507596 0used time: 00:00:01.013. Execute id is 1317. At this point, the study on "how to execute SQL statements for archival backup" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.