5 Replies Latest reply: Mar 8, 2007 1:21 AM by 564854 RSS

    Export/Import in Oracle8i to Oracle10g

    492688
      Hi,
      We have an Oracle8i Enterprise Edition Release 8.1.6.0.0(32 Bit) running on AIX4.3.3,we are planning to upgrade/migrate it to[b] Oracle10g running on AIX5.3.If i do an export from oracle8i whether i can import it in Oracle 10g without any issues.

      Regards,
      Cherry
        • 1. Re: Export/Import in Oracle8i to Oracle10g
          522339
          Hi

          Thanks for your reply

          Have you been using Microsoft Exchange and have you migrated the Exchange to Outlook connection?

          Regard

          Cat
          • 2. Re: Export/Import in Oracle8i to Oracle10g
            563075
            i have also same problem.did u get the answer?please help me.

            sujit
            • 3. Re: Export/Import in Oracle8i to Oracle9i
              564854
              Hi,

              We are running Oracle 8i (8.1.6) in a SUN SPARC Solaris 5.6. It is a productive server, and we need to do some testing with the database for adding some features. hence we require the database to be copied to some other system ( A Pentium4 HT [intel X86] Windows 2000 server, running Oracle 9i Release 2). As im new to oracle, i know only this is possible via Export/ Import. I exported the database from Solaris (selected the Export Entire Database Option) it gave "Export terminated successfully with out warnings" , and using ftp, copied to Windows 2000 server. In windows 2000 server i created a database in the same name like in solaris, and created the same dba username (Given grant DBA to that user), and opened a command prompt and gave import username/password as DBA. I gave the dmp file name and left other options as default, and imported entire database. it reports "Import terminated successfully with out warnings".
              But when i login to ORACLE thru SQL+ as the username i used to import, it is not showing the tables. i have tried it many times, with different options and with different usernames nearly for past one month. but the result is always the same that i cant see any tables. but im able to create new tables here.
              I dont know where im making mistakes. im in a great pressure to complete this ASAP.
              Please any one kindly help me solving this problem.
              and we dont have any other SPARC machines or Oracle versions.

              Thanks in Advance,
              Sakthivel
              mailto: sakthivel.v@ap.sony.com

              Message was edited by:
              user561851
              • 4. Re: Export/Import in Oracle8i to Oracle9i
                3520
                Probably this forum would be more appropriate for your question as well as you'll get more readers and eventual answers there
                General Database Discussions

                Gints Plivna
                http://www.gplivna.eu
                • 5. Re: Export/Import in Oracle8i to Oracle9i
                  564854
                  Dear All,

                  Thanks for your efforts.

                  I have solved this problem and running the db in windows 2000 server without any problem.

                  Solution:

                  A tablespace should be created in the same name, size as in the solaris server. we cant transfer a tablespace created by solaris to windows. Before importing the data we need to create a tablespace and assign to the user (empuser).

                  here is the method that works for me.


                  1.GENERAL STEPS TO FOLLOW:
                  ****************************************

                  WINDOWS 2000 Server:
                  -----------------------------------

                  Install Oracle 9i release 1 (9.0.1.1.1) in windows 2000 server.
                  Create a database with following parameters;
                  1. Select 'General Database'
                  2. Global Database Name: EMP
                  3. SID: EMP
                  4. All default Options and click finish to create and start the oracle instance.
                  5. Login to SQL-PLUS (username: scott, Password: tiger, Hoststring: <empty>)
                  6. Connect as sys/change_on_install as sysdba;
                  7. CREATE TABLESPACE empdeploy DATAFILE 'C:\oracle\oradata\EMP\emp_data_new.dbf' SIZE 1000m AUTOEXTEND ON NEXT 100m MAXSIZE 2048m EXTENT MANAGEMENT LOCAL;
                  8. CREATE USER empuser IDENTIFIED BY emppass DEFAULT TABLESPACE EMPDEPLOY TEMPORARY TABLESPACE TEMP;
                  9. GRANT DBA TO EMPUSER (Im not sure whether i given this command or not, dont use this unless u get error during import)
                  10. disconnect

                  References:
                  ----------
                  http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm
                  http://www.adp-gmbh.ch/ora/sql/create_tablespace.html

                  SUN-SOLARIS 5.6 (SPARC):
                  ------------------------------------------
                  It is running Oracle Enterprise edition 8.1.6.
                  Get the informations below,
                  INSTANCE NAME
                  SID
                  USER HAVING CONTROL TO THAT INSTANCE (like here it was EMPUSER)
                  TABLESPACES USED
                  TABLESPACE NAME
                  DATAFILE NAME and PATH
                  ORACLE VERSION


                  2.EXPORT/IMPORT PROCEDURE:
                  ****************************************

                  I) Exporting Database from SOLARIS SERVER:
                  ******************************************************

                  option-1:
                  ------------

                  exp empuser/emppass
                  leave all default options
                  Select Entire database to export (option 1)
                  ........
                  YOU SHOULD GET 'Export terminated successfully without warnings'. (Failure causes may be low disk space, ensure that u have enough disk space)

                  Option-2:
                  ---------

                  exp empuser/emppass
                  leave all default options
                  Enter the user name whose tables to be exported: EMPUSER
                  Enter the user name whose tables to be exported: <Enter-to-quit>
                  ........
                  YOU SHOULD GET 'Export terminated successfully without warnings'.

                  Option-3:
                  ---------

                  exp TRIGGERS=y CONSTRAINTS=y GRANTS=y FILE=expdat.dmp
                  Username: EMPUSER as sysdba
                  Password: emppass
                  leave all default options
                  Select Entire database to export (option 1)
                  ........
                  YOU SHOULD GET 'Export terminated successfully without warnings'.

                  Option-4 (99% wont Work, RISKY):
                  ---------

                  EXPORT THE TRANSPORTABLE TABLE SPACE (This is different from exporting tables/data, so if all the above fails then export the Transportable table space and import into windows probably it will fail due to platform issue and CHARACTER SET Conversion)
                  Now import the expdat.dmp file again and see.
                  Change the required TABLESPACES READ ONLY.
                  exp TRANSPORT_TABLESPACE=y TABLESPACES=PMTDEPLOY TRIGGERS=y CONSTRAINTS=y GRANTS=y FILE=expdat.dmp
                  Username: EMPUSER as sysdba
                  Password: emppass
                  ........
                  YOU SHOULD GET 'Export terminated successfully without warnings'.
                  Now Again Change the TABLESPACES to READ WRITE. (If you forgot this production server cant able to write to the database, the database will be left in read-only mode.)

                  $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


                  II) TRANSFERRING EXPORTED DATA FROM SOLARIS TO WINDOWS 2000 Server.
                  *************************************************************************************************

                  Now from windows system,
                  Open command prompt.
                  connect to SOLARIS SERVER USING FTP.
                  ( cd /user1/home/oracle)change to the Path where you have exported the expdat.dmp file.
                  set the transfer mode to binary (dont use the default ASCII Transfer Mode)
                  get expdat.dmp
                  close
                  quit
                  Now the database was successfully exported from solaris and transferred to Windows 2000 server.

                  $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

                  III) Importing the Database in Windows 2000 server:
                  ***************************************************

                  Open a Command prompt,

                  imp FULL=y IGNORE=n
                  Enter User Name : empuser
                  Password : emppass

                  You should get the same messages what you got during export, with out any other warnings or big messages running in the screen.
                  After completion you will get,
                  'Import terminated successfully without warnings'.


                  +++++++++Now Login thru SQLPLUS using username: empuser, password: emppass++++++++++
                  #the following query should return the total number of tables (142 here), this should be same as what you get in solaris. If you get 'no rows selected' then the import was failed, may be take a look at creating tablespaces, or around tablespaces)

                  select * from tab;
                  ....
                  ...
                  ....
                  ......
                  ..
                  ....
                  142 rows selected.


                  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  A kind request:
                  if you got your problem solved, please update the forum / usergroup and write how you solved the problem. this might be helpful for others who may face the same trouble. for me also there was some old postings that having similar problem. but they haven't given the solution. after their problem solved just left this forums.

                  Thanks & Regards,
                  Sakthivel