How to copy the Oracle database structure to new server?

[momer79]

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

[zargon]

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

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/B...b.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.

[momer79]

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

[zargon]

Hi,

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

Yes.

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

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.

[momer79]

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

[zargon]

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

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.

[momer79]

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

[zargon]

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

Yes, import creates tables.

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

[momer79]

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?

[zargon]

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?

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/B...pdb.htm#441820

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