oracle数据库迁移

 

同版本数据库，同操作系统数据库的迁移；

迁移思路是，在目标操作系统上，按照源数据库的$ORACLE_BASE来安装一个数据库软件，然后拷贝相应的源数据库的数据文件、密码文件、spfile文件、相应dump的文件夹到迁移的库上来完成迁移；

 

步骤如下:

首先查看源数据的相应信息，为迁移做准备；

源数据库：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE10.2.0.3.0 Production

TNS for 32-bit Windows: Version 10.2.0.3.0- Production

NLSRTL Version 10.2.0.3.0 – Production

SQL> select file_name,file_id,tablespace_name from dba_data_files;

FILE_NAMEFILE_ID TABLESPACE_NAME

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

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF 4 USERS

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF 3 SYSAUX

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 2 UNDOTBS1

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 1 SYSTEM

E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF 5 EXAMPLE

E:\RM_TAB.DBF6 RM_TABLESPACE

E:\RMAN.DBF7 RMAN_TAB

 

已选择7行。

 

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string E:\ORACLE\PRODUCT\10.2.0\DB_1\

DATABASE\SPFILEORCL.ORA

 

然后在目标操作系统上创建数据库软件，注意，只安装数据库软件；

安装完成后，按照思路中说的，把相应的文件拷贝到迁移库的相应目录；

在迁移库上操作：

Microsoft Windows XP [版本 5.1.2600]

(C) 版权所有 1985-2001Microsoft Corp.

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

SQL*Plus: Release 10.2.0.3.0 - Productionon 星期一 7月 29 17:59:34 2013

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

ERROR:

ORA-12560: TNS: 协议适配器错误

请输入用户名:

ERROR:

ORA-12560: TNS: 协议适配器错误

请输入用户名:

ERROR:

ORA-12560: TNS: 协议适配器错误

SP2-0157: 在 3 次尝试之后无法连接到ORACLE, 退出 SQL*Plus

 

在迁移库上只是安装了数据库软件，这个时候没有oracle 服务，所以用oradim来创建一个与源数据相同的oracle 服务；

 

C:\Documents and Settings\xuniji>oradim-new -sid orcl

实例已创建。

查看是否创建成功

C:\Documents and Settings\xuniji>netstart

已经启动以下 Windows 服务:

Application Layer Gateway Service

Automatic Updates

COM+ Event System

Computer Browser

Cryptographic Services

DCOM Server Process Launcher

DHCP Client

Distributed Link Tracking Client

DNS Client

Error Reporting Service

Event Log

Fast User Switching Compatibility

Help and Support

IPSEC Services

Logical Disk Manager

Network Connections

Network Location Awareness (NLA)

OracleServiceorcl

Plug and Play

Print Spooler

……

……

……

 

再来登录

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

SQL*Plus: Release 10.2.0.3.0 - Productionon 星期一 7月 29 19:25:13 2013

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

已连接到空闲例程。

 

SQL> startup nomount;

ORACLE 例程已经启动。

Total System Global Area 838860800 bytes

Fixed Size 1293384 bytes

Variable Size 729809848 bytes

Database Buffers 104857600 bytes

Redo Buffers 2899968 bytes

SQL> alter database mount;

数据库已更改。

 

SQL> alter database open;

数据库已更改。

 

来验证一下是否是源库信息：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise EditionRelease 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE10.2.0.3.0 Production

TNS for 32-bit Windows: Version 10.2.0.3.0- Production

NLSRTL Version 10.2.0.3.0 - Production

 

SQL> set line 170

SQL> select * from scott.emp;

 

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7369 SMITH CLERK 7902 17-12? -80 800 20

7499 ALLEN SALESMAN 7698 20-2? -811600 300 30

7521 WARD SALESMAN 7698 22-2? -811250 500 30

7566 JONES MANAGER 7839 02-4? -812975 20

7654 MARTIN SALESMAN 7698 28-9? -811250 1400 30

7698 BLAKE MANAGER 7839 01-5? -812850 30

7839 KING PRESIDENT 17-11? -81 5000 10

7844 TURNER SALESMAN 7698 08-9? -811500 0 30

7900 JAMES CLERK 7698 03-12? -81 950 30

7902 FORD ANALYST 7566 03-12? -81 3000 20

7934 MILLER CLERK 7782 23-1? -821300 10

 

EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO

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

7901 YJG20

 

已选择12行。

 

SQL> select tablespace_name fromdba_tablespaces;

 

TABLESPACE_NAME

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

SYSTEM

UNDOTBS1

SYSAUX

TEMP

USERS

EXAMPLE

RMAN_TAB

RM_TABLESPACE

 

已选择8行。

 

OK，说明是源库信息；

 

这个时候如果远程链接该数据库，因为没有监听的原因，会提示“ora-12154无法解析指定的链接标示符”；

用Net ConfigurationAssistant 来配置监听即可；自此迁移完成；

 

附注alert_orcl.log 中的内容：

 

Mon Jul 29 19:25:23 2013

ORACLE V10.2.0.3.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Windows XP Version V5.1 Service Pack 3

CPU : 1 - type 586, 1 PhysicalCores

Process Affinity : 0x00000000

Memory (Avail/Total): Ph:732M/1023M,Ph+PgF:2283M/2462M, VA:1940M/2047M

Mon Jul 29 19:25:23 2013

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Shared memory segment for instancemonitoring created

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_10 parameter defaultvalue as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =18

LICENSE_MAX_USERS = 0

SYS auditing is disabled

ksdpec: called for event 13740 prior toevent group initialization

Starting up ORACLE RDBMS Version:10.2.0.3.0.

System parameters with non-default values:

processes = 150

sga_max_size =838860800

__shared_pool_size =503316480

shared_pool_size =503316480

__large_pool_size = 4194304

large_pool_size = 4194304

__java_pool_size = 4194304

java_pool_size = 4194304

__streams_pool_size = 4194304

streams_pool_size = 4194304

sga_target =629145600

control_files = E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL,E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL

db_block_size = 8192

__db_cache_size =109051904

db_cache_size =88080384

compatible =10.2.0.3.0

db_file_multiblock_read_count= 16

db_recovery_file_dest = F:\rmanbackup

db_recovery_file_dest_size=3221225472

fast_start_mttr_target = 90

undo_management = AUTO

undo_tablespace =UNDOTBS1

remote_login_passwordfile= EXCLUSIVE

db_domain =

dispatchers =(PROTOCOL=TCP) (SERVICE=orclXDB)

utl_file_dir = e:\oracle\logminer

job_queue_processes = 10

audit_file_dest= E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\ADUMP

background_dump_dest =E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\BDUMP

user_dump_dest =E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

core_dump_dest =E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\CDUMP

db_name = orcl

open_cursors = 300

pga_aggregate_target =203423744

PSP0 started with pid=3, OS id=1392

PMON started with pid=2, OS id=320

MMAN started with pid=4, OS id=1744

DBW0 started with pid=5, OS id=1452

LGWR started with pid=6, OS id=576

CKPT started with pid=7, OS id=928

SMON started with pid=8, OS id=1224

RECO started with pid=9, OS id=596

CJQ0 started with pid=10, OS id=1888

MMON started with pid=11, OS id=1296

Mon Jul 29 19:25:35 2013

starting up 1 dispatcher(s) for networkaddress '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

MMNL started with pid=12, OS id=1184

Mon Jul 29 19:25:35 2013

starting up 1 shared server(s) ...

Mon Jul 29 19:25:51 2013

alter database mount

Mon Jul 29 19:25:55 2013

Setting recovery target incarnation to 3

Mon Jul 29 19:25:55 2013

Successful mount of redo thread 1, withmount id 1349813183

Mon Jul 29 19:25:55 2013

Database mounted in Exclusive Mode

Completed: alter database mount

Mon Jul 29 19:26:04 2013

alter database open

Mon Jul 29 19:26:05 2013

Thread 1 opened at log sequence 60

Current log# 2 seq# 60 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG

Successful open of redo thread 1

Mon Jul 29 19:26:05 2013

SMON: enabling cache recovery

Mon Jul 29 19:26:15 2013

Successfully onlined Undo Tablespace 1.

Mon Jul 29 19:26:15 2013

SMON: enabling tx recovery

Mon Jul 29 19:26:17 2013

Database Characterset is ZHS16GBK

replication_dependency_tracking turned off(no async multimaster replication found)

Starting background process QMNC

QMNC started with pid=16, OS id=1800

Mon Jul 29 19:27:01 2013

Completed: alter database open

Mon Jul 29 19:27:09 2013

db_recovery_file_dest_size of 3072 MB is5.12% used. This is a

user-specified limit on the amount of spacethat will be used by this

database for recovery-related files, anddoes not reflect the amount of

space available in the underlyingfilesystem or ASM diskgroup.

 

文件中的红色字体标示出的应该在启动数据库前有相应的路径和文件（夹）存在，如果没有就要创建；

 

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

博文所有内容均属原创，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog: http://blog.csdn.net/xuelanggongwu

Email: [e-mail]