ITPub博客

首页 > 数据库 > Oracle > windows下的oracle库移植到linux下

windows下的oracle库移植到linux下

原创 Oracle 作者:nathanzhn 时间:2014-04-08 16:05:40 0 删除 编辑

装一个Windows 2008 Server 64并装Oracle,然后迁移库至Linux环境里
Windows Server 2008-192.168.182.128 Oracle 11.2.0.3.0 ORACLE_SID=orcl
Oracle Enterprise Linux 5.8-192.168.182.131 Oracle 11.2.0.1.0 ORACLE_SID=ORCL

流程与10g库迁移到11g相同,在修改控制文件的脚本的时候,除了要修改路径,还要修改数据库名
1 check information from PROD
on 128-windows oracle服务器
sqlplus / as sysdba

SYS@orcl>select dbid from v$database;

      DBID
----------
1371889729

SYS@orcl>create pfile='c:\initorcl.ora' from spfile;

File created.

SYS@PROD1>alter database backup controlfile to trace;

Database altered.

SYS@PROD1>show parameter user_dump

NAME                                 TYPE        VALUE                                                                                          
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      C:\oracle\diag\rdbms\orcl\orcl
                                                 \trace
                                                
cd C:\oracle\diag\rdbms\orcl\orcl\trace
copy the trc file to c:\dbclone\control.txt
and edit it:
from:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/ORCL/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/ORCL/UNDOTBS01.DBF',
  '/u01/app/oracle/oradata/ORCL/USERS01.DBF',
  '/u01/app/oracle/oradata/ORCL/EXAMPLE01.DBF'
CHARACTER SET AL32UTF8
;
to:
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/ORCL/REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/ORCL/REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/ORCL/REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/ORCL/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/ORCL/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/ORCL/UNDOTBS01.DBF',
  '/u01/app/oracle/oradata/ORCL/USERS01.DBF',
  '/u01/app/oracle/oradata/ORCL/EXAMPLE01.DBF'
CHARACTER SET AL32UTF8
;


2.2 cold backup tar-128 orcl

SYS@orcl>select name from v$dbfile;                                                                                                             

NAME                                                                                                                                            
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\USERS01.DBF                                            
C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF                  
C:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF                                                                                                             
C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF


SYS@orcl>select member from v$logfile;                                                                                                          

MEMBER                                                                        
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\REDO03.LOG                                                                                                               
C:\ORACLE\ORADATA\ORCL\REDO02.LOG 
C:\ORACLE\ORADATA\ORCL\REDO01.LOG     

SYS@orcl>select name from v$controlfile;

NAME                                                                           
--------------------------------------------------------------------------------                                                                
C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL
C:\ORACLE\RECOVERY_AREA\ORCL\CONTROL02.CTL   

SYS@orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

cd C:\ORACLE\ORADATA\
安装7zip软件
打成orcl.zip包
传到131服务器上放到/u01/app/oracle/oradata目录下解压缩,并且把目录名改为大写的ORCL

把之前生成的"c:\initorcl.ora"传到131服务器的/s01/app/oracle/product/11.2.0/db_1/dbs/下,改名为initORCL.ora
并到131服务器的/s01/app/oracle/product/11.2.0/db_1/dbs/下生成密码文件
orapwd file=orapwORCL password=oracle

cd $ORACLE_HOME/network/admin
vi listener.ora
添加sid_list
vi tnsnames.ora
添加连接串orcl

2.2 check path and SID
131->
mkdir -p /u01/app/oracle/admin/ORCL/adump

cd $ORACLE_HOME/dbs
vi initORCL.ora
ORCL.__db_cache_size=671088640
ORCL.__java_pool_size=16777216
ORCL.__large_pool_size=16777216
ORCL.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=704643072
ORCL.__sga_target=1023410176
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=285212672
ORCL.__streams_pool_size=16777216
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORCL'
*.db_recovery_file_dest='/home/oracle/flash'
*.db_recovery_file_dest_size=1G
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.memory_target=1717567488
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

2.3 delete old control files
cd /u01/app/oracle/oradata/ORCL/
rm CONTROL01.CTL


2.4 create spfile from pfile-131 ORCL
[oracle@oelr5u8-1 ORCL]$ export ORACLE_SID=ORCL
[oracle@oelr5u8-1 ORCL]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 5 19:50:29 2014

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

@>connect sys/oracle as sysdba
Connected to an idle instance.
SYS@ORCL>create spfile from pfile;

File created.

SYS@ORCL>startup nomount
ORACLE instance started.

Total System Global Area 1724186624 bytes
Fixed Size                  1337016 bytes
Variable Size            1040189768 bytes
Database Buffers          671088640 bytes
Redo Buffers               11571200 bytes

SYS@ORCL>CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/ORCL/REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/ORCL/REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/ORCL/REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/ORCL/SYSTEM01.DBF',
 14    '/u01/app/oracle/oradata/ORCL/SYSAUX01.DBF',
 15    '/u01/app/oracle/oradata/ORCL/UNDOTBS01.DBF',
 16    '/u01/app/oracle/oradata/ORCL/USERS01.DBF',
 17    '/u01/app/oracle/oradata/ORCL/EXAMPLE01.DBF'
 18  CHARACTER SET AL32UTF8
 19  ;

Control file created.

SYS@ORCL>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 6175
Session ID: 125 Serial number: 3

SYS@ORCL>startup upgrade
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SYS@ORCL>startup migrate
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

SYS@ORCL>alter database open upgrade;
ERROR:
ORA-03114: not connected to ORACLE


SYS@ORCL>conn / as sysdba
Connected to an idle instance.
SYS@ORCL>startup upgrade
ORACLE instance started.

Total System Global Area 1724186624 bytes
Fixed Size                  1337016 bytes
Variable Size            1040189768 bytes
Database Buffers          671088640 bytes
Redo Buffers               11571200 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/SYSTEM01.DBF'


----



在这之后,把linux服务器上的数据库和实例名从大写的ORCL改为小写的orcl,并修改了所有的参数文件内容和密码文件
重新解压缩冷备份的数据文件,并重新生成控制文件,使用REUSE生成
CREATE CONTROLFILE REUSE DATABASE "orcl" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/REDO03.LOG'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/orcl/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/orcl/UNDOTBS01.DBF',
  '/u01/app/oracle/oradata/orcl/USERS01.DBF',
  '/u01/app/oracle/oradata/orcl/EXAMPLE01.DBF'
CHARACTER SET AL32UTF8
;

然后:
SYS@orcl>shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup upgrade
ORACLE instance started.

Total System Global Area 1724186624 bytes
Fixed Size                  1337016 bytes
Variable Size            1040189768 bytes
Database Buffers          671088640 bytes
Redo Buffers               11571200 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@orcl>alter database open resetlogs upgrade;

Database altered.

竟然打开了!!为什么??
但之后
SYS@orcl>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup
ORACLE instance started.

Total System Global Area 1724186624 bytes
Fixed Size                  1337016 bytes
Variable Size            1040189768 bytes
Database Buffers          671088640 bytes
Redo Buffers               11571200 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 7775
Session ID: 125 Serial number: 5

startup upgrade就可以打开数据库:
为什么?
SYS@orcl>startup upgrade
ORACLE instance started.

Total System Global Area 1724186624 bytes
Fixed Size                  1337016 bytes
Variable Size            1040189768 bytes
Database Buffers          671088640 bytes
Redo Buffers               11571200 bytes
Database mounted.
Database opened.
SYS@orcl>

执行catupgrd.sql失败,应该是因为Linux的Oracle 11g版本低于Windows 11g。

网上有这么一段,用于编译32位、64位平台的存储过程,因本次克隆没有自编译的存储过程,因此不需要执行。
2) run $ORACLE_HOME/rdbms/admin/utlirp.sql <---- UTLIRP.sql (to migrate from 32 bit to 64 bit)
3) shut immediate
4) startup 
5)run $ORACLE_HOME/rdbms/admin/utlrp.sql
??
是因为64->32的缘故?还是因为平台的缘故?考虑用rman convert的方法重新做一次。


SYS@orcl>SELECT * FROM V$TRANSPORTABLE_PLATFORM order by ENDIAN_FORMAT;

PLATFORM_ID PLATFORM_NAME                                                                                 ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
          3 HP-UX (64-bit)                                                                                    Big
          6 AIX-Based Systems (64-bit)                                                                        Big
         18 IBM Power Based Linux                                                                             Big  
          2 Solaris[tm] OE (64-bit)                                                                           Big  
          4 HP-UX IA (64-bit)                                                                                 Big
         16 Apple Mac OS                                                                                      Big
          1 Solaris[tm] OE (32-bit)                                                                           Big  
          9 IBM zSeries Based Linux                                                                           Big
         17 Solaris Operating System (x86)                                                                    Little
         19 HP IA Open VMS                                                                                    Little
         20 Solaris Operating System (x86-64)                                                                 Little

PLATFORM_ID PLATFORM_NAME                                                                                 ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
         12 Microsoft Windows x86 64-bit                                                                      Little
         13 Linux x86 64-bit                                                                                  Little
          8 Microsoft Windows IA (64-bit)                                                                     Little
         21 Apple Mac OS (x86-64)                                                                             Little
         11 Linux IA (64-bit)                                                                                 Little
          5 HP Tru64 UNIX                                                                                     Little
         10 Linux IA (32-bit)                                                                                 Little
          7 Microsoft Windows IA (32-bit)                                                                     Little
         15 HP Open VMS                                                                                       Little

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

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

注册时间:2014-01-23

  • 博文量
    49
  • 访问量
    263401