Solved

Migrating oracle database.

Posted on 2014-01-07
12
431 Views
Last Modified: 2014-02-17
hi people i have a databse in a server oracle 10g. i dont know oracle and i need to move this database or something to another server 11

i can connect to the data with sqlplus system/pass@cm and all work great.
how can i move to other server this data? step by step please im a newbie on oracle
0
Comment
Question by:pablouruguay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39762039
How large is the database?
Do you need just one user or does the application have several users?

I'm thinking a simple export/import but will need the information above before I provide the syntax.
0
 
LVL 14

Author Comment

by:pablouruguay
ID: 39762130
the database is 156Mb and i need 1 user only this user system
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39762310
Have you already created the database on the 11g server?

If not, go ahead and to it.  To make like easier, pre-create the same tablespaces that you are using in the 10g database.

Then pre-create the user you are wanting to move over.


Log into the 10g database server and from a command prompt:
exp system/password file=myMigrate.dmp owner=userToMigrate consistent=Y compress=Y

Copy it over to the 11g server (binary copy).

Then from a command prompt:
imp system/password file=myMigrate.dmp full=y
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 14

Author Comment

by:pablouruguay
ID: 39762471
mmm. this only export the user i need to export the database called CM
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39762474
>>mmm. this only export the user i need to export the database called CM

I do not understand what you are trying to tell me with this post.
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 39764447
Yes you can export whole database and import it to new server, but since the system and sys schema related most of the data will already present in the new server i.e 11g so you may encounter error while importing which you can ignore.

But you think you have only one schema in your CM database which has all the related data you can go for export/ import the schema or user only as Slightwv mention in is post.

here are the detail steps to do so

check how may users present in 10g database i.e

SQL> select username from dba_users where username not in ('SYS','SYSTEM','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN');

Also check the tablespace created in 10g dtabase i.e

SQL>select * from dba_tablespaces;

SQL> select * from dba_directories;

Now export the database using expdp to the directory present above.
export ORACLE_SID=<SID>
expdp system/**** schemas=<list of users which you want to export> directory=DATA_PUMP_DIR dumpfile=exp_CM.dmp logfile=exp_CM.log

The below one is for full database export
expdp system/**** full=Y directory=DATA_PUMP_DIR dumpfile=exp_CM.dmp logfile=exp_CM.log

Copy the exp_CM.dmp file to 11g server in the directory present, then create the tablespaces in 11g database which are not present.

example:
CREATE TABLESPACE <Tablespace_name> DATAFILE '<path>' SIZE 10M AUTOEXTEND   ON ;

now import it
export ORACLE_SID=<SID>
impdp system/**** schemas==<list of users which you want to export> directory=DATA_PUMP_DIR dumpfile=exp_CM.dmp logfile=imp_CM.log

check this doc for reference
http://blog.oraclecontractors.com/?p=626

http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf
0
 
LVL 14

Author Comment

by:pablouruguay
ID: 39765917
i do this. and i have in the new server. Invalid username and passwords when i try to enter my software.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
ID: 39765927
>>Invalid username and passwords when i try to enter my software

Does you application use database usernames and passwords or does it use a user table that it created?

If it uses actual database usernames then you have more than one user to export.
0
 
LVL 12

Accepted Solution

by:
Praveen Kumar Chandrashekatr earned 166 total points
ID: 39767219
>>Invalid username and passwords when i try to enter my software.<<

which are the schemas you used to export and import  and can you provide the output of the below SQL

SQL> select username from dba_users where username not in ('SYS','SYSTEM','APPQOSSYS','DBSNMP','ORACLE_OCM','OUTLN');

and also the export/import syntax which you used and its log files.
0
 
LVL 14

Author Comment

by:pablouruguay
ID: 39767653
In the new server 11g have this users.

SQL> select username from dba_us
SSYS','DBSNMP','ORACLE_OCM','OUT

USERNAME
------------------------------
MGMT_VIEW
SYSMAN
SHERATONC
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
APEX_030200
OWBSYS_AUDIT
WKSYS

USERNAME
------------------------------
WK_TEST
ORDDATA
CTXSYS
ANONYMOUS
XDB
WKPROXY
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
SCOTT

USERNAME
------------------------------
XS$NULL
BI
PM
MDDATA
IX
SH
DIP
OE
APEX_PUBLIC_USER
HR
SPATIAL_CSW_ADMIN_USR

USERNAME
------------------------------
SPATIAL_WFS_ADMIN_USR

34 rows selected.

SQL>


and in the old server

SQL>  select username from dba
OSSYS','DBSNMP','ORACLE_OCM','

USERNAME
------------------------------
MGMT_VIEW
SYSMAN
OLAPSYS
SI_INFORMTN_SCHEMA
ORDPLUGINS
WKPROXY
XDB
ANONYMOUS
CTXSYS
WK_TEST
WKSYS

USERNAME
------------------------------
WMSYS
DMSYS
EXFSYS
ORDSYS
MDSYS
HR
OE
DIP
SH
IX
MDDATA

USERNAME
------------------------------
PM
BI
SCOTT
SHERATONC

26 rows selected.




and the line to export i use this

expdp system/**** full=Y directory=DATA_PUMP_DIR dumpfile=exp_CM.dmp logfile=exp_CM.log
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 39767794
then how did you import in new server full or schema level?

did you check all the users present in old server are also present in new server?

also if possible can you share the import log.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
ID: 39767841
A lot of the users from dba_users are internal Oracle created users.  You typically do not need to 'migrate' them.

You need to know what users you need to migrate.

Some Experts on the site have never had a problem doing a FULL export followed by a FULL import.  Personally, I do not recommend them as things between releases can change for the Oracle provided users and it may cause issues.

I never do a FULL.  I export/import only the users I know I need.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question