Avatar of AshleySPM
AshleySPMFlag for United States of America asked on

Oracle 11.1.0.7 Upgrade to 11.2.0.3

Hello,

I am testing an Oracle database upgrade. My current version is 11.1.0.7 and I am going to 11.2.0.3. I would like to move it to a new virtual server instead of an in place upgrade. I am trying the export/import method using the following commands:

expdp scott/tiger@oracle11.1.0.7 directory=DIR_DATAPUMP full=y dumpfile=dumpfile.dmp logfile=dumpfile.log encryption=ALL encryption_password=tiger


impdp scott/tiger@oracle11.2.0.3 directory=data_pump_dir full=y dumpfile=dumpfile.dmp logfile=impdp.log encryption_password=tiger


The export works fine. The import just repeats the command over and over in a command prompt but doesnt seem to do anything. Any suggestions?


Thank you,

Ashley
Oracle Database

Avatar of undefined
Last Comment
Praveen Kumar Chandrashekatr

8/22/2022 - Mon
MikeOM_DBA

To do full import from a full export you may need to use an oracle account with DBA role.
Praveen Kumar Chandrashekatr

what is that getting repeating and what is that error you are getting?

also if you can upload the log file it will be more helpful.
ASKER
AshleySPM

Nothing is actually writing to the log. The command prompt keeps repeating my command quickly like this:

E:\> impdp scott/tiger@oracle11.2.0.3 directory=data_pump_dir full=y dumpfile=dumpfile.dmp logfile=impdp.log encryption_password=tiger

E:\> impdp scott/tiger@oracle11.2.0.3 directory=data_pump_dir full=y dumpfile=dumpfile.dmp logfile=impdp.log encryption_password=tiger

E:\> impdp scott/tiger@oracle11.2.0.3 directory=data_pump_dir full=y dumpfile=dumpfile.dmp logfile=impdp.log encryption_password=tiger

But nothing actually happens.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
AshleySPM

Running the command prompt as administrator seems to have at least gotten the process going. I will follow up with results shortly.
ASKER
AshleySPM

So it is still erroring. It is not creating tablespaces, users, or roles. Exerpts from the log file:

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  scott/*******@oracle directory=data_pump_dir full=y dumpfile=dumpfile.dmp logfile=impdp.log encryption_password=*****
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name
Failing sql is:
CREATE TABLESPACE "SYSAUX" DATAFILE SIZE 125829120 AUTOEXTEND ON NEXT 10485760 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name

CREATE TABLESPACE "TBS_LOB_01" DATAFILE SIZE 4294967296 AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
ORA-39083: Object type TABLESPACE failed to create with error:
ORA-02236: invalid file name
Failing sql is:
CREATE TEMPORARY TABLESPACE "TBS_TEMP" TEMPFILE SIZE 34358689792 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576
Processing object type DATABASE_EXPORT/PROFILE
ORA-31684: Object type PROFILE:"APPUSER" already exists
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-31684: Object type USER:"OUTLN" already exists
ORA-31684: Object type USER:"SYSMAN" already exists
ORA-31684: Object type USER:"MGMT_VIEW" already exists
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'TBS_DATA_03' does not exist
Failing sql is:
 CREATE USER "APP_USER1" IDENTIFIED BY VALUES 'S:503F990CAC66785A742A60EA8DA87539503A8B5543487155FA39AD43C263;20F3B33194442EB5' DEFAULT TABLESPACE "TBS_DATA_03" TEMPORARY TABLESPACE "TBS_TEMP"
ORA-39083: Object type USER failed to create with error:
ORA-00959: tablespace 'TBS_DATA_02' does not exist


Thanks for your help


All the errors that follow relate to users not existing
ASKER
AshleySPM

It looks like I need to copy over the tablespaces first and I would like to do that using the expdp utility. However, in order to do that, it says that I have to make it read-only. I found instructions on how to do that, but how do I undo it when it is done? I am only testing right now so I want the original database to remain usable.


Thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
AshleySPM

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
AshleySPM

This wasn't really a solution to the particular issue - it was a different way to upgrade.
Praveen Kumar Chandrashekatr

from your post ID: 39577711 i see that yo were able to get the log file created and got the errors.

When ever you are importing using impdp from the expdp dump file and you are not aware of the structure of your previous database then first create the meta data using the dump

impdp system/***** schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=exp_test.dmp content=METADATA_ONLY logfile=imp_test.log
                                                    or

impdp system/***** schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=exp_test.dmp sqlfile=meta_ddl.sql

which will create the DLL script which you can use to create the database tables and then import the data into it.

impdp system/***** schemas=SCOTT directory=DATA_PUMP_DIR dumpfile=exp_test.dmp content=DATA_ONLY logfile=imp_test.log

http://oracleacedba.blogspot.in/2012/08/how-to-identify-tablespace-names-from.html

also refers the oracle doc
http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_import.htm#g1025464