pg数据库镜像添加初始数据 数据库镜像备份

使用RMAN备份与恢复数据库（7）——管理备份集

通过RMAN有两种不同类型的备份方式：使用镜像备份和使用备份集备份。

镜像备份（Image Copies）：镜像复制其实就是创建数据文件、控制文件或归档文件的副本，与用户通过操作系统命令创建的备份一样，RMAN利用目标数据库中的服务进程来完成文件复制。

备份集（Backup Sets）：为RMAN默认的备份选项。备份集是RMAN创建的具有特定格式的逻辑备份对象，备份集在逻辑上由一个或多个备份片段（Backup Piece）组成，每个备份片段在物理上对应一个操作系统文件，一个备份片段中可能包含多个数据文件、控制文件或归档文件。使用备份集备份时只读取数据库中已经使用的数据块，因此备份文件的体积更小。

一、查看和修改RMAN的备份方式

1、查看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 OFF; # 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 CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rmanbak/%u';
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/local/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default

2、修改RMAN的备份方式

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY; 

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY;
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1

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 OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
--#################################################################################
--备份方式修改为镜像备份
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY;
--#################################################################################
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rmanbak/%u';
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/local/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default

3、把备份方式重新修改为备份集

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; 

old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COPY;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

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 OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
--#################################################################################
--备份方式修改为备份集备份
--PARALLELISM参数为并行数
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
--#################################################################################
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rmanbak/%u';
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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/usr/local/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f'; # default

在备份集备份方式下也可以使用镜像备份，只需要使用backup as copy命令即可。

二、查看备份集

1、查看参数文件备份集

RMAN> list backup of spfile;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
59      Full    80.00K     DISK        00:00:00     2020-04-10 06:13:03
        BP Key: 91   Status: AVAILABLE  Compressed: NO  Tag: TAG20200410T061303
        Piece Name: /home/oracle/rmanbak/spfileorcl.old
  SPFILE Included: Modification time: 2020-04-10 05:34:50
  SPFILE db_unique_name: ORCL

2、查看控制文件备份集

RMAN> list backup of controlfile;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
60      Full    9.83M      DISK        00:00:02     2020-04-10 06:13:38
        BP Key: 92   Status: AVAILABLE  Compressed: NO  Tag: TAG20200410T061336
        Piece Name: /home/oracle/rmanbak/control01.old
  Control File Included: Ckp SCN: 1766092      Ckp time: 2020-04-10 06:13:36

3、查看数据文件备份集

RMAN> list backup of datafile 5;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
57      Full    18.42M     DISK        00:00:00     2020-04-09 20:28:34
        BP Key: 89   Status: AVAILABLE  Compressed: NO  Tag: TAG20200409T202834
        Piece Name: /home/oracle/rmanbak/data01.dbf
  List of Datafiles in backup set 57
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  5       Full 1741852    2020-04-09 20:28:34 /usr/local/oradata/orcl/data01.dbf

4、查看表空间备份集

说明：表空间和数据文件备份结果相同。

RMAN> list backup of tablespace data01;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
57      Full    18.42M     DISK        00:00:00     2020-04-09 20:28:34
        BP Key: 89   Status: AVAILABLE  Compressed: NO  Tag: TAG20200409T202834
        Piece Name: /home/oracle/rmanbak/data01.dbf
  List of Datafiles in backup set 57
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  5       Full 1741852    2020-04-09 20:28:34 /usr/local/oradata/orcl/data01.dbf

5、查看归档日志备份集

RMAN> list backup of archivelog all;


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


BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
58      14.20M     DISK        00:00:02     2020-04-09 20:32:06
        BP Key: 90   Status: AVAILABLE  Compressed: YES  Tag: TAG20200409T203204
        Piece Name: /home/oracle/rmanbak/archivelog_ORCL_1559168802_81_1_20200409

  List of Archived Logs in backup set 58
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    9       1741038    2020-04-09 20:20:23 1741173    2020-04-09 20:21:58
  1    10      1741173    2020-04-09 20:21:58 1741284    2020-04-09 20:22:27
  1    11      1741284    2020-04-09 20:22:27 1741391    2020-04-09 20:22:58
  1    12      1741391    2020-04-09 20:22:58 1741661    2020-04-09 20:23:41
  1    13      1741661    2020-04-09 20:23:41 1742829    2020-04-09 20:30:10
  1    14      1742829    2020-04-09 20:30:10 1743756    2020-04-09 20:30:52
  1    15      1743756    2020-04-09 20:30:52 1743788    2020-04-09 20:32:04

6、查看所有的备份集

RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
57      Full    18.42M     DISK        00:00:00     2020-04-09 20:28:34
        BP Key: 89   Status: AVAILABLE  Compressed: NO  Tag: TAG20200409T202834
        Piece Name: /home/oracle/rmanbak/data01.dbf
  List of Datafiles in backup set 57
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  5       Full 1741852    2020-04-09 20:28:34 /usr/local/oradata/orcl/data01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time    
------- ---------- ----------- ------------ -------------------
58      14.20M     DISK        00:00:02     2020-04-09 20:32:06
        BP Key: 90   Status: AVAILABLE  Compressed: YES  Tag: TAG20200409T203204
        Piece Name: /home/oracle/rmanbak/archivelog_ORCL_1559168802_81_1_20200409

  List of Archived Logs in backup set 58
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    9       1741038    2020-04-09 20:20:23 1741173    2020-04-09 20:21:58
  1    10      1741173    2020-04-09 20:21:58 1741284    2020-04-09 20:22:27
  1    11      1741284    2020-04-09 20:22:27 1741391    2020-04-09 20:22:58
  1    12      1741391    2020-04-09 20:22:58 1741661    2020-04-09 20:23:41
  1    13      1741661    2020-04-09 20:23:41 1742829    2020-04-09 20:30:10
  1    14      1742829    2020-04-09 20:30:10 1743756    2020-04-09 20:30:52
  1    15      1743756    2020-04-09 20:30:52 1743788    2020-04-09 20:32:04

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
59      Full    80.00K     DISK        00:00:00     2020-04-10 06:13:03
        BP Key: 91   Status: AVAILABLE  Compressed: NO  Tag: TAG20200410T061303
        Piece Name: /home/oracle/rmanbak/spfileorcl.old
  SPFILE Included: Modification time: 2020-04-10 05:34:50
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
60      Full    9.83M      DISK        00:00:02     2020-04-10 06:13:38
        BP Key: 92   Status: AVAILABLE  Compressed: NO  Tag: TAG20200410T061336
        Piece Name: /home/oracle/rmanbak/control01.old
  Control File Included: Ckp SCN: 1766092      Ckp time: 2020-04-10 06:13:36

三、使用crosscheck命令核对备份集

crosscheck命令用于核对磁盘和磁带上的备份文件，以确保RMAN资料库与备份文件保持同步。当执行crosscheck命令时，如果资料库记录不匹配于备份文件的物理状态，那么该命令会更新资料库记录的状态信息。备份文件的状态包括：AVALIABLE（可用的）、UNAVALIABLE（不可用的）、EXPIRED（失效的）。crosscheck命令的格式如下：

（1）crosscheck backup;   --核对所有备份集
（2）crosscheck backup of database;   --核对所有数据文件的备份集
（3）crosscheck backup of tablespace data01;   --核对特定表空间的备份集
（4）crosscheck backup of datafile 4;   --核对特定数据文件的备份集
（5）crosscheck backup of controlfile;   --核对控制文件的备份集
（6）crosscheck backup of spfile;   --核对SPFILE的备份集
（7）crosscheck backup of archivelog sequence 3;   --核对归档日志的备份集

（8）crosscheck copy;   --核对所有镜像副本
（9）crosscheck copy of database;   --核对所有数据文件的镜像副本
（10）crosscheck copy of tablespace users;   --核对特定表空间的镜像副本
（11）crosscheck copy of datafile 6;   --核对特定数据文件的镜像副本
（12）crosscheck copy of archivelog sequence 4;   --核对归档日志的镜像副本
（13）crosscheck copy of controlfile;  --核对控制文件的镜像副本

举例：

--控制文件的备份被删除，检查之后该备份集的状态变成expired（失效）
RMAN> crosscheck backup of controlfile;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rmanbak/control01.old RECID=92 STAMP=1037340817
Crosschecked 1 objects

RMAN> list backup of controlfile;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
60      Full    9.83M      DISK        00:00:02     2020-04-10 06:13:38
        BP Key: 92   Status: EXPIRED  Compressed: NO  Tag: TAG20200410T061336
        Piece Name: /home/oracle/rmanbak/control01.old
  Control File Included: Ckp SCN: 1766092      Ckp time: 2020-04-10 06:13:36

四、删除备份集

删除备份集之前通常先使用crosscheck命令进行检查。删除备份集的命令如下：

1、删除过期备份

当使用RMAN命令执行备份操作时，RMAN会根据备份冗余策略确定备份是否过期。

RMAN> crosscheck backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rmanbak/data01.dbf RECID=89 STAMP=1037305714
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rmanbak/archivelog_ORCL_1559168802_81_1_20200409 RECID=90 STAMP=1037305924
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/home/oracle/rmanbak/spfileorcl.old RECID=91 STAMP=1037340783
Crosschecked 3 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/home/oracle/rmanbak/control01.old RECID=92 STAMP=1037340817
Crosschecked 1 objects


RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
using channel ORA_DISK_2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           60     2020-04-10 06:13:38
  Backup Piece       92     2020-04-10 06:13:38 /home/oracle/rmanbak/control01.old

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/home/oracle/rmanbak/control01.old RECID=92 STAMP=1037340817
Deleted 1 objects

2、删除无效备份

首先执行CROSSCHECK命令核对备份集，如果发现备份无效（比如备份对应的数据文件损坏或丢失），RMAN会将该备份集标记为 EXPIRED状态。

说明：noprompt参数表示删除时不提示yes或no。

RMAN> delete noprompt expired backup;

using channel ORA_DISK_1
using channel ORA_DISK_2
specification does not match any backup in the repository

3、删除EXPIRED镜像副本

delete expired copy;

4、删除特定备份集

RMAN> delete noprompt backupset 57;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
89      57      1   1   AVAILABLE   DISK        /home/oracle/rmanbak/data01.dbf
deleted backup piece
backup piece handle=/home/oracle/rmanbak/data01.dbf RECID=89 STAMP=1037305714
Deleted 1 objects

5、删除特定备份片

RMAN> delete noprompt backuppiece '/home/oracle/rmanbak/archivelog_ORCL_1559168802_81_1_20200409';

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
90      58      1   1   AVAILABLE   DISK        /home/oracle/rmanbak/archivelog_ORCL_1559168802_81_1_20200409
deleted backup piece
backup piece handle=/home/oracle/rmanbak/archivelog_ORCL_1559168802_81_1_20200409 RECID=90 STAMP=1037305924
Deleted 1 objects

6、删除所有备份集

delete noprompt backup;

7、删除所有镜像副本

delete noprompt copy;

8、删除特定镜像副本

delete datafile copy '/home/oracle/rmanbak/data01.dbf';