数据库表空间转移问题

最新推荐文章于 2021-10-29 15:14:55 发布

congpu1904

最新推荐文章于 2021-10-29 15:14:55 发布

阅读量276

点赞数

表空间移植：
192.168.1.239数据库为在升级完软件后在建立库，而192.168.1.77是在建库过后升级数据库。在进行移植的时候出现下面问题。
原因是高版本到低版本会出错，低版本到高版本可以。
Compatibility Considerations for Transportable Tablespaces

When you create a transportable tablespace set, Oracle Database computes the lowest
compatibility level at which the target database must run. This is referred to as the
compatibility level of the transportable set. Beginning with Oracle Database 10g, a
tablespace can always be transported to a database with the same or higher
compatibility setting, whether the target database is on the same or a different
platform. The database signals an error if the compatibility level of the transportable
set is higher than the compatibility level of the target database.

高版本到低版本
192.168.1.239（source database)
数据库信息
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.3.0
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select t.endian_format
2 from v$transportable_platform. t,v$database d
3 where t.platform_name=d.platform_name;

ENDIAN_FORMAT
--------------
Little

SQL> create tablespace wbsc datafile '/opt/oracle/wbsc/wbsc.dbf' size 10m;

Tablespace created.

SQL> create user wbsc identified by wbsc default tablespace wbsc;

User created.

SQL> grant connect,resource to wbsc;

Grant succeeded.

SQL> create directory wbsc as '/opt/oracle/wbsc';

Directory created.

SQL> grant read,write on directory wbsc to system;

Grant succeeded.

SQL> execute sys.dbms_tts.transport_set_check('wbsc',true);

PL/SQL procedure successfully completed.

oracle@oracle:~> expdp system/qingsheng directory=wbsc dumpfile=wbsc.dmp transport_tablespaces=wbsc

Export: Release 10.2.0.4.0 - Production on Thursday, 06 May, 2010 6:54:21

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 06:54:43
SQL>alter tablespace wbsc read only;
oracle@oracle:~> expdp system/qingsheng directory=wbsc dumpfile=wbsc.dmp transport_tablespaces=wbsc

Export: Release 10.2.0.4.0 - Production on Thursday, 06 May, 2010 6:55:41

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=wbsc dumpfile=wbsc.dmp transport_tablespaces=wbsc
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/wbsc/wbsc.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 06:56:09

192.168.1.77(target database)
数据库信息
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
SQL> select * from v$version;

SQL> select t.endian_format
2 from v$transportable_platform. t,v$database d
3 where t.platform_name=d.platform_name;

ENDIAN_FORMAT
--------------
Little

oracle@linux-rpu7:~/trans> impdp system/qingsheng directory=trans dumpfile=wbsc.dmp transport_datafiles='/opt/oracle/trans/wbsc.dbf'

Import: Release 10.2.0.4.0 - Production on Wednesday, 19 May, 2010 15:02:45

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=trans dumpfile=wbsc.dmp transport_datafiles=/opt/oracle/trans/wbsc.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.1.0

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 15:02:54

低版本到高版本
192.168.1.77(source database)
QL> create user low identified by low default tablespace low;

User created.

SQL> grant connect,resource to low;

Grant succeeded.

SQL> conn low/low
Connected.
SQL> create table test as select * from user_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)
----------
1

SQL> conn / as sysdba
Connected.
SQL> alter tablespace low read only;

Tablespace altered.
oracle@linux-rpu7:~/trans> expdp system/qingsheng directory=trans dumpfile=low.dmp transport_tablespaces=low

Export: Release 10.2.0.4.0 - Production on Wednesday, 19 May, 2010 16:08:17

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=trans dumpfile=low.dmp transport_tablespaces=low
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/opt/oracle/trans/low.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:08:35

oracle@linux-rpu7:~/trans> scp low.dmp /opt/oracle/oradata/tmorcl/low.dbf oracle@192.168.1.239:/opt/oracle/wbsc/
The authenticity of host '192.168.1.239 (192.168.1.239)' can't be established.
RSA key fingerprint is fb:ed:d1:9c:1f:57:bd:05:f8:0f:02:ce:d2:cb:c0:a0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.239' (RSA) to the list of known hosts.
Password:
low.dmp 100% 76KB 76.0KB/s 00:00
low.dbf 100% 10MB 10.0MB/s 00:01

192.168.1.239(traget database)

SQL> create user low identified by low; 目标数据库中用户必须存在

User created.
oracle@oracle:~/wbsc> pwd
/opt/oracle/wbsc
oracle@oracle:~/wbsc> ls
low.dbf low.dmp
oracle@oracle:~/wbsc> impdp system/qingsheng directory=wbsc dumpfile=low.dmp transport_datafiles='/opt/oracle/wbsc/low.dbf'

Import: Release 10.2.0.4.0 - Production on Thursday, 06 May, 2010 8:07:22

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=wbsc dumpfile=low.dmp transport_datafiles=/opt/oracle/wbsc/low.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 08:07:35

SQL> alter tablespace low read write;

Tablespace altered.

SQL> conn low/low
ERROR:
ORA-01045: user LOW lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> grant connect,resource to low;

Grant succeeded.

SQL> conn low/low
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE

SQL> select count(*) from test;

COUNT(*)
----------
1

第版本数据库表空间可以移植到高版本数据库

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

转载于:http://blog.itpub.net/22779291/viewspace-663099/

congpu1904

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库表空间转移问题

表空间移植：192.168.1.239数据库为在升级完软件后在建立库，而192.168.1.77是在建库过后升级数据库。在进行移植的时候出现下面问题。原因是高版本到低版本会出错，低版本到高版本可以。Compatibility C...
复制链接

扫一扫