转自http://www.ohsdba.cn/index.php?m=Article&a=show&id=108
在做数据库需要做恢复时，如果对恢复没把握，在做之前最好先做备份，一旦这些文件损坏或被破坏，还可能给后续恢复人员带来更多的麻烦，数据库就很难正常打开了。这些备份可以通过正常的方式去做，有时可能无法备份整个库，比如数据库很大，备份耗时很长，在时间上不允许，也可能没有多余的空间等原因。在这种情况下，最好先备份比较重要的文件：当前的控制文件、system表空间的数据文件、redo日志文件，这些对恢复至关重要的。这些可以通过正常的命令去备份，也可以用dd去备份。本文介绍如果使用dd备份和还原，在使用dd时，注意使用conv=notrunc，要不你会把整个文件搞没。
在文件系统下用dd备份文件头部做测试 SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/upgr/system01.dbf /oradata/upgr/sysaux01.dbf /oradata/upgr/undotbs01.dbf /oradata/upgr/users01.dbf SQL> SQL> set lines 156 pages 100 SQL> col dd_backup_cmd for a156 SQL> col dd_restore_cmd for a156 SQL> select 'dd if='||name||' of=/backup/'||substr(name,instr(name,'/',-1)+1)||'.FH bs='||block_size||' count=2' dd_backup_cmd from v$datafile; DD_BACKUP_CMD -------------------------------------------------------------------------------- dd if=/oradata/upgr/system01.dbf of=/backup/system01.dbf.FH bs=8192 count=2 dd if=/oradata/upgr/sysaux01.dbf of=/backup/sysaux01.dbf.FH bs=8192 count=2 dd if=/oradata/upgr/undotbs01.dbf of=/backup/undotbs01.dbf.FH bs=8192 count=2 dd if=/oradata/upgr/users01.dbf of=/backup/users01.dbf.FH bs=8192 count=2 SQL> select 'dd of='||name||' if=/backup/'||substr(name,instr(name,'/',-1)+1)||'.FH bs='||block_size||' count=2 conv=notrunc' dd_restore_cmd from v$datafile; DD_RESTORE_CMD ------------------------------------------------------------------------------------------ dd of=/oradata/upgr/system01.dbf if=/backup/system01.dbf.FH bs=8192 count=2 conv=notrunc dd of=/oradata/upgr/sysaux01.dbf if=/backup/sysaux01.dbf.FH bs=8192 count=2 conv=notrunc dd of=/oradata/upgr/undotbs01.dbf if=/backup/undotbs01.dbf.FH bs=8192 count=2 conv=notrunc dd of=/oradata/upgr/users01.dbf if=/backup/users01.dbf.FH bs=8192 count=2 conv=notrunc SQL> [oracle@db1 ~]$ dd if=/oradata/upgr/system01.dbf of=/backup/system01.dbf.FH bs=8192 count=2 2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.000412654 s, 39.7 MB/s破坏数据文件头部
[oracle@db1 ~]$ dd if=/dev/zero of=/oradata/upgr/system01.dbf bs=8192 count=2 conv=notrunc
2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.000434433 s, 37.7 MB/s [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 16 15:23:33 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 313159680 bytes Fixed Size 2252824 bytes Variable Size 167776232 bytes Database Buffers 138412032 bytes Redo Buffers 4718592 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/oradata/upgr/system01.dbf' SQL> shutdown abort ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options恢复数据文件头部
[oracle@db1 ~]$ dd of=/oradata/upgr/system01.dbf if=/backup/system01.dbf.FH bs=8192 count=2 conv=notrunc
2+0 records in 2+0 records out 16384 bytes (16 kB) copied, 0.000574576 s, 28.5 MB/s [oracle@db1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 16 15:24:32 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 313159680 bytes Fixed Size 2252824 bytes Variable Size 167776232 bytes Database Buffers 138412032 bytes Redo Buffers 4718592 bytes Database mounted. Database opened. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options [oracle@db1 ~]$ 在ASM情况下用dd备份文件头部做测试 SQL> select ' dd '|| 2 ' if=/backup/'||e.group_kffxp||'_'||e.disk_kffxp||'_'||a.file_number||'.FH'|| 3 ' of='||d.path|| 4 ' bs='||c.allocation_unit_size|| 5 ' seek='||e.au_kffxp|| 6 ' count=1 conv=notrunc' dd_restore_cmd 7 from 8 v$asm_alias a, 9 v$asm_file b, 10 v$asm_diskgroup c, 11 v$asm_disk d, 12 x$kffxp e 13 where a.file_number=e.number_kffxp 14 and a.group_number=e.group_kffxp 15 and b.group_number=a.group_number 16 and d.group_number=c.group_number 17 and e.group_kffxp=d.group_number 18 and a.file_number=b.file_number 19 and d.disk_number=e.disk_kffxp 20 and a.system_created='Y' 21 and b.type='DATAFILE' 22 and e.xnum_kffxp=0 23 order by a.file_number 24 / DD_RESTORE_CMD -------------------------------------------------------------------------------------------- dd if=/backup/1_3_283.FH of=/dev/oracleasm/disks/ASMDISK7 bs=1048576 seek=21 count=1 conv=notrunc dd if=/backup/1_4_285.FH of=/dev/oracleasm/disks/ASMDISK8 bs=1048576 seek=262 count=1 conv=notrunc dd if=/backup/1_2_287.FH of=/dev/oracleasm/disks/ASMDISK6 bs=1048576 seek=266 count=1 conv=notrunc dd if=/backup/1_1_288.FH of=/dev/oracleasm/disks/ASMDISK5 bs=1048576 seek=299 count=1 conv=notrunc dd if=/backup/1_3_291.FH of=/dev/oracleasm/disks/ASMDISK7 bs=1048576 seek=163 count=1 conv=notrunc SQL> SQL> select ' dd'|| 2 ' if='||d.path|| 3 ' of=/backup/'||e.group_kffxp||'_'||e.disk_kffxp||'_'||a.file_number||'.FH'|| 4 ' bs='||c.allocation_unit_size|| 5 ' skip='||e.au_kffxp|| 6 ' count=1' dd_backup_cmd 7 from 8 v$asm_alias a, 9 v$asm_file b, 10 v$asm_diskgroup c, 11 v$asm_disk d, 12 x$kffxp e 13 where a.file_number=e.number_kffxp 14 and a.group_number=e.group_kffxp 15 and b.group_number=a.group_number 16 and d.group_number=c.group_number 17 and e.group_kffxp=d.group_number 18 and a.file_number=b.file_number 19 and d.disk_number=e.disk_kffxp 20 and a.system_created='Y' 21 and b.type='DATAFILE' 22 and e.xnum_kffxp=0 23 order by a.file_number 24 / DD_BACKUP_CMD --------------------------------------------------------------------------------------------------------------- dd if=/dev/oracleasm/disks/ASMDISK7 of=/backup/1_3_283.FH bs=1048576 skip=21 count=1 dd if=/dev/oracleasm/disks/ASMDISK8 of=/backup/1_4_285.FH bs=1048576 skip=262 count=1 dd if=/dev/oracleasm/disks/ASMDISK6 of=/backup/1_2_287.FH bs=1048576 skip=266 count=1 dd if=/dev/oracleasm/disks/ASMDISK5 of=/backup/1_1_288.FH bs=1048576 skip=299 count=1 dd if=/dev/oracleasm/disks/ASMDISK7 of=/backup/1_3_291.FH bs=1048576 skip=163 count=1 SQL> 备份ASM中数据文件头部 [oracle@ohs1 ~]$ dd if=/dev/oracleasm/disks/ASMDISK7 of=/backup/1_3_283.FH bs=1048576 skip=21 count=1 1+0 records in 1+0 records out 1048576 bytes (1.0 MB) copied, 0.00664794 seconds, 158 MB/s破坏ASM中数据文件头部
[oracle@ohs1 ~]$ dd if=/dev/zero of=/dev/oracleasm/disks/ASMDISK7 seek=21 count=1 conv=notrunc bs=1M
1+0 records in 1+0 records out 1048576 bytes (1.0 MB) copied, 0.000517497 seconds, 2.0 GB/s [oracle@ohs1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 16 14:58:53 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 318046208 bytes Fixed Size 1344680 bytes Variable Size 188746584 bytes Database Buffers 121634816 bytes Redo Buffers 6320128 bytes Database mounted. ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '+DATA_PGOLD/racdb/system01.dbf' ORA-01210: data file header is media corrupt SQL> shut abort ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options
恢复ASM中数据文件头部
[oracle@ohs1 ~]$ dd if=/backup/1_3_283.FH of=/dev/oracleasm/disks/ASMDISK7 bs=1048576 seek=21 count=1 conv=notrunc 1+0 records in 1+0 records out 1048576 bytes (1.0 MB) copied, 0.00153561 seconds, 683 MB/s [oracle@ohs1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 16 15:00:08 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 318046208 bytes Fixed Size 1344680 bytes Variable Size 188746584 bytes Database Buffers 121634816 bytes Redo Buffers 6320128 bytes Database mounted. Database opened. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> 生成备份数据文件头部所需要的dd脚本(数据库用户) set lines 156 pages 100 col dd_backup_cmd for a156 col dd_restore_cmd for a156 select 'dd if='||name||' of=/backup/'||substr(name,instr(name,'/',-1)+1)||'.FH bs='||block_size||' count=2' dd_backup_cmd from v$datafile; select 'dd of='||name||' if=/backup/'||substr(name,instr(name,'/',-1)+1)||'.FH bs='||block_size||' count=2 conv=notrunc' dd_restore_cmd from v$datafile; 生成备份ASM数据文件头部所需要的dd脚本(在GI用户下ASM实例) set lines 156 pages 100 col dd_backup_cmd for a156 col dd_restore_cmd for a156 select ' dd'|| ' if='||d.path|| ' of=/backup/'||e.group_kffxp||'_'||e.disk_kffxp||'_'||a.file_number||'.FH'|| ' bs='||c.allocation_unit_size|| ' skip='||e.au_kffxp|| ' count=1' dd_backup_cmd from v$asm_alias a, v$asm_file b, v$asm_diskgroup c, v$asm_disk d, x$kffxp e where a.file_number=e.number_kffxp and a.group_number=e.group_kffxp and b.group_number=a.group_number and d.group_number=c.group_number and e.group_kffxp=d.group_number and a.file_number=b.file_number and d.disk_number=e.disk_kffxp and a.system_created='Y' and b.type='DATAFILE' and e.xnum_kffxp=0 order by a.file_number / select ' dd '|| ' if=/backup/'||e.group_kffxp||'_'||e.disk_kffxp||'_'||a.file_number||'.FH'|| ' of='||d.path|| ' bs='||c.allocation_unit_size|| ' seek='||e.au_kffxp|| ' count=1 conv=notrunc' dd_restore_cmd from v$asm_alias a, v$asm_file b, v$asm_diskgroup c, v$asm_disk d, x$kffxp e where a.file_number=e.number_kffxp and a.group_number=e.group_kffxp and b.group_number=a.group_number and d.group_number=c.group_number and e.group_kffxp=d.group_number and a.file_number=b.file_number and d.disk_number=e.disk_kffxp and a.system_created='Y' and b.type='DATAFILE' and e.xnum_kffxp=0 order by a.file_number / 生成备份ASM磁盘头部所需要的dd脚本(在GI用户下ASM实例) set lines 150 set pages 1000 select 'dd if='||path|| ' of=/backup/'||substr(path,instr(path,'/',-1)+1)||'_'||group_number||'_'||disk_number||'.DH bs=1M count=100' from v$asm_disk asm_header_backup where header_status='MEMBER' order by group_number,disk_number;
select 'dd if=/backup/'||substr(path,instr(path,'/',-1)+1)||'_'||group_number||'_'||disk_number||'.DH'|| ' of='||path||' bs=1M count=100 conv=notrunc' from v$asm_disk asm_header_restore where header_status='MEMBER' order by group_number,disk_number;
生成asmcmd cp命令(数据库用户下执行) select 'asmcmd cp '||name||' /backup/'||substr(name,instr(name,'/',-1)+1) asmcmd_cp from v$controlfile union all select 'asmcmd cp '||name||' /backup/'||substr(name,instr(name,'/',-1)+1) from v$datafile union all select 'asmcmd cp '||member||' /backup/'||thread#||'_'||a.group#||'_'||substr(member,instr(member,'/',-1)+1) from v$log a, v$logfile b where a.group# = b.group#; ASMCMD_CP -------------------------------------------------------------------------------- asmcmd cp +DATA_PGOLD/racdb/control01.ctl /backup/control01.ctl asmcmd cp +DATA_PGOLD/racdb/control02.ctl /backup/control02.ctl asmcmd cp +DATA_PGOLD/racdb/system01.dbf /backup/system01.dbf asmcmd cp +DATA_PGOLD/racdb/sysaux01.dbf /backup/sysaux01.dbf asmcmd cp +DATA_PGOLD/racdb/undotbs01.dbf /backup/undotbs01.dbf asmcmd cp +DATA_PGOLD/racdb/users01.dbf /backup/users01.dbf asmcmd cp +DATA_PGOLD/racdb/undotbs02.dbf /backup/undotbs02.dbf asmcmd cp +DATA_PGOLD/racdb/redo02.log /backup/1_2_redo02.log asmcmd cp +DATA_PGOLD/racdb/redo01.log /backup/1_1_redo01.log asmcmd cp +DATA_PGOLD/racdb/redo03.log /backup/2_3_redo03.log asmcmd cp +DATA_PGOLD/racdb/redo04.log /backup/2_4_redo04.log 11 rows selected. SQL> 然后执行以上命令即可 生成rman copy命令(数据库用户下执行) select 'copy current controlfile to ''/tmp/'||substr(name,instr(name,'/',-1)+1)||''';' rman_copy from v$controlfile union all select 'copy datafile '||file#||' to ''/backup/'||substr(name,instr(name,'/',-1)+1)||''';' from v$datafile; RMAN_COPY -------------------------------------------------------------------------------- copy current controlfile to '/tmp/control01.ctl'; copy current controlfile to '/tmp/control02.ctl'; copy datafile 1 to '/backup/system01.dbf'; copy datafile 2 to '/backup/sysaux01.dbf'; copy datafile 3 to '/backup/undotbs01.dbf'; copy datafile 4 to '/backup/users01.dbf'; copy datafile 5 to '/backup/undotbs02.dbf'; 7 rows selected. SQL>
然后使用rman这些以上命令
