传输数据库

最新推荐文章于 2022-10-19 13:14:31 发布

m18994118189

最新推荐文章于 2022-10-19 13:14:31 发布

阅读量595

点赞数

分类专栏： oracle学习笔记文章标签：传输数据库

本文链接：https://blog.csdn.net/m18994118189/article/details/81866897

版权

oracle学习笔记专栏收录该内容

100 篇文章 0 订阅

订阅专栏

1.在源和目标平台确认平台的信息
传输表空间及传输数据库所支持的所有平台
SQL> select platform_name,endian_format from v$transportable_platform order by 2,1;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
AIX-Based Systems (64-bit) Big
Apple Mac OS Big
HP-UX (64-bit) Big
HP-UX IA (64-bit) Big
IBM Power Based Linux Big
IBM zSeries Based Linux Big
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Apple Mac OS (x86-64) Little
HP IA Open VMS Little
HP Open VMS Little
HP Tru64 UNIX Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Linux x86 64-bit Little
Microsoft Windows IA (32-bit) Little
Microsoft Windows IA (64-bit) Little
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
Solaris Operating System (x86-64) Little

源
col platform_name for a50
col endian_format for a20
SELECT tp.platform_name,tp.endian_format
FROM v$transportable_platform tp, v$database d
WHERE tp.platform_name = d.platform_name;

PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------- --------------------
Linux IA (32-bit) Little

目标
PLATFORM_NAME ENDIAN_FORMAT
-------------------------------------------------- --------------------
Microsoft Windows IA (32-bit) Little

2.调用dbms_tdb.check_db帮助我们检查数据库是否可以被传输
在linux数据库上

SQL> set serveroutput on

SQL> declare
db boolean;
begin
db := dbms_tdb.check_db('Linux IA (32-bit)');
if db then
dbms_output.put_line('THIS DATABASE CAN BE TRANSPORT');
else
dbms_output.put_line('THIS DATABASE CAN NOT BE TRANSPORT');
end if;
end;
/

Database is not open in READ-ONLY mode. Open the database in READ-ONLY mode and
retry.
THIS DATABASE CAN NOT BE TRANSPORT

PL/SQL procedure successfully completed.
发现问题，数据库必须在read only模式才可以被传输

3.将数据库以read only方式打开，再次调用dbms_tdb检查
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 468701184 bytes
Fixed Size 1337268 bytes
Variable Size 264243276 bytes
Database Buffers 197132288 bytes
Redo Buffers 5988352 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> set serveroutput on
SQL> declare
db boolean;
begin
db := dbms_tdb.check_db('Linux IA (32-bit)');
if db then
dbms_output.put_line('THIS DATABASE CAN BE TRANSPORT');
else
dbms_output.put_line('THIS DATABASE CAN NOT BE TRANSPORT');
end if;
end;

THIS DATABASE CAN BE TRANSPORT

PL/SQL procedure successfully completed.

4.使用dbms_tdb.check_external检测该数据库所有的外部元素（外部表，目录对象这些在操作系统上存在的文件及目录）
SQL> declare
db_external boolean;
begin
db_external := dbms_tdb.check_external();
end;
/
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT, SYS.WRR$_REPLAY_UC_GRAPH_EXT
The following directories exist in the database:
SYS.TSPITR_DIROBJ_DPDIR, SYS.DUMP_DIR, SYS.SUBDIR, SYS.SS_OE_XMLDIR,
SYS.MEDIA_DIR, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.XMLDIR,
SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

发现有三类存储在数据库外部的对象：外部表、目录对象、BFILE，这些要手动的复制和创建

5.检查RMAN配置的默认通道
若使用RMAN的转换平台的功能，实现跨平台传输数据库，在RMAN进行转化的时候，如果RMAN配置了并行通道，那么转化会出错
所以，使用RMAN转换平台功能时，一定不要配置并行通道
[oracle@oel ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jun 8 11:11:27 2016

connected to target database: ORCL (DBID=1440654326)

RMAN> show device type;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
我们发现，我们当前配置的并行度就是1，不必去修改

6.使用rman的convert功能转换平台
RMAN> convert database new database 'oaec'
2> transport script '/u03/trans/trans.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/u01/app/oracle/oradata/orcl' '/u03/trans';

注意如果是同平台的就不需要to platform '' 这一步了

Starting conversion at source at 08-JUN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=192 device type=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database
External table SYS.WRR$_REPLAY_UC_GRAPH_EXT found in the database

Directory SYS.DUMP_DIR found in the database
Directory SYS.TSPITR_DIROBJ_DPDIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.SS_OE_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.XMLDIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_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 file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
converted datafile=/u03/trans/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
converted datafile=/u03/trans/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
converted datafile=/u03/trans/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
converted datafile=/u03/trans/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
converted datafile=/u03/trans/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/test.dbf
converted datafile=/u03/trans/test.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /u01/app/oracle/product/11.2.0/db_1/dbs/init_00r7keeb_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /u03/trans/trans.sql on the target platform to create database
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 conversion at source at 08-JUN-16

从上面的输出中，可以得到以下信息
（1）rman帮助我们生成目标数据库的参数文件/u01/app/oracle/product/11.2.0/db_1/dbs/init_00r7keeb_1_0.ora
（2）rman帮助我们生成了在目录数据库要执行的脚本文件/u03/trans/trans.sql
（3）所有的数据文件都被rman转换成了目标平台的格式，存储的目录为/u03/trans
将上述三条所涉及所有文件都复制到WINDOWS平台上

7.在windows平台上
创建数据文件所需要的目录D:\app\Administrator\oradata\oaec
把所有的数据文件复制到上述目录

8.准备参数文件
RMAN帮助我们生成的参数文件，是这样的
# Please change the values of the following parameters:
control_files = "/u01/app/oracle/product/11.2.0/db_1/dbs/cf_D-OAEC_id-1440654326_01r7keeb"
db_recovery_file_dest = "/u01/app/oracle/product/11.2.0/db_1/dbs/u02"
db_recovery_file_dest_size= 8388608000
audit_file_dest = "/u01/app/oracle/product/11.2.0/db_1/dbs/adump"
db_name = "OAEC"

# Please review the values of the following parameters:
# __oracle_base = "/u01/app/oracle"
__shared_pool_size = 222298112
__large_pool_size = 4194304
__java_pool_size = 29360128
__streams_pool_size = 8388608
__sga_target = 469762048
__db_cache_size = 197132288
__shared_io_pool_size = 0
_shared_io_pool_size = 0
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
__pga_aggregate_target = 469762048

# The values of the following parameters are from source database:
processes = 150
pre_page_sga = TRUE
lock_sga = TRUE
shared_pool_size = 0
large_pool_size = 0
java_pool_size = 0
streams_pool_size = 0
backup_tape_io_slaves = TRUE
resource_manager_plan = "DEFAULT_MAINTENANCE_PLAN"
sga_target = 469762048
db_block_size = 8192
db_cache_size = 0
compatible = "11.2.0.0.0"
log_archive_format = "%t_%s_%r.dbf"
undo_tablespace = "UNDOTBS1"
cursor_sharing = "EXACT"
audit_trail = "OS"
open_cursors = 300
query_rewrite_enabled = "FALSE"
pga_aggregate_target = 468006400
# diagnostic_dest = "/u01/app/oracle"

要修改其中一些路径的信息，改成WINDOWS平台的路径
# Please change the values of the following parameters:
control_files = "D:\app\Administrator\oradata\oaec\control01.ctl"
db_recovery_file_dest = "D:\app\Administrator\flash_recovery_area"
db_recovery_file_dest_size= 8388608000
audit_file_dest = "D:\app\Administrator\admin\oaec\adump"
db_name = "OAEC"

# Please review the values of the following parameters:
# __oracle_base = "D:\app\Administrator"
__shared_pool_size = 222298112
__large_pool_size = 4194304
__java_pool_size = 29360128
__streams_pool_size = 8388608
__sga_target = 469762048
__db_cache_size = 197132288
__shared_io_pool_size = 0
_shared_io_pool_size = 0
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
__pga_aggregate_target = 469762048

9.在WINDOWS平台上，创建oaec实例的服务，并设置oracle_sid环境变量
以管理员身份运行cmd.exe

C:\Windows\system32>oradim -NEW -SID oaec
实例已创建。

C:\Windows\system32>set oracle_sid=oaec

10.创建 spfile

SQL> startup nomount pfile='E:\download\trans\initoaec.ora'
ORACLE 例程已经启动。

Total System Global Area 468701184 bytes
Fixed Size 1375340 bytes
Variable Size 264242068 bytes
Database Buffers 197132288 bytes
Redo Buffers 5951488 bytes
SQL> create spfile from pfile='E:\download\trans\initoaec.ora';

文件已创建。

SQL> shutdown abort
ORACLE 例程已经关闭。

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 468701184 bytes
Fixed Size 1375340 bytes
Variable Size 264242068 bytes
Database Buffers 197132288 bytes
Redo Buffers 5951488 bytes

11.修改并执行trans.sql

SQL> CREATE CONTROLFILE REUSE SET DATABASE "OAEC" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'D:\app\Administrator\oradata\oaec\redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 'D:\app\Administrator\oradata\oaec\redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 'D:\app\Administrator\oradata\oaec\redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 'D:\app\Administrator\oradata\oaec\system01.dbf',
13 'D:\app\Administrator\oradata\oaec\sysaux01.dbf',
14 'D:\app\Administrator\oradata\oaec\undotbs01.dbf',
15 'D:\app\Administrator\oradata\oaec\users01.dbf',
16 'D:\app\Administrator\oradata\oaec\example01.dbf',
17 'D:\app\Administrator\oradata\oaec\test.dbf'
18 CHARACTER SET AL32UTF8
19 ;

控制文件已创建。

此时数据库已经自动Mount了
SQL> select status from v$instance;

STATUS
------------------------
MOUNTED

继续执行脚本中的内容
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

数据库已更改。

SQL> ALTER DATABASE OPEN RESETLOGS;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\app\Administrator\oradata\oaec\temp.
dbf' SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

表空间已更改。

SQL> SHUTDOWN IMMEDIATE
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP UPGRADE
ORACLE 例程已经启动。

Total System Global Area 468701184 bytes
Fixed Size 1375340 bytes
Variable Size 264242068 bytes
Database Buffers 197132288 bytes
Redo Buffers 5951488 bytes
数据库装载完毕。
数据库已经打开。
SQL> @@ ?/rdbms/admin/utlirp.sql

等待脚本执行完毕
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> utlirp.sql completed successfully. All PL/SQL objects in the
DOC> database have been invalidated.
DOC>
DOC> Shut down and restart the database in normal mode and run utlrp.sql to
DOC> recompile invalid objects.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

继续按脚本进行执行
SQL> SHUTDOWN IMMEDIATE
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> STARTUP
ORACLE 例程已经启动。

等待脚本执行完成

12.创建WINDOWS数据库使用的口令文件（cmd窗口要以管理员身份执行）
C:\Windows\system32>orapwd file=D:\app\Administrator\product\11.2.0\dbhome_1\dat
abase\PWDoaec.ora password=oracle

13.在OAEC数据库中执行一些完整性检查，验证传输成功
C:\Users\Administrator>set oracle_sid=oaec

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 8 11:53:01 2016

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set lines 120
SQL> show parameter name

NAME TYPE VALUE
------------------------------------ ---------------------- --------------------
----------
db_file_name_convert string
db_name string OAEC
db_unique_name string OAEC
global_names boolean FALSE
instance_name string oaec
lock_name_space string
log_file_name_convert string
service_names string OAEC

SQL> conn test/test
已连接。
SQL> select * from tab;

TNAME TABTYPE CLU
STERID
------------------------------------------------------------ -------------- ----
------
TEST_TRANS TABLE

SQL> select * from test_trans;

ID
----------
CONTENT
--------------------------------------------------------------------------------
----------------------------------------
1
Linux oel 2.6.18-164.el5PAE #1 SMP Thu Sep 3 02:28:20 EDT 2009 i686 i686 i386 GN
U/Linux

SQL>

m18994118189

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
传输数据库

1.在源和目标平台确认平台的信息传输表空间及传输数据库所支持的所有平台SQL&gt; select platform_name,endian_format from v$transportable_platform order by 2,1;PLATFORM_NAME ENDIAN_FORMAT---------------------...
复制链接

扫一扫