Results 1 to 10 of 10

Thread: How to copy an Oracle database to another server

  1. #1
    Join Date
    Jan 2004
    Posts
    8

    How to copy an Oracle database to another server

    Hi,
    I would like to set up a test environment for an Oracle 9i database. What is the easiest way to copy the database to the test server? Please note that the production server has different hard drive structure. I.E, the production server has data files on c, d, e, f drives but there is only a C drive on test server.

    Thanks in advance,

    Celia

  2. #2
    Join Date
    Jan 2004
    Posts
    8

    Lightbulb

    You can copy the datafiles to test server and recreate the control files with the path name you want. Let me know if you still have questions.

  3. #3
    Join Date
    Jan 2004
    Location
    Somewhere on earth
    Posts
    3
    What datafiles? what are there extentions and where would these files be found on the oracle folder?

  4. #4
    Join Date
    Jan 2004
    Posts
    8

    Thumbs up

    You can look at dba_data_files to see all the datfiles in the datadbase with its current location. You need to shutdown the existing database or keep the tablespaces in backup mode (you need to be in archive log mode to do this) and copy all these files to the new server. Note down the location of the files you copied on the new test server.

    2)startup the existing database.

    3)at the existing database
    sql> alter database backup control file to trace.

    4)go to udump and collect the trace file edit it and add th enew datafile locations.

    5)stratup nomount the new database.

    6) craete control file from the trace file.

    7) Recover or open the new database.

  5. #5
    Join Date
    Jan 2004
    Location
    San Antonio, TX
    Posts
    3
    To see where your datafiles are, go to SQLPLUS: select file_name, maxbytes,user_bytes,tablespace_name
    from dba_data_files ;

    This will give the tablespace, the size allocated, the size used, and the location of datafile that holds the data for the tablespace(S)in your database.

    For the test server question, I'm going to make the assumption that an Oracle Instance is currenly on the server.

    1. To make test look as close as possible to production, I would create tablespaces with the same name as production linking them to datafiles with simular names on the test server.
    Ex. create tablespace noname
    datafile 'C:\oracle\oradata\name01.dbf'
    extent management local
    uniform size 64k;

    2. Create a user schema in test db
    Ex.
    create user xyz identified by xyz123
    default tablespace spacename
    temporary tablespace temp
    ;
    ...

    3. Perform an export of prod
    Ex.
    exp scott/tiger file=MyExportFile.dat Ful=y;

    4. Copy file to test server and perform an Import. I prefer the fromuser touser scenario.
    EX. imp system/manager
    file=MyExportFile.dat fromuser=scott touser=xyz;

    hope it helps.

  6. #6
    Join Date
    Jan 2004
    Location
    Somewhere on earth
    Posts
    3
    Thanks for the quick answer dgrif0 6, greatly appreciated. Got a another question. I have Oracle 8.0.X.X running on my home computer. What do I need to do to host the database to all the computers on my local network? I know I need to have SQLNET or something like that. Does SQLNET come with ORACLE ENTERPRICE 8.0.x.x?

  7. #7
    Join Date
    Jan 2004
    Location
    San Antonio, TX
    Posts
    3
    No prob.....

    SQLNET is packaged in Oracle 8i, so Install Oracle Enterprise software on your host computer; config your listner, tnsnames, and sqlnet.ora files. This can be achieve through the DB config Assistance tool that is packaged as well. More specific to your question, the SQLNET entries should be simular to the following:

    # SQLNET.ORA Network Configuration File: # C:\oracle\ora92\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES)
    # tells client to attempt a connect through
    # the TNSNAMES.ora file

    # if you are using an LDAP Server, then
    NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES)
    # tells client to attempt a connection through
    # LDAP first

    Install the client on every node machine you want to reach the host machine from;

    On the client(s) set SQLNET simular to the following:

    # SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora
    # Generated by Oracle configuration tools.

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES)# Should reflect # host's SQLNET

    SQLNET.EXPIRE_TIME= 5 # Dead Connection Wait # time


    I think that should do it....


    If anyone has any additional comments, please chime in.


    Thanks

  8. #8
    Join Date
    Jan 2004
    Posts
    8

    failed to create control file

    Thanks for the advice to copy Oracle database.
    However when trying to create a control file, I had error 01161. Seems that the new database on the test server will have to have exact db_name and global database name as the production database. So my question is will this have any impact on the production database at all? I mean any naming conflict?

  9. #9
    Join Date
    Jan 2004
    Location
    San Antonio, TX
    Posts
    3
    C,

    If you are creating a schema on an existing DB, then you do not have to create control files. They already established. When the new Tablespace(s) and schema are created on test, simple export from product and import on test fromuser/touser method.


    If you are trying to create a new database and not just create an user account/ schema on the existing, then do the following:

    1. Create your new database the same size as your original database with a different SID and database name.
    (use OEM -- this help u set up the basic file structure for the database)

    2. When the new database has been built successfully including adequatly sized private rollback segments, create your users.
    3. On the original database perform a FULL database EXPORT.
    4. New database do a FULL database IMPORT.

    Hope this helps.

  10. #10
    Join Date
    Jan 2004
    Location
    Somewhere on earth
    Posts
    3
    Hello dgrif0 6,

    Thanks for your quick response on the SQLNET question. I have another if you don't mind. I am getting the following error when I try to log into the host database from my client machine or when I run tnsping80.exe on the client machine.

    TNS-12571: TNSacket writer failure

    Any ideas?, please respond.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •