备份按系统的准备程度分冷备份:shutdown停机拷贝文件不支持724业务热备份:open状态下进行支持724业务备份按数据类型备份分逻辑备份exp、expdp物理备份rman、用户管理的备份[alter tablespace XX begin backupOS拷贝]RMAN备份类型分镜像复制备份backup as copy备份文件和实际文件大小一样 --实际工作中很少使用备份集备份backup as backset备份文件远远小于实际文件大小空块不会备份 --最主流的备份方式RMAN(Recovery Manager)backupset方式的优点1、归档模式下可以在数据库open状态下进行备份保证了7*24的业务2、不使用的空块不会备份减少了I/O消耗3、备份期间会默认检查数据库是否损坏4、可以进行增量备份5、可以实现各种级别的备份比如数据库、表空间、数据文件6、能够恢复到故障点或者离故障点最近的时间点保证数据的最少丢失配置rman默认的备份路径RMAN configure channel device type disk format ‘/home/oracle/bak/%U’;如果没有配置则默认存放在db_recovery_file_dest参数路径下如果没有配置db_recovery_file_dest参数则默认存放在$ORACLE_HOME/dbs目录备份集复制RMAN CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;–配置rman备份时生成两份备份文件RMAN backup copies 2 datafile 4 FORMAT ‘/home/oracle/bak1/%U’,‘/home/oracle/bak2/%U’;–生成两份备份文件一份存放于/home/oracle/bak1目录一份存放于/home/oracle/bak2目录Format字符串替代变量的意思%U是最常用的为每个备份片生成一个唯一名称%F:具体格式为c-IIIIIIIIII-YYYYMMDD-QQ其中IIIIIIIIII表示DBID.YYYYMMDD表示年月日的时间戳QQ表示00开头的十六进制数字序列(最大值为FF即256RMAN备份的保存策略过期的备份文件oracle不会自动从磁盘上删除仅仅是标记过期过期的文件可以使用RMAN delete obsolete直接从磁盘上删除。基于时间的备份保留策略说的简单些就是你希望数据库最早能恢复到几天前如果有增量备份则备份文件的保留时间retention period level 0 backup interval比如每隔一个月即30天执行一次0级备份每隔1天执行一次1级备份如果设置备份保留策略为3天不是说3天以前的所有备份文件都过期了而是说我们可以恢复到3天前。理解每个月的1号执行0级备份今天是3号要恢复到三天前那就是要恢复到上个月的30号上个月的30号的数据是基于上个月1号的0级备份上个月每天的1级备份那么上个月1号的0级备份文件的保留天数就是33033备份集备份片一般情况下一个备份集由一个备份片组成设置备份文件冗余度或出现备份片有大小限制时才会有一个备份集对应多个备份片备份片:用于存储RMAN备份集的物理文件格式。每个逻辑备份集包含一个或多个物理备份件。简单说备份片就是备份后生成的一个个物理文件一个备份片里面可能包含一个或多个datafile。备份集:是个逻辑概念,一个或多个数据文件、控制文件、服务器参数文件和归档重做日志文件的备份。每个备份集由一个或多个二进制文件组成。每个二进制文件称为备份片。备份片是以专有格式编写的只能由RMAN创建或恢复。默认情况下备份集和备份片是一一对应的RMANbackupdatabaseformat/home/oracle/1data%U;select*fromv$backup_setselect*fromv$backup_piece设置备份集大小时备份集和备份片是一对多的RMANCONFIGURE MAXSETSIZE clear;RMANrun2{allocate channel c1 devicetypediskmaxpiecesize1000M;3backupdatabaseformat/home/oracle/2data%U;4}select*fromv$backup_setselect*fromv$backup_piece增量备份默认是Differential差异增量备份备份上级及同级以来所有变化的数据块Cumulative累积增量备份备份上级以来所有变化的数据块就算没有0级备份直接在执行1级或2级备份会自动先进行0级备份。RMANbackupincrementallevel2databaseformat/home/oracle/bak/%U;Startingbackupat24-MAY-18usingtargetdatabasecontrolfileinsteadofrecovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID32devicetypeDISKnoparentbackuporcopyofdatafile1foundnoparentbackuporcopyofdatafile2foundnoparentbackuporcopyofdatafile5foundnoparentbackuporcopyofdatafile3foundnoparentbackuporcopyofdatafile4found channel ORA_DISK_1:startingincrementallevel0datafilebackupsetchannel ORA_DISK_1: specifying datafile(s)inbackupsetinput datafilefilenumber00001name/u01/app/oracle/oradata/orcl/system01.dbf input datafilefilenumber00002name/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafilefilenumber00005name/u01/app/oracle/oradata/orcl/example01.dbf input datafilefilenumber00003name/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafilefilenumber00004name/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1:startingpiece1at24-MAY-18channel ORA_DISK_1: finished piece1at24-MAY-18piece handle/home/oracle/bak/1at3lltr_1_1 tagTAG20180524T024722commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:01:24channel ORA_DISK_1:startingincrementallevel2datafilebackupsetchannel ORA_DISK_1: specifying datafile(s)inbackupsetinput datafilefilenumber00006name/u01/app/oracle/oradata/orcl/users02.dbf input datafilefilenumber00007name/u01/app/oracle/oradata/orcl/users03.dbf input datafilefilenumber00008name/u01/app/oracle/oradata/orcl/cataspace.dbf channel ORA_DISK_1:startingpiece1at24-MAY-18channel ORA_DISK_1: finished piece1at24-MAY-18piece handle/home/oracle/bak/1bt3lm0g_1_1 tagTAG20180524T024722commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:03Finishedbackupat24-MAY-18StartingControlFileandSPFILE Autobackup at24-MAY-18piece handle/home/oracle/bak/c-1498036209-20180524-00commentNONE Finished ControlFileandSPFILE Autobackup at24-MAY-18Rman备份控制文件只要对1号数据文件即SYSTEM 表空间的数据文件做备份时就算控制文件的自动备份没有打开RMAN也会自动对控制文件做备份。所以备份数据库时也一定会自动备份控制文件因为数据库包含1号数据文件。Rman备份归档日志如果多个归档路径时rman备份时也只备份一份delete all input --如果有多个归档路径则删除所有路径下的归档日志delete input --如果有多个归档路径只删除一个路径下的归档日志新的归档日志目录不同于默认的归档日志目录如果Oracle 判定日志已存在也不会恢复该归档日志文件。RMANrun{2setarchivelog destinationto/home/oracle;3restorearchivelogfromsequence34until sequence34;4} executing command:SETARCHIVELOG DESTINATIONStartingrestoreat15-MAY-18usingchannel ORA_DISK_1usingchannel ORA_DISK_2 archived logforthread1withsequence34isalreadyondiskasfile/u01/app/oracle/archivelog/1_34_970255476.dbfrestorenotdone;allfilesreadonly,offline,oralready restored Finishedrestoreat15-MAY-18catalog backuppieceRMAN catalog backuppiece ‘’如果重建控制文件里面的备份信息都丢失了的话就算没有使用catalog数据库可以使用catalog命令把备份文件注册到控制文件中。查看备份进度的SQL语句SELECTSID,SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||% %_COMPLETE,TRUNC(elapsed_seconds/60)||:||MOD(elapsed_seconds,60)elapsed,TRUNC(time_remaining/60)||:||MOD(time_remaining,60)remaining,CONTEXT,target,SOFAR,TOTALWORKFROMV$SESSION_LONGOPSWHEREOPNAMELIKERMAN%ANDOPNAMENOTLIKE%aggregate%ANDTOTALWORK!0ANDSOFARTOTALWORK;restore和recover的区别restore:还原把数据文件从备份集中还原出来restore默认从控制文件中最新的一个备份包中恢复如果最新的不存在就找第二新的如果第二新的不存在就找第三新的依次类推如果一个都没有就报错restore有until time则是使用until time范围内最新的备份包比如3个备份包分别是1:00,2:00,3:00如果UNTIL TIME “TO_DATE(‘2:30:00’,‘hh24:mi:ss’)”那就是使用2:30范围内的最新包那就是2:00的那个备份包recover恢复使用归档日志或在线日志恢复rman的advisor工具RmanList failure;–显示异常的文件数量RmanAdvise failure;–显示需要恢复的文件详细信息RmanRepair failure preview;–显示需要恢复操作的脚本并把脚本存放到某个路径下,当没有产生报告时敲一下Validate database再重新来一遍List failure\ Advise failureRmanRepair failure;–进行恢复操作备份一个文件再关机再删除这个文件再启动到mount执行上面的顺序操作就可以恢复VALIDATE 命令VALIDATE 命令支持对单个备份集和数据块进行验证。在物理损坏中数据库根本无法识别块。在逻辑损坏中块的内容在逻辑上不一致。默认情况下VALIDATE 命令只检查物理损坏。也可以指定CHECK LOGICAL 检查逻辑损坏。VALIDATE 命令只检查块内损坏。块损坏可分为块间损坏和块内损坏。在块内损坏中块本身发生损坏可以是物理损坏也可以是逻辑损坏。在块间损坏中块与块之间发生的损坏只能是逻辑损坏。RMAN VALIDATE DATABASE;–检查物理坏块只做检查动作RMAN VALIDATE CHECK LOGICAL DATABASE;–检查逻辑坏块只做检查动作或RMAN BACKUP VALIDATE DATABASE;–检查物理坏块只检查不备份RMAN BACKUP VALIDATE CHECK LOGICAL DATABASE;–检查逻辑坏块只检查不备份RMAN BACKUP DATABASE;–没有VILIDATE表示做备份的动作备份的同时检查物理坏块RMAN备份时默认检查物理坏块RMAN BACKUP CHECK LOGICAL DATABASE;–没有VILIDATE表示做备份的动作备份的同时检查逻辑坏块CATALOG当没有CATALOG恢复目录时RMAN相关的备份信息比如归档文件路径、备份集路径等均存储在目标数据库的控制文件中不过考虑到控制文件并不能无限增长而且控制文件也不仅仅是用来存储与备份相关的信息因此RMAN也有一个专门的备份信息存储地这就是CATALOG恢复目录了。当待备份的数据库注册到恢复目录之后RMAN相关的信息除了保存在控制文件中外控制文件实际上只保存一部分更加详细的信息就都被存储在恢复目录中。建议将恢复目录存放在一个专有的数据库中。如果恢复目录和其他数据存储在一个数据库中那么如果这个数据库损坏你的恢复目录很有可能也会丢失。同样如果选择使用恢复目录方式也同样要对恢复目录做备份。恢复目录一般不需要用RMAN做备份只要EXPDP即可。一旦CATALOG恢复目录数据库发生故障通过IMPDP恢复即可。Nocatalog就是用control file控制文件记录备份信息如果使用nocatalog方式备份文件的保留时间retention period level 0 backup interval控制文件记录保留时间control_file_record_keep_time备份文件的保留时间1比如每隔一个月即30天执行一次0级备份每隔1天执行一次1级备份如果设置备份保留策略为3天就是说我们可以恢复到3天前。控制文件中记录保留天数就是330134CATALOG创建和使用步骤1.创建表空间SQL create tablespace cataspace datafile ‘/u01/app/oracle/oradata/ocp/cataspace.dbf’ size 10M autoextend on;2.创建用户SQL create user catauser identified by 123456 default tablespace cataspace;3.用户授权必须要有recovery_catalog_ownerSQL grant connect,resource,recovery_catalog_owner to catauser;4.RMAN登录CATALOG并创建CATALOG[oracleocp ~]$ rman catalog catauser/123456RMAN create catalog;5.RMAN登录数据库和CATALOG并注册数据库到CATALOG并使用。[oracleocp ~]$ rman target / catalog catauser/123456RMAN register database;RMAN backup datafile 7;设置归档模式SQL startup mount;SQL alter database archivelog;SQL alter database open;SQL alter system set log_archive_dest_1‘location/u01/app/oracle/archivelog’;SQL archive log list;SQL alter system switch logfile;SQL alter system archive log current;查看RMAN的配置RMANshowall;usingtargetdatabasecontrolfileinsteadofrecovery catalog RMAN configuration parametersfordatabasewithdb_unique_name DB1 are: CONFIGURE RETENTION POLICYTOREDUNDANCY1;# defaultCONFIGUREBACKUPOPTIMIZATIONOFF;# defaultCONFIGUREDEFAULTDEVICETYPETODISK;# defaultCONFIGURE CONTROLFILE AUTOBACKUPON;# defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMATFORDEVICETYPEDISKTO%F;# defaultCONFIGURE DEVICETYPEDISKPARALLELISM1BACKUPTYPETOBACKUPSET;# defaultCONFIGURE DATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;# defaultCONFIGURE ARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;# defaultCONFIGURE MAXSETSIZETOUNLIMITED;# defaultCONFIGURE ENCRYPTIONFORDATABASEOFF;# defaultCONFIGURE ENCRYPTIONALGORITHMAES128;# defaultCONFIGURE COMPRESSIONALGORITHMBASICASOFRELEASEDEFAULTOPTIMIZEFORLOADTRUE;# defaultCONFIGURE RMAN OUTPUTTOKEEPFOR7DAYS;# defaultCONFIGURE ARCHIVELOG DELETION POLICYTONONE;# defaultCONFIGURESNAPSHOTCONTROLFILE NAMETO/u01/app/oracle/product/19.3.0/db_1/dbs/snapcf_DB1.f;# defaultRMAN配置备份文件保留30天即可以恢复到30天之前。RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;表空间的备份恢复使用alter tablespace xx offline immediate方式RMANRUN{sqlalter tablespace xx offline immediate;restoretablespacexx;recovertablespacexx;sqlalter tablespace xx online;}datafile的备份恢复可以使用脱机对应表空间的方式alter tablespace tablespacename offline也可以直接脱机数据文件datafile offline的方式进行restore和recoverSQL alter database datafile N offline;RMANrestore datafile N;RMANrecover datafile N;SQL alter database datafile N online;最全面最简洁的备份脚本和恢复方法:RMANCONFIGURE CONTROLFILE AUTOBACKUPON;RMANCONFIGURE CONTROLFILE AUTOBACKUP FORMATFORDEVICETYPEDISKTO/u01/backup/%F;RMANbackupdatabaseformat/u01/backup/datafile%Uplus archivelog format/u01/backup/arch%Udeleteallinput;以上脚本备份步骤如下1、归档再备份归档日志删除已备份的归档日志2、备份数据库文件3、归档再备份归档日志删除已备份的归档日志4、备份控制文件和参数文件RMANbackupdatabaseformat/u01/backup/datafile%Uplus archivelog format/u01/backup/arch%Udeleteallinput;Startingbackupat21-MAY-25currentlog archivedusingchannel ORA_DISK_1 channel ORA_DISK_1:startingarchived logbackupsetchannel ORA_DISK_1: specifying archived log(s)inbackupsetinput archived log thread1sequence24RECID9STAMP1201708288channel ORA_DISK_1:startingpiece1at21-MAY-25channel ORA_DISK_1: finished piece1at21-MAY-25piece handle/u01/backup/arch053q1781_1_1 tagTAG20250521T155128commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01channel ORA_DISK_1: deleting archived log(s)archived logfilename/u01/app/oracle/archivelog/1_24_1200999124.dbf RECID9STAMP1201708288Finishedbackupat21-MAY-25Startingbackupat21-MAY-25usingchannel ORA_DISK_1 channel ORA_DISK_1:startingfulldatafilebackupsetchannel ORA_DISK_1: specifying datafile(s)inbackupsetinput datafilefilenumber00001name/u01/app/oracle/oradata/DB1/system01.dbf input datafilefilenumber00003name/u01/app/oracle/oradata/DB1/sysaux01.dbf input datafilefilenumber00004name/u01/app/oracle/oradata/DB1/undotbs01.dbf input datafilefilenumber00007name/u01/app/oracle/oradata/DB1/users01.dbf channel ORA_DISK_1:startingpiece1at21-MAY-25channel ORA_DISK_1: finished piece1at21-MAY-25piece handle/u01/backup/datafile063q1782_1_1 tagTAG20250521T155130commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:01:06Finishedbackupat21-MAY-25Startingbackupat21-MAY-25currentlog archivedusingchannel ORA_DISK_1 channel ORA_DISK_1:startingarchived logbackupsetchannel ORA_DISK_1: specifying archived log(s)inbackupsetinput archived log thread1sequence25RECID10STAMP1201708357channel ORA_DISK_1:startingpiece1at21-MAY-25channel ORA_DISK_1: finished piece1at21-MAY-25piece handle/u01/backup/arch073q17a5_1_1 tagTAG20250521T155237commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01channel ORA_DISK_1: deleting archived log(s)archived logfilename/u01/app/oracle/archivelog/1_25_1200999124.dbf RECID10STAMP1201708357Finishedbackupat21-MAY-25StartingControlFileandSPFILE Autobackup at21-MAY-25piece handle/u01/backup/c-1794862737-20250521-01commentNONE Finished ControlFileandSPFILE Autobackup at21-MAY-25假设所有文件都丢失了恢复步骤如下1、恢复spfile文件rman target /RMAN startup nomount;RMAN restore spfile from ‘XX’;2、启动数据库到nomount状态sqlplus / as sysdbaSQL shutdown abort;SQL startup nomount;3、恢复控制文件rman target /RMAN restore controlfile from ‘/u01/backup/c-1794862737-20250521-01’;4、启动数据库到mount状态sqlplus / as sysdbaSQL alter database mount;5、restore数据库、recover不完全恢复数据库RMAN restore database;RMAN recover database;RMAN recover database until sequence XX;–XX使用上一步recover database中提示需要的sequence6、启动数据库到open状态使用resetlogssqlplus / as sysdbaSQL alter database open resetlogs;备份脚本的一个示例vi /rmanbackup/scripts/backupLev0.shDATEdate%Y%m%drman target/cmdfile/rmanbackup/scripts/rmanBackupLev0.rmanmsglog/rmanbackup/log/rmanBackupLev0_$DATE.logvi/rmanbackup/scripts/rmanBackupLev0.rman run{ALLOCATE CHANNEL ch1 TYPE disk;BACKUP INCREMENTAL LEVEL0TAGDB0DATABASE FORMAT/rmanbackup/data/DB_0_%T_%s_%UPLUS ARCHIVELOG FORMAT/rmanbackup/archivelog/ARCH_0_%T_%sDELETE ALL INPUT;RELEASE CHANNEL ch1;CROSSCHECK BACKUP;CROSSCHECK ARCHIVELOG ALL;DELETE NOPROMPT EXPIRED BACKUP;DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;REPORT OBSOLETE;DELETE NOPROMPT OBSOLETE;}vi/rmanbackup/scripts/backupLev0_log.shDATEdate-d-1 day%Y%m%dmail_listXXYY.COMcat/rmanbackup/log/rmanBackupLev0_$DATE.log|mailx-sXXDB IP 0级备份过程明细$mail_list
备份按系统的准备程度分冷备份:shutdown停机拷贝文件不支持724业务热备份:open状态下进行支持724业务备份按数据类型备份分逻辑备份exp、expdp物理备份rman、用户管理的备份[alter tablespace XX begin backupOS拷贝]RMAN备份类型分镜像复制备份backup as copy备份文件和实际文件大小一样 --实际工作中很少使用备份集备份backup as backset备份文件远远小于实际文件大小空块不会备份 --最主流的备份方式RMAN(Recovery Manager)backupset方式的优点1、归档模式下可以在数据库open状态下进行备份保证了7*24的业务2、不使用的空块不会备份减少了I/O消耗3、备份期间会默认检查数据库是否损坏4、可以进行增量备份5、可以实现各种级别的备份比如数据库、表空间、数据文件6、能够恢复到故障点或者离故障点最近的时间点保证数据的最少丢失配置rman默认的备份路径RMAN configure channel device type disk format ‘/home/oracle/bak/%U’;如果没有配置则默认存放在db_recovery_file_dest参数路径下如果没有配置db_recovery_file_dest参数则默认存放在$ORACLE_HOME/dbs目录备份集复制RMAN CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;–配置rman备份时生成两份备份文件RMAN backup copies 2 datafile 4 FORMAT ‘/home/oracle/bak1/%U’,‘/home/oracle/bak2/%U’;–生成两份备份文件一份存放于/home/oracle/bak1目录一份存放于/home/oracle/bak2目录Format字符串替代变量的意思%U是最常用的为每个备份片生成一个唯一名称%F:具体格式为c-IIIIIIIIII-YYYYMMDD-QQ其中IIIIIIIIII表示DBID.YYYYMMDD表示年月日的时间戳QQ表示00开头的十六进制数字序列(最大值为FF即256RMAN备份的保存策略过期的备份文件oracle不会自动从磁盘上删除仅仅是标记过期过期的文件可以使用RMAN delete obsolete直接从磁盘上删除。基于时间的备份保留策略说的简单些就是你希望数据库最早能恢复到几天前如果有增量备份则备份文件的保留时间retention period level 0 backup interval比如每隔一个月即30天执行一次0级备份每隔1天执行一次1级备份如果设置备份保留策略为3天不是说3天以前的所有备份文件都过期了而是说我们可以恢复到3天前。理解每个月的1号执行0级备份今天是3号要恢复到三天前那就是要恢复到上个月的30号上个月的30号的数据是基于上个月1号的0级备份上个月每天的1级备份那么上个月1号的0级备份文件的保留天数就是33033备份集备份片一般情况下一个备份集由一个备份片组成设置备份文件冗余度或出现备份片有大小限制时才会有一个备份集对应多个备份片备份片:用于存储RMAN备份集的物理文件格式。每个逻辑备份集包含一个或多个物理备份件。简单说备份片就是备份后生成的一个个物理文件一个备份片里面可能包含一个或多个datafile。备份集:是个逻辑概念,一个或多个数据文件、控制文件、服务器参数文件和归档重做日志文件的备份。每个备份集由一个或多个二进制文件组成。每个二进制文件称为备份片。备份片是以专有格式编写的只能由RMAN创建或恢复。默认情况下备份集和备份片是一一对应的RMANbackupdatabaseformat/home/oracle/1data%U;select*fromv$backup_setselect*fromv$backup_piece设置备份集大小时备份集和备份片是一对多的RMANCONFIGURE MAXSETSIZE clear;RMANrun2{allocate channel c1 devicetypediskmaxpiecesize1000M;3backupdatabaseformat/home/oracle/2data%U;4}select*fromv$backup_setselect*fromv$backup_piece增量备份默认是Differential差异增量备份备份上级及同级以来所有变化的数据块Cumulative累积增量备份备份上级以来所有变化的数据块就算没有0级备份直接在执行1级或2级备份会自动先进行0级备份。RMANbackupincrementallevel2databaseformat/home/oracle/bak/%U;Startingbackupat24-MAY-18usingtargetdatabasecontrolfileinsteadofrecovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID32devicetypeDISKnoparentbackuporcopyofdatafile1foundnoparentbackuporcopyofdatafile2foundnoparentbackuporcopyofdatafile5foundnoparentbackuporcopyofdatafile3foundnoparentbackuporcopyofdatafile4found channel ORA_DISK_1:startingincrementallevel0datafilebackupsetchannel ORA_DISK_1: specifying datafile(s)inbackupsetinput datafilefilenumber00001name/u01/app/oracle/oradata/orcl/system01.dbf input datafilefilenumber00002name/u01/app/oracle/oradata/orcl/sysaux01.dbf input datafilefilenumber00005name/u01/app/oracle/oradata/orcl/example01.dbf input datafilefilenumber00003name/u01/app/oracle/oradata/orcl/undotbs01.dbf input datafilefilenumber00004name/u01/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_1:startingpiece1at24-MAY-18channel ORA_DISK_1: finished piece1at24-MAY-18piece handle/home/oracle/bak/1at3lltr_1_1 tagTAG20180524T024722commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:01:24channel ORA_DISK_1:startingincrementallevel2datafilebackupsetchannel ORA_DISK_1: specifying datafile(s)inbackupsetinput datafilefilenumber00006name/u01/app/oracle/oradata/orcl/users02.dbf input datafilefilenumber00007name/u01/app/oracle/oradata/orcl/users03.dbf input datafilefilenumber00008name/u01/app/oracle/oradata/orcl/cataspace.dbf channel ORA_DISK_1:startingpiece1at24-MAY-18channel ORA_DISK_1: finished piece1at24-MAY-18piece handle/home/oracle/bak/1bt3lm0g_1_1 tagTAG20180524T024722commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:03Finishedbackupat24-MAY-18StartingControlFileandSPFILE Autobackup at24-MAY-18piece handle/home/oracle/bak/c-1498036209-20180524-00commentNONE Finished ControlFileandSPFILE Autobackup at24-MAY-18Rman备份控制文件只要对1号数据文件即SYSTEM 表空间的数据文件做备份时就算控制文件的自动备份没有打开RMAN也会自动对控制文件做备份。所以备份数据库时也一定会自动备份控制文件因为数据库包含1号数据文件。Rman备份归档日志如果多个归档路径时rman备份时也只备份一份delete all input --如果有多个归档路径则删除所有路径下的归档日志delete input --如果有多个归档路径只删除一个路径下的归档日志新的归档日志目录不同于默认的归档日志目录如果Oracle 判定日志已存在也不会恢复该归档日志文件。RMANrun{2setarchivelog destinationto/home/oracle;3restorearchivelogfromsequence34until sequence34;4} executing command:SETARCHIVELOG DESTINATIONStartingrestoreat15-MAY-18usingchannel ORA_DISK_1usingchannel ORA_DISK_2 archived logforthread1withsequence34isalreadyondiskasfile/u01/app/oracle/archivelog/1_34_970255476.dbfrestorenotdone;allfilesreadonly,offline,oralready restored Finishedrestoreat15-MAY-18catalog backuppieceRMAN catalog backuppiece ‘’如果重建控制文件里面的备份信息都丢失了的话就算没有使用catalog数据库可以使用catalog命令把备份文件注册到控制文件中。查看备份进度的SQL语句SELECTSID,SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||% %_COMPLETE,TRUNC(elapsed_seconds/60)||:||MOD(elapsed_seconds,60)elapsed,TRUNC(time_remaining/60)||:||MOD(time_remaining,60)remaining,CONTEXT,target,SOFAR,TOTALWORKFROMV$SESSION_LONGOPSWHEREOPNAMELIKERMAN%ANDOPNAMENOTLIKE%aggregate%ANDTOTALWORK!0ANDSOFARTOTALWORK;restore和recover的区别restore:还原把数据文件从备份集中还原出来restore默认从控制文件中最新的一个备份包中恢复如果最新的不存在就找第二新的如果第二新的不存在就找第三新的依次类推如果一个都没有就报错restore有until time则是使用until time范围内最新的备份包比如3个备份包分别是1:00,2:00,3:00如果UNTIL TIME “TO_DATE(‘2:30:00’,‘hh24:mi:ss’)”那就是使用2:30范围内的最新包那就是2:00的那个备份包recover恢复使用归档日志或在线日志恢复rman的advisor工具RmanList failure;–显示异常的文件数量RmanAdvise failure;–显示需要恢复的文件详细信息RmanRepair failure preview;–显示需要恢复操作的脚本并把脚本存放到某个路径下,当没有产生报告时敲一下Validate database再重新来一遍List failure\ Advise failureRmanRepair failure;–进行恢复操作备份一个文件再关机再删除这个文件再启动到mount执行上面的顺序操作就可以恢复VALIDATE 命令VALIDATE 命令支持对单个备份集和数据块进行验证。在物理损坏中数据库根本无法识别块。在逻辑损坏中块的内容在逻辑上不一致。默认情况下VALIDATE 命令只检查物理损坏。也可以指定CHECK LOGICAL 检查逻辑损坏。VALIDATE 命令只检查块内损坏。块损坏可分为块间损坏和块内损坏。在块内损坏中块本身发生损坏可以是物理损坏也可以是逻辑损坏。在块间损坏中块与块之间发生的损坏只能是逻辑损坏。RMAN VALIDATE DATABASE;–检查物理坏块只做检查动作RMAN VALIDATE CHECK LOGICAL DATABASE;–检查逻辑坏块只做检查动作或RMAN BACKUP VALIDATE DATABASE;–检查物理坏块只检查不备份RMAN BACKUP VALIDATE CHECK LOGICAL DATABASE;–检查逻辑坏块只检查不备份RMAN BACKUP DATABASE;–没有VILIDATE表示做备份的动作备份的同时检查物理坏块RMAN备份时默认检查物理坏块RMAN BACKUP CHECK LOGICAL DATABASE;–没有VILIDATE表示做备份的动作备份的同时检查逻辑坏块CATALOG当没有CATALOG恢复目录时RMAN相关的备份信息比如归档文件路径、备份集路径等均存储在目标数据库的控制文件中不过考虑到控制文件并不能无限增长而且控制文件也不仅仅是用来存储与备份相关的信息因此RMAN也有一个专门的备份信息存储地这就是CATALOG恢复目录了。当待备份的数据库注册到恢复目录之后RMAN相关的信息除了保存在控制文件中外控制文件实际上只保存一部分更加详细的信息就都被存储在恢复目录中。建议将恢复目录存放在一个专有的数据库中。如果恢复目录和其他数据存储在一个数据库中那么如果这个数据库损坏你的恢复目录很有可能也会丢失。同样如果选择使用恢复目录方式也同样要对恢复目录做备份。恢复目录一般不需要用RMAN做备份只要EXPDP即可。一旦CATALOG恢复目录数据库发生故障通过IMPDP恢复即可。Nocatalog就是用control file控制文件记录备份信息如果使用nocatalog方式备份文件的保留时间retention period level 0 backup interval控制文件记录保留时间control_file_record_keep_time备份文件的保留时间1比如每隔一个月即30天执行一次0级备份每隔1天执行一次1级备份如果设置备份保留策略为3天就是说我们可以恢复到3天前。控制文件中记录保留天数就是330134CATALOG创建和使用步骤1.创建表空间SQL create tablespace cataspace datafile ‘/u01/app/oracle/oradata/ocp/cataspace.dbf’ size 10M autoextend on;2.创建用户SQL create user catauser identified by 123456 default tablespace cataspace;3.用户授权必须要有recovery_catalog_ownerSQL grant connect,resource,recovery_catalog_owner to catauser;4.RMAN登录CATALOG并创建CATALOG[oracleocp ~]$ rman catalog catauser/123456RMAN create catalog;5.RMAN登录数据库和CATALOG并注册数据库到CATALOG并使用。[oracleocp ~]$ rman target / catalog catauser/123456RMAN register database;RMAN backup datafile 7;设置归档模式SQL startup mount;SQL alter database archivelog;SQL alter database open;SQL alter system set log_archive_dest_1‘location/u01/app/oracle/archivelog’;SQL archive log list;SQL alter system switch logfile;SQL alter system archive log current;查看RMAN的配置RMANshowall;usingtargetdatabasecontrolfileinsteadofrecovery catalog RMAN configuration parametersfordatabasewithdb_unique_name DB1 are: CONFIGURE RETENTION POLICYTOREDUNDANCY1;# defaultCONFIGUREBACKUPOPTIMIZATIONOFF;# defaultCONFIGUREDEFAULTDEVICETYPETODISK;# defaultCONFIGURE CONTROLFILE AUTOBACKUPON;# defaultCONFIGURE CONTROLFILE AUTOBACKUP FORMATFORDEVICETYPEDISKTO%F;# defaultCONFIGURE DEVICETYPEDISKPARALLELISM1BACKUPTYPETOBACKUPSET;# defaultCONFIGURE DATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;# defaultCONFIGURE ARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;# defaultCONFIGURE MAXSETSIZETOUNLIMITED;# defaultCONFIGURE ENCRYPTIONFORDATABASEOFF;# defaultCONFIGURE ENCRYPTIONALGORITHMAES128;# defaultCONFIGURE COMPRESSIONALGORITHMBASICASOFRELEASEDEFAULTOPTIMIZEFORLOADTRUE;# defaultCONFIGURE RMAN OUTPUTTOKEEPFOR7DAYS;# defaultCONFIGURE ARCHIVELOG DELETION POLICYTONONE;# defaultCONFIGURESNAPSHOTCONTROLFILE NAMETO/u01/app/oracle/product/19.3.0/db_1/dbs/snapcf_DB1.f;# defaultRMAN配置备份文件保留30天即可以恢复到30天之前。RMAN CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;表空间的备份恢复使用alter tablespace xx offline immediate方式RMANRUN{sqlalter tablespace xx offline immediate;restoretablespacexx;recovertablespacexx;sqlalter tablespace xx online;}datafile的备份恢复可以使用脱机对应表空间的方式alter tablespace tablespacename offline也可以直接脱机数据文件datafile offline的方式进行restore和recoverSQL alter database datafile N offline;RMANrestore datafile N;RMANrecover datafile N;SQL alter database datafile N online;最全面最简洁的备份脚本和恢复方法:RMANCONFIGURE CONTROLFILE AUTOBACKUPON;RMANCONFIGURE CONTROLFILE AUTOBACKUP FORMATFORDEVICETYPEDISKTO/u01/backup/%F;RMANbackupdatabaseformat/u01/backup/datafile%Uplus archivelog format/u01/backup/arch%Udeleteallinput;以上脚本备份步骤如下1、归档再备份归档日志删除已备份的归档日志2、备份数据库文件3、归档再备份归档日志删除已备份的归档日志4、备份控制文件和参数文件RMANbackupdatabaseformat/u01/backup/datafile%Uplus archivelog format/u01/backup/arch%Udeleteallinput;Startingbackupat21-MAY-25currentlog archivedusingchannel ORA_DISK_1 channel ORA_DISK_1:startingarchived logbackupsetchannel ORA_DISK_1: specifying archived log(s)inbackupsetinput archived log thread1sequence24RECID9STAMP1201708288channel ORA_DISK_1:startingpiece1at21-MAY-25channel ORA_DISK_1: finished piece1at21-MAY-25piece handle/u01/backup/arch053q1781_1_1 tagTAG20250521T155128commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01channel ORA_DISK_1: deleting archived log(s)archived logfilename/u01/app/oracle/archivelog/1_24_1200999124.dbf RECID9STAMP1201708288Finishedbackupat21-MAY-25Startingbackupat21-MAY-25usingchannel ORA_DISK_1 channel ORA_DISK_1:startingfulldatafilebackupsetchannel ORA_DISK_1: specifying datafile(s)inbackupsetinput datafilefilenumber00001name/u01/app/oracle/oradata/DB1/system01.dbf input datafilefilenumber00003name/u01/app/oracle/oradata/DB1/sysaux01.dbf input datafilefilenumber00004name/u01/app/oracle/oradata/DB1/undotbs01.dbf input datafilefilenumber00007name/u01/app/oracle/oradata/DB1/users01.dbf channel ORA_DISK_1:startingpiece1at21-MAY-25channel ORA_DISK_1: finished piece1at21-MAY-25piece handle/u01/backup/datafile063q1782_1_1 tagTAG20250521T155130commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:01:06Finishedbackupat21-MAY-25Startingbackupat21-MAY-25currentlog archivedusingchannel ORA_DISK_1 channel ORA_DISK_1:startingarchived logbackupsetchannel ORA_DISK_1: specifying archived log(s)inbackupsetinput archived log thread1sequence25RECID10STAMP1201708357channel ORA_DISK_1:startingpiece1at21-MAY-25channel ORA_DISK_1: finished piece1at21-MAY-25piece handle/u01/backup/arch073q17a5_1_1 tagTAG20250521T155237commentNONE channel ORA_DISK_1:backupsetcomplete,elapsedtime:00:00:01channel ORA_DISK_1: deleting archived log(s)archived logfilename/u01/app/oracle/archivelog/1_25_1200999124.dbf RECID10STAMP1201708357Finishedbackupat21-MAY-25StartingControlFileandSPFILE Autobackup at21-MAY-25piece handle/u01/backup/c-1794862737-20250521-01commentNONE Finished ControlFileandSPFILE Autobackup at21-MAY-25假设所有文件都丢失了恢复步骤如下1、恢复spfile文件rman target /RMAN startup nomount;RMAN restore spfile from ‘XX’;2、启动数据库到nomount状态sqlplus / as sysdbaSQL shutdown abort;SQL startup nomount;3、恢复控制文件rman target /RMAN restore controlfile from ‘/u01/backup/c-1794862737-20250521-01’;4、启动数据库到mount状态sqlplus / as sysdbaSQL alter database mount;5、restore数据库、recover不完全恢复数据库RMAN restore database;RMAN recover database;RMAN recover database until sequence XX;–XX使用上一步recover database中提示需要的sequence6、启动数据库到open状态使用resetlogssqlplus / as sysdbaSQL alter database open resetlogs;备份脚本的一个示例vi /rmanbackup/scripts/backupLev0.shDATEdate%Y%m%drman target/cmdfile/rmanbackup/scripts/rmanBackupLev0.rmanmsglog/rmanbackup/log/rmanBackupLev0_$DATE.logvi/rmanbackup/scripts/rmanBackupLev0.rman run{ALLOCATE CHANNEL ch1 TYPE disk;BACKUP INCREMENTAL LEVEL0TAGDB0DATABASE FORMAT/rmanbackup/data/DB_0_%T_%s_%UPLUS ARCHIVELOG FORMAT/rmanbackup/archivelog/ARCH_0_%T_%sDELETE ALL INPUT;RELEASE CHANNEL ch1;CROSSCHECK BACKUP;CROSSCHECK ARCHIVELOG ALL;DELETE NOPROMPT EXPIRED BACKUP;DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;REPORT OBSOLETE;DELETE NOPROMPT OBSOLETE;}vi/rmanbackup/scripts/backupLev0_log.shDATEdate-d-1 day%Y%m%dmail_listXXYY.COMcat/rmanbackup/log/rmanBackupLev0_$DATE.log|mailx-sXXDB IP 0级备份过程明细$mail_list
