异机恢复在以下不同情况下处理方式不同:
一、当Oracle 参数文件,控制文件,数据文件,日志文件,密码文件都正常,且目标机器和源机器安装目录(即环境变量)都一致,则直接拷贝文件到相应目录,然后启动数据库即可;
二、当Oracle 参数文件,控制文件,数据文件,日志文件,密码文件都正常,但目标机器和源机器安装目录不一致,则直接拷贝文件到相应目录:
1. 首先vi pfile,将pfile里面的control file 和audit file,闪回区路径都改成目标机器上的路径;
2. sqlplus / as sysdba
利用pfile 创建spfile:SQL>create spfile from pfile='pfile 路径';
3. 然后startup nomount ——>没报错,继续alter database mount;
4. 由于控制文件中记录了数据文件(临时文件除外),日志文件的路径,所以需要在mount 状态下将这些文件路径修改正确;
alter database rename file ‘old_file_name’ to ‘new_file_name';
5. 然后再alter database open;这一步成功了,表示数据库正常启动了,查看状态,如果报错,则根据报错信息具体问题具体对待;
(注意:在打开数据库时,如果redolog路径正常,且权限正常,磁盘正常,都不需要做resetlog操作,resetlog 是将redo的sequence号只清空,从头开始写redolog。)
三、当Oracle 参数文件,控制文件,数据文件,日志文件,密码文件中某些文件有损坏情况下恢复操作:
1. 参数文件损坏:
startup nomount启动方式,查找文件的顺序是 spfileSID.ora-〉spfile.ora-〉initSID.ora-〉init.ora(spfile优先于pfile)
如果spfile 损坏,可以通过pfile来创建spfile,如果所有的spfile和pfile都损坏或丢失,则需要手动创建init.ora 参数文件,里面要写明控制文件等参数的正确路径,再启动到nomount下;
2. 控制文件损坏:
2-1. 损坏一个控制文件:
一般数据库里面控制文件会有多个副本,如果只是其中一个控制文件损坏,则可以拷贝正常的控制文件;
2.2. 如果所有的控制文件都损坏,则需要重建控制文件。如果系统上有重建控制文件的脚本,则可以用脚本重建控制文件,否则需要自定义脚本,修改其中数据文件,日志文件的路径来重建控制文件。
alter database backup controlfile to trace as '/home/oracle/crontol_trace.trc'; 该命令需要在mount状态下才能执行,如果当控制文件坏了,只能到nomount状态下时,就自定义脚本:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "EASTMONEY" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 4672
LOGFILE
GROUP 1 '/opt/app/oracle/oradata/eastmoney/redo01.log' SIZE 1024M,
GROUP 2 '/opt/app/oracle/oradata/eastmoney/redo02.log' SIZE 1024M,
GROUP 3 '/opt/app/oracle/oradata/eastmoney/redo03.log' SIZE 1024M
-- STANDBY LOGFILE
DATAFILE
'/opt/app/oracle/oradata/eastmoney/system01.dbf',
'/opt/app/oracle/oradata/eastmoney/undotbs01.dbf',
'/opt/app/oracle/oradata/eastmoney/sysaux01.dbf',
'/opt/app/oracle/oradata/eastmoney/users01.dbf',
'/opt/app/oracle/oradata/eastmoney/dev03.dbf'
CHARACTER SET ZHS16GBK;
除了以上重建控制文件方法外,如果有控制文件的
备份和数据库的备份,可以将其恢复出来来重建控制文件:
模拟情景:
SQL>ho rm /u01/orcl/control01.ctl
SQL>ho rm /u01/orcl/control02.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
SQL>
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
解决方法:
如果之前备份控制文件是是nocatalog模式,则restore controlfile命令会报错,必须要以autobackup来恢复,除非自己指定备份集恢复;
rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 29 16:34:31 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
^[[3~connected to target database: EMTEST (
DBID=1493189685
)
using target database control file instead of recovery catalog
红色是指数据库id
RMAN> set dbid 1788360963
executing command: SET DBID
RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/ORACLE/backupset/2016_06_27/o1_mf_ncsnf_TAG20160627T093153_cq10jkp9_.bkp';
Starting restore at 27-JUN-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/oracle/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/oracle/control02.ctl
Finished restore at 27-JUN-16
RMAN> sql 'alter database mount';
RMAN> restore database;
RMAN> recover database;
RMAN> sql 'alter database open';
注意:如果备份是以catalog模式备份的,即使没有打开controlfile的自动备份,进行restore controlfile时也不会报出如下错误:
3. 数据文件损坏:
1)、数据库在非归档有一个过去的冷备份情况下,数据文件丢失了,而且在线重做日志切换过多次,则会导致在冷备到数据文件丢失时刻的数据都会丢失;
恢复办法只能将冷备数据全部拷贝到数据库文件目录,恢复,只能恢复到冷备时间点的数据。而且恢复必须要把当时冷备的控制文件和数据文件,日志文件全部拿出来恢复,否则三者文件头不一致会报错。
2)、数据库在归档
有数据库的全备和归档日志的备份
3-2-1、丢失一个数据文件的情况:
模拟情景:
SQL>ho rm /u01/orcl/users01.dbf
SQL> shutdown immediate
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/orcl/users01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/orcl/users01.dbf'
恢复语法:
[oracle@oracle 2016_06_24]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jun 24 16:13:15 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORACLE (DBID=1788360963, not open)
RMAN> run {
2> allocate channel c1 type disk;
3> restore datafile 4;
4> recover datafile 4;
5> sql 'alter database datafile 4 online';
6> sql 'alter database open';
7> release channel c1;
8> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=20 device type=DISK
Starting restore at 24-JUN-16
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00004 to /u01/orcl/users01.dbf
channel c1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORACLE/backupset/2016_06_24/o1_mf_nnndf_TAG20160624T151220_cpsqbnp0_.bkp
channel c1: piece handle=/u01/app/oracle/fast_recovery_area/ORACLE/backupset/2016_06_24/o1_mf_nnndf_TAG20160624T151220_cpsqbnp0_.bkp tag=TAG20160624T151220
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:01
Finished restore at 24-JUN-16
Starting recover at 24-JUN-16
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-JUN-16
sql statement: alter database datafile 4 online
sql statement: alter database open
released channel: c1
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ WRITE
========================================================================================================
3-2-2、丢失所有数据文件的情况:
模拟情景:
SQL> ho rm /u01/orcl/*.dbf
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/orcl/system01.dbf'
恢复语法:
RMAN> run{
2> allocate channel c1 type disk;
3> restore database;
4> recover database;
5> sql 'alter database open';
6> release channel c1;
7> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=20 device type=DISK
Starting restore at 24-JUN-16
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /u01/orcl/system01.dbf
channel c1: restoring datafile 00002 to /u01/orcl/sysaux01.dbf
channel c1: restoring datafile 00003 to /u01/orcl/undotbs01.dbf
channel c1: restoring datafile 00004 to /u01/orcl/users01.dbf
channel c1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORACLE/backupset/2016_06_24/o1_mf_nnndf_TAG20160624T151220_cpsqbnp0_.bkp
channel c1: piece handle=/u01/app/oracle/fast_recovery_area/ORACLE/backupset/2016_06_24/o1_mf_nnndf_TAG20160624T151220_cpsqbnp0_.bkp tag=TAG20160624T151220
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:07
Finished restore at 24-JUN-16
Starting recover at 24-JUN-16
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 24-JUN-16
sql statement: alter database open
released channel: c1
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ WRITE
========================================================================================================
3-2-3. 损坏非当前联机日志成员(非current状态):
模拟情景:
SQL> ho rm /u01/orcl/redo03_1.log
SQL> shutdown immeidate;
SP2-0717: illegal SHUTDOWN option
SQL> shutdown immeidate
SP2-0717: illegal SHUTDOWN option
SQL>
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- -------------------------------------------------- ---------
2 ONLINE /u01/orcl/redo02.log NO
1 ONLINE /u01/orcl/redo01.log NO
3 ONLINE /u01/orcl/redo03.log NO
1 ONLINE /u01/orcl/redo01_1.log NO
2 ONLINE /u01/orcl/redo02_1.log NO
3 INVALID ONLINE /u01/orcl/redo03_1.log NO
解决方法:
SQL> alter database drop logfile member '/u01/orcl/redo03_1.log';
Database altered.
SQL> alter database add logfile member '/u01/orcl/redo03_1.log' to group 3;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- -------------------------------------------------- ---------
2 ONLINE /u01/orcl/redo02.log NO
1 ONLINE /u01/orcl/redo01.log NO
3 ONLINE /u01/orcl/redo03.log NO
1 ONLINE /u01/orcl/redo01_1.log NO
2 ONLINE /u01/orcl/redo02_1.log NO
3 ONLINE /u01/orcl/redo03_1.log NO
3-2-4. 损坏非当前联机日志组
模拟情景:
SQL> ho rm /u01/orcl/redo02.log
SQL> ho rm /u01/orcl/redo02_1.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 11005
Session ID: 1 Serial number: 5
解决方法
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter database clear logfile group 2;
Database altered.
SQL> alter database open;
Database altered.
注意:
如果该日志组还没有归档,则用
alter database clear unarchived logfile group 2;
3-3-5. 损坏所有联机日志:
模拟情景:
SQL> ho rm /u01/orcl/*.log
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 11155
Session ID: 1 Serial number: 5
解决方法:
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> alter system set "_allow_resetlogs_corruption"=true scope =spfile;
System altered.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ WRITE
4. 临时文件损坏:
模拟情景:
SQL> ho rm /u01/orcl/temp01.dbf
SQL> shutdown immeidate
SQL> startup
SQL>select file_name,tablespace_name from dba_temp_files;
如果此时临时文件自动出来了,则不需要恢复,如果重启数据库之后,临时文件没有出来,则需要删掉以前的,重新给临时表空间添加临时文件;
SQL> alter database tempfile '/u01/orcl/temp01.dbf' drop;
Database altered.
SQL> alter tablespace temp1 add tempfile '/u01/orcl/temp01.dbf' size 10M;
Tablespace altered.