oracle19c容器数据库rman备份特性-----性能优化(三)

2024-01-09 06:29:00

目录

冗余备份片

1.备份的时候指定

2.rman配置中设定

归档备份(将备份集保留)

二级备份(将备份文件保留)

1.备份闪回恢复区的恢复文件

2.备份所有恢复文件

recovery catalog database

1.創建recovery catalog

2.创建VPC

?data recovery advisor


备份

如果一个数据文件很大,可以设置多通道并行备份,提高备份性能。

RMAN> show all;

RMAN configuration parameters for database with db_unique_name CDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
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 3 BACKUP TYPE TO COMPRESSED BACKUPSET;//设置3个通道并行备份
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 ? '/u01/app/oracle/rman_backup/%U.bk';
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/app/oracle/product/19.2.0/db_home1/dbs/snapcf_CDB.f'; # default


?

RMAN> run
?{allocate channel c1 DEVICE TYPE DISK;
allocate channel c2 DEVICE TYPE DISK;
allocate channel c3 DEVICE TYPE DISK;
BACKUP DATAFILE 1 SECTION SIZE=100M TAG'section25mb';------这个代表备份数据文件1,oracle会收件将这个数据文件分成若干个100m,然后用这三个通道进行备份
}2>?
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "{"
RMAN-01008: the bad identifier was:?
RMAN-01007: at line 2 column 1 file: standard input

RMAN>?
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "for"
RMAN-01008: the bad identifier was: c2
RMAN-01007: at line 1 column 18 file: standard input

RMAN>?
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "for"
RMAN-01008: the bad identifier was: c3
RMAN-01007: at line 1 column 18 file: standard input

RMAN>?
Starting backup at 03-JAN-24
allocated channel: ORA_DISK_1// t
channel ORA_DISK_1: SID=25 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=6 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=32 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 1 through 12800
channel ORA_DISK_1: starting piece 1 at 03-JAN-24
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 12801 through 25600
channel ORA_DISK_2: starting piece 2 at 03-JAN-24
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 25601 through 38400
channel ORA_DISK_3: starting piece 3 at 03-JAN-24
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_1_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 38401 through 51200
channel ORA_DISK_1: starting piece 4 at 03-JAN-24
channel ORA_DISK_1: finished piece 4 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_4_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 51201 through 64000
channel ORA_DISK_1: starting piece 5 at 03-JAN-24
channel ORA_DISK_2: finished piece 2 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_2_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:19
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 64001 through 76800
channel ORA_DISK_2: starting piece 6 at 03-JAN-24
channel ORA_DISK_3: finished piece 3 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_3_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:19
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 76801 through 89600
channel ORA_DISK_3: starting piece 7 at 03-JAN-24
channel ORA_DISK_1: finished piece 5 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_5_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 89601 through 102400
channel ORA_DISK_1: starting piece 8 at 03-JAN-24
channel ORA_DISK_2: finished piece 6 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_6_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 102401 through 115200
channel ORA_DISK_2: starting piece 9 at 03-JAN-24
channel ORA_DISK_3: finished piece 7 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_7_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:16
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
backing up blocks 115201 through 116480
channel ORA_DISK_3: starting piece 10 at 03-JAN-24
channel ORA_DISK_3: finished piece 10 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_10_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 8 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_8_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_2: finished piece 9 at 03-JAN-24
piece handle=/u01/app/oracle/rman_backup/0b2flagn_9_1.bk tag=SECTION25MB comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
Finished backup at 03-JAN-24

Starting Control File and SPFILE Autobackup at 03-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157278273_ls9jl2bg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-24



RMAN> list backup;------以下看出也是备份了控制文件和spfile文件,你看一共是236m,一个备份成了15部分


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


BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 ? ? ? Full ? ?236.32M ? ?DISK ? ? ? ?00:00:42 ? ? 03-JAN-24 ? ? ?
? List of Datafiles in backup set 8
? File LV Type Ckp SCN ? ?Ckp Time ?Abs Fuz SCN Sparse Name
? ---- -- ---- ---------- --------- ----------- ------ ----
? 1 ? ? ? Full 3757643 ? ?03-JAN-24 ? ? ? ? ? ? ?NO ? ?/u01/app/oracle/oradata/CDB/system01.dbf

? Backup Set Copy #1 of backup set 8
? Device Type Elapsed Time Completion Time Compressed Tag
? ----------- ------------ --------------- ---------- ---
? DISK ? ? ? ?00:00:42 ? ? 03-JAN-24 ? ? ? YES ? ? ? ?SECTION25MB

? ? List of Backup Pieces for backup set 8 Copy #1
? ? BP Key ?Pc# Status ? ? ?Piece Name
? ? ------- --- ----------- ----------
? ? 8 ? ? ? 1 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_1_1.bk
? ? 9 ? ? ? 2 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_2_1.bk
? ? 10 ? ? ?3 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_3_1.bk
? ? 11 ? ? ?4 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_4_1.bk
? ? 13 ? ? ?5 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_5_1.bk
? ? 14 ? ? ?6 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_6_1.bk
? ? 12 ? ? ?7 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_7_1.bk
? ? 17 ? ? ?8 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_8_1.bk
? ? 16 ? ? ?9 ? AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_9_1.bk
? ? 15 ? ? ?10 ?AVAILABLE ? /u01/app/oracle/rman_backup/0b2flagn_10_1.bk

BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9 ? ? ? Full ? ?1.05M ? ? ?DISK ? ? ? ?00:00:01 ? ? 03-JAN-24 ? ? ?
? ? ? ? BP Key: 18 ? Status: AVAILABLE ?Compressed: YES ?Tag: TAG20240103T101113
? ? ? ? Piece Name: /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157278273_ls9jl2bg_.bkp
? SPFILE Included: Modification time: 03-JAN-24
? SPFILE db_unique_name: CDB
? Control File Included: Ckp SCN: 3757678 ? ? ?Ckp time: 03-JAN-24

查看备份出来的文件:

冗余备份片

冗余备份片是为了防止备份片损坏,如果一个备份片损坏了,可以用另一个没有损坏的来做恢复

1.备份的时候指定

RMAN> backup copies 2 datafile 1;-----一个备份集里面有两个相同的备份片

Starting backup at 03-JAN-24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JAN-24
channel ORA_DISK_1: finished piece 1 at 03-JAN-24 with 2 copies and tag TAG20240103T112254
piece handle=/u01/app/oracle/rman_backup/0m2fleoe_1_1.bk comment=NONE
piece handle=/u01/app/oracle/rman_backup/0m2fleoe_1_2.bk comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 03-JAN-24

Starting Control File and SPFILE Autobackup at 03-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157282620_ls9nswdz_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-24

RMAN> list backup;-------显示一个备份集有19和20两个备份片


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


BS Key ?Type LV Size
------- ---- -- ----------
10 ? ? ?Full ? ?230.16M
? List of Datafiles in backup set 10
? File LV Type Ckp SCN ? ?Ckp Time ?Abs Fuz SCN Sparse Name
? ---- -- ---- ---------- --------- ----------- ------ ----
? 1 ? ? ? Full 3761907 ? ?03-JAN-24 ? ? ? ? ? ? ?NO ? ?/u01/app/oracle/oradata/CDB/system01.dbf

? Backup Set Copy #1 of backup set 10
? Device Type Elapsed Time Completion Time Compressed Tag
? ----------- ------------ --------------- ---------- ---
? DISK ? ? ? ?00:00:37 ? ? 03-JAN-24 ? ? ? YES ? ? ? ?TAG20240103T112254

? ? List of Backup Pieces for backup set 10 Copy #1
? ? BP Key ?Pc# Status ? ? ?Piece Name
? ? ------- --- ----------- ----------
? ? 19 ? ? ?1 ? AVAILABLE ? /u01/app/oracle/rman_backup/0m2fleoe_1_1.bk

? Backup Set Copy #2 of backup set 10
? Device Type Elapsed Time Completion Time Compressed Tag
? ----------- ------------ --------------- ---------- ---
? DISK ? ? ? ?00:00:37 ? ? 03-JAN-24 ? ? ? YES ? ? ? ?TAG20240103T112254

? ? List of Backup Pieces for backup set 10 Copy #2
? ? BP Key ?Pc# Status ? ? ?Piece Name
? ? ------- --- ----------- ----------
? ? 20 ? ? ?1 ? AVAILABLE ? /u01/app/oracle/rman_backup/0m2fleoe_1_2.bk

BS Key ?Type LV Size ? ? ? Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 ? ? ?Full ? ?1.05M ? ? ?DISK ? ? ? ?00:00:00 ? ? 03-JAN-24 ? ? ?
? ? ? ? BP Key: 21 ? Status: AVAILABLE ?Compressed: YES ?Tag: TAG20240103T112340
? ? ? ? Piece Name: /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157282620_ls9nswdz_.bkp
? SPFILE Included: Modification time: 03-JAN-24
? SPFILE db_unique_name: CDB
? Control File Included: Ckp SCN: 3761934 ? ? ?Ckp time: 03-JAN-24

查看备份出来的文件,这两个备份片的数据内容是相同的:

2.rman配置中设定

也可以通过在rman配置中设定copies2,这样的话就不需要每次都去指定了。

RMAN> ?CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;------指定copy两份

new RMAN configuration parameters:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;
new RMAN configuration parameters are successfully stored

RMAN> backup datafile 1;

Starting backup at 03-JAN-24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB/system01.dbf
channel ORA_DISK_1: starting piece 1 at 03-JAN-24
channel ORA_DISK_1: finished piece 1 at 03-JAN-24 with 2 copies and tag TAG20240103T121011
piece handle=/u01/app/oracle/rman_backup/0o2flhh3_1_1.bk comment=NONE
piece handle=/u01/app/oracle/rman_backup/0o2flhh3_1_2.bk comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 03-JAN-24

Starting Control File and SPFILE Autobackup at 03-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157285456_ls9qljvq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-24

查看备份出来的文件:

归档备份(将备份集保留)

創建一個歸檔備份,歸檔備份是什麽意思呢?

創建一個歸檔備份的最大好處就是我們可以設定备份文件保存的時間,但是只能在catalog數據庫中創建歸檔備份,因爲備份都是分爲知識庫和備份集,知識庫又是存儲在控制文件中的,控制文件中的備份默認保存時間為7天,所以7天過後,就算我們的備份文件還在,但是控制文件中的备份集也已經不在了,那這也是無效備份(expired)。

归档备份脱离备份的保留策略

RMAN>RMAN TARGET /?CATALOG catalog_username/catalog_password@catalog_database?? ? ? ? ? ? ??? //连接到目标数据库和catalog数据库

RMAN> BACKUP DATABASE ?KEEP FOREVER RETORE POINT'mark';//备份的时候我们给这个备份集设定的保留时间是永久保留,取个名字叫做mark,方便识别
?
RMAN> SHOW ALL ;-----------查看備份的冗餘策略,如果是1的話
?
RMAN> BACKUP DATABASE;-------再做一次全庫備份,那麽正常情況下我們做的第一次備份就是過時的
?
RMAN> report obsolete;------------查看當前的過時備份,這裏面并沒有記錄我們的第一次備份
?
RMAN> CHANGE BACKUPSET 954 NOKEEP;-----delete obslete命令是無法刪除我們的第一次備份的,我們要先用這個命令再執行 delete obsolete才可以刪除

二级备份(将备份文件保留)

实验得知:二级备份的没有备份集信息,但是因为发生rman备份都会自动备份控制文件和spfile文件,所以每次发生二级备份的时候会多1个备份集是数据控制文件和spfile备份的。

二级备份就是将数据库恢复需要的备份文件备份到另一个位置,开始二级备份前所有之前的备份文件如下:

?创建一个二级备份的备份路径:

1.备份闪回恢复区的恢复文件

RMAN> backup recovery area to destination '/u01/app/oracle/oradata/twice_backup/';------备份闪回恢复区中数据库恢复需要用到的文件,备份路径是需要提前创建的,只需要创建一个路径就行了,oracle会自动为二级备份的文件命名。如下可以看出只备份了闪回恢复区中的归档日志文件和之前自动备份在里面的spfile和控制文件。
Starting backup at 03-JAN-24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
specification does not match any datafile copy in the repository
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=5 STAMP=1157230219
input archived log thread=1 sequence=31 RECID=6 STAMP=1157273524
input archived log thread=1 sequence=32 RECID=7 STAMP=1157289510/归档日志文件
channel ORA_DISK_1: starting piece 1 at 03-JAN-24
channel ORA_DISK_2: starting compressed archived log backup set
channel ORA_DISK_2: specifying archived log(s) in backup set
input archived log thread=1 sequence=33 RECID=8 STAMP=1157289605
input archived log thread=1 sequence=34 RECID=9 STAMP=1157294647
input archived log thread=1 sequence=35 RECID=10 STAMP=1157295975
channel ORA_DISK_2: starting piece 1 at 03-JAN-24
channel ORA_DISK_3: starting compressed archived log backup set
channel ORA_DISK_3: specifying archived log(s) in backup set
input archived log thread=1 sequence=36 RECID=11 STAMP=1157296064
channel ORA_DISK_3: starting piece 1 at 03-JAN-24
channel ORA_DISK_2: finished piece 1 at 03-JAN-24
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_annnn_TAG20240103T151552_lsb2f8bk_.bkp tag=TAG20240103T151552 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_2: input backup set: count=86, stamp=1157296066, piece=1
channel ORA_DISK_2: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157296066_lsb1y2nk_.bkp
channel ORA_DISK_3: finished piece 1 at 03-JAN-24
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_annnn_TAG20240103T151552_lsb2f8c2_.bkp tag=TAG20240103T151552 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_annnn_TAG20240103T151552_lsb2f89p_.bkp tag=TAG20240103T151552 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2f9jw_.bkp comment=NONE
channel ORA_DISK_2: finished piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece complete, elapsed time: 00:00:01
Finished backup at 03-JAN-24

Starting Control File and SPFILE Autobackup at 03-JAN-24//这里也备份了控制文件和归档日志文件
piece handle=/u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157296554_lsb2fbx9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-24

查看二级备份的文件:

2.备份所有恢复文件

RMAN> backup recovery files to destination '/u01/app/oracle/oradata/twice_backup/';

Starting backup at 03-JAN-24
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
specification does not match any datafile copy in the repository
skipping archived logs of thread 1 from sequence 30 to 36; already backed up
skipping backup set key 44; already backed up 1 time(s)
skipping backup set key 45; already backed up 1 time(s)
skipping backup set key 46; already backed up 1 time(s)
skipping backup set key 47; already backed up 1 time(s)
channel ORA_DISK_1: input backup set: count=75, stamp=1157295975, piece=1
channel ORA_DISK_1: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece /u01/app/oracle/rman_backup/2b2flrr7_1_1.bk
channel ORA_DISK_2: input backup set: count=76, stamp=1157295975, piece=1
channel ORA_DISK_2: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece /u01/app/oracle/rman_backup/2c2flrr7_1_1.bk
channel ORA_DISK_3: input backup set: count=74, stamp=1157295975, piece=1
channel ORA_DISK_3: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece /u01/app/oracle/rman_backup/2a2flrr7_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z3n6_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=77, stamp=1157295976, piece=1
channel ORA_DISK_1: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece /u01/app/oracle/rman_backup/2d2flrr8_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z3nm_.bkp comment=NONE
channel ORA_DISK_2: finished piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_2: input backup set: count=80, stamp=1157295978, piece=1
channel ORA_DISK_2: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece /u01/app/oracle/rman_backup/2g2flrra_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z3ot_.bkp comment=NONE
channel ORA_DISK_3: finished piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_3: input backup set: count=79, stamp=1157295978, piece=1
channel ORA_DISK_3: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece /u01/app/oracle/rman_backup/2f2flrra_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z4qp_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:00
channel ORA_DISK_1: input backup set: count=81, stamp=1157296013, piece=1
channel ORA_DISK_1: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece /u01/app/oracle/rman_backup/2h2flrsd_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/0B1BBA83F7076645E06302F4A8C0A4FF/backupset/2024_01_03/o1_mf_nnnnf__lsb2z5bt_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=82, stamp=1157296013, piece=1
channel ORA_DISK_1: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece /u01/app/oracle/rman_backup/2i2flrsd_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/0B1C0921A890709AE06302F4A8C0D026/backupset/2024_01_03/o1_mf_nnnnf__lsb2z4th_.bkp comment=NONE
channel ORA_DISK_2: finished piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece complete, elapsed time: 00:00:02
channel ORA_DISK_2: input backup set: count=84, stamp=1157296039, piece=1
channel ORA_DISK_2: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece /u01/app/oracle/rman_backup/2k2flrt7_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z4yh_.bkp comment=NONE
channel ORA_DISK_3: finished piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece complete, elapsed time: 00:00:02
channel ORA_DISK_3: input backup set: count=78, stamp=1157295978, piece=1
channel ORA_DISK_3: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece /u01/app/oracle/rman_backup/2e2flrra_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/0B1C0921A890709AE06302F4A8C0D026/backupset/2024_01_03/o1_mf_nnnnf__lsb2z67d_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_1: input backup set: count=83, stamp=1157296039, piece=1
channel ORA_DISK_1: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece /u01/app/oracle/rman_backup/2j2flrt7_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/0B1BBA83F7076645E06302F4A8C0A4FF/backupset/2024_01_03/o1_mf_nnnnf__lsb2z6dn_.bkp comment=NONE
channel ORA_DISK_2: finished piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_2: input backup set: count=85, stamp=1157296065, piece=1
channel ORA_DISK_2: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece /u01/app/oracle/rman_backup/2l2flru1_1_1.bk
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z6l2_.bkp comment=NONE
channel ORA_DISK_3: finished piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece complete, elapsed time: 00:00:01
channel ORA_DISK_3: input backup set: count=91, stamp=1157296554, piece=1
channel ORA_DISK_3: starting compressed piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157296554_lsb2fbx9_.bkp
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z7pv_.bkp comment=NONE
channel ORA_DISK_2: finished piece 1 at 03-JAN-24
channel ORA_DISK_2: backup piece complete, elapsed time: 00:00:00
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/0B1BBA83F7076645E06302F4A8C0A4FF/backupset/2024_01_03/o1_mf_nnnnf__lsb2z7mv_.bkp comment=NONE
channel ORA_DISK_1: finished piece 1 at 03-JAN-24
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:01
piece handle=/u01/app/oracle/oradata/twice_backup/CDB/backupset/2024_01_03/o1_mf_nnnnf__lsb2z7tp_.bkp comment=NONE
channel ORA_DISK_3: finished piece 1 at 03-JAN-24
channel ORA_DISK_3: backup piece complete, elapsed time: 00:00:01
Finished backup at 03-JAN-24

Starting Control File and SPFILE Autobackup at 03-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_03/o1_mf_s_1157297128_lsb2z91d_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-JAN-24

查看备份出来的文件:

如果我现在删除所有的备份集信息会发生什么变化呢?

二次备份路径中的备份文件和之前的备份文件全部被删除。

recovery catalog database


其實回復目錄數據庫就是普通的數據庫。

rman備份分爲知識庫和備份的數據塊(也就是實際備份的文件),知識庫默認是存儲在控制文件中,控制文件分爲固定和可變兩部分,固定部分存儲數據庫的結構等,可變部分存儲rman備份的知識庫,知識庫在控制文件中的默認保存時間是7天,如果我們希望知識庫的保存時間更長,那麽控制文件就會增大,這不方便數據庫的管理,所以我們可以在知識庫的信息放在recovery catalog中。

1.創建recovery catalog

VPC不是catalog recovery的拥有者,它只是有权限访问和管理分配给它的实例

SQL>create tablespaces rc_tbs datafile size 20m;-----------------在catalog数据库創建一個存儲恢復目錄的表空間
?
SQL>CREATE USER rcowner IDENTIFIED BY rcpass
?
TEMPORARY TABLESPACE temp
?
DEFAULT TABLESPACE rc_tbs
?
QUOTA ULIMITED ON rc_tbs;---------------創建一個管理恢復目錄的用戶
SQL>GRANT CONNECT,RESOURCE TO rcowner;
?
SQL>GRANT recovery_catalog_owner TO rcowner;--------------賦權
?
rman catalog rcowner/rcpass@orcl-------------以上做完之后使用rman登录到catalog數據庫
?
RMAN>CREATE CATALOG;--------------創建catalog数据库
?
RMAN>connect taget sys/oracle@cuug ------再連接目標數據庫
?
RMAN>register database;----------將目標數據庫注冊到catalog数据库
?
?
?

2.创建VPC

如果存放catalog的数据库升级了,那VPC也要做升级

SQL>CREATE USER vpc1 IDENTIFIED BY vpc1;-------------在catalog数据库創建一個私有用戶
?
SQL>GRANT CREATE SESSION TO vpc1;--------------賦權
?
SQL>@?rdbms/admin/rdbmsrmanvpc.sql ?-vpd rcowner--------------在sqlplus下用sys用户執行這個脚本,启动VPD,也就是虚拟的私有数据库,rcowner是catalog数据库的owner
?
rman catalog rcowner/rcpass@orcl-------------在下一次登录到catalog数据库时要执行命令升级一下(这里需要先退出rman重新登陆)
?
RMAN>UPGRADE CATALOG;
RMAN>UPGRADE CATALOG;----需要执行两次
RMAN>GRANT CATALOG FOR DATABASE cuug to vpc1;---------授權vpc1可以管理的數據庫
RMAN>connect catalog vpc1/vpc1@cuug-----然后就可以用vpc1用户连接到数据库
?
rman target sys/oracle@cuug catalog vpc1/vpc1@orcl----------------用vpc1用戶登錄到目標數據庫和catalog數據庫
RMAN>backup database;-----然后我就可以在这里面进行备份恢复数据库了,和正常数据库操作一样
RMAN>report schema;---------可以看到cuug数据库的结构

RMAN>REVOKE CATALOG FOR DATABASE cuug from vpc1;--------收回vpc1管理cuug数据库的权限
RMAN>DROP DATALOG;-------删除vpc

在连接到catalog数据库后,查看目标数据库的结构

?

data recovery advisor


依赖ADR收集数据故障信息

[oracle@zhuke ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 15 18:27:28 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. ?All rights reserved.

connected to target database: ZHUKE (DBID=749924755, not open)

SQL> startup force---重新启动数据库产生报警,这个报警会记录在ADR
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size ? ? ? ? ? ?2252864 bytes
Variable Size ? ? ? ? ?805310400 bytes
Database Buffers ? ? ?452984832 bytes
Redo Buffers ? ? ? ? ? ?8818688 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/zhuke/users01.dbf'

RMAN> list failure;---oracle发生错误后用这个命令找出错误

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

RMAN> list failure all;---有时候执行list failure命令会出现没有错误显示的情况,有可能时因为重要性低

List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

RMAN> change failure 62 priority high;----可以执行这个命令修改重要性

List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

Do you really want to change the above failures (enter YES or NO)? yes
changed 1 failures to HIGH priority

RMAN> advise failure;----分析提供解决方案,有时候我们执行这个命令没有给我们解决方案,有可能是因为我们没有备份或者备份过期了

RMAN> crosscheck backup of database;--可以用这个命令检查备份集和备份片的信息是否一致

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/oradata/0125q3pk_1_1.BK RECID=1 STAMP=1146949428
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/oradata/032bic6s_1_1.BK RECID=3 STAMP=1152987356
Crosschecked 2 objects

RMAN> crosscheck archivelog all;-----检查所需要的归档是否都在

List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/zhuke/users01.dbf was unintentionally renamed or moved, restore it
2. Try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. ?Then perform a Data Guard role change (failover). ?Available standbys: zhuke_st.

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 ? ? ?Restore and recover datafile 4 ?
? Strategy: The repair includes complete media recovery with no data loss
? Repair script: /u01/app/oracle/diag/rdbms/zhuke_pd/zhuke/hm/reco_3209059545.hm

[oracle@zhuke zhuke]$ cd /u01/app/oracle/diag/rdbms/zhuke_pd/zhuke/hm/
[oracle@zhuke hm]$ ll -ls
total 4
4 -rw-r----- 1 oracle oinstall 123 Nov 15 18:32 reco_3209059545.hm
[oracle@zhuke hm]$ more reco_3209059545.hm--------查看这个脚本的内容
? ?# restore and recover datafile
? ?restore datafile 4;
? ?recover datafile 4;
? ?sql 'alter database datafile 4 online';

RMAN> change failure 62 closed;--也可以手动将故障关闭,oracle会认为故障已经解决,但是数据库还是不能启动,同样会报错

List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

Do you really want to change the above failures (enter YES or NO)? yes
closed 1 failures

RMAN> list failure;

no failures found that match specification

RMAN> list failure all;

no failures found that match specification

SQL> shutdown immediate;--同样会报错
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup force;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size ? ? ? ? ? ?2252864 bytes
Variable Size ? ? ? ? ?805310400 bytes
Database Buffers ? ? ?452984832 bytes
Redo Buffers ? ? ? ? ? ?8818688 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/zhuke/users01.dbf'

[oracle@zhuke ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 15 18:51:45 2023

Copyright (c) 1982, 2011, Oracle and/or its affiliates. ?All rights reserved.

connected to target database: ZHUKE (DBID=749924755, not open)

RMAN> list failure;----又出现了错误

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status ? ?Time Detected Summary
---------- -------- --------- ------------- -------
62 ? ? ? ? HIGH ? ? OPEN ? ? ?15-NOV-23 ? ? One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/zhuke/users01.dbf was unintentionally renamed or moved, restore it
2. Try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command. ?Then perform a Data Guard role change (failover). ?Available standbys: zhuke_st.

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 ? ? ?Restore and recover datafile 4 ?
? Strategy: The repair includes complete media recovery with no data loss
? Repair script: /u01/app/oracle/diag/rdbms/zhuke_pd/zhuke/hm/reco_3003399314.hm

RMAN> repair failure;----修复错误

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/zhuke_pd/zhuke/hm/reco_3003399314.hm

contents of repair script:
? ?# restore and recover datafile
? ?restore datafile 4;
? ?recover datafile 4;
? ?sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 15-NOV-23
using channel ORA_DISK_1

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 00004 to /u01/app/oracle/oradata/zhuke/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/032bic6s_1_1.BK
channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/032bic6s_1_1.BK tag=TAG20231115T181556
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-NOV-23

Starting recover at 15-NOV-23
using channel ORA_DISK_1

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

Finished recover at 15-NOV-23

sql statement: alter database datafile 4 online
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened


?

文章知识点与官方知识档案匹配,可进一步学习相关知识
————————————————
版权声明:本文为CSDN博主「niceme!」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/m0_70247753/article/details/132415851

文章来源:https://blog.csdn.net/m0_70247753/article/details/135356996
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。