OraFAQ Forum: Backup & Recovery » copying data & database from one machine to another

Home » RDBMS Server » Backup & Recovery » copying data & database from one machine to another

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

copying data & database from one machine to another [message #162813]

Mon, 13 March 2006 14:00

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

Hi guys. something simple but amazingly i cannot find the formula. This is so easy for every other database but i cannot copy a database from one machine to another using oacle 9i. The both machines are installed with the same version i.e. oracle 9i. I have a database witha few tables in one machine at work, with a few tuples of data and I want to transport that same database to home, both the schema and the data. How do I do that in oracle? Cause i have beeen searching on the internet all day and nothing works. Somebody help, and I'm a newbie to oracle, please give me something that I could understand. This is so easy in Mysql and access, how is it that difficult in oracle? Somebody help me please, I don't want to get fired.

Jennifer.

Report message to a moderator

Re: copying data & database from one machine to another [message #162832 is a reply to message #162813]

Mon, 13 March 2006 15:15

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://www.orafaq.com/faq/import_export

Report message to a moderator

Re: copying data & database from one machine to another [message #162900 is a reply to message #162813]

Tue, 14 March 2006 01:16

sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata

Senior Member

Hi,
U can use the Following Steps for Exporting Data From one machine.

Open a Command Prompt then Enter the <Oracle Home Directroy>/bin
Write the Following Line
EXP userName/Password@ConnectingString
where ConnectingString is your TNS name.
Then a Input Screen appear by asking that
Enter array fetch buffer size: 4096 >
Simply press Enter Key.
next
Export file: EXPDAT.DMP >
Here you put the Export File Name like
Export file: EXPDAT.DMP >C:\expdata.dmp
Next asked is What do u want to export. You need to Export User
So simply press Enter
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >

Export grants (yes/no): yes >

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified users ...
User to be exported: (RETURN to quit) >

Now here give the User Name like

User to be exported: (RETURN to quit) >scott

After that your Export will start.

Now copy the .dmp file and import it into another machine on the same user which u exported.

IMP userName/Password@ConnectingString

Export file: EXPDAT.DMP >c:\expdata.dmp
Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no >yes
Import entire export file (yes/no): no > yes
Then your export file will be imported on the other database.

Report message to a moderator

Re: copying data & database from one machine to another [message #164267 is a reply to message #162900]

Wed, 22 March 2006 12:46

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

Ok I have a question. This probably might be a stupid question but keep in mind I am a newbie with Oracle.

EXP userName/Password@ConnectingString

You said the ConnectingString is a TNS name. What is that and how do I find out what is my TNS name. I just accepted all the defaults.

My username is: system
password is: useruser
service: oracle

- Jennifer

Report message to a moderator

Re: copying data & database from one machine to another [message #164297 is a reply to message #162900]

Wed, 22 March 2006 19:19

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

Don't bother to reply to my last question, I figured it out, the TNS is the global DBase name. Now thank you for the detailed steps, it was quite clear, I understood it, however one problem, I followed exactly what you detailed. I got the .dmp file to export. When I went to the other machine, I create a user with the same username and password that I had on the original machine,

username - tstt
password - tstt

It seemed that everything worked fine except that when it was finished and I went back and did select table_name from user_tables; I got no rows, which implied to me that it was not created. Here is a listing of the export:

-----------------------------------------------------------------

C:\Documents and Settings\Darren>cd c:\oracle\ora92\bin

C:\oracle\ora92\bin>IMP tstt/tstt@oracle

Import: Release 9.2.0.1.0 - Production on Wed Mar 22 20:47:13 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Import file: EXPDAT.DMP > c:\test.dmp

Enter insert buffer size (minimum is 8192) 30720>

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
List contents of import file only (yes/no): no > yes

Import entire export file (yes/no): no > yes

. importing TSTT's objects into TSTT
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>'TSTT', export_db_name="
">'ORACLE.US.ORACLE.COM', inst_scn=>'1557915');"
"COMMIT; END;"
"CREATE SEQUENCE "FILESSEQUENCE" MINVALUE 1 MAXVALUE 99999999999999999999999"
"9999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER NOCYCLE"
"CREATE SEQUENCE "FRAMESEQUENCE" MINVALUE 1 MAXVALUE 99999999999999999999999"
"9999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE"
"CREATE SEQUENCE "KIOSKGROUPSEQUENCE" MINVALUE 1 MAXVALUE 999999999999999999"
"999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE"
"CREATE SEQUENCE "KIOSKSEQUENCE" MINVALUE 1 MAXVALUE 99999999999999999999999"
"9999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE"
"CREATE SEQUENCE "MICROSOFTSEQDTPROPERTIES" MINVALUE 1 MAXVALUE 999999999999"
"999999999999999 INCREMENT BY 1 START WITH 1 CACHE 50 NOORDER NOCYCLE"
"CREATE TABLE "FILES" ("FILEID" NUMBER(*,0), "FILENAME" VARCHAR2(100), "ADNA"
"ME" VARCHAR2(100), "FILESIZE" VARCHAR2(100), "CREATIONDATE" DATE, "COMPANY""
" VARCHAR2(100)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INIT"
"IAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING NOCOMP"
"RESS"
. . skipping table "FILES"

"ALTER TABLE "FILES" ADD PRIMARY KEY ("FILEID") USING INDEX PCTFREE 10 INIT"
"RANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TA"
"BLESPACE "SYSTEM" LOGGING ENABLE"
"CREATE TABLE "FRAME" ("FRAMEID" NUMBER(*,0), "FRAMENAME" VARCHAR2(100), "ST"
"ATUS" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(I"
"NITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING NOC"
"OMPRESS"
. . skipping table "FRAME"

"ALTER TABLE "FRAME" ADD PRIMARY KEY ("FRAMEID") USING INDEX PCTFREE 10 INI"
"TRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) T"
"ABLESPACE "SYSTEM" LOGGING ENABLE"
"CREATE TABLE "KIOSK" ("KIOSKID" NUMBER(*,0), "LOCATION" VARCHAR2(100), "KIO"
"SKNAME" VARCHAR2(100)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORA"
"GE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING"
" NOCOMPRESS"
. . skipping table "KIOSK"

"ALTER TABLE "KIOSK" ADD PRIMARY KEY ("KIOSKID") USING INDEX PCTFREE 10 INI"
"TRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) T"
"ABLESPACE "SYSTEM" LOGGING ENABLE"
"CREATE TABLE "KIOSKGROUP" ("KIOSKGPID" NUMBER(*,0), "GROUPNAME" VARCHAR2(10"
"0), "FRAMEID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 S"
"TORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOG"
"GING NOCOMPRESS"
. . skipping table "KIOSKGROUP"

"ALTER TABLE "KIOSKGROUP" ADD PRIMARY KEY ("KIOSKGPID") USING INDEX PCTFREE"
" 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU"
"PS 1) TABLESPACE "SYSTEM" LOGGING ENABLE"
"CREATE TABLE "KIOSKTOGROUP" ("KIOSKGPID" NUMBER(*,0), "KIOSKID" NUMBER(*,0)"
") PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREE"
"LISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS"
. . skipping table "KIOSKTOGROUP"

"ALTER TABLE "KIOSKTOGROUP" ADD PRIMARY KEY ("KIOSKGPID", "KIOSKID") USING "
"INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 "
"FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING ENABLE"
"CREATE TABLE "MICROSOFTDTPROPERTIES" ("ID" NUMBER CONSTRAINT "MICROSOFT_NN_"
"ID" NOT NULL ENABLE, "OBJECTID" NUMBER, "PROPERTY" VARCHAR2(64) CONSTRAINT "
""MICROSOFT_NN_PROPERTY" NOT NULL ENABLE, "VALUE" VARCHAR2(255), "LVALUE" LO"
"NG RAW, "VERSION" NUMBER CONSTRAINT "MICROSOFT_NN_VERSION" NOT NULL ENABLE)"
" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREEL"
"ISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING NOCOMPRESS"
" ALTER TABLE "MICROSOFTDTPROPERTIES" MODIFY ("VERSION" DEFAULT (0) )"
. . skipping table "MICROSOFTDTPROPERTIES"

"CREATE UNIQUE INDEX "MICROSOFT_PK_DTPROPERTIES" ON "MICROSOFTDTPROPERTIES" "
"("ID" , "PROPERTY" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65"
"536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING"
"ALTER TABLE "MICROSOFTDTPROPERTIES" ADD CONSTRAINT "MICROSOFT_PK_DTPROPERT"
"IES" PRIMARY KEY ("ID", "PROPERTY") USING INDEX PCTFREE 10 INITRANS 2 MAXTR"
"ANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SY"
"STEM" LOGGING ENABLE "
"CREATE TABLE "SLOT" ("FRAMEID" NUMBER(*,0), "SLOTNUM" NUMBER(*,0), "FILEID""
" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIA"
"L 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING NOCOMPRE"
"SS"
. . skipping table "SLOT"

"ALTER TABLE "SLOT" ADD PRIMARY KEY ("FRAMEID", "SLOTNUM") USING INDEX PCTF"
"REE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST G"
"ROUPS 1) TABLESPACE "SYSTEM" LOGGING ENABLE"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = PLSQL"
"CREATE PROCEDURE DT_DROPUSEROBJECTBYID ( PARAM_ID IN NUMBER ) AS BEGIN DELE"
"TE FROM MICROSOFTDTPROPERTIES WHERE OBJECTID = PARAM_ID; END DT_DROPUSEROBJ"
"ECTBYID; "
"CREATE PROCEDURE DT_SETPROPERTYBYID ( PARAM_ID IN NUMBER, PARAM_PROPERTY "
"IN VARCHAR2, PARAM_VALUE IN VARCHAR2, PARAM_LVALUE IN LONG RAW ) AS BEGIN"
" DECLARE X NUMBER(40); BEGIN SELECT COUNT(*) INTO X FROM MICROSOFTDTPROPERT"
"IES WHERE OBJECTID=PARAM_ID AND PROPERTY=PARAM_PROPERTY; IF X = 0 THEN INSE"
"RT INTO MICROSOFTDTPROPERTIES (ID, PROPERTY, OBJECTID, VALUE, LVALUE, VERSI"
"ON) VALUES (MICROSOFTSEQDTPROPERTIES.NEXTVAL, PARAM_PROPERTY, PARAM_ID, PAR"
"AM_VALUE, PARAM_LVALUE, 0); ELSE UPDATE MICROSOFTDTPROPERTIES SET VALUE=PAR"
"AM_VALUE, LVALUE=PARAM_LVALUE, VERSION=VERSION+1 WHERE OBJECTID=PARAM_ID AN"
"D PROPERTY=PARAM_PROPERTY; END IF; END; END DT_SETPROPERTYBYID; "
"CREATE FUNCTION DT_ADDUSEROBJECT RETURN NUMBER IS NEWOBJECTID NUMBER; BEGIN"
" /* GET THE NEXT VALUE FROM THE SEQUENCE FOR THE OBJECT ID */ SELECT MICROS"
"OFTSEQDTPROPERTIES.NEXTVAL INTO NEWOBJECTID FROM DUAL; /* ADD THE PARENT NO"
"DE FOR THE NEW OBJECT */ INSERT INTO MICROSOFTDTPROPERTIES ( ID, OBJECTID, "
"PROPERTY ) VALUES ( NEWOBJECTID, NEWOBJECTID, 'DtgSchemaOBJECT' ); RETURN( "
"NEWOBJECTID ); END DT_ADDUSEROBJECT; "
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = NONE"
"ALTER PROCEDURE "DT_DROPUSEROBJECTBYID" COMPILE REUSE SETTINGS TIMESTAMP '2"
"006-03-10:09:50:42'"
"ALTER PROCEDURE "DT_SETPROPERTYBYID" COMPILE REUSE SETTINGS TIMESTAMP '2006"
"-03-10:09:50:43'"
"ALTER FUNCTION "DT_ADDUSEROBJECT" COMPILE REUSE SETTINGS TIMESTAMP '2006-03"
"-10:09:50:41'"
"CREATE TRIGGER "TSTT".Files_bir"
"BEFORE INSERT ON Files"
"FOR EACH ROW"
"WHEN (new.FileId IS NULL)"
"BEGIN"
" SELECT FilesSequence.NEXTVAL"
" INTO :new.FileId"
" FROM dual;"
"END;"
"ALTER TRIGGER "FILES_BIR" ENABLE"
"CREATE TRIGGER "TSTT".Frame_bir"
"BEFORE INSERT ON Frame"
"FOR EACH ROW"
"WHEN (new.FrameId IS NULL)"
"BEGIN"
" SELECT FrameSequence.NEXTVAL"
" INTO :new.FrameId"
" FROM dual;"
"END;"
"ALTER TRIGGER "FRAME_BIR" ENABLE"
"CREATE TRIGGER "TSTT".KioskGroup_bir"
"BEFORE INSERT ON KioskGroup"
"FOR EACH ROW"
"WHEN (new.KioskGpId IS NULL)"
"BEGIN"
" SELECT KioskGroupSequence.NEXTVAL"
" INTO :new.KioskGpId"
" FROM dual;"
"END;"
"ALTER TRIGGER "KIOSKGROUP_BIR" ENABLE"
"CREATE TRIGGER "TSTT".Kiosk_bir"
"BEFORE INSERT ON Kiosk"
"FOR EACH ROW"
"WHEN (new.KioskId IS NULL)"
"BEGIN"
" SELECT KioskSequence.NEXTVAL"
" INTO :new.KioskId"
" FROM dual;"
"END;"
"ALTER TRIGGER "KIOSK_BIR" ENABLE"
Import terminated successfully without warnings.

C:\oracle\ora92\bin>

--------------------------------------------------------------

What did I do wrong.

- Jennifer

Report message to a moderator

Re: copying data & database from one machine to another [message #164311 is a reply to message #164297]

Wed, 22 March 2006 21:07

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Don't bother to reply to my last question, I figured it out, the TNS is the global DBase name.
You sure?
it can be anything you want. Here mydb1 is the TNS. You can call it whatever you want. mydb2/myddb3? You can find those entries in tnsnames.ora

mydb1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mutation)
    )
  )

Your current issue is a consequence of
>>List contents of import file only (yes/no): no > yes
which is effectively equivalent of SHOW=Y.

Did you even go through the faq link that was posted?
Please find more information in documentation
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/toc.htm

[Updated on: Wed, 22 March 2006 21:08]

Report message to a moderator

Re: copying data & database from one machine to another [message #165948 is a reply to message #164311]

Mon, 03 April 2006 10:04

JenniferBabes
Messages: 18
Registered: March 2006
Location: Student

Junior Member

I got it to work.

One question, I thought that the tns global name was the Database global name or the username that was created. YOu said it could be found in the tnsnames.ora.

How could I find this tnsnames.ora?

Jennifer

Report message to a moderator