最近公司一个生产系统由于业务部门同事误操作导致数据错乱且无法从应用层进行修复,经领导决定利用几天前的全备+归档日志进行数据库不完全恢复,由于服务器资源有限,只能在另一台数据库服务器进行数据库恢复,目录结构也不一致。经了解,此同事是早晨8点左右进行的误操作,目前心神已经凌乱了
;于是要将数据库恢复至6点位操作之前的状态,对于DBA来说此次恢复操作并不复杂,但是在操作过程中还是遇到了几个报错问题,简单的写了下步骤,有几个点大家要注意否则会报错,也跟大家分享一下
备份目标库
run{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/backup/orabk0_%d_%T_%s';
BACKUP DATABASE;
RELEASE CHANNEL C1;
}
连接到RMAN,set dbid=原库,先恢复spfile
请注意:
1、从备份中恢复spfile,必须在rman命令行startup nomount,然后restore spfile from ...
2、恢复时可能报错,是oracle的BUG,需export ORACLE_SID=xxx export ORA_RMAN_SGA_TARGET=350
create pfile from spfile修改各路径、内存大小(DBNAME不改),然后用pfile启动数据库到nomount
恢复出控制文件,restore controlfile from ...,然后mount数据库
恢复数据库
备份片新位置注册到控制文件:
catalog backuppiece '/backup/db_0iq51uq2_1_1','/home/barcode/db_0iq51uq2_1_1';
run{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
set newname for datafile 1 to '/oracle/oracletest/oradata/system01.dbf';
set newname for datafile 2 to '/oracle/oracletest/oradata/sysaux01.dbf';
set newname for datafile 3 to '/oracle/oracletest/oradata/undotbs01.dbf';
set newname for datafile 4 to '/oracle/oracletest/oradata/users01.dbf';
set newname for datafile 5 to '/oracle/oracletest/oradata/hoau_tbs01.dbf';
set newname for datafile 6 to '/oracle/oracletest/oradata/TRAINING.dbf';
set newname for datafile 7 to '/oracle/oracletest/oradata/training_tnt_tbs01.dbf';
set newname for datafile 8 to '/oracle/oracletest/oradata/HOAUTBL01.dbf';
set newname for datafile 9 to '/oracle/oracletest/oradata/users02.dbf';
set newname for datafile 10 to '/oracle/oracletest/oradata/drtbs1.dbf';
set newname for datafile 11 to '/oracle/oracletest/oradata/drtbs2.dbf';
restore database;
switch datafile all;
RELEASE CHANNEL C1;
}
--switch datafile all新路径注册到控制文件
恢复到时间点
run
{
set until time "to_date('2015-04-23 06:00:00','yyyy-mm-dd hh24:mi:ss')";
recover database;
alter database open resetlogs;
}
启动时会自动创建redo文件和临时表空间,建议启动前先在控制文件中定义好目录
如启动失败:1、创建redo目录 2、清除current日志组alter database clear logfile group 3;
注册到监听:
alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.39.251.181)(PORT=1521))';
默认会将数据库的名字注册到监听中作为服务名,也可以在数据库中service_name中定义服务名
![](http://blog.itpub.net/kindeditor/plugins/emoticons/images/1.gif)
![](http://blog.itpub.net/kindeditor/plugins/emoticons/images/13.gif)
备份目标库
run{
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/backup/orabk0_%d_%T_%s';
BACKUP DATABASE;
RELEASE CHANNEL C1;
}
连接到RMAN,set dbid=原库,先恢复spfile
请注意:
1、从备份中恢复spfile,必须在rman命令行startup nomount,然后restore spfile from ...
2、恢复时可能报错,是oracle的BUG,需export ORACLE_SID=xxx export ORA_RMAN_SGA_TARGET=350
create pfile from spfile修改各路径、内存大小(DBNAME不改),然后用pfile启动数据库到nomount
恢复出控制文件,restore controlfile from ...,然后mount数据库
恢复数据库
备份片新位置注册到控制文件:
catalog backuppiece '/backup/db_0iq51uq2_1_1','/home/barcode/db_0iq51uq2_1_1';
run{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
set newname for datafile 1 to '/oracle/oracletest/oradata/system01.dbf';
set newname for datafile 2 to '/oracle/oracletest/oradata/sysaux01.dbf';
set newname for datafile 3 to '/oracle/oracletest/oradata/undotbs01.dbf';
set newname for datafile 4 to '/oracle/oracletest/oradata/users01.dbf';
set newname for datafile 5 to '/oracle/oracletest/oradata/hoau_tbs01.dbf';
set newname for datafile 6 to '/oracle/oracletest/oradata/TRAINING.dbf';
set newname for datafile 7 to '/oracle/oracletest/oradata/training_tnt_tbs01.dbf';
set newname for datafile 8 to '/oracle/oracletest/oradata/HOAUTBL01.dbf';
set newname for datafile 9 to '/oracle/oracletest/oradata/users02.dbf';
set newname for datafile 10 to '/oracle/oracletest/oradata/drtbs1.dbf';
set newname for datafile 11 to '/oracle/oracletest/oradata/drtbs2.dbf';
restore database;
switch datafile all;
RELEASE CHANNEL C1;
}
--switch datafile all新路径注册到控制文件
恢复到时间点
run
{
set until time "to_date('2015-04-23 06:00:00','yyyy-mm-dd hh24:mi:ss')";
recover database;
alter database open resetlogs;
}
启动时会自动创建redo文件和临时表空间,建议启动前先在控制文件中定义好目录
如启动失败:1、创建redo目录 2、清除current日志组alter database clear logfile group 3;
注册到监听:
alter system set local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=10.39.251.181)(PORT=1521))';
默认会将数据库的名字注册到监听中作为服务名,也可以在数据库中service_name中定义服务名
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29018063/viewspace-2063860/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29018063/viewspace-2063860/