Today while trying to copy a schema (in same instance using impdp) i got the following error
CREATE TYPE ""T2."NAME_AND_COUNT" OID 'B39B42D096902425E0409C0AF60C2DB0' IS OBJECT ( OBJECT_NAME varchar2(30), UPDATE_COUNT INTEGER);ORA-39083: Object type TYPE failed to create with error:ORA-02304: invalid object identifier literalFailing sql is:CREATE TYPE "T2"."OBJECTIDLIST" OID 'B39B42D096992425E0409C0AF60C2DB0' IS TABLE OF INTEGER;ORA-39083: Object type TYPE failed to create with error:ORA-02304: invalid object identifier literal
Cause : Oracle was trying to import an object with OID which already existed in database. Since i was copying the user t1 to user t2 no surprise that the OID already existed. And OID should be unique in a database.
Solution : I added the clause transform=OID:n in my impdp command.
example : -impdp T2/T2@Tesddb REMAP_SCHEMA=T1:T2 VERSION=10.2 DIRECTORY=DMP LOGFILE=impdp.log.log JOB_NAME=t1tot2_job transform=OID:n
DUMPFILE=t1exp.dmp
Everything went fine after that.
Ankur
Monday, 19 December 2011
Wednesday, 21 April 2010
Recovering from Datafile loss without backup
This is a demonstration for recovering from a loss of datafile without backup.
But we require all the archivelogs and online redologs since the creation of datafile.
Ok so let’s get it started.
Step 1 : Preparing a tablespace test with which we will play.
Log in as sys user.
SQL> show user;
USER is "SYS"
Let’s check which are the present datafiles in system.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\EXAMPLE01.DBF
SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\test.dbf' size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\EXAMPLE01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST.DBF
6 rows selected.
Step 2: Let’s create a user named test. And I am setting the default tablespace as test. And granting the privs to create table , session etc. This i am doing so that we can verify later whether we have recovered the actual data in datafile.
SQL> create user test identified by test default tablespace test temporary tablespace temp
2 quota unlimited on test;
User created.
SQL> grant create session to test;
Grant succeeded.
SQL> grant create table to test;
Grant succeeded.
Step 3: Now create a table insert some rows commit it;.
SQL> conn test/test@PROD
Connected.
SQL> create table ipl(team_name varchar2(8) , wins number , lost number);
Table created.
SQL> insert into ipl values('KXIP' , 4 , 10);
1 row created.
SQL> insert into ipl values('CSK' ,7,7);
1 row created.
SQL> insert into ipl values('DC' , 7 , 7);
1 row created.
SQL> insert into ipl values('MI' , 12 , 2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ipl;
TEAM_NAM WINS LOST
-------- ---------- ----------
KXIP 4 10
CSK 7 7
DC 7 7
MI 12 2
Step 4: Ok so we have created a table called IPL owned by test user. Let’s check if the IPL table is really in test tablespace or not.
SQL> conn sys/prod@PROD as sysdba
Connected.
SQL> select TABLESPACE_NAME from dba_segments where segment_name='IPL';
TABLESPACE_NAME
------------------------------
TEST
So yes we have IPL table in the test tablespace.
Step 5: Let’s archive the current log
SQL> alter system archive log current;
System altered.
Step 6: Time to shutdown database . In windows we have to shutdown otherwise we will not be able to delete the test datafile. For unix or linux this is not required.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Setp 7: Now steer down to the directory where we have the test.dbf datafile and delete it.
D:\oracle\product\10.2.0\oradata\PROD>del TEST.DBF
D:\oracle\product\10.2.0\oradata\PROD>dir
Volume in drive D has no label.
Volume Serial Number is 606C-36CF
Directory of D:\oracle\product\10.2.0\oradata\PROD
04/21/2010 04:11 PM.
04/21/2010 04:11 PM..
04/21/2010 04:07 PM 7,061,504 CONTROL01.CTL
04/21/2010 04:07 PM 7,061,504 CONTROL02.CTL
04/21/2010 04:07 PM 7,061,504 CONTROL03.CTL
04/21/2010 04:07 PM 104,865,792 EXAMPLE01.DBF
04/21/2010 04:07 PM 52,429,312 REDO01.LOG
04/21/2010 04:07 PM 52,429,312 REDO02.LOG
04/21/2010 04:07 PM 52,429,312 REDO03.LOG
04/21/2010 04:07 PM 262,152,192 SYSAUX01.DBF
04/21/2010 04:07 PM 503,324,672 SYSTEM01.DBF
04/21/2010 03:56 PM 20,979,712 TEMP01.DBF
04/21/2010 04:07 PM 31,465,472 UNDOTBS01.DBF
04/21/2010 04:07 PM 5,251,072 USERS01.DBF
13 File(s) 1,127,491,072 bytes
2 Dir(s) 18,588,344,320 bytes free
Here we can see that the datafile is not present it has been physically deleted.
Step 8: Let’s try to start database now
D:\oracle\product\10.2.0\oradata\PROD>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 21 16:11:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST.DBF'
Oops it’s not coming up.
Step 9 :
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST.
DBF'
2 as 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST1.DBf';
Database altered.
This is the most important step. It will create an empty datafile exactly of same size but with a different name test1.dbf. We can rename the datafile later on to it’s original name if we want to.
Let’s try again to open database.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST1.DBF'
So it seems that the datafile needs recovery in absolute sense it does not have anything in it so better we apply all the archivelogs to put data into it.This can be done with command goven below
.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database open;
Database altered.
Step 10: So we have managed to bring up the database, But wait a minute what about the table that we have created in Step 3 ??? let’s connect as test user and check if data is there or not
SQL>conn test/test@PROD
SQL> show user
USER is "TEST"
SQL> select * from IPL;
TEAM_NAM WINS LOST
-------- ---------- ----------
KXIP 4 10
CSK 7 7
DC 7 7
MI 12 2
SQL>
Hurray data is there. So mission accomplished.
The above example was on Windows XP and Oracle 10g r2.
Disclaimer The above demonstration is for educational purposes only. In real world always have a valid backup of datafile.
Hope you enjoyed it.
Khurana
But we require all the archivelogs and online redologs since the creation of datafile.
Ok so let’s get it started.
Step 1 : Preparing a tablespace test with which we will play.
Log in as sys user.
SQL> show user;
USER is "SYS"
Let’s check which are the present datafiles in system.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\EXAMPLE01.DBF
SQL> create tablespace test datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\test.dbf' size 10m;
Tablespace created.
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\SYSAUX01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\USERS01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\EXAMPLE01.DBF
D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST.DBF
6 rows selected.
Step 2: Let’s create a user named test. And I am setting the default tablespace as test. And granting the privs to create table , session etc. This i am doing so that we can verify later whether we have recovered the actual data in datafile.
SQL> create user test identified by test default tablespace test temporary tablespace temp
2 quota unlimited on test;
User created.
SQL> grant create session to test;
Grant succeeded.
SQL> grant create table to test;
Grant succeeded.
Step 3: Now create a table insert some rows commit it;.
SQL> conn test/test@PROD
Connected.
SQL> create table ipl(team_name varchar2(8) , wins number , lost number);
Table created.
SQL> insert into ipl values('KXIP' , 4 , 10);
1 row created.
SQL> insert into ipl values('CSK' ,7,7);
1 row created.
SQL> insert into ipl values('DC' , 7 , 7);
1 row created.
SQL> insert into ipl values('MI' , 12 , 2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from ipl;
TEAM_NAM WINS LOST
-------- ---------- ----------
KXIP 4 10
CSK 7 7
DC 7 7
MI 12 2
Step 4: Ok so we have created a table called IPL owned by test user. Let’s check if the IPL table is really in test tablespace or not.
SQL> conn sys/prod@PROD as sysdba
Connected.
SQL> select TABLESPACE_NAME from dba_segments where segment_name='IPL';
TABLESPACE_NAME
------------------------------
TEST
So yes we have IPL table in the test tablespace.
Step 5: Let’s archive the current log
SQL> alter system archive log current;
System altered.
Step 6: Time to shutdown database . In windows we have to shutdown otherwise we will not be able to delete the test datafile. For unix or linux this is not required.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Setp 7: Now steer down to the directory where we have the test.dbf datafile and delete it.
D:\oracle\product\10.2.0\oradata\PROD>del TEST.DBF
D:\oracle\product\10.2.0\oradata\PROD>dir
Volume in drive D has no label.
Volume Serial Number is 606C-36CF
Directory of D:\oracle\product\10.2.0\oradata\PROD
04/21/2010 04:11 PM
04/21/2010 04:11 PM
04/21/2010 04:07 PM 7,061,504 CONTROL01.CTL
04/21/2010 04:07 PM 7,061,504 CONTROL02.CTL
04/21/2010 04:07 PM 7,061,504 CONTROL03.CTL
04/21/2010 04:07 PM 104,865,792 EXAMPLE01.DBF
04/21/2010 04:07 PM 52,429,312 REDO01.LOG
04/21/2010 04:07 PM 52,429,312 REDO02.LOG
04/21/2010 04:07 PM 52,429,312 REDO03.LOG
04/21/2010 04:07 PM 262,152,192 SYSAUX01.DBF
04/21/2010 04:07 PM 503,324,672 SYSTEM01.DBF
04/21/2010 03:56 PM 20,979,712 TEMP01.DBF
04/21/2010 04:07 PM 31,465,472 UNDOTBS01.DBF
04/21/2010 04:07 PM 5,251,072 USERS01.DBF
13 File(s) 1,127,491,072 bytes
2 Dir(s) 18,588,344,320 bytes free
Here we can see that the datafile is not present it has been physically deleted.
Step 8: Let’s try to start database now
D:\oracle\product\10.2.0\oradata\PROD>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Apr 21 16:11:35 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST.DBF'
Oops it’s not coming up.
Step 9 :
SQL> alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST.
DBF'
2 as 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST1.DBf';
Database altered.
This is the most important step. It will create an empty datafile exactly of same size but with a different name test1.dbf. We can rename the datafile later on to it’s original name if we want to.
Let’s try again to open database.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\PROD\TEST1.DBF'
So it seems that the datafile needs recovery in absolute sense it does not have anything in it so better we apply all the archivelogs to put data into it.This can be done with command goven below
.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database open;
Database altered.
Step 10: So we have managed to bring up the database, But wait a minute what about the table that we have created in Step 3 ??? let’s connect as test user and check if data is there or not
SQL>conn test/test@PROD
SQL> show user
USER is "TEST"
SQL> select * from IPL;
TEAM_NAM WINS LOST
-------- ---------- ----------
KXIP 4 10
CSK 7 7
DC 7 7
MI 12 2
SQL>
Hurray data is there. So mission accomplished.
The above example was on Windows XP and Oracle 10g r2.
Disclaimer The above demonstration is for educational purposes only. In real world always have a valid backup of datafile.
Hope you enjoyed it.
Khurana
Friday, 29 January 2010
Subscribe to:
Comments (Atom)