importing full database [message #73560] |
Sun, 23 May 2004 23:43 |
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 #73578 is a reply to message #73566] |
Tue, 25 May 2004 05:28 |
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 #73587 is a reply to message #73566] |
Wed, 26 May 2004 06:39 |
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 |
|
Mahesh Rajendran
Messages: 10708 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;
|
|
|