记一次数据迁移

本次试验将完成一次源数据库到目标数据库的迁移

源数据库所在系统ip地址：192.168.133.128

目标数据库所在系统ip地址：192.168.133.132

源数据库文件位置：/u01/app/oracle/oradata/orcl

目标数据库文件位置： /u01/app/oracle/oradata/orcl

( 二者目录最好相同，以避免不必要的麻烦)

 

在目标数据库所在的系统上要 创建相应的目录 

数据文件和控制文件的目录：

[ oracle@localhost oracle]$ mkdir -p oradata/orcl

[ oracle@localhost oradata]$ pwd 
/u01/app/oracle/oradata

日志文件和跟踪文件的目录：

[ oracle@localhost oracle]$ mkdir -p diag/rdbms/orcl/orcl/trace

[ oracle@localhost trace]$ pwd 
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

闪回区：

[ oracle@localhost oracle]$ mkdir -p flash_recovery_area/orcl

[ oracle@localhost orcl]$ pwd 
/u01/app/oracle/flash_recovery_area/orcl

归档日志文件目录：

[ oracle@localhost app]$ mkdir -p archivelog/dest1

[ oracle@localhost dest1]$ pwd 
/u01/app/archivelog/dest1

审计相关目录：

[ oracle@localhost oracle]$ mkdir -p admin/orcl/adump

[ oracle@localhost adump]$ pwd 
/u01/app/oracle/admin/orcl/adump

备份目录：

[ oracle@localhost orcl]$ pwd 
/backup/orcl

使用rman将源数据库做一次全备：

在备份之前要做一次归档

SYS@orcl 28-OCT-14> alter system switch logfile; 

System altered.

打开rman进行全库备份：

[ oracle@oracle /]$ rman target sys/oracle nocatalog 

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 28 05:21:06 2014 

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 

connected to target database: ORCL (DBID=1383986269) 
using target database control file instead of recovery catalog

RMAN> run{

2> configure device type disk parallelism 2;

3> configure controlfile autobackup on;

4> configure controlfile autobackup format for device type disk to '/u01/app/backup/mvbackup/controlfile_%F';

5> allocate channel c1 type disk format '/u01/app/backup/mvbackup/fulldb_%U';

6> backup spfile format '/u01/app/backup/mvbackup/spfile_%d_%s';

7> backup database plus archivelog tag 'fulldb' delete input;

8> release channel c1;

9> }

接下来就可以使用scp命令将生成的备份文件发送到目标系统192.168.133.132：

[ oracle@oracle mvbackup]$ scp ./*  oracle@192.168.133.132 :/backup/orcl/


在目标系统/backup/orcl下执行watch -n 1 du -sh *可以看到文件正在拷贝：

到相应目录下查看是否传完：

[ oracle@localhost orcl]$ pwd 
/backup/orcl 
[ oracle@localhost orcl]$ ls 
controlfile_c-1383986269-20141028-01 fulldb_4cpm5oqc_1_1 fulldb_4dpm5p1c_1_1 fulldb_4epm5p6g_1_1 fulldb_4fpm5pa7_1_1 spfile_ORCL_138 spfile_ORCL_139

 

首先 通过rman恢复spfile：

  [ oracle@localhost  ~]$ rman target / 

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 28 20:18:06 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. 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/11.2.0/dbhome_1/dbs/initorcl.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1335192 bytes

Variable Size 75497576 bytes

Database Buffers 79691776 bytes

Redo Buffers 2494464 bytes

RMAN> restore spfile to '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from '/backup/orcl/spfile_ORCL_139';

Starting restore at 28-OCT-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /backup/orcl/spfile_ORCL_139

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 28-OCT-14

由于与目标服务器路径不相同，所以恢复完spfile之后，需要生成pfile文件进行编辑修改参数值：

[ oracle@localhost orcl]$ sqlplus /nolog 

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 28 20:19:50 2014 

Copyright (c) 1982, 2009, Oracle. All rights reserved. 

SQL> conn /as sysdba 
Connected. 
SQL> shutdown immediate 
ORA-01507: database not mounted 

ORACLE instance shut down. 
SQL> create pfile from spfile; 

File created. 

[ oracle@localhost dbs]$ vi initorcl.ora 
orcl.__db_cache_size=201326592 
orcl.__java_pool_size=4194304 
orcl.__large_pool_size=4194304 
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment 
orcl.__pga_aggregate_target=297795584 
orcl.__sga_target=553648128 
orcl.__shared_io_pool_size=0 
orcl.__shared_pool_size=331350016 
orcl.__streams_pool_size=4194304 
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump' 
*.audit_trail='db' 
*.compatible='11.2.0.0.0' 
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' 
*.db_block_size=8192 
*.db_domain='' 
*.db_flashback_retention_target=240 
*.db_name='orcl' 
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' 
*.db_recovery_file_dest_size=4039114752 
*.diagnostic_dest='/u01/app/oracle' 
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' 
*.fast_start_mttr_target=300 
*.job_queue_processes=1000 
*.log_archive_dest_1='location=/u01/app/archivelog/dest1' 
*.log_archive_format='%t_%s_%r.dbf' 
*.memory_target=848297984 
*.open_cursors=300 
*.processes=150 
*.remote_login_passwordfile='exclusive' 
*.resource_limit=TRUE 
*.undo_tablespace='UNDOTBS1'

存盘退出。

将修改应用到spfile中：

SQL> create spfile from pfile;

File created.

启动数据库到nomount：

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 849530880 bytes

Fixed Size 1339824 bytes

Variable Size 641732176 bytes

Database Buffers 201326592 bytes

Redo Buffers 5132288 bytes

SQL> show parameter spfile

NAME              TYPE                                VALUE

------------------------------------ ----------- ------------------------------

spfile               string                         /u01/app/oracle/product/11.2.0

                                                          /dbhome_1/dbs/spfileorcl.ora

恢复控制文件：

RMAN> restore controlfile from '/backup/orcl/controlfile_c-1383986269-20141028-01';

Starting restore at 28-OCT-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=20 device type=DISK

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/orcl/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Finished restore at 28-OCT-14

检查物理上的控制文件是否已经恢复：

[ oracle@localhost orcl]$ cd /u01/app/oracle/oradata/orcl 
[ oracle@localhost orcl]$ ls 
control01.ctl

[ oracle@localhost orcl]$ ls 
control02.ctl 
[ oracle@localhost orcl]$ pwd 
/u01/app/oracle/flash_recovery_area/orcl

启动数据库到mount：

SQL> alter database mount;

Database altered.

恢复数据文件：

由于在源服务器中我的备份集是在/u01/app/backup/mvbackup目录下的，而这些备份信息又是存放在控制文件中。

我们在目标服务器恢复的控制文件是来源于源数据库的，而我们在目标服务器中的备份集存放的目录(/backup/orcl)和源服务器是不同的，因此在目标服务器中使用rman进行数据文件恢复的时候，rman读取的还是原控制文件中记录的位置信息，我们要让rman去读取/backup/orcl中的备份集，所以执行以下步骤通知rman备份集的存放位置已经改变了：

RMAN> catalog start with '/backup/orcl';

searching for all files that match the pattern /backup/orcl

List of Files Unknown to the Database

=====================================

File Name: /backup/orcl/spfile_ORCL_138

File Name: /backup/orcl/spfile_ORCL_139

File Name: /backup/orcl/fulldb_4fpm5pa7_1_1

File Name: /backup/orcl/controlfile_c-1383986269-20141028-01

File Name: /backup/orcl/fulldb_4cpm5oqc_1_1

File Name: /backup/orcl/fulldb_4epm5p6g_1_1

File Name: /backup/orcl/fulldb_4dpm5p1c_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /backup/orcl/spfile_ORCL_138

File Name: /backup/orcl/spfile_ORCL_139

File Name: /backup/orcl/fulldb_4fpm5pa7_1_1

File Name: /backup/orcl/controlfile_c-1383986269-20141028-01

File Name: /backup/orcl/fulldb_4cpm5oqc_1_1

File Name: /backup/orcl/fulldb_4epm5p6g_1_1

File Name: /backup/orcl/fulldb_4dpm5p1c_1_1

接着开始进行数据文件的恢复(由于目标数据库中数据文件存放的位置和目标数据库相同，所以这里无须rename file)：

RMAN> restore database;

Starting restore at 28-OCT-14

using channel ORA_DISK_1

using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orcl/data02.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/test01.dbf

channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orcl/hh01.dbf

channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/orcl/goods01.dbf

channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/orcl/data01a.dbf

channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orcl/data01b.dbf

channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /backup/orcl/fulldb_4epm5p6g_1_1

channel ORA_DISK_1: piece handle=/backup/orcl/fulldb_4epm5p6g_1_1 tag=TAG20141028T053200

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:23

Finished restore at 28-OCT-14

最后recover database:

RMAN> recover database;

Starting recover at 28-OCT-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=68

channel ORA_DISK_1: reading from backup piece /backup/orcl/fulldb_4fpm5pa7_1_1

channel ORA_DISK_1: piece handle=/backup/orcl/fulldb_4fpm5pa7_1_1 tag=FULLDB

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

archived log file name=/u01/app/archivelog/dest1/1_68_861508222.dbf thread=1 sequence=68

unable to find archived log

archived log thread=1 sequence=69

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/28/2014 21:05:08

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 69 and starting SCN of 2941977

这里出现找不到某个序列号的归档日志属于正常现象。因为我们在备份源数据库的时候数据库处于运行的状态，就会有些归档日志内容没有备份到。

RMAN> recover database until sequence 69;

Starting recover at 28-OCT-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 28-OCT-14

接下来就可以使用 alter database open resetlogs;打开数据库了：

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-19751: could not create the change tracking file

ORA-19750: change tracking file: '/u01/app/datafile/change_tracking.dbf'

ORA-27040: file create error, unable to create file

Linux Error: 2: No such file or directory

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

遇到这个问题是由于源数据库中使用了块变化跟踪，这里我们只需要把它disable掉就可以解决问题了。

SQL> alter database disable block change tracking;

Database altered.

接下来就可以打开数据库了。

数据库成功打开之后要 进行检查：

SQL> select name from v$datafile

  2 union

  3 select member from v$logfile

  4 union

  5 select name from v$controlfile

  6 union

  7 select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

/u01/app/oracle/oradata/orcl/control01.ctl

/u01/app/oracle/oradata/orcl/data01a.dbf

/u01/app/oracle/oradata/orcl/data01b.dbf

/u01/app/oracle/oradata/orcl/data02.dbf

/u01/app/oracle/oradata/orcl/example01.dbf

/u01/app/oracle/oradata/orcl/goods01.dbf

/u01/app/oracle/oradata/orcl/hh01.dbf

/u01/app/oracle/oradata/orcl/redo01.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo03.log

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo04.log

/u01/app/oracle/oradata/orcl/sysaux01.dbf

/u01/app/oracle/oradata/orcl/system01.dbf

/u01/app/oracle/oradata/orcl/temp02.dbf

/u01/app/oracle/oradata/orcl/test01.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/users01.dbf

18 rows selected.

最后 配置监听文件listener.ora：

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

# )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME = orcl)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

  )

ADR_BASE_LISTENER = /u01/app/oracle

~

~

启动监听：

[ oracle@localhost ~]$ lsnrctl start 

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-OCT-2014 21:22:39 

Copyright (c) 1991, 2009, Oracle. All rights reserved. 

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... 

TNSLSNR for Linux: Version 11.2.0.1.0 - Production 
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 
Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml 
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))) 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521))) 
STATUS of the LISTENER 
------------------------ 
Alias LISTENER 
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production 
Start Date 28-OCT-2014 21:22:39 
Uptime 0 days 0 hr. 0 min. 2 sec 
Trace Level off 
Security ON: Local OS Authentication 
SNMP OFF 
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml 
Listening Endpoints Summary... 
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))) 
Services Summary... 
Service "orcl" has 1 instance(s). 
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... 
The command completed successfully

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29800581/viewspace-1312980/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29800581/viewspace-1312980/