Oracle 12c数据库定时备份和清理脚本

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