写成批处理在计划任务中定时培训主要使用RMAN 备份:noarchivelog下:(mounted database not open) 只能执行数据库完全备份(是指不能单独备份表空间、数据文件等),备份时候数据库必须关闭。恢复管理器脚本: run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( database include current controlfile ); } 一级,非积累 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 1 ( database include current controlfile ); } 三级,积累 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 3 cumulative ( database include current controlfile ); } 恢复时候,在noarchive下,数据库必须mount才能恢复,open时候不能恢复 archivelog,and open 模式下1:整个库,包含archive log文件,full 恢复管理器脚本: run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( database include current controlfile ); backup ( archivelog all delete input ); } 恢复管理器脚本: run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( database include current controlfile ); backup ( archivelog from time 'Jun 12 2001 10:54:33' all delete input ); } 2:一级增量,不积累(默认为不积累) run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 1 ( database include current controlfile ); backup ( archivelog all delete input ); } 积累 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 1 cumulative ( database include current controlfile ); backup ( archivelog all delete input ); } 3:表空间,增量,不积累,包含全部日志 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 2 ( tablespace 'CFW_DAT', 'TEMP' include current controlfile ); backup ( archivelog all delete input ); } 4表空间完全备份,不含日志 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( tablespace 'CFW_DAT', 'TEMP' include current controlfile ); } 5表空间增量,含所有日志,不积累 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 2 ( tablespace 'CFW_DAT', 'TEMP' include current controlfile ); backup ( archivelog all delete input ); } 6:数据文件,不含日志,完全背份 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( datafile 'D:\DATA\CFW_IDX2.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' include current controlfile ); } 7:数据文件,含日志,增量背份 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup incremental level 2 ( datafile 'D:\DATA\CFW_IDX2.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' include current controlfile ); backup ( archivelog all delete input ); } 8:日志文件,所有,背份后删除 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog all delete input ); } 不删 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog all ); 时间段 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog from time 'Jun 12 2001 10:54:33' until time 'Jun 12 2001 10:54:33' all delete input ); } 时间点 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog from time 'Jun 12 2001 10:54:33' all delete input ); } 恢复脚本: 因为恢复是个复杂的事情,需要DBA谨慎地进行。 不能发在计划任务中。一、noarchivelog and mount(must) RMAN> run{ allocate channel DefaultChannel type disk format 'D:\B_0ADVE8CH_10_1'; restore database ; recover database; sql "alter database open"; } RMAN> run{ allocate channel d1 type disk; allocate channel d2 type disk; # Parallel Restore restore database; recover database; sql "alter database open";} 二、数据库状态:archivelog and open 数据库open时候,只能恢复表空间或数据文件,要恢复database,则必须mount状态 1:恢复表空间2个,没有重命名,执行恢复,不复原数据文件 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; sql "alter tablespace CFW_DAT offline immediate "; sql "alter tablespace CFW_IDX offline immediate "; restore ( tablespace 'CFW_DAT', 'CFW_IDX' ); recover tablespace 'CFW_DAT', 'CFW_IDX' ; sql "alter tablespace CFW_DAT online "; sql "alter tablespace CFW_IDX online ";} 2:恢复表空间2个,都做了有重命名,执行恢复,可能复原数据文件 'D:\DATA\CFW_IDX2.ORA' to 'e:\database\idx1.ora'. 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA' to 'e:\database\dat1.ora'. 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' to 'e:\database\idx2.ora'. 'F:\DATA\CFW_DAT2.ORA' to 'e:\databae\dat2.ora'. 脚本: run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; sql "alter tablespace CFW_DAT offline immediate "; sql "alter tablespace CFW_IDX offline immediate "; set newname for datafile 'D:\DATA\CFW_IDX2.ORA' to 'e:\database\idx1.ora'; set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA' to 'e:\database\dat1.ora'; set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' to 'e:\database\idx2.ora'; set newname for datafile 'F:\DATA\CFW_DAT2.ORA' to 'e:\databae\dat2.ora'; restore ( tablespace 'CFW_DAT', 'CFW_IDX' ); switch datafile all; recover tablespace 'CFW_DAT', 'CFW_IDX' ; sql "alter tablespace CFW_DAT online "; sql "alter tablespace CFW_IDX online ";} 结果:将已有的数据文件复原到了其他的位置,已有的数据文件将被自动切换。3:恢复数据文件2个,没有复原(没有重定位) 恢复管理器脚本: run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; restore ( datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' ); recover datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' ;} 4 恢复数据文件两个,并进行了重定位,自动切换 run { allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA' to 'd:\database\dat.ora'; set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_TMP1.ORA' to 'd:\database\idx.ora'; restore ( datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_TMP1.ORA' ); switch datafile all; recover datafile 'd:\database\dat.ora', 'd:\database\idx.ora';}
备份:noarchivelog下:(mounted database not open)
只能执行数据库完全备份(是指不能单独备份表空间、数据文件等),备份时候数据库必须关闭。恢复管理器脚本:
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup
( database include current controlfile );
}
一级,非积累
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 1
( database include current controlfile );
}
三级,积累
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 3 cumulative
( database include current controlfile );
} 恢复时候,在noarchive下,数据库必须mount才能恢复,open时候不能恢复
archivelog,and open 模式下1:整个库,包含archive log文件,full
恢复管理器脚本:
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup
( database include current controlfile );
backup ( archivelog all delete input );
} 恢复管理器脚本:
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup
( database include current controlfile );
backup ( archivelog from time 'Jun 12 2001 10:54:33' all delete input );
}
2:一级增量,不积累(默认为不积累)
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 1
( database include current controlfile );
backup ( archivelog all delete input );
}
积累
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 1 cumulative
( database include current controlfile );
backup ( archivelog all delete input );
}
3:表空间,增量,不积累,包含全部日志
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 2
( tablespace 'CFW_DAT', 'TEMP' include current controlfile );
backup ( archivelog all delete input );
} 4表空间完全备份,不含日志
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup
( tablespace 'CFW_DAT', 'TEMP' include current controlfile );
}
5表空间增量,含所有日志,不积累
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 2
( tablespace 'CFW_DAT', 'TEMP' include current controlfile );
backup ( archivelog all delete input );
}
6:数据文件,不含日志,完全背份
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup
( datafile 'D:\DATA\CFW_IDX2.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' include current controlfile );
}
7:数据文件,含日志,增量背份
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
backup incremental level 2
( datafile 'D:\DATA\CFW_IDX2.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' include current controlfile );
backup ( archivelog all delete input );
}
8:日志文件,所有,背份后删除
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog all delete input );
}
不删
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog all );
时间段
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog from time 'Jun 12 2001 10:54:33' until time 'Jun 12 2001 10:54:33' all delete input );
}
时间点
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p'; backup ( archivelog from time 'Jun 12 2001 10:54:33' all delete input );
}
恢复脚本:
因为恢复是个复杂的事情,需要DBA谨慎地进行。
不能发在计划任务中。一、noarchivelog and mount(must) RMAN> run{
allocate channel DefaultChannel type disk format 'D:\B_0ADVE8CH_10_1';
restore database ;
recover database;
sql "alter database open";
}
RMAN> run{
allocate channel d1 type disk;
allocate channel d2 type disk; # Parallel Restore
restore database;
recover database;
sql "alter database open";} 二、数据库状态:archivelog and open
数据库open时候,只能恢复表空间或数据文件,要恢复database,则必须mount状态
1:恢复表空间2个,没有重命名,执行恢复,不复原数据文件
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
sql "alter tablespace CFW_DAT offline immediate ";
sql "alter tablespace CFW_IDX offline immediate ";
restore ( tablespace 'CFW_DAT', 'CFW_IDX' );
recover tablespace 'CFW_DAT', 'CFW_IDX' ;
sql "alter tablespace CFW_DAT online ";
sql "alter tablespace CFW_IDX online ";}
2:恢复表空间2个,都做了有重命名,执行恢复,可能复原数据文件
'D:\DATA\CFW_IDX2.ORA' to 'e:\database\idx1.ora'.
'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA' to 'e:\database\dat1.ora'.
'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' to 'e:\database\idx2.ora'.
'F:\DATA\CFW_DAT2.ORA' to 'e:\databae\dat2.ora'.
脚本:
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
sql "alter tablespace CFW_DAT offline immediate ";
sql "alter tablespace CFW_IDX offline immediate ";
set newname for datafile 'D:\DATA\CFW_IDX2.ORA' to 'e:\database\idx1.ora';
set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA' to 'e:\database\dat1.ora';
set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' to 'e:\database\idx2.ora';
set newname for datafile 'F:\DATA\CFW_DAT2.ORA' to 'e:\databae\dat2.ora';
restore ( tablespace 'CFW_DAT', 'CFW_IDX' );
switch datafile all;
recover tablespace 'CFW_DAT', 'CFW_IDX' ;
sql "alter tablespace CFW_DAT online ";
sql "alter tablespace CFW_IDX online ";}
结果:将已有的数据文件复原到了其他的位置,已有的数据文件将被自动切换。3:恢复数据文件2个,没有复原(没有重定位)
恢复管理器脚本:
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
restore ( datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' );
recover datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_IDX1.ORA' ;} 4 恢复数据文件两个,并进行了重定位,自动切换
run {
allocate channel DefaultChannel type disk format 'E:\ORACLE\ORADATA\MFX\b_%u_%s_%p';
set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA' to 'd:\database\dat.ora';
set newname for datafile 'E:\ORACLE\ORA81\DATABASE\CFW_TMP1.ORA' to 'd:\database\idx.ora';
restore ( datafile 'E:\ORACLE\ORA81\DATABASE\CFW_DAT1.ORA', 'E:\ORACLE\ORA81\DATABASE\CFW_TMP1.ORA' );
switch datafile all;
recover datafile 'd:\database\dat.ora', 'd:\database\idx.ora';}
其一、bzszp(SongZip) 兄说的没有错,
保留所有数据文件,所有控制文件,所有联机REDO LOG 文件,NIT<sid>.ORA文件(最好要)。
但是重新创建数据库的时候,只要名称一致就可以了,有必要名称,表空间,数据文件等与原先的相同吗?????你是这样恢复的吗?我起码不是。
在关闭数据的时候拷贝回去(一定是关闭数据库),表空间,数据文件的信息是放在控制文件中的,有必要也建立一样的吗????
其二,luckysxn(风子) 是在胡闹
人家问怎么恢复,他在说怎么用备份管理器备份!!!
其三、 rjcludy说“当然前提是你要有有效的备份(注意是有效的)”,系统恢复的时候,为什么需要有效的备份,其实,以前的那四类文件就是一个相当于一个冷备份。不需要其它备份。
其四、scott_zy(虎牙)说的是以前需要有逻辑备份,但现在人家是系统崩溃后恢复数据库。
----------------------------
解决的办法是
1、保留所有数据文件,所有控制文件,所有联机REDO LOG 文件,INIT<sid>.ORA文件(相当于一个冷备份,可以拷贝到其它地方先)
2、重新安装系统和数据库。
3、建立一个与原数据库名称一样的数据库(包括SID)。
4、在关闭数据库的情况下拷贝回所有的四类文件到原来位置(一定是原来位置)
如果系统崩溃,数据库肯定停掉了,这时数据库文件的scn不一定一致,
相当于shutdown abort.
但这时的文件,也可以算是一个有效的冷备份。只要拷贝出来就可以。