利用RMAN Convert database特性进行跨平台迁移数据

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:1495

一:
利用RMAN Convert database特性进行跨平台迁移数据
在Oracle10g中,不仅可以利用跨平台传输表空间的新特性在异构平台数据库之间迁移数据,在特定条件下,还可以利用Convert database特性进行整个数据库跨平台的迁移。
如果要在10G中进行全库跨平台的迁移,那么只能在低位和低位或者高位和高位平台之间进行迁移,无法进行高位与低位平台的迁移。大家可以参考V$TRANSPORTABLE_PLATFORM中的信息以决定是否能够迁移。
SQL> select * from V$TRANSPORTABLE_PLATFORM order by 3;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
4 HP-UX IA (64-bit) Big
1 Solaris[tm] OE (32-bit) Big
16 Apple Mac OS Big
3 HP-UX (64-bit) Big
9 IBM zSeries Based Linux Big
6 AIX-Based Systems (64-bit) Big
2 Solaris[tm] OE (64-bit) Big
18 IBM Power Based Linux Big
17 Solaris Operating System (x86) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
8 Microsoft Windows IA (64-bit) Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
10 Linux IA (32-bit) Little
7 Microsoft Windows IA (32-bit) Little
11 Linux IA (64-bit) Little、
Convert database具体如下特性:
1、 只能传输数据表空间的数据文件。临时表空间将不会被传输,如果临时表空间是本地管理的话,那么传输数据库完成后将会自动在目标数据库创建临时表空间。
2、 假如源主数据库使用PFILE,那么PFILE也会被传输。如果源数据库使用的是SPFILE,那么将会自动产生一个PFILE进行传输,并在目标数据库生成SPFILE。
3、 控制文件和联机日志文件将不会被传输。在目标数据库open resetlogs的过程中,将会自动创建控制文件和联机日志文件。
4、 BFILE、外部表和directory将不会被传输。RMAN检查的时候将会把这些对象列出,用户可以手工创建这些对象。
5、 密码文件将不会被传输,用户必须在目标数据库手工创建密码文件。
传输数据库的第一步是将源数据库置于READ ONLY模式:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1262044 bytes
Variable Size 385879588 bytes
Database Buffers 1207959552 bytes
Redo Buffers 15511552 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
第二步利用dbms_tdb包检查数据库能否被传输以及列出外部表和DIRECTORY等无法传输的对象信息(测试案例采用Linux迁移至Windows平台):
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any
5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',
7 dbms_tdb.skip_none);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> declare
2 external boolean;
3 begin
4 /* value of external is ignored, but with SERVEROUTPUT set to ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := dbms_tdb.check_external;
8 end;
9
10 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR,
SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA
PL/SQL procedure successfully completed.
环境检查完毕后就可以利用RMAN的convert database命令在源数据库进行转换(convert database也可以在目标数据库进行,具体可以参考文档):
SQL> host rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 15:23:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBTEST (DBID=1018019231)
RMAN> CONVERT DATABASE NEW DATABASE 'newdb'
2> transport script '/home/oracle/liuyun/transportdb.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/opt/oracle/oradata/dbtest/' '/home/oracle/liuyun/';
Starting convert at 28-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
External table SH.SALES_TRANSACTIONS_EXT found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
BFILE PM.PRINT_MEDIA found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=/opt/oracle/oradata/dbtest/system01.dbf
converted datafile=/home/oracle/liuyun/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=/opt/oracle/oradata/dbtest/sysaux01.dbf
converted datafile=/home/oracle/liuyun/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/opt/oracle/oradata/dbtest/example01.dbf
converted datafile=/home/oracle/liuyun/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=/opt/oracle/oradata/dbtest/undotbs01.dbf
converted datafile=/home/oracle/liuyun/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/opt/oracle/oradata/dbtest/users01.dbf
converted datafile=/home/oracle/liuyun/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /home/oracle/liuyun/transportdb.sql on the target platform to create database
Edit init.ora file /opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 28-JUN-07
在这一步中new database表示目标数据库的名字,transport script指定生成建库脚本的位置,to platform表示目标数据库的平台,db_file_name_convert指定源数据库数据文件所在的位置以及转换后数据文件存放的目录。生成的PFILE为/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora,可以根据目标数据库的情况编辑,编辑后的内容如下:
newdb.__db_cache_size=12582912
newdb.__java_pool_size=4194304
newdb.__large_pool_size=4194304
newdb.__shared_pool_size=58720256
newdb.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\newdb\adump'
*.background_dump_dest='D:\oracle\admin\newdb\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\oradata\newdb\control01.ctl'
*.core_dump_dest='D:\oracle\admin\newdb\cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='newdb'
*.db_recovery_file_dest_size=1
*.db_recovery_file_dest=''
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=d:\oracle\oradata\newdb\archive'
*.log_archive_dest_2='service=standby'
*.log_archive_dest_state_2='DEFER'
*.nls_language='AMERICAN'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.query_rewrite_integrity='STALE_TOLERATED'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=80M
*.star_transformation_enabled='FALSE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\newdb\udump'
生成的transportdb.sql内容如下,可以根据目标数据库的情况更改:
STARTUP NOMOUNT PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-47_T-1_A-625156386_00ildsr9' SIZE 50M,
GROUP 2 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-45_T-1_A-625156386_00ildsr9' SIZE 50M,
GROUP 3 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-46_T-1_A-625156386_00ildsr9' SIZE 50M
DATAFILE
'/home/oracle/liuyunsystem01.dbf',
'/home/oracle/liuyunundotbs01.dbf',
'/home/oracle/liuyunsysaux01.dbf',
'/home/oracle/liuyunusers01.dbf',
'/home/oracle/liuyunexample01.dbf'
CHARACTER SET ZHS16GBK;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/product/10.2/dbs/data_D-NEWDB_I-1018019231_TS-TEMP_FNO-1_00ildsr9'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
更改后的脚本内容如下:
STARTUP NOMOUNT PFILE='d:\init.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'd:\oracle\oradata\newdb\redo01.log' SIZE 5M,
GROUP 2 'd:\oracle\oradata\newdb\redo02.log' SIZE 5M
DATAFILE
'd:\oracle\oradata\newdb\system01.dbf',
'd:\oracle\oradata\newdb\undotbs01.dbf',
'd:\oracle\oradata\newdb\sysaux01.dbf',
'd:\oracle\oradata\newdb\users01.dbf',
'd:\oracle\oradata\newdb\example01.dbf'
CHARACTER SET ZHS16GBK;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'd:\oracle\oradata\temp01.dbf'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='d:\init.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='d:\init.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
在目标数据库新创建一个ORACLE服务,将转换后的数据文件COPY至目标数据库的相应目录,运行建库脚本即完成跨平台数据库的转换:
C:\Documents and Settings\Roby.Xuexb>oradim -new -sid newdb -startmode m
Instance created.
C:\Documents and Settings\Roby.Xuexb>set oracle_sid=newdb

三:_____________________________________________________________
RMAN 跨平台迁移--之 covert database
2011-04-05 21:55
转载自 xu521huan
最终编辑 jiamiao442
最近研究goldengate 折腾了2天 以为都弄好了呢 最后检查 发现数据实际没用同步
既然这样 那咋就再弄一次 先把整个库同步一下 原库是linux的 我准备恢复到win xp上,当然可以使用gg的 load data功能,该功能还没试验过 由于之前一直没用过rman的covert功能,这次正好试试 过程如下:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1267236 bytes
Variable Size 260049372 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> declare
db_ready boolean;
begin
/* db_ready is ignored, but with SERVEROUTPUT set to ON any
* conditions preventing transport will be output to console */
2 3 4 5 6 db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',
7 dbms_tdb.skip_none);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> declare
2 external boolean;
3 begin
4 /* value of external is ignored, but with SERVEROUTPUT set to ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := dbms_tdb.check_external;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
----检查是否有外部表或无法传输的对象
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost bdump]$ cd $ORACLE_HOME/bin
[oracle@localhost bin]$ ./rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Nov 17 13:34:49 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2024668720)
RMAN> CONVERT DATABASE NEW DATABASE 'roger'
2> transport script '/oracle/transport_win32.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/oracle/product/oradata/test' '/backup/roger'; ---进行转换并生成相关的脚本
Starting convert at 17-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/oracle/product/oradata/test/test01.dbf
converted datafile=/backup/roger/test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/oracle/product/oradata/test/sysaux01.dbf
converted datafile=/backup/roger/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=/oracle/product/oradata/test/test03.dbf
converted datafile=/backup/roger/test03.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/oracle/product/oradata/test/undo02.dbf
converted datafile=/backup/roger/undo02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=/oracle/product/oradata/test/system01.dbf
converted datafile=/backup/roger/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/oracle/product/oradata/test/perfstat.dbf
converted datafile=/backup/roger/perfstat.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=/oracle/product/oradata/test/rman.dbf
converted datafile=/backup/roger/rman.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=/oracle/product/oradata/test/undo01.dbf
converted datafile=/backup/roger/undo01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=/oracle/product/oradata/test/test02.dbf
converted datafile=/backup/roger/test02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /oracle/transport_win32.sql on the target platform to create database
Edit init.ora file /oracle/product/10.2/db/dbs/init_00lt7v47_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 17-NOV-10
RMAN>
---------------这里我们可以看到,红色的部分是生成的pfile 我们可以根据实际情况进行修改
下面来看看transport_win32.sql的内容:
[oracle@localhost oracle]$ cat transport_win32.sql
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT PFILE='/oracle/product/10.2/db/dbs/init_00lt7v47_1_0.ora'
CREATE CONTROLFILE REUSE SET DATABASE "ROGER" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 4
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/oracle/product/10.2/db/dbs/arch_D-ROGER_id-2024668720_S-313_T-1_A-726529113_00lt7v47' SIZE 10M,
GROUP 2 '/oracle/product/10.2/db/dbs/arch_D-ROGER_id-2024668720_S-314_T-1_A-726529113_00lt7v47' SIZE 20M
DATAFILE
'/backup/roger/system01.dbf',
'/backup/roger/undo01.dbf',
'/backup/roger/rman.dbf',
'/backup/roger/test01.dbf',
'/backup/roger/perfstat.dbf',
'/backup/roger/undo02.dbf',
'/backup/roger/sysaux01.dbf',
'/backup/roger/test02.dbf',
'/backup/roger/test03.dbf'
CHARACTER SET ZHS16GBK
;
-- Set Database Guard and/or Supplemental Logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/product/10.2/db/dbs/data_D-ROGER_I-2024668720_TS-TEMP_FNO-1_00lt7v47'
SIZE 104857600 AUTOEXTEND OFF;
-- End of tempfile additions.
--
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='/oracle/product/10.2/db/dbs/init_00lt7v47_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/oracle/product/10.2/db/dbs/init_00lt7v47_1_0.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
修改以后的transport脚本如下:
STARTUP NOMOUNT PFILE='G:\oracle\product\10.2.0\oradata\roger\pfile.ora'
CREATE CONTROLFILE REUSE SET DATABASE "ROGER" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 4
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'G:\oracle\product\10.2.0\oradata\roger\redo01' SIZE 10M,
GROUP 2 'G:\oracle\product\10.2.0\oradata\roger\redo02' SIZE 20M
DATAFILE
'G:\oracle\product\10.2.0\oradata\roger\system01.dbf',
'G:\oracle\product\10.2.0\oradata\roger\undo01.dbf',
'G:\oracle\product\10.2.0\oradata\roger\rman.dbf',
'G:\oracle\product\10.2.0\oradata\roger\test01.dbf',
'G:\oracle\product\10.2.0\oradata\roger\perfstat.dbf',
'G:\oracle\product\10.2.0\oradata\roger\undo02.dbf',
'G:\oracle\product\10.2.0\oradata\roger\sysaux01.dbf',
'G:\oracle\product\10.2.0\oradata\roger\test02.dbf',
'G:\oracle\product\10.2.0\oradata\roger\test03.dbf'
CHARACTER SET ZHS16GBK
;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'G:\oracle\product\10.2.0\oradata\roger\temp01.dbf'
SIZE 104857600 AUTOEXTEND OFF;
set echo off
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='G:\oracle\product\10.2.0\oradata\roger\pfile.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='G:\oracle\product\10.2.0\oradata\roger\pfile.ora'
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
修改后的pfile如下:
*.background_dump_dest='G:\oracle\product\10.2.0\admin\roger\bdump'
*.compatible='10.2.0.3.0'
*.control_files='G:\ORACLE\PRODUCT\10.2.0\ORADATA\roger\CONTROL01.CTL','G:\ORACLE\PRODUCT\10.2.0\ORADATA\roger\CONTROL02.CTL','G:\ORACLE\PRODUCT\10.2.0\ORADATA\roger\CONTROL03.CTL'#Restore Controlfile
*.core_dump_dest='G:\oracle\product\10.2.0\admin\roger\cdump'
*.cpu_count=1
*.db_block_size=8192
*.db_cache_size=62914560
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_flashback_retention_target=600
*.db_name='roger'
*.db_recovery_file_dest_size=3221225472
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rogerXDB)'
*.hash_area_size=150000
*.java_pool_size=8388608
*.job_queue_processes=10
*.large_pool_size=12582912
*.lock_sga=TRUE
*.log_archive_dest_1='Location=G:\backup'
*.log_buffer=4194304
*.nls_date_format='yyyy/mm/hh'
*.open_cursors=300
*.parallel_max_servers=4
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=52428800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=209715200
*.sga_target=209715200
*.shared_pool_reserved_size=5242880
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='G:\oracle\product\10.2.0\admin\roger\udump'

用oradim新建一个实例 然后登陆sqlplus
oradim -new -sid roger -startmode m
C:\Documents and Settings\Administrator>set ORACLE_SID=roger
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on 星期三 11月 17 15:54:17 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
已连接到空闲例程。
SQL> @G:\oracle\product\10.2.0\oradata\roger\transport.sql;
ORACLE 例程已经启动。
Total System Global Area 209715200 bytes
Fixed Size 1295896 bytes
Variable Size 79694312 bytes
Database Buffers 121634816 bytes
Redo Buffers 7090176 bytes
CREATE CONTROLFILE REUSE SET DATABASE "ROGER" RESETLOGS FORCE LOGGING ARCHIVELOG
第 1 行出现错误:
ORA-01503: CREATE CONTROLFILE ??
ORA-01130: ??????? 10.2.0.4.0 ? ORACLE ?? 10.2.0.3.0 ???
ORA-01110: ???? 1: 'G:\oracle\product\10.2.0\oradata\roger\system01.dbf'
。。。。。。。。省略部分内容
从 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
-----------上面报错的原因很简单 修改*.compatible='10.2.0.3.0' 为10.2.0.4.0 即可。
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on 星期三 11月 17 16:16:28 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate;
ORA-01507: 未装载数据库
ORACLE 例程已经关闭。
SQL> @G:\oracle\product\10.2.0\oradata\roger\transport.sql;
ORACLE 例程已经启动。
Total System Global Area 209715200 bytes
Fixed Size 1295896 bytes
Variable Size 79694312 bytes
Database Buffers 121634816 bytes
Redo Buffers 7090176 bytes
控制文件已创建。
数据库已更改。
数据库已更改。
表空间已更改。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
* or the global database name for this database. Use the
* NEWDBID Utility (nid).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
ORACLE 例程已经启动。
Total System Global Area 209715200 bytes
Fixed Size 1295896 bytes
Variable Size 79694312 bytes
Database Buffers 121634816 bytes
Redo Buffers 7090176 bytes
数据库装载完毕。
数据库已经打开。
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if there the database was not opened in UPGRADE mode
DOC>
DOC> If you encounter this error, execute "SHUTDOWN", "STARTUP UPGRADE" and
DOC> re-execute utlirp.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2 WHERE status != 'OPEN MIGRATE';
未选定行
SQL>
SQL> Rem
SQL> Rem Store object numbers of all valid PL/SQL-based functional indexes
SQL> Rem
SQL> DROP TABLE utlirp_enabled_func_indexes;
表已删除。。。。。。。。。。省略部分内容
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
SQL>
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;
PL/SQL 过程已成功完成。
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> set feedback 6;
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE

二________________________________________________________________________
利用RMAN Convert database特性进行跨平台迁移数据2007-07-04 10:56在Oracle10g中,不仅可以利用跨平台传输表空间的新特性在异构平台数据库之间迁移数据,在特定条件下,还可以利用Convert database特性进行整个数据库跨平台的迁移。
如果要在10G中进行全库跨平台的迁移,那么只能在低位和低位或者高位和高位平台之间进行迁移,无法进行高位与低位平台的迁移。大家可以参考V$TRANSPORTABLE_PLATFORM中的信息以决定是否能够迁移。
SQL> select * from V$TRANSPORTABLE_PLATFORM order by 3;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
4 HP-UX IA (64-bit) Big
1 Solaris[tm] OE (32-bit) Big
16 Apple Mac OS Big
3 HP-UX (64-bit) Big
9 IBM zSeries Based Linux Big
6 AIX-Based Systems (64-bit) Big
2 Solaris[tm] OE (64-bit) Big
18 IBM Power Based Linux Big
17 Solaris Operating System (x86) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
8 Microsoft Windows IA (64-bit) Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
10 Linux IA (32-bit) Little
7 Microsoft Windows IA (32-bit) Little
11 Linux IA (64-bit) Little、

Convert database具体如下特性:
1、 只能传输数据表空间的数据文件。临时表空间将不会被传输,如果临时表空间是本地管理的话,那么传输数据库完成后将会自动在目标数据库创建临时表空间。
2、 假如源主数据库使用PFILE,那么PFILE也会被传输。如果源数据库使用的是SPFILE,那么将会自动产生一个PFILE进行传输,并在目标数据库生成SPFILE。
3、 控制文件和联机日志文件将不会被传输。在目标数据库open resetlogs的过程中,将会自动创建控制文件和联机日志文件。
4、 BFILE、外部表和directory将不会被传输。RMAN检查的时候将会把这些对象列出,用户可以手工创建这些对象。
5、 密码文件将不会被传输,用户必须在目标数据库手工创建密码文件。

传输数据库的第一步是将源数据库置于READ ONLY模式:

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

Total System Global Area 1610612736 bytes
Fixed Size 1262044 bytes
Variable Size 385879588 bytes
Database Buffers 1207959552 bytes
Redo Buffers 15511552 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

第二步利用dbms_tdb包检查数据库能否被传输以及列出外部表和DIRECTORY等无法传输的对象信息(测试案例采用Linux迁移至Windows平台):

SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any
5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',
7 dbms_tdb.skip_none);
8 end;
9 /

PL/SQL procedure successfully completed.

SQL> declare
2 external boolean;
3 begin
4 /* value of external is ignored, but with SERVEROUTPUT set to ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := dbms_tdb.check_external;
8 end;
9
10 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR,
SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

环境检查完毕后就可以利用RMAN的convert database命令在源数据库进行转换(convert database也可以在目标数据库进行,具体可以参考文档):

SQL> host rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 15:23:16 2007

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

connected to target database: DBTEST (DBID=1018019231)

RMAN> CONVERT DATABASE NEW DATABASE 'newdb'
2> transport script '/home/oracle/liuyun/transportdb.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/opt/oracle/oradata/dbtest/' '/home/oracle/liuyun/';

Starting convert at 28-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=/opt/oracle/oradata/dbtest/system01.dbf
converted datafile=/home/oracle/liuyun/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=/opt/oracle/oradata/dbtest/sysaux01.dbf
converted datafile=/home/oracle/liuyun/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/opt/oracle/oradata/dbtest/example01.dbf
converted datafile=/home/oracle/liuyun/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=/opt/oracle/oradata/dbtest/undotbs01.dbf
converted datafile=/home/oracle/liuyun/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/opt/oracle/oradata/dbtest/users01.dbf
converted datafile=/home/oracle/liuyun/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /home/oracle/liuyun/transportdb.sql on the target platform to create database
Edit init.ora file /opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 28-JUN-07

在这一步中new database表示目标数据库的名字,transport script指定生成建库脚本的位置,to platform表示目标数据库的平台,db_file_name_convert指定源数据库数据文件所在的位置以及转换后数据文件存放的目录。生成的PFILE为/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora,可以根据目标数据库的情况编辑,编辑后的内容如下:

newdb.__db_cache_size=12582912
newdb.__java_pool_size=4194304
newdb.__large_pool_size=4194304
newdb.__shared_pool_size=58720256
newdb.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\newdb\adump'
*.background_dump_dest='D:\oracle\admin\newdb\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\oradata\newdb\control01.ctl'
*.core_dump_dest='D:\oracle\admin\newdb\cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='newdb'
*.db_recovery_file_dest_size=1
*.db_recovery_file_dest=''
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=d:\oracle\oradata\newdb\archive'
*.log_archive_dest_2='service=standby'
*.log_archive_dest_state_2='DEFER'
*.nls_language='AMERICAN'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.query_rewrite_integrity='STALE_TOLERATED'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=80M
*.star_transformation_enabled='FALSE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\newdb\udump'

生成的transportdb.sql内容如下,可以根据目标数据库的情况更改:

STARTUP NOMOUNT PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-47_T-1_A-625156386_00ildsr9' SIZE 50M,
GROUP 2 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-45_T-1_A-625156386_00ildsr9' SIZE 50M,
GROUP 3 '/opt/oracle/product/10.2/dbs/arch_D-NEWDB_id-1018019231_S-46_T-1_A-625156386_00ildsr9' SIZE 50M
DATAFILE
'/home/oracle/liuyunsystem01.dbf',
'/home/oracle/liuyunundotbs01.dbf',
'/home/oracle/liuyunsysaux01.dbf',
'/home/oracle/liuyunusers01.dbf',
'/home/oracle/liuyunexample01.dbf'
CHARACTER SET ZHS16GBK;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '/opt/oracle/product/10.2/dbs/data_D-NEWDB_I-1018019231_TS-TEMP_FNO-1_00ildsr9'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

更改后的脚本内容如下:

STARTUP NOMOUNT PFILE='d:\init.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'd:\oracle\oradata\newdb\redo01.log' SIZE 5M,
GROUP 2 'd:\oracle\oradata\newdb\redo02.log' SIZE 5M
DATAFILE
'd:\oracle\oradata\newdb\system01.dbf',
'd:\oracle\oradata\newdb\undotbs01.dbf',
'd:\oracle\oradata\newdb\sysaux01.dbf',
'd:\oracle\oradata\newdb\users01.dbf',
'd:\oracle\oradata\newdb\example01.dbf'
CHARACTER SET ZHS16GBK;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE 'd:\oracle\oradata\temp01.dbf'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='d:\init.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='d:\init.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

在目标数据库新创建一个ORACLE服务,将转换后的数据文件COPY至目标数据库的相应目录,运行建库脚本即完成跨平台数据库的转换:

C:\Documents and Settings\Roby.Xuexb>oradim -new -sid newdb -startmode m

Instance created.

C:\Documents and Settings\Roby.Xuexb>set oracle_sid=newdb

四:
RMAN Convert database
2011-01-07 15:09
在Oracle10g中,不仅可以利用跨平台传输表空间的新特性在异构平台数据库之间迁移数据,在特定条件下,还可以利用Conver database特性进行整个数据库跨平台的迁移。
如果要在10G中进行全库跨平台的迁移,那么只能在低位和低位或者高位和高位平台之间进行迁移,无法进行高位与低位平台的迁移。大家可以参考V$TRANSPORTABLE_PLATFORM中的信息以决定是否能够迁移。PHP code:

SQL> select * from V$TRANSPORTABLE_PLATFORM order by 3;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
4 HP-UX IA (64-bit) Big
1 Solaris[tm] OE (32-bit) Big
16 Apple Mac OS Big
3 HP-UX (64-bit) Big
9 IBM zSeries Based Linux Big
6 AIX-Based Systems (64-bit) Big
2 Solaris[tm] OE (64-bit) Big
18 IBM Power Based Linux Big
17 Solaris Operating System (x86) Little
12 Microsoft Windows 64-bit for AMD Little
13 Linux 64-bit for AMD Little
8 Microsoft Windows IA (64-bit) Little
15 HP Open VMS Little
5 HP Tru64 UNIX Little
10 Linux IA (32-bit) Little
7 Microsoft Windows IA (32-bit) Little
11 Linux IA (64-bit) Little
..
Convert database具体如下特性:
1、 只能传输数据表空间的数据文件。临时表空间将不会被传输,如果临时表空间是本地管理的话,那么传输数据库完成后将会自动在目标数据库创建临时表空间。
2、 假如源主数据库使用PFILE,那么PFILE也会被传输。如果源数据库使用的是SPFILE,那么将会自动产生一个PFILE进行传输,并在目标数据库生成SPFILE。
3、 控制文件和联机日志文件将不会被传输。在目标数据库open resetlogs的过程中,将会自动创建控制文件和联机日志文件。
4、 BFILE、外部表和directory将不会被传输。RMAN检查的时候将会把这些对象列出,用户可以手工创建这些对象。
传输数据库的第一步是将源数据库置于READ ONLY模式:
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 1262044 bytes
Variable Size 385879588 bytes
Database Buffers 1207959552 bytes
Redo Buffers 15511552 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
第二步利用dbms_tdb包检查数据库能否被传输以及列出外部表和DIRECTORY等无法传输的对象信息(测试案例采用Linux迁移至Windows平台):PHP code:
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any
5 * conditions preventing transport will be output to console */
6 db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',
7 dbms_tdb.skip_none);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> declare
2 external boolean;
3 begin
4 /* value of external is ignored, but with SERVEROUTPUT set to ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := dbms_tdb.check_external;
8 end;
9
10 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR,
SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA
PL/SQL procedure successfully completed.
环境检查完毕后就可以利用RMAN的convert database命令在源数据库进行转换(convert database也可以在目标数据库进行,具体可以参考文档):PHP code:

SQL> host rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 15:23:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBTEST (DBID=1018019231)
RMAN> CONVERT DATABASE NEW DATABASE 'newdb'
2> transport script '/home/oracle/liuyun/transportdb.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/opt/oracle/oradata/dbtest/' '/home/oracle/liuyun/';
Starting convert at 28-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISKExternal table SH.SALES_TRANSACTIONS_EXT found in the databaseDirectory SYS.DATA_PUMP_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.WORK_DIR found in the database
Directory SYS.ADMIN_DIR found in the databaseBFILE PM.PRINT_MEDIA found in the databaseUser SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=/opt/oracle/oradata/dbtest/system01.dbf
converted datafile=/home/oracle/liuyun/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=/opt/oracle/oradata/dbtest/sysaux01.dbf
converted datafile=/home/oracle/liuyun/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=/opt/oracle/oradata/dbtest/example01.dbf
converted datafile=/home/oracle/liuyun/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=/opt/oracle/oradata/dbtest/undotbs01.dbf
converted datafile=/home/oracle/liuyun/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=/opt/oracle/oradata/dbtest/users01.dbf
converted datafile=/home/oracle/liuyun/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /home/oracle/liuyun/transportdb.sql on the target platform to create database
Edit init.ora file /opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 28-JUN-07
..
在这一步中new database表示目标数据库的名字,transport script指定生成建库脚本的位置,to platform表示目标数据库的平台,db_file_name_convert指定源数据库数据文件所在的位置以及转换后数据文件存放的目录。生成的 PFILE为/opt/oracle/product/10.2/dbs/init_00ildsr9_1_0.ora,可以根据目标数据库的情况编辑,编辑后的内容如下:
newdb.__db_cache_size=12582912
newdb.__java_pool_size=4194304
newdb.__large_pool_size=4194304
newdb.__shared_pool_size=58720256
newdb.__streams_pool_size=0
*.audit_file_dest='D:\oracle\admin\newdb\adump'
*.background_dump_dest='D:\oracle\admin\newdb\bdump'
*.compatible='10.2.0.1.0'
*.control_files='D:\oracle\oradata\newdb\control01.ctl'
*.core_dump_dest='D:\oracle\admin\newdb\cdump'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='newdb'
*.db_recovery_file_dest_size=1
*.db_recovery_file_dest=''
*.dispatchers='(PROTOCOL=TCP) (SERVICE=newdbXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=d:\oracle\oradata\newdb\archive'
*.log_archive_dest_2='service=standby'
*.log_archive_dest_state_2='DEFER'
*.nls_language='AMERICAN'
*.nls_territory='UNITED KINGDOM'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.query_rewrite_integrity='STALE_TOLERATED'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=80M
*.star_transformation_enabled='FALSE'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\newdb\udump'

更改后的脚本内容如下:
STARTUP NOMOUNT PFILE='d:\init.ora'
CREATE CONTROLFILE REUSE SET DATABASE "NEWDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'd:\oracle\oradata\newdb\redo01.log' SIZE 5M,
GROUP 2 'd:\oracle\oradata\newdb\redo02.log' SIZE 5M
DATAFILE
'd:\oracle\oradata\newdb\system01.dbf',
'd:\oracle\oradata\newdb\undotbs01.dbf',
'd:\oracle\oradata\newdb\sysaux01.dbf',
'd:\oracle\oradata\newdb\users01.dbf',
'd:\oracle\oradata\newdb\example01.dbf'
CHARACTER SET ZHS16GBK;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'd:\oracle\oradata\temp01.dbf'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='d:\init.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='d:\init.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

在目标数据库新创建一个ORACLE服务,将转换后的数据文件COPY至目标数据库的相应目录,运行建库脚本即完成跨平台数据库的转换:
C:\Documents and Settings\Roby.Xuexb>oradim -new -sid newdb -startmode m
Instance created.
C:\Documents and Settings\Roby.Xuexb>set oracle_sid=newdb
C:\Documents and Settings\Roby.Xuexb>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on 星期四 6月 28 15:44:48 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> @D:\oracle\oradata\NEWDB\transportdb.sql
ORACLE instance started.
Total System Global Area 83886080 bytes
Fixed Size 1289028 bytes
Variable Size 71304380 bytes
Database Buffers 8388608 bytes
Redo Buffers 2904064 bytes
Control file created.
Elapsed: 00:00:02.20
Database altered.
Elapsed: 00:00:24.84
Tablespace altered.
Elapsed: 00:00:05.93
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* Your database has been created successfully!
* There are many things to think about for the new database. Here
* is a checklist to help you stay on track:
* 1. You may want to redefine the location of the directory objects.
* 2. You may want to change the internal database identifier (DBID)
* or the global database name for this database. Use the
* NEWDBID Utility (nid).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 83886080 bytes
Fixed Size 1289028 bytes
Variable Size 71304380 bytes
Database Buffers 8388608 bytes
Redo Buffers 2904064 bytes
__________________

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】