Copy ---物理备份 源文件大小与备份的文件大小一致
Backup ---逻辑物理备份，基于块级别的备份。备份的是已经使用的块也就是热点块。
RMAN的使用前提条件
1、归档模式
2、数据库启动在mount或者open状态
RMAN 使用方式
确认调用的是oracle的RMAN而不是OS自带的RMAN
|
[oracle@yang ~]$ which rman /u01/app/oracle/product/10.2.0/db_1/bin/rman [oracle@yang ~]$ [root@yang ~]# find / -name rman /u01/app/oracle/product/10.2.0/db_1/bin/rman /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/perl/db/rman /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/rman /u01/app/oracle/product/10.2.0/db_1/sysman/admin/scripts/db/rman /home/oracle/bf/db_1/bin/rman /home/oracle/bf/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/perl/db/rman /home/oracle/bf/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/database/rman /home/oracle/bf/db_1/sysman/admin/scripts/db/rman
如果最先调用的不是oracle的RMAN可以修改环境变量 [oracle@yang ~]$ vi ./.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH unset USERNAME export DISPLAY=192.168.56.1:0.0 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH (让$ORACLE_HOME/bin在前，优先执行) export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib export ORACLE_SID=orcl export NLS_LANG=american_america.zhs16gbk （保存退出） 生效的简单方法（重新登录就可以） [oracle@yang~]$ su - Password: [root@yang ~]# su - oracle [oracle@yangzai ~]$ 确认是否生效 [oracle@yang ~]$ which rman /u01/app/oracle/product/10.2.0/db_1/bin/rman
|
本地连接方式与远程连接方式
|
[oracle@yang ~]$ rman target / （/ 的意思是本地默认实例） [oracle@yang ~]$ echo $ORACLE_SID orcl 远程连接方式 [oracle@yang ~]$ rman target sys/oracle@orcl 或者使用下面方法 [oracle@yang ~]$ rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:22:25 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target /
connected to target database: ORCL (DBID=1374419528)
RMAN>connect target sys/oracle@orcl 通过连接RMAN 识别状态 [oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:33:15 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> 通过SQL 查询 SQL> select dbid,name,open_mode from v$database;
DBID NAME OPEN_MODE ---------- --------- ---------- 1374419528 ORCL READ WRITE
SQL>
|
|
|
RMAN备份
|
数据库全备 （不会备份联机日志文件） [oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon May 12 02:33:15 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> backup database;
备份参数文件 RMAN> backup spfile; 备份当前使用的控制文件 RMAN> backup current controlfile; 备份单独数据文件 RMAN> backup datafile 2; 备份表空间 RMAN> backup tablespace users; 备份归档日志 RMAN> backup archivelog all; 备份归档日志并删除被备份的归档日志 RMAN> backup archivelog all delete input; 全库备份并包含归档日志（也可以加 delete input） RMAN> backup full database plus archivelog; 备份全库跳过某个表空间不备份（以users为例） SQL> alter tablespace users read only; ---需要将表空间置为只读 RMAN>backup database skip readonly; --跳过只读的 SQL> alter tablespace users offline; --置为脱机 RMAN>backup database skip offline; --跳过脱机的
备份存放的默认路径 文件系统： [oracle@yang oracle]$ ls /u01/app/oracle/flash_recover_area/ORCL/backupset/ ASM ASMCMD> pwd +data/orcl/backupset
SQL> show parameter db_rec
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +DATA db_recovery_file_dest_size big integer 2G db_recycle_cache_size big integer 0 SQL>
备份时指定存放路径 RMAN>backup database format ‘/u02/dbfall%U’; ---必须要使用变量
format的替换变量 1. %d --数据库的db_name 2. %n --数据库的8位长度的db_name，不足部分用“x”后面填充 3. %N --数据库表空间的name 4. %I --数据库的dbid 5. %T --年月日（YYYYMMDD） == %Y%M%D （%M 位于该年中第几个月%D位于该月中第几天） 6. %t --9位字符的timestamp 7. %s --备份集序号 8. %p --备份片序号 9. %c --备份片的多个copy的序号 10. %e --archived redo file 的序列号，只能用在archived redo 上 11. %f --datafile filenmuber，只能用在备份datafile、tablespace上，否则没有意义 12. %F --复合format == c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII为dbid，YYYYMMDD为年月日，QQ为十六进制的备份片的多个copy的序号（00-ff）。 13. %u --8为字母唯一串 14. %U --复合format == · backupset：%u_%p_%c · copy of datafile：data-D-%d_id-%I_TS-%N_FNO-%f_%u · copy of archived log：arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u · copy of controlfile：cf-D_%d-id-%I_%u
增量备份 RMAN> backup incremental level=0 database; 或者 run { allocate channel cl type disk; backup incremental level 0 format "/u01/rmanbak/inc0_%u_%T" tag monday_inc0 database; release channel cl; }
|
Copy 备份
|
备份当前控制文件到/u02/backctl.ctl下 RMAN> copy current controlfile to '/u02/backctl.ctl'; ----使用copy必须要指定一个路径 SQL>alter database backup controlfile to ‘/u02/bakctl.ctl’; ---与上面语句是等效的 备份数据文件 RMAN> copy datafile 4 to '/u02/dbf4.dbf';
备份全库 RMAN> backup as copy database;
|
查看曾经做过的备份
|
查看所有backup的操作 ***（备份集只需要将backup变为backupset） RMAN> list backup;
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 631.66M DISK 00:03:13 12-MAY-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/nnndf0_tag20140512t025337_0.276.847335217 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 715714 12-MAY-14 +DATA/orcl/datafile/system.256.846638591 2 Full 715714 12-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 715714 12-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 4 Full 715714 12-MAY-14 +DATA/orcl/datafile/users.259.846638599 5 Full 715714 12-MAY-14 +DATA/orcl/datafile/example.269.846638953
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14 SPFILE Included: Modification time: 12-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 80.00K DISK 00:00:03 12-MAY-14 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20140512T031638 Piece Name: +DATA/orcl/backupset/2014_05_12/nnsnf0_tag20140512t031638_0.278.847336599 SPFILE Included: Modification time: 12-MAY-14
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4 211.67M DISK 00:01:13 12-MAY-14 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20140512T035343 Piece Name: +DATA/orcl/backupset/2014_05_12/annnf0_tag20140512t035343_0.280.847338827
List of Archived Logs in backup set 4 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY-14
RMAN>
查看所有copy的记录 RMAN> list copy;
List of Datafile Copies Key File S Completion Time Ckp SCN Ckp Time Name ------- ---- - --------------- ---------- --------------- ---- 3 4 A 12-MAY-14 719685 12-MAY-14 /u02/dbf4.dbf
List of Control File Copies Key S Completion Time Ckp SCN Ckp Time Name ------- - --------------- ---------- --------------- ---- 2 A 12-MAY-14 719581 12-MAY-14 /u02/backctl.ctl
List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - --------- ---- 1 1 3 A 04-MAY-14 +DATA/orcl/1_3_846638803.dbf 2 1 4 A 04-MAY-14 +DATA/orcl/1_4_846638803.dbf 3 1 5 A 06-MAY-14 +DATA/orcl/1_5_846638803.dbf 4 1 6 A 07-MAY-14 +DATA/orcl/1_6_846638803.dbf 5 1 7 A 08-MAY-14 +DATA/orcl/1_7_846638803.dbf 6 1 8 A 08-MAY-14 +DATA/orcl/1_8_846638803.dbf
RMAN> 可以单独查看某一个备份 RMAN> list backup of archivelog all; ---查看所有归档日志备份
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 4 211.67M DISK 00:01:13 12-MAY-14 BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20140512T035343 Piece Name: +DATA/orcl/backupset/2014_05_12/annnf0_tag20140512t035343_0.280.847338827
List of Archived Logs in backup set 4 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY- RMAN> list backup of controlfile; ------查看所有控制文件备份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14
RMAN> list backup of database; -----查看所有数据文件备份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 631.66M DISK 00:03:13 12-MAY-14 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/nnndf0_tag20140512t025337_0.276.847335217 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 715714 12-MAY-14 +DATA/orcl/datafile/system.256.846638591 2 Full 715714 12-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 715714 12-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 4 Full 715714 12-MAY-14 +DATA/orcl/datafile/users.259.846638599 5 Full 715714 12-MAY-14 +DATA/orcl/datafile/example.269.846638953
RMAN> list backup of spfile; ------查看所有spfile备份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 SPFILE Included: Modification time: 12-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 3 Full 80.00K DISK 00:00:03 12-MAY-14 BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20140512T031638 Piece Name: +DATA/orcl/backupset/2014_05_12/nnsnf0_tag20140512t031638_0.278.847336599 SPFILE Included: Modification time: 12-MAY-14
RMAN> |
RMAN的环境变量及配置
查看环境变量
|
[oracle@yang ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue May 13 01:51:24 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528)
RMAN> show all;
using target database control file instead of recovery catalog RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default -------定义RMAN备份的冗余度 1表示备份集有1个，也可以定义保留备份时间 CONFIGURE BACKUP OPTIMIZATION OFF; # default ------定义对备份进行优化 默认为off CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default ----定义备份的存放设备默认为磁盘，可以改为磁带 CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default ----定义控制文件是否自动备份，默认为off CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default –--定义如果控制文件自动备份那么备份到磁盘且为%F复合format CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default ----定义并行度也就是备份时打开多少通道 CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default ---定义copy或者备份数据文件的设备为disk 且只备份1分 CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default --定义copy或者备份归档日志的设备是disk 且只备份1分 CONFIGURE MAXSETSIZE TO UNLIMITED; # default ----定义了在备份时备份片的大小的限制 默认为UNLIMITED无限制 CONFIGURE ENCRYPTION FOR DATABASE OFF; # default -----定义了RMAN备份时是否加密，默认为off CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default -------定义了加密的运算，默认为AES128 CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default ---定义了归档日志失效后是否删除，默认为none不删除 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_orcl.f'; # default—开启了自动备份控制文件会在后面路径生成快照
RMAN>
|
修改环境变量
|
设置备份保留策略为基于时间 RMAN> CONFIGURE RETENTION POLICY TO ;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "none, redundancy, recovery" –RMAN会告诉你后面要跟多有哪些参数 RMAN-01007: at line 2 column 1 file: standard input 根据提示定义 RMAN> CONFIGURE RETENTION POLICY TO recovery window of 10 days; ---定义了备份保留策略为10天也就是可以恢复到10天前
new RMAN configuration parameters: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; new RMAN configuration parameters are successfully stored
RMAN>
RMAN> show all;
RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; ---要么基于时间，要么基于冗余度
设置控制文件的自动备份 RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
RMAN> RMAN> show all;
RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; 创建表空间确认是否会自动备份 （数据库结构发生变化以及数据库全备控制文件都会自动备份） RMAN> list backup of controlfile; ---查看当前有几个控制文件备份
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14
RMAN> SQL> create tablespace u2 datafile '+data' size 10m;
Tablespace created.
SQL> RMAN> list backup of controlfile;
List of Backup Sets ===================
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:07 12-MAY-14 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140512T025337 Piece Name: +DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 Control File Included: Ckp SCN: 715784 Ckp time: 12-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 5 Full 6.80M DISK 00:00:07 13-MAY-14 BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20140513T023148 Piece Name: +DATA/orcl/autobackup/2014_05_13/s_847420308.282.847420313 Control File Included: Ckp SCN: 731317 Ckp time: 13-MAY-14
RMAN> 设置并行度的数量
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters are successfully stored
RMAN>
设置基于冗余的备份
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2; --配置备份数据文件为2份
new RMAN configuration parameters: CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2; new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2; --配置备份归档日志为2份
new RMAN configuration parameters: CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2; new RMAN configuration parameters are successfully stored
RMAN> RMAN> configure channel 2 device type disk format '/u02/%U','/u01/app/%U '; -- 设置备份的存放路径为2个
new RMAN configuration parameters: CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u02/%U', '/u01/app/%U'; new RMAN configuration parameters are successfully stored
RMAN> 这时候备份时无效的 RMAN> backup spfile;
Starting backup at 13-MAY-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=143 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=140 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 13-MAY-14 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/13/2014 03:00:02 ORA-19806: cannot make duplex backups in recovery area
RMAN> 需要修改db_recovery_file_dest参数 SQL> show parameter db_re
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +DATA db_recovery_file_dest_size big integer 2G db_recycle_cache_size big integer 0 SQL> alter system set db_recovery_file_dest='';
System altered.
SQL> 开启优化备份
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters: CONFIGURE BACKUP OPTIMIZATION ON; new RMAN configuration parameters are successfully stored
RMAN> 设置压缩备份 RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found ";": expecting one of: "backupset, copy, compressed" ---根据提示输入参数 RMAN-01007: at line 1 column 56 file: standard input
RMAN> RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
old RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; new RMAN configuration parameters: CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 released channel: ORA_DISK_2
RMAN> |
临时变量的修改（使用RUN）
|
定义两个通道备份全库 RMAN> run { 2> allocate channel a1 type disk; 3> allocate channel a2 type disk; 4> backup database; 5> release channel a1; 6> release channel a2; 7> } 定义备份存放设备为磁带备份users表空间 RMAN> run { 2> allocate channel a1 type sbt; 3> allocate channel a2 type sbt; 4> backup tablespace users; 5> release channel a1; 6> release channel a2; 7> } 指定一个备份集放多少文件 RMAN> run { 2> allocate channel a1 type disk; 3> allocate channel a2 type disk; 4> backup database filesperset 3; 5> release channel a1; 6> release channel a2; 7> }
定义备份片的大小 （也就是控制单个备份片的大小） RMAN> run { 2> allocate channel a1 type disk maxpiecesize 1g; 3> allocate channel a2 type disk maxpiecesize 1g; 4> backup database; 5> release channel a1; 6> release channel a2; 7> }
可以指定路径 RMAN> run{ --运行 2> allocate channel cha1 type disk; --开始分配名称为cha1类型为磁盘的通道 3> backup -- 备份 4> format '/u01/rmanbak/full_%t' --备份到目录为/u01/rmanbak/名为full_备份集时间戳的文件 5> tag full_backup_bat --设置标签为full_backup_bat 6> database; --备份的是数据库 7> release channel cha1; --完成后释放通道 8> }
|
备份的管理
|
检查所有备份是有效还是无效 RMAN> crosscheck backup;
allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=150 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=154 devtype=DISK crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/nnndf0_tag20140512t025337_0.276.847335217 recid=1 stamp=847335217 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/ncsnf0_tag20140512t025337_0.277.847335419 recid=2 stamp=847335417 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/nnsnf0_tag20140512t031638_0.278.847336599 recid=3 stamp=847336599 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_12/annnf0_tag20140512t035343_0.280.847338827 recid=4 stamp=847338826 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/autobackup/2014_05_13/s_847420308.282.847420313 recid=5 stamp=847420311 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0ap858n7_1_1 recid=6 stamp=847422185 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/0ap858n7_1_2 recid=7 stamp=847422185 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/u01/app/oracle/product/10.2.0/db_1/dbs/c-1374419528-20140513-01 recid=8 stamp=847422190 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_13/nnsnf0_tag20140513t032608_0.283.847423569 recid=9 stamp=847423569 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/autobackup/2014_05_13/s_847423572.284.847423575 recid=10 stamp=847423574 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_13/nnndf0_tag20140513t034819_0.286.847424903 recid=11 stamp=847424901 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=+DATA/orcl/backupset/2014_05_13/nnndf0_tag20140513t034819_0.285.847424901 recid=12 stamp=847424900 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=+DATA/orcl/autobackup/2014_05_13/s_847425002.287.847425003 recid=13 stamp=847425003 Crosschecked 13 objects RMAN> 报告需要做备份的文件 RMAN> report need backup days 7;
Report of files whose recovery needs more than 7 days of archived logs File Days Name ---- ----- -----------------------------------------------------
RMAN>
删除过期的备份 RMAN> delete expired backup; Do you really want to delete the above objects (enter YES or NO)? YES 可以直接删除不用输入YES RMAN> delete noprompt expired backup; 删除无效的备份 RMAN> delete obsolete;
RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 10 days -----定义删除的是10天前的 using channel ORA_DISK_1 using channel ORA_DISK_2 no obsolete backups found
RMAN>
|
RMAN恢复
数据文件丢失
数据文件的改名
|
RMAN> run { 2> allocate channel a1 type disk; 3> sql 'alter tablespace users offline'; 4> set newname for datafile '+DATA/orcl/datafile/users.259.846638599' to '/u01/app/oracle/oradata/users.dbf'; 5> restore tablespace users; 6> switch datafile '/u01/app/oracle/oradata/users.dbf'; 7> recover tablespace users; 8> sql 'alter tablespace users online'; 9> }
SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- +DATA/orcl/datafile/system.256.846638591 +DATA/orcl/datafile/undotbs1.258.846638595 +DATA/orcl/datafile/sysaux.257.846638593 /u01/app/oracle/oradata/users.dbf +DATA/orcl/datafile/example.269.846638953 +DATA/orcl/datafile/u2.281.847420303
6 rows selected.
SQL> SQL> |
能脱机的表空间
|
Users表空间中数据文件丢失 RMAN> sql 'alter tablespace users offline'; -----可以强制脱机 加参数 immediate
sql statement: alter tablespace users offline
RMAN> restore tablespace users;
Starting restore at 14-MAY-14 using channel ORA_DISK_1 using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00004 to +DATA/orcl/datafile/users.259.846638599 channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 channel ORA_DISK_1: restored backup piece 1 piece handle=+DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 tag=TAG20140514T020858 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 14-MAY-14
RMAN>recover tablespace users;
Starting recover at 14-MAY-14 using channel ORA_DISK_1 using channel ORA_DISK_2
starting media recovery media recovery complete, elapsed time: 00:00:00
Finished recover at 14-MAY-14
RMAN> sql 'alter tablespace users online';
sql statement: alter tablespace users online
RMAN> |
不能脱机的表空间
|
Undo表空间丢失 RMAN> shutdown immediate
database closed database dismounted Oracle instance shut down
RMAN> startup mount
connected to target database (not started) Oracle instance started database mounted
Total System Global Area 599785472 bytes
Fixed Size 2022632 bytes Variable Size 201327384 bytes Database Buffers 394264576 bytes Redo Buffers 2170880 bytes
RMAN> SQL> select name,file#,status,enabled from v$datafile; -----查看数据文件的路径或者直接找到编号
NAME -------------------------------------------------------------------------------- FILE# STATUS ENABLED ---------- ------- ---------- +DATA/orcl/datafile/system.256.846638591 1 SYSTEM READ WRITE
+DATA/orcl/datafile/undotbs1.258.846638595 2 ONLINE READ WRITE
+DATA/orcl/datafile/sysaux.257.846638593 3 ONLINE READ WRITE
NAME -------------------------------------------------------------------------------- FILE# STATUS ENABLED ---------- ------- ---------- +DATA/orcl/datafile/users.259.846638599 4 ONLINE READ WRITE
+DATA/orcl/datafile/example.269.846638953 5 ONLINE READ WRITE
+DATA/orcl/datafile/u2.281.847420303 6 ONLINE READ WRITE
6 rows selected.
SQL> RMAN> restore datafile 2; ---
Starting restore at 14-MAY-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=152 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00002 to +DATA/orcl/datafile/undotbs1.258.846638595 channel ORA_DISK_1: reading from backup piece /u02/0lp87ptq_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=/u02/0lp87ptq_1_1 tag=TAG20140514T020858 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 Finished restore at 14-MAY-14
RMAN>recover datafile 2;
Starting recover at 14-MAY-14 using channel ORA_DISK_1 using channel ORA_DISK_2
starting media recovery media recovery complete, elapsed time: 00:00:01
Finished recover at 14-MAY-14
RMAN> alter database open;
database opened
RMAN> |
可以指定备份片的名字恢复
|
RMAN> list backup;
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 13 35.55M DISK 00:00:22 14-MAY-14 BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: /u02/0ip87psh_1_1
List of Archived Logs in backup set 13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY-14 1 9 717051 12-MAY-14 764006 14-MAY-14
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 14 49.31M DISK 00:00:27 14-MAY-14 BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: +DATA/orcl/backupset/2014_05_14/annnf0_tag20140514t020815_0.285.847505299
List of Archived Logs in backup set 14 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 15 Full 6.80M DISK 00:00:02 14-MAY-14 BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20140514T020844 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505324.286.847505327 Control File Included: Ckp SCN: 764026 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 16 Full 38.24M DISK 00:01:08 14-MAY-14 BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: /u02/0lp87ptq_1_1 List of Datafiles in backup set 16 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 Full 764041 14-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 764041 14-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 5 Full 764041 14-MAY-14 +DATA/orcl/datafile/example.269.846638953
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 17 Full 81.47M DISK 00:01:24 14-MAY-14 BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: +DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 List of Datafiles in backup set 17 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 764040 14-MAY-14 +DATA/orcl/datafile/system.256.846638591 4 Full 764040 14-MAY-14 +DATA/orcl/datafile/users.259.846638599 6 Full 764040 14-MAY-14 +DATA/orcl/datafile/u2.281.847420303
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 18 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021025 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505425.283.847505427 Control File Included: Ckp SCN: 764073 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021310 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505590.282.847505593 Control File Included: Ckp SCN: 764152 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 6.80M DISK 00:00:06 14-MAY-14 BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021522 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505722.280.847505727 Control File Included: Ckp SCN: 764244 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
RMAN> restore tablespace users from tag='TAG20140514T020858'; RMAN>recove tablespace users; |
基于时间的不完全恢复
----不完全恢复，也就是将数据库恢复到过去的某一个时刻的状态。
----很多情况下都必须进行不完全恢复，如：
-恢复用户的误操作，可以将数据库恢复到误操作前的状态
当前联机重做日志文件损坏或丢失时，必须进行不完全恢复
不完全恢复的方案：
1、基于用户管理的不完全恢复
2、在RMAN中进行不完全恢复
3、可以是用闪回数据库
4、可以进行日志挖掘的不完全恢复
|
Export NLS_LANG=American
RMAN>run { Sql ‘alter session set NLS_TATE_FORMAT=”yyyy-mm-dd :hh24:mi:ss” ’; set until time = ‘2014-05-13 10:11:40’; restore databse; recover database; alter database open resetlogs; }
|
使用RMAN基于数据库时间点的不完全恢复
|
Scott用户下的emp表于2014-05-16 09:30:25 误删除 SQL> conn scott/tiger Connected. SQL> select * from tab;
TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE LOGT TABLE EMPSAL TABLE V1 VIEW A1 TABLE A2 TABLE RUPD$_EMP TABLE MLOG$_EMP TABLE
11 rows selected.
SQL> drop table emp;
克隆数据库完成不完全恢复 创建参数文件 [oracle@yang dbs]$ cd $ORACLE_HOME/dbs [oracle@yang dbs]$ vi initqwe.ora compatible='10.2.0.1.0' db_name='qwe' db_block_size=8192 control_files='/u01/app/oracle/oradata/qwe/control01.ctl' undo_management=auto undo_tablespace='UNDOTBS1' db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/' db_recovery_file_dest_size=2g 建立必要的目录 [oracle@yang dbs]$ mkdir /u01/app/oracle/oradata/qwe [oracle@yang dbs]$ mkdir /u01/app/oracle/admin/qwe/{a,b,c}dump –p 手动切换一下orcl实例日志实现归档 SQL> alter system switch logfile; System altered. SQL> 启动qwe实例的数据库到nomount状态 [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 16 05:22:47 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ORACLE instance started.
Total System Global Area 167772160 bytes Fixed Size 2019320 bytes Variable Size 113246216 bytes Database Buffers 50331648 bytes Redo Buffers 2174976 bytes SQL> 使用RMAN连接到orcl实例数据库以qwe实例数据库为辅助数据库 [oracle@yang dbs]$ rman target sys/oracle@orcl10g auxiliary /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 16 05:27:47 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1374419528) connected to auxiliary database: QWE (not mounted)
RMAN> 执行克隆脚本 [oracle@yang ~]$ pwd /home/oracle [oracle@yang ~]$ vi dup.rman run { set until time "to_date('2014-05-16 09:30:00','yyyy-mm-dd hh24:mi:ss')"; duplicate target database to qwe db_file_name_convert=(' +data/orcl/datafile/','/u01/app/oracle/oradata/qwe/') logfile '/u01/app/oracle/oradata/qwe/redo01.log' size 10m, '/u01/app/oracle/oradata/qwe/redo02.log' size 10m; } RMAN>@/home/oracle/dup.rman
RMAN-05517: temporary file +DATA/orcl/tempfile/temp.268.846638915 conflicts 解决方法：删除原临时表空间文件在重建 SQL> alter database tempfile '+DATA/orcl/tempfile/temp.268.846638915' drop;
Database altered.
SQL> alter tablespace temp add tempfile '+DATA/orcl/tempfile/temp1.dbf' size 50m;
Tablespace altered.
SQL> alter database tempfile '+DATA/orcl/tempfile/temp1.dbf' online;
Database altered.
SQL> 继续执行脚本 RMAN>@/home/oracle/dup.rman ……. …….. contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script
database opened Finished Duplicate Db at 16-MAY-14
RMAN> RMAN> **end-of-file**
RMAN> 登录qwe实例数据库查看表emp [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 16 06:34:32 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
SQL> select count(*) from emp;
COUNT(*) ---------- 16
SQL> 将需要的数据导出 [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ exp scott/tiger tables=emp file=scott1.dmp 导入到目标数据库 [oracle@yang dbs]$ export ORACLE_SID=orcl [oracle@yang dbs]$ imp scott/tiger tables=emp file=scott1.dmp ignore=y 到orcl数据库查看数据是否存在 SQL> conn scott/tiger Connected. SQL> select count(*) from emp;
COUNT(*) ---------- 16
SQL> OK关闭qwe实例数据库删除没有的文件 [oracle@yang dbs]$ export ORACLE_SID=qwe [oracle@yang dbs]$ sqlplus /nolog SQL> conn / as sysdba SQL> shutdown abort [oracle@yang dbs]$ rm initqwe.ora -rf [oracle@yang dbs]$ rm /u01/app/oracle/admin/qwe/ -rf [oracle@yang dbs]$ rm /u01/app/oracle/oradata/qwe/ -rf [oracle@yang dbs]$ rm /u01/app/oracle/flash_recovery_area/QWE/ -rf [oracle@yang dbs]$ |
使用RMAN基于表空间时间点的不完全恢复
|
首先备份全库及控制文件 RMAN> backup full database plus archivelog; RMAN> backup current controlfile; 实验删除emp表 SQL> show user USER is "SCOTT" SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY ------------------- 2014-05-16 07:08:05
SQL> drop table emp purge;
Table dropped.
SQL> [oracle@yang ~]$ vi tspitr.rma
run { recover tablespace users until time "to_date('2014-05-16 07:08:00','yyyy-mm-dd h h24:mi:ss')" auxiliary destination '/tmp'; }
|
参数文件、控制文件与数据文件都丢失
|
RMAN> startup nomount RMAN> restore spfile; RMAN> restore spfile from autobackup; 以上方法如果无效那么 RMAN> list backup;
List of Backup Sets ===================
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 13 35.55M DISK 00:00:22 14-MAY-14 BP Key: 14 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: /u02/0ip87psh_1_1
List of Archived Logs in backup set 13 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 646250 07-MAY-14 694945 08-MAY-14 1 7 694945 08-MAY-14 695857 08-MAY-14 1 8 695857 08-MAY-14 717051 12-MAY-14 1 9 717051 12-MAY-14 764006 14-MAY-14
BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 14 49.31M DISK 00:00:27 14-MAY-14 BP Key: 15 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020815 Piece Name: +DATA/orcl/backupset/2014_05_14/annnf0_tag20140514t020815_0.285.847505299
List of Archived Logs in backup set 14 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 3 556989 04-MAY-14 581849 04-MAY-14 1 4 581849 04-MAY-14 618249 06-MAY-14 1 5 618249 06-MAY-14 646250 07-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 15 Full 6.80M DISK 00:00:02 14-MAY-14 BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20140514T020844 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505324.286.847505327 Control File Included: Ckp SCN: 764026 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 16 Full 38.24M DISK 00:01:08 14-MAY-14 BP Key: 17 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: /u02/0lp87ptq_1_1 List of Datafiles in backup set 16 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 2 Full 764041 14-MAY-14 +DATA/orcl/datafile/undotbs1.258.846638595 3 Full 764041 14-MAY-14 +DATA/orcl/datafile/sysaux.257.846638593 5 Full 764041 14-MAY-14 +DATA/orcl/datafile/example.269.846638953
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 17 Full 81.47M DISK 00:01:24 14-MAY-14 BP Key: 18 Status: AVAILABLE Compressed: YES Tag: TAG20140514T020858 Piece Name: +DATA/orcl/backupset/2014_05_14/nnndf0_tag20140514t020858_0.284.847505349 List of Datafiles in backup set 17 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 764040 14-MAY-14 +DATA/orcl/datafile/system.256.846638591 4 Full 764040 14-MAY-14 +DATA/orcl/datafile/users.259.846638599 6 Full 764040 14-MAY-14 +DATA/orcl/datafile/u2.281.847420303
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 18 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021025 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505425.283.847505427 Control File Included: Ckp SCN: 764073 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 19 Full 6.80M DISK 00:00:03 14-MAY-14 BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021310 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505590.282.847505593 Control File Included: Ckp SCN: 764152 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 20 Full 6.80M DISK 00:00:06 14-MAY-14 BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140514T021522 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847505722.280.847505727 Control File Included: Ckp SCN: 764244 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 21 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071650 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523810.278.847523811 Control File Included: Ckp SCN: 772202 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 22 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 23 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071705 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523825.277.847523827 Control File Included: Ckp SCN: 772255 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 23 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071920 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523960.276.847523963 Control File Included: Ckp SCN: 772330 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 24 Full 6.80M DISK 00:00:04 14-MAY-14 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20140514T071927 Piece Name: +DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969 Control File Included: Ckp SCN: 772379 Ckp time: 14-MAY-14 SPFILE Included: Modification time: 14-MAY-14
RMAN> restore spfile from ‘+DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969’; RMAN>startup force nomount RMAN>restore controlfile from ‘+DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969’; RMAN>alter database mount RMAN>restore database; RMAN>recover database; RMAN>alter database open resetlogs; |
在RMAN中以spfile得到pfile
|
RMAN> restore spfile to pfile '/u02/orcl.ora';
Starting restore at 16-MAY-14 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=138 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: restoring SPFILE to PFILE output filename=/u02/orcl.ora channel ORA_DISK_1: reading from backup piece +DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969 channel ORA_DISK_1: restored backup piece 1 piece handle=+DATA/orcl/autobackup/2014_05_14/s_847523967.288.847523969 tag=TAG20140514T071927 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02 Finished restore at 16-MAY-14
RMAN> [oracle@yang u02]$ ll orcl.ora -rw-r--r-- 1 oracle oinstall 1191 May 16 01:51 orcl.ora [oracle@yang u02]$ cat orcl.ora orcl.__db_cache_size=390070272 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__shared_pool_size=197132288 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='NONE' *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump' *.compatible='10.2.0.1.0' *.control_files='+DATA/orcl/controlfile/current.261.846638793','+DATA/orcl/controlfile/current.260.846638797' *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_file_multiblock_read_count=16 *.db_flashback_retention_target=5400 *.db_name='orcl' *.db_recovery_file_dest_size=2147483648 *.db_recovery_file_dest='+DATA' *.dispatchers='(protocol=tcp)(service=ora10gs)(dispatchers=2)' *.job_queue_processes=10 *.log_archive_dest_1='LOCATION=+DATA/orcl/' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=199229440 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' orcl.resource_manager_plan='UPLAN' *.resource_manager_plan='UPLAN' *.sga_target=598736896 *.shared_servers=2 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/orcl/udump' [oracle@yang u02]$ |
CATLOG方式
|
Oracle 10G数据库 使用rman 备份（裸设备管理）1检查数据库模式：oracle@yangzai ~]$ uniread sqlplus / as sysdba [uniread] Loaded history (149 lines)
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 06:25:23 2014
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL>startup 。。。 SQL> archive log list （查看是否在归档模式下） Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 9 Next log sequence to archive 10 Current log sequence 10 SQL>
若为非归档则修改数据库归档模式
SQL> startup mount ORA-01081: cannot start already-running ORACLE - shut it down first 需要关闭数据库 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 启动到mount状态 SQL> startup mount ORACLE instance started.
Total System Global Area 608174080 bytes Fixed Size 2098240 bytes Variable Size 155192256 bytes Database Buffers 444596224 bytes Redo Buffers 6287360 bytes Database mounted. SQL> 更改数据库为归档模式 SQL> alter database archivelog; Database altered. SQL> 打开数据库 SQL> alter database open; Database altered. SQL> 2连接到target数据库[oracle@yangzai bdump]$ rman target 'ORCL' catalog rman/rman
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 13 07:10:34 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password: connected to target database: ORCL (DBID=1354749965) connected to recovery catalog database
RMAN>
1.首先查看一下Oracle的数据文件SQL> select name from v$datafile；
NAME -------------------------------------------------------------------------------- /dev/raw/raw14 /dev/raw/raw16 /dev/raw/raw13 /dev/raw/raw17 /dev/raw/raw18 /dev/raw/raw19 /dev/raw/raw20 /dev/raw/raw21
8 rows selected.
SQL> 2.建立RMAN所需的表空间添加裸设备/raw/raw22查看vg状态 [root@yangzai ~]# vgdisplay --- Volume group --- VG Name vg_oracle System ID Format lvm2 Metadata Areas 5 Metadata Sequence No 70 VG Access read/write VG Status resizable MAX LV 0 Cur LV 21 Open LV 0 Max PV 0 Cur PV 5 Act PV 5 VG Size 20.46 GB PE Size 4.00 MB Total PE 5239 Alloc PE / Size 3102 / 12.12 GB Free PE / Size 2137 / 8.35 GB VG UUID c7sMGI-Ow5z-OO0F-y47X-7BcE-mqtB-goAsUN
创建lv [root@yangzai ~]# lvcreate -n rmanup -L 600M vg_oracle Logical volume "rmanup" created 指定raw22 [root@yangzai ~]# vi /etc/sysconfig/rawdevices /dev/raw/raw17 /dev/vg_oracle/users /dev/raw/raw18 /dev/vg_oracle/user2 /dev/raw/raw19 /dev/vg_oracle/qqqqq /dev/raw/raw20 /dev/vg_oracle/tianjia1 /dev/raw/raw21 /dev/vg_oracle/qqqtmp /dev/raw/raw22 /dev/vg_oracle/rmanup 重启裸设备 [root@yangzai ~]# /sbin/service rawdevices restart Assigning devices: /dev/raw/raw1 --> /dev/vg_oracle/control1 /dev/raw/raw1: bound to major 253, minor 0 /dev/raw/raw2 --> /dev/vg_oracle/control2 /dev/raw/raw2: bound to major 253, minor 11 /dev/raw/raw3 --> /dev/vg_oracle/control3 /dev/raw/raw3: bound to major 253, minor 12 /dev/raw/raw4 --> /dev/vg_oracle/example /dev/raw/raw4: bound to major 253, minor 5 /dev/raw/raw5 --> /dev/vg_oracle/passwordfile /dev/raw/raw5: bound to major 253, minor 14 /dev/raw/raw6 --> /dev/vg_oracle/redo1_1 /dev/raw/raw6: bound to major 253, minor 15 /dev/raw/raw7 --> /dev/vg_oracle/redo1_2 /dev/raw/raw7: bound to major 253, minor 16 /dev/raw/raw8 --> /dev/vg_oracle/redo2a /dev/raw/raw8: bound to major 253, minor 7 /dev/raw/raw9 --> /dev/vg_oracle/redo2b /dev/raw/raw9: bound to major 253, minor 8 /dev/raw/raw10 --> /dev/vg_oracle/redo3a /dev/raw/raw10: bound to major 253, minor 9 /dev/raw/raw11 --> /dev/vg_oracle/redo3b /dev/raw/raw11: bound to major 253, minor 10 /dev/raw/raw12 --> /dev/vg_oracle/lv_spf_spfile /dev/raw/raw12: bound to major 253, minor 13 /dev/raw/raw13 --> /dev/vg_oracle/sysaux /dev/raw/raw13: bound to major 253, minor 2 /dev/raw/raw14 --> /dev/vg_oracle/system /dev/raw/raw14: bound to major 253, minor 1 /dev/raw/raw15 --> /dev/vg_oracle/temp /dev/raw/raw15: bound to major 253, minor 4 /dev/raw/raw16 --> /dev/vg_oracle/undotbs1 /dev/raw/raw16: bound to major 253, minor 3 /dev/raw/raw17 --> /dev/vg_oracle/users /dev/raw/raw17: bound to major 253, minor 6 /dev/raw/raw18 --> /dev/vg_oracle/user2 /dev/raw/raw18: bound to major 253, minor 17 /dev/raw/raw19 --> /dev/vg_oracle/qqqqq /dev/raw/raw19: bound to major 253, minor 18 /dev/raw/raw20 --> /dev/vg_oracle/tianjia1 /dev/raw/raw20: bound to major 253, minor 19 /dev/raw/raw21 --> /dev/vg_oracle/qqqtmp /dev/raw/raw21: bound to major 253, minor 20 /dev/raw/raw22 --> /dev/vg_oracle/rmanup /dev/raw/raw22: bound to major 253, minor 21 Done 永久加载裸设备 [root@yangzai ~]# chown oracle:dba /dev/raw/raw* [root@yangzai ~]# chmod 660 /dev/raw/raw* [root@yangzai ~]# vi /etc/rc.local
#!/bin/sh # # This script will be executed *after* all the other init scripts. # You can put your own initialization stuff in here if you don't # want to do the full Sys V style init stuff.
touch /var/lock/subsys/local chown oracle:dba /dev/raw/raw* chmod 660 /dev/raw/raw*
查看裸设备/RAW/RAW22大小 [root@yangzai ~]# blockdev --getsize /dev/raw/raw22 1228800 登录sql创建表空间创建数据文件为裸设备/raw/raw22名为ora_backup 表空间 SQL> create tablespace ora_backup datafile'/dev/raw/raw22' size 500M;
Tablespace created.
SQL>
进行查看确认一下 SQL> select name from v$datafile;
NAME -------------------------------------------------------------------------------- /dev/raw/raw14 /dev/raw/raw16 /dev/raw/raw13 /dev/raw/raw17 /dev/raw/raw18 /dev/raw/raw19 /dev/raw/raw20 /dev/raw/raw21 /dev/raw/raw22
9 rows selected.
SQL> 3建立RMAN用户以及设定密码和用户表空间SQL> create user rman identified by rman default tablespace ora_backup temporary tablespace temp; 可以加上quota unlimited on ora_backup 命令的意思是rman用户可以使用该表空间的全部空间）
User created.
SQL> 4.给用户授权SQL> grant connect,resource,recovery_catalog_owner to rman;
Grant succeeded.
SQL>
看看都给了那些权限 SQL> select * from dba_sys_privs where grantee='CONNECT';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- CONNECT CREATE SESSION NO
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RESOURCE CREATE TRIGGER NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TYPE NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE TABLE NO
8 rows selected.
SQL> select * from dba_sys_privs where grantee='RECOVERY_CATALOG_OWNER';
GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- RECOVERY_CATALOG_OWNER CREATE SYNONYM NO RECOVERY_CATALOG_OWNER CREATE CLUSTER NO RECOVERY_CATALOG_OWNER ALTER SESSION NO RECOVERY_CATALOG_OWNER CREATE DATABASE LINK NO RECOVERY_CATALOG_OWNER CREATE PROCEDURE NO RECOVERY_CATALOG_OWNER CREATE SEQUENCE NO RECOVERY_CATALOG_OWNER CREATE TABLE NO RECOVERY_CATALOG_OWNER CREATE SESSION NO RECOVERY_CATALOG_OWNER CREATE TYPE NO RECOVERY_CATALOG_OWNER CREATE VIEW NO RECOVERY_CATALOG_OWNER CREATE TRIGGER NO
11 rows selected.
SQL>
5退出数据库重新.连接到数据库退出数据库 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options exit [uniread] Saved history (149 lines) 重连 [oracle@yangzai ~]$ rman target 'orcl' catalog rman/rman
Argument Value Description ----------------------------------------------------------------------------- target quoted-string connect-string for target database catalog quoted-string connect-string for recovery catalog nocatalog none if specified, then no recovery catalog cmdfile quoted-string name of input command file log quoted-string name of output message log file trace quoted-string name of output debugging message log file append none if specified, log is opened in append mode debug optional-args activate debugging msgno none show RMAN-nnnn prefix for all messages send quoted-string send a command to the media manager pipe string building block for pipe names timeout integer number of seconds to wait for pipe input checksyntax none check the command file for syntax errors ----------------------------------------------------------------------------- Both single and double quotes (' or ") are accepted for a quoted-string. Quotes are not required unless the string contains embedded white-space.
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00552: syntax error in command line arguments RMAN-01009: syntax error: found "identifier": expecting one of: "append, at, auxiliary, catalog, cmdfile, clone, checksyntax, debug, log, msglog, mask, msgno, nocatalog, pipe, rcvcat, script, slaxdebug, send, target, timeout, trace" RMAN-01008: the bad identifier was: targetzhiruicatalog RMAN-01007: at line 2 column 1 file: command line arguments
看看rman是否可用 [oracle@yangzai ~]$ rman
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 13 03:08:33 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved. 是可用 退出在连接 RMAN> exit
Recovery Manager complete. 数据库名用大写试试 [oracle@yangzai ~]$ rman target 'ORCL' catalog rman/rman
[oracle@yangzai ~]$ rman catalog rman/rman （2个命令一样）
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 20 02:15:43 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to recovery catalog database
RMAN>
Recovery Manager: Release 10.2.0.5.0 - Production on Mon Jan 13 03:10:15 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password: connected to target database: ORCL (DBID=1354749965) connected to recovery catalog database
RMAN> 连接成功 6.注册数据库直接注册 RMAN> register database;
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of register command at 01/13/2014 03:15:52 RMAN-06428: recovery catalog is not installed 报错：恢复目录没有安装，需要自己创建 RMAN> 7.建立catalog表空间到ora_backupRMAN> create catalog tablespace ora_backup
recovery catalog created
RMAN> 在注册试试 RMAN> register database;
database registered in recovery catalog starting full resync of recovery catalog full resync complete 注册成功 RMAN>
通过dbca数据库连接到orcl数据库中catalog目录数据库配置数据库DBCA实例中的tnsnames.ora文件添加ORCL实例数据库中的网段。使DBCA实例数据库能连接到ORCL数据库
[oracle@dbca admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
ORALOCAL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.188)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = dbca) ) )
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.187)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SID = orcl) ) ) 重新启动监听 连接测试[oracle@dbca admin]$ rman target sys/oracle@ORALOCAL catalog rman/rman@ORCL RMAN 连接 连接符为@ORALOCAL 的RMAN 在连接目录数据库为@ORCL连接符的RMAN
这时候使用备份，会将备份集存放在catlog rman用户下的表空间中。 |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1174656/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29532781/viewspace-1174656/
245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
