1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to copy the Oracle database structure to new server?

Discussion in 'Security, Backup and Recovery' started by momer79, Jan 8, 2010.

  1. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    I am using Oracle 9i on development server and want to copy the all database structure to the new oracle server.

    Both server are using oracle 9i and window server.

    On development server If i run following query then it brings 19 rows.
    - select * from dba_data_files order by tablespace_name;

    And this query returns 257 rows.
    select * from sys.v_$parameter;

    Is there a way to just copy a database structure (without data) to new server? instead of manually defining all parameters, storage space and etc?

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Certainly, you'll use exp with the parameters full=y and rows=n which will copy all of the database structures without any data. Of course this won't generate an init.ora file so you will need to copy that to your new server. Also you'll need to use the oradim.exe utility to create a service on the new machine so Oracle wll be able to start and run since this is Windows. You should really read the following information on cloning a database:

    http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmdupdb.htm#RCMUG012

    The procedure uses RMAN to accomplish the task and it does retain all of the data from the source database. It might be easier on you to do a full export with rows=n and use the same scripts and parameter file you used on the source database to create a 'blank' database on the destination server to house this data-less copy. Then import the structure-only export and you have your desired copy.
     
  3. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    For using exporting method , do i need to create database and tables in Testing server to import everything?

    What if copy Datafiles, Control Files and SPFILE from live server ( Live server is up and running ) to test server and then startup the test server? would this method copy everything to test server?

    Thanks
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes.

    You're running on Windows so this isn't as easy as doing this on UNIX/Linux. Read the RMAN information I gave you at the link from my last post; it s the most reliable method for you to use.
     
  5. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    I thought that export will create everything on testing and i won't have to create tables on testing server but it seems like i still have to create tables manually? (There are about 100 tables in 17 Schemas)

    I am missing something here i think....What kind of files export(full) will generate?

    Thanks
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A full export will create tablespaces, users, roles, profiles, indexes, in fact it will create all user-owner objects if you export as SYS or SYSTEM and import with the same user used for the export. Of course any object that exists may cause the import to fail which is why one also uses ignore=y on such imports. Since you won't post what you've done and the results of that work there isn't much one can do to assist you. Post what you've done and why it wasn't successful and someone may be able to help.
     
  7. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi Zargon,

    Thanks for your reply.

    Before i go ahead and do full export i have two quick questions.

    You said that "A full export will create tablespaces, users, roles, profiles, indexes, in fact it will create all user-owner objects "

    1- Does it include tables as well?

    2- Is there any good document for full export and do i need to shut down live server for full export?

    Thanks
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, import creates tables.

    You cannot export the database if it isn't running.
     
  9. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    I am getting following error message after copying all necessary files to testing server.

    ORA-01092 Oracle instance determinated.

    I copied following folder and files to testing server:

    Oracle/ora92/database
    Oracle/Oradata/ORCL
    PWDORCL.ORA
    ORCLCORE.LOG

    Content from alert_orcl.log file

    ---------------------------------------------
    Mon Jan 18 14:04:43 2010
    /* OracleOEM */ ALTER DATABASE OPEN
    Mon Jan 18 14:04:43 2010
    Beginning crash recovery of 1 threads
    Mon Jan 18 14:04:43 2010
    Started first pass scan
    Mon Jan 18 14:04:44 2010
    Completed first pass scan
    63 redo blocks read, 4 data blocks need recovery
    Mon Jan 18 14:04:44 2010
    Started recovery at
    Thread 1: logseq 5750, block 3, scn 0.0
    Recovery of Online Redo Log: Thread 1 Group 6 Seq 5750 Reading mem 0
    Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO06.LOG
    Mon Jan 18 14:04:44 2010
    Ended recovery at
    Thread 1: logseq 5750, block 66, scn 1395.753385779
    4 data blocks read, 4 data blocks written, 63 redo blocks read
    Crash recovery completed successfully
    Mon Jan 18 14:04:44 2010
    LGWR: Primary database is in CLUSTER CONSISTENT mode
    Thread 1 advanced to log sequence 5751
    Thread 1 opened at log sequence 5751
    Current log# 2 seq# 5751 mem# 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
    Successful open of redo thread 1.
    Mon Jan 18 14:04:45 2010
    SMON: enabling cache recovery
    Mon Jan 18 14:04:45 2010
    ARC0: Evaluating archive log 6 thread 1 sequence 5750
    ARC0: Beginning to archive log 6 thread 1 sequence 5750
    Creating archive destination LOG_ARCHIVE_DEST_2: 'E:\ORACLE_DUPLEX\ARC05750.001'
    Creating archive destination LOG_ARCHIVE_DEST_1: 'D:\ORACLE_DUPLEX\ARC05750.001'
    ARC0: Completed archiving log 6 thread 1 sequence 5750
    Mon Jan 18 14:04:45 2010
    Undo Segment 1 Onlined
    Undo Segment 2 Onlined
    Undo Segment 3 Onlined
    Undo Segment 4 Onlined
    Undo Segment 5 Onlined
    Undo Segment 6 Onlined
    Undo Segment 7 Onlined
    Undo Segment 8 Onlined
    Undo Segment 9 Onlined
    Undo Segment 10 Onlined
    Successfully onlined Undo Tablespace 1.
    Mon Jan 18 14:04:45 2010
    SMON: enabling tx recovery
    Mon Jan 18 14:04:45 2010
    Database Characterset is WE8MSWIN1252
    Mon Jan 18 14:04:46 2010
    Errors in file d:\oracle\admin\orcl\bdump\orcl_smon_3804.trc:
    ORA-00600: internal error code, arguments: [4193], [12650], [128], [], [], [], [], []

    Mon Jan 18 14:04:47 2010
    Errors in file d:\oracle\admin\orcl\udump\orcl_ora_1932.trc:
    ORA-00600: internal error code, arguments: [4193], [31281], [139], [], [], [], [], []

    Mon Jan 18 14:08:24 2010
    Recovery of Online Redo Log: Thread 1 Group 2 Seq 5751 Reading mem 0
    Mon Jan 18 14:08:24 2010
    Recovery of Online Redo Log: Thread 1 Group 2 Seq 5751 Reading mem 0
    Mon Jan 18 14:08:24 2010
    Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
    Mon Jan 18 14:08:25 2010
    Recovery of Online Redo Log: Thread 1 Group 2 Seq 5751 Reading mem 0
    Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
    Mon Jan 18 14:08:25 2010
    Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
    Mon Jan 18 14:08:26 2010
    Errors in file d:\oracle\admin\orcl\bdump\orcl_smon_3804.trc:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00607: Internal error occurred while making a change to a data block
    ORA-00600: internal error code, arguments: [4193], [12650], [128], [], [], [], [], []

    Mon Jan 18 14:08:26 2010
    Recovery of Online Redo Log: Thread 1 Group 2 Seq 5751 Reading mem 0
    Mem# 0 errs 0: E:\ORACLE\ORADATA\ORCL\REDO02.LOG
    Mon Jan 18 14:08:27 2010
    Errors in file d:\oracle\admin\orcl\udump\orcl_ora_1932.trc:
    ORA-00607: Internal error occurred while making a change to a data block
    ORA-00600: internal error code, arguments: [4193], [31281], [139], [], [], [], [], []

    Error 607 happened during db open, shutting down database
    USER: terminating instance due to error 607
    Mon Jan 18 14:08:27 2010
    Errors in file d:\oracle\admin\orcl\bdump\orcl_pmon_1700.trc:
    ORA-00607: Internal error occurred while making a change to a data block

    Instance terminated by USER, pid = 1932
    ORA-1092 signalled during: /* OracleOEM */ ALTER DATABASE OPEN ...
    ---------------------------

    Any idea?
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, you have not copied everything you need to for this clone which is why I told you to use RMAN to duplicate this database. I'll repeat that advice:

    Use RMAN to duplicate this database onto another server

    Please read here:

    http://download.oracle.com/docs/cd/B10501_01/server.920/a96566/rcmdupdb.htm#441820

    and follow the instructions to successfully copy your database to a new server.
     
  11. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    Somehow i can't use RMAN at the moment and its not in the requirments i have been given.

    I have to copy the files from server to server Not through RMAN or Export.

    Could you tell me what files i have missed after looking at error message?

    Can i use this cloning technique on window server?

    http://www.dba-oracle.com/oracle_tips_db_copy.htm

    Thanks
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, you cannot. You CAN try this:

    http://coskan.wordpress.com/2007/04...s-without-dbca-or-rman-clone-or-rman-restore/
     
  13. momer79

    momer79 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi Zargon,

    Its working perfectly fine. I think first time i didn't transfer the files correctly, later i tried again and its working fine.

    I would say this method is quickest then RMAN or Import/Export.

    Thanks