Home » RDBMS Server » Backup & Recovery » Clone only structure of 9i to 10g
Clone only structure of 9i to 10g [message #296486] Sun, 27 January 2008 23:18 Go to next message
DrNeko
Messages: 17
Registered: January 2007
Location: NJ
Junior Member
Hello,
Is there a way to clone the structure/metadata of a 9i database (Windows 2000 Server) to a remote server that's 10g (Windows 2003 R2 Standard x64 Edition) ? I'm only interested in the structure/metadata since the table data in 9i are dropped daily and reimported. There's a lot of users, roles, db links, etc, that I want to transport into the 10g database. I've tried exp/imp, and I got a lot of errors. I was suggested to use RMAN, but I don't know how to use RMAN just to backup the structure/metadata of 9i, especially since the directory structure for the datafiles of the new server (10g) is different than the old server (we have a dedicated hard drive ready just for the datafiles in the new server). Also, I would like to increase the db size in the 10g server to 8K (the 9i has db block size of 4K). Any way to do that with RMAN? Thanks.
Re: Clone only structure of 9i to 10g [message #296492 is a reply to message #296486] Sun, 27 January 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
export/import with "rows=n"

Regards
Michel
Re: Clone only structure of 9i to 10g [message #296520 is a reply to message #296492] Mon, 28 January 2008 01:17 Go to previous messageGo to next message
DrNeko
Messages: 17
Registered: January 2007
Location: NJ
Junior Member
Thanks for the quick response. I've tried something like that, but I received errors. Here's what I typed in the 9i server:
exp system/******** full=y direct=y constraints=y indexes=y file=C:\backup\expimp\worows\full.dmp 
log=C:\backup\expimp\worows\log.txt rows=n;

I've copied the full.dmp file (only 10MB) to the 10g server. From the 10g server, I've used the imp command and got the following error:
Export file created by EXPORT:V09.00.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE UNDO TABLESPACE "UNDOTBS" BLOCKSIZE 4096 DATAFILE 'C:\ORACLE\ORADAT"
"A\INFOTEST\UNDOTBS01.DBF' SIZE 209715200 AUTOEXTEND ON NEXT 5242880 "
"MAXSIZE 16383M EXTENT MANAGEMENT LOCAL "
IMP-00003: ORACLE error 29339 encountered
ORA-29339: tablespace block size 4096 does not match configured block sizes
IMP-00017: following statement failed with ORACLE error 29339:
"CREATE TABLESPACE "CWMLITE" BLOCKSIZE 4096 DATAFILE 'C:\ORACLE\ORADATA\INF"
"OTEST\CWMLITE01.DBF' SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE"
" 16383M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT "

I also got a few other errors, mainly about it can't import some objects since they already exist in the 10g server (I created a new, blank database during 10g installation). I've created the "C:\ORACLE\ORADATA\INFOTEST\" directory on the 10g server before importing, and I don't know how to get around the block size issue (the block size in 9i is 4K, and I want to increase it to 8K in the 10g server). How can I get around all of this?

Some of the instructions I've read online are vague. Do I need to create a database on the 10g server before I use the imp command, or only just install the 10g software? I can drop the database I've created in 10g if I need to (it's only a test box). I can't upgrade the 9i server since it's a live production server. Any ideas? Thank you.

[Updated on: Mon, 28 January 2008 02:19] by Moderator

Report message to a moderator

Re: Clone only structure of 9i to 10g [message #296538 is a reply to message #296520] Mon, 28 January 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I also got a few other errors, mainly about it can't import some objects since they already exist in the 10g server

These are just warnings.

Quote:
I don't know how to get around the block size issue

Just precreate the tablespaces and ignore the message (or use ignore=y)

Regards
Michel
Re: Clone only structure of 9i to 10g [message #296698 is a reply to message #296538] Mon, 28 January 2008 13:34 Go to previous message
DrNeko
Messages: 17
Registered: January 2007
Location: NJ
Junior Member
Hello,
I gave it another try, and I think it went ok. I'm still a bit concerned about the outcome, so I logged everything. Could you kindly look over the logs in case I did something wrong? Let me break it down on what I've done this time:

In the 9i server, I've typed the following:
exp system/******** full=y direct=y constraints=y indexes=y file=C:\backup\expimp\worows\full.dmp 
log=C:\backup\expimp\worows\log.txt rows=n;


I've transferred the full.dmp file to the 10g server. From the 10g server, I've created the necessary tablespaces, then ran the following command:
imp system/******** full=y constraints=y indexes=y file=C:\backup\imptest\worows\full.dmp log=C:\backup\imptest\worows\log.txt rows=n;


The result of the log is in imp.log. I've checked for any invalid objects, and executed the following statement:
SELECT object_name FROM dba_objects WHERE status = 'INVALID';


I recompiled the objects by executing:
SQL>@utlprp.sql 0


About 150 objects remained invalid. I then executed the following statement:
select obj#, compile_err from utl_recomp_errors;


They were mostly synonym translation is no longer valid errors (seen in 01.txt), so I used a script to remove public synonyms and recompiled again (script and result in 02.txt). I then execute the following:

SQL> select obj#, compile_err from UTL_RECOMP_ERRORS
  2  WHERE (compile_err not like '%ORA-04045%' OR
  3  compile_err not like '%ORA-00980%');

no rows selected


There still remain 131 invalid objects, as seen in 03.txt. Should I safely ignore these invalid objects, as well as the errors in imp.log? Finally, I've excerpt some of the errors from imp.log that caught my eye and put them in concern_errors.log. Should I ignore those as well, or are there any additional steps I should follow? Thank you.
  • Attachment: logs.zip
    (Size: 136.93KB, Downloaded 228 times)

[Updated on: Mon, 28 January 2008 14:45]

Report message to a moderator

Previous Topic: RMAN parallelism error
Next Topic: rman original files to new host
Goto Forum:
  


Current Time: Sun Dec 11 00:36:29 CST 2016

Total time taken to generate the page: 0.08793 seconds