OCP-043之：数据库备份操作

于 2024-06-14 09:06:28 发布
阅读量1.3k 收藏 16
点赞数 20
分类专栏： 数据库专栏 文章标签： 数据库 oracle RMAN backup
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wuxiaobing1234/article/details/139669093
版权

1 RMAN备份操作

1.1 基本操作

基本的控制和数据文件备份

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/backup/cs_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/backup/cs_%F';
new RMAN configuration parameters are successfully stored

RMAN> backup database format '/tmp/backup/fulldb_%u%p%s.bak';

Starting backup at 05-DEC-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1144 device type=DISK
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=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/tbs01_001.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00002 name=/u01/app/oracle/tbs003.dbf
channel ORA_DISK_1: starting piece 1 at 05-DEC-22
channel ORA_DISK_1: finished piece 1 at 05-DEC-22
piece handle=/tmp/backup/fulldb_061ek4g616.bak tag=TAG20221205T112454 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 05-DEC-22

Starting Control File and SPFILE Autobackup at 05-DEC-22
piece handle=/tmp/backup/cs_c-1648706630-20221205-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-22

[oracle@ora-server ~]$ ll /tmp/backup/
total 1229088
-rw-r----- 1 oracle oinstall   10698752 Dec  5 11:25 cs_c-1648706630-20221205-00
-rw-r----- 1 oracle oinstall 1247887360 Dec  5 11:25 fulldb_061ek4g616.bak

列出所有备份

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       Full    10.19M     DISK        00:00:00     17-NOV-22      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20221117T144309
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_17/o1_mf_s_1121006589_kqdgmfvj_.bkp
  SPFILE Included: Modification time: 17-NOV-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1539134      Ckp time: 17-NOV-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    10.19M     DISK        00:00:00     19-NOV-22      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20221119T160124
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_19/o1_mf_s_1121184084_kqkvy4dr_.bkp
  SPFILE Included: Modification time: 19-NOV-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1846775      Ckp time: 19-NOV-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    10.19M     DISK        00:00:01     29-NOV-22      
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20221129T162704
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_29/o1_mf_s_1122049624_krd968y9_.bkp
  SPFILE Included: Modification time: 29-NOV-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2743706      Ckp time: 29-NOV-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    10.19M     DISK        00:00:00     29-NOV-22      
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20221129T164706
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_29/o1_mf_s_1122050826_krdbctkg_.bkp
  SPFILE Included: Modification time: 29-NOV-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2746266      Ckp time: 29-NOV-22

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    10.19M     DISK        00:00:00     02-DEC-22      
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20221202T152315
        Piece Name: /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_12_02/o1_mf_s_1122390640_krn2lmbv_.bkp
  SPFILE Included: Modification time: 02-DEC-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2869406      Ckp time: 02-DEC-22

--以上是达到触发条件后，控制文件的自动备份--

--以下是手动备份的数据文件--

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1.16G      DISK        00:00:09     05-DEC-22      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20221205T112454
        Piece Name: /tmp/backup/fulldb_061ek4g616.bak
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 3116228    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 3116228    05-DEC-22              NO    /u01/app/oracle/tbs003.dbf
  3       Full 3116228    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/sysaux01.dbf
  4       Full 3116228    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/undotbs01.dbf
  5       Full 3116228    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/tbs01_001.dbf
  7       Full 3116228    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/users01.dbf

--以下是手动备份的控制文件和参数文件--

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    10.19M     DISK        00:00:00     05-DEC-22      
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20221205T112509
        Piece Name: /tmp/backup/cs_c-1648706630-20221205-00
  SPFILE Included: Modification time: 05-DEC-22
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 3116239      Ckp time: 05-DEC-22

删除所有备份文件

RMAN> delete backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_17/o1_mf_s_1121006589_kqdgmfvj_.bkp
2       2       1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_19/o1_mf_s_1121184084_kqkvy4dr_.bkp
3       3       1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_29/o1_mf_s_1122049624_krd968y9_.bkp
4       4       1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_29/o1_mf_s_1122050826_krdbctkg_.bkp
5       5       1   1   AVAILABLE   DISK        /u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_12_02/o1_mf_s_1122390640_krn2lmbv_.bkp
6       6       1   1   AVAILABLE   DISK        /tmp/backup/fulldb_061ek4g616.bak
7       7       1   1   AVAILABLE   DISK        /tmp/backup/cs_c-1648706630-20221205-00

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_17/o1_mf_s_1121006589_kqdgmfvj_.bkp RECID=1 STAMP=1121006589
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_19/o1_mf_s_1121184084_kqkvy4dr_.bkp RECID=2 STAMP=1121184084
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_29/o1_mf_s_1122049624_krd968y9_.bkp RECID=3 STAMP=1122049624
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_11_29/o1_mf_s_1122050826_krdbctkg_.bkp RECID=4 STAMP=1122050826
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/autobackup/2022_12_02/o1_mf_s_1122390640_krn2lmbv_.bkp RECID=5 STAMP=1122391395
deleted backup piece
backup piece handle=/tmp/backup/fulldb_061ek4g616.bak RECID=6 STAMP=1122636294
deleted backup piece
backup piece handle=/tmp/backup/cs_c-1648706630-20221205-00 RECID=7 STAMP=1122636309
Deleted 7 objects

查看系统认为需要备份的文件

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    0     /u01/app/oracle/oradata/orcl/system01.dbf
2    0     /u01/app/oracle/tbs003.dbf
3    0     /u01/app/oracle/oradata/orcl/sysaux01.dbf
4    0     /u01/app/oracle/oradata/orcl/undotbs01.dbf
5    0     /u01/app/oracle/oradata/orcl/tbs01_001.dbf
7    0     /u01/app/oracle/oradata/orcl/users01.dbf

备份1号文件

RMAN> backup datafile 1;

Starting backup at 05-DEC-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=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 05-DEC-22
channel ORA_DISK_1: finished piece 1 at 05-DEC-22
piece handle=/u01/app/oracle/fast_recovery_area/orcl/ORCL/backupset/2022_12_05/o1_mf_nnndf_TAG20221205T114001_krvln1b8_.bkp tag=TAG20221205T114001 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-DEC-22

Starting Control File and SPFILE Autobackup at 05-DEC-22
piece handle=/tmp/backup/cs_c-1648706630-20221205-01 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-22

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups

--由于冗余策略是1，因此备份一次后，可以看到1号文件消失了，意思是已经满足系统的备份条件，不需要备份了--
File #bkps Name
---- ----- -----------------------------------------------------
2    0     /u01/app/oracle/tbs003.dbf
3    0     /u01/app/oracle/oradata/orcl/sysaux01.dbf
4    0     /u01/app/oracle/oradata/orcl/undotbs01.dbf
5    0     /u01/app/oracle/oradata/orcl/tbs01_001.dbf
7    0     /u01/app/oracle/oradata/orcl/users01.dbf

1.2 Backup Sets

不压缩备份

RMAN> backup as backupset format '/tmp/backup/df_%d_%s_%p.set' tablespace users;

Starting backup at 05-DEC-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=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-DEC-22
channel ORA_DISK_1: finished piece 1 at 05-DEC-22
piece handle=/tmp/backup/df_ORCL_10_1.set tag=TAG20221205T142249 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-DEC-22

Starting Control File and SPFILE Autobackup at 05-DEC-22
piece handle=/tmp/backup/cs_c-1648706630-20221205-02 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-22

压缩备份

RMAN> backup as compressed backupset format '/tmp/backup/df_%d_%s_%p.set' tablespace users;

Starting backup at 05-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-DEC-22
channel ORA_DISK_1: finished piece 1 at 05-DEC-22
piece handle=/tmp/backup/df_ORCL_12_1.set tag=TAG20221205T142343 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-DEC-22

Starting Control File and SPFILE Autobackup at 05-DEC-22
piece handle=/tmp/backup/cs_c-1648706630-20221205-03 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-22

查看备份

RMAN> list backup of datafile 7;

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    1.04M      DISK        00:00:00     05-DEC-22      
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20221205T142249
        Piece Name: /tmp/backup/df_ORCL_10_1.set
  List of Datafiles in backup set 10
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 3123074    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    1.02M      DISK        00:00:00     05-DEC-22      
        BP Key: 12   Status: AVAILABLE  Compressed: YES  Tag: TAG20221205T142343
        Piece Name: /tmp/backup/df_ORCL_12_1.set
  List of Datafiles in backup set 12
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  7       Full 3123206    05-DEC-22              NO    /u01/app/oracle/oradata/orcl/users01.dbf

删除备份：

RMAN> delete backupset 15;				-
最低0.47元/天 解锁文章
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

-风中叮铃-

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值