非归档模式下备份数据库后到另一台机器上恢复的过程(源库和目的库路径不一致)

源端和目的端的操作系统和数据库:

Os:Red Hat Linux As 5

Db Version:10GR2

 

前期准备,需要备份源数据库,备份脚本可以参考如下，将备份集拷贝到目的机器上.在目的机器恢复的过程中,db_name,oracle_sid,dbid设置必须跟源数据库保持一致,若需要修改的情况下,等待数据库完成后通过nid来修改.

非归档模式下的备份脚本:

run
{
shutdown immediate;
startup mount; #noarchive mode,must be mount
allocate channel c1 type disk;
allocate channel c2 type disk;
backup database format '/u01/rman_backup/2011/201112/20111201/df_%t_%s_%p.bak';
release channel c1;
release channel c2;
alter database open;
}

备份完数据库将备份集拷贝到目的机器上后(具体拷贝过程可以通过FTP，我这里备份集拷贝到目的机器存放于/u01/rman_backup/mybak),下面进行在目的机器上的恢复.

 

步骤1:创建数据库恢复的路径跟源库路径不相同(源库的路径是/u01/app/oracle)
[oracle@hxl01 /]$ mkdir /u01/mydb

 

步骤2:创建数据库各文件目录
[oracle@hxl01 u01]$ mkdir -p /u01/mydb/oracl/{adump,bdump,cdump,dpdump,udump,pfile}
[oracle@hxl01 u01]$ mkdir -p /u01/mydb/oradata/oracl
[oracle@hxl01 u01]$ mkdir -p /u01/mydb/flash_recovery_area

 

步骤3:创建口令文件(linux口令文件命名格式为orapwSID,windows口令文件命名格式为pwdSID)
orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworacl.ora password=oracle

 

步骤4:恢复初始化参数文件(这里需要找到包含初始化参数文件的备份集,在源端数据库可以通过list backup可以找到包含参数文件的备份集)

[oracle@hxl01 dbs]$ export ORACLE_SID=oracl
[oracle@hxl01 dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 12 11:26:55 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/db s/initoracl.ora'
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 54528292 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
RMAN>
RMAN>restore spfile to pfile '/u01/app/oracle/product/10.2.0/db_1/dbs/initoracl.ora' from '/u01/rman_backup/mybak/df_768589735_8_1.bak';
Starting restore at 12-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /u01/rman_backup/mybak/df_768589735_8_1.bak
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 12-DEC-11
RMAN> shutdown immediate
Oracle instance shut down

步骤5:修改初始化参数文件

*.audit_file_dest='/u01/mydb/oracl/adump'
*.background_dump_dest='/u01/mydb/oracl/bdump'
*.control_files='/u01/mydb/oradata/oracl/control01.ctl','/u01/mydb/oradata/oracl/control02.ctl','/u01/mydb/oradata/oracl/control03.ctl'
*.core_dump_dest='/u01/mydb/oracl/cdump'
*.db_recovery_file_dest='/u01/mydb/flash_recovery_area'
*.user_dump_dest='/u01/mydb/oracl/udump'

步骤6:使用恢复的初始化参数文件nomount数据库

[oracle@hxl01 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 12 11:45:19 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initoracl.ora
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 159383800 bytes
Database Buffers 360710144 bytes
Redo Buffers 2973696 bytes
SQL>

步骤7:恢复控制文件

RMAN> restore controlfile from '/u01/rman_backup/mybak/df_768589733_7_1.bak';
Starting restore at 12-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/mydb/oradata/oracl/control01.ctl
output filename=/u01/mydb/oradata/oracl/control02.ctl
output filename=/u01/mydb/oradata/oracl/control03.ctl
Finished restore at 12-DEC-11
RMAN>

步骤8:启动数据库到mount状态

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

步骤9:将备份集的信息加入到catalog,使目前的控制文件记录有目前的备份信息,以便用户恢复.从远端数据库拷贝过来的备份集存放于目录/u01/rman_backup/mybak.

RMAN> catalog start with '/u01/rman_backup/mybak';
Starting implicit crosscheck backup at 12-DEC-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 5 objects
Finished implicit crosscheck backup at 12-DEC-11
Starting implicit crosscheck copy at 12-DEC-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-DEC-11
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/rman_backup/mybak
List of Files Unknown to the Database
=====================================
File Name: /u01/rman_backup/mybak/bash_profile.txt
File Name: /u01/rman_backup/mybak/df_768589733_7_1.bak
File Name: /u01/rman_backup/mybak/df_768589674_5_1.bak
File Name: /u01/rman_backup/mybak/df_768589674_6_1.bak
File Name: /u01/rman_backup/mybak/df_768589735_8_1.bak
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/rman_backup/mybak/df_768589733_7_1.bak
File Name: /u01/rman_backup/mybak/df_768589674_5_1.bak
File Name: /u01/rman_backup/mybak/df_768589674_6_1.bak
File Name: /u01/rman_backup/mybak/df_768589735_8_1.bak
List of Files Which Where Not Cataloged
=======================================
File Name: /u01/rman_backup/mybak/bash_profile.txt
  RMAN-07517: Reason: The file header is corrupted

步骤10:恢复数据文件,因为恢复的路径跟源库的数据文件路径不一致,需要用set newname转换路径.

RMAN> run{
set newname for datafile 1 to '/u01/mydb/oradata/oracl/system01.dbf';
set newname for datafile 2 to '/u01/mydb/oradata/oracl/undotbs01.dbf';
set newname for datafile 3 to '/u01/mydb/oradata/oracl/sysaux01.dbf';
set newname for datafile 4 to '/u01/mydb/oradata/oracl/users01.dbf';
set newname for datafile 5 to '/u01/mydb/oradata/oracl/hxl01.dbf';
restore database;
switch datafile all;
}
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 12-DEC-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/mydb/oradata/oracl/system01.dbf
restoring datafile 00004 to /u01/mydb/oradata/oracl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/mybak/df_768589674_5_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman_backup/mybak/df_768589674_5_1.bak tag=TAG20111130T170752
channel ORA_DISK_1: restore complete, elapsed time: 00:00:38
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /u01/mydb/oradata/oracl/undotbs01.dbf
restoring datafile 00003 to /u01/mydb/oradata/oracl/sysaux01.dbf
restoring datafile 00005 to /u01/mydb/oradata/oracl/hxl01.dbf
channel ORA_DISK_1: reading from backup piece /u01/rman_backup/mybak/df_768589674_6_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rman_backup/mybak/df_768589674_6_1.bak tag=TAG20111130T170752
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
Finished restore at 12-DEC-11
datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=769694142 filename=/u01/mydb/oradata/oracl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=769694142 filename=/u01/mydb/oradata/oracl/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=8 stamp=769694142 filename=/u01/mydb/oradata/oracl/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=9 stamp=769694142 filename=/u01/mydb/oradata/oracl/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=10 stamp=769694142 filename=/u01/mydb/oradata/oracl/hxl01.dbf

步骤11:打开数据库

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/oracl/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

这里报找不到redo日志文件的错误,因为数据库是非归档的,日志文件没有备份,在新库中也没有redo日志文件,这里需要重新创建日志文件组.

alter database drop logfile group 1;
alter database add logfile group 1 '/u01/mydb/oradata/oracl/redo01.log' size 50m;
 
alter database drop logfile group 2;
alter database add logfile group 2 '/u01/mydb/oradata/oracl/redo02.log' size 50m;
日志组1和组2能顺利的删除并重新创建.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance oracl (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/oracl/redo03.log'

呵呵,日志组3是当前日志组,当然删除不了.下面试着切换日志组,也不行,因为数据库未打开.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

下面试着修改日志文件的在控制文件中的路径(restore回来的控制文件中记录的日志文件路径是源端日志文件的路径)

SQL> alter database rename file  '/u01/app/oracle/oradata/oracl/redo03.log' TO '/u01/mydb/oradata/oracl/redo03.log';
Database altered.

SQL>alter database open resetlogs;

步骤12:处理临时表空间文件

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 159383800 bytes
Database Buffers 360710144 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select * from dba_temp_files;
Select * From Dba_Temp_Files
              *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/oracl/temp01.dbf'

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220360 bytes
Variable Size 159383800 bytes
Database Buffers 360710144 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database rename file '/u01/app/oracle/oradata/oracl/temp01.dbf' TO '/u01/mydb/oradata/oracl/temp01.dbf';

Database altered.


SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE_NAME
----------------------------------- ------------------------------

/u01/mydb/oradata/oracl/temp01.dbf TEMP

步骤13：创建spfile

SQL> connect / as sysdba
SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initoracl.ora';

步骤14：配置监听器

可以使用netca配置监听器,想必大家都比较熟悉配置过程,这里具体过程省略.

总结:目的端数据文件若跟源端数据文件不一致的情况下,在restore的时候需要使用set newname改变目的端数据文件的路径,同时日志文件和临时表空间文件与源端路径不一致的情况下,需要修改控制文件的中信息,以便控制文件能够识别目的端的路径.