| Clone only structure of 9i to 10g [message #296486] |
Sun, 27 January 2008 23:18  |
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 #296520 is a reply to message #296492] |
Mon, 28 January 2008 01:17  |
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 #296698 is a reply to message #296538] |
Mon, 28 January 2008 13:34 |
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:
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 1195 times)
[Updated on: Mon, 28 January 2008 14:45] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
