Ask The Real Tom

January 5, 2010



Oracle Import / Export Tables with flashback archive enabled

Filed under: Uncategorized — Tags: , , , , , , — admin @ 9:43 am

I created a simple schema with one table and flashback archive.

exp test

exp file=test.dmp owner=TKRIM

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TKRIM
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TKRIM
About to export TKRIM's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TKRIM's tables via Conventional Path ...
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

exp seem not be working with orcle flashback archive.

An export with datapump expdp works. But it does not export the history.

expdp schemas=TKRIM dumpfile=test.dp

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=TKRIM dumpfile=test.dp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TKRIM"."DEMO":"E1" 5.421 KB 2 rows
. . exported "TKRIM"."DEMO":"E2" 5.429 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u00/app/oracle/admin/DIGATE20/dpdump/test.dp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:19:26

the import look more difficutl already

impdp schemas=TKRIM dumpfile=test.dp TABLE_EXISTS_ACTION=replace

..
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39121: Table "TKRIM"."DEMO" can't be replaced, data will be skipped. Failing error is:
ORA-55610: Invalid DDL statement on history-tracked table
ORA-00955: name is already used by an existing object
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at 09:22:03

disabling flashback archive on the table does the trick.

ALTER TABLE tkrim.demo NO FLASHBACK ARCHIVE;
drop table tkrim.demo;

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TKRIM"."DEMO":"E1" 5.421 KB 2 rows
. . imported "TKRIM"."DEMO":"E2" 5.429 KB 2 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 09:24:05

but the table stays without flashback archiving enable. a query to the flashback archive still works.


select * from tkrim.demo as of scn 827813;

ID VAL
---------- ----------
1 abc1
2 abc2
30 test30
31 test31
--> wrong!!!! (flashback archive not enabled)

connect tkrim/tkrim1
ALTER TABLE demo FLASHBACK ARCHIVE fla1;

It can be enabled again – but this behavior leaves room to do easy mistakes.

January 4, 2010



Oracle Flashback Archive (Total Recall) Problems

Filed under: oracle,Uncategorized — Tags: , , , , , — admin @ 1:42 pm

Oracle Flashback Archive offers simple archiving with simple administration.
During some testing on Oracle 11.2 with Flashback Archive I exprerianced some heavy problems.

The day began with a simple testcase:

create user tkrim identified by tkrim1;
grant connect to tkrim;
grant resource to tkrim;
grant FLASHBACK ARCHIVE administer to tkrim;

connect tkrim/tkrim1

col scn for 999999999999999999999999

create table demo (id number, val varchar(200) ) ;
ALTER TABLE demo FLASHBACK ARCHIVE fla1;

insert into demo values (1,'test1');
insert into demo values (2,'test2');
insert into demo values (3,'test3');
commit;

select dbms_flashback.get_system_change_number scn from dual;

update demo set val='abc1' where id=1;
update demo set val='abc2' where id=2;
update demo set val='abc3' where id=3;
commit;

select dbms_flashback.get_system_change_number scn from dual;

delete from demo where id=3;
commit;

select dbms_flashback.get_system_change_number scn from dual;

select * from demo;

select * from demo as of scn 55710824751 ;

After a my UNDO tablespace was full I played a bit with undo_retention and changing the undo tablespace which case following error.


ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=0 sec, SCN: 0x000c.f7ca7c8f):
select file#, block#, ts#, blocks from seg$ where type# = 3
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=1 sec, SCN: 0x000c.f7ca7d8f):
select file#, block#, ts#, blocks from seg$ where type# = 3
ORA-01555 caused by SQL statement below (SQL ID: 1p36ta7p3d7fu, Query Duration=0 sec, SCN: 0x000c.f7ca7ee1):
select file#, block#, ts#, blocks from seg$ where type# = 3

Somehow not really bulletprof, this oracle falshback archive.
Anyway the real trouble started when i tried to drop the user and the flashback archive.


ALTER TABLE demo NO FLASHBACK ARCHIVE;

Worked, but leafes some tables named like TKRIM.SYS_FBA_HIST_* and SYS_FBA_TCRV_*.

At this stage it’s impossible to drop the Flashback archive tablepspace or the user tkrim aswell this tables.

After doing some hacks

SELECT o.object_id, o. owner, o.object_name, t.property
FROM dba_objects o, tab$ t
WHERE o.object_type = 'TABLE'
AND o.object_id = t.obj#
AND t.property = 9126805504;

UPDATE tab$
SET property = 536870912
WHERE property = 9126805504;

COMMIT;

I was able to drop the table SYS_FBA_TCRV_*.

But it was still impossible to drop the HIST Table.


SQL > drop table TKRIM.SYS_FBA_HIST_15517640;
drop table TKRIM.SYS_FBA_HIST_15517640
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktadrprc-1], [], [], [], [], [],
[], [], [], [], [], []

At the end I was not able to cleanup the flashback archive or the user.

Powered by WordPress