精华内容
下载资源
问答
  • oracle 12c 数据库 教程

    2018-11-06 10:57:05
    (六)Oracle 数据库备份的解决方案 89 二、闪回 90 (一)什么是闪回 Flashback? 90 (五)闪回查询:Flashback Query 91 (六)闪回版本查询:Flashback Version Query 92 (七)闪回表:Flashback Table 93 (八...
  • 这是一篇学习笔记,在这篇笔记中,对Oracle数据库和归档日志进行了备份,然后将数据库删掉。之后通过恢复spfile+恢复控制文件+restore数据库+recover数据库的方式进行了恢复。环境: Oracle 12.2.0.1.0 on LINUX准备...

    这是一篇学习笔记,在这篇笔记中,对Oracle数据库和归档日志进行了备份,然后将数据库删掉。之后通过恢复spfile+恢复控制文件+restore数据库+recover数据库的方式进行了恢复。

    环境: Oracle 12.2.0.1.0 on LINUX

    准备工作: 数据库为归档模式,归档目录为/home/oracle/archlog/orcl。并且已经将归档目录里的文件清空。打开数据库,查询表T1的内容如下:

    SQL>

    archive log list;

    Database log mode              Archive Mode

    Automatic archival             Enabled

    Archive destination            /home/oracle/archlog/orcl

    Oldest online log sequence     8

    Next log sequence to archive   10

    Current log sequence           10

    SQL>

    select * from t1;

    ID

    ----------

    10010

    [oracle@node01 ~]$

    ls -ltrh /home/oracle/archlog/orcl

    total 0

    1. 备份数据库这一部分中,先备份了全库,然后往表T1里插入了一条记录,最后备份日志。这样restore只能恢复一条记录,recover之后才能恢复新加的记录

    创建一个目录,用于存放备份位置

    $

    mkdir -p /home/oracle/orcbackup

    $

    export NLS_DATA_FORMAT='dd-mon-yyyy hh24:mi:ss'

    打开RMAN,

    $

    rman target /

    RMAN>

    set echo on;

    RMAN>

    configure device type disk parallelism 1;

    设置数据库备份文件的目录和格式:

    RMAN>

    configure channel 1 device type disk format '/home/oracle/orcbackup/rman1_%U.bk';

    设置spfile和control file备份文件的目录和格式:

    RMAN>

    show controlfile autobackup;

    RMAN>

    configure controlfile autobackup format for device type disk to '/home/oracle/orcbackup/rman_ctl_%F.bk';

    对数据库进行备份,最后自动备份了spfile和control file:

    RMAN>

    backup incremental level=0 database;

    Starting backup at 09-MAR-18

    using target database control file instead of recovery catalog

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=269 device type=DISK

    channel ORA_DISK_1: starting incremental level 0 datafile backup set

    channel ORA_DISK_1: specifying datafile(s) in backup set

    input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf

    input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

    input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

    input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf

    channel ORA_DISK_1: starting piece 1 at 09-MAR-18

    channel ORA_DISK_1: finished piece 1 at 09-MAR-18

    piece handle=/home/oracle/orcbackup/rman1_0pstcmht_1_1.bk tag=TAG20180309T212517 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46

    channel ORA_DISK_1: starting incremental level 0 datafile backup set

    channel ORA_DISK_1: specifying datafile(s) in backup set

    input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf

    input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/system01.dbf

    input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf

    input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/users01.dbf

    channel ORA_DISK_1: starting piece 1 at 09-MAR-18

    channel ORA_DISK_1: finished piece 1 at 09-MAR-18

    piece handle=/home/oracle/orcbackup/rman1_0qstcmjb_1_1.bk tag=TAG20180309T212517 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

    channel ORA_DISK_1: starting incremental level 0 datafile backup set

    channel ORA_DISK_1: specifying datafile(s) in backup set

    input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf

    input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf

    input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf

    channel ORA_DISK_1: starting piece 1 at 09-MAR-18

    channel ORA_DISK_1: finished piece 1 at 09-MAR-18

    piece handle=/home/oracle/orcbackup/rman1_0rstcmk5_1_1.bk tag=TAG20180309T212517 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

    Finished backup at 09-MAR-18

    Starting Control File and SPFILE Autobackup at 09-MAR-18

    piece handle=/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-01.bk comment=NONE

    Finished Control File and SPFILE Autobackup at 09-MAR-18

    完成后指定的备份目录内容如下:

    [oracle@node01 ~]$

    ls -ltrh /home/oracle/orcbackup

    total 2.1G

    -rw-r-----. 1 oracle oinstall 1.1G Mar  9 21:25 rman1_0pstcmht_1_1.bk

    -rw-r-----. 1 oracle oinstall 524M Mar  9 21:26 rman1_0qstcmjb_1_1.bk

    -rw-r-----. 1 oracle oinstall 513M Mar  9 21:26 rman1_0rstcmk5_1_1.bk

    -rw-r-----. 1 oracle oinstall  18M Mar  9 21:26 rman_ctl_c-1496578512-20180309-01.bk

    往T1中插入一条记录:

    SQL>

    insert into t1 values(20020);

    SQL>

    commit;

    备份归档日志:

    RMAN>

    crosscheck archivelog all;

    RMAN>

    backup archivelog all;

    Starting backup at 09-MAR-18

    current log archived

    using channel ORA_DISK_1

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=2 RECID=1 STAMP=968881650

    input archived log thread=1 sequence=10 RECID=9 STAMP=970349297

    input archived log thread=1 sequence=11 RECID=10 STAMP=970349345

    channel ORA_DISK_1: starting piece 1 at 09-MAR-18

    channel ORA_DISK_1: finished piece 1 at 09-MAR-18

    piece handle=/home/oracle/orcbackup/rman1_0tstcmp1_1_1.bk tag=TAG20180309T212905 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

    Finished backup at 09-MAR-18

    Starting Control File and SPFILE Autobackup at 09-MAR-18

    piece handle=/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-02.bk comment=NONE

    Finished Control File and SPFILE Autobackup at 09-MAR-18

    再次查看备份目录内容如下

    [oracle@node01 ~]$

    ls -ltrh /home/oracle/orcbackup

    total 2.3G

    -rw-r-----. 1 oracle oinstall 1.1G Mar  9 21:25 rman1_0pstcmht_1_1.bk

    -rw-r-----. 1 oracle oinstall 524M Mar  9 21:26 rman1_0qstcmjb_1_1.bk

    -rw-r-----. 1 oracle oinstall 513M Mar  9 21:26 rman1_0rstcmk5_1_1.bk

    -rw-r-----. 1 oracle oinstall  18M Mar  9 21:26 rman_ctl_c-1496578512-20180309-01.bk

    -rw-r-----. 1 oracle oinstall 192M Mar  9 21:29 rman1_0tstcmp1_1_1.bk

    -rw-r-----. 1 oracle oinstall  18M Mar  9 21:29 rman_ctl_c-1496578512-20180309-02.bk

    2. 删除数据库SQL>

    shutdown immediate;

    SQL>

    startup force mount;

    SQL>

    ALTER SYSTEM ENABLE RESTRICTED SESSION;

    SQL>

    drop database;

    3. 恢复数据库

    3.1 恢复spfile先造一个假的pfile,随便起个名叫pfile_tmp.ora,目地是设置数据库的名子,使数据库启动到nomount状态,因此这个文件只包含一条记录即可

    [oracle@node01 ~]$

    export ORACLE_SID=orcl

    [oracle@node01 ~]$

    echo "db_name=orcl" >> $ORACLE_HOME/dbs/pfile_tmp.ora

    RMAN>

    startup nomount pfile='$ORACLE_HOME/dbs/pfile_tmp.ora';

    再从备份中恢复出真正的spfile,由于两次备份操作都备份了spfile和control file,选择最后一个即可:

    RMAN>

    restore spfile from '/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-02.bk';

    RMAN>

    shutdown immediate;

    RMAN>

    startup nomount;

    3.2 恢复控制文件从备份中恢复控制文件

    RMAN>

    restore controlfile from '/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-02.bk';

    RMAN>

    alter database mount;

    3.3 restore db由于都是在本机,备份片的位置没有变化,都记录在上一步的控制文件里,所以这一步比较简单:

    RMAN>

    restore database;

    3.4 recover dbRMAN>

    recover database;

    RMAN>

    alter database open resetlogs;

    3.5 验证[oracle@node01 dbs]$

    sqlplus / as sysdba

    SQL>

    select * from t1;         ID ----------      10010      20020

    展开全文
  • Oracle 12c数据库定时备份和清理脚本

    千次阅读 2017-12-15 17:50:03
    1、每周的周六晚上12:58执行一个全备操作 ...1、我可以恢复从现在到七天前这段时间任意时刻的数据库状态 2、比七天前还早的时间我可以恢复到任意某天晚上24:00的状态 3、一个月之前的备份都被删除,不占用磁盘空间

    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              NO    /u01/oradata/recovery/system01.dbf
      2    0  Incr 26018564   29-NOV-17              NO    /u01/oradata/recovery/extra.dbf
      3    0  Incr 26018564   29-NOV-17              NO    /u01/oradata/recovery/sysaux01.dbf
      4    0  Incr 26018564   29-NOV-17              NO    /u01/oradata/recovery/undotbs01.dbf
      5    0  Incr 26018564   29-NOV-17              NO    /u01/oradata/recovery/cocology.dbf
      7    0  Incr 26018564   29-NOV-17              NO    /u01/oradata/recovery/users01.dbf
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    33      Full    10.28M     DISK        00:00:00     29-NOV-17      
            BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20171129T180421
            Piece Name: /backup/c-2041618695-20171129-00
      SPFILE Included: Modification time: 29-NOV-17
      SPFILE db_unique_name: RECOVERY
      Control File Included: Ckp SCN: 26018597     Ckp time: 29-NOV-17
    

    上面的备份有全备有增量,还有一些控制文件的备份

    从上面的清单中,可以看出#28 2017年11月15日,#30 2017年11月23日,32# 2017年11月29日有三个全备,按照冗余为1的规则,2017年11月29日之前的备份全都属于超标的备份,应当被删除。

    下面使用report obsolete;命令查看一下已经过期的备份

    RMAN> report obsolete;                
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to redundancy 1
    Report of obsolete backups and copies
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           28     15-NOV-17         
      Backup Piece       28     15-NOV-17          /backup/full_0tsjl1e2_1_1
    Backup Set           30     23-NOV-17         
      Backup Piece       30     23-NOV-17          /u01/fast_recovery_area/recovery/RECOVERY/backupset/2017_11_23/o1_mf_nnnd0_TAG20171123T085153_f1d6xbkz_.bkp
    Archive Log          365    26-NOV-17          /home/alex/db_backup/1_324_955645578.dbf
    Archive Log          366    26-NOV-17          /home/alex/db_backup/1_325_955645578.dbf
    Archive Log          367    26-NOV-17          /home/alex/db_backup/1_326_955645578.dbf
    Archive Log          368    26-NOV-17          /home/alex/db_backup/1_327_955645578.dbf
    Archive Log          369    26-NOV-17          /home/alex/db_backup/1_328_955645578.dbf
    Archive Log          370    26-NOV-17          /home/alex/db_backup/1_329_955645578.dbf
    Archive Log          371    27-NOV-17          /home/alex/db_backup/1_330_955645578.dbf
    Archive Log          372    27-NOV-17          /home/alex/db_backup/1_331_955645578.dbf
    Archive Log          373    27-NOV-17          /home/alex/db_backup/1_332_955645578.dbf
    Archive Log          374    27-NOV-17          /home/alex/db_backup/1_333_955645578.dbf
    Archive Log          375    28-NOV-17          /home/alex/db_backup/1_334_955645578.dbf
    Archive Log          376    28-NOV-17          /home/alex/db_backup/1_335_955645578.dbf
    Archive Log          377    28-NOV-17          /home/alex/db_backup/1_336_955645578.dbf
    Archive Log          378    28-NOV-17          /home/alex/db_backup/1_337_955645578.dbf
    Archive Log          379    28-NOV-17          /home/alex/db_backup/1_338_955645578.dbf
    Archive Log          380    28-NOV-17          /home/alex/db_backup/1_339_955645578.dbf
    Archive Log          381    29-NOV-17          /home/alex/db_backup/1_340_955645578.dbf
    Archive Log          382    29-NOV-17          /home/alex/db_backup/1_341_955645578.dbf
    Archive Log          383    29-NOV-17          /home/alex/db_backup/1_342_955645578.dbf
    Archive Log          384    29-NOV-17          /home/alex/db_backup/1_343_955645578.dbf
    


    从这里看出RMAN分析的结果和我们预想的是一样的,11月15日和11月23日的两个全备全都是obsolete状态了,不仅如此,最后一次全备之前的归档日志也被列为了obsolete,这样也是正确的,因为全备都失效了归档日志也就没什么意义了,但是有很多的一级备份没有变成obsolete状态。


    现在我们修改一下冗余数量看看有什么变化,进入RMAN执行CONFIGURE RETENTION POLICY TO REDUNDANCY 7;

    RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 5;    
    
    using target database control file instead of recovery catalog
    old RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
    new RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
    new RMAN configuration parameters are successfully stored
    
    RMAN> 

    然后再检查一遍obsolete的项目

    RMAN> report obsolete;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to redundancy 5
    no obsolete backups found
    
    RMAN> 

    可以看到,由于增加了冗余数量,原先obsolete的备份现在被剔除出这个名单了。

    除了通过冗余数量来判定obsolete之外,还有一个配置是根据备份保留的天数判定。该判断方式会与冗余数量冲突,以最后一次配置的为准,比较如下

    首先设置两个冗余

    RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;      
    
    old RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
    new RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
    new RMAN configuration parameters are successfully stored
    
    RMAN> report obsolete;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to redundancy 2
    Report of obsolete backups and copies
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           28     15-NOV-17         
      Backup Piece       28     15-NOV-17          /backup/full_0tsjl1e2_1_1
    

    然后设置20天的备份时间(最后一个备份距今17天)

    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 20 DAYS;   
    
    old RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
    new RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 20 DAYS;
    new RMAN configuration parameters are successfully stored
    
    RMAN> report obsolete;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 20 days
    Report of obsolete backups and copies
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           1      20-SEP-17         
      Backup Piece       1      20-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_20/o1_mf_s_955217824_dw4j112n_.bkp
    Backup Set           5      21-SEP-17         
      Backup Piece       5      21-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_21/o1_mf_s_955271284_dw647o4s_.bkp
    Backup Set           9      22-SEP-17         
      Backup Piece       9      22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955363188_dw8xznhb_.bkp
    Backup Set           11     22-SEP-17         
      Backup Piece       11     22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955368940_dw93mdoh_.bkp
    Backup Set           13     25-SEP-17         
      Backup Piece       13     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955621294_dwjt1h0m_.bkp
    Backup Set           14     25-SEP-17         
      Backup Piece       14     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955635962_dwk8ct6p_.bkp
    Backup Set           15     25-SEP-17         
      Backup Piece       15     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637164_dwk9kdxy_.bkp
    Backup Set           16     25-SEP-17         
      Backup Piece       16     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637766_dwkb4650_.bkp
    Backup Set           17     25-SEP-17         
      Backup Piece       17     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955639269_dwkcm53r_.bkp
    Backup Set           18     25-SEP-17         
      Backup Piece       18     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955645585_dwkkrkl4_.bkp
    Backup Set           20     27-SEP-17         
      Backup Piece       20     27-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_27/o1_mf_s_955796239_dwp4wh7w_.bkp
    Backup Set           21     30-SEP-17         
      Backup Piece       21     30-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_30/o1_mf_s_956074613_dwynqowf_.bkp
    Backup Set           23     23-OCT-17         
      Backup Piece       23     23-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_23/o1_mf_s_958142378_dyv3oc62_.bkp
    Backup Set           25     25-OCT-17         
      Backup Piece       25     25-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_25/o1_mf_s_958315141_dz0dd5nv_.bkp
    Backup Set           27     06-NOV-17         
      Backup Piece       27     06-NOV-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_11_06/o1_mf_s_959360922_f009ntdv_.bkp
    Backup Set           28     15-NOV-17         
      Backup Piece       28     15-NOV-17          /backup/full_0tsjl1e2_1_1
    Backup Set           29     15-NOV-17         
      Backup Piece       29     15-NOV-17          /backup/c-2041618695-20171115-00
    

    可以看到,设置恢复窗口天数之后就把冗余数量的设置给冲掉了,这两个以最后一个设定为准。

    但是设定天数和设置冗余数还不是完全一样的,如上,当设置冗余数为1的时候,两个旧的全备会obsolete,最后一次全备之前的归档日志也会obsolete,但是这期间的1级备份和数据库控制文件的备份没有obsolete。在设定天数之后(保留最后一次全备的时间),两个旧的全备会obsolete,这期间的1级备份和数据库控制文件的备份也会obsolete。

    除非你把保留天数设置到最后一次全备之后的一个时间,才会把旧的全备,归档日志以及1级2级的增备删掉,如下(最后一次全备距今17天,而我设置保留天数为10天)

    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; 
    
    old RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 25 DAYS;
    new RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
    new RMAN configuration parameters are successfully stored
    
    RMAN> report obsolete;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 10 days
    Report of obsolete backups and copies
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           1      20-SEP-17         
      Backup Piece       1      20-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_20/o1_mf_s_955217824_dw4j112n_.bkp
    Backup Set           5      21-SEP-17         
      Backup Piece       5      21-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_21/o1_mf_s_955271284_dw647o4s_.bkp
    Backup Set           9      22-SEP-17         
      Backup Piece       9      22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955363188_dw8xznhb_.bkp
    Backup Set           11     22-SEP-17         
      Backup Piece       11     22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955368940_dw93mdoh_.bkp
    Backup Set           13     25-SEP-17         
      Backup Piece       13     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955621294_dwjt1h0m_.bkp
    Backup Set           14     25-SEP-17         
      Backup Piece       14     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955635962_dwk8ct6p_.bkp
    Backup Set           15     25-SEP-17         
      Backup Piece       15     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637164_dwk9kdxy_.bkp
    Backup Set           16     25-SEP-17         
      Backup Piece       16     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637766_dwkb4650_.bkp
    Backup Set           17     25-SEP-17         
      Backup Piece       17     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955639269_dwkcm53r_.bkp
    Backup Set           18     25-SEP-17         
      Backup Piece       18     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955645585_dwkkrkl4_.bkp
    Backup Set           20     27-SEP-17         
      Backup Piece       20     27-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_27/o1_mf_s_955796239_dwp4wh7w_.bkp
    Backup Set           21     30-SEP-17         
      Backup Piece       21     30-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_30/o1_mf_s_956074613_dwynqowf_.bkp
    Backup Set           23     23-OCT-17         
      Backup Piece       23     23-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_23/o1_mf_s_958142378_dyv3oc62_.bkp
    Backup Set           25     25-OCT-17         
      Backup Piece       25     25-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_25/o1_mf_s_958315141_dz0dd5nv_.bkp
    Backup Set           27     06-NOV-17         
      Backup Piece       27     06-NOV-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_11_06/o1_mf_s_959360922_f009ntdv_.bkp
    Backup Set           28     15-NOV-17         
      Backup Piece       28     15-NOV-17          /backup/full_0tsjl1e2_1_1
    Backup Set           29     15-NOV-17         
      Backup Piece       29     15-NOV-17          /backup/c-2041618695-20171115-00
    Backup Set           30     23-NOV-17         
      Backup Piece       30     23-NOV-17          /u01/fast_recovery_area/recovery/RECOVERY/backupset/2017_11_23/o1_mf_nnnd0_TAG20171123T085153_f1d6xbkz_.bkp
    Archive Log          365    26-NOV-17          /home/alex/db_backup/1_324_955645578.dbf
    Backup Set           31     23-NOV-17         
      Backup Piece       31     23-NOV-17          /backup/c-2041618695-20171123-00
    Archive Log          366    26-NOV-17          /home/alex/db_backup/1_325_955645578.dbf
    Archive Log          367    26-NOV-17          /home/alex/db_backup/1_326_955645578.dbf
    Archive Log          368    26-NOV-17          /home/alex/db_backup/1_327_955645578.dbf
    Archive Log          369    26-NOV-17          /home/alex/db_backup/1_328_955645578.dbf
    Archive Log          370    26-NOV-17          /home/alex/db_backup/1_329_955645578.dbf
    Archive Log          371    27-NOV-17          /home/alex/db_backup/1_330_955645578.dbf
    Archive Log          372    27-NOV-17          /home/alex/db_backup/1_331_955645578.dbf
    Archive Log          373    27-NOV-17          /home/alex/db_backup/1_332_955645578.dbf
    Archive Log          374    27-NOV-17          /home/alex/db_backup/1_333_955645578.dbf
    Archive Log          375    28-NOV-17          /home/alex/db_backup/1_334_955645578.dbf
    Archive Log          376    28-NOV-17          /home/alex/db_backup/1_335_955645578.dbf
    Archive Log          377    28-NOV-17          /home/alex/db_backup/1_336_955645578.dbf
    Archive Log          378    28-NOV-17          /home/alex/db_backup/1_337_955645578.dbf
    Archive Log          379    28-NOV-17          /home/alex/db_backup/1_338_955645578.dbf
    Archive Log          380    28-NOV-17          /home/alex/db_backup/1_339_955645578.dbf
    Archive Log          381    29-NOV-17          /home/alex/db_backup/1_340_955645578.dbf
    Archive Log          382    29-NOV-17          /home/alex/db_backup/1_341_955645578.dbf
    Archive Log          383    29-NOV-17          /home/alex/db_backup/1_342_955645578.dbf
    Archive Log          384    29-NOV-17          /home/alex/db_backup/1_343_955645578.dbf
    
    RMAN> 
    

    其实这个也很好理解,虽然我最后一次全备已经过去超过10天,但是数据库要保证最近十天任意时刻都要能恢复,必须保留最后一次全备和那次全备之后到最近10天的全部归档日志才行,哪怕最后一次全备距今一年那也得留着,不然最后10天就成了空谈。同理,最后一次全备到现在的归档日志和1级2级增备要保留,但是最后一次全备之前的归档日志和1级2级增备就没有意义了,所以都obsolete了。

    这样看起来通过恢复窗口时间要比冗余备份个数要灵活一些,因为设定时间可以把1级2级增备也设置成obsolete,而设置冗余个数没有这个功能。


    假如现在我要删掉三个备份中最后一个备份也就是11月15日的全备和倒数第二个备份之前的所有归档日志和1级2级备份,那么我应该使用设置恢复窗口天数,而且这个天数要是最后一次备份和倒数第二次备份直接,也就是11月23日到11月29日的一天。有人可能会问要删掉11月15日的备份为什么不是11月15日到11月23日中的一天,答案是如果设置这个时间段中,那么11月15日的全备就要用来还原这个期间的数据了,所以11月15日就不会是obsolete状态。

    设置好备份保留天数以后,就可以开始执行删除操作了,命令是:delete obsolete device type disk;

    如下

    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 22 DAYS;
    
    old RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
    new RMAN configuration parameters:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 22 DAYS;
    new RMAN configuration parameters are successfully stored
    
    RMAN> report obsolete
    2> ;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 22 days
    Report of obsolete backups and copies
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           1      20-SEP-17         
      Backup Piece       1      20-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_20/o1_mf_s_955217824_dw4j112n_.bkp
    Backup Set           5      21-SEP-17         
      Backup Piece       5      21-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_21/o1_mf_s_955271284_dw647o4s_.bkp
    Backup Set           9      22-SEP-17         
      Backup Piece       9      22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955363188_dw8xznhb_.bkp
    Backup Set           11     22-SEP-17         
      Backup Piece       11     22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955368940_dw93mdoh_.bkp
    Backup Set           13     25-SEP-17         
      Backup Piece       13     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955621294_dwjt1h0m_.bkp
    Backup Set           14     25-SEP-17         
      Backup Piece       14     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955635962_dwk8ct6p_.bkp
    Backup Set           15     25-SEP-17         
      Backup Piece       15     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637164_dwk9kdxy_.bkp
    Backup Set           16     25-SEP-17         
      Backup Piece       16     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637766_dwkb4650_.bkp
    Backup Set           17     25-SEP-17         
      Backup Piece       17     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955639269_dwkcm53r_.bkp
    Backup Set           18     25-SEP-17         
      Backup Piece       18     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955645585_dwkkrkl4_.bkp
    Backup Set           20     27-SEP-17         
      Backup Piece       20     27-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_27/o1_mf_s_955796239_dwp4wh7w_.bkp
    Backup Set           21     30-SEP-17         
      Backup Piece       21     30-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_30/o1_mf_s_956074613_dwynqowf_.bkp
    Backup Set           23     23-OCT-17         
      Backup Piece       23     23-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_23/o1_mf_s_958142378_dyv3oc62_.bkp
    Backup Set           25     25-OCT-17         
      Backup Piece       25     25-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_25/o1_mf_s_958315141_dz0dd5nv_.bkp
    Backup Set           27     06-NOV-17         
      Backup Piece       27     06-NOV-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_11_06/o1_mf_s_959360922_f009ntdv_.bkp
    Backup Set           28     15-NOV-17         
      Backup Piece       28     15-NOV-17          /backup/full_0tsjl1e2_1_1
    Backup Set           29     15-NOV-17         
      Backup Piece       29     15-NOV-17          /backup/c-2041618695-20171115-00
    
    
    
    RMAN> delete obsolete device type disk;
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 22 days
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=284 device type=DISK
    Deleting the following obsolete backups and copies:
    Type                 Key    Completion Time    Filename/Handle
    -------------------- ------ ------------------ --------------------
    Backup Set           1      20-SEP-17         
      Backup Piece       1      20-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_20/o1_mf_s_955217824_dw4j112n_.bkp
    Backup Set           5      21-SEP-17         
      Backup Piece       5      21-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_21/o1_mf_s_955271284_dw647o4s_.bkp
    Backup Set           9      22-SEP-17         
      Backup Piece       9      22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955363188_dw8xznhb_.bkp
    Backup Set           11     22-SEP-17         
      Backup Piece       11     22-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955368940_dw93mdoh_.bkp
    Backup Set           13     25-SEP-17         
      Backup Piece       13     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955621294_dwjt1h0m_.bkp
    Backup Set           14     25-SEP-17         
      Backup Piece       14     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955635962_dwk8ct6p_.bkp
    Backup Set           15     25-SEP-17         
      Backup Piece       15     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637164_dwk9kdxy_.bkp
    Backup Set           16     25-SEP-17         
      Backup Piece       16     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637766_dwkb4650_.bkp
    Backup Set           17     25-SEP-17         
      Backup Piece       17     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955639269_dwkcm53r_.bkp
    Backup Set           18     25-SEP-17         
      Backup Piece       18     25-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955645585_dwkkrkl4_.bkp
    Backup Set           20     27-SEP-17         
      Backup Piece       20     27-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_27/o1_mf_s_955796239_dwp4wh7w_.bkp
    Backup Set           21     30-SEP-17         
      Backup Piece       21     30-SEP-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_30/o1_mf_s_956074613_dwynqowf_.bkp
    Backup Set           23     23-OCT-17         
      Backup Piece       23     23-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_23/o1_mf_s_958142378_dyv3oc62_.bkp
    Backup Set           25     25-OCT-17         
      Backup Piece       25     25-OCT-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_25/o1_mf_s_958315141_dz0dd5nv_.bkp
    Backup Set           27     06-NOV-17         
      Backup Piece       27     06-NOV-17          /u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_11_06/o1_mf_s_959360922_f009ntdv_.bkp
    Backup Set           28     15-NOV-17         
      Backup Piece       28     15-NOV-17          /backup/full_0tsjl1e2_1_1
    Backup Set           29     15-NOV-17         
      Backup Piece       29     15-NOV-17          /backup/c-2041618695-20171115-00
    
    Do you really want to delete the above objects (enter YES or NO)? YES
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_20/o1_mf_s_955217824_dw4j112n_.bkp RECID=1 STAMP=955217825
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_21/o1_mf_s_955271284_dw647o4s_.bkp RECID=5 STAMP=955271285
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955363188_dw8xznhb_.bkp RECID=9 STAMP=955363188
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_22/o1_mf_s_955368940_dw93mdoh_.bkp RECID=11 STAMP=955368940
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955621294_dwjt1h0m_.bkp RECID=13 STAMP=955621295
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955635962_dwk8ct6p_.bkp RECID=14 STAMP=955635962
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637164_dwk9kdxy_.bkp RECID=15 STAMP=955637164
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955637766_dwkb4650_.bkp RECID=16 STAMP=955637766
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955639269_dwkcm53r_.bkp RECID=17 STAMP=955639269
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_25/o1_mf_s_955645585_dwkkrkl4_.bkp RECID=18 STAMP=955645585
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_27/o1_mf_s_955796239_dwp4wh7w_.bkp RECID=20 STAMP=955796239
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_09_30/o1_mf_s_956074613_dwynqowf_.bkp RECID=21 STAMP=956074613
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_23/o1_mf_s_958142378_dyv3oc62_.bkp RECID=23 STAMP=958142379
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_10_25/o1_mf_s_958315141_dz0dd5nv_.bkp RECID=25 STAMP=958315141
    deleted backup piece
    backup piece handle=/u01/fast_recovery_area/recovery/RECOVERY/autobackup/2017_11_06/o1_mf_s_959360922_f009ntdv_.bkp RECID=27 STAMP=959360922
    deleted backup piece
    backup piece handle=/backup/full_0tsjl1e2_1_1 RECID=28 STAMP=960136642
    deleted backup piece
    backup piece handle=/backup/c-2041618695-20171115-00 RECID=29 STAMP=960136668
    Deleted 17 objects
    
    
    RMAN> 

    然后我们就会发现,倒数第二次全备之前的归档日志,1级2级备份,全备都被删掉了,只剩下第二次全备之后的内容了。

    归档日志经常会占用我们很多空间,除了通过RMAN标记成obsolete之后删除这种方式以外,还可以手动删除,命令为

    DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
    SYSDATE-7就是删除7天前的归档日志

    比如我们可以先看一下档期系统的归档日志

    RMAN> list archivelog all;
    
    List of Archived Log Copies for database with db_unique_name RECOVERY
    =====================================================================
    
    Key     Thrd Seq     S Low Time 
    ------- ---- ------- - ---------
    404     1    363     A 02-DEC-17
            Name: /home/alex/db_backup/1_363_955645578.dbf
    
    405     1    364     A 03-DEC-17
            Name: /home/alex/db_backup/1_364_955645578.dbf
    
    406     1    365     A 03-DEC-17
            Name: /home/alex/db_backup/1_365_955645578.dbf
    
    407     1    366     A 03-DEC-17
            Name: /home/alex/db_backup/1_366_955645578.dbf
    
    408     1    367     A 03-DEC-17
            Name: /home/alex/db_backup/1_367_955645578.dbf
    
    409     1    368     A 03-DEC-17
            Name: /home/alex/db_backup/1_368_955645578.dbf
    
    410     1    369     A 03-DEC-17
            Name: /home/alex/db_backup/1_369_955645578.dbf
    
    411     1    370     A 03-DEC-17
            Name: /home/alex/db_backup/1_370_955645578.dbf
    
    412     1    371     A 04-DEC-17
            Name: /home/alex/db_backup/1_371_955645578.dbf
    
    413     1    372     A 04-DEC-17
            Name: /home/alex/db_backup/1_372_955645578.dbf
    
    414     1    373     A 04-DEC-17
            Name: /home/alex/db_backup/1_373_955645578.dbf
    
    415     1    374     A 04-DEC-17
            Name: /home/alex/db_backup/1_374_955645578.dbf
    
    416     1    375     A 04-DEC-17
            Name: /home/alex/db_backup/1_375_955645578.dbf
    
    417     1    376     A 04-DEC-17
            Name: /home/alex/db_backup/1_376_955645578.dbf
    
    418     1    377     A 04-DEC-17
            Name: /home/alex/db_backup/1_377_955645578.dbf
    
    419     1    378     A 05-DEC-17
            Name: /home/alex/db_backup/1_378_955645578.dbf
    
    420     1    379     A 05-DEC-17
            Name: /home/alex/db_backup/1_379_955645578.dbf
    
    421     1    380     A 05-DEC-17
            Name: /home/alex/db_backup/1_380_955645578.dbf
    
    422     1    381     A 05-DEC-17
            Name: /home/alex/db_backup/1_381_955645578.dbf
    
    423     1    382     A 05-DEC-17
            Name: /home/alex/db_backup/1_382_955645578.dbf
    
    424     1    383     A 05-DEC-17
            Name: /home/alex/db_backup/1_383_955645578.dbf
    
    425     1    384     A 06-DEC-17
            Name: /home/alex/db_backup/1_384_955645578.dbf
    
    426     1    385     A 06-DEC-17
            Name: /home/alex/db_backup/1_385_955645578.dbf
    
    427     1    386     A 06-DEC-17
            Name: /home/alex/db_backup/1_386_955645578.dbf
    
    428     1    387     A 06-DEC-17
            Name: /home/alex/db_backup/1_387_955645578.dbf
    
    429     1    388     A 06-DEC-17
            Name: /home/alex/db_backup/1_388_955645578.dbf
    
    430     1    389     A 06-DEC-17
            Name: /home/alex/db_backup/1_389_955645578.dbf
    
    431     1    390     A 07-DEC-17
            Name: /home/alex/db_backup/1_390_955645578.dbf
    
    432     1    391     A 07-DEC-17
            Name: /home/alex/db_backup/1_391_955645578.dbf
    
    433     1    392     A 07-DEC-17
            Name: /home/alex/db_backup/1_392_955645578.dbf
    
    434     1    393     A 07-DEC-17
            Name: /home/alex/db_backup/1_393_955645578.dbf
    
    435     1    394     A 07-DEC-17
            Name: /home/alex/db_backup/1_394_955645578.dbf
    
    436     1    395     A 07-DEC-17
            Name: /home/alex/db_backup/1_395_955645578.dbf
    
    437     1    396     A 07-DEC-17
            Name: /home/alex/db_backup/1_396_955645578.dbf
    
    438     1    397     A 08-DEC-17
            Name: /home/alex/db_backup/1_397_955645578.dbf
    
    439     1    398     A 08-DEC-17
            Name: /home/alex/db_backup/1_398_955645578.dbf
    
    440     1    399     A 08-DEC-17
            Name: /home/alex/db_backup/1_399_955645578.dbf
    
    441     1    400     A 08-DEC-17
            Name: /home/alex/db_backup/1_400_955645578.dbf
    
    442     1    401     A 09-DEC-17
            Name: /home/alex/db_backup/1_401_955645578.dbf
    
    443     1    402     A 09-DEC-17
            Name: /home/alex/db_backup/1_402_955645578.dbf
    
    444     1    403     A 09-DEC-17
            Name: /home/alex/db_backup/1_403_955645578.dbf
    
    445     1    404     A 09-DEC-17
            Name: /home/alex/db_backup/1_404_955645578.dbf
    
    446     1    405     A 09-DEC-17
            Name: /home/alex/db_backup/1_405_955645578.dbf
    
    447     1    406     A 09-DEC-17
            Name: /home/alex/db_backup/1_406_955645578.dbf
    
    448     1    407     A 09-DEC-17
            Name: /home/alex/db_backup/1_407_955645578.dbf
    
    449     1    408     A 09-DEC-17
            Name: /home/alex/db_backup/1_408_955645578.dbf
    
    450     1    409     A 09-DEC-17
            Name: /home/alex/db_backup/1_409_955645578.dbf
    
    451     1    410     A 09-DEC-17
            Name: /home/alex/db_backup/1_410_955645578.dbf
    
    452     1    411     A 10-DEC-17
            Name: /home/alex/db_backup/1_411_955645578.dbf
    
    453     1    412     A 10-DEC-17
            Name: /home/alex/db_backup/1_412_955645578.dbf
    
    454     1    413     A 10-DEC-17
            Name: /home/alex/db_backup/1_413_955645578.dbf
    
    455     1    414     A 10-DEC-17
            Name: /home/alex/db_backup/1_414_955645578.dbf
    
    456     1    415     A 10-DEC-17
            Name: /home/alex/db_backup/1_415_955645578.dbf
    
    457     1    416     A 11-DEC-17
            Name: /home/alex/db_backup/1_416_955645578.dbf
    
    458     1    417     A 11-DEC-17
            Name: /home/alex/db_backup/1_417_955645578.dbf
    
    459     1    418     A 11-DEC-17
            Name: /home/alex/db_backup/1_418_955645578.dbf
    
    460     1    419     A 11-DEC-17
            Name: /home/alex/db_backup/1_419_955645578.dbf
    
    461     1    420     A 12-DEC-17
            Name: /home/alex/db_backup/1_420_955645578.dbf
    
    462     1    421     A 12-DEC-17
            Name: /home/alex/db_backup/1_421_955645578.dbf
    
    463     1    422     A 12-DEC-17
            Name: /home/alex/db_backup/1_422_955645578.dbf
    
    464     1    423     A 12-DEC-17
            Name: /home/alex/db_backup/1_423_955645578.dbf
    
    465     1    424     A 12-DEC-17
            Name: /home/alex/db_backup/1_424_955645578.dbf
    
    466     1    425     A 12-DEC-17
            Name: /home/alex/db_backup/1_425_955645578.dbf
    
    467     1    426     A 13-DEC-17
            Name: /home/alex/db_backup/1_426_955645578.dbf
    
    468     1    427     A 13-DEC-17
            Name: /home/alex/db_backup/1_427_955645578.dbf
    
    469     1    428     A 13-DEC-17
            Name: /home/alex/db_backup/1_428_955645578.dbf
    
    470     1    429     A 13-DEC-17
            Name: /home/alex/db_backup/1_429_955645578.dbf
    
    471     1    430     A 13-DEC-17
            Name: /home/alex/db_backup/1_430_955645578.dbf
    
    472     1    431     A 13-DEC-17
            Name: /home/alex/db_backup/1_431_955645578.dbf
    
    473     1    432     A 14-DEC-17
            Name: /home/alex/db_backup/1_432_955645578.dbf
    
    474     1    433     A 14-DEC-17
            Name: /home/alex/db_backup/1_433_955645578.dbf
    
    475     1    434     A 14-DEC-17
            Name: /home/alex/db_backup/1_434_955645578.dbf
    
    476     1    435     A 14-DEC-17
            Name: /home/alex/db_backup/1_435_955645578.dbf
    
    

    现在我们如果只想保留最近七天的归档日志,就可以用上面的命令,如下

    RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';
    
    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=284 device type=DISK
    List of Archived Log Copies for database with db_unique_name RECOVERY
    =====================================================================
    
    Key     Thrd Seq     S Low Time 
    ------- ---- ------- - ---------
    404     1    363     A 02-DEC-17
            Name: /home/alex/db_backup/1_363_955645578.dbf
    
    405     1    364     A 03-DEC-17
            Name: /home/alex/db_backup/1_364_955645578.dbf
    
    406     1    365     A 03-DEC-17
            Name: /home/alex/db_backup/1_365_955645578.dbf
    
    407     1    366     A 03-DEC-17
            Name: /home/alex/db_backup/1_366_955645578.dbf
    
    408     1    367     A 03-DEC-17
            Name: /home/alex/db_backup/1_367_955645578.dbf
    
    409     1    368     A 03-DEC-17
            Name: /home/alex/db_backup/1_368_955645578.dbf
    
    410     1    369     A 03-DEC-17
            Name: /home/alex/db_backup/1_369_955645578.dbf
    
    411     1    370     A 03-DEC-17
            Name: /home/alex/db_backup/1_370_955645578.dbf
    
    412     1    371     A 04-DEC-17
            Name: /home/alex/db_backup/1_371_955645578.dbf
    
    413     1    372     A 04-DEC-17
            Name: /home/alex/db_backup/1_372_955645578.dbf
    
    414     1    373     A 04-DEC-17
            Name: /home/alex/db_backup/1_373_955645578.dbf
    
    415     1    374     A 04-DEC-17
            Name: /home/alex/db_backup/1_374_955645578.dbf
    
    416     1    375     A 04-DEC-17
            Name: /home/alex/db_backup/1_375_955645578.dbf
    
    417     1    376     A 04-DEC-17
            Name: /home/alex/db_backup/1_376_955645578.dbf
    
    418     1    377     A 04-DEC-17
            Name: /home/alex/db_backup/1_377_955645578.dbf
    
    419     1    378     A 05-DEC-17
            Name: /home/alex/db_backup/1_378_955645578.dbf
    
    420     1    379     A 05-DEC-17
            Name: /home/alex/db_backup/1_379_955645578.dbf
    
    421     1    380     A 05-DEC-17
            Name: /home/alex/db_backup/1_380_955645578.dbf
    
    422     1    381     A 05-DEC-17
            Name: /home/alex/db_backup/1_381_955645578.dbf
    
    423     1    382     A 05-DEC-17
            Name: /home/alex/db_backup/1_382_955645578.dbf
    
    424     1    383     A 05-DEC-17
            Name: /home/alex/db_backup/1_383_955645578.dbf
    
    425     1    384     A 06-DEC-17
            Name: /home/alex/db_backup/1_384_955645578.dbf
    
    426     1    385     A 06-DEC-17
            Name: /home/alex/db_backup/1_385_955645578.dbf
    
    427     1    386     A 06-DEC-17
            Name: /home/alex/db_backup/1_386_955645578.dbf
    
    428     1    387     A 06-DEC-17
            Name: /home/alex/db_backup/1_387_955645578.dbf
    
    429     1    388     A 06-DEC-17
            Name: /home/alex/db_backup/1_388_955645578.dbf
    
    430     1    389     A 06-DEC-17
            Name: /home/alex/db_backup/1_389_955645578.dbf
    
    431     1    390     A 07-DEC-17
            Name: /home/alex/db_backup/1_390_955645578.dbf
    
    432     1    391     A 07-DEC-17
            Name: /home/alex/db_backup/1_391_955645578.dbf
    
    433     1    392     A 07-DEC-17
            Name: /home/alex/db_backup/1_392_955645578.dbf
    
    434     1    393     A 07-DEC-17
            Name: /home/alex/db_backup/1_393_955645578.dbf
    
    435     1    394     A 07-DEC-17
            Name: /home/alex/db_backup/1_394_955645578.dbf
    
    436     1    395     A 07-DEC-17
            Name: /home/alex/db_backup/1_395_955645578.dbf
    
    437     1    396     A 07-DEC-17
            Name: /home/alex/db_backup/1_396_955645578.dbf
    
    
    Do you really want to delete the above objects (enter YES or NO)? YES
    deleted archived log
    archived log file name=/home/alex/db_backup/1_363_955645578.dbf RECID=404 STAMP=961724639
    deleted archived log
    archived log file name=/home/alex/db_backup/1_364_955645578.dbf RECID=405 STAMP=961744478
    deleted archived log
    archived log file name=/home/alex/db_backup/1_365_955645578.dbf RECID=406 STAMP=961750774
    deleted archived log
    archived log file name=/home/alex/db_backup/1_366_955645578.dbf RECID=407 STAMP=961762523
    deleted archived log
    archived log file name=/home/alex/db_backup/1_367_955645578.dbf RECID=408 STAMP=961777558
    deleted archived log
    archived log file name=/home/alex/db_backup/1_368_955645578.dbf RECID=409 STAMP=961793970
    deleted archived log
    archived log file name=/home/alex/db_backup/1_369_955645578.dbf RECID=410 STAMP=961804021
    deleted archived log
    archived log file name=/home/alex/db_backup/1_370_955645578.dbf RECID=411 STAMP=961819134
    deleted archived log
    archived log file name=/home/alex/db_backup/1_371_955645578.dbf RECID=412 STAMP=961836900
    deleted archived log
    archived log file name=/home/alex/db_backup/1_372_955645578.dbf RECID=413 STAMP=961838292
    deleted archived log
    archived log file name=/home/alex/db_backup/1_373_955645578.dbf RECID=414 STAMP=961844645
    deleted archived log
    archived log file name=/home/alex/db_backup/1_374_955645578.dbf RECID=415 STAMP=961859950
    deleted archived log
    archived log file name=/home/alex/db_backup/1_375_955645578.dbf RECID=416 STAMP=961869872
    deleted archived log
    archived log file name=/home/alex/db_backup/1_376_955645578.dbf RECID=417 STAMP=961884220
    deleted archived log
    archived log file name=/home/alex/db_backup/1_377_955645578.dbf RECID=418 STAMP=961895461
    deleted archived log
    archived log file name=/home/alex/db_backup/1_378_955645578.dbf RECID=419 STAMP=961930852
    deleted archived log
    archived log file name=/home/alex/db_backup/1_379_955645578.dbf RECID=420 STAMP=961942043
    deleted archived log
    archived log file name=/home/alex/db_backup/1_380_955645578.dbf RECID=421 STAMP=961952987
    deleted archived log
    archived log file name=/home/alex/db_backup/1_381_955645578.dbf RECID=422 STAMP=961970585
    deleted archived log
    archived log file name=/home/alex/db_backup/1_382_955645578.dbf RECID=423 STAMP=961974856
    deleted archived log
    archived log file name=/home/alex/db_backup/1_383_955645578.dbf RECID=424 STAMP=962006477
    deleted archived log
    archived log file name=/home/alex/db_backup/1_384_955645578.dbf RECID=425 STAMP=962017797
    deleted archived log
    archived log file name=/home/alex/db_backup/1_385_955645578.dbf RECID=426 STAMP=962031356
    deleted archived log
    archived log file name=/home/alex/db_backup/1_386_955645578.dbf RECID=427 STAMP=962040403
    deleted archived log
    archived log file name=/home/alex/db_backup/1_387_955645578.dbf RECID=428 STAMP=962056807
    deleted archived log
    archived log file name=/home/alex/db_backup/1_388_955645578.dbf RECID=429 STAMP=962057011
    deleted archived log
    archived log file name=/home/alex/db_backup/1_389_955645578.dbf RECID=430 STAMP=962064016
    deleted archived log
    archived log file name=/home/alex/db_backup/1_390_955645578.dbf RECID=431 STAMP=962092939
    deleted archived log
    archived log file name=/home/alex/db_backup/1_391_955645578.dbf RECID=432 STAMP=962107254
    deleted archived log
    archived log file name=/home/alex/db_backup/1_392_955645578.dbf RECID=433 STAMP=962119961
    deleted archived log
    archived log file name=/home/alex/db_backup/1_393_955645578.dbf RECID=434 STAMP=962131470
    deleted archived log
    archived log file name=/home/alex/db_backup/1_394_955645578.dbf RECID=435 STAMP=962143371
    deleted archived log
    archived log file name=/home/alex/db_backup/1_395_955645578.dbf RECID=436 STAMP=962149265
    deleted archived log
    archived log file name=/home/alex/db_backup/1_396_955645578.dbf RECID=437 STAMP=962179416
    Deleted 34 objects
    
    
    RMAN> list archivelog all;
    
    List of Archived Log Copies for database with db_unique_name RECOVERY
    =====================================================================
    
    Key     Thrd Seq     S Low Time 
    ------- ---- ------- - ---------
    438     1    397     A 08-DEC-17
            Name: /home/alex/db_backup/1_397_955645578.dbf
    
    439     1    398     A 08-DEC-17
            Name: /home/alex/db_backup/1_398_955645578.dbf
    
    440     1    399     A 08-DEC-17
            Name: /home/alex/db_backup/1_399_955645578.dbf
    
    441     1    400     A 08-DEC-17
            Name: /home/alex/db_backup/1_400_955645578.dbf
    
    442     1    401     A 09-DEC-17
            Name: /home/alex/db_backup/1_401_955645578.dbf
    
    443     1    402     A 09-DEC-17
            Name: /home/alex/db_backup/1_402_955645578.dbf
    
    444     1    403     A 09-DEC-17
            Name: /home/alex/db_backup/1_403_955645578.dbf
    
    445     1    404     A 09-DEC-17
            Name: /home/alex/db_backup/1_404_955645578.dbf
    
    446     1    405     A 09-DEC-17
            Name: /home/alex/db_backup/1_405_955645578.dbf
    
    447     1    406     A 09-DEC-17
            Name: /home/alex/db_backup/1_406_955645578.dbf
    
    448     1    407     A 09-DEC-17
            Name: /home/alex/db_backup/1_407_955645578.dbf
    
    449     1    408     A 09-DEC-17
            Name: /home/alex/db_backup/1_408_955645578.dbf
    
    450     1    409     A 09-DEC-17
            Name: /home/alex/db_backup/1_409_955645578.dbf
    
    451     1    410     A 09-DEC-17
            Name: /home/alex/db_backup/1_410_955645578.dbf
    
    452     1    411     A 10-DEC-17
            Name: /home/alex/db_backup/1_411_955645578.dbf
    
    453     1    412     A 10-DEC-17
            Name: /home/alex/db_backup/1_412_955645578.dbf
    
    454     1    413     A 10-DEC-17
            Name: /home/alex/db_backup/1_413_955645578.dbf
    
    455     1    414     A 10-DEC-17
            Name: /home/alex/db_backup/1_414_955645578.dbf
    
    456     1    415     A 10-DEC-17
            Name: /home/alex/db_backup/1_415_955645578.dbf
    
    457     1    416     A 11-DEC-17
            Name: /home/alex/db_backup/1_416_955645578.dbf
    
    458     1    417     A 11-DEC-17
            Name: /home/alex/db_backup/1_417_955645578.dbf
    
    459     1    418     A 11-DEC-17
            Name: /home/alex/db_backup/1_418_955645578.dbf
    
    460     1    419     A 11-DEC-17
            Name: /home/alex/db_backup/1_419_955645578.dbf
    
    461     1    420     A 12-DEC-17
            Name: /home/alex/db_backup/1_420_955645578.dbf
    
    462     1    421     A 12-DEC-17
            Name: /home/alex/db_backup/1_421_955645578.dbf
    
    463     1    422     A 12-DEC-17
            Name: /home/alex/db_backup/1_422_955645578.dbf
    
    464     1    423     A 12-DEC-17
            Name: /home/alex/db_backup/1_423_955645578.dbf
    
    465     1    424     A 12-DEC-17
            Name: /home/alex/db_backup/1_424_955645578.dbf
    
    466     1    425     A 12-DEC-17
            Name: /home/alex/db_backup/1_425_955645578.dbf
    
    467     1    426     A 13-DEC-17
            Name: /home/alex/db_backup/1_426_955645578.dbf
    
    468     1    427     A 13-DEC-17
            Name: /home/alex/db_backup/1_427_955645578.dbf
    
    469     1    428     A 13-DEC-17
            Name: /home/alex/db_backup/1_428_955645578.dbf
    
    470     1    429     A 13-DEC-17
            Name: /home/alex/db_backup/1_429_955645578.dbf
    
    471     1    430     A 13-DEC-17
            Name: /home/alex/db_backup/1_430_955645578.dbf
    
    472     1    431     A 13-DEC-17
            Name: /home/alex/db_backup/1_431_955645578.dbf
    
    473     1    432     A 14-DEC-17
            Name: /home/alex/db_backup/1_432_955645578.dbf
    
    474     1    433     A 14-DEC-17
            Name: /home/alex/db_backup/1_433_955645578.dbf
    
    475     1    434     A 14-DEC-17
            Name: /home/alex/db_backup/1_434_955645578.dbf
    
    476     1    435     A 14-DEC-17
            Name: /home/alex/db_backup/1_435_955645578.dbf
    
    
    RMAN> 
    


    下面开始实战的操作,使用crontab定时执行一个RMAN脚本,实现如下备份策略

    1、每周的周六晚上12:58执行一个全备操作

    2、周日和周一到周五每晚执行一个1级备份

    3、全备保留一个月,超过一个月的全备和1级备份全都删除

    4、归档日志保留最近七天的,七天前的归档日志全部删除

    这样的条件就会产生以下的效果

    1、我可以恢复从现在到七天前这段时间任意时刻的数据库状态

    2、比七天前还早的时间我可以恢复到任意某天晚上24:00的状态

    3、一个月之前的备份都被删除,不占用磁盘空间


    根据上面的要求,我们应该写三个脚本。

    第一个是周六晚上用来执行全备的。

    第二个是周日到周五晚上执行1级增备的。

    第三个是每天晚上执行用来删除归档日志的。


    首先我们把恢复时间窗口设置为30天

    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;


    第一个脚本我们叫它backup0.sh,内容如下

    connect target sys/123456@127.0.0.1/haha
    run
    {
    allocate channel d1 type disk;
    backup incremental level 0 format '/oradata/backup/orcl_full_%U' database include current controlfile;
    delete noprompt obsolete device type disk;
    sql 'alter system archive log current';
    crosscheck backup;
    delete noprompt expired backup;
    release channel d1;
    }

    第一行是连接数据库RMAN,此处需要写数据库的密码。

    第二行开始是RMAN的run块,里面写相关的命令,注意delete命令都需要添加noprompt来实现无提示的自动运行效果。

    allocate channel d1 type disk;是自动脚本所需的文件通道(手动执行RMAN命令并不需要这个),type disk是指该通道是读写硬盘而非带库的。

    run块中的第二行就是最普通的0级备份的语句

    run块中的第三行是删除被标记为过期的备份文件,上面有讲到过,就是删除30天之前最后一个全备和那次全备之前的所有1级2级备份和归档日志。这里一定要注意,是30天之前的最后一个全备,可能是31,32,33,34,35,36,37天前的某个全备。

    run块中第四行是重做归档日志文件,让备份完毕后新的归档日志以当前的全备为起始,防止产生归档日志断续的问题,也可以在恢复的时候自动寻找最近的一次全备然后开始逐条sql恢复。

    run块中第五行是检查备份文件物理上有没有缺失的状况,可以不执行这条语句。这条语句的主要作用是如果你从磁盘上物理删除了之前的某次备份,可以通过这个检查出来

    run块中第六行是删除上面那一条发现的,物理磁盘已经丢失但控制文件还记录的备份文件,删除物理已经丢失的文件可以在回滚数据库的时候减少无意义的报错。

    run块中第七行是释放通道,停止文件的读写。


    第二个脚本我们叫它backup1.sh,内容如下

    connect target sys/123456@127.0.0.1/haha
    run
    {
    allocate channel d1 type disk;
    backup incremental level 1 format '/oradata/backup/orcl_level1_%U' database;
    delete noprompt obsolete device type disk;
    crosscheck backup;
    delete noprompt expired backup;
    release channel d1;
    }

    第三个脚本我们叫它delarch.sh,内容如下,sysdate-8而不是-7是因为每7天备份一次,防止出现断档

    connect target sys/oracle@127.0.0.1/ecology
    run
    {
    allocate channel d2 type disk;
    DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-8';
    release channel d2;
    }


    另外在执行这些脚本之前还需要定义相关的环境变量,并记录日志,如下

    rmanbackup0.sh

    #!/bin/sh
    DATE=`date +%Y-%m-%d`
    export ORACLE_HOME=/u01/oracle/
    export ORACLE_BASE=/u01/
    export ORACLE_SID=haha
    $ORACLE_HOME/bin/rman cmdfile /oradata/scripts/backup0.sh log=/oradata/scripts/log_rman_0_$DATE

    rmanbackup1.sh

    #!/bin/sh
    DATE=`date +%Y-%m-%d`
    export ORACLE_HOME=/u01/oracle/
    export ORACLE_BASE=/u01/
    export ORACLE_SID=haha
    $ORACLE_HOME/bin/rman cmdfile /oradata/scripts/backup1.sh log=/oradata/scripts/log_rman_1_$DATE
    

    rmandelarch.sh

    #!/bin/sh
    DATE=`date +%Y-%m-%d`
    export ORACLE_HOME=/u01/oracle/
    export ORACLE_BASE=/u01/
    export ORACLE_SID=haha
    $ORACLE_HOME/bin/rman cmdfile /oradata/scripts/delarch.sh log=/oradata/scripts/log_rman_arch_$DATE
    

    然后我们修改数据库用户的crontab,如下

    [root@localhost ~]# su - oracle
    [oracle@localhost ~]$ crontab -e 

    59 23 * * 6 /oradata/scripts/rmanbackup0.sh
    59 23 * * 0-5 /oradata/scripts/rmanbackup1.sh
    59 23 * * * /oradata/scripts/rmandelarch.sh


    自动执行过之后,会产生相应的log,便于排查错误,比如下面是周六备份的日志

    [oracle@localhost scripts]$ cat ./log_rman_0_2017-12-15 
    
    Recovery Manager: Release 12.2.0.1.0 - Production on Fri Dec 15 17:44:54 2017
    
    Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
    
    RMAN> connect target *
    2> run
    3> {
    4> allocate channel d1 type disk;
    5> backup incremental level 0 format '/oradata/backup/orcl_full_%U' database include current controlfile;
    6> delete noprompt obsolete device type disk;
    7> sql 'alter system archive log current';
    8> crosscheck backup;
    9> delete noprompt expired backup;
    10> release channel d1;
    11> }
    12> 
    13> 
    connected to target database: ECOLOGY (DBID=4155752618)
    
    using target database control file instead of recovery catalog
    allocated channel: d1
    channel d1: SID=1284 device type=DISK
    
    Starting backup at 15-DEC-17
    channel d1: starting incremental level 0 datafile backup set
    channel d1: specifying datafile(s) in backup set
    input datafile file number=00005 name=/oradata/ecology/cocology.dbf
    input datafile file number=00001 name=/oradata/ecology/system01.dbf
    input datafile file number=00004 name=/oradata/ecology/undotbs01.dbf
    input datafile file number=00003 name=/oradata/ecology/sysaux01.dbf
    input datafile file number=00007 name=/oradata/ecology/users01.dbf
    channel d1: starting piece 1 at 15-DEC-17
    channel d1: finished piece 1 at 15-DEC-17
    piece handle=/oradata/backup/orcl_full_0qsm6t0o_1_1 tag=TAG20171215T174456 comment=NONE
    channel d1: backup set complete, elapsed time: 00:00:15
    channel d1: starting incremental level 0 datafile backup set
    channel d1: specifying datafile(s) in backup set
    including current control file in backup set
    channel d1: starting piece 1 at 15-DEC-17
    channel d1: finished piece 1 at 15-DEC-17
    piece handle=/oradata/backup/orcl_full_0rsm6t17_1_1 tag=TAG20171215T174456 comment=NONE
    channel d1: backup set complete, elapsed time: 00:00:01
    Finished backup at 15-DEC-17
    
    Starting Control File and SPFILE Autobackup at 15-DEC-17
    piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_15/o1_mf_s_962819114_f376fb5r_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 15-DEC-17
    
    RMAN retention policy will be applied to the command
    RMAN retention policy is set to recovery window of 30 days
    no obsolete backups found
    
    sql statement: alter system archive log current
    
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_12/o1_mf_s_962528576_f2ybp0d8_.bkp RECID=1 STAMP=962528576
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_13/o1_mf_s_962613761_f30xw1gh_.bkp RECID=4 STAMP=962613761
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_13/o1_mf_s_962619538_f313jld3_.bkp RECID=7 STAMP=962619538
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_13/o1_mf_s_962619541_f313jp0s_.bkp RECID=8 STAMP=962619542
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_13/o1_mf_s_962648350_f31znyjf_.bkp RECID=10 STAMP=962648350
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_13/o1_mf_s_962668761_f32mlsdx_.bkp RECID=13 STAMP=962668761
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_13/o1_mf_s_962668767_f32mlz5k_.bkp RECID=14 STAMP=962668767
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_14/o1_mf_s_962755161_f357ysxs_.bkp RECID=17 STAMP=962755161
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_14/o1_mf_s_962755166_f357yymr_.bkp RECID=18 STAMP=962755166
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_15/o1_mf_s_962818627_f375y3v1_.bkp RECID=20 STAMP=962818627
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/backup/orcl_full_0lsm6sn7_1_1 RECID=21 STAMP=962818791
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_15/o1_mf_s_962818808_f3763rvd_.bkp RECID=23 STAMP=962818808
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/backup/orcl_level1_0osm6suo_1_1 RECID=24 STAMP=962819032
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_15/o1_mf_s_962819035_f376bvyb_.bkp RECID=25 STAMP=962819035
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/backup/orcl_full_0qsm6t0o_1_1 RECID=26 STAMP=962819096
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/backup/orcl_full_0rsm6t17_1_1 RECID=27 STAMP=962819112
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/oradata/fast_recovery_area/ecology/ECOLOGY/autobackup/2017_12_15/o1_mf_s_962819114_f376fb5r_.bkp RECID=28 STAMP=962819114
    Crosschecked 17 objects
    
    
    specification does not match any backup in the repository
    
    released channel: d1
    
    Recovery Manager complete.
    








    展开全文
  • Oracle数据库自动备份

    2021-05-08 19:10:12
    Oracle提示TNS:无监听程序的解决办法1.首先看一下服务中的以下的两个服务是否开启: 2.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora文件 SID_NAME = CLRExtProc 改为 SID_NAME = orcl ...

    Oracle提示TNS:无监听程序的解决办法

    1.首先看一下服务中的以下的两个服务是否开启:

    6f3a52ef8d5789b6bf0288ce3cef090b.png

    2.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora文件

    d1bea5e203685e2232b40b69e8b456e2.png

    SID_NAME = CLRExtProc 改为 SID_NAME = orcl (orcl是实例名)

    HOST = localhost 改为 HOST = 192.168.1.111(远程连接改为IP地址)本机就是改为127.0.0.1

    3.修改..\app\admin\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora文件

    73dcc2015c2f93101b18f807b19f336e.png

    两处 HOST = localhost 改为 HOST = 192.168.1.111 (远程连接为改为IP地址)

    SID = CLRExtProc 改为 SID = orcl

    4.好了,重启oracle的上边的两个服务就OK了。

    Oracle自动备份脚本文件

    @echo off

    Set OrclSid=orcl

    Set ExpUser=scott

    Set ExpPass=123456

    Set FileDir=F:\oracle-bak

    Set SysDate=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%

    Set FileName=%ExpUser%~%OrclSid%_%SysDate%

    @echo * * * * * * * * * * * * * * * * * * * *

    @echo * * * * * * * * * * * * * * * * * * * *

    @echo * * * * * ORACLE 数据库备份 * * * * * *

    @echo * * * * * * * * * * * * * * * * * * * *

    @echo * * * * * * * * * * * * * * * * * * * *

    @echo 服务名 = %OrclSid%

    @echo 用户名 = %ExpUser%

    @echo 密 码 = %ExpPass%

    @echo 目 录 = %FileDir%

    @echo 时 间 = %SysDate%

    @echo 文件名 = %FileName%.rar

    @echo 开始备份数据库...

    exp %ExpUser%/%ExpPass%@%OrclSid% file=%FileDir%/%FileName%.dmp log=%FileDir%/%FileName%.log

    @echo 数据库备份完成!

    @echo 退出cmd

    ~exit

    ad89043e77bbb6ffa544c45ddaf8e4a2.png

    Windows设置定时任务执行Oracle数据库备份的脚本(bat)文件

    百度:如何用windows任务计划程序设置程序定时任务

    展开全文
  • ORACLE数据库自动备份
  • Oracle 12c 使用跨平台增量备份来减少传输表空间的停机时间 Oracle 12c 使用跨平台增量备份来减少传输表空间的停机时间
  • NULL 博文链接:https://jimmy9495.iteye.com/blog/1781679
  • 为避免数据丢失或错误,对数据库数据的定时备份oracle导出脚本 及windows 任务 设置的说明文档
  • 当然要备份和恢复数据库,我们一定要先安装数据库,这里oracle 12c在安装的时候有个需要注意的地方,如下图 注意到那个“创建为容器数据库”的选项了吗?那个钩一定取消。本次的恢复和备份都在非容...

    oracle用得不是很多,但总体用起来也没啥问题,因为大多数时候就是增删查改的操作,这些跟其他数据库也没多大区别。不过当遇到备份和恢复的时候,才发现区别是比较大的,因此留下这篇文章做一个记录,方便以后查阅。

    当然要备份和恢复数据库,我们一定要先安装数据库,这里oracle 12c在安装的时候有个需要注意的地方,如下图

    0629c3afcc47769bd75614d0d5f01875.png

    注意到那个“创建为容器数据库”的选项了吗?那个钩一定取消。本次的恢复和备份都在非容器数据库中进行了,如果你安装的时候已经创建为容器数据库了,本方法可能就不适合你做备份和恢复了。

    好了说了那么多就直接进入正题了(啰嗦一下,所有备份和恢复都是用sys账号操作)

    先说备份操作,如下

    1、  首先通过

    select*fromdba_directories;

    查询备份的路径

    1a7dfd32b7206480870fdcc78f56acca.png

    DATA_PUMP_DIR就是备份文件所在位置

    2、select*fromdba_tablespaces;查询表空间,恢复端与备份端表空间要保持一致

    3、  通过以下命令,直接在命令行界面运行完成备份

    expdp 'sys/密码 as sysdba'  directory=DATA_PUMP_DIR schemas=表空间所在用户 dumpfile=备份文件名.dmp logfile=日志文件名.log再来说下恢复,因为我不是在本机恢复,而是在另一台机器恢复,所以过程要繁琐一点。如果你只是在本机执行备份和恢复操作,那么只需用到恢复操作的最后一步即可。

    1、首先通过

    select*fromdba_directories;

    查询备份的路径

    c0520f971d97662f963a4c463d684811.png

    将备份文件放到DATA_PUMP_DIR路径下

    2、创建表空间

    createtable space 表空间名称(与你备份时的表空间名称保持一致)

    datafile'D:\database\oracle_data\表空间名称.dbf'–设置表空间文件位置

    size2048m  --设置初始大小

    autoextendon

    next50m

    extentmanagementlocal;

    3、创建用户

    create user 用户名 identified by 密码(用户也跟备份时的所在用户名保持一致)

    default tablespace 表空间名称

    temporary tablespace temp;

    4、给用户授权

    GRANT

    CREATE SESSION,

    CREATE ANY SEQUENCE,

    CREATE ANY TABLE,

    CREATE ANY VIEW ,

    CREATE ANY INDEX,

    CREATE ANY PROCEDURE,

    CREATE ANY DIRECTORY,

    SELECT ANY TABLE,

    SELECT ANY DICTIONARY,

    INSERT ANY TABLE,

    UPDATE ANY TABLE,

    DEBUG ANY PROCEDURE

    to 用户名(及刚创建的这个用户);

    5、执行恢复(在运行中cmd命令调出命令行界面,直接在命令行中执行以下语句)

    impdp 'sys/密码 as sysdba' directory=DATA_PUMP_DIR schemas=表空间所在用户 dumpfile= 备份文件名.dmp

    好了这样就完成了oracle 12c的备份与恢复

    展开全文
  • Oracle 12c简介 11.1 Oracle 12c简介 11.2 Oracle 12c产品系列 21.3 Oracle 12c新特性 21.3.1 插接式数据库PDB 21.3.2 高可用性 41.3.3 XML DB 61.3.4 In-Memory数据库内存选件 61.3.5 Oracle JSON文档存储 71.3....
  • 实现oracle 数据库自动备份+保留最新的7天数据+另一机器拷贝备份的脚本
  • 相信为数不少的系统管理员每天都在做着同一样的工作——对数据进行备份。一旦哪一天疏忽了, 而这一天系统又恰恰发生了故障,...本文结合实践 经验,谈一谈UNIX环境下Oracle数据库自动备份,以起到抛砖引玉的作用。
  • 此版本的数据库适合64位的Linux系统安装使用,软件语言中文版本,甲骨文官方原版数据库,有需要此版本数据库的各位用户敬请下载安装使用。
  • Linux下Oracle数据库自动备份迁移 通过linux下Crontab实现定时任务,结合oracle自动备份脚本实现oracle数据库备份操作,结合linux下Scp服务实现备份文件的异地存储,从而减少了人为备份的繁琐工作和服务器损坏造成...
  • 主要介绍了 Oracle12c数据库向11g导进的方法的相关资料,需要的朋友可以参考下
  • Centos 7.2系统安装Oracle12C数据库

    千次阅读 2020-01-13 17:42:35
    数据库版本:Oracle 12C for x86 (64位) 二、安装前准备工作 1.系统支持 Oracle 12 c:支持以下Linux版本(都是64位系,没有32位): Supported Oracle Linux 7 and Red Hat Enterprise Linux 7 Distributions for...
  • oracle不太了解,只会简单的startup、shutdown、lsnrctl start等命令,不喜欢oracle,不好用请按实际情况修改username、password、ORACLE_SID、/PATH/TO/DIR等expdp全备注意expdp只能全备,不能进行增量备份创建...
  • oracle 12c pdb数据库全库备份

    千次阅读 2019-06-13 10:08:33
    oracle 帐户下: expdp dpda/dpda@pdborcl DIRECTORY=dump_dir3 dumpfile=bigdata.dmp logfile=bigdata.log schemas=bigdata 直接执行会报错如下: UDE-12154: operation generated ORACLE error 12154 ORA-12154...
  • 优点:提高整合度(可插拔数据库),多租户架构,节省硬件资源,管理共享资源(优先级),简化升级(提供数据库灵活的打补丁),将多个数据库作为一个管理(统一备份多个数据库-在可插拔式数据库,支持全部备份,也支持选择...
  • 本文对exp/crontab方式对Oracle数据库自动备份作了一些尝 试,并不保证在所有的环境下皆能正常运行。
  • 查询SCN的方法 SQL> select current_scn from v$database 2 union 3 select current_scn from v$database; CURRENT_SCN 3126705 3126706 SQL> select current_scn from v$database 2 un...
  • 单元1 登录Oracle数据库与试用Oracle的常用工具 1教学导航 1前导知识―心中有数 2操作实战―循序渐进 101.1 查看与启动Oracle的服务 10【任务1-1】 查看与启动Oracle的相关服务 101.2 登录Oracle数据库与查看Oracle...
  • 安装环境及版本要求 系统:Windows server 2012 版本:12.1 硬件要求:CPU:16H 内存:64G 硬盘:100G 安装步骤 1.将下载好的安装包解压,双击“setup.exe” ...8.这一步创建数据库时,输入你想创建的数据库
  • 操作系统:Linux数据库oracle9.2.0.41、数据库设置为归档方式2、数据库备份脚本db_full_backup.sh :数据库全备脚本db_l0_backup.sh :数据库0级备份脚本db_l1_backup.sh :数据库1级备份脚本ftp.sh :数据FTP...
  • Linux 环境下Oracle 12C数据库卸载

    千次阅读 2018-04-30 21:15:34
    这里,我的数据库版本是oracle 12cR2,为了装RAC,我准备把当前的数据库卸载了,基础的系统和用户保留着,这样不用再重新装系统了,没意思,哈哈哈~个人比较懒,请体谅~1、首先关闭数据库SQL> shutdown ...
  • Oracle 12c环境下查询,alert日志并不在bdump目录下,看到网上和书上都写着可以通过初始化参数background_dump_dest来查看alter日志路径,还说警告日志文件的缺省位置是%Oracle_base%\admin\orcl\bdump,其实12c中...
  • 需要更多相关资料可以联系Q2748165793第一讲:介绍备份和恢复相关操作备份和恢复的解决方案第二讲:开始RMAN操作和RMAN体系架构RMAN ChannelsRMAN备份和恢复相关操作配置RMAN备份环境配置备份保留策略client工具使用...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 4,080
精华内容 1,632
关键字:

oracle12c数据库自动备份