Home » RDBMS Server » Server Utilities » Copy database
Copy database [message #544758] Thu, 23 February 2012 11:09 Go to next message
eddyno
Messages: 20
Registered: January 2012
Location: Beograd
Junior Member
Hello, i've 0racle 9i database, i wanted to know the easiest way how to copy that database on another computer throw local network,
Re: Copy database [message #544765 is a reply to message #544758] Thu, 23 February 2012 11:27 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How is that problem related to Forms?

Source database is 9i. What is a target database?

Do you want to "copy" the whole database, or is it just a schema (i.e. one particular user with his tables, procedures, etc.)?
Re: Copy database [message #544780 is a reply to message #544765] Thu, 23 February 2012 14:15 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
For copy whole database go

c:\oracle\products\10.1.0\Db_1\BIN\EXP

Then type username & password

USERNAME----System or anyother DBA User
PASSWORD----whatever

Export file: EXPDAT.DMP > C:\ENTIRE_DATABASE.DMP

Cheers

[Updated on: Thu, 23 February 2012 14:17]

Report message to a moderator

Re: Copy database [message #544905 is a reply to message #544780] Fri, 24 February 2012 09:52 Go to previous messageGo to next message
eddyno
Messages: 20
Registered: January 2012
Location: Beograd
Junior Member
Yeah, i want to copy the whole database to another comp on network. Now i have ENTIRE_DATABASE.DMP in my c: direktory, wondering just how to open it(whole database) and how to open database I just copied on another computer. And targer is 9i database on another comp.

[Updated on: Fri, 24 February 2012 09:53]

Report message to a moderator

Re: Copy database [message #544909 is a reply to message #544905] Fri, 24 February 2012 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "database" mean for you?

Regards
Michel
Re: Copy database [message #544910 is a reply to message #544909] Fri, 24 February 2012 10:40 Go to previous messageGo to next message
eddyno
Messages: 20
Registered: January 2012
Location: Beograd
Junior Member
"Database" means know for me collection of tables and records in tables.

And i tried exp and after that imp and it reports in my sql*plus ORA-12545: Connect failed because target host or object doesn't exist
Re: Copy database [message #544912 is a reply to message #544910] Fri, 24 February 2012 12:25 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
eddyno wrote on Fri, 24 February 2012 11:40
"Database" means know for me collection of tables and records in tables.

And i tried exp and after that imp and it reports in my sql*plus ORA-12545: Connect failed because target host or object doesn't exist


Sounds to me like you are defining a schema, not a database.

How can you expect anybody to debug what you tell us without showing us what you did? And please do not "tell" us what you did; "show" us.
Re: Copy database [message #544913 is a reply to message #544912] Fri, 24 February 2012 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition,
ORA-12545: Connect failed because target host or object does not exist
 *Cause: The address specified is not valid, or the program being
 connected to does not exist.
 *Action: Ensure the ADDRESS parameters have been entered correctly; the
 most likely incorrect parameter is the node name.  Ensure that the
 executable for the server exists (perhaps "oracle" is missing.)
 If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
 host name to a numeric IP address and try again.

Regards
Michel
Re: Copy database [message #544969 is a reply to message #544913] Sat, 25 February 2012 10:57 Go to previous messageGo to next message
eddyno
Messages: 20
Registered: January 2012
Location: Beograd
Junior Member
Ok, i installed on my laptop oracle 9i, and database called o9i, with more that 20 tables. Later I installed oracle forms on laptop and copy tnsnames to directory orant and it worked fine. After that I installed on my desktop database with same name "o9i", and oracle forms 6, but my coputer wasn't on network. On my laptop(C:\oracle\ora90\BIN) i opened EXE app, and typed username and pass, lateron as mughalsking sain i made Export file: EXPDAT.DMP > C:\ENTIRE_DATABASE.DMP. After that on my desktop i opened the Import file: EXPDAT.DMP > C:\ENTIRE_DATABASE.DMP. Pressing default values tables were copied. When i tried to logg in my sql*plus with scott/tiger it reports :
ORA-12545: Connect failed because target host or object does not exist.
So i change the name of desktop computer with same name as laptop, change the host in tnsnames to 192.168.0.1 but now it reports

ORA-12203: TNS:unable to connect to destination.

And i'm not trying to define a schema, a want to copy whole physical database with all relation cursors and tables

[Updated on: Sat, 25 February 2012 11:28]

Report message to a moderator

Re: Copy database [message #544978 is a reply to message #544969] Sat, 25 February 2012 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
database called o9i, with more that 20 tables

This is certainly NOT an entire database. Where did you create those tables? I sincerely hope not in SYS schema.

Joy Division told you NOT to tell us story, but rather show what you did. It means that you were supposed to copy/paste entire exporting (and importing) session over here. Why? Because what you are saying is probably not what you did. You said that you "typed username and pass" - which username was it?

Anyway: sooner you start answering questions the way they are asked, sooner you'll get help.
Re: Copy database [message #544995 is a reply to message #544969] Sun, 26 February 2012 04:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'll have a go at helping. export/import does not copy a database: it copies tables from one database into another. So, the question becomes "have you created a database on the desktop PC?" Installing the software is not enough. Until you create a database, there is nothing into which you can import the tables.
Re: Copy database [message #545003 is a reply to message #544758] Sun, 26 February 2012 05:25 Go to previous messageGo to next message
eddyno
Messages: 20
Registered: January 2012
Location: Beograd
Junior Member
I created those tables in sql*plus with scott/tiger username and pass. Yes i tried to make database on my desktop but the SID already exists. i opened EXE app, and typed username and pass, lateron as mughalsking sain i made Export file: EXPDAT.DMP > C:\ENTIRE_DATABASE.DMP. After that on my desktop i opened the Import file: EXPDAT.DMP > C:\ENTIRE_DATABASE.DMP. Pressing default values tables were copied
Re: Copy database [message #545005 is a reply to message #545003] Sun, 26 February 2012 05:37 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You say that when you tried to create a database, you were told that it already exists. OK.
But now you really do have to do as you have been asked: show what you have been doing. What does Quote:
i opened EXE app, and typed username and pass
mean? Show us! Use copy/paste. AndQuote:
i opened the Import file
ditto!

You must copy/paste the results of running the commands from the CMD prompt. Also include the output of running SET.

Please format your copy paste with [code] tags to make it easier to read
Re: Copy database [message #545007 is a reply to message #545005] Sun, 26 February 2012 05:44 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
i wonder import/export database not an issue that much its quite easy to export and import tables or database to another pc or same pc for copying on network u have to provide network path which is something like that
www.dba-oracle.com/oracle_tips_db_copy.htmCached - Similar

and another way to copy db to another server


How do i copy Oracle DB to another server?


For sure you may use Oracle Export utility, but despite this approach works, it has a set of disadvantages that someday forced me to find another (faster) way to move Oracle data around.

Here we go with step-by-step how-to.

Note: you may want to change DB name while copying it to another destination. This case does not change the procedure much and steps required to do so will be specially highlighted in the article.

Also, you may want to change Oracle DBID of the Database. the reason is that Recovery Manager (RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. For changing Oracle DBID for a database please refer to the corresponding HOWTO.

OK. Here we go. We will imagine a situation when you need top copy PRO (production) DB into TST (test) DB on test server...





1. Instance service creation. (If you have instance already created - skip this step)

On Windows platform at destination DB server you will need to create Windows service for the DB instance to wich we will copy our PRO DB.

For this we will use ORADIM Windows utility.


1.oradim -new -sid TST -INTPWD mypassword -STARTMODE AUTO
The command above will create and start Windows service named OracleServiceTST.

Now you need to set ORACLE_SID environment variable to the newly created instance name:


1.set ORACLE_SID=tst
Then you need to specify new instance runtime parameters. I recommend to do it by exporting pfile from source DB instance, modify it and then import it to the destination DB instance:

a) Exporting pfile from source DB.

Connect source DB server in SQLPlus and run:


1.create pfile='d:\PROpfile.txt' from spfile;
b) Modifying pfile.

Look inside PROpfile.txt and modify memory parameters making them more suitable for the destination server environment. If you plan to change locations of the DB files on the destination server, then modify the pathes in PROpfile.txt. Please take care and make sure that pathes you specified are correct and exist.

Pay attention to control_files parameter value.

Also look at the parameter db_name. If you like to rename DB (to form unique globalname), change the value of this parameter to the DB name you like.

c) Importing pfile to the destination DB.

Next step is to create Oracle SPFile out of modified PFILE. At the destination DB Server run (make sure ORACLE_SID environment variable is set to newly created instance name as was mentioned above):


1.sqlplus / as sysdba

1.create spfile from pfile='d:\PROpfile.txt';


Ok. You have created new DB instance service and specified initialization parameters to it. Now it is time to continue.


2. Copying data from source DB server to destination DB server.

Shutdown source DB (and destination DB in case it is running):


1.shutdown immediate;
and copy all datafiles, logfiles and controlfiles from source DB server to the destination DB server. Please make sure that controlfiles are copied into correct destination DB server locations (the controlfiles names and locations are specified in spfile or pfile of the destination DB. See point b) of step 1 in this HowTo for details). Delete old DB files before copying the new ones if you update already existing DB on destination server.

Do not forget to startup source DB back. Smile



3. Changing destination DB name or destination DB datafiles location

If you do not want to change data files location or rename DB, skip this step.

In case you want to change DB name or modify the location of data files, follow this procedure:



a) Backup controlfile from source DB to script:

Connect source DB server in SQLPlus and run:


1.alter database backup controlfile to trace as 'd:\createcontrolfile.sql' resetlogs;


b) Modify controlfile creation script:

Look inside d:\createcontrolfile.sql and modify it, specifying new location of datafiles.

If you want to change database name, modify create controlfile statement adding set keywork before database keyword and changing the DB name.

For example:


1.CREATE CONTROLFILE REUSE SET DATABASE "TST" RESETLOGS NOARCHIVELOG ...


Since you did shutdown source DB before copying the files using shutdown immediate, please delete the line

RECOVER DATABASE USING BACKUP CONTROLFILE;

in createcontrolfile.sql

Please also remove commented and empty lines from the createcontrolfile.sql file to avoid possible errors.



c) Execute modifyied script on the destination DB:

At the destination DB Server run (make sure ORACLE_SID environment variable is set to newly created instance name as was mentioned above):


1.sqlplus / as sysdba

1.@'d:\createcontrolfile.sql';
\If everything went OK and you see no errors, then execute


1.shutdown immediate;


4. Startup destination Db and verify it work OK.

At the destination DB Server run:


1.sqlplus / as sysdba

1.startup;


Congratulation! You have copied DB from one server to another!


thanks
Re: Copy database [message #545009 is a reply to message #545007] Sun, 26 February 2012 05:53 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
because when your using EXP.EXE its asking wheither u want to copy tables or database and user as well like in image

  • Attachment: entire_db.PNG
    (Size: 17.70KB, Downloaded 1135 times)

[Updated on: Sun, 26 February 2012 05:55]

Report message to a moderator

Re: Copy database [message #546969 is a reply to message #545009] Sat, 10 March 2012 15:55 Go to previous message
eddyno
Messages: 20
Registered: January 2012
Location: Beograd
Junior Member
I know it asking and I chooes 1 database,and after that i typed the location of exp.dmp file. Later i inserted the same file, and those pictures i attached dont display. Now i copied database it's ok, but in my sql*plus i dont type host string only Smile), but problem is in oracle forms when i try to loggin it displays target host or object doesnt exists?? Why?

[Updated on: Sun, 11 March 2012 15:38]

Report message to a moderator

Previous Topic: SQL Loader Unable to open file error (2 Merged)
Next Topic: Oracle 9i V/s Oracle 10g Import / Export Problem
Goto Forum:
  


Current Time: Fri Mar 29 10:07:05 CDT 2024