URGENT:-XDB Schema [message #148190] |
Wed, 23 November 2005 00:53 |
milind_sri
Messages: 70 Registered: February 2005 Location: Pune
|
Member |
|
|
Hi Gurus,
I have a problem that goes like this. My senior sent me the recent dumps of our database from production. He told me that dont drop the tablespaces and users in order to save time of re-creation. He told me told me to drop the objects of individual users. Now, i have dropped the objects of XDB oracle's default schema which is required for XML support functions.
How to export the XDB schema from another database with the same version and re-store in the present database. Whenever I try to take the export of XDB user from other database I get error user does not exist. I have checked in the console it is locked.
Now, how do i restore the XDB schema. Please provide your feedback on the same as early as possible.
Thanks in Advance.
Milind.
|
|
|
Re: URGENT:-XDB Schema [message #148276 is a reply to message #148190] |
Wed, 23 November 2005 06:36 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
>> dont drop the tablespaces and users in order to save time of re-creation.
>> drop the objects of individual users.
How long will it take to create a user / tablespace? Is your XDB tablespace in terabytes?
Anyhow you have the dropped the tables/indexes / whatever. So what is the whole idea?
The proper method to uninstall XDB is to run catnoqm.sql and to install again , run catqm.sql and catxdbj.sql with proper parameters
(catqm.sql < password > < default tablespace > < tempspace > )
Please look here for precise information.
http://www.adp-gmbh.ch/ora/xml_db/install.html
[Updated on: Wed, 23 November 2005 10:36] Report message to a moderator
|
|
|
|
|
Re: URGENT:-XDB Schema [message #148891 is a reply to message #148320] |
Mon, 28 November 2005 21:43 |
milind_sri
Messages: 70 Registered: February 2005 Location: Pune
|
Member |
|
|
Hi Mahesh,
While taking export of XDB user i am getting this error. I dont understand how to take the export of XDB and import it again in another database.
C:\>exp system/system@idbr11arrow owner=XDB file=C:\XDB.dmp log=C:\XDB.log
Connected to: Oracle9i Release 9.2.0.5.0 - Production
JServer Release 9.2.0.5.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified users ...
EXP-00010: XDB is not a valid username
Export terminated successfully with warnings.
Please, advise me on this so that I can export XDB and import it in another database.
Thanks in advance.
Milind.
|
|
|
|
|
Re: URGENT:-XDB Schema [message #149367 is a reply to message #149287] |
Thu, 01 December 2005 06:36 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I overlooked something before posting the last response.
Several posts above , i said
>>The proper method to uninstall XDB is to run catnoqm.sql and to install again , run catqm.sql
That holds true.
The metadata cannot be exported.
quoting docs
Quote: | Metadata in Repository is Not Exported During a Full Database Export
Oracle XML DB stores the metadata (and the non-schema data) for the Repository in the XML DB database user schema. Since Oracle does not support the Repository structure to be exported, these metadata tables and structures will not be exported when doing a full database export.
In fact the entire XML DB user schema will be skipped during a full database export and any database objects owned by XML DB ("XDB") will not be exported.
|
--
-- Let us see how far this is a fact.
--
scott@9i > create user XDB identified by test;
User created.
scott@9i > !exp scott/tiger owner=XDB
Export: Release 9.2.0.4.0 - Production on Thu Dec 1 07:28:44 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
EXP-00010: XDB is not a valid username
Export terminated successfully with warnings.
--
-- Why is this behaviour?
-- a quick look into catexp.sql , explains it.
-- You can see several users are specifically excluded from being exported.
680 CREATE OR REPLACE VIEW exu8usr (
681 name, userid, passwd, defrole, datats, tempts, profile#,
682 profname, astatus, ext_username) AS
683 SELECT u.name, u.user#, DECODE(u.password, 'N', '', u.password),
684 DECODE(u.defrole, 0, 'N', 1, 'A', 2, 'L', 3, 'E', 'X'),
685 ts1.name, ts2.name, u.resource$, p.name, u.astatus,
686 u.ext_username
687 FROM sys.user$ u, sys.ts$ ts1, sys.ts$ ts2, sys.profname$ p
688 WHERE u.datats# = ts1.ts# AND
689 u.tempts# = ts2.ts# AND
690 u.type# = 1 AND
691 u.resource$ = p.profile# AND
692 u.name NOT IN ('ORDSYS', 'MDSYS', 'CTXSYS', 'ORDPLUGINS',
693 'LBACSYS', 'XDB')
694 /
|
|
|
Re: URGENT:-XDB Schema [message #149377 is a reply to message #149367] |
Thu, 01 December 2005 06:54 |
milind_sri
Messages: 70 Registered: February 2005 Location: Pune
|
Member |
|
|
Hi Mahesh,
I got your point but while executing catqm.sql i am getting errors which i have already posted on the site. You can have a look at the logfile from my previous post.
Please give your suggestions on how to solve these errors.
Thanks in Advance.
Milind.
|
|
|
|
|
|
|