Solved

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

Posted on 2008-09-29
12
862 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.

Join & Write a Comment

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

759 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

20 Experts available now in Live!

Get 1:1 Help Now