Home » RDBMS Server » Server Utilities » full import
full import [message #271865] Wed, 03 October 2007 03:46 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i have exported the database as exp system/xxx@bank file=c:\xxx\dddd\bank_011007.dmp full=y
size is around 3gb
i have dropped the database bank as database was corrupt
i have created the database with same name (bank) on the same system
can i import full=y without creating user's/tablespace
imp system/xxx@bank file=c:\xxx\dddd\bank_011007.dmp full=y
Re: full import [message #271868 is a reply to message #271865] Wed, 03 October 2007 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
yes.

Regards
Michel
Re: full import [message #271873 is a reply to message #271868] Wed, 03 October 2007 04:25 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
micheal as i posted morning the database is corrupt you told me to recreate i dont want to drop .. i will create new database
can i change the database name and do full import
imp system/xxx@newdatabase_name file=c:\xxx\dddd\bank_011007.dmp full=y
work
do i need to create user and tablespace

Re: full import [message #271879 is a reply to message #271873] Wed, 03 October 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already answered.

Regards
Michel
Re: full import [message #271954 is a reply to message #271865] Wed, 03 October 2007 08:11 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
oracle_coorgi wrote on Wed, 03 October 2007 04:46

i have dropped the database bank as database was corrupt
i have created the database with same name (bank) on the same system
can i import full=y without creating user's/tablespace



If you created a new database and you don't want to import the users and tablespaces, then there is nothing to do. If there is nothing to be created, why would you do an import?
Re: full import [message #272054 is a reply to message #271865] Wed, 03 October 2007 18:49 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
If you don't want to include the USER schema, you don't have to import over it.

Simply re-create the schema/tablespace structure for the other schema/tablespaces that you had before in the old database.

Set permissions and so forth.

For more information on the syntax of the binary IMPort utility, see to see how to include (and by extension exclude) schemas -->

* At the command line type in: imp help=y

* Or else, Google.com


Hope it helps...
seethem...
Re: full import [message #272476 is a reply to message #272054] Fri, 05 October 2007 03:41 Go to previous messageGo to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
as i understood the my database is corrupt i need to import the exported file which was taken two days back
i had given exp system/xxxx@dddd file=c:\.....exported.dmp full=y
i have created new database without user and tablespace(only default user like sys system etc.. and tablespace system,user,temp default object are created)
has i was told it will create automatically with the export file
all the user(around 45 user and extra three tablespace)

now i have tried to import the dump
imp system/xxxx@newdatabase file=c:\......\exported.dmp full=y log=c:\...\cc.log
its giving error
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'xxxx' does not exist
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE USER "xxxx" IDENTIFIED BY VALUES 'F68005CBF5B3EAA5' DEFAULT TABLE"
 "SPACE "xxxxxx" TEMPORARY TABLESPACE "TEMP""

IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'xxxxx' does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYSCALLS", line 106
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4565
ORA-06512: at "SYS.DBMS_AQADM", line 465
ORA-06512: at line 2
IMP-00017: following statement failed with ORACLE error 1917:
 "BEGIN  "
 "SYS.DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE('DEQUEUE_ANY','xxxxxx',FALSE);"
 "COMMIT; END;"

IMP-00019: row rejected due to ORACLE error 1
IMP-00003: ORACLE error 1 encountered
ORA-00001: unique constraint (SYSTEM.REPCAT$_OBJECT_TYPE_PK) violated
Column 1 14
Column 2 TYPE BODY
Column 3 01
Column 4 


how can i slove this urgent ...
do i need to create user's and tablespace's and other thing manually
if that is the case what is the use of full export (assuming that we can import in case of emergency )

Re: full import [message #272481 is a reply to message #272476] Fri, 05 October 2007 04:06 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
oracle_coorgi,

You are correct. You need to create a "shell" prior to the binary IMPORT process.

For example, if I have two databases, one called OLD (has data) and one called NEW (empty)

Suppose OLD has the following non-system SCHEMAS (or as you call them USERS) and TABLESPACES :

SCHEMA --> TABLESPACES
-----------------------------------
FOOD --> FOOD_DATA, FOOD_INDEX
DRINK --> DRINK_DATA, DRINK_INDEX
-----------------------------------

Suppose further, I did an EXPORT of OLD and called it OLD.dmp

Now the game plan is to populate NEW with the data from OLD, keeping the schemas consistent, then I must manually recreate the same schema/tablespace shell in NEW.

I.e.
1) I must run a CREATE TABLESPACE for FOOD_DATA, FOOD_INDEX (preferably the same size as in the OLD database)
2) Run a CREATE USER for FOOD and assign it FOOD_DATA, FOOD_INDEX (with the same attributes that was present in the OLD database and unlimited quota on the tablespaces for example)
3) I must run a CREATE TABLESPACE for DRINK_DATA, DRINK_INDEX (preferably the same size as in the OLD database)
4) Run a CREATE USER for DRINK and assign it DRINK_DATA, DRINK_INDEX (with the same attributes that was present in the OLD database and unlimited quota on the tablespaces for example)

When this non-system "shell" has been created, finally, perform the IMPORT.



Hopefully this helps
seethem
Re: full import [message #272513 is a reply to message #272481] Fri, 05 October 2007 06:17 Go to previous message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
hi
i had created required user and tablespace
first break it got Import terminated successfully without warnings.
ok
i dont have any object related to the user and data in the table what i will have to do to restore the objects and data
i got indexfile.....

REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)) ;
REM  ... 0 rows
CONNECT SYSTEM;
CREATE INDEX "SYSTEM"."DEF$_TRANORDER" ON "DEF$_AQCALL" ("CSCN" , 
"ENQ_TID" ) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 
FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "SYSTEM" LOGGING ;
REM  ALTER TABLE "SYSTEM"."DEF$_AQCALL" ADD PRIMARY KEY ("ENQ_TID", REM  "STEP_NO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE 
REM  "SYSTEM" LOGGING ENABLE ;

i got log ....
C:\>imp system/xxxx@newdatabase file=C:\ddd\backup\ffff.d
mp log=C:\xxxxxx\backup\final\fff.log full=y show=y indexfile=
spef.sql

. . skipping table "DEF$_TEMP$LOB"
. . skipping table "HELP"
. . skipping table "LOGSTDBY$APPLY_MILESTONE"
. . skipping partition "LOGSTDBY$APPLY_PROGRESS":"P0"
. . skipping partition "fff":"ffff"
. . skipping partition "gggg":"gggg"
. . skipping partition "hhhh":"hhh"
. . skipping table "LOGSTDBY$EVENTS"
. . skipping table "LOGSTDBY$HISTORY"
. . skipping table "ddddd"
. . skipping table "ffff"
. . skipping table "rrrrr"
Import terminated successfully without warnings.
Previous Topic: IMP-00019: row rejected due to ORACLE error 1401
Next Topic: Export Not completed
Goto Forum:
  


Current Time: Fri Dec 09 00:01:29 CST 2016

Total time taken to generate the page: 0.10476 seconds