Home » RDBMS Server » Server Utilities » datapump option for DB upgrade from solaris to Linux (oracle 11.2.0.4)
datapump option for DB upgrade from solaris to Linux [message #632274] Wed, 28 January 2015 09:52 Go to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
hi,

can you suggest the best optioni should be using to copy from oracle 10g solaris, to linux 11.2.0.4,
in lower envs like dev, QA, each DB has 10 Application schemas pretty small like 5 to 20 gb,
Linux DB instance is already made, i just need to bring all APP schemas from source to target,

i had datapump as one of the option, so if datapump, should i create users, application tablespace before in target DB,
what are options/parameters i should be using in datapump, and to get the DDl's of tablespace,users before hand .

Thanks

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632278 is a reply to message #632274] Wed, 28 January 2015 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

If you use full export tablespaces and users will be created on import.
If you use schema export only users will be created on import, you have to precreate the tablespaces.

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632280 is a reply to message #632278] Wed, 28 January 2015 10:35 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
thanks michel,

looks like full exp is good option and i can remove the unwanted one from target later,
but will i get SYS, other SYSTEM related users also in full exp which i think we don't want in target as target DB is already created
with system related users.

thanks for your time

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632282 is a reply to message #632280] Wed, 28 January 2015 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
i get SYS, other SYSTEM related users also in full exp


No.
From the documentation:

Quote:
•A full export does not export system schemas that contain Oracle-managed data and metadata. Examples of system schemas that are not exported include SYS, ORDSYS, and MDSYS.


Also:

Quote:
FULL=YES indicates that all data and metadata are to be exported. Filtering can restrict what is exported using this export mode.


You can exclude the schemas (and other components) you don't want even on FULL export.

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632704 is a reply to message #632282] Wed, 04 February 2015 12:48 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Hi,

i used below steps for export and import using data pump from 10g to 11.2.0.4
userid="/ as sysdba"
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_full20150204_%U.dmp
CONTENT=ALL
SCHEMAS=('RPTS','FORMS')
LOGFILE=col1_exp_full20150204.log
parallel=8



userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=exp_full20150204_%U.dmp
logfile=imp_full_schema_col2.log
REMAP_TABLESPACE=(RPTS_DATA:RPTS_DATA,RPTS_INDEX:RPTS_INDEX,FORMS:FORMS_DATA,FORMS_INDEX:FORMS_INDEX)
remap_schema=(FORMS:FORMS,RPTS:RPTS)
content=data_only
parallel=6
transform=oid:n


getting this below error.


ORA-39034: Table TABLE_DATA:"RPTS"."BASE_EVENT" does not exist.
ORA-39034: Table TABLE_DATA:"RPTS"."GETACCESS" does not exist.
ORA-39034: Table TABLE_DATA:"RPTS"."LGDUSER" does not exist.exist.
ORA-39034: Table TABLE_DATA:"FORMS"."DRA_DETAIL" does not exist.
ORA-39034: Table TABLE_DATA:"FORMS"."DRA_SUMMARY" does not exist.

what's wrong in the exp or imp parameters.. Please advice.

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632705 is a reply to message #632704] Wed, 04 February 2015 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Please post complete logs from export and import.

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632712 is a reply to message #632705] Wed, 04 February 2015 14:57 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
EXP PAR
[code]
userid="/ as sysdba"
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=col1_exp_full20150204_%U.dmp
CONTENT=ALL
SCHEMAS=('XXXX', 'YYYYY', 'ZZZZZ, 'ABCD')
LOGFILE=col1_exp_full20150204.log
parallel=8

/code]
LAST Lines of Exp Log, No errors in entire log file..

******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_01.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_02.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_03.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_04.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_05.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_06.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_07.dmp
/oradb/app/oracle/product/10.2.0/db_1/rdbms/log/col1_exp_full20150204_08.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:02:13


IMP PAR

userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=col1_exp_full20150204_%U.dmp
logfile=imp_full_schema_col2.log
REMAP_TABLESPACE=(ABCD:ABCD_DATA,XYZ:XYZ_INDEX)
remap_schema=(XXXX:XXXXX,YYYYYYY:YYYYYY,ZZZZZZ:ZZZZZZZ,ABCD:ABCD)
content=data_only
parallel=6
transform=oid:n


IMP LOG Copied

ORA-39034: Table TABLE_DATA:"ABCD"."BATCH_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."BATCH_MASTER_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."BATCH_TRANSACTION_DETAILS_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."CHANNEL_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_BATCHING_LOGIC_INFO" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_BATCH_ALERT_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_BATCH_DET_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_BATCH_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_BATCH_TRANS_DET_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_CODE_XREF_LOOKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_COUNTRY_CCY" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_DAILY_LIMIT" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_ERRORS" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_EXT_REMITTANCE_DETAILS" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_EXT_REMITTANCE_DET_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_FILE_ALERT_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_FILE_FORMAT" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_FILE_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_INSTRUMENT_TYPE_MAPPING" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_ISO_COUNTRY_LIST" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_PROC_EXECUTION_LOG" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_REVOKE_BATCH_ALERT_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_RULE_SWITCH" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_SIGNATURE_DETAILS_LOG" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_SIGNATURE_LOG" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_SIGNATURE_SUMMARY_LOG" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_STATUS_DESCRIPTION" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_STATUS_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TEMPORARY_PAYMENT" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_MASTER_BACKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_MASTER_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_REMIT" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_REMIT_BACKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_REMIT_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_TRAN_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_TRAN_MASTER_BACKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TMPLT_TRAN_MASTER_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."ABCD_TRANSACTION_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."MESSAGES" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."FIELDS_DETAIL" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."FIELD_MULTICHOICE" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."PRF_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."REM_DETAIL" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TEST2" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMPLT_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMPLT_MASTER_BACKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMPLT_MASTER_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMLT_REMT_DETAILS" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMLT_REMT_DETAILS_BACKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMLT_REMIT_DETAILS_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMLT_TRANS_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMLT_TRANS_MASTER_BACKUP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMLT_TRANS_MASTER_TEMP" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TMP_FILE_LAST_UPDATED_BY" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."TRANSACTION_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."USER_ENTITLED_ACCOUNTS" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."USER_ENTITLED_INSTRUMENT_TYPES" does not exist.
ORA-39034: Table TABLE_DATA:"ABCD"."USER_ENTITLED_PAYMENT_TYPES" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."BAN_MESSAGE" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."CONFIGURATION" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."CTRY_ISD_CODE" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."C_COUNTRY" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."C_COUNTRY_TERRITORIES" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."CS_CURRENCY" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."CS_HOLIDAY" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."CS_WEEKENDS" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."D0513_EN_US_EVENT_IDENTIFIER" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."DIL_SIGN_INFO" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EN_US_EVENT_CATEGORY" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EN_US_EVENT_IDENTIFIER" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EN_US_EVENT_TYPE" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EVENT_AUDIT" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EVENT_AUDIT_CTRL" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EVENT_DIAGNOSIS" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EVENT_FILTER" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."EVT_BASE_EVENT" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."GETACCESSXREF" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."LOGGEDONUSER" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."LOGGEDONUSER2" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."PREFERENCES_TMP" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."REPORTCONFIG" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."REPORTINFO" does not exist.
ORA-39034: Table TABLE_DATA:"XXXXX"."SEPA_DESTINATION_COUNTRIES" does not exist.
ORA-39034: Table TABLE_DATA:"YYYYYYY"."DRA_DETAIL" does not exist.
ORA-39034: Table TABLE_DATA:"YYYYYYY"."DRA_SUMMARY" does not exist.
ORA-39034: Table TABLE_DATA:"ZZZZZZ"."ACTION_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ZZZZZZ"."CATEGORY_MASTER" does not exist.
ORA-39034: Table TABLE_DATA:"ZZZZZZ"."GROUP_MASTER" does not exist.
Job "SYS"."SYS_IMPORT_FULL_01" completed with 368 error(s) at Wed Feb 4 15:25:02 2015 elapsed 0 00:00:03


Thanks for looking into it.

[Updated on: Wed, 04 February 2015 15:00]

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #632713 is a reply to message #632712] Wed, 04 February 2015 15:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>content=data_only
So no metadata & therefore no objects to contain the data

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #633510 is a reply to message #632713] Thu, 19 February 2015 15:44 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
hi,
Export parameter file, to export few Schemas

userid="/ as sysdba"
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=col1_exp_full20150217_%U.dmp
CONTENT=ALL
SCHEMAS=('ACT_ENCYC_D02',
'ACT_ENCYC_USRD02',
'ACT_ISVR_D02',
'ACT_ISVR_USRD02',
'ACT_SYS_D02',
'ACT_SYS_USRD02')
LOGFILE=col1_exp_full20150217.log
parallel=8


Import Parameter 
userid="/ as sysdba"
directory=DATA_PUMP_DIR
dumpfile=col1_exp_full20150217_%U.dmp
logfile=imp_full_schema_col20219.log
REMAP_TABLESPACE=(ACT_ENC_DATA:ACT_ENC_DATA,
ACT_ENC_INDEX:ACT_ENC_INDEX,
ACT_ISVR_DATA:ACT_ISVR_DATA,
ACT_ISVR_INDEX:ACT_ISVR_INDEX,
ACT_SYS_DATA:ACT_SYS_DATA,
ACT_SYS_INDEX:ACT_SYS_INDEX)
content=all
exclude=grant
parallel=6
transform=oid:n


I am getting below errors on additional Tablespaces, the copied Users and tablespaces were already created..
ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'QUARTZ_INDEX' does not exist
ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'TOOLS' does not exist


so I stopped the job,
it seems some additional tablespaces which are not created are required, does that means Users which i have exported have either data or quota on these missing tablespaces ?
how can i find out what all the tablespaces are required to be mapped in my import for X numbers Schemas getting exported.

Thanks for your time and suggestions

Report message to a moderator

Re: datapump option for DB upgrade from solaris to Linux [message #633511 is a reply to message #633510] Thu, 19 February 2015 15:47 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

If you need these tablespaces in the target database you can ignore these errors.

Report message to a moderator

Previous Topic: Export Statistics of schema from the database
Next Topic: SQL Loader Error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 15:56:50 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.