Oracle 11g 使用RMAN备份数据库
使用Oracle自带的备份恢复工具RMAN(Recovery Manager)具有以下几种优势:
配置快闪恢复区(flash recovery area) 快闪恢复区是存储备份和恢复数据文件以及相关信息的存储区。需要设置两个参数:db_recovery_file_dest_size(快闪恢复区的最大容量),db_recovery_file_dest(快闪恢复区所在的路径)。 查看快闪恢复区信息: SQL>showparameterdb_recovery_file_dest; NAMETYPEVALUE ----------------------------------------------------------------------------- db_recovery_file_deststring/data/app/oracle/recovery_area db_recovery_file_dest_sizebiginteger3882M 修改快闪恢复区大小: SQL>altersystemsetdb_recovery_file_dest_size=2g; Systemaltered. SQL>showparameterdb_recovery_file_dest; NAMETYPEVALUE ----------------------------------------------------------------------------- db_recovery_file_deststring/data/app/oracle/recovery_area db_recovery_file_dest_sizebiginteger2G 查看使用情况: SQL>selectname,space_limit,space_used,number_of_filesfromv$recovery_file_dest; NAME -------------------------------------------------------------------------------- SPACE_LIMITSPACE_USEDNUMBER_OF_FILES ------------------------------------ /data/app/oracle/recovery_area 214748364800 显示没有使用。 也可以使用此命令查看详细信息: SQL>selectfile_type,percent_space_used,percent_space_reclaimable,number_of_filesfromv$flash_recovery_area_usage; 建立RMAN到数据库的连接 创建rman用户,并授权: [oracle@temp-testoracle]$sqlplus/nolog SQL>connect/assysdba Connected. SQL>createuserrmanidentifiedbyoracle; Usercreated. SQL>grantresource,connect,dbatorman; Grantsucceeded. SQL>quit 使用rman用户登录RMAN: [oracle@temp-testoracle]$rmantargetrman/oracle RecoveryManager:Release11.2.0.1.0-ProductiononFriNov316:59:272017 Copyright(c)1982,2009,Oracleand/oritsaffiliates.Allrightsreserved. connectedtotargetdatabase:ORCL(DBID=1486802665) RMAN> 也可以使用如下方式登录: [oracle@temp-testoracle]$rman RecoveryManager:Release11.2.0.1.0-ProductiononFriNov317:00:352017 Copyright(c)1982,Oracleand/oritsaffiliates.Allrightsreserved. RMAN>connecttargetsystem/rman connectedtotargetdatabase:ORCL(DBID=1486802665) RMAN>connecttargetrman/oracle 也可以使用操作系统认证连接RMAN: [oracle@temp-testoracle]$rmantarget/ RecoveryManager:Release11.2.0.1.0-ProductiononFriNov317:02:522017 Copyright(c)1982,Oracleand/oritsaffiliates.Allrightsreserved. connectedtotargetdatabase:ORCL(DBID=1486802665) RMAN> 配置RMAN 查看RMAN的配置参数: RMAN>showall; usingtargetdatabasecontrolfileinsteadofrecoverycatalog RMANconfigurationparametersfordatabasewithdb_unique_nameORCLare: CONFIGURERETENTIONPOLICYTOREDUNDANCY1;#default CONFIGUREBACKUPOPTIMIZATIONOFF;#default CONFIGUREDEFAULTDEVICETYPETODISK;#default CONFIGURECONTROLFILEAUTOBACKUPOFF;#default CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F';#default CONFIGUREDEVICETYPEDISKPARALLELISM1BACKUPTYPETOBACKUPSET;#default CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;#default CONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;#default CONFIGUREMAXSETSIZETOUNLIMITED;#default CONFIGUREENCRYPTIONFORDATABASEOFF;#default CONFIGUREENCRYPTIONALGORITHM'AES128';#default CONFIGURECOMPRESSIONALGORITHM'BASIC'ASOFRELEASE'DEFAULT'OPTIMIZEFORLOADTRUE;#default CONFIGUREARCHIVELOGDELETIONPOLICYTONONE;#default CONFIGURESNAPSHOTCONTROLFILENAMETO'/data/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f';#default 参数含义: CONFIGURE RETENTION POLICY TO REDUNDANCY 1 保留的备份副本数量,表示只保留一个数据文件的副本,并且保留最新的备份副本。 CONFIGURE DEFAULT DEVICE TYPE TO DISK 数据文件默认备份到磁盘上,也可以是其他设备如磁带(sbt) CONFIGURE BACKUP OPTIMIZATION OFF 不使用备份优化,启用备份优化的作用是如果已经备份了某个文件的相同版本,则不会再备份该文件。,只保留一份备份文件。 CONFIGURE CONTROLFILE AUTOBACKUP OFF 不启动控制文件的自动备份,修改为ON之后,如果数据库结构发生变化或者在备份数据库过程中,控制文件会自动再备份到指定目录下 CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET 备份通道数量为1,默认使用备份集的方式。通道数量越多,执行任务时间越短。 如果要修改参数,可以执行如下命令: 开启备份优化: RMAN>CONFIGUREBACKUPOPTIMIZATIONON; newRMANconfigurationparameters: CONFIGUREBACKUPOPTIMIZATIONON; newRMANconfigurationparametersaresuccessfullystored 设置备份类型为Copy,也就是映像模式: RMAN>CONFIGUREDEVICETYPEDISKBACKUPTYPETOCOPY; newRMANconfigurationparameters: CONFIGUREDEVICETYPEDISKBACKUPTYPETOCOPYPARALLELISM1; newRMANconfigurationparametersaresuccessfullystored 备份控制文件到快闪恢复区: RMAN>backupcurrentcontrolfile; Startingbackupat03-NOV-17 usingchannelORA_DISK_1 channelORA_DISK_1:startingdatafilecopy copyingcurrentcontrolfile outputfilename=/data/app/oracle/recovery_area/ORCL/controlfile/o1_mf_TAG20171103T180113_dzrhm9vk_.ctltag=TAG20171103T180113RECID=3STAMP=959104874 channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:01 Finishedbackupat03-NOV-17 如果没有指定快闪恢复区也可以用手动指定路径: 将快闪恢复区改为test目录 SQL>connect/assysdba Connected. SQL>showparameterdb_recovery_file_dest; NAMETYPEVALUE ----------------------------------------------------------------------------- db_recovery_file_deststring/data/app/oracle/recovery_area db_recovery_file_dest_sizebiginteger2G SQL>altersystemsetdb_recovery_file_dest='/data/app/oracle/test'; Systemaltered. SQL>showparameterdb_recovery_file_dest; NAME TYPE VALUE ----------------------------------------------------------------------------- db_recovery_file_dest string /data/app/oracle/test db_recovery_file_dest_size biginteger2G 配置控制文件备份的磁盘类型和备份目录,路径以%F结尾: RMAN>configurecontrolfileautobackupformatfordevicetypediskto'/data/app/oracle/test/%F'; newRMANconfigurationparameters: CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'/data/app/oracle/test/%F'; newRMANconfigurationparametersaresuccessfullystored RMAN脱机备份 RMAN脱机备份需要关闭数据库,启动数据库到mount状态。 [oracle@temp-testoracle]$sqlplus/nolog SQL>connect/assysdba Connected. SQL>shutdownimmediate; Databaseclosed. Databasedismounted. ORACLEinstanceshutdown. SQL>startupmount; ORACLEinstancestarted. TotalSystemGlobalArea1586708480bytes FixedSize 2213736bytes VariableSize 939526296bytes DatabaseBuffers 637534208bytes RedoBuffers 7434240bytes Databasemounted. SQL>quit 进入RMAN,备份数据: 先查看数据备份的默认参数,备份方式(copy或backupset),和备份格式(disk 或其他) RMAN>showall; usingtargetdatabasecontrolfileinsteadofrecoverycatalog RMANconfigurationparametersfordatabasewithdb_unique_nameORCLare: CONFIGURERETENTIONPOLICYTOREDUNDANCY1;#default CONFIGUREBACKUPOPTIMIZATIONON; CONFIGUREDEFAULTDEVICETYPETODISK;#default CONFIGURECONTROLFILEAUTOBACKUPOFF;#default CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'%F';#default CONFIGUREDEVICETYPEDISKBACKUPTYPETOCOPYPARALLELISM1; CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1;#default CONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1;#default CONFIGUREMAXSETSIZETOUNLIMITED;#default CONFIGUREENCRYPTIONFORDATABASEOFF;#default CONFIGUREENCRYPTIONALGORITHM'AES128';#default CONFIGURECOMPRESSIONALGORITHM'BASIC'ASOFRELEASE'DEFAULT'OPTIMIZEFORLOADTRUE;#default CONFIGUREARCHIVELOGDELETIONPOLICYTONONE;#default CONFIGURESNAPSHOTCONTROLFILENAMETO'/data/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f';#default 执行备份命令使用copy方式,映像复制整个库: RMAN>backupascopydatabase; 如果是使用默认的backupset模式,可以使用: RMAN>backupascompressedbackupsetdatabase; 备份完成后,打开数据库: RMAN>sql'alterdatabaSEOpen'; sqlstatement:alterdatabaSEOpen RMAN联机备份整个数据库 联机备份必须要设置快闪恢复区,快闪恢复区要足够大,并且数据库要置于归档模式。在数据库进行备份的同时,可以进行DML操作,可以正常读取,新的DML操作记录会在重做日志文件中,如果备份时间很长,而且在这个期间产生了大量的数据变化,重做日志会切换从而将这些变化的数据写到归档日志文件中。这里的归档日志就类似于mysql的binlog,在出现介质故障的时候,使用备份和归档日志记录和完成数据的全部恢复。 查看数据库归档模式是否开启,当前属于关闭状态: SQL>archiveloglist; DatabaselogmodeNoArchiveMode AutomaticarchivalDisabled ArchivedestinationUSE_DB_RECOVERY_FILE_DEST Oldestonlinelogsequence22 Currentlogsequence24 开启日志归档: SQL>alterdatabasearchivelog; alterdatabasearchivelog * ERRORatline1: ORA-01126:databasemustbemountedinthisinstanceandnotopeninany instance 日志归档必须在mount模式,关闭数据库启动到mount模式,开启归档模式: SQL>shutdownimmediate; Databaseclosed. Databasedismounted. ORACLEinstanceshutdown. SQL>startupmount; ORACLEinstancestarted. TotalSystemGlobalArea1586708480bytes FixedSize2213736bytes VariableSize1040189592bytes DatabaseBuffers536870912bytes RedoBuffers7434240bytes Databasemounted. SQL>alterdatabasearchivelog; Databasealtered. SQL>alterdatabaSEOpen; Databasealtered. SQL>archiveloglist; DatabaselogmodeArchiveMode AutomaticarchivalEnabled ArchivedestinationUSE_DB_RECOVERY_FILE_DEST Oldestonlinelogsequence22 Nextlogsequencetoarchive24 Currentlogsequence24 使用RMAN联机备份数据库: RMAN>backupascompressedbackupsetdatabaseplusarchivelogdeleteallinput; 使用备份集格式进行备份,并采用压缩的方式,压缩比例大概为5:1。备份数据文件,同时也备份归档日志文件,备份完成之后会将已经备份的归档日志文件从存储目录中删除,清除归档空间。 备份表空间: RMAN>backuptablespaceusers; Startingbackupat06-NOV-17 usingchannelORA_DISK_1 channelORA_DISK_1:startingdatafilecopy inputdatafilefilenumber=00004name=/data/app/oracle/oradata/orcl/users01.dbf outputfilename=/data/app/oracle/test/ORCL/datafile/o1_mf_users_dzzxzmxv_.dbftag=TAG20171106T134939RECID=12STAMP=959348980 channelORA_DISK_1:datafilecopycomplete,elapsedtime:00:00:01 Finishedbackupat06-NOV-17 使用压缩: RMAN>backupascompressedbackupsettablespaceusers; 使用RMAN备份数据文件,%U表示自动分配一个唯一的命名: RMAN>backupasbackupsetdatafile1format'/data/app/backup/datafile_1_%U'; 清除命令,慎用 清除旧的备份文件set格式: RMAN>deleteobsolete; RMAN>crosscheckbackupset; RMAN>deleteexpiredbackupset; 清楚旧的备份文件和所有备份,copy模式: RMAN>listdatafilecopyall; RMAN>deleteexpiredbackupcopy; RMAN>crosscheckdatafilecopyall; RMAN>deleteexpiredbackupcopy; 如果是由于快闪恢复区容量限制,可以修改快闪恢复区的路径和容量: SQL>altersystemsetdb_recovery_file_dest='/data/app/oracle/test'; SQL>altersystemsetdb_recovery_file_dest_size=10g; RMAN增量备份 使用backup database时,都是全库备份,每次这样备份很耗时也占用磁盘空间,RMAN的增量备份就能解决这些问题。 这里有两种备份级别,级别0备份为全库备份,级别1的备份为增量备份,一般情况下需要对数据进行0级别的备份之后在进行级别1的增量备份。 RMAN>backupincrementallevel0database; 级别1的差异增量备份: RMAN>backupincrementallevel1database; 对映像副本进行增量备份 RMAN>run{ 2>backupincrementallevel1forrecoverofcopywithtag'incr_copy_backup'database; 3>recovercopyofdatabasewithtag'incr_copy_backup'; 4>} 每次执行这条命令,就可以使用映像的方式对数据库进行增量备份。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |