Solved

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

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

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL - Query help 7 68
make null the repeated levels 2 31
Oracle create type table from existing table%rowtype ? 6 38
Loading flat file data in tables 2 42
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…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

828 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