[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Copy Oracle 9 db/instance from one machine to another?

Posted on 2008-09-29
12
Medium Priority
?
888 Views
Last Modified: 2013-12-19
I have Oracle 9i Enterprise Edition Release 9.2.0.1.0 installed on two machines running Solaris.  I want to copy a production database from one server to another.  

What is the simplest way to copy the production database over using the same instance name, user name, and password?

The production machine is currently two Oracle instances and the backup machine is running one (unrelated to the new one I want to move over).

Thanks,
Shannon
0
Comment
Question by:shannon_adams
[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
  • 6
  • 5
12 Comments
 

Expert Comment

by:somosnones
ID: 22603776
Hi Shannon,
If you are a beginner I recommend you to use PLSQL Developer
http://www.allroundautomations.com/plsqldev.html

You can export table records using three different methods:
1 Oracle Export
2 SQL Inserts
3 PLSQL Developer export format
I have been using the PLSQL Developer format to copy back and forth between my test and production environments. It is easy and very straightforward.

Furthermore you can export users objects (sequences, views, packages, triggers, etc) to one or to different files.

0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22604756
Install binaries on another server - possibly to same directory and of courese same version (patches etc.).
Make clean shutdown of database.
Copy files to same directories:datafiles, logfiles, controlfiles, initfile.
Set oracle env settings and startup copied database.
You may need to configure listener.
0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22604758
And one more, You mast generate passwordfile.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shannon_adams
ID: 22604912
Thanks for the responses.  How do I generate a password file?
0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22605368
There is utility called orapwd.
0
 

Author Comment

by:shannon_adams
ID: 22611335
It looks like I am going to have a different directory structure on the test server for the database than where it is located on the production server.  The test server does not have enough space to hold the database in the same file structure.  

So, I am going to export the database on the production server, ftp it to the test server, create a new database and tablespaces on the new server, then run the import.

My question is how do I create the same database and tablespaces using the same space options and extents as are on the production.  I assume I can run a command to get this info on the production server and use the same values when creating the new database to import into?
0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22612229
You can create oryginal structure as softlinks to directories on different filestystems.
You can also startup database and rename files.

You can use Oracle Enterprise Manager (Storage Manager) to generate scripts for tablespaces.
0
 

Author Comment

by:shannon_adams
ID: 22612843
KICUSek - can you give me an example of creating the original structure as softlinks to directories on different file systems?  Also, how do I rename the files after db startup?  Sorry for the beginner questions, I am still learning...
0
 
LVL 4

Accepted Solution

by:
KICUSek earned 2000 total points
ID: 22612972
oryginal foleder with data to be copied: /oracle/data/prod
folder on destination used to hlod that data: /oracle/database/prod

so on destination host you will cat create /oracle/data softlink to /oracle/database folder:
ln -s /oracle/database  /oracle/data
now: ls /oracle/data will show you content of /oracle/database

as to the renaming:
when you try to startup database you will get error that there is no file and name of this file folows
when you do: alter database rename file 'oryginal_file_name' to 'new_file_name';
then you do: alter database open and again missing file error with name
all you mast do is to rename all files and at the end you will get database open :)
0
 

Author Comment

by:shannon_adams
ID: 22617150
KICUSek-

I think I am getting closer.  See if you can follow what I have done here...

Database files were in /export/home/oracle/OraHome1/oradata/edi on production server.  They are now in /db/oracle/OraHome1/oradata/edi on the test server.  I created a symbolic link on the test server to match the path were the files were stored on the prod server:
ln -s /db/oracle/OraHome1/oradata/edi /export/home/oracle/OraHome1/oradata

tnsnames.ora and oratab were configured on test server to match settings of prod server (except IP addresses, of course).

/var/opt/oracle/oratab:
edi:/export/home/oracle/OraHome1:Y

/export/home/oracle/OraHome1/network/admin/tnsnames.ora:
SPI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.110.220)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = spi)
    )
  )

EDI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.110.220)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = edi)
    )
  )



Set vars:
ORACLE_SID=edi;export ORACLE_SID
ORACLE_HOME=/export/home/oracle/OraHome1; export ORACLE_HOME
PATH=$PATH:/usr/opt/OraClient/10.2.0/client_1/bin;export PATH

Try to connect:
sqlplus '/ as sysdba'

returns:
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 1 13:46:54 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12546: TNS:permission denied

Any ideas?
0
 

Author Comment

by:shannon_adams
ID: 22617270
Oops, forgot to add that I set  listener.ora:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.110.220)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /export/home/oracle/OraHome1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = spi)
      (ORACLE_HOME = /export/home/oracle/OraHome1)
      (SID_NAME = spi)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = edi)
      (ORACLE_HOME = /export/home/oracle/OraHome1)
      (SID_NAME = edi)
    )

  )

Still getting the same error when connecting:  ORA-12546: TNS:permission denied
0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22617860
I'd expect:
PATH=$PATH:/export/home/oracle/OraHome1/bin;export PATH
to use server binaries
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

650 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