| full import [message #271865] |
Wed, 03 October 2007 03:46  |
oracle_coorgi
Messages: 188
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 #271873 is a reply to message #271868] |
Wed, 03 October 2007 04:25  |
oracle_coorgi
Messages: 188
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 #271954 is a reply to message #271865] |
Wed, 03 October 2007 08:11 |
joy_division
Messages: 4963
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 |
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 |
oracle_coorgi
Messages: 188
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 |
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 |
oracle_coorgi
Messages: 188
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
