Home » RDBMS Server » Server Utilities » URGENT:-XDB Schema
URGENT:-XDB Schema [message #148190] Wed, 23 November 2005 00:53 Go to next message
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 #148320 is a reply to message #148190] Wed, 23 November 2005 10:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>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.

And could you also post what you did? Please Post your session.
Even when a user is locked, you can do the export.
scott@9i > alter user scott account lock;

User altered.

scott@9i > select username,account_status from dba_users where username='SCOTT';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT                          LOCKED

oracle@mutation#exp system/manager owner=scott

Export: Release 9.2.0.4.0 - Production on Wed Nov 23 11:43:40 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 ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                        MYTABLE          3 rows exported
. . exporting table                     PLAN_TABLE          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

Re: URGENT:-XDB Schema [message #148388 is a reply to message #148320] Wed, 23 November 2005 22:29 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Thanks a lot Mahesh for your feedback.

It worked.

Milind.
Re: URGENT:-XDB Schema [message #148891 is a reply to message #148320] Mon, 28 November 2005 21:43 Go to previous messageGo to next message
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 #148974 is a reply to message #148891] Tue, 29 November 2005 06:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Make sure user XDB exist in your database.
Re: URGENT:-XDB Schema [message #149287 is a reply to message #148974] Thu, 01 December 2005 01:02 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Hi Mahesh,

XDB user exists in the database and i am sure about that.

Thanks

Milind.
Re: URGENT:-XDB Schema [message #149367 is a reply to message #149287] Thu, 01 December 2005 06:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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.
Re: URGENT:-XDB Schema [message #149379 is a reply to message #149377] Thu, 01 December 2005 06:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> i am getting errors which i have already posted on the site
Where?
I am not seeing it.
Re: URGENT:-XDB Schema [message #149458 is a reply to message #149379] Thu, 01 December 2005 22:09 Go to previous messageGo to next message
milind_sri
Messages: 70
Registered: February 2005
Location: Pune
Member
Hi Mahesh,

I am sorry. I have posted the log file in another post on XML forum on this site.

I am uploading the log once again. Please, let me know how to resolve these errrors which i am getting in the logfile as now it is becoming a big issue.

I need your help.

Thanks in advance.

Milind.
  • Attachment: catqm.txt
    (Size: 34.68KB, Downloaded 2027 times)
Re: URGENT:-XDB Schema [message #149628 is a reply to message #149458] Fri, 02 December 2005 09:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Your environment is pretty much messed up.
I cannot comment on anything, at this stage.
Please try this.
run catnoqm.sql ( as recomended in the link i provided).
Do clean UNINSTALL.
and run to install again.
and if same error comes again.
Quote:

call xdb.dbms_xdbz0.initXDBSecurity()
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Look into alertlog.
there should be related errors for this.
Re: URGENT:-XDB Schema [message #460096 is a reply to message #148276] Wed, 09 June 2010 10:35 Go to previous message
MarkusW
Messages: 20
Registered: June 2009
Location: Austria
Junior Member
Works fine!

www.adp-gmbh.ch/ora/xml_db/install.html

Markus
Previous Topic: DBMS_DATAPUMP.metadata_filter + long table list
Next Topic: Export/Import using unix pipe
Goto Forum:
  


Current Time: Thu Mar 28 13:47:20 CDT 2024