TTS from Single to RAC





WHY

在软件升级到10.2.0.5之后无法使用带有schema的方式的建库,所以从10.2.0.1的软件中取出example来实现schema的整体迁移

ENV

os: oel 5.5 64bit

db: source 10.2.0.1; target 10.2.0.5

clusterware: 10.2.0.5

fs: source lvm; target asm

PROCESSES

source

检查自包含

设置只读

导出

数据文件复制到目标端

恢复读写

target

建立TBS上的用户

HR, SH, OE, IX, PM, BI

用RMAN做fs到ASM的转换

导入

设置读写

TIPS

不需要建立TBS

others

少建一个BI的用户

LOG

source

SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME=’EXAMPLE’;

FILE_NAME

——————————————————————————–

TABLESPACE_NAME

——————————

/u01/app/oracle/oradata/ora10gd/example01.dbf

EXAMPLE

SQL> select OWNER, disctinct (OWNER) from dba_segments where TABLESPACE_NAME=’EXAMPLE’;

OWNER

——————————

HR

SH

OE

PM

IX

SQL> execute sys.dbms_tts.transport_set_check(‘EXAMPLE’,true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

SQL> alter tablespace example read only;

Tablespace altered.

SQL> ! mkdir /u01/dump

SQL>! ls -ld /u01/dump

drwxr-xr-x 2 ora10g oinstall 4096 May 16 16:29 /u01/dump

SQL> create or replace directory mydir as ‘. /u01/dump’;

Directory created.

SQL> grant read,write on directory mydir to public;

Grant succeeded.

SQL> exit

[ora10g@oeld ~]$ cd /u01/dumpls

[ora10g@oeld dump]$ expdp system/oracle dumpfile=expeexample.dbmp directory=dummyudidydir transport_tablespaeces=EXAMPLE

Export: Release 10.2.0.1.0 – 64bit Production on Wednesday, 16 May, 2012 16:36:35

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expexample.dmp directory=mydir transport_tablespaces=EXAMPLE

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ

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:

/u01/dump/expexample.dmp

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:37:09

[ora10g@oeld dump]$ scp expexample.dmp 173.10.10.101:/u01/dump

[ora10g@oeld dump]$ scp /u01/app/oracle/oradata/ora10gd/example01.dbf /u01/dump173.10.10.101:/u01/dump

[ora10g@oeld dump]$ sqlplus / as sysdba

SQL> alter tablespace example read write;

Tablespace altered.

target

[ora10g@oela ~]$ mkdir /u01/dmump

[ora10g@oela ~]$ cd /u01/dupmp

[ora10g@oela dump]$ ls

[ora10g@oela dump]$ sqecho #$oracORACLE_SID

ora10gc1

[ora10g@oela dump]$ sqlplus / as sysdba

SQL> reate or replace directory mydir as ‘/u01/dump’;

Directory created.

SQL> grant read,write on directory mydir to public;

Grant succeeded.

SQL> create user HR identified by hr default tablespace example;

User created.

SQL> grant resource , connect o to hr;

Grant succeeded.

SQL> create user sh identified by sh default tablespace example;

User created.

SQL> grant resource,connect to sh;

Grant succeeded.

SQL> create user oe identified by oe default tablespace example;

User created.

SQL> default tablespace example;grant resource,connect to grant resource,connect to oe;

Grant succeeded.

SQL> create user pm identified by pm default tablespace example;

User created.

SQL> grant resource,connect to pm

2 ;

Grant succeeded.

SQL> create user ix identified by ix identified by default tablespace example; default tablespace example;

User created.

SQL> grant resource,connect to ix;

Grant succeeded.

SQL> exit

[ora10g@oela dump]$ rman taget rget /

Recovery Manager: Release 10.2.0.5.0 – Production on Wed May 16 16:51:30 2012

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

connected to target database: ORA10GC (DBID=4094988700)

RMAN> connvert datafile ‘/u01/dump/example01.dbf’

2> db_file_name_convert ="/u01/dump/example01.dbf","+DATA";

Starting backup at 16-MAY-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=136 instance=ora10gc1 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input filename=/u01/dump/example01.dbf

converted datafile=+DATA/ora10gc/datafile/example.276.783449567

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03

Finished backup at 16-MAY-12

RMAN> exit

Recovery Manager complete.

[ora10g@oela dump]$ sqlplus / as sysdbaimpdp system/oracle dumpfile=expexample.dmp directory=mydir transport_datafiles=+DATA/ora10gc/datafile/example.276.783449567

Import: Release 10.2.0.5.0 – 64bit Production on Wednesday, 16 May, 2012 16:57:37

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, Real Application Clusters, 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/******** dumpfile=expexample.dmp directory=mydir transport_datafiles=+DATA/ora10gc/datafile/example.276.783449567

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."CUSTOMERS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."WAREHOUSES" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."ORDER_ITEMS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."ORDERS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."INVENTORIES" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."PRODUCT_INFORMATION" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."PRODUCT_DESCRIPTIONS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "OE"."PROMOTIONS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."SALES" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."COSTS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."TIMES" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."PRODUCTS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."CHANNELS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."PROMOTIONS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."CUSTOMERS" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."COUNTRIES" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."CAL_MONTH_SALES_MV" TO "BI"

ORA-39083: Object type OBJECT_GRANT failed to create with error:

ORA-01917: user or role ‘BI’ does not exist

Failing sql is:

GRANT SELECT ON "SH"."FWEEK_PSCAT_SALES_MV" TO "BI"

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings

ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings

ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings

ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW

ORA-31685: Object type MATERIALIZED_VIEW:"SH"."CAL_MONTH_SALES_MV" failed due to insufficient privileges. Failing sql is:

CREATE MATERIALIZED VIEW "SH"."CAL_MONTH_SALES_MV" ("CALENDAR_MONTH_DESC", "DOLLARS") USING ("CAL_MONTH_SALES_MV", (8, ‘ORA10GD.EXAMPLE.COM’, 2, 0, 0, "SH", "TIMES", ‘2012-05-16 14:23:09′, 8, 52050, ‘2012-05-16 14:23:10′, ”, 1, ‘0208′, 548974, 0, NULL, 1, "SH", "SALES", ‘2012-05-16 14:23:09′, 33032, 51992, ‘2012-05-16 14:23:10′, ”, 1, ‘88′, 548974, 0, NULL), 1183809, 8, (‘1950-01-01

ORA-31685: Object type MATERIALIZED_VIEW:"SH"."FWEEK_PSCAT_SALES_MV" failed due to insufficient privileges. Failing sql is:

CREATE MATERIALIZED VIEW "SH"."FWEEK_PSCAT_SALES_MV" ("WEEK_ENDING_DAY", "PROD_SUBCATEGORY", "DOLLARS", "CHANNEL_ID", "PROMO_ID") USING ("FWEEK_PSCAT_SALES_MV", (8, ‘ORA10GD.EXAMPLE.COM’, 3, 0, 0, "SH", "PRODUCTS", ‘2012-05-16 14:23:10′, 8, 52052, ‘2012-05-16 14:23:11′, ”, 1, ‘12′, 548993, 0, NULL, 1, "SH", "TIMES", ‘2012-05-16 14:23:10′, 8, 52050, ‘2012-05-16 14:23:11′, ”, 1, ‘82′

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 24 error(s) at 16:58:12

[ora10g@oela dump]$ sqlplus / as sysdba

SQL> alter tablespace example read write;

Tablespace altered.

如果新建了同样的tablespace

[ora10g@oela dump]$ impdp system/oracle dumpfile=expexample.dmp directory=myudir transport_datafiles=+DATA/ora10gc/datafile/example.276

.783449567

Import: Release 10.2.0.5.0 – 64bit Production on Wednesday, 16 May, 2012 16:55:08

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production

With the Partitioning, Real Application Clusters, 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/******** dumpfile=expexample.dmp directory=mydir transport_datafiles=+DATA/ora10gc/datafile/example.276.783449567

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29349: tablespace ‘EXAMPLE’ already exists

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:55:18




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

2 comments untill now

  1. abatuno@consort.ingested” rel=”nofollow”>.…

    ñïñ çà èíôó!…

  2. stateless@evocative.dominique” rel=”nofollow”>.…

    ñïñ!…

Add your comment now

无觅相关文章插件