Oracle21C--使用RMAN备份和恢复容器数据库

原创听见风的声音 2023-01-13

996

2022-08-19 42删除编辑

使用rman备份的前提数据库处于归档模式下，在Oracle 21C中，可插拔数据库和根容器数据库使用同一组在线日志文件和归档日志文件，只要打开了容器数据库的归档模式，它下面的可插拔数据库的归档模式也一起打开了。打开数据库归档模式后，就可以使用rman进行数据库在线备份了。

1 执行数据库全备

使用rman登录数据库

[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ rman target /

查看一下rman现在的配置参数

RMAN> show all;
      
      RMAN configuration parameters for database with db_unique_name ORCL 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 '%F'; # default
      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 '/opt/oracle/dbs/snapcf_orcl.f'; # default

从现有的配置参数来看，控制文件自动备份是默认打开的，这样在进行数据库备份时，也会备份控制文件和参数文件。看一下连接数据库的schema

RMAN> report schema;

      using target database control file instead of recovery catalog
      Report of database schema for database with db_unique_name ORCL

      List of Permanent Datafiles
      ===========================
      File Size(MB) Tablespace           RB segs Datafile Name
      ---- -------- -------------------- ------- ------------------------
      1    1340     SYSTEM               YES     /opt/oracle/oradata/ORCL/system01.dbf
      3    620      SYSAUX               NO      /opt/oracle/oradata/ORCL/sysaux01.dbf
      4    110      UNDOTBS1             YES     /opt/oracle/oradata/ORCL/undotbs01.dbf
      5    280      PDB$SEED:SYSTEM      NO      /opt/oracle/oradata/ORCL/pdbseed/system01.dbf
      6    340      PDB$SEED:SYSAUX      NO      /opt/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
      7    5        USERS                NO      /opt/oracle/oradata/ORCL/users01.dbf
      8    100      PDB$SEED:UNDOTBS1    NO      /opt/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
      10   280      PDB1:SYSTEM          YES     /opt/oracle/oradata/ORCL/pdb1/system01.dbf
      11   350      PDB1:SYSAUX          NO      /opt/oracle/oradata/ORCL/pdb1/sysaux01.dbf
      12   100      PDB1:UNDOTBS1        YES     /opt/oracle/oradata/ORCL/pdb1/undotbs01.dbf
      13   100      PDB1:TBS_TEST        NO      /opt/oracle/oradata/ORCL/pdb1/test01.dbf
      List of Temporary Files
      =======================
      File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
      ---- -------- -------------------- ----------- --------------------
      1    237      TEMP                 32767       /opt/oracle/oradata/ORCL/temp01.dbf
      2    35       PDB$SEED:TEMP        32767       /opt/oracle/oradata/ORCL/pdbseed/temp012022-08-18_17-18-50-054-PM.dbf
      3    35       PDB1:TEMP            32767       /opt/oracle/oradata/ORCL/pdb1/temp012022-08-18_17-18-50-054-PM.dbf

从report schema的输出中可以看到容器数据库，PDB种子数据库、PDB数据库的表空间和数据文件。

执行数据库备份，同时备份归档日志文件。

RMAN> backup database plus archivelog;
      
      --先备份归档日志
      Starting backup at 19-AUG-22
      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=3 RECID=1 STAMP=1113130687
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0115i1m0_1_1_1 tag=TAG20220819T105808 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
      Finished backup at 19-AUG-22
     --备份容器数据库的数据文件 
      Starting backup at 19-AUG-22
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backup set
      channel ORA_DISK_1: specifying datafile(s) in backup set
      input datafile file number=00001 name=/opt/oracle/oradata/ORCL/system01.dbf
      input datafile file number=00003 name=/opt/oracle/oradata/ORCL/sysaux01.dbf
      input datafile file number=00004 name=/opt/oracle/oradata/ORCL/undotbs01.dbf
      input datafile file number=00007 name=/opt/oracle/oradata/ORCL/users01.dbf
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      --备份可插拔数据库pdb1的数据文件
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0215i1m3_2_1_1 tag=TAG20220819T105811 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
      channel ORA_DISK_1: starting full datafile backup set
      channel ORA_DISK_1: specifying datafile(s) in backup set
      input datafile file number=00011 name=/opt/oracle/oradata/ORCL/pdb1/sysaux01.dbf
      input datafile file number=00010 name=/opt/oracle/oradata/ORCL/pdb1/system01.dbf
      input datafile file number=00012 name=/opt/oracle/oradata/ORCL/pdb1/undotbs01.dbf
      input datafile file number=00013 name=/opt/oracle/oradata/ORCL/pdb1/test01.dbf
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      --备份种子数据库的数据文件
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0315i1n6_3_1_1 tag=TAG20220819T105811 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
      channel ORA_DISK_1: starting full datafile backup set
      channel ORA_DISK_1: specifying datafile(s) in backup set
      input datafile file number=00006 name=/opt/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
      input datafile file number=00005 name=/opt/oracle/oradata/ORCL/pdbseed/system01.dbf
      input datafile file number=00008 name=/opt/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0415i1nm_4_1_1 tag=TAG20220819T105811 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
      Finished backup at 19-AUG-22
      --将现有redo日志归档后再次备份归档日志，这样备份集里就包含了至当前时刻的所有redo日志。
      Starting backup at 19-AUG-22
      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=4 RECID=2 STAMP=1113130757
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0515i1o6_5_1_1 tag=TAG20220819T105918 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
      Finished backup at 19-AUG-22
      --备份控制文件和参数文件
      Starting Control File and SPFILE Autobackup at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/c-1640847823-20220819-00 comment=NONE
      Finished Control File and SPFILE Autobackup at 19-AUG-22

也可以对单独的可插拔数据执行备份

RMAN> backup pluggable database pdb1 plus archivelog;
      
      
      Starting backup at 19-AUG-22
      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=3 RECID=1 STAMP=1113130687
      input archived log thread=1 sequence=4 RECID=2 STAMP=1113130757
      input archived log thread=1 sequence=5 RECID=3 STAMP=1113130941
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0715i1tt_7_1_1 tag=TAG20220819T110221 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
      Finished backup at 19-AUG-22
      
      Starting backup at 19-AUG-22
      using channel ORA_DISK_1
      channel ORA_DISK_1: starting full datafile backup set
      channel ORA_DISK_1: specifying datafile(s) in backup set
      input datafile file number=00011 name=/opt/oracle/oradata/ORCL/pdb1/sysaux01.dbf
      input datafile file number=00010 name=/opt/oracle/oradata/ORCL/pdb1/system01.dbf
      input datafile file number=00012 name=/opt/oracle/oradata/ORCL/pdb1/undotbs01.dbf
      input datafile file number=00013 name=/opt/oracle/oradata/ORCL/pdb1/test01.dbf
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0815i1u0_8_1_1 tag=TAG20220819T110224 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
      Finished backup at 19-AUG-22
      
      Starting backup at 19-AUG-22
      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=6 RECID=4 STAMP=1113130960
      channel ORA_DISK_1: starting piece 1 at 19-AUG-22
      channel ORA_DISK_1: finished piece 1 at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0915i1uh_9_1_1 tag=TAG20220819T110240 comment=NONE
      channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
      Finished backup at 19-AUG-22
      
      Starting Control File and SPFILE Autobackup at 19-AUG-22
      piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/c-1640847823-20220819-01 comment=NONE
      Finished Control File and SPFILE Autobackup at 19-AUG-22

这里就只对容器数据库的数据文件进行了备份，其它的归档日志和控制文件、参数文件的备份是相同的。

2 模拟数据库故障

这次模拟的故障是数据库有未提交事务时，当前redo日志故障，这种情况下需要执行数据库的不完全恢复。故障发生时数据库已经关闭。

3 执行数据库不完全恢复

3.1 用rman连接并启动数据库

[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ rman target /
    
    connected to target database (not started)
 --目标数据库没有启动，启动目标数据库至mount模式
    
RMAN> startup mount;
    
    Oracle instance started
    database mounted
    
    Total System Global Area     763362712 bytes
    
    Fixed Size                     9690520 bytes
    Variable Size                549453824 bytes
    Database Buffers             201326592 bytes
    Redo Buffers                   2891776 bytes

3.2 查看故障情况

RMAN> list failure;
    
    using target database control file instead of recovery catalog
    Database Role: PRIMARY
    List of Database Failures
    =========================
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    202        CRITICAL OPEN      19-AUG-22     Online log group 1 is unavailable
    205        HIGH     OPEN      19-AUG-22     Online log member /opt/oracle/oradata/ORCL/redo01.log is corrupt

可以查看故障的详细情况

RMAN> list failure detail;
    
    Database Role: PRIMARY
    
    List of Database Failures
    =========================
    
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    202        CRITICAL OPEN      19-AUG-22     Online log group 1 is unavailable
      Impact: Database might be unrecoverable or become unrecoverable
    
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- -------
    205        HIGH     OPEN      19-AUG-22     Online log member /opt/oracle/oradata/ORCL/redo01.log is corrupt
      Impact: Redo log group may become unavailable

3.3 查看修复建议

rman现在具有修复建议功能，可以根据数据库故障的不同推荐响应的修复方案

RMAN> advise failure;
      
      Database Role: PRIMARY
      
      List of Database Failures
      =========================
      
      Failure ID Priority Status    Time Detected Summary
      ---------- -------- --------- ------------- -------
      202        CRITICAL OPEN      19-AUG-22     Online log group 1 is unavailable
        Impact: Database might be unrecoverable or become unrecoverable
      
      Failure ID Priority Status    Time Detected Summary
      ---------- -------- --------- ------------- -------
      205        HIGH     OPEN      19-AUG-22     Online log member /opt/oracle/oradata/ORCL/redo01.log is corrupt
        Impact: Redo log group may become unavailable
      
      analyzing automatic repair options; this may take some time
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=430 device type=DISK
      analyzing automatic repair options complete
      
      Mandatory Manual Actions
      ========================
      no manual actions available
      
      Optional Manual Actions
      =======================
      no manual actions available
      
      Automated Repair Options
      ========================
      Option Repair Description
      ------ ------------------
      1      Perform incomplete database recovery to SCN 2743364
        Strategy: The repair includes point-in-time recovery with some data loss
        Repair script: /opt/oracle/diag/rdbms/orcl/orcl/hm/reco_18471703.hm

rman对数据库故障的情况进行了分析，提供了自动修复方案及修复脚本，如果数据库之前没有执行过备份，这里会显示没有可用的修复选项。

由于丢失的当前在线事务组日志文件，只能执行不完全恢复，恢复方案里给出了数据库可以恢复到的最大的SCN。修复建议给出的脚本可用文本编辑器来查看。

[root@ ~]# cat /opt/oracle/diag/rdbms/orcl/orcl/hm/reco_18471703.hm
           # database point-in-time recovery
           restore database until scn 2743364;
           recover database until scn 2743364;
           alter database open resetlogs;

脚本一看可知，是数据库恢复的标准步骤。

3.4 修复数据库

可以手动运行来进行数据库恢复，rman也提供了修复数据库的命令，运行一下就会执行修复脚本。

RMAN> repair failure;
              --修复策略说明
              Strategy: The repair includes point-in-time recovery with some data loss
              Repair script: /opt/oracle/diag/rdbms/orcl/orcl/hm/reco_18471703.hm
              --修复脚本的内容
              contents of repair script:
                 # database point-in-time recovery
                 restore database until scn 2743364;
                 recover database until scn 2743364;
                 alter database open resetlogs;
              --建议yes后就可以执行修复脚本
              Do you really want to execute the above repair (enter YES or NO)? yes
              executing repair script
              --还原数据文件
              Starting restore at 19-AUG-22
              using channel ORA_DISK_1
              --不需要还原的数据文件（pdb种子数据库的数据文件）自动跳过
              skipping datafile 5; already restored to file /opt/oracle/oradata/ORCL/pdbseed/system01.dbf
              skipping datafile 6; already restored to file /opt/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
              skipping datafile 8; already restored to file /opt/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
              --还原数据文件
              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 /opt/oracle/oradata/ORCL/system01.dbf
              channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ORCL/sysaux01.dbf
              channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ORCL/undotbs01.dbf
              channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCL/users01.dbf
              channel ORA_DISK_1: reading from backup piece /opt/oracle/homes/OraDBHome21cEE/dbs/0215i1m3_2_1_1
              channel ORA_DISK_1: piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0215i1m3_2_1_1 tag=TAG20220819T105811
              channel ORA_DISK_1: restored backup piece 1
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
              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 00010 to /opt/oracle/oradata/ORCL/pdb1/system01.dbf
              channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCL/pdb1/sysaux01.dbf
              channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCL/pdb1/undotbs01.dbf
              channel ORA_DISK_1: restoring datafile 00013 to /opt/oracle/oradata/ORCL/pdb1/test01.dbf
              channel ORA_DISK_1: reading from backup piece /opt/oracle/homes/OraDBHome21cEE/dbs/0815i1u0_8_1_1
              channel ORA_DISK_1: piece handle=/opt/oracle/homes/OraDBHome21cEE/dbs/0815i1u0_8_1_1 tag=TAG20220819T110224
              channel ORA_DISK_1: restored backup piece 1
              channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
              Finished restore at 19-AUG-22
              --开始恢复数据库
              Starting recover at 19-AUG-22
              using channel ORA_DISK_1
              --执行介质恢复
              starting media recovery
              
              archived log for thread 1 with sequence 4 is already on disk as file /opt/oracle/homes/OraDBHome21cEE/dbs/arch1_4_1113067087.dbf
              archived log for thread 1 with sequence 5 is already on disk as file /opt/oracle/homes/OraDBHome21cEE/dbs/arch1_5_1113067087.dbf
              archived log for thread 1 with sequence 6 is already on disk as file /opt/oracle/homes/OraDBHome21cEE/dbs/arch1_6_1113067087.dbf
              archived log file name=/opt/oracle/homes/OraDBHome21cEE/dbs/arch1_4_1113067087.dbf thread=1 sequence=4
              archived log file name=/opt/oracle/homes/OraDBHome21cEE/dbs/arch1_5_1113067087.dbf thread=1 sequence=5
              media recovery complete, elapsed time: 00:00:00
              Finished recover at 19-AUG-22
              
              Statement processed
              repair failure complete

4 打开并连接数据库

使用rman修复数据库完成后，数据库已经处于open状态，由于数据库没有配置启动可插拔数据库的启动触发器，可插拔数据库处于mount状态，需要运行alter pluggable database命令将其打开。

[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ sqlplus / as sysdba       
SQL> show pdbs;
                  CON_ID CON_NAME                       OPEN MODE  RESTRICTED
              ---------- ------------------------------ ---------- ----------
                       2 PDB$SEED                       READ ONLY  NO
                       3 PDB1                           MOUNTED
SQL> alter pluggable database PDB1 open;
              Pluggable database altered.

登录可插拔数据库pdb1，查看表中的数据

[oracle@iZ2ze0t8khaprrpfvmevjiZ ~]$ sqlplus test/test123@iZ2ze0t8khaprrpfvmevjiZ/pdb1
 SQL> select * from test;   
              no rows selected

表恢复到了事务运行之前的状态。

可以看到，使用rman备份数据库十分简单方便，rman的数据库修复功能可以避免大量的手动操作，可以有效避免繁琐复杂操作造成的失误。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者