Oracle不完全恢复后,是否会导致历史归档文件无法delete?

2024年 2月 19日 47.3k 0

技术群里有朋友反馈由于数据库执行过不完全恢复,导致不完全恢复前的归档文件无法通过delete方式进行删除。

通过测试,发现两者并没有直接联系,在执行基于时间点的不完全恢复后,通过delete archivelog all;命令,仍然可以删除不完全恢复前的归档文件。

测试过程如下:

数据库版本:11.2.0.4.0

1 启动归档

    sqlplus as sysdba
    alter system set log_archive_dest_1='location=/arch';
    alter system set log_archive_format = "cjc_%t_%s_%r.arc" scope=spfile;
    shutdown immediate;
    startup mount;
    alter database archivelog;
    alter database open;
    archive log list;

    2 创建测试数据

      select name from v$dbfile;
      create tablespace cjc datafile '/oradata/cjc/cjctbs01.dbf' size 1M;
      create user cjc identified by "a" default tablespace cjc;
      grant connect,resource to cjc;
      conn cjc/a
      create table t1(id number,ttime varchar2(100));
      CREATE OR REPLACE PROCEDURE t_pro is
      begin
      for i in 1 .. 10 loop
      insert into t1 values (i, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
      commit;
      end loop;
      end;
      /

      生成测试数据,切换归档日志

        exec t_pro();

        查看

          select * from t1;
          ID TTIME
          ---------- -----------------------------------
          1 2024-02-18 14:59:32
          2 2024-02-18 14:59:32
          3 2024-02-18 14:59:32
          4 2024-02-18 14:59:32
          5 2024-02-18 14:59:32
          6 2024-02-18 14:59:32
          7 2024-02-18 14:59:32
          8 2024-02-18 14:59:32
          9 2024-02-18 14:59:32
          10 2024-02-18 14:59:32


          10 rows selected

          切换归档

            alter system switch logfile;

            查看归档

              SQL> conn as sysdba
              Connected.
              SQL> archive log list;
              Database log mode Archive Mode
              Automatic archival Enabled
              Archive destination arch
              Oldest online log sequence 5
              Next log sequence to archive 7
              Current log sequence 7

                [oracle@cjc-db-02 ~]$ cd arch/
                [oracle@cjc-db-02 arch]$ ls -lrth
                total 7.8M
                -rw-r----- 1 oracle oinstall 7.5M Feb 18 14:35 cjc_1_5_1161266757.arc
                -rw-r----- 1 oracle oinstall 317K Feb 18 15:00 cjc_1_6_1161266757.arc

                  rman target
                  RMAN> list archivelog all;
                  using target database control file instead of recovery catalog
                  List of Archived Log Copies for database with db_unique_name CJC
                  =====================================================================


                  Key Thrd Seq S Low Time
                  ------- ---- ------- - ---------
                  1 1 5 A 18-FEB-24
                  Name: /arch/cjc_1_5_1161266757.arc


                  2 1 6 A 18-FEB-24

                  执行rman 0级别全备份

                    vi rmanbak/script/0_rmanbak.sh
                    ###0_rmanbak.sh begin###
                    export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
                    export ORACLE_SID=cjc
                    rman target log=/rmanbak/db/0_rmanbak.log 3> 4> 5> 6> 7> 8> 9>
                    using target database control file instead of recovery catalog
                    allocated channel: ch1
                    channel ch1: SID=143 device type=DISK


                    allocated channel: ch2
                    channel ch2: SID=19 device type=DISK


                    Starting backup at 2024-02-18 15:27:58
                    channel ch1: starting incremental level 0 datafile backup set
                    channel ch1: specifying datafile(s) in backup set
                    input datafile file number=00001 name=/oradata/cjc/system01.dbf
                    input datafile file number=00005 name=/oradata/cjc/cjctbs01.dbf
                    channel ch1: starting piece 1 at 2024-02-18 15:27:58
                    channel ch2: starting incremental level 0 datafile backup set
                    channel ch2: specifying datafile(s) in backup set
                    input datafile file number=00002 name=/oradata/cjc/sysaux01.dbf
                    input datafile file number=00003 name=/oradata/cjc/undotbs01.dbf
                    input datafile file number=00004 name=/oradata/cjc/users01.dbf
                    channel ch2: starting piece 1 at 2024-02-18 15:27:58
                    channel ch2: finished piece 1 at 2024-02-18 15:30:33
                    piece handle=/rmanbak/db/rman_lev0_CJC_20240218_022jf6bu_1_1.bak tag=0_RMANBAK_CJCDB comment=NONE
                    channel ch2: backup set complete, elapsed time: 00:02:40
                    channel ch2: starting incremental level 0 datafile backup set
                    channel ch2: specifying datafile(s) in backup set
                    channel ch1: finished piece 1 at 2024-02-18 15:30:55
                    piece handle=/rmanbak/db/rman_lev0_CJC_20240218_012jf6bu_1_1.bak tag=0_RMANBAK_CJCDB comment=NONE
                    channel ch1: backup set complete, elapsed time: 00:02:57
                    channel ch1: starting incremental level 0 datafile backup set
                    channel ch1: specifying datafile(s) in backup set
                    including current SPFILE in backup set
                    channel ch1: starting piece 1 at 2024-02-18 15:30:55
                    including current control file in backup set
                    channel ch2: starting piece 1 at 2024-02-18 15:30:55
                    channel ch1: finished piece 1 at 2024-02-18 15:30:56
                    piece handle=/rmanbak/db/rman_lev0_CJC_20240218_042jf6hf_1_1.bak tag=0_RMANBAK_CJCDB comment=NONE
                    channel ch1: backup set complete, elapsed time: 00:00:01
                    channel ch2: finished piece 1 at 2024-02-18 15:30:56
                    piece handle=/rmanbak/db/rman_lev0_CJC_20240218_032jf6h5_1_1.bak tag=0_RMANBAK_CJCDB comment=NONE
                    channel ch2: backup set complete, elapsed time: 00:00:01
                    Finished backup at 2024-02-18 15:30:56


                    sql statement: alter system archive log current


                    Starting backup at 2024-02-18 15:30:57
                    current log archived
                    channel ch1: starting archived log backup set
                    channel ch1: specifying archived log(s) in backup set
                    input archived log thread=1 sequence=5 RECID=1 STAMP=1161268547
                    channel ch1: starting piece 1 at 2024-02-18 15:30:58
                    channel ch2: starting archived log backup set
                    channel ch2: specifying archived log(s) in backup set
                    input archived log thread=1 sequence=6 RECID=2 STAMP=1161270039
                    input archived log thread=1 sequence=7 RECID=3 STAMP=1161271857
                    channel ch2: starting piece 1 at 2024-02-18 15:30:58
                    channel ch1: finished piece 1 at 2024-02-18 15:30:59
                    piece handle=/rmanbak/db/arch_CJC_20240218_052jf6hh_1_1.bak tag=ARCH_CJCDB comment=NONE
                    channel ch1: backup set complete, elapsed time: 00:00:01
                    channel ch1: starting archived log backup set
                    channel ch1: specifying archived log(s) in backup set
                    input archived log thread=1 sequence=8 RECID=4 STAMP=1161271857
                    channel ch1: starting piece 1 at 2024-02-18 15:30:59
                    channel ch2: finished piece 1 at 2024-02-18 15:30:59
                    piece handle=/rmanbak/db/arch_CJC_20240218_062jf6hi_1_1.bak tag=ARCH_CJCDB comment=NONE
                    channel ch2: backup set complete, elapsed time: 00:00:01
                    channel ch1: finished piece 1 at 2024-02-18 15:31:00
                    piece handle=/rmanbak/db/arch_CJC_20240218_072jf6hj_1_1.bak tag=ARCH_CJCDB comment=NONE
                    channel ch1: backup set complete, elapsed time: 00:00:01
                    Finished backup at 2024-02-18 15:31:00


                    released channel: ch1


                    released channel: ch2


                    RMAN>


                    Recovery Manager complete.

                    查看备份信息

                    备份文件

                      [oracle@cjc-db-02 trace]$ cd rmanbak/db/
                      [oracle@cjc-db-02 db]$ ls -lrth
                      total 1.1G
                      -rw-r----- 1 oracle oinstall 445M Feb 18 15:29 rman_lev0_CJC_20240218_022jf6bu_1_1.bak
                      -rw-r----- 1 oracle oinstall 635M Feb 18 15:30 rman_lev0_CJC_20240218_012jf6bu_1_1.bak
                      -rw-r----- 1 oracle oinstall 96K Feb 18 15:30 rman_lev0_CJC_20240218_042jf6hf_1_1.bak
                      -rw-r----- 1 oracle oinstall 9.4M Feb 18 15:30 rman_lev0_CJC_20240218_032jf6h5_1_1.bak
                      -rw-r----- 1 oracle oinstall 7.5M Feb 18 15:30 arch_CJC_20240218_052jf6hh_1_1.bak
                      -rw-r----- 1 oracle oinstall 3.2M Feb 18 15:30 arch_CJC_20240218_062jf6hi_1_1.bak
                      -rw-r----- 1 oracle oinstall 2.5K Feb 18 15:30 arch_CJC_20240218_072jf6hj_1_1.bak
                      -rw-r--r-- 1 oracle oinstall 3.9K Feb 18 15:31 0_rmanbak.log

                      备份信息

                        RMAN> list backup summary;


                        List of Backups
                        ===============
                        Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
                        ------- -- -- - ----------- --------------- ------- ------- ---------- ---
                        1 B 0 A DISK 18-FEB-24 1 1 NO 0_RMANBAK_CJCDB
                        2 B 0 A DISK 18-FEB-24 1 1 NO 0_RMANBAK_CJCDB
                        3 B 0 A DISK 18-FEB-24 1 1 NO 0_RMANBAK_CJCDB
                        4 B 0 A DISK 18-FEB-24 1 1 NO 0_RMANBAK_CJCDB
                        5 B A A DISK 18-FEB-24 1 1 NO ARCH_CJCDB
                        6 B A A DISK 18-FEB-24 1 1 NO ARCH_CJCDB
                        7 B A A DISK 18-FEB-24 1 1 NO ARCH_CJCDB

                          RMAN> list backup;


                          using target database control file instead of recovery catalog


                          List of Backup Sets
                          ===================


                          BS Key Type LV Size Device Type Elapsed Time Completion Time
                          ------- ---- -- ---------- ----------- ------------ ---------------
                          1 Incr 0 444.38M DISK 00:02:14 18-FEB-24
                          BP Key: 1 Status: AVAILABLE Compressed: NO Tag: 0_RMANBAK_CJCDB
                          Piece Name: /rmanbak/db/rman_lev0_CJC_20240218_022jf6bu_1_1.bak
                          List of Datafiles in backup set 1
                          File LV Type Ckp SCN Ckp Time Name
                          ---- -- ---- ---------- --------- ----
                          2 0 Incr 967443 18-FEB-24 oradata/cjc/sysaux01.dbf
                          3 0 Incr 967443 18-FEB-24 oradata/cjc/undotbs01.dbf
                          4 0 Incr 967443 18-FEB-24 oradata/cjc/users01.dbf


                          BS Key Type LV Size Device Type Elapsed Time Completion Time
                          ------- ---- -- ---------- ----------- ------------ ---------------
                          2 Incr 0 634.34M DISK 00:02:50 18-FEB-24
                          BP Key: 2 Status: AVAILABLE Compressed: NO Tag: 0_RMANBAK_CJCDB
                          Piece Name: /rmanbak/db/rman_lev0_CJC_20240218_012jf6bu_1_1.bak
                          List of Datafiles in backup set 2
                          File LV Type Ckp SCN Ckp Time Name
                          ---- -- ---- ---------- --------- ----
                          1 0 Incr 967442 18-FEB-24 oradata/cjc/system01.dbf
                          5 0 Incr 967442 18-FEB-24 oradata/cjc/cjctbs01.dbf




                          BS Key Type LV Size Device Type Elapsed Time Completion Time
                          ------- ---- -- ---------- ----------- ------------ ---------------
                          3 Incr 0 80.00K DISK 00:00:00 18-FEB-24
                          BP Key: 3 Status: AVAILABLE Compressed: NO Tag: 0_RMANBAK_CJCDB
                          Piece Name: /rmanbak/db/rman_lev0_CJC_20240218_042jf6hf_1_1.bak
                          SPFILE Included: Modification time: 18-FEB-24
                          SPFILE db_unique_name: CJC




                          BS Key Type LV Size Device Type Elapsed Time Completion Time
                          ------- ---- -- ---------- ----------- ------------ ---------------
                          4 Incr 0 9.33M DISK 00:00:11 18-FEB-24
                          BP Key: 4 Status: AVAILABLE Compressed: NO Tag: 0_RMANBAK_CJCDB
                          Piece Name: /rmanbak/db/rman_lev0_CJC_20240218_032jf6h5_1_1.bak
                          Control File Included: Ckp SCN: 967569 Ckp time: 18-FEB-24




                          BS Key Size Device Type Elapsed Time Completion Time
                          ------- ---------- ----------- ------------ ---------------
                          5 7.40M DISK 00:00:01 18-FEB-24
                          BP Key: 5 Status: AVAILABLE Compressed: NO Tag: ARCH_CJCDB
                          Piece Name: /rmanbak/db/arch_CJC_20240218_052jf6hh_1_1.bak




                          List of Archived Logs in backup set 5
                          Thrd Seq Low SCN Low Time Next SCN Next Time
                          ---- ------- ---------- --------- ---------- ---------
                          1 5 961747 18-FEB-24 965322 18-FEB-24




                          BS Key Size Device Type Elapsed Time Completion Time
                          ------- ---------- ----------- ------------ ---------------
                          6 3.12M DISK 00:00:00 18-FEB-24
                          BP Key: 6 Status: AVAILABLE Compressed: NO Tag: ARCH_CJCDB
                          Piece Name: /rmanbak/db/arch_CJC_20240218_062jf6hi_1_1.bak




                          List of Archived Logs in backup set 6
                          Thrd Seq Low SCN Low Time Next SCN Next Time
                          ---- ------- ---------- --------- ---------- ---------
                          1 6 965322 18-FEB-24 966168 18-FEB-24
                          1 7 966168 18-FEB-24 967583 18-FEB-24




                          BS Key Size Device Type Elapsed Time Completion Time
                          ------- ---------- ----------- ------------ ---------------
                          7 2.00K DISK 00:00:00 18-FEB-24
                          BP Key: 7 Status: AVAILABLE Compressed: NO Tag: ARCH_CJCDB
                          Piece Name: /rmanbak/db/arch_CJC_20240218_072jf6hj_1_1.bak




                          List of Archived Logs in backup set 7
                          Thrd Seq Low SCN Low Time Next SCN Next Time
                          ---- ------- ---------- --------- ---------- ---------
                          1 8 967583 18-FEB-24 967591 18-FEB-24

                          模拟误操作

                            conn cjc/a
                            SQL> delete from t1;
                            10 rows deleted.
                            SQL> commit;
                            Commit complete.

                              ---0 数据丢失
                              select count(*) from t1;
                              ---10 闪回查询
                              select count(*) from t1 as of timestamp (systimestamp - interval '30' minute);

                              记录时间

                                [oracle@cjc-db-02 admin]$ date
                                Sun Feb 18 15:35:21 CST 2024

                                执行不完全恢复

                                  SQL> shutdown immediate
                                  SQL> startup mount
                                  rman target
                                  run{
                                  set until time "to_date('20240218 15:33:00','yyyymmdd hh24:mi:ss')";
                                  restore database;
                                  recover database;
                                  }


                                  RMAN> ALTER DATABASE OPEN RESETLOGS;

                                  对应告警日志 alert_cjc.log 如下:

                                    Sun Feb 18 16:08:42 2024
                                    Full restore complete of datafile 4 oradata/cjc/users01.dbf. Elapsed time: 0:00:00
                                    checkpoint is 967443
                                    last deallocation scn is 3
                                    Full restore complete of datafile 3 oradata/cjc/undotbs01.dbf. Elapsed time: 0:00:04
                                    checkpoint is 967443
                                    last deallocation scn is 966203
                                    Sun Feb 18 16:09:15 2024
                                    Full restore complete of datafile 2 oradata/cjc/sysaux01.dbf. Elapsed time: 0:00:26
                                    checkpoint is 967443
                                    last deallocation scn is 964071
                                    Full restore complete of datafile 5 oradata/cjc/cjctbs01.dbf. Elapsed time: 0:00:00
                                    checkpoint is 967442
                                    last deallocation scn is 925704
                                    Sun Feb 18 16:09:51 2024
                                    Full restore complete of datafile 1 oradata/cjc/system01.dbf. Elapsed time: 0:00:30
                                    checkpoint is 967442
                                    last deallocation scn is 963325
                                    Sun Feb 18 16:09:54 2024
                                    alter database recover datafile list clear
                                    Completed: alter database recover datafile list clear
                                    alter database recover datafile list
                                    1 , 2 , 3 , 4 , 5
                                    Completed: alter database recover datafile list
                                    1 , 2 , 3 , 4 , 5
                                    alter database recover if needed
                                    start until time 'FEB 18 2024 15:33:00'
                                    Media Recovery Start
                                    started logmerger process
                                    Parallel Media Recovery started with 2 slaves
                                    Sun Feb 18 16:09:56 2024
                                    Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
                                    Mem# 0: oradata/cjc/redo01.log
                                    Recovery of Online Redo Log: Thread 1 Group 2 Seq 8 Reading mem 0
                                    Mem# 0: oradata/cjc/redo02.log
                                    Recovery of Online Redo Log: Thread 1 Group 3 Seq 9 Reading mem 0
                                    Mem# 0: oradata/cjc/redo03.log
                                    Incomplete Recovery applied until change 967691 time 02/18/2024 15:33:48
                                    Media Recovery Complete (cjc)
                                    Completed: alter database recover if needed
                                    start until time 'FEB 18 2024 15:33:00'

                                    对应rman恢复日志如下:

                                      executing command: SET until clause


                                      Starting restore at 18-FEB-24
                                      allocated channel: ORA_DISK_1
                                      channel ORA_DISK_1: SID=10 device type=DISK


                                      channel ORA_DISK_1: starting datafile backup set restore
                                      channel ORA_DISK_1: specifying datafile(s) to restore from backup set
                                      channel ORA_DISK_1: restoring datafile 00002 to oradata/cjc/sysaux01.dbf
                                      channel ORA_DISK_1: restoring datafile 00003 to oradata/cjc/undotbs01.dbf
                                      channel ORA_DISK_1: restoring datafile 00004 to oradata/cjc/users01.dbf
                                      channel ORA_DISK_1: reading from backup piece rmanbak/db/rman_lev0_CJC_20240218_022jf6bu_1_1.bak
                                      channel ORA_DISK_1: piece handle=/rmanbak/db/rman_lev0_CJC_20240218_022jf6bu_1_1.bak tag=0_RMANBAK_CJCDB
                                      channel ORA_DISK_1: restored backup piece 1
                                      channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
                                      channel ORA_DISK_1: starting datafile backup set restore
                                      channel ORA_DISK_1: specifying datafile(s) to restore from backup set
                                      channel ORA_DISK_1: restoring datafile 00001 to oradata/cjc/system01.dbf
                                      channel ORA_DISK_1: restoring datafile 00005 to oradata/cjc/cjctbs01.dbf
                                      channel ORA_DISK_1: reading from backup piece rmanbak/db/rman_lev0_CJC_20240218_012jf6bu_1_1.bak
                                      channel ORA_DISK_1: piece handle=/rmanbak/db/rman_lev0_CJC_20240218_012jf6bu_1_1.bak tag=0_RMANBAK_CJCDB
                                      channel ORA_DISK_1: restored backup piece 1
                                      channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
                                      Finished restore at 18-FEB-24


                                      Starting recover at 18-FEB-24
                                      using channel ORA_DISK_1


                                      starting media recovery
                                      media recovery complete, elapsed time: 00:00:03


                                      Finished recover at 18-FEB-24

                                      执行

                                        SQL> ALTER DATABASE OPEN RESETLOGS;

                                        对应告警日志如下:

                                          Sun Feb 18 16:12:12 2024
                                          ALTER DATABASE OPEN RESETLOGS
                                          RESETLOGS after incomplete recovery UNTIL CHANGE 967691
                                          Archived Log entry 5 added for thread 1 sequence 9 ID 0xe2b32b81 dest 1:
                                          Resetting resetlogs activation ID 3803392897 (0xe2b32b81)
                                          Sun Feb 18 16:12:23 2024
                                          Setting recovery target incarnation to 3
                                          Sun Feb 18 16:12:23 2024
                                          Assigning activation ID 3803418276 (0xe2b38ea4)
                                          LGWR: STARTING ARCH PROCESSES
                                          Sun Feb 18 16:12:23 2024
                                          ARC0 started with pid=23, OS id=13074
                                          ARC0: Archival started
                                          LGWR: STARTING ARCH PROCESSES COMPLETE
                                          ARC0: STARTING ARCH PROCESSES
                                          Sun Feb 18 16:12:23 2024
                                          ARC1 started with pid=24, OS id=13076
                                          Sun Feb 18 16:12:23 2024
                                          ARC2 started with pid=25, OS id=13078
                                          Thread 1 opened at log sequence 1
                                          Current log# 1 seq# 1 mem# 0: oradata/cjc/redo01.log
                                          Successful open of redo thread 1
                                          Sun Feb 18 16:12:23 2024
                                          MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
                                          Sun Feb 18 16:12:23 2024
                                          SMON: enabling cache recovery
                                          Sun Feb 18 16:12:23 2024
                                          ARC3 started with pid=26, OS id=13080
                                          ARC1: Archival started
                                          ARC2: Archival started
                                          ARC1: Becoming the 'no FAL' ARCH
                                          ARC1: Becoming the 'no SRL' ARCH
                                          ARC2: Becoming the heartbeat ARCH
                                          ARC3: Archival started
                                          ARC0: STARTING ARCH PROCESSES COMPLETE
                                          [12672] Successfully onlined Undo Tablespace 2.
                                          Undo initialization finished serial:0 start:9065814 end:9066514 diff:700 (7 seconds)
                                          Dictionary check beginning
                                          Dictionary check complete
                                          Verifying file header compatibility for 11g tablespace encryption..
                                          Verifying 11g file header compatibility for tablespace encryption completed
                                          SMON: enabling tx recovery
                                          Database Characterset is AL32UTF8
                                          No Resource Manager plan active
                                          replication_dependency_tracking turned off (no async multimaster replication found)
                                          Starting background process QMNC
                                          Sun Feb 18 16:12:29 2024
                                          QMNC started with pid=27, OS id=13087
                                          LOGSTDBY: Validating controlfile with logical metadata
                                          LOGSTDBY: Validation complete
                                          Sun Feb 18 16:12:40 2024
                                          Completed: ALTER DATABASE OPEN RESETLOGS
                                          Sun Feb 18 16:12:41 2024
                                          Starting background process CJQ0
                                          Sun Feb 18 16:12:41 2024
                                          CJQ0 started with pid=30, OS id=13113

                                          查看数据,已恢复:

                                            SQL> col ttime for a25
                                            SQL> select * from cjc.t1;


                                            ID TTIME
                                            ---------- -------------------------
                                            1 2024-02-18 14:59:32
                                            2 2024-02-18 14:59:32
                                            3 2024-02-18 14:59:32
                                            4 2024-02-18 14:59:32
                                            5 2024-02-18 14:59:32
                                            6 2024-02-18 14:59:32
                                            7 2024-02-18 14:59:32
                                            8 2024-02-18 14:59:32
                                            9 2024-02-18 14:59:32
                                            10 2024-02-18 14:59:32


                                            10 rows selected.

                                            继续插入数据

                                              SQL> exec t_pro();
                                              SQL> select count(*) from cjc.t1;
                                              COUNT(*)
                                              ----------
                                              20

                                              切换归档日志

                                                alter system switch logfile;
                                                /
                                                /

                                                查看归档

                                                  [oracle@cjc-db-02 cjc]$ ls -lrth arch/
                                                  total 14M
                                                  -rw-r----- 1 oracle oinstall 7.5M Feb 18 14:35 cjc_1_5_1161266757.arc
                                                  -rw-r----- 1 oracle oinstall 317K Feb 18 15:00 cjc_1_6_1161266757.arc
                                                  -rw-r----- 1 oracle oinstall 2.9M Feb 18 15:30 cjc_1_7_1161266757.arc
                                                  -rw-r----- 1 oracle oinstall 1.0K Feb 18 15:30 cjc_1_8_1161266757.arc
                                                  -rw-r----- 1 oracle oinstall 2.8M Feb 18 16:12 cjc_1_9_1161266757.arc
                                                  -rw-r----- 1 oracle oinstall 111K Feb 18 16:16 cjc_1_1_1161274332.arc
                                                  -rw-r----- 1 oracle oinstall 2.0K Feb 18 16:16 cjc_1_2_1161274332.arc
                                                  -rw-r----- 1 oracle oinstall 3.5K Feb 18 16:16 cjc_1_3_1161274332.arc

                                                    RMAN> list archivelog all;


                                                    using target database control file instead of recovery catalog
                                                    List of Archived Log Copies for database with db_unique_name CJC
                                                    =====================================================================




                                                    Key Thrd Seq S Low Time
                                                    ------- ---- ------- - ---------
                                                    1 1 5 A 18-FEB-24
                                                    Name: /arch/cjc_1_5_1161266757.arc




                                                    2 1 6 A 18-FEB-24
                                                    Name: /arch/cjc_1_6_1161266757.arc




                                                    3 1 7 A 18-FEB-24
                                                    Name: /arch/cjc_1_7_1161266757.arc




                                                    4 1 8 A 18-FEB-24
                                                    Name: /arch/cjc_1_8_1161266757.arc




                                                    5 1 9 A 18-FEB-24
                                                    Name: /arch/cjc_1_9_1161266757.arc




                                                    6 1 1 A 18-FEB-24
                                                    Name: /arch/cjc_1_1_1161274332.arc




                                                    7 1 2 A 18-FEB-24
                                                    Name: /arch/cjc_1_2_1161274332.arc




                                                    8 1 3 A 18-FEB-24
                                                    Name: /arch/cjc_1_3_1161274332.arc

                                                      RMAN> list incarnation;


                                                      List of Database Incarnations
                                                      DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
                                                      ------- ------- -------- ---------------- --- ---------- ----------
                                                      1 1 CJC 3803415169 PARENT 1 24-AUG-13
                                                      2 2 CJC 3803415169 PARENT 925702 18-FEB-24
                                                      3 3 CJC 3803415169 CURRENT 967692 18-FEB-24

                                                      其中,不完全恢复过一次,list incatnation输出3条记录,也就是无不完全恢复操作,list incatnation正常输出2条记录。

                                                      关于incarnation的描述

                                                      官网:

                                                        https://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta027.htm#i82460

                                                        If LIST INCARNATION displays n incarnations of a database, then you have reset the online redo logs for this database n-1 times.

                                                        如果LIST INCARNATION显示了一个数据库的n个实例,那么您已经重置了该数据库的联机重做日志n-1次。

                                                        其他:《在rman恢复中incarnation的概念》

                                                          https://www.cnblogs.com/bicewow/p/11205685.html

                                                          从10g开始,incarnation被引入,用于跨越resetlogs进行恢复,由此可见,此概念在rman中可以找到相关使用轨迹。

                                                          Resetlogs命令表示一个数据库逻辑生存期的结束和另一个数据库逻辑生存期的开始,Oracle把这个数据库逻辑生存期称为incarnation;

                                                          每次使用resetlogs打开数据库,就会使incarnation + 1,也就是产生一个新的incarnation;

                                                          如果想要恢复到之前incarnation的scn/time,就需要先恢复到之前的incarnation;

                                                          具体如何通过incarnation执行恢复,可以参考我在15年写的博客:

                                                          《rman 穿越incarnation恢复数据》

                                                            https://blog.itpub.net/29785807/viewspace-1726518/

                                                            查看RESETLOGS_ID

                                                              col name for a35
                                                              select NAME,FIRST_CHANGE#,NEXT_CHANGE#,to_char(RESETLOGS_TIME,'yyyymmdd hh24:mi:ss') RESETLOGS_TIME,RESETLOGS_ID,STATUS from v$archived_log;
                                                              NAME FIRST_CHANGE# NEXT_CHANGE# RESETLOGS_TIME RESETLOGS_ID S
                                                              ----------------------------------- ------------- ------------ ----------------- ------------ -
                                                              /arch/cjc_1_5_1161266757.arc 961747965322 20240218 14:05:57 1161266757 A
                                                              /arch/cjc_1_6_1161266757.arc 965322966168 20240218 14:05:57 1161266757 A
                                                              /arch/cjc_1_7_1161266757.arc 966168967583 20240218 14:05:57 1161266757 A
                                                              /arch/cjc_1_8_1161266757.arc 967583967591 20240218 14:05:57 1161266757 A
                                                              /arch/cjc_1_9_1161266757.arc 967591969722 20240218 14:05:57 1161266757 A
                                                              /arch/cjc_1_1_1161274332.arc 967692968111 20240218 16:12:12 1161274332 A
                                                              /arch/cjc_1_2_1161274332.arc 968111968116 20240218 16:12:12 1161274332 A
                                                              /arch/cjc_1_3_1161274332.arc 968116968121 20240218 16:12:12 1161274332 A




                                                              8 rows selected.

                                                              删除归档日志

                                                                rman target /


                                                                RMAN> delete archivelog all;


                                                                allocated channel: ORA_DISK_1
                                                                channel ORA_DISK_1: SID=21 device type=DISK
                                                                List of Archived Log Copies for database with db_unique_name CJC
                                                                =====================================================================


                                                                Key Thrd Seq S Low Time
                                                                ------- ---- ------- - ---------
                                                                1 1 5 A 18-FEB-24
                                                                Name: /arch/cjc_1_5_1161266757.arc




                                                                2 1 6 A 18-FEB-24
                                                                Name: /arch/cjc_1_6_1161266757.arc




                                                                3 1 7 A 18-FEB-24
                                                                Name: /arch/cjc_1_7_1161266757.arc




                                                                4 1 8 A 18-FEB-24
                                                                Name: /arch/cjc_1_8_1161266757.arc




                                                                5 1 9 A 18-FEB-24
                                                                Name: /arch/cjc_1_9_1161266757.arc




                                                                6 1 1 A 18-FEB-24
                                                                Name: /arch/cjc_1_1_1161274332.arc




                                                                7 1 2 A 18-FEB-24
                                                                Name: /arch/cjc_1_2_1161274332.arc




                                                                8 1 3 A 18-FEB-24
                                                                Name: /arch/cjc_1_3_1161274332.arc




                                                                Do you really want to delete the above objects (enter YES or NO)? y
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_5_1161266757.arc RECID=1 STAMP=1161268547
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_6_1161266757.arc RECID=2 STAMP=1161270039
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_7_1161266757.arc RECID=3 STAMP=1161271857
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_8_1161266757.arc RECID=4 STAMP=1161271857
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_9_1161266757.arc RECID=5 STAMP=1161274333
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_1_1161274332.arc RECID=6 STAMP=1161274570
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_2_1161274332.arc RECID=7 STAMP=1161274572
                                                                deleted archived log
                                                                archived log file name=/arch/cjc_1_3_1161274332.arc RECID=8 STAMP=1161274575
                                                                Deleted 8 objects

                                                                  RMAN> list archivelog all;


                                                                  specification does not match any archived log in the repository

                                                                  可以正常删除,不完全恢复不影响历史归档文件的删除

                                                                    [oracle@cjc-db-02 cjc]$ ls -lrth /arch/
                                                                    total 0
                                                                    [oracle@cjc-db-02 cjc]$

                                                                    ###chenjuchao 20240218###

                                                                    相关文章

                                                                    Oracle如何使用授予和撤销权限的语法和示例
                                                                    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
                                                                    下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
                                                                    社区版oceanbase安装
                                                                    Oracle 导出CSV工具-sqluldr2
                                                                    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

                                                                    发布评论