Solved

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

Posted on 2008-09-29
12
863 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
  • 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
Oracle -- identify blocking session 24 41
VB.Net - CSV to Oracle table 4 51
Oracle query output question 4 36
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now