oracle 第23章 RMAN 备份恢复
2015-10-24
#查看快闪恢复区的参数信息 SQL> show parameter db_recovery_file_dest; #修改快闪恢复区参数 SQL> alter system set db_recovery_file_dest_size=4G; #查看快闪恢复区位置、空间使用信息 SQL> col name for a30 SQL> set line 100 SQL> select name,space_limit,space_used,number_of_files from v$recovery_file_dest; #设置快闪恢复区位置 SQL> alter system set db_recovery_file_dest='/u01/backup/newflasharea'; #查看快闪恢复区使用情况 SQL> select file_type,percent_space_used,percent_space_reclaimable,number_of_files from v$flash_recovery_area_usage; #使用数据库用户名和密码登录RMAN RMAN> connect target system/oracle; #创建用户rman,并赋予权限 SQL> create user rman identified by oracle; SQL> grant resource,connect,dba to rman; #使用rman用户连接rman [oracle@mdb ~]$ rman target rman/oracle #使用系统认证连接rman [oracle@mdb ~]$ rman target / #查看RMAN配置参数 RMAN> show all; CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # 保留备份副本的数量1 CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # 备份设备类型为磁带 CONFIGURE CONTROLFILE AUTOBACKUP OFF; # 控制文件自动备份关闭 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # 通道数量1,备份类型为备份集 CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default 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 ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbs/snapcf_orcl.f'; # default #配置备份设备类型为磁带 RMAN> configure default device type to sbt; #配置备份设备类型为磁盘 RMAN> configure default device type to disk; #配置备份类型为映像复制(慎用,太占空间!!!) RMAN> configure device type disk backup type to copy; #配置控制文件自动备份 RMAN> configure controlfile autobackup on; #配置控制文件备份的磁盘类型和备份目录 RMAN> configure controlfile auotobackup format for device type disk to '/u01/app/oracle/backup/%F'; RMAN> backup as copy database; RMAN> backup as copy tablespace users; RMAN> backup as copy datafile 3; RMAN> backup current controlfile format '/u01/app/oracle/backup/ctl_%u.bak'; RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/full_%u.bak'; RMAN> backup as compressed backupset tablespace users format '/u01/app/oracle/backup/tbs_users_%u.bak'; RMAN> backup as compressed backupset datafile 1 format '/u01/app/oracle/backup/dtf_1_%u.bak'; RMAN> backup archivelog all format '/u01/app/oracle/backup/arch_%u.bak' delete input; ----------------------------------------------------------------------------------- #全量备份 RMAN> backup database format '/u01/app/oracle/backup/full_%u.bak'; #0级增量备份 RMAN> backup incremental level 0 database format '/u01/app/oracle/backup/lv0_%u.bak'; #增加数据 RMAN> sql 'create table rman_test tablespace users as select * from dba_objects'; RMAN> sql 'insert into rman_test select * from rman_test'; #1级差分增量备份 RMAN> backup incremental level 1 database format '/u01/app/oracle/backup/lv1_diff1_%u.bak'; #1级累积增量备份 RMAN> backup incremental level 1 cumulative database format '/u01/app/oracle/backup/lv1_cumu1_%u.bak'; -------------------------------------------------------- 删除备份集 [root@mdb ORCL]# rm -rf *; RMAN> list backupset; RMAN> crosscheck backupset; RMAN> delete noprompt expired backupset; RMAN> delete noprompt obsolete; RMAN> list backupset; ---------------------------------- 1.全量备份与恢复 #开启归档模式 SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; #全量备份 SQL> startup; RMAN> backup database format '/u01/app/oracle/backup/full_%u.bak'; #模拟故障 cd /u01/app/oracle/oradata/ && mv orcl orcl.full && mkdir orcl #全量恢复 SQL> shutdown abort; SQL> startup nomount; RMAN> restore controlfile from '/u01/app/oracle/backup/full_2fqmno6s.bak'; RMAN> sql 'alter database mount'; RMAN> restore database; RMAN> recover database noredo; RMAN> sql 'alter database open resetlogs'; 2.增量备份与恢复 #0级增量备份 SQL> startup; RMAN> backup incremental level 0 database format '/u01/app/oracle/backup/lv0_%u.bak'; #模拟故障 cd /u01/app/oracle/oradata/ && mv orcl orcl.inc0 && mkdir orcl #0级增量恢复 SQL> shutdown abort; SQL> startup nomount; RMAN> restore controlfile from '/u01/app/oracle/backup/lv0_04qmvr0i.bak'; RMAN> sql 'alter database mount'; RMAN> restore database; RMAN> recover database noredo; RMAN> sql 'alter database open resetlogs'; RMAN> sql 'create table rman_test tablespace users as select * from dba_objects'; RMAN> sql 'insert into rman_test select * from rman_test'; RMAN> sql 'insert into rman_test select * from rman_test'; RMAN> backup incremental level 1 database format '/u01/app/oracle/backup/lv1_%u.bak'; ------------------------------------------------------------------------------------ 恢复目录(不重要) 场景:PROD数据库(TARGET),TEST数据库(CATALOG) TEST数据库: create tablespace cata datafile '/u01/app/oracle/oradata/test/cata.dbf' size 100m; create user cata identified by cata default tablespace cata; grant connect,resource,recovery_catalog_owner,unlimited tablespace to cata; rman catalog cata/cata@test create catalog tablespace cata; rman target sys/oracle@prod catalog cata/cata@test 需要备份的数据库 备份信息写入的数据库 register database; --------------------------------------------------------------------------- RMAN命令 list、backup、crosscheck、delete、restore、recover validate backupset、restore...validate、restore...preview、report
参考资料
[1] 林树泽.Oracle 11g R2 DBA操作指南[M].北京:清华大学出版社,2013
[3] Oracle备份与恢复案例
[4] ORACLE 备份与恢复
[5] Oracle数据库的备份方法
[6] oracle之rman入门指南
[7] ORACLE RMAN备份及还原
[8] rman备份恢复实例(经典)
[9] RMAN 备份详解
[10] RMAN 还原与恢复
[11] RMAN恢复
[12] RMAN简明使用手册(转载)
[13] RMAN简明参考使用手册
[14] Oracle学习系列之Rman学习(三)
[15] rman 全备与恢复
[16] RMAN - command & env option
[17] RMAN的list和report命令详解