Home » RDBMS Server » Server Utilities » importing full database
importing full database [message #73560] Sun, 23 May 2004 23:43 Go to next message
sunitha agarwal
Messages: 7
Registered: February 2004
Junior Member
hi

how to import a full database dump to a a new database.i
want export all database to new one whether i have to create the db manually then import it or it will create automatically.
with regards
sunitha
Re: importing full database [message #73566 is a reply to message #73560] Mon, 24 May 2004 04:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. create the DB manually.
2. recreate all the tablespaces and users in TargetDB as-same-as in sourceDB.
3. export the SOurceDB
4. Import into TargetDB
Re: importing full database [message #73578 is a reply to message #73566] Tue, 25 May 2004 05:28 Go to previous messageGo to next message
suntiha
Messages: 1
Registered: May 2004
Junior Member
i exactly want to import the specific users to specific tablespaces
if we r not specificing tablespace where it will import
to system tablespace or wheter we can specify specific
tablespace to import
Re: importing full database [message #73580 is a reply to message #73578] Tue, 25 May 2004 05:41 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
create the same tablespaces in targetDB.
assign a default tablespace to the user.
Do not grant RESOURCE role to the user.
Becuase RESOURCE role gives full access to all tablespaces.

so it may create objects in system tablespace also.

so without granting REsource ROLE,
create the user
allocate quota on all the other tablespaces in which the user is allowed to create objects.
now

You can import a specific user.

imp dba/password file=somedumpfile.dmp fromuser=sourceUser touser=targetUser
Re: importing full database [message #73587 is a reply to message #73566] Wed, 26 May 2004 06:39 Go to previous messageGo to next message
Calla
Messages: 5
Registered: May 2004
Junior Member
Hi Mahesh,

About the full database import, I have 2 questions:
1. How to creat a script in old DB for creating the users in new DB? Do I need to give the roles, syste/object privilege to the user, or can they be impported from the dump file?

2. When importing the database, do I need to import the data first, grants, indexes, contraints later?

I am going to rebuild my production DB with the proprt DB block size. I will follow the procedure you mentioned. But actually how can I be sure the new DB is exactly same as the old one? I appreciate if you can tell me more about it. Thanks

Jadie
Re: importing full database [message #73589 is a reply to message #73587] Wed, 26 May 2004 08:12 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
1. 

   If you are doing a full database import, just pre-create the tablespaces like the soourceDB.
   do a full import. It will work.
   personally i dont do this.
   if The number of schema/users to be ported are less, do the import schema-by-schema.
   just recreate the users in targetDB with minumum roles.
   the grants can be given during the import.
   or
   if u want to generate the script from dump file you can also do it.
   
   -- how to generate script from dump file
   -- this will not DO the import. but just goes through the whole process of reading the
   -- dumpfile and just gives some readable text.
   imp dba/password full=y file=somedumpfile.dmp show=y log=somelogfile.log

   now if u open the somelogfile.log, you can see all the details you want.
   -- typical entries in somelogfile.log would be like

 "CREATE TABLESPACE "USERS" BLOCKSIZE 8192 DATAFILE  '/u01/app/oracle/oradata"
 "/mutation/users01.dbf' SIZE 279183360       AUTOEXTEND ON NEXT 1310720  MAX"
 "SIZE 32767M, '/u01/app/oracle/oradata/mutation/users02.dbf' SIZE 10485760  "
 "     EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  NOLOGGING SEG"
 "MENT SPACE MANAGEMENT AUTO"

"CREATE USER "TEST" IDENTIFIED BY VALUES '7A0F2B316C212D67' DEFAULT TABLESPA"
 "CE "USERS" TEMPORARY TABLESPACE "TEMP""

   -- how to create the users in new targetDB with same password like in sourceDB
   -- (if you dont know the passcode in source).
   --  do the import with show=y and log the output.
   --  If you are createing the user TEST, copy the corresponding entry and run 
   --  it in targetDB.
"CREATE USER "TEST" IDENTIFIED BY VALUES '7A0F2B316C212D67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP""

   
2.
  if there is too much volume of data,
  it is recomended to load the data first and then rebuild the indexes manually.
  so during import 
  -- to load just table and data and NO INDEXES
  imp dba/password full=y file=somedumpfile.dmp indexes=N
  -- how to recreate the indexes in targetDB?
  -- method 1.
  -- get the DDL from the dump file as shown above.
  -- run ddl against the targetDB.
  -- method 2.
  -- works only in 9i, using dbms_metadata.
  -- run the script ddl.sql againts the sourcedb and spool the output. 
  -- you can get the ddl of indexes. now run the same spooled output against the targetDB.
	

dbadmin@republic_lawp1 > @ddl.sql

  CREATE INDEX "DBADMIN"."LAWPSTATS" ON "DBADMIN"."LAWPSTATS" ("STATID", "TYPE",
 "C5", "C1", "C2", "C3", "C4", "VERSION")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
 ;

  CREATE UNIQUE INDEX "DBADMIN"."SYS_IL0000028549C00003$$" ON "DBADMIN"."XMLTABL
E" (
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
  PARALLEL (DEGREE 0 INSTANCES 0)
 ;

--
-- this the sourcecode for ddl.sql
-- 
dbadmin@republic_lawp1 > get ddl.sql
  1  set long 500000000000000
  2  SET HEAD off
  3  SELECT DBMS_METADATA.GET_DDL('INDEX',D.INDEX_NAME)||';' FROM user_indexes D;
 

Previous Topic: error when creating a new tablespace
Next Topic: Calling SQLLDR from Perl
Goto Forum:
  


Current Time: Mon Jul 01 16:02:50 CDT 2024