Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Build an exact copy of a database using export / import

RE: Build an exact copy of a database using export / import

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Wed, 30 May 2001 12:39:34 -0700
Message-ID: <F001.0031454A.20010530123831@fatcity.com>

Thanks!
Ron

-----Original Message-----
Sent: Wednesday, May 30, 2001 3:08 PM
To: Multiple recipients of list ORACLE-L

A full export is supposed to allow for a full recovery. Thus, if you do a full export, the import will try to create all the tablespaces that existed when you exported. That is the first item in the export file (see below). However, if you create the tablespaces you want and don't allow (via file naming convention) the ones you don't want created, you can get the import to just create the tablespaces you want created. For example, the first tablespace in the excerpt below is the TEMPORARY tablespace. If I did this on a server where /salxha2/u01/oradata/swims did not exist, then the create will fail and as long as ignore=y is set, the import will continue. When import sees that a tablespace doesn't exist for a table it is supposed to create and import, it will use the default tablespace of the importer it is connected as (usually the owner of the object).

Other options include logging into svrmgrl or sqlplus as sys and dropping the tablespaces that you don't want as they are created by the import or editting the export file. I know some people have successfully editted an export file, however, I haven't been able too. I always get an invalid marker message or something like that.

As far as some grants not coming across, items created by sys will not be exported as they are expected to be there before the import (via the dictionary scripts). So if you have sys granting directly to a user (say granting select on v$_lock), then you'll need to do a script that runs as the import is running to fix those problems. If it isn't grants from sys failing, then make sure the user that granted the options still has the power to grant (ie the "WITH GRANT OPTION" and/or "WITH ADMIN OPTION" authorities in place) when you do the export. Otherwise the create/alter user that import does will not grant the powers to that user that it used when it made the grants and when it comes time for that user to grant permissions, it may fail due to "insufficient privs".

Hope this helps...




^C^AEXPORT:V07.03.04
DSYS
RENTIRE
1024
0
                                                               Mon May 28
14:28:
32 2001
BEGINSYS
CONNECT SYSTEM
CREATE TABLESPACE "TEMPORARY" DATAFILE
'/salxha2/u01/oradata/swims/temporary_01
.dbf' SIZE 356515840 REUSE DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 MINEXTE
NTS 1 MAXEXTENTS 121 PCTINCREASE 0) ONLINE TEMPORARY CREATE TABLESPACE "MASTER" DATAFILE
'/salxha2/u03/oradata/swims/master_tab_03.d
bf' SIZE 52428800 REUSE, '/salxha2/u03/oradata/swims/master_tab_02.dbf' SIZE 524
28800 REUSE, '/salxha2/u03/oradata/swims/master_tab_01.dbf' SIZE 83886080 REUSE,
 '/salxha2/u03/oradata/swims/master_04.dbf' SIZE 262144000 REUSE DEFAULT STORAGE
 (INITIAL 10240 NEXT 409600 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 0) ONLINE PE
RMANENT
CREATE TABLESPACE "WAYBILL" DATAFILE
'/salxha2/u03/oradata/swims/waybill_tab_02
.dbf' SIZE 524288000 REUSE, '/salxha2/u03/oradata/swims/waybill_tab_01.dbf' SIZE
 419430400 REUSE DEFAULT STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 2 MAXE
XTENTS 11 PCTINCREASE 0) ONLINE PERMANENT ...
CREATE USER "xx" IDENTIFIED BY VALUES 'xxxxxxxxxxxxxxxxx' DEFAULT TABLESPACE

 "USERS" TEMPORARY TABLESPACE "TEMPORARY" .....
GRANT "MONITORER" TO "xx" WITH ADMIN OPTION GRANT "DBA" TO "xx"


--Chris
Chris.Bowes_at_Kosa.com

-----Original Message-----
]
Sent: Wednesday, May 30, 2001 12:57 PM
To: Multiple recipients of list ORACLE-L

Well, depending on the size of the db and some other network stuff, I do the

following. I shut down the real db, copy all of the data files, controlfiles and init file to a machine with Oracle on it. Then you can create the service (NT), alter any of the datafiles if necessary (on different logical drive) then you can just bring that bad boy up. Then I just give it a different alias, works for me.

        However, what do you mean it creates new tablespaces, I have never seen
that happen, it just fails miserably for me if the tablespaces don't exist. You could do the full export, create a new user on the new machine and grant

"become any user" to the new user. I believe that then the import would work, I've done it before. Sorry I don't have more, brain is still a littel

soft from the weekend.
Kev

-----Original Message-----
L.
Sent: Wednesday, May 30, 2001 12:26 PM
To: Multiple recipients of list ORACLE-L

When I build a new test database I create the database and run all the required Oracle scripts. Then I do a full import of a production export. This works pretty well but I miss some of the grants and The full import wants to create tablespaces that I may or may not want created. I get a lot

of errors on system objects. Does anyone have the proper steps to create an

exact copy of a production database using export / import?

Thanks!
Ron Smith
Database Administration
rlsmith_at_kmg.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>  
-- 
Author: Smith, Ron L. 
  INET: rlsmith_at_kmg.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>  
-- 
Author: Kevin Kostyszyn 
  INET: kevin_at_dulcian.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California        -- Public Internet access / Mailing Lists 
-------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Smith, Ron L.
  INET: rlsmith_at_kmg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed May 30 2001 - 14:39:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US