RMAN除了可以像sqlplus一样通过SSH执行命令之外,还可以与crontab配合执行预先写好的脚本,实现Oracle数据库的定时自动备份和清理备份数据和归档日志。
本文基于Oracle 12c 12.2.0.1.0进行操作
备份和清除应该围绕下面几项要素展开
1、数据库的全备
2、1级2级等等增量备份
3、归档日志
对于备份和删除操作,需要掌握一些知识点,下面先介绍RMAN的基础知识。
先查看一下RMAN默认的备份参数,登录RMAN之后使用show all;即可查看
RMAN> show all
2> ;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RECOVERY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/product/12.2.0/dbhome_1/dbs/snapcf_recoverySid.f'; # default
其中最终要的是CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
这条参数,它规定了数据库全备(也就0级备份的冗余策略),默认该参数冗余为1份,也就是说如果你某年某月某日执行了1次0级备份,那么之前的0级备份和之前的归档日志就全部过时,变成了obsolete状态,然后我们可以使用report obsolete;查看已经过期的全备。注意旧的数据库备份虽然已经被标记obsolete,但是RMAN并不会自动将其删除,必须手动删除。
比如我们使用默认的冗余策略为1来看一下效果,首先我们查看一下目前已经备份过的文件
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 10.19M DISK 00:00:01 20-SEP-17
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20170920T181704
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_20/o1_mf_s_955217824_dw4j112n_.bkp
SPFILE Included: Modification time: 20-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 1425194 Ckp time: 20-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 10.19M DISK 00:00:01 21-SEP-17
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20170921T090804
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_21/o1_mf_s_955271284_dw647o4s_.bkp
SPFILE Included: Modification time: 21-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2228302 Ckp time: 21-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 Full 10.19M DISK 00:00:00 22-SEP-17
BP Key: 9 Status: AVAILABLE Compressed: NO Tag: TAG20170922T103948
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955363188_dw8xznhb_.bkp
SPFILE Included: Modification time: 21-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2422741 Ckp time: 22-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 10.19M DISK 00:00:00 22-SEP-17
BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20170922T121540
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955368940_dw93mdoh_.bkp
SPFILE Included: Modification time: 21-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2431350 Ckp time: 22-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
13 Full 10.19M DISK 00:00:01 25-SEP-17
BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20170925T102134
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955621294_dwjt1h0m_.bkp
SPFILE Included: Modification time: 24-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2919603 Ckp time: 25-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 10.19M DISK 00:00:00 25-SEP-17
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20170925T142602
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955635962_dwk8ct6p_.bkp
SPFILE Included: Modification time: 25-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2928090 Ckp time: 25-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 10.19M DISK 00:00:00 25-SEP-17
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20170925T144604
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637164_dwk9kdxy_.bkp
SPFILE Included: Modification time: 25-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2933808 Ckp time: 25-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 10.22M DISK 00:00:00 25-SEP-17
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20170925T145606
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637766_dwkb4650_.bkp
SPFILE Included: Modification time: 25-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2936940 Ckp time: 25-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 10.22M DISK 00:00:00 25-SEP-17
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20170925T152109
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955639269_dwkcm53r_.bkp
SPFILE Included: Modification time: 25-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2942439 Ckp time: 25-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 10.22M DISK 00:00:00 25-SEP-17
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20170925T170625
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955645585_dwkkrkl4_.bkp
SPFILE Included: Modification time: 25-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2424060 Ckp time: 25-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 10.22M DISK 00:00:00 27-SEP-17
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20170927T105718
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_27/o1_mf_s_955796239_dwp4wh7w_.bkp
SPFILE Included: Modification time: 26-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 2617722 Ckp time: 27-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
21 Full 10.22M DISK 00:00:00 30-SEP-17
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20170930T161653
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_30/o1_mf_s_956074613_dwynqowf_.bkp
SPFILE Included: Modification time: 30-SEP-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 3066380 Ckp time: 30-SEP-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
23 Full 10.22M DISK 00:00:01 23-OCT-17
BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20171023T143938
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_23/o1_mf_s_958142378_dyv3oc62_.bkp
SPFILE Included: Modification time: 23-OCT-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 6536164 Ckp time: 23-OCT-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25 Full 10.22M DISK 00:00:00 25-OCT-17
BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20171025T143901
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_25/o1_mf_s_958315141_dz0dd5nv_.bkp
SPFILE Included: Modification time: 23-OCT-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 6804258 Ckp time: 25-OCT-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27 Full 10.22M DISK 00:00:00 06-NOV-17
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20171106T170842
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_11_06/o1_mf_s_959360922_f009ntdv_.bkp
SPFILE Included: Modification time: 06-NOV-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 9287158 Ckp time: 06-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
28 Full 2.58G DISK 00:00:23 15-NOV-17
BP Key: 28 Status: AVAILABLE Compressed: NO Tag: TAG20171115T163721
Piece Name: /backup/full_0tsjl1e2_1_1
List of Datafiles in backup set 28
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 12414768 15-NOV-17 NO /u01/oradata/recovery/system01.dbf
2 Full 12414768 15-NOV-17 NO /u01/oradata/recovery/extra.dbf
3 Full 12414768 15-NOV-17 NO /u01/oradata/recovery/sysaux01.dbf
4 Full 12414768 15-NOV-17 NO /u01/oradata/recovery/undotbs01.dbf
5 Full 12414768 15-NOV-17 NO /u01/oradata/recovery/cocology.dbf
7 Full 12414768 15-NOV-17 NO /u01/oradata/recovery/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29 Full 10.28M DISK 00:00:00 15-NOV-17
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20171115T163747
Piece Name: /backup/c-2041618695-20171115-00
SPFILE Included: Modification time: 07-NOV-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 12414917 Ckp time: 15-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
30 Incr 0 4.01G DISK 00:00:37 23-NOV-17
BP Key: 30 Status: AVAILABLE Compressed: NO Tag: TAG20171123T085153
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/backupset/2017_11_23/o1_mf_nnnd0_TAG20171123T085153_f1d6xbkz_.bkp
List of Datafiles in backup set 30
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 23230464 23-NOV-17 NO /u01/oradata/recovery/system01.dbf
2 0 Incr 23230464 23-NOV-17 NO /u01/oradata/recovery/extra.dbf
3 0 Incr 23230464 23-NOV-17 NO /u01/oradata/recovery/sysaux01.dbf
4 0 Incr 23230464 23-NOV-17 NO /u01/oradata/recovery/undotbs01.dbf
5 0 Incr 23230464 23-NOV-17 NO /u01/oradata/recovery/cocology.dbf
7 0 Incr 23230464 23-NOV-17 NO /u01/oradata/recovery/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31 Full 10.28M DISK 00:00:00 23-NOV-17
BP Key: 31 Status: AVAILABLE Compressed: NO Tag: TAG20171123T085240
Piece Name: /backup/c-2041618695-20171123-00
SPFILE Included: Modification time: 16-NOV-17
SPFILE db_unique_name: RECOVERY
Control File Included: Ckp SCN: 23230485 Ckp time: 23-NOV-17
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32 Incr 0 4.18G DISK 00:00:36 29-NOV-17
BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20171129T180335
Piece Name: /u01/fast_recovery_area/recovery/RECOVERY/backupset/2017_11_29/o1_mf_nnnd0_TAG20171129T180335_f1x1hr26_.bkp
List of Datafiles in backup set 32
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 26018564 29-NOV-17