目录
RMAN備份
在CDB下进入RMAN进行备份
备份集
備份整個 CDB數據庫
備份的是cdb$root和所有的pdb和pdbseed
1.首先指定要備份的CDB的sid
export ORACLE_SID=CDB1
2.然后将数据库设为归档模式
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 4294963960 bytes
Fixed Size 9143032 bytes
Variable Size 973078528 bytes
Database Buffers 3305111552 bytes
Redo Buffers 7630848 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
[oracle@oracle19c oradata]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 10 18:58:42 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB (DBID=2260625579, not open)
如果需要經常備份的話我們應該指定一個固定的備份目錄。
RMAN> backup database plus archivelog;--------备份 所有的数据文件,控制文件,spfile文件和归档日志文件
数据文件和归档日志文件都备份到了同一个地方:
以下模拟数据文件丢失,然后用刚才的备份恢复数据库:
1.删除一个pdbusers.dbf表空间文件,打开pdb1的时候报错
RMAN> select status from v$instance;
STATUS
------------
OPEN
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount--------需要将数据库启动到mount状态下恢复
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 838858864 bytes
Fixed Size 9140336 bytes
Variable Size 557842432 bytes
Database Buffers 268435456 bytes
Redo Buffers 3440640 bytes
RMAN> restore database;
Starting restore at 01-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=427 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=428 device type=DISK
skipping datafile 5; already restored to file /u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
skipping datafile 9; already restored to file /u01/app/oracle/oradata/CDB/pdb1/system01.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/CDB/pdb1/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 00003 to /u01/app/oracle/oradata/CDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/CDB/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman_backup/0a2fgsdn_1_1.bk
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /u01/app/oracle/oradata/CDB/system01.dbf
channel ORA_DISK_2: restoring datafile 00007 to /u01/app/oracle/oradata/CDB/users01.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/rman_backup/092fgsdn_1_1.bk
channel ORA_DISK_1: piece handle=/u01/app/oracle/rman_backup/0a2fgsdn_1_1.bk tag=TAG20240101T174526
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
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 00012 to /u01/app/oracle/oradata/CDB/pdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman_backup/0e2fgsfp_1_1.bk
channel ORA_DISK_1: piece handle=/u01/app/oracle/rman_backup/0e2fgsfp_1_1.bk tag=TAG20240101T174526
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/u01/app/oracle/rman_backup/092fgsdn_1_1.bk tag=TAG20240101T174526
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:10
Finished restore at 01-JAN-24
RMAN> recover database;
Starting recover at 01-JAN-24
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 01-JAN-24
查看控制文件和spfile文件的备份:
恢复完成之后pdb1 的users文件又回来了:
備份指定的pdb
RMAN>REPORT SCHEMA;----這個是可以查看當前CDB數據庫有多少個數據文件
RMAN>BACKUP PLUGGABLE DATABASE CUUGPDB1;-----完全备份指定的pdb数据库,还是在CDB下操作
RMAN>RECOVER PLUGGABLE DATABASE CUUGPDB1;-----恢复指定的pdb数据库
RMAN>RECOVER PLUGGABLE DATABASE CUUGPDB1;-----恢复指定的pdb数据库
只备份root容器
RMAN>BACKUP PLUGGABLE DATABASE "CDB$ROOT";------只备份root容器
备份某一个pdb数据库下的指定表空间
RMAN>BACKUP DATABASE CUUGPDB1:USERS;----备份指定的表空间,这个时候oracle就会把users表空间中的所有数据文件,放到指定的备份集里面,备份集里面装的是数据文件的数据块,这只是一个逻辑概念,实际存放数据文件的位置是在备份片,一个备份集至少需要一个备份片
RMAN>RESTORE TABLESPACE USERS;----恢复指定的表空间
镜像备份
RMAN>BACKUP AS COPY DATAFILE 'u01/app/oracle/oradata/user01.dbf';----说白了镜像备份就是直接copy数据文件,在样子在恢复的时候就可以直接把备份出来的数据文件copy回去就可以了
在PDB下面进入rman进行备份
rman target sys/oracle@cuugpdb1-----直接连接到某一个指定的pdb
RMAN>REPORT SCHEMA;----看到的就只有pdb的数据文件
RMAN>BACKUP DATABASE PLUS ARCHIVELOG;--------只备份pdb的数据文件,会跳过归档日志文件,归档是为整个CDB服务的,不是为单个pdb服务的
备份策略
备份窗口时间:允许做备份操作的时间段,一般都是半夜。rman镜像copy的备份文件需要rm删除操作系统文件。
每天都按顺序执行这两条命令(增量更新备份),建议将备份出来的数据放在闪回区。
RMAN> backup incremental level 1 for recover of copy with tag 'daily_inc' database;------第1天做一个增量1级备份,因为前面没有full备份,所以其实就是full备份
RMAN> backup incremental level 1 for recover of copy with tag 'daily_inc' database;------第2天再做一个增量1级备份,此时会产生增量的备份集
RMAN>recover copy of database with tag 'daily_inc';--------第2天还要执行此命令将增量备份应用到full备份,又是一个最新的full备份
RMAN> backup incremental level 1 for recover of copy with tag 'daily_inc' database;--------依次类推,第三天又是做一个增量备份集
实验:
RMAN> backup incremental level 1 for recover of copy with tag 'daily_inc' database;--------做一次增量备份,因为前面没有完全备份,所以这次做的时full备份
RMAN> list backup;------如下显示镜像copy时也自动备份了控制文件和spfile文件
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 1.05M DISK 00:00:01 02-JAN-24
BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20240102T104022
Piece Name: /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_02/o1_mf_s_1157193622_ls6xwpyn_.bkp
SPFILE Included: Modification time: 02-JAN-24
SPFILE db_unique_name: CDB
Control File Included: Ckp SCN: 3609097 Ckp time: 02-JAN-24
查看备份的文件:备份了所有的数据文件(不包含临时数据文件)和控制文件和spfile,备份出来的文件的大小和原本数据文件的大小时一样的
RMAN> backup incremental level 1 for recover of copy with tag 'daily_inc' database;-----再执行一次这个命令,这次就是真的做的增量1级备份,这次备份出来的数据会产生一个备份集
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 1.05M DISK 00:00:01 02-JAN-24
BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20240102T104022
Piece Name: /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_02/o1_mf_s_1157193622_ls6xwpyn_.bkp
SPFILE Included: Modification time: 02-JAN-24
SPFILE db_unique_name: CDB
Control File Included: Ckp SCN: 3609097 Ckp time: 02-JAN-24 //这个是上一次备份的spfile和控制文件
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Incr 1 1.96M DISK 00:00:06 02-JAN-24
BP Key: 18 Status: AVAILABLE Compressed: YES Tag: DAILY_INC
Piece Name: /u01/app/oracle/rman_backup/0u2fj1ed_1_1.bk
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
3 1 Incr 3618236 02-JAN-24 NO /u01/app/oracle/oradata/CDB/sysaux01.dbf
7 1 Incr 3618236 02-JAN-24 NO /u01/app/oracle/oradata/CDB/users01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Incr 1 1.80M DISK 00:00:08 02-JAN-24
BP Key: 19 Status: AVAILABLE Compressed: YES Tag: DAILY_INC
Piece Name: /u01/app/oracle/rman_backup/0t2fj1ed_1_1.bk
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 3618235 02-JAN-24 NO /u01/app/oracle/oradata/CDB/system01.dbf
4 1 Incr 3618235 02-JAN-24 NO /u01/app/oracle/oradata/CDB/undotbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 1.05M DISK 00:00:00 02-JAN-24
BP Key: 20 Status: AVAILABLE Compressed: YES Tag: TAG20240102T132333
Piece Name: /u01/app/oracle/fast_recovery_area/CDB/autobackup/2024_01_02/o1_mf_s_1157203413_ls77goq1_.bkp
SPFILE Included: Modification time: 02-JAN-24
SPFILE db_unique_name: CDB
Control File Included: Ckp SCN: 3618247 Ckp time: 02-JAN-24
查看备份出来的文件:
RMAN> recover copy of database with tag 'daily_inc';----增量备份完之后做一个recover,就是把第二次做的增量备份应用到第一次的full备份,保证备份是处于一个最新的状态
如果此时数据库坏了数据库需要恢复的时间:将数据文件重定向+一天的归档日志恢复的时间,所以最多是一天的时间。
rman备份性能评估
backup validate:分析读取和处理步骤,不将数据写入磁盘
restore validate:读取备份片
可以预估备份和恢复所花费的时间
SQL>SELECT SID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,ROUND(SOFAR/TOTALWORK/100*2)"%_COMPLETE" FROM SESSION_LONGOPS WHERE OPNAME LIKE'RMAN%' AND OPNAME NOT LIKE'%aggregate%' AND TOTALWORK!=0 AND SOFAR <>TOTALWORK;----------rman备份的时候查看备份进程
SQL>SELECT FILENAME ,LONG_WAITS/IO_COUNT FROM v$backup_async_io;------查看io的性能,如果很大的话就代表有问题