Windows下的Oracle数据库移植到Linux下
hanruiding 2014-04-09
装一个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'
