分享
2015-12-16
今天测试了一把在windows平台上使用冷备份方式来迁移oracle数据库，以下为详细步骤，
测试环境：
src db:
DBMS: 10.2.0.4
OS: windows XP
dst db:
DBMS: 11.2.0.1
OS: windows2008
迁移详细步骤：
1. 通过下面的sql查找原库的控制文件，日志文件，数据文件所在的位置。
SQL
> select
name from
v$datafile
2 union
select
member from
v$logfile
3 union
select
name from
v$controlfile;
NAME
--------------------------------------------------------------------------------
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/CONTROL01.CTL
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/CONTROL02.CTL
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/CONTROL03.CTL
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/REDO01.LOG
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/REDO02.LOG
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/REDO03.LOG
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/SDE.DBF
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/SYSAUX01.DBF
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/SYSTEM01.DBF
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/UNDOTBS01.DBF
D:/ORACLE/PRODUCT/10.2.0/ORADATA/CESHI/USERS01.DBF
NAME
--------------------------------------------------------------------------------
D:/ORACLE/PRODUCT/TEST_INDEX.DBF
12 rows
selected.
2. 超找到该库的参数文件和密码文件所在的位置
这两个文件所在的位置在$ORACLE_HOME/database目录下，参数文件的命名方式为SPFILE.ORA，密码文件的命名方式为orapw.ora
3. shutdown掉原库。
SQL
> shutdown
immediate
;
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL
> 4. 拷贝原库的数据文件，日志文件，控制文件分别到目标库的 e:/liufeng/datafile e:/liufeng/logfile e:/liufeng/controlfile 三个目录中 5. 将原库中的参数文件和密码文件拷贝到目标机器上的$ORACLE_HOME/database中。 6. 在目标机器上新创建一个数据库的实例。
C:/Users/Administrator>oradim -NEW -SID CESHI -SPFILE -STARTMODE auto
实例已创建。
7. 启动新创建的实例的时候，报如下错误，
C:/Users/Administrator>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on
星期二 1月 4 09:37:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
输入口令:
已连接到空闲例程。
SQL
> startup nomount;
ORA-32004: obsolete or deprecated parameter
(s) specified for RDBMS instance
ORA-02778: Name given for the log directory is invalid
SQL>
应该是有些参数文件所指向的目录并不存在，
使用写字板打开参数文件，(参数文件是二进制文件只能看，不能改)，拷贝出文件中的明码到另一个文本文件pfile.txt中，拷贝出来的内容如下：
*.audit_file_dest='D:/oracle/product/10.2.0/admin/CESHI/adump'
*.background_dump_dest='D:/oracle/product/10.2.0/admin/CESHI/bdump'
*.compatible='10.2.0.3.0'
*.control_files='D:/oracle/product/10.2.0/oradata/CESHI/control01.ctl'
,'D:/oracle/product/10.2.0/oradata/CESHI/control02.ctl'
,'D:/oracle/product/10.2.0/oradata/CESHI/control03.ctl'
*.core_dump_dest='D:/oracle/product/10.2.0/admin/CESHI/cdump'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=33554432
*.db_name='CESHI'
*.db_recovery_file_dest='D:/oracle/product/10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CESHIXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=220200960
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:/oracle/product/10.2.0/admin/CESHI/udump'
从参数文件中可以看出来，以下几个参数所指向的路径是不存在或者不正确的，
audit_file_dest
background_dump_dest
control_files
core_dump_dest
db_recovery_file_dest
user_dump_dest
在操作系统上新建一些目录，并且使这些参数执行正确的目录，修改后的文件为：
*.audit_file_dest='c:/oracle/product/10.2.0/admin/CESHI/adump'
*.background_dump_dest='C:/oracle/product/11.2.0/db_1/admin/bdump'
*.compatible='10.2.0.3.0'
*.control_files='e:/liufeng/controlfile/control01.ctl'
,'e:/liufeng/controlfile/control02.ctl'
,'e:/liufeng/controlfile/control03.ctl'
*.core_dump_dest='C:/oracle/product/11.2.0/db_1/admin/cdump'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_keep_cache_size=33554432
*.db_name='CESHI'
*.db_recovery_file_dest='c:/oracle/product/11.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CESHIXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=220200960
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:/oracle/product/11.2.0/db_1/admin/udump'
8. 使用新的文本文件来启动实例，报如下错误：
C:/Users/Administrator>sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on
星期二 1月 4 09:55:24 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
输入口令:
已连接到空闲例程。
SQL> startup mount pfile='C:/oracle/product/11.2.0/db_1/database/pfile.txt';
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORA-04031: unable to allocate 10272 bytes of shared memory ("shared pool","unknown object
","sga heap(1,0)","KGLSG")
从字面上看应该是共享内存池给的太小了，那增大共享内存池的大小到200M：
*.shared_pool_size=209715200
重新启动实例，到mount状态，又报如下错误：
SQL> startup mount pfile='C:/oracle/product/11.2.0/db_1/database/pfile.txt'
;
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORA-00821: Specified value of sga_target 200M is too small, needs to be at least 340M
ORA-01078: 处理系统参数失败
sga_target参数太小了，修改sga_target到1G，再启动实例：
*.sga_target=1073741824
又报如下错误：
ORA-01078: 处理系统参数失败
SQL> startup mount pfile='C:/oracle/product/11.2.0/db_1/database/pfile.txt';
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: 处理系统参数失败
继续修改sga_max_size参数为1.2G后，再启动实例后成功：
*.sga_max_size=1088490188
SQL> startup mount pfile='C:/oracle/product/11.2.0/db_1/database/pfile.txt';
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE 例程已经启动。
Total System Global Area 1085640704 bytes
Fixed Size 2182752 bytes
Variable Size 234881440 bytes
Database Buffers 843055104 bytes
Redo Buffers 5521408 bytes
数据库装载完毕。
9. 修改目标库中，控制文件中所指向的日志文件和数据文件的路径：
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/redo01.log' to'e:/liufeng/logfile/redo01.log';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/redo02.log' to 'e:/liufeng/logfile/redo02.log';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/redo03.log' to 'e:/liufeng/logfile/redo03.log';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/sde.dbf' to 'e:/liufeng/datafile/sde.dbf';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/sysaux01.dbf' to 'e:/liufeng/datafile/sysaux01.dbf';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/system01.dbf' to 'e:/liufeng/datafile/system01.dbf';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/undotbs01.dbf' to 'e:/liufeng/datafile/undotbs01.dbf';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/10.2.0/oradata/ceshi/users01.dbf' to'e:/liufeng/datafile/users01.dbf';
数据库已更改。
SQL> alter database rename file 'd:/oracle/product/test_index.dbf' to 'e:/liufeng/datafile/test_index.dbf';
数据库已更改。
10. 打开数据库
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
进程 ID: 5800
会话 ID: 66 序列号: 3 因为数据库的版本不同，需要升级， 10. 升级数据库
SQL> startup upgrade pfile='C:/oracle/product/11.2.0/db_1/database/pfile.txt';
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE 例程已经启动。
Total System Global
Area 1085640704 bytes
Fixed Size 2174928 bytes
Variable Size 268435504 bytes
Database Buffers 805306368 bytes
Redo Buffers 9723904 bytes
数据库装载完毕。
数据库已经打开。
@ ?/rdbms/admin/catupgrd
进过半个小时的过程后，升级完成。
11. 创建spfile。
create spfile file pfile='c:/oracle/product/11.2.0/db_1/database/pfile.txt'
至此全部搞定。
文章来源：http://blog.csdn.net/liufeng1980423/article/details/6115033
722
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
