(Sorry this is a little long, but I put as much into it as I could to
make it a document you could refer to.... please, anyone out there that
wishes to critique it, please do.)
Tony:
Is it going to the same platform? Did you build the original with
scripts?
There are a couple paths you could go down, but regardless of the
answers to the questions above, here is the way I did it when I was
first learning Oracle, and what about import and export was, blah , blah
, blah.
Go into OEM and create it. Now to do that, you'll create:
--A user (the future schema owner)
--A tablespace with datafiles (or multiple tablespaces, depending)
Look at the size of the datafiles and how full or empty they are. You
may want to create larger or smaller datafiles based on what you see, as
well as set the auto extend to a more appropriate size, etc.
THIS IS A GREAT TIME TO RESIZE THE DB.
Step ZERO: On your new platform, create a database using the DBCA, or
create it from a script, however you feel most comfortable. I use the
DBCA and strip out all the pieces I don't need. I used to be a total
command line guy, but I'm getting old and lazy.
STEP 1: Create a tablespace
Define your datafiles within it. Check your old database for 'unique'
index tablespaces, user tablespaces, etc. Hopefully they will use the
defaults.
Regardless, make the new database match the old database as for as the
architecture. You can make changes to the structure later, and only if
you want to.
Second, create the user (future schema owner) under 'security | users'.
Grant the role of connect and resource (no admin option). For now, give
it DBA... you'll see why later (any we will revoke it when done).
Associate the user with the appropriate tablespace and temp tablespace
(which you just created), set the password, make sure the user is
unlocked.
OK -- now you have a user and tablespaces associated... but why doesn't
it show up as a schema?
It's an easy answer but always baffled me in the beginning. The reason
is that A USER IS A USER, while a schema owner is a USER THAT ALSO OWNS
OBJECTS. Your 'user' any objects...yet. It will when you import.
For me, that piece was HUGE. I truly started understanding more and more
about Oracle architecture after that. OK, enough about me.
Now get and export of your old database using the fromuser, touser. You
don't want all the other junk that comes with full=y since you've got a
new Oracle installation.
Now import the database using your new user, or as sys or system. ***
The reason I had you grant DBA to the new user is that if you export a
database as a user that has DBA privileges, the import might freak if
the import user doesn't have DBA as a grant.
My big thing is to have an import that is clean, and says 'Import
terminated successfully WITHOUT warnings'.
You can import a database with warnings that may be unimportant, but
that's a chance a new guy/gal with a production database doesn't want to
take.
Let me know if this helps.
Jeffrey C. Ferrel
DMS III
State of Nevada
775.687.9327
[Email address protected]
-----Original Message-----
From: Tony
[mailto:oracledba-ezmlmshield-x51816616.[Email address protected]
Sent: Tuesday, February 27, 2007 7:23 AM
To: LazyDBA Discussion
Subject: Moving database from one server to another
Hi Gurus,
I need to move a small production database from one server to another.
I have Oracle9i on a Unix platform. Can some direct me or give me the
information needed to export data from a production database.
Thanks in advance.
Tony
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page