OK - My turn,
When I read this earlier, I thought I saw that this was a 9i dB. If so,
why not just shut everything down and clone the new dB with the appropriate
user, sid, $oh blah blah blah. Don't forget to add the temp file.
-----Original Message-----
From: Kathy Wright
[mailto:oracledba-ezmlmshield-x34202378.[Email address protected]
Sent: Tuesday, February 27, 2007 3:03 PM
To: LazyDBA Discussion
Subject: RE: Moving database from one server to another -- Kevin FYI only
--- Peter, please read
Hi,
I do this ALL the time - updating test db's with production data. So I
agree with Jeff in everything, especially using dbca and
only taking what you need. (Iv'e been doing this since 1984, Oracle release
2!!!) Once your "test" db storage is built (the
tablespaces - and the names will have to be the same) and your user is
created, you can then follow these 7 steps. Like I said, I do this ALL the
time!!!
And yes, the definition of "schema" is a group of objects owned by a single
user.
To move from prod to test or vice versa.
1. Get an export of the schema(s) being moved:
exp user/pw file=exp_schema.dmp owner=schema consistent=y statistics=none
log=exp_schema.log
2. create a drop object script to be used against the schema to be
overwritten:
** YOU CAN SKIP THIS SINCE YOU"RE IMPORTING INTO A CLEAN DB, NOT UPDATING A
TEST DB****
set pagesize 10000
set linesize 200
spool drop_objs.sql
select 'drop '||object_type||' '||owner||'.'||object_name||';'
from dba_objects where owner = 'putschemaname here';
spool off
3. Create two grant scripts from the ORIGINAL schema:
spool grant_role.sql
select 'grant '||granted_role||' to '||grantee||';'
from dba_role_privs where grantee = 'PUTSCHEMANAMEHERE';
spool off
spool grant_tab_privs.sql
select 'grant '||privilege||' on '||table_name||' to '||grantee||';'
from dba_tab_privs where owner = 'PUTSCHEMANAMEHERE';
spool off
4. Run the drop script created in step 2 in the database whose schema you
are replacing.
** SKIP STEP FOUR, AS YOU ARE IMPORTING INTO A NEW DB
5. Import the export done in step #1
imp system/[Email Address Removed] file=exp_schema.dmp fromuser=originalschema
touser=newschema commit=Y grants=N statistics=NONE ignore=Y log=import.log
------------------------------------------------
DO NOT FORGET THESE STEPS !!!!!!!!!!!!!!!!!!!
------------------------------------------------
6. Run the two grant scripts created in step 3.
7. Recompile any invalid objects. These could be invalid becuase the import
runs the ddl scripts, and they
could be relying on objects which hadn't been imported yet.
Kathy Wright
AVP, Sr. Oracle DBA, O.C.P.
Database & Decision Systems
>>> "Jeff Ferrel " <oracledba-ezmlmshield-x52724114.[Email address
protected] 2/27/2007 12:30:15 PM >>>
(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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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