ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 直接复制数据文件实现linux平台数据库复制到windows平台数据库

直接复制数据文件实现linux平台数据库复制到windows平台数据库

Linux操作系统 作者:Appleses 时间:2016-01-30 17:00:29 0 删除 编辑

一.1  平台环境概述

 

之前在测试传输表空间时感受了一下跨平台的移值,当时只测试了通过传输表空间的特性复制某个指定表空间,或者是通过RMAN中的CONVERTTTS复制数据库,测试结束之后,感觉ORACLE10G之后对不同平台(相同字节顺序)的数据文件相互兼容性方面得到大大提升,下意识认为不通过传输表空间直接复制数据文件应该也可以,今天在本地测试了一下,确实相当好使,通过这种方式使得跨平台的移植更加高效,也易于管理和操作,下面记录的为操作过程。
注意:源平台与目标平台的字节顺序(endian format)需要相同。


源平台:RHEL6.5  系统(64) + oracle 11.2.0.1.0
目标平台:Windows xp 系统(32bit) + oracle11.2.0.1.0

 

注意: 本章节采用直接复制相关数据文件的形式来实现linuxwindows平台的数据库复制

 

一.2  查看字节序

SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i

WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME

and d.DB_UNIQUE_NAME = i.INSTANCE_NAME;

 

 

(一)------  windows平台下查看,windows下之前安装过一个orcl的库

C:\Users\华荣>sqlplus lhr/lhr@orclxp

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 1129 12:49:15 2014

 

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

 

 

连接到:

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 line 9999 pages 9999

SQL> SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT

  2  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i

  3  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME

  4  and d.DB_UNIQUE_NAME = i.INSTANCE_NAME;

 

NAME      VERSION           PLATFORM_NAME                                                                                 ENDIAN_FORMAT

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

ORCL      11.2.0.1.0        Microsoft Windows IA (32-bit)                                                                 Little

 

SQL>

 

 

(二)----------  linux 平台下查看

 

C:\Users\华荣>sqlplus lhr/lhr@rman

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 1129 12:52:49 2014

 

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

 

 

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> set line 9999 pages 9999

SQL> SELECT d.NAME, i.VERSION, d.PLATFORM_NAME, ENDIAN_FORMAT

  2  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d, V$INSTANCE i

  3  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME

  4  and d.DB_UNIQUE_NAME = i.INSTANCE_NAME;

 

NAME      VERSION           PLATFORM_NAME                                                                                 ENDIAN_FORMAT

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

RMAN      11.2.0.1.0        Linux x86 64-bit                                                                              Little

 

SQL>

 

 

结论: 可知windows 32位系统,linux64位系统,都是Little字节序。

 

一.3  linux 下操作

1、生成客户端初始化参数文件---linux操作
2、生成重建控制文件脚本---linux操作

一.3.1  linux下生成pfilecontrol file

 

------------------------------------------ linux 下操作

[oracle@rhel6 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 29 12:55:58 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create pfile='/home/oracle/initrman.ora' from spfile;

 

File created.

 

SQL> alter database backup controlfile to trace as '/home/oracle/contr_back.txt' ;

 

Database altered.

 

 

 

(三)查看数据文件的路径:

SQL> select name from v$datafile;

 

NAME

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

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

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

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

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

/u01/app/oracle/oradata/rman/rman.dbf

 

(四)关闭库--为创建一致性复制,首先关闭源数据库

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rhel6 ~]$

 

 

一.3.2  linux/u01/app/oracle/oradata/rman下的数据文件、重做日志文件、归档文件,还有刚才创建的pfile和控制文件及listener.oratnsnames.ora文件复制到windows平台上

wps3C8C.tmp 

 

一.4  windows 下操作

一.4.1  创建一个rman的实例,注意SID要与linux服务器中的相同

使用命令为Windows 添加相同的服务,并启动它

 

------------------------------------------ windows 下操作

 

 

C:\Documents and Settings\Administrator>oradim -new -sid rman

实例已创建。

 

 

 

wps3C8D.tmp 

 

一.4.2  修改初始化参数文件,并创建相关目录

修改之前:

rman.__db_cache_size=79691776

rman.__java_pool_size=4194304

rman.__large_pool_size=4194304

rman.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

rman.__pga_aggregate_target=58720256

rman.__sga_target=222298112

rman.__shared_io_pool_size=0

rman.__shared_pool_size=125829120

rman.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/rman/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/rman/control01.ctl','/u01/app/oracle/oradata/rman/control02.ctl'

*.db_block_size=8192

*.db_domain='lhr.com'

*.db_name='rman'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=rmanXDB)'

*.open_cursors=300

*.pga_aggregate_target=58720256

*.processes=30

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=38

*.sga_target=220200960

*.undo_tablespace='UNDOTBS1'

 

-----------修改之后

 

rman.__db_cache_size=79691776

rman.__java_pool_size=4194304

rman.__large_pool_size=4194304

rman.__oracle_base=F:\app\oracle  #ORACLE_BASE set from environment

rman.__pga_aggregate_target=58720256

rman.__sga_target=222298112

rman.__shared_io_pool_size=0

rman.__shared_pool_size=125829120

rman.__streams_pool_size=0

*.audit_file_dest=F:\app\oracle\admin\rman\adump

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='F:\app\oracle\oradata\rman\control01.ctl','F:\app\oracle\oradata\rman\control02.ctl'

*.db_block_size=8192

*.db_domain='lhr.com'

*.db_name='rman'

*.diagnostic_dest=F:\app\oracle

*.dispatchers='(PROTOCOL=TCP) (SERVICE=rmanXDB)'

*.open_cursors=300

*.pga_aggregate_target=58720256

*.processes=30

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=38

*.sga_target=220200960

*.undo_tablespace='UNDOTBS1'

 

C:\Documents and Settings\Administrator>mkdir F:\app\oracle\admin\rman\adump

 

C:\Documents and Settings\Administrator>mkdir F:\app\oracle\oradata\rman

 

一.4.3  创建spfile并启动到nomount状态

 

C:\Documents and Settings\Administrator>set ORACLE_SID=rman

 

C:\Documents and Settings\Administrator>echo %ORACLE_SID%

rman

 

C:\Documents and Settings\Administrator>sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.1.0 Production on 星期六 1129 13:36:42 2014

 

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

 

已连接到空闲例程。

 

SQL> create spfile from pfile='e:initrman.ora';

 

文件已创建。

 

SQL> startup nomount;

ORACLE 例程已经启动。

 

Total System Global Area  221790208 bytes

Fixed Size                  1373684 bytes

Variable Size             134220300 bytes

Database Buffers           83886080 bytes

Redo Buffers                2310144 bytes

SQL>

 

一.4.4  将相应的数据文件拷贝到相关的目录然后重建控制文件(也别忘了更改文件路径)

由于是完全备份,因此我们选择noresetlogs方式重建(如果你的复制并非建立数据文件一致性的基础上,那你只能选择resetlogs方式重建)

 

----原脚本

CREATE CONTROLFILE REUSE DATABASE "RMAN" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/rman/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/rman/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/rman/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

 

DATAFILE

  '/u01/app/oracle/oradata/rman/system01.dbf',

  '/u01/app/oracle/oradata/rman/sysaux01.dbf',

  '/u01/app/oracle/oradata/rman/undotbs01.dbf',

  '/u01/app/oracle/oradata/rman/users01.dbf',

  '/u01/app/oracle/oradata/rman/rman.dbf'

CHARACTER SET ZHS16GBK

;

 

 

--修改之后

CREATE CONTROLFILE REUSE DATABASE "RMAN" NORESETLOGS  NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 'F:\app\oracle\oradata\rman\redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 'F:\app\oracle\oradata\rman\redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 'F:\app\oracle\oradata\rman\redo03.log'  SIZE 50M BLOCKSIZE 512

DATAFILE

  'F:\app\oracle\oradata\rman\system01.dbf',

  'F:\app\oracle\oradata\rman\sysaux01.dbf',

  'F:\app\oracle\oradata\rman\undotbs01.dbf',

  'F:\app\oracle\oradata\rman\users01.dbf',

  'F:\app\oracle\oradata\rman\rman.dbf'

CHARACTER SET ZHS16GBK

;

 

 

-------------------  将相应的数据文件拷贝到相关的目录,然后创建控制文件:

 

 

SQL> CREATE CONTROLFILE REUSE DATABASE "RMAN" NORESETLOGS  NOARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 'F:\app\oracle\oradata\rman\redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 'F:\app\oracle\oradata\rman\redo02.log'  SIZE 50M BLOCKSIZE 512,

10    GROUP 3 'F:\app\oracle\oradata\rman\redo03.log'  SIZE 50M BLOCKSIZE 512

11  DATAFILE

12    'F:\app\oracle\oradata\rman\system01.dbf',

13    'F:\app\oracle\oradata\rman\sysaux01.dbf',

14    'F:\app\oracle\oradata\rman\undotbs01.dbf',

15    'F:\app\oracle\oradata\rman\users01.dbf',

16    'F:\app\oracle\oradata\rman\rman.dbf'

17  CHARACTER SET ZHS16GBK

18  ;

 

控制文件已创建。

 

一.4.5  打开数据库并添加临时表空间数据文件

--由于前面是在数据库正常关闭情况下拷贝的数据文件,处于一致性状态,不需要执行recover,直接open

 

SQL> ALTER DATABASE OPEN;

数据库已更改。

 

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE 'F:\app\oracle\oradata\rman\temp01.dbf'SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

表空间已更改。

 

 

一.4.6  由于是64位到32位操作系统,所以需要编译一下内核代码

错误原因:用64位系统上的备份片将数据库还原到32位系统中所产生,反过来也会产生此错误。

解决方案:运行脚本用32位系统重新编译一下内核参数即可

以下是详细描述:

 


$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Jul 30 11:21:16 2010

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ERROR:

ORA-06553: PLS-801: internal error [56319]

SQL> conn xxx/xxx

Connected.

 

ERROR at line 1:

ORA-06553: PLS-801: internal error [56319]

 

 


解决方法如下:


SQL> shutdown immediate;
SQL> startup upgrade;

SQL> @?/rdbms/admin/utlirp.sql

SQL> @?/rdbms/admin/utlrp.sql

SQL> shutdown immediate;

SQL> startup;

 

其中:

utlirp.sql的作用是把相关内容全部在32bit平台下编译一遍.

utlrp.sql的作用是编译所有失效对象.

然后再重新连接,就不会报错了。

 

告警日志报错内容:

Error 604 in kwqmnpartition(), aborting txn

Sat Nov 29 14:00:09 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_mmon_5476.trc:

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_mmon_5476.trc:

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_ora_5436.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-06553: PLS-801: 内部错误 [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_ora_5436.trc:

ORA-00604: 递归 SQL 级别 1 出现错误

ORA-06553: PLS-801: 内部错误 [56327]

Completed: alter database open

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_mmon_5476.trc:

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_mmon_5476.trc:

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:11 2014

Starting background process CJQ0

Sat Nov 29 14:00:11 2014

CJQ0 started with pid=21, OS id=3048

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:15 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_j000_5048.trc:

ORA-12012: error on auto execute of job 57371

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:18 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_q000_1940.trc:

ORA-06553: PLS-801: internal error [56327]

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:25 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:35 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:45 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

Sat Nov 29 14:00:55 2014

Errors in file f:\app\oracle\diag\rdbms\rman\rman\trace\rman_cjq0_3048.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []

ORA-06553: PLS-801: internal error [56327]

 

 

一.4.7  其它配置工作

重配一下listener及tnsnames,重建密码文件等等其它工作你懂的。。。

一.4.8  测试OK

linuxrman库:

SQL> select dbid,name,current_scn,database_role,force_logging,open_mode from v$database;

 

      DBID NAME      CURRENT_SCN DATABASE_ROLE   FOR OPEN_MODE

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

1738582916 RMAN 1257134 PRIMARY   NO  READ WRITE

 

windows上的rman库:

SQL> select dbid,name,current_scn,database_role,force_logging,open_mode from v$database;

 

      DBID NAME      CURRENT_SCN DATABASE_ROLE    FOR OPEN_MODE

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

1738582916 RMAN          1306218 PRIMARY          NO  READ WRITE

 

注意:我原来是在没有编译内核代码的时候测试建表语句的时候内部错误,从告警日志也可以看出是内部错误,最后重新编译了内核后建表就没有问题了

 

SQL> create table t as select * from dual;

 

表已创建。

 

SQL> insert into t select * from dual;

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> delete from t;

 

已删除2行。

 

SQL> commit;

 

提交完成。

 

SQL> drop table t;

 

表已删除。

 

SQL>

一.4.9  删除数据库做其它测试

SQL> shutdown abort

ORACLE 例程已经关闭。

SQL> startup mount restrict;

ORACLE 例程已经启动。

 

Total System Global Area  221790208 bytes

Fixed Size                  1373684 bytes

Variable Size             138414604 bytes

Database Buffers           79691776 bytes

Redo Buffers                2310144 bytes

数据库装载完毕。

SQL> drop database;

 

数据库已删除。

 

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

SQL>

 

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

请登录后发表评论 登录
全部评论