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
LVL 1
AshleySPMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:
To do full import from a full export you may need to use an oracle account with DBA role.
0
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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.
0
AshleySPMAuthor Commented:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AshleySPMAuthor Commented:
Running the command prompt as administrator seems to have at least gotten the process going. I will follow up with results shortly.
0
AshleySPMAuthor Commented:
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
0
AshleySPMAuthor Commented:
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
0
AshleySPMAuthor Commented:
I followed the standard upgrade path on the same server instead of trying to move it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AshleySPMAuthor Commented:
This wasn't really a solution to the particular issue - it was a different way to upgrade.
0
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.