SDE for Oracle数据库的冷备份迁移之windows
来源:互联网 发布:电脑登录windows密码 编辑:程序博客网 时间:2024/04/29 15:33
今天测试了一把在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. 拷贝原库的数据文件,日志文件,控制文件分别到目标库的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
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'
至此全部搞定。
- SDE for Oracle数据库的冷备份迁移之windows
- 冷备份迁移oracle数据库
- SDE For PostgreSQL物理迁移(冷备份,恢复)
- windows下迁移oracle冷备份
- SDE数据库的备份、迁移或恢复
- sde for Oracle的物理迁移
- Oracle的冷备份 windows
- Oracle数据库迁移--冷迁移
- oracle数据库冷备迁移的步骤
- oracle数据库冷备份
- Oracle数据库冷备份
- Oracle数据库冷备份
- SDE For SQLServer迁移步骤(备份,恢复)
- Oracle数据库冷备份恢复的具体步骤
- Oracle数据库冷备份恢复的具体步骤
- Oracle数据库冷备份恢复的步骤
- Oracle数据库的冷备份与恢复
- oracle9i的冷备份想出的数据库迁移方案
- 分布式文件系统FastDFS架构剖析
- 深入理解C++重载、多态、虚函数
- FileDialog简单使用demo
- tags相关命令
- 关于下拉选择框移位
- SDE for Oracle数据库的冷备份迁移之windows
- VS2005编译ACE
- 新的一年
- 指定浏览器访问指定页面(支持UC、Opera、QQ、Dolphin、Skyfire、Steel、Google)
- .NET读Excel的数字文本混合列
- Ext图片上传之预览
- ace-5.5下载编译
- 海量数据面试题整理
- C#结构体与类的区别