Home » RDBMS Server » Server Utilities » Oracle documentation regarding full database export import operation
Oracle documentation regarding full database export import operation [message #191693] Thu, 07 September 2006 15:26 Go to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
In reading Oracle documentation in preparing to do a full export and import of a database to a duplicate database (there are other possibly preferred ways such as RMAN, but I have never tried the export import method), I came across the following comment in the Utilities Guide.

This is the 10gR2 database and documentation set, utilities guide chapter 19.

Points to Consider for Full Database Exports and Imports
A full database export and import can be a good way to replicate or clean up a database. However, to avoid problems be sure to keep the following points in mind:

Quote:

A full export does not export triggers owned by schema SYS. You must manually re-create SYS triggers either before or after the full import. Oracle recommends that you re-create them after the import in case they define actions that would impede progress of the import.


Is this true? Has anyone experienced this? If so, how would one recreate the triggers? No further mention of this issue is made in the Utilities Guide or in the Upgrade Guide (which in chapter 8 gives a list of procedures for upgrading a database using the export import method).
Re: Oracle documentation regarding full database export import operation [message #191700 is a reply to message #191693] Thu, 07 September 2006 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
By using SQL like CREATE TRIGGER .......
Re: Oracle documentation regarding full database export import operation [message #191701 is a reply to message #191700] Thu, 07 September 2006 17:11 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Well....

You should NEVER create anything under SYS anyway!

SYS is for the database.
SYS is speciel.

Do not use SYS for Your own data, objects etc.

Have fun
Kim
Re: Oracle documentation regarding full database export import operation [message #191872 is a reply to message #191693] Fri, 08 September 2006 07:54 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Right. I agree.

But it isn't that I'm worried about my own objects or triggers. I'm worried about any oracle created objects in the sys schema not transferring over.
Re: Oracle documentation regarding full database export import operation [message #191876 is a reply to message #191872] Fri, 08 September 2006 08:02 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
You should not worry.

Say You have two running database, A and B.

Sys's objects in A has information only relevant for A, and B for B.

The data in fx. sys.v$data_file should not be overwritten when You import. Bad example, but whould database B then tri to access A's datafiles?

(Yes, I know of control files, it's just a quick and dirty thought of example)


Do not worry about SYS. Oracle handles it perfectly!

,-)
Kim
Re: Oracle documentation regarding full database export import operation [message #191920 is a reply to message #191693] Fri, 08 September 2006 11:57 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hmm...I still have doubts, based on the statement in the oracle docs quoted above. I have not done much of anything in the below database since installing it, but if I do:

MYDBA@orcl > select count(*) from dba_triggers where owner = 'SYS';

  COUNT(*)
----------
        13

1 row selected.

MYDBA@orcl > select trigger_name from dba_triggers where owner = 'SYS';

TRIGGER_NAME
------------------------------
AW_DROP_TRG
NO_VM_DROP
NO_VM_DROP_A
NO_VM_CREATE
NO_VM_ALTER
AURORA$SERVER$STARTUP
AURORA$SERVER$SHUTDOWN
CDC_ALTER_CTABLE_BEFORE
CDC_CREATE_CTABLE_AFTER
CDC_CREATE_CTABLE_BEFORE
CDC_DROP_CTABLE_BEFORE
OLAPISTARTUPTRIGGER
OLAPISHUTDOWNTRIGGER

13 rows selected.

MYDBA@orcl >


There are definitely triggers there. But the docs explicitly say, or so it seems to me, that these 13 triggers will not be exported. So I need to find out if this is true, and if so, how to correct it. If not though, then I'll ignore it as a documentation bug.

Re: Oracle documentation regarding full database export import operation [message #191924 is a reply to message #191920] Fri, 08 September 2006 12:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Methinks, this time it is not a bug. Those triggers you are seeing are used by the default options (like JVM features) you are using.
If my memory is not failing on me, it is the same from 8.1.6 ( in 8.1.6 the utilities manual had another bug, which stated a export parameter TRIGGER=Y/N which does not exist acatually).
A simple method to test is (If you can afford to do)
1. do a full database export (with rows=n)
2. do a full import with show=y log=somelogfile
3. check the logfile.

I did and as stated in docset, SYS triggers are not exported.
--
-- From Source database.
dbadmin@dubbel_primary > SELECT TRIGGER_NAME,OWNER from dba_triggers;

TRIGGER_NAME                   OWNER
------------------------------ ------------------------------
DEF$_PROPAGATOR_TRIG           SYSTEM
REPCATLOGTRIG                  SYSTEM
bi_BASE_DATA1                  ANTIBODY
MYTRIGGER                      DBADMIN
AW_DROP_TRG                    SYS
NO_VM_DROP                     SYS
NO_VM_DROP_A                   SYS
NO_VM_CREATE                   SYS
NO_VM_ALTER                    SYS

9 rows selected.

--
-- Just a quick grep through the log file.
-- An attempt to recreate all triggers execpt the ones
-- owned by SYS is shown
-- DBADMIN and ANTIBODY are database users.
--
oracle@dubbel:~> cat full.log | grep "CREATE TRIGGER"
 "GRANT CREATE TRIGGER TO "RECOVERY_CATALOG_OWNER""
 "CREATE TRIGGER "SYSTEM".def$_propagator_trig"
 "CREATE TRIGGER "SYSTEM".repcatlogtrig"
 "CREATE TRIGGER "DBADMIN".mytrigger"
 "CREATE TRIGGER "ANTIBODY"."bi_BASE_DATA1" "
Re: Oracle documentation regarding full database export import operation [message #191933 is a reply to message #191920] Fri, 08 September 2006 14:44 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Sure, there are triggers there.
And that should there also be in Your other database.

This still does not mean that You should transfer these.¨

The Oracle docs just explains that if You did create any in SYS, they will not be exported.

Smile Do not go looking for troubles, when all is ok. Export is an ancient technique of Oracle. It has been working for ages!

Br
Kim
Re: Oracle documentation regarding full database export import operation [message #191934 is a reply to message #191693] Fri, 08 September 2006 14:46 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
My thoughts exactly on how to test it. What version was your test done in Mahesh?

I did the test in 10gR2 (10.2.0.1) on Windows and searched for 4 of the 13 triggers and found none of them in the import log file with the show option. My export was done with sys as sysdba, likewise with the import.

So..lets say you were doing the export import method to upgrade a database, or to duplicate a database for a test environment. How would one go about recreating these triggers? What would be the best way?

I wonder if there is anything else that does not get exported in a "full" export.

[Updated on: Fri, 08 September 2006 14:49]

Report message to a moderator

Re: Oracle documentation regarding full database export import operation [message #191936 is a reply to message #191934] Fri, 08 September 2006 14:54 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Please!

They should (must) NOT be exported and imported.
It is the way this is supposed to work!

Re: Oracle documentation regarding full database export import operation [message #191937 is a reply to message #191936] Fri, 08 September 2006 15:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> What version was your test done in Mahesh?
10GR2 on SLES9
>>export import method to upgrade a database, or to
>>duplicate a database for a test environment.
>>How would one go about recreating these triggers? What would be the best way?

Regarding upgrade using export/import, it works only if the data is plain/Straightforward without using options like java/intermedia.
Just create a new 10g database,export and import. In many cases it will (as long as you are not using the fancy modules).
Else going through the DBUA/methods said upgrade manuals are best.



To recreate the trigger in target,
just install those options (as in source).
Then you can manually check the differences. You can manually recreate them.
These specific triggers are created by the OPTIONs you have chosen during the database creation.
So when you create the database again in target with same options, it will be created for you.

Another thing is,
Even for upgrade using export/import, i will never to FULL import.
I will precreate the tablespaces/users and use fromuser/touser (unless i have large number of users). It gives me much better control.
Re: Oracle documentation regarding full database export import operation [message #192497 is a reply to message #191693] Tue, 12 September 2006 11:34 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hmmm. So I've created the new database, done the export from the old database. Now I'm importing, and getting an error which halts the import session:

"ALTER SESSION SET CURRENT_SCHEMA= "XDB""
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
IMP-00000: Import terminated unsuccessfully

My thought/assumption is that this is one of the optional components that must not have been installed by simply running the create database statement to create the new DB?

I didn't precreate users, but did tablespaces. But I shouldn't _have_ to precreate the users, because I already noticed the import session taking care of various users for me.

So if I'm right, and error is due to not having the option that uses the XDB user installed (probably xml db?), how would one find a list in the database of all components installed in the old database so that I could generate the same list in the new database and compare and then know what needs to be installed?

As a side note, I'm posting my current version of instructions that I have written and followed for the export import procedure. Perhaps they will help others.

I'm thinking these are close to correct, but please let me know if you spot a problem. I'm thinking that I got the import error because I skipped the "install optional components" step.

Re: Oracle documentation regarding full database export import operation [message #192501 is a reply to message #192497] Tue, 12 September 2006 11:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I would check the regular dictionary objects like PRODUCT_COMPONENT_VERSION.

There is a partial, outdated list here
http://www.orafaq.com/forum/m/55276/0/?srch=Oracle+Text+option+is+installed#msg_55276

If you are not using those options, you can actually, ignore those related error messages.
I would remove the those users/schema with the corresponding scripts oracle has already supplied.

Probably metalink will have an updated list for 10g.
Re: Oracle documentation regarding full database export import operation [message #192506 is a reply to message #191693] Tue, 12 September 2006 12:20 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hmm...I found a list of user administrative accounts, burried in chapter 5 of the 10gR2 installation guide for windows.

I'd like to ignore the errors if I don't need the options, but the errors halt the import session completely, not allowing me to continue (even with ignore=y).

What I can't figure out yet is why the error. And when I check product_component_version from both databases, I get the same result (both database share the same home).

So..I'm still looking.
Re: Oracle documentation regarding full database export import operation [message #192509 is a reply to message #192506] Tue, 12 September 2006 12:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Do not do full import.
Import only the specifc user using fromuser/touser
Re: Oracle documentation regarding full database export import operation [message #192518 is a reply to message #191693] Tue, 12 September 2006 13:07 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Well, the thing is I don't want to just do a fromuser touser situation. I hear you....but I guess in my mind if I have a database called A, and I want to create a test database call ATEST, which is exactly like A, I should be able to follow the procedures (as outlined by Oracle) to do a full export import and a manual database creation. If there are 500 users in the database, Ishouldn't have to list them all on the fromuser param.

I did find out one more thing. The XDB user itself is not exported during a full database export! The docs don't mention this, they just mention sys triggers not being exported.

But when I do an import with the show to create a log, and search for a create user xdb, it is missing! Most every other user is included, including other system user accounts. But XDB for some reason is not there.

So I ran catqm and catxdbj to install xdb manually. This creates the user, among other things. Next it is time to do the import, now that the user will be essentially precreated.
Re: Oracle documentation regarding full database export import operation [message #192529 is a reply to message #191693] Tue, 12 September 2006 14:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Be aware that the following users (in addition to xdb) are also not exported, and therefore need to be
installed in the new dupe database prior to using their associated functionality:

ctxsys
dmsys
exfsys
mdsys
ordplugins
ordsys
si_informtn_schema
wmsys
Re: Oracle documentation regarding full database export import operation [message #193012 is a reply to message #191693] Thu, 14 September 2006 09:42 Go to previous messageGo to next message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

have you created any triggers or any others objects in SYS schema. As per the oracle standard you have to create the your objects in you own tablespace and own schema.

In case you have your own schema you take the export those schema objects. (It will export all table, vires,..Etc., and triggers also).

You want more detail please give the more details. Like export only your schema objects. and the same thing you want import in some other location like..


Thanks,

Re: Oracle documentation regarding full database export import operation [message #193043 is a reply to message #193012] Thu, 14 September 2006 11:50 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
goudelly wrote on Thu, 14 September 2006 10:42

Hi,

have you created any triggers or any others objects in SYS schema. As per the oracle standard you have to create the your objects in you own tablespace and own schema.



I have to tell you, I have seen quite a few posts by you in the last few days and I have to disagree with something you have said in each and every one of these postings. You should really check your facts before making some of these statements.
Re: Oracle documentation regarding full database export import operation [message #193303 is a reply to message #193043] Fri, 15 September 2006 10:39 Go to previous message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi joy_division,

>I have to tell you, I have seen quite a few posts by you in the last few days and I have to disagree with something you have said in each and every one of these postings. You should really check your facts before making some of these statements.

Please tell where i was posted the wrong posting, i will correct my self.

Thanks,

Previous Topic: when to use SQL loader
Next Topic: perl and Oracle 9
Goto Forum:
  


Current Time: Tue Dec 06 04:41:33 CST 2016

Total time taken to generate the page: 0.10551 seconds