oracle 创建表空间 pcincrease,一次oracle10g表空间迁移实验

系统： redhat-as4-u7

oracle：10.2.0.4

源数据库：

主机：192.168.1.241

实例：orcl

目标数据库：

主机：192.168.1.215

实例：lizidb

所要迁移的表空间名称：lizi，数据文件lizi.dbf

步骤：

第一步：源数据库以sys身份登陆，将lizi表空间置为read only状态：

SQL> alter tablespace lizi read only;

Tablespace altered.

SQL>

第二步：在客户端pc机上把源数据库lizi表空间用exp导出：

D:\>EXP \" as sysdba\" FILE=lizi.DMP LOG=log.LOG TRANSPORT_TABLESPACE=y TABLESPACES=\"lizi\"

Export: Release 10.2.0.1.0 - Production on 星期四 3月 4 16:46:13 2010

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

已导出 ZHS16GBK 字符集和 UTF8 NCHAR 字符集

注: 将不导出表数据 (行)

即将导出可传输的表空间元数据...

对于表空间 LIZI...

. 正在导出簇定义

. 正在导出表定义

. 正在导出引用完整性约束条件

. 正在导出触发器

. 结束导出可传输的表空间元数据

成功终止导出, 没有出现警告。

*************************************************************

该步骤中，开始对exp以sys身份导出表空间的命令一直不正确，经仔细的研究和google查询才找到以上正确的命令。以后在对本问题进行补充。

*************************************************************

第三步：将源数据库lizi表空间数据文件lizi.dbf拷贝到目标数据库相应的目录下：/u00/oracle/lizidb/lizi.dbf

第四步：将源数据库lizi表空间置为read write

SQL> alter tablespace lizi read write;

Tablespace altered.

SQL>

第五步：在客户端pc机上用imp进行表空间迁移，将lizi表空间导入至目标数据库

D:\>imp \" as sysdba\" FILE=lizi.DMP LOG=log.LOG TRANSPORT_TABLESPACE=y TABLESPACES=\"lizi\" datafiles='/u00/oracle/lizidb/lizi.dbf'

Import: Release 10.2.0.1.0 - Production on 星期三 3月 10 11:03:59 2010

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc

tion

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

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

即将导入可传输的表空间元数据...

已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入

. 正在将 SYS 的对象导入到 SYS

. 正在将 SYS 的对象导入到 SYS

成功终止导入, 没有出现警告。

注意：

*************************************************************

1、导入之前不要在目标数据库中创建相同的表空间记数据文件，否则会报下面的错误：

D:\>imp \" as sysdba\" FILE=users.DMP LOG=log.LOG TRANSPORT_TA

BLESPACE=y TABLESPACES=\"users\" datafiles='/u00/oracle/lizidb/users02.dbf'

Import: Release 10.2.0.1.0 - Production on 星期二 3月 30 16:53:44 2010

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

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

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

即将导入可传输的表空间元数据...

已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入

. 正在将 SYS 的对象导入到 SYS

. 正在将 SYS 的对象导入到 SYS

IMP-00017: 由于 ORACLE 错误 29349, 以下语句失败:

"BEGIN   sys.dbms_plugts.beginImpTablespace('USERS',4,'SYS',1,0,8192,1,65328"

"833,1,2147483645,8,128,8,0,1,0,8,2470502657,1,33,10743,NULL,0,0,NULL,NULL);"

" END;"

IMP-00003: 遇到 ORACLE 错误 29349

ORA-29349: tablespace 'USERS' already exists

ORA-06512: at "SYS.DBMS_PLUGTS", line 1801

ORA-06512: at line 1

IMP-00000: 未成功终止导入

2、

开始这一步骤中遇到了以下错误：

G:\>imp \" as sysdba\" FILE=lizi.DMP LOG=log.LOG TRANSPORT_TABLESPACE=y TABLESPACES=\"lizi\" datafiles='/u00/oracle/lizidb/lizi.dbf'

Import: Release 10.2.0.1.0 - Production on 星期三 3月 10 09:37:05 2010

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc

tion

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

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

即将导入可传输的表空间元数据...

已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入

. 正在将 SYS 的对象导入到 SYS

. 正在将 SYS 的对象导入到 SYS

IMP-00017: 由于 ORACLE 错误 721, 以下语句失败:

"BEGIN   sys.dbms_plugts.checkCompType('COMPATSG','10.2.0.3.0'); END;"

IMP-00003: 遇到 ORACLE 错误 721

ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.1.0

ORA-06512: at "SYS.DBMS_PLUGTS", line 2004

ORA-06512: at line 1

IMP-00000: 未成功终止导入

经google搜索检查，原来是目标数据库在由10.2.0.1版本升级到10.2.0.4版本时候，不知道是什么原因compatible参数并没有升级成功，但是升级过程中并没有报错，导致源数据库与目标数据库的compatible参数的版本不一样，检查init文件：

源数据库：compatible=10.2.0.3.0

目标数据库：compatible=10.2.0.1.0

因此这个问题解决方法需要先将compatible升级，但是升级compatible并不是简单的将pfile和spfile中的compatible直接修改就能解决，需要修改所有datafile的datafile header，否则会报contrlfile同compatible版本不一致的错误：

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  2089400 bytes

Variable Size             276827720 bytes

Database Buffers          788529152 bytes

Redo Buffers                6295552 bytes

ORA-00201: control file version 10.2.0.1.0 incompatible with ORACLE version

10.0.2.3.0

ORA-00202: control file: '/u00/oracle/lizidb/control01.ctl'

经高手指点，升级compatible方法如下：

SQL> alter system set compatible='10.2.0.3.0' scope=spfile;

System altered.

SQL> startup upgrade;ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  2089400 bytes

Variable Size             264244808 bytes

Database Buffers          801112064 bytes

Redo Buffers                6295552 bytes

Database mounted.

Database opened.

SQL> @/opt/oracle/product/10.2.0/rdbms/admin/catupgrd.sql

DOC>######################################################################

DOC>######################################################################

DOC>    The following statement will cause an "ORA-01722: invalid number"

DOC>    error if the user running this script is not SYS.  Disconnect

DOC>    and reconnect with AS SYSDBA.

DOC>######################################################################

DOC>######################################################################

DOC>#

no rows selected

。。。

。。。

。。。

(这一步需要执行漫长的sql批处理脚本，耐心等待)

。。。

。。。

。。。

Total Upgrade Time: 00:20:57

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The above PL/SQL lists the SERVER components in the upgraded

DOC>   database, along with their current version and status.

DOC>

DOC>   Please review the status and version columns and look for

DOC>   any errors in the spool log file.  If there are errors in the spool

DOC>   file, or any components are not VALID or not the current version,

DOC>   consult the Oracle Database Upgrade Guide for troubleshooting

DOC>   recommendations.

DOC>

DOC>   Next shutdown immediate, restart for normal operation, and then

DOC>   run utlrp.sql to recompile any invalid application objects.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

(执行完毕)

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  2089400 bytes

Variable Size             318770760 bytes

Database Buffers          746586112 bytes

Redo Buffers                6295552 bytes

Database mounted.

Database opened.

SQL> @/opt/oracle/product/10.2.0/rdbms/admin/utlrp.sql

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN  2010-03-10 10:57:50

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC>   objects in the database. Recompilation time is proportional to the

DOC>   number of invalid objects in the database, so this command may take

DOC>   a long time to execute on a database with a large number of invalid

DOC>   objects.

DOC>

DOC>   Use the following queries to track recompilation progress:

DOC>

DOC>   1. Query returning the number of invalid objects remaining. This

DOC>      number should decrease with time.

DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC>   2. Query returning the number of objects compiled so far. This number

DOC>      should increase with time.

DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC>   This script automatically chooses serial or parallel recompilation

DOC>   based on the number of CPUs available (parameter cpu_count) multiplied

DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC>   On RAC, this number is added across all RAC nodes.

DOC>

DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC>   recompilation. Jobs are created without instance affinity so that they

DOC>   can migrate across RAC nodes. Use the following queries to verify

DOC>   whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC>   1. Query showing jobs created by UTL_RECOMP

DOC>         SELECT job_name FROM dba_scheduler_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC>   2. Query showing UTL_RECOMP jobs that are running

DOC>         SELECT job_name FROM dba_scheduler_running_jobs

DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END  2010-03-10 10:59:25

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

0

(无错误)

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

0

(无错误，说明compatible升级成功)

SQL> show parameter compatible;

NAME                                 TYPE        VALUE

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

compatible                           string      10.2.0.3.0

SQL> SQL>

**************************************************************

第六步：在目标数据库上将表空间lizi置为read write

SQL> alter tablespace lizi read write;

Tablespace altered.

SQL>

以下进行数据迁移

第六步、导出源数据库用户及所有数据：

D:\>exp

Export: Release 10.2.0.1.0 - Production on 星期三 3月 17 10:54:51 2010

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

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

输入数组提取缓冲区大小: 4096 >

导出文件: EXPDAT.DMP > lizidata.dmp

(2)U(用户), 或 (3)T(表): (2)U > 2

导出权限 (yes/no): yes >

导出表数据 (yes/no): yes >

压缩区 (yes/no): yes >

已导出 ZHS16GBK 字符集和 UTF8 NCHAR 字符集

. 正在导出 pre-schema 过程对象和操作

. 正在导出用户 LIZI 的外部函数库名

. 导出 PUBLIC 类型同义词

. 正在导出专用类型同义词

. 正在导出用户 LIZI 的对象类型定义

即将导出 LIZI 的对象...

. 正在导出数据库链接

. 正在导出序号

. 正在导出簇定义

. 即将导出 LIZI 的表通过常规路径...

. . 正在导出表                           TESTA导出了       10004 行

. 正在导出同义词

. 正在导出视图

. 正在导出存储过程

. 正在导出运算符

. 正在导出引用完整性约束条件

. 正在导出触发器

. 正在导出索引类型

. 正在导出位图, 功能性索引和可扩展索引

. 正在导出后期表活动

. 正在导出实体化视图

. 正在导出快照日志

. 正在导出作业队列

. 正在导出刷新组和子组

. 正在导出维

. 正在导出 post-schema 过程对象和操作

. 正在导出统计信息

成功终止导出, 没有出现警告。

第七步、在目标数据库中创建用户

[oracle@db-primary ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 17 11:07:07 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

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

SQL> create user lizi identified by lizi default tablespace lizi temporary tablespace temp;

User created.

SQL> GRANT "CONNECT" TO "lizi";

GRANT "CONNECT" TO "lizi"

*

ERROR at line 1:

ORA-01917: user or role 'lizi' does not exist

SQL> GRANT "CONNECT" TO lizi;

Grant succeeded.

SQL> grant resource to lizi;

Grant succeeded.

SQL> alter user lizi default role all;

User altered.

SQL> conn lizi

Enter password:

Connected.

SQL> alter user lizi identified by lizi123;

User altered.

SQL> exit

第八步、在目标数据库中导入用户及数据

D:\>imp file=lizidata.dmp fromuser=lizi touser=lizi grants=y

Import: Release 10.2.0.1.0 - Production on 星期三 3月 17 11:08:52 2010

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc

tion

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

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 UTF8 NCHAR 字符集中的导入

. . 正在导入表                         "TESTA"导入了       10004 行

成功终止导入, 没有出现警告。

登陆数据库检查：

[oracle@db-primary ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 17 11:07:07 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

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

SQL> conn lizi/lizi123

Connected.

SQL>

SQL>

SQL> select tname from tab;

TNAME

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

TESTA

SQL> desc testa;

Name                                      Null?    Type

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

CODE                                               NUMBER(10)

NAME                                               VARCHAR2(10)

SQL> select  * from testa where rownum<11;

CODE NAME

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

1

2

3

4

5

6

7

8

9

10

10 rows selected.