?
Solved

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

Posted on 2008-09-29
12
Medium Priority
?
878 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 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