Oracle数据库RMAN备份及恢复

1、RMAN备份与恢复一、 原理和单机使用RMAN备份原理相同，利用其中一个节点做备份（数据都放在ASM共享存储上），恢复也在一个节点上做，恢复到共享存储上，最后2个节点都能正常启动1.1查看归档日志rootredhat2 # su - oracleredhat2-> sqlplus / as sysdba;SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 24 18:31:46 2015Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Dat

2、abase 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options/查看归档模式和强制日志是否开启，这里都没有开启SQL> select name,log_mode,force_logging from gv$database;NAME LOG_MODE FOR

3、- - -DEVDB NOARCHIVELOG NODEVDB NOARCHIVELOG NO/开启强制日志模式，在一个节点上执行就可以了SQL> alter database force logging;Database altered./再次查看发现强制日志模式已开启SQL> select name,log_mode,force_logging from gv$database;NAME LOG_MODE FOR- - -DEVDB NOARCHIVELOG YESDEVDB NOARCHIVELOG YESSQL>1.2开启归档模式/开启归档模式需要关闭数据库，而且需要

4、在mount模式下rootredhat1 # su - grid/查看数据库状态redhat1-> crs_stat -tName Type Target State Host - ora.up.type ONLINE ONLINE redhat1 ora.up.type ONLINE ONLINE redhat1 ora.up.type ONLINE ONLINE redhat1 ora.ER.lsnr ora.er.type ONLINE ONLINE redhat1 ora.N1.lsnr ora.er.type ONLINE ONLINE redhat2 ora.asm ONLI

5、NE ONLINE redhat1 ora.se.type ONLINE ONLINE redhat1 ora.eons ONLINE ONLINE redhat1 ora.gsd OFFLINE OFFLINE work ora.rk.type ONLINE ONLINE redhat1 ora.oc4j ora.oc4j.type OFFLINE OFFLINE ora.ons ONLINE ONLINE redhat1 ora.SM1.asm application ONLINE ONLINE redhat1 ora.T1.lsnr application ONLINE ONLINE r

6、edhat1 ora.at1.gsd application OFFLINE OFFLINE ora.at1.ons application ONLINE ONLINE redhat1 ora.at1.vip ora.t1.type ONLINE ONLINE redhat1 ora.SM2.asm application ONLINE ONLINE redhat2 ora.T2.lsnr application ONLINE ONLINE redhat2 ora.at2.gsd application OFFLINE OFFLINE ora.at2.ons application ONLIN

7、E ONLINE redhat2 ora.at2.vip ora.t1.type ONLINE ONLINE redhat2 ora.scan1.vip ora.ip.type ONLINE ONLINE redhat2 /查看集群节点运行状态 redhat1-> srvctl status database -d devdbInstance devdb1 is running on node redhat1Instance devdb2 is running on node redhat2/关闭集群节点实例redhat1-> srvctl stop database -d dev

8、dbredhat1-> srvctl status database -d devdbInstance devdb1 is not running on node redhat1Instance devdb2 is not running on node redhat2/切换到oracle用户，挂启数据库rootredhat1 # su - oracleredhat2-> sqlplus / as sysdba;SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 24 18:50:10 2015Copyright (c) 1982,

9、 2009, Oracle. All rights reserved.Connected to an idle instance.SQL> startup mount;ORACLE instance started.Total System Global Area 1269366784 bytesFixed Size 2212976 bytesVariable Size 1023413136 bytesDatabase Buffers 234881024 bytesRedo Buffers 8859648 bytesDatabase mounted./查看数据库是挂载状态SQL>

10、select open_mode from v$database;OPEN_MODE-MOUNTED/查看归档模式还没有开启SQL> select open_mode,log_mode from v$database;OPEN_MODE LOG_MODE- -MOUNTED NOARCHIVELOG/开启归档模式SQL> alter database archivelog;Database altered./查看归档模式已开启SQL> select open_mode,log_mode from v$database;OPEN_MODE LOG_MODE- -MOUNTED

11、ARCHIVELOG/关闭数据库，通过集群启动两个数据库节点SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SQL> /切换到grid用户，通过集群软件启动数据库实例rootredhat1 # su - gridredhat1-> iduid=1100(grid) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper),1

12、300(dba)redhat1-> srvctl start database -d devdbredhat1-> /切换到oracle用户，再次查看状态，归档模式和强制日志都是开启rootredhat2 # su - oracleredhat2-> sqlplus / as sysdba;SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 24 19:40:51 2015Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Data

13、base 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> select name,log_mode,force_logging from gv$database;NAME LOG_MODE FOR- - -DEVDB ARCHIVELOG YES

14、DEVDB ARCHIVELOG YESSQL> 1.3数据库备份/查看日志信息SQL> set line 200SQL> select * from v$log;GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZEMEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME- - - - - - - - - - - - 1 1 7 52428800 5122 NO CURRENT 28201762 2015/07/24 18:53:19 2.8147E+14 2 1 6 52

15、428800 5122 YES INACTIVE 28201760 2015/07/24 18:53:18 28201762 2015/07/24 18:53:19 3 2 3 52428800 5122 NO CURRENT 28201758 2015/07/24 18:53:18 2.8147E+14 2015/07/24 18:53:18 4 2 2 52428800 5122 YES INACTIVE 28132426 2015/07/24 09:00:33 28201752 2015/07/24 18:34:35SQL> /查看归档日志存放路径，这里并没有配置存放路径SQL&g

16、t; show parameter log_archive;NAME TYPE VALUE- - -log_archive_config stringlog_archive_dest stringlog_archive_dest_1 stringlog_archive_dest_10 stringlog_archive_dest_11 stringlog_archive_dest_12 stringlog_archive_dest_13 stringlog_archive_dest_14 stringlog_archive_dest_15 stringlog_archive_dest_16 s

17、tringlog_archive_dest_17 stringNAME TYPE VALUE- - -log_archive_dest_18 stringlog_archive_dest_19 stringlog_archive_dest_2 stringlog_archive_dest_20 stringlog_archive_dest_21 stringlog_archive_dest_22 stringlog_archive_dest_23 stringlog_archive_dest_24 stringlog_archive_dest_25 stringlog_archive_dest

18、_26 stringlog_archive_dest_27 stringNAME TYPE VALUE- - -log_archive_dest_28 stringlog_archive_dest_29 stringlog_archive_dest_3 stringlog_archive_dest_30 stringlog_archive_dest_31 stringlog_archive_dest_4 stringlog_archive_dest_5 stringlog_archive_dest_6 stringlog_archive_dest_7 stringlog_archive_des

19、t_8 stringlog_archive_dest_9 stringNAME TYPE VALUE- - -log_archive_dest_state_1 string enablelog_archive_dest_state_10 string enablelog_archive_dest_state_11 string enablelog_archive_dest_state_12 string enablelog_archive_dest_state_13 string enablelog_archive_dest_state_14 string enablelog_archive_

20、dest_state_15 string enablelog_archive_dest_state_16 string enablelog_archive_dest_state_17 string enablelog_archive_dest_state_18 string enablelog_archive_dest_state_19 string enableNAME TYPE VALUE- - -log_archive_dest_state_2 string enablelog_archive_dest_state_20 string enablelog_archive_dest_sta

21、te_21 string enablelog_archive_dest_state_22 string enablelog_archive_dest_state_23 string enablelog_archive_dest_state_24 string enablelog_archive_dest_state_25 string enablelog_archive_dest_state_26 string enablelog_archive_dest_state_27 string enablelog_archive_dest_state_28 string enablelog_arch

22、ive_dest_state_29 string enableNAME TYPE VALUE- - -log_archive_dest_state_3 string enablelog_archive_dest_state_30 string enablelog_archive_dest_state_31 string enablelog_archive_dest_state_4 string enablelog_archive_dest_state_5 string enablelog_archive_dest_state_6 string enablelog_archive_dest_st

23、ate_7 string enablelog_archive_dest_state_8 string enablelog_archive_dest_state_9 string enablelog_archive_duplex_dest stringlog_archive_format string %t_%s_%r.dbfNAME TYPE VALUE- - -log_archive_local_first boolean TRUElog_archive_max_processes integer 4log_archive_min_succeed_dest integer 1log_arch

24、ive_start boolean FALSElog_archive_trace integer 0SQL> /需要注意的是，默认情况下，我们的归档是存在闪回恢复区的SQL> show parameter db_recoverNAME TYPE VALUE- - -db_recovery_file_dest string +FLASHdb_recovery_file_dest_size big integer 3882MSQL> /在第二个节点利用RMAN查看/查看有那些数据需要备份RMAN> report need backup;RMAN retention poli

25、cy will be applied to the commandRMAN retention policy is set to redundancy 6Report of files with less than 6 redundant backupsFile #bkps Name- - -1 2 +DATA/devdb/datafile2 2 +DATA/devdb/datafile3 2 +DATA/devdb/datafile4 2 +DATA/devdb/datafile5 2 +DATA/devdb/datafile6 2 +DATA/devdb/datafile7 2 +DATA

26、/devdb/datafile/hstest_db/删除所有归档日志RMAN> delete archivelog all;/查看有那些备份RMAN> list backup;/查看集群实例数SQL> show parameter cluster_database;NAME TYPE VALUE- - -cluster_database boolean TRUEcluster_database_instances integer 2/查看数据库当前大小SQL> select sum(bytes)/1024/1024 mb from dba_data_files;MB-

27、3310SQL> /查看所有归档日志RMAN> list archivelog all;specification does not match any archived log in the repositoryRMAN>二、 当前环境数据库版本：oracle 11gRac1和rac2数据和归档放在ASM磁盘组上，3 个asm 磁盘组，分别为：OCR_VOL,DATA_VOL,FLASH_VOL。其中DATA_VOL将存放数据库文件；FLASH_VOL存放闪回文件2.1、查看备份相关文件以下未特别说明，均在节点1执行，其实2个节点查询出来的结果是一样的，因为文件都是放在AS

28、M上/查看配置文件rootdawu1 # su - oracledawu1-> sqlplus / as sysdba;SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 14 15:13:05 2015Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, Real

29、 Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing optionsSQL> show parameter pfile;NAME TYPE VALUE- - -spfile string +DATA/devdb/spfiledevdb.ora/查看控制文件SQL> show parameter control_files;NAME TYPE VALUE- - -control_files string +DATA/devdb/control

30、file/curren t.260.874263893, +FLASH/devdb/ controlfile 893/查看表空间数据大小SQL> select file_name,tablespace_name,bytes/1024/1024 MB from dba_data_files;FILE_NAME-TABLESPACE_NAME MB- -+DATA/devdb/datafileUSERS5+DATA/devdb/datafileUNDOTBS1 110+DATA/devdb/datafileSYSAUX 1360FILE_NAME-TABLESPACE_NAME MB- -+

31、DATA/devdb/datafileSYSTEM 1110+DATA/devdb/datafileEXAMPLE 100+DATA/devdb/datafileUNDOTBS2 100FILE_NAME-TABLESPACE_NAME MB- -+DATA/devdb/datafile/huashang_dbHUASHANG_DB 1000+DATA/devdb/datafile/hstest_dbHSTEST_DB 10008 rows selected.SQL> select * from v$logfile; GROUP# STATUS TYPE- - -MEMBER-IS_-

32、2 ONLINE+DATA/devdb/onlinelogNO 2 ONLINE+FLASH/devdb/onlinelogYES GROUP# STATUS TYPE- - -MEMBER-IS_- 1 ONLINE+DATA/devdb/onlinelogNO 1 ONLINE+FLASH/devdb/onlinelog GROUP# STATUS TYPE- - -MEMBER-IS_-YES 3 ONLINE+DATA/devdb/onlinelogNO 3 ONLINE GROUP# STATUS TYPE- - -MEMBER-IS_-+FLASH/devdb/onlinelogY

33、ES 4 ONLINE+DATA/devdb/onlinelogNO GROUP# STATUS TYPE- - -MEMBER-IS_- 4 ONLINE+FLASH/devdb/onlinelogYES8 rows selected.SQL>SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 1818Current log seque

34、nce 1819SQL> show parameter DB_RECOVERY_FILE_DEST;NAME TYPE VALUE- - -db_recovery_file_dest string +FLASHdb_recovery_file_dest_size big integer 3882MSQL>节点2也是一样输出2.2、查看ASM磁盘组中的归档日志/2个节点上看到的是一样的rootredhat1 mail# su - gridredhat1-> asmcmdASMCMD> lsDATA/FLASH/GRIDDG/ASMCMD> cd FLASHASMCM

35、D> lsDEVDB/ASMCMD> cd DEVDBASMCMD> lsARCHIVELOG/CONTROLFILE/ONLINELOG/ASMCMD> cd ARCHIVELOGASMCMD> ls2015_08_17/ASMCMD> cd 2015_08_17ASMCMD> ls 2.3、生成测试数据，提交 SQL>create table myTestTable asselect rownum as id, to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss')

36、 as inc_datetime, trunc(dbms_random.value(0, 100) as random_id, dbms_random.string('x', 20) random_string from dual connect by level <= 100000; SQL>commit;三、 使用RMAN备份数据库3.1 脚本export ORACLE_SID=devdb1export BACKUP_DIR=/home/oracleexport RMAN_SCRIPTS=$BACKUP_DIR/scriptsexport RMAN_BACKUP

37、SETS=$BACKUP_DIR/backupsetsexport RMAN_LOG=$BACKUP_DIR/logs/rman_$ORACLE_SID.logexport RMAN=$ORACLE_HOME/bin/rmanecho 'Starting Full Database Backup at 'date +%Y-%m-%d:%H:%M:%S >>$RMAN_LOG$RMAN target / log $RMAN_LOG append <<EOFset echo on;CONFIGURE DEFAULT DEVICE TYPE TO DISK;C

38、ONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$RMAN_BACKUPSETS/$ORACLE_SID-%U.rman'CONFIGURE CONTROLFILE AUTOBACKUP ON;CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$RMAN_BACKUPSETS/$ORACLE_SID-%F.ctl'CONFIGURE RETENTION POLICY TO REDUNDANCY 6;backup as compressed back

39、upset database format '$RMAN_BACKUPSETS/$ORACLE_SID-%U.db'plusarchivelog format '$RMAN_BACKUPSETS/$ORACLE_SID-%U.arc' delete all input;delete noprompt obsolete;list backup summary;list backup by file;crosscheck archivelog all;crosscheck backupset;exit;EOFecho ''>>$RMAN_

40、LOGecho 'End Full Database Backup at 'date +%Y-%m-%d:%H:%M:%S >>$RMAN_LOGecho ''>>$RMAN_LOG3.2 执行备份脚本，执行后查看redhat1-> lltotal 1308352-rw-r- 1 oracle asmadmin 453228032 Aug 17 23:14 devdb1-11qes3fi_1_1.arc-rw-r- 1 oracle asmadmin 455261696 Aug 17 23:15 devdb1-12qes3id_1_

41、1.arc-rw-r- 1 oracle asmadmin 401924096 Aug 17 23:18 devdb1-13qes3mk_1_1.db-rw-r- 1 oracle asmadmin 9184256 Aug 17 23:18 devdb1-14qes3qo_1_1.arc-rw-r-1oracle asmadmin 18808832 Aug 17 23:18 devdb1-c-778563805-20150817-00.ctlredhat1-> rmanRecovery Manager: Release 11.2.0.1.0 - Production on Mon Aug

42、 17 23:28:43 2015Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: DEVDB (DBID=778563805)RMAN> list backup of database summary;using target database control file instead of recovery catalogList of Backups=Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag- - - - - - - - - -11 B F A DISK 2015/07/23 04:12:45 1 1 NO TAG20150723T04123912 B F A DISK 2015/07/23 04:13:19 1 1 NO TAG20150723T04123915 B F A DISK 2015/07/23 04:14:05 1 1 NO TAG20150723T04123916 B F A DISK 2015/07/23 04:14:22 1 1 NO TAG20150723T04123920 B