Oracle异机恢复处理
在平时工作中，我们可能会或多或少遇到数据迁移问题，比如：数据库之前在windows机器上，然后我们由于更换新服务器，想把数据迁移到新服务器上，新服务器安装的是linux系统。由于系统不同，目录结构肯定也不一样，所以在备份恢复数据时，我们就遇到很多问题。下面分享下我处理这种问题的恢复方法。
环境：SUSE linux10（由于资源有限，源库和目标库都在此服务器上，但不影响操作方法，源库和目标库的文件位置不同，这个是重点，因为异构系统的文件存放路径肯定不一致，比如上面提到windows系统的库恢复到linux系统上）。
源库：db_name：ceshidb，instance_name： ceshidb
目标库：db_name： ceshidb,instance_name： oric
测试版本：10.2.0.5
几点说明：
（1） RMAN 恢复的时候，db_name必须相同。如果说要想改成其他的数据库名，可以在恢复成功后，用nid 命令修改。数据库名的信息会记录到控制文件里，所以如果在恢复的时候，如果db_name不一致，恢复的时候会报错。
（2） 如果恢复的路径和源库不一致，就需要在restore时用set 命令指定新位置。 并且使用switch datafile all将信息更新的到控制文件。
开始实战操作
一：备份源库数据
先做全库备份（可以把归档备份和控制文件备份写到一个run里面，我是分开备份的，目的在于说明下备份顺序，全库备份-归档日志备份-控制文件备份，建议大家采用这样的顺序来备份数据，因为一般都没有配置catalog库，所以备份的信息存储在控制文件中，当还原数据时，首先还原控制文件，控制文件记录了你的备份信息，如果先备份控制文件，那么在备份的控制文件之后做的备份信息，就没有记录在此备份的控制文件中，请大家注意哈）
全库备份
oracle@nwsj:/u01/oracle/oradata> rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Thu Jan 21 18:49:522016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: CESHIDB (DBID=2027504492)
RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup database format '/u01/backup_full_%U';
release channel d1;
release channel d2;
}
allocate channel d1 type disk;
allocate channel d2 type disk;
Starting backup at 21-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/u01/oracle/oradata/ceshiku/cs_dir03.dbf
input datafile fno=00004 name=/u01/oracle/oradata/ceshiku/users01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/ceshiku/undotbs01.dbf
input datafile fno=00001 name=/u01/oracle/oradata/ceshiku/system01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/ceshiku/sysaux01.dbf
input datafile fno=00006 name=/u01/oracle/oradata/ceshiku/CS_DIR02.dbf
input datafile fno=00007 name=/u01/oracle/oradata/ceshiku/aa01.dbf
input datafile fno=00005 name=/u01/oracle/oradata/ceshiku/cs_dir01.dbf
input datafile fno=00010 name=/u01/oracle/oradata/ceshiku/b02.dbf
input datafile fno=00008 name=/u01/oracle/oradata/ceshiku/b01.dbf
channel ORA_DISK_1: starting piece 1 at 21-JAN-16
channel ORA_DISK_1: finished piece 1 at 21-JAN-16
piece handle=/u01/backup_full_01qrurnv_1_1 tag=TAG20160121T185039comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 21-JAN-16
channel ORA_DISK_1: finished piece 1 at 21-JAN-16
piece handle=/u01/backup_full_02qruroe_1_1 tag=TAG20160121T185039comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
released channel: d1
released channel: d2
Finished backup at 21-JAN-16
切换下日志
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
RMAN> sql 'alter system switch logfile';
sql statement: alter system switch logfile
归档日志备份
RMAN> backup archivelog all format '/u01/backup_arch_%U';
Starting backup at 21-JAN-16
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=1 stamp=901738168
input archive log thread=1 sequence=2 recid=2 stamp=901738171
input archive log thread=1 sequence=3 recid=3 stamp=901738174
input archive log thread=1 sequence=4 recid=4 stamp=901738276
input archive log thread=1 sequence=5 recid=5 stamp=901738282
input archive log thread=1 sequence=6 recid=6 stamp=901738288
input archive log thread=1 sequence=7 recid=7 stamp=901738309
channel ORA_DISK_1: starting piece 1 at 21-JAN-16
channel ORA_DISK_1: finished piece 1 at 21-JAN-16
piece handle=/u01/backup_arch_03qrurq5_1_1 tag=TAG20160121T185149comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 21-JAN-16
控制文件备份
RMAN> backup current controlfile format'/u01/backup_con_%U';
Starting backup at 21-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackupset
channel ORA_DISK_1: specifying datafile(s)in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at21-JAN-16
channel ORA_DISK_1: finished piece 1 at21-JAN-16
piece handle=/u01/backup_con_04qrurr1_1_1tag=TAG20160121T185217 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 21-JAN-16
然后关闭源库ceshidb（因为我是在同一台服务器上做的，所以需要修改下源库的db_name，否则在目标库恢复了控制文件后，不能mount的，会提示：cannot mount database inEXCLUSIVE mode，因为在$ORACLE_HOME/dbs下面的lkceshidb文件存在，进程在运行中，所以会有这个提示。如果是不同的服务器，则不需要此操作，顺便练习下nid方式来修改数据库名）
oracle@nwsj:/u01/oracle> nid target=/dbname=ceshiku
DBNEWID: Release 10.2.0.5.0 - Production onThu Jan 21 18:53:52 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database CESHIDB(DBID=2027504492)
Connected to server version 10.2.0
Control Files in database:
/u01/oracle/oradata/ceshiku/control01.ctl
/u01/oracle/oradata/ceshiku/control02.ctl
/u01/oracle/oradata/ceshiku/control03.ctl
Change database ID and database nameCESHIDB to CESHIKU? (Y/[N]) => y
Proceeding with operation
Changing database ID from 2027504492 to429152512
Changing database name from CESHIDB toCESHIKU
Control File /u01/oracle/oradata/ceshiku/control01.ctl - modified
Control File /u01/oracle/oradata/ceshiku/control02.ctl - modified
Control File/u01/oracle/oradata/ceshiku/control03.ctl - modified
Datafile /u01/oracle/oradata/ceshiku/system01.dbf - dbid changed, wrotenew name
Datafile /u01/oracle/oradata/ceshiku/undotbs01.dbf - dbid changed, wrotenew name
Datafile /u01/oracle/oradata/ceshiku/sysaux01.dbf - dbid changed, wrotenew name
Datafile /u01/oracle/oradata/ceshiku/users01.dbf - dbid changed, wrotenew name
Datafile /u01/oracle/oradata/ceshiku/cs_dir01.dbf - dbid changed, wrotenew name
Datafile /u01/oracle/oradata/ceshiku/CS_DIR02.dbf - dbid changed, wrotenew name
Datafile /u01/oracle/oradata/ceshiku/aa01.dbf - dbid changed, wrote newname
Datafile /u01/oracle/oradata/ceshiku/b01.dbf - dbid changed, wrote newname
Datafile /u01/oracle/oradata/ceshiku/cs_dir03.dbf - dbid changed, wrote new name
Datafile /u01/oracle/oradata/ceshiku/b02.dbf - dbid changed, wrote newname
Control File /u01/oracle/oradata/ceshiku/control01.ctl - dbid changed,wrote new name
Control File /u01/oracle/oradata/ceshiku/control02.ctl - dbid changed,wrote new name
Control File /u01/oracle/oradata/ceshiku/control03.ctl - dbid changed,wrote new name
Instance shut down
Database name changed to CESHIKU.
Modify parameter file and generate a newpassword file before restarting.
Database ID for database CESHIKU changed to429152512.
All previous backups and archived redo logsfor this database are unusable.
Database is not aware of previous backupsand archived logs in Recovery Area.
Database has been shutdown, open databasewith RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
oracle@nwsj:/u01/oracle> sqlplus / assysdba
SQL*Plus: Release 10.2.0.5.0 - Productionon Thu Jan 21 18:54:03 2016
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> alter system set db_name='ceshiku'scope=spfile;
System altered.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2097624 bytes
Variable Size 939527720 bytes
Database Buffers 1174405120 bytes
Redo Buffers 31453184 bytes
Database mounted.
ORA-01589: must use RESETLOGS orNORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
Ok，至此，源库修改db_name完成。
二：在目标库恢复
创建目标库需要的目录，此处省略…………
创建目标库的口令文件
oracle@nwsj:/u01/oracle> orapwd file=?/dbs/orapwdoric password=oracle
创建初始化参数文件
将源库的pfile文件拷贝过来，修改里面的实例名和路径，然后 启动oric实例到nomount状态
oracle@nwsj:/u01/oracle>export ORACLE_SID=oric
SQL> create spflie from pfile=’/home/oracle/initoric.pfile’;
spfile created.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2101608 bytes
Variable Size 260050584 bytes
Database Buffers 788529152 bytes
Redo Buffers 23060480 bytes
RMAN恢复控制文件并启动数据库到mount
oracle@nwsj:/u01/oracle/product/10.2.0/db_1/dbs>$ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.5.0 -Production on Thu Jan 21 19:33:34 2016
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ceshidb (notmounted)
RMAN> restore controlfile from'/u01/backup_con_04qrurr1_1_1';
Starting restore at 21-JAN-16
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
outputfilename=/u01/oracle/oradata/oric/control01.ctl
outputfilename=/u01/oracle/oradata/oric/control02.ctl
outputfilename=/u01/oracle/oradata/oric/control03.ctl
Finished restore at 21-JAN-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN恢复数据文件
RMAN> run
2> {
3> allocate channel d1 type disk;
4> allocate channel d2 type disk;
5> set newname for datafile'/u01/oracle/oradata/ceshiku/system01.dbf' to '/u01/oracle/oradata/oric/system01.dbf';
6> set newname for datafile'/u01/oracle/oradata/ceshiku/undotbs01.dbf' to'/u01/oracle/oradata/oric/undotbs01.dbf';
7> set newname for datafile'/u01/oracle/oradata/ceshiku/sysaux01.dbf' to '/u01/oracle/oradata/oric/sysaux01.dbf';
8> set newname for datafile'/u01/oracle/oradata/ceshiku/users01.dbf' to'/u01/oracle/oradata/oric/users01.dbf';
9> set newname for datafile'/u01/oracle/oradata/ceshiku/cs_dir01.dbf' to'/u01/oracle/oradata/oric/cs_dir01.dbf';
10> set newname for datafile'/u01/oracle/oradata/ceshiku/CS_DIR02.dbf' to'/u01/oracle/oradata/oric/CS_DIR02.dbf';
11> set newname for datafile'/u01/oracle/oradata/ceshiku/aa01.dbf' to '/u01/oracle/oradata/oric/aa01.dbf';
12> set newname for datafile '/u01/oracle/oradata/ceshiku/b01.dbf'to '/u01/oracle/oradata/oric/b01.dbf';
13> set newname for datafile'/u01/oracle/oradata/ceshiku/cs_dir03.dbf ' to'/u01/oracle/oradata/oric/cs_dir03.dbf ';
14> set newname for datafile'/u01/oracle/oradata/ceshiku/b02.dbf' to '/u01/oracle/oradata/oric/b02.dbf';
15> restore database;
16> switch datafile all;
17> release channel d1;
18> release channel d2;
19> }
allocated channel: d1
channel d1: sid=152 devtype=DISK
allocated channel: d2
channel d2: sid=151 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 21-JAN-16
Starting implicit crosscheck backup at21-JAN-16
Crosschecked 3 objects
Finished implicit crosscheck backup at21-JAN-16
Starting implicit crosscheck copy at 21-JAN-16
Finished implicit crosscheck copy at21-JAN-16
searching for all files in the recoveryarea
cataloging files...
no files cataloged
channel d1: starting datafile backupsetrestore
channel d1: specifying datafile(s) torestore from backup set
restoring datafile 00001 to/u01/oracle/oradata/oric/system01.dbf
restoring datafile 00002 to/u01/oracle/oradata/oric/undotbs01.dbf
restoring datafile 00003 to/u01/oracle/oradata/oric/sysaux01.dbf
restoring datafile 00004 to/u01/oracle/oradata/oric/users01.dbf
restoring datafile 00005 to/u01/oracle/oradata/oric/cs_dir01.dbf
restoring datafile 00006 to/u01/oracle/oradata/oric/CS_DIR02.dbf
restoring datafile 00007 to/u01/oracle/oradata/oric/aa01.dbf
restoring datafile 00008 to/u01/oracle/oradata/oric/b01.dbf
restoring datafile 00009 to/u01/oracle/oradata/oric/cs_dir03.dbf
restoring datafile 00010 to/u01/oracle/oradata/oric/b02.dbf
channel d1: reading from backup piece/u01/backup_full_01qrurnv_1_1
channel d1: restored backup piece 1
piece handle=/u01/backup_full_01qrurnv_1_1tag=TAG20160121T185039
channel d1: restore complete, elapsed time:00:01:05
Finished restore at 21-JAN-16
datafile 1 switched to datafile copy
input datafile copy recid=11 stamp=901740940filename=/u01/oracle/oradata/oric/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=12stamp=901740940 filename=/u01/oracle/oradata/oric/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=13 stamp=901740940filename=/u01/oracle/oradata/oric/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=14stamp=901740940 filename=/u01/oracle/oradata/oric/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=15stamp=901740940 filename=/u01/oracle/oradata/oric/cs_dir01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=16stamp=901740940 filename=/u01/oracle/oradata/oric/CS_DIR02.dbf
datafile 7 switched to datafile copy
input datafile copy recid=17stamp=901740940 filename=/u01/oracle/oradata/oric/aa01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=18stamp=901740940 filename=/u01/oracle/oradata/oric/b01.dbf
datafile 9 switched to datafile copy
input datafile copy recid=19stamp=901740940 filename=/u01/oracle/oradata/oric/cs_dir03.dbf
datafile 10 switched to datafile copy
input datafile copy recid=20stamp=901740940 filename=/u01/oracle/oradata/oric/b02.dbf
released channel: d1
released channel: d2
（注意，因为我们的路径不同，所以我们需要使用set 命令转换一下路径。
还需要switch datafile all，是用来更新控制文件里的信息。因为数据文件的路径信息等存在控制文件中）
RECOVER DB
RMAN> recover database;
Starting recover at 21-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
starting media recovery
archive log thread 1 sequence 4 is already on disk as file/u01/backup/ceshiku/archivelog/1_4_901735878.dbf
archive log thread 1 sequence 5 is already on disk as file/u01/backup/ceshiku/archivelog/1_5_901735878.dbf
archive log thread 1 sequence 6 is already on disk as file/u01/backup/ceshiku/archivelog/1_6_901735878.dbf
archive log thread 1 sequence 7 is already on disk as file/u01/backup/ceshiku/archivelog/1_7_901735878.dbf
archive log filename=/u01/backup/ceshiku/archivelog/1_4_901735878.dbfthread=1 sequence=4
archive log filename=/u01/backup/ceshiku/archivelog/1_5_901735878.dbfthread=1 sequence=5
archive log filename=/u01/backup/ceshiku/archivelog/1_6_901735878.dbfthread=1 sequence=6
archive log filename=/u01/backup/ceshiku/archivelog/1_7_901735878.dbfthread=1 sequence=7
unable to find archive log
archive log thread=1 sequence=8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/21/2016 19:41:13
RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn9671019808993
因为缺少online redolog，所以提示找不到thread 1 seq 8日志组，我们可以在recover database时候，通过set until scn或者setuntil time命令设置恢复到的scn号或时间。就可以避免这个错误。
查询下v$datafile_header记录的scn和v$datafile记录的scn，以及系统scn，还有数据文件终止scn，如下：
SQL> selectname,to_char(checkpoint_change#) from v$datafile_header;
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/system01.dbf
9671019808993
/u01/oracle/oradata/oric/undotbs01.dbf
9671019808993
/u01/oracle/oradata/oric/sysaux01.dbf
9671019808993
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/users01.dbf
9671019808993
/u01/oracle/oradata/oric/cs_dir01.dbf
9671019808993
/u01/oracle/oradata/oric/CS_DIR02.dbf
9671019808993
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/aa01.dbf
9671019808993
/u01/oracle/oradata/oric/b01.dbf
9671019808993
/u01/oracle/oradata/oric/cs_dir03.dbf
9671019808993
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/b02.dbf
9671019808993
10 rows selected.
SQL> selectname,to_char(checkpoint_change#) from v$datafile;
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/system01.dbf
9671019808993
/u01/oracle/oradata/oric/undotbs01.dbf
9671019808993
/u01/oracle/oradata/oric/sysaux01.dbf
9671019808993
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/users01.dbf
9671019808993
/u01/oracle/oradata/oric/cs_dir01.dbf
9671019808993
/u01/oracle/oradata/oric/CS_DIR02.dbf
9671019808993
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/aa01.dbf
9671019808993
/u01/oracle/oradata/oric/b01.dbf
9671019808993
/u01/oracle/oradata/oric/cs_dir03.dbf
9671019808993
NAME
--------------------------------------------------------------------------------
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/b02.dbf
9671019808993
10 rows selected.
SQL> selectto_char(checkpoint_change#) from v$database;
TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
9671019808993
SQL> selectname,to_char(last_change#) from v$datafile;
NAME
--------------------------------------------------------------------------------
TO_CHAR(LAST_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/system01.dbf
/u01/oracle/oradata/oric/undotbs01.dbf
/u01/oracle/oradata/oric/sysaux01.dbf
NAME
--------------------------------------------------------------------------------
TO_CHAR(LAST_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/users01.dbf
/u01/oracle/oradata/oric/cs_dir01.dbf
/u01/oracle/oradata/oric/CS_DIR02.dbf
NAME
--------------------------------------------------------------------------------
TO_CHAR(LAST_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/aa01.dbf
/u01/oracle/oradata/oric/b01.dbf
/u01/oracle/oradata/oric/cs_dir03.dbf
NAME
--------------------------------------------------------------------------------
TO_CHAR(LAST_CHANGE#)
----------------------------------------
/u01/oracle/oradata/oric/b02.dbf
10 rows selected.
数据库重新启动的时候，Oracle将文件头中的那个启动scn（v$datafile_header中记录的scn）与数据库文件检查点scn进行比较，如果这两个值相互匹配，oracle接下来还要比较数据文件头中的启动scn和控制文件中数据文件的终止scn。如果这两个值也一致，就意味着所有数据块多已经提交，所有对数据库的修改都没有在关闭数据库的过程中丢失，因此这次启动数据库的过程也不需要任何恢复操作，此时数据库就可以打开了。当所有的数据库都打开之后，存储在控制文件中的数据文件终止scn的值再次被更改为null，这表示数据文件已经打开并能够正常使用了。如果数据库是异常关闭或者通过不完全恢复过来的，那么终止scn会是null，所以启动数据库时，就需要做实例恢复，利用online redolog来恢复数据库，这也就是实例启动时，SMON进程会去利用redolog做恢复，俗称‘前滚‘数据库。
通过查询上述SCN号，我们可以看到，我们做了不完全恢复，需要以open resetlogs方式打开数据库。
尝试openresetlogs启动数据库
SQL> alter database openresetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-createonline log
'/u01/oracle/oradata/ceshiku/redo01.log'
ORA-27040: file create error,unable to create file
Linux-x86_64 Error: 2: No suchfile or directory
发现启动不了的，提示不能创建redo01.log，没有此目录（我是把/u01/oracle/oradata/ceshiku这个目录改名了，异构系统的话，也不会存在源库这样的目录，比如windows到linux）
接下来我们查询日志文件信息，如下：
SQL> select * fromv$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
2 ONLINE
/u01/oracle/oradata/ceshiku/redo02.log
NO
1 ONLINE
/u01/oracle/oradata/ceshiku/redo01.log
NO
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_
---
3 ONLINE
/u01/oracle/oradata/ceshiku/redo03.log
NO
可以看到控制文件记录的日志信息还是源库的，查询v$log视图
SQL> select sequence#,group#,status from v$log;
SEQUENCE# GROUP# STATUS
---------- ---------- ----------------
8 1 CURRENT
9 2 INACTIVE
10 3 INACTIVE
可以看到日志8处于current，那么想通过新增日志组，删除8,9,10日志组是不行的，因为current日志组是不能删掉的。
我们虽然不能删除8这个日志组，但是可以新增日志组文件，然后重建控制文件，清除控制文件保留的日志信息，然后以open resetlogs方式打开数据库。具体操作如下：
SQL> alter database addlogfile group 4'/u01/oracle/oradata/oric/redo04.dbf' size 50M;
Database altered.
SQL> alter database addlogfile group 5 '/u01/oracle/oradata/oric/redo05.dbf' size 50M;
Database altered.
SQL> alter database addlogfile group 6 '/u01/oracle/oradata/oric/redo06.dbf' size 50M;
Database altered.
备份控制文件到trace文件
SQL> alter database backupcontrolfile to trace as '/home/oracle/aaa.ctl';
Database altered.
然后打开这个trace文件，摘出里面重建控制文件，制作脚本。
关闭数据库，重新启动到nomount，然后重建控制文件。（注意，以resetlogs方式重建控制文件）
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area1073741824 bytes
Fixed Size 2101608 bytes
Variable Size 260050584 bytes
Database Buffers 788529152 bytes
Redo Buffers 23060480 bytes
SQL> CREATE CONTROLFILE REUSEDATABASE "CESHIDB" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 '/u01/oracle/oradata/oric/redo04.dbf' SIZE 50M,
9 GROUP 5'/u01/oracle/oradata/oric/redo05.dbf' SIZE 50M,
10 GROUP 6 '/u01/oracle/oradata/oric/redo06.dbf' SIZE 50M
11 --STANDBY LOGFILE
12 DATAFILE
13 '/u01/oracle/oradata/oric/system01.dbf',
14 '/u01/oracle/oradata/oric/undotbs01.dbf',
15 '/u01/oracle/oradata/oric/sysaux01.dbf',
16 '/u01/oracle/oradata/oric/users01.dbf',
17 '/u01/oracle/oradata/oric/cs_dir01.dbf',
18 '/u01/oracle/oradata/oric/CS_DIR02.dbf',
19 '/u01/oracle/oradata/oric/aa01.dbf',
20 '/u01/oracle/oradata/oric/b01.dbf',
21 '/u01/oracle/oradata/oric/cs_dir03.dbf ',
22 '/u01/oracle/oradata/oric/b02.dbf'
23 CHARACTER SET ZHS16GBK;
Control file created.
然后以open resetlogs方式打开库。
SQL> alter database openresetlogs;
Database altered.
完成后，还需要善后工作，重建temp表空间
SQL> create temporary tablespace TEMP1 TEMPFILE '/u01/oracle/oradata/oric/temp01.DBF'SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;
至此所有工作顺利完毕。
后记：之前看过很多人写的异机恢复数据库，目录结构也不同，在做完recover database，就以open resetlogs方式打开库了，然后才去处理online redolog和temp表空间，可是仔细想下下此问题，包括我上面的实验，可以看到，实际上是不能直接open resetlogs，因为控制文件属于源库的，还原到目标库上，在restoredatabase时，通过set new name for的方式把数据文件恢复到了新的目录上，通过switch datafile all，把数据文件的路径信息更新到控制文件中，但是onlineredolog的信息一点都没有变化的，所以在尝试直接open resetlogs启动数据库时，会报错的。除非你可以创建一个和源库一样的目录，来记录online redolog，然后open resetlogs后，在添加日志组，删除之前旧的日志组。（如果是异构系统，比如windows到linux，是创建不了这样的目录的，所以异构系统，建议采用上述方式来处理）
————————————————
版权声明：本文为CSDN博主「renhui1234」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_22935429/java/article/details/50558321