Testvorbereitung/Befehle

-------------------------------------------------------------------------------
--
-- Vorbereitung 
--
-------------------------------------------------------------------------------
-- DB-Versionen: 19.3. (pdb), zum Teil 10.2 sowie 11.1

create user scott identified by ***;
-- der Einfachheit halber:
grant dba to scott;
conn scott/***#@pdb1
set lines 200 trimspool on
col kommentar format a60
col current_scn format 999999999999
col undo_sql format a60
alter session set nls_date_format='dd.mm.yy hh24:mi:ss';

/*
drop table t1 cascade constraints;
drop table t2 cascade constraints;
*/

create table t1 (id number, datum date, kommentar varchar2(60) ) tablespace users;
create table t2 (id number, fid number, datum date, kommentar varchar2(60)) tablespace users;
alter table t1 add constraint p_t1_id primary key (id);
alter table t2 add constraint p_t2_id primary key (id);
alter table t2 add constraint f_t2_t1_id foreign key(fid) references t1(id);
insert into t1 values(1,sysdate,'initial');
insert into t1 values(2,sysdate,'initial');
insert into t1 values(3,sysdate,'initial');
insert into t2 values(1,1,sysdate,'initial');
insert into t2 values(2,1,sysdate,'initial');
insert into t2 values(3,2,sysdate,'initial');
commit;


select * from t1;
select * from t2;
exit;


-------------------------------------------------------------------------------
--
-- flashback table to before drop, aus Recyclebin:
--
-------------------------------------------------------------------------------
drop table t1;
show recyclebin;
flashback table t1 to before drop;
select * from t1;


-------------------------------------------------------------------------------
--
-- flashback query, aus Undo Tablespace
--
-------------------------------------------------------------------------------
update t1 set kommentar='kommentar geaendert fuer flashback query' where id=1;
commit;
select * from t1 as of timestamp to_timestamp('18.08.19 10:08','dd.mm.yy hh24:mi:ss');
update t1 set datum=sysdate,kommentar='initial' where id=1;
commit;


-------------------------------------------------------------------------------
--
-- flashback version query, aus Undo Tablespace
--
-------------------------------------------------------------------------------
update t1 set kommentar='kommentar geaendert fuer flashback version query' where id=1;
commit;
SELECT versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN, versions_operation OPERATION, id, kommentar from t1 versions between scn minvalue and maxvalue where id=1;


-------------------------------------------------------------------------------
--
-- flashback transaction version query, aus Undo Tablespace, add supplemental logging
--
-------------------------------------------------------------------------------
alter database add supplemental log data;
update t1 set datum=sysdate,kommentar='kommentar geaendert fuer flashback transaction version query' where id=1;
commit;
SELECT xid, start_scn , commit_scn, operation, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = hextoraw('09000B00E6010000');


-------------------------------------------------------------------------------
--
-- flashback transaction (backout), aus Undo Tablespace, add supplemental logging
--
-------------------------------------------------------------------------------
alter database add supplemental log data;
update t1 set datum=sysdate,kommentar='geaendert fuer flashback transaction backout, session 1' where id=1;
commit;
-- in zweiter Session:
update t2 set datum=sysdate,kommentar='geaendert fuer flashback transaction backout, session 2' where fid=1;
update t1 set datum=sysdate,kommentar='geaendert fuer flashback transaction backout, session 2' where id=1;
commit;
select * from t1;
select * from t2;
-- XIDs ermitteln (alternativ auch über Logminer möglich):
SELECT versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN, versions_operation OPERATION, id, kommentar from t1 versions between scn minvalue and maxvalue where id=1;

XID               START_SCN    END_SCN O         ID KOMMENTAR
---------------- ---------- ---------- - ---------- ------------------------------------------------------------
070014005D010000    2086382            U          1 geaendert fuer flashback transaction backout, session 2
09000B00E6010000    2086298    2086382 U          1 geaendert fuer flashback transaction backout, session 1
                               2086298            1 initial

--Zurückrollen des ersten Updates (=XID 09000B00E6010000):
-- als SYS-User in pdb!:
SET SERVEROUTPUT ON
DECLARE
  v_xid SYS.xid_array;
BEGIN
  v_xid := sys.xid_array('09000B00E6010000');
  DBMS_FLASHBACK.transaction_backout (numtxns => 1, xids => v_xid, options =>DBMS_FLASHBACK.cascade);
END;
/
commit;
select * from scott.t1;
select * from scott.t2;


-------------------------------------------------------------------------------
--
-- Flashback table, aus Undo Tablespace, enable row movement
--
-------------------------------------------------------------------------------
select * from scott.t1;
-- !!! nicht vergessen, Fehler gibts sonst erst bei der Wiederherstellung!!!:
alter table t1 enable row movement;
flashback table t1 to timestamp to_timestamp('18.08.19 17:05','dd.mm.yy hh24:mi:ss');
select * from scott.t1;

-------------------------------------------------------------------------------
--
-- Flashback Data Archive, aus separatem Archive Tablespace, enable row movement
--
-------------------------------------------------------------------------------
als SYS-User:
create flashback archive fda_default tablespace fda_ts retention 1 year no optimize data;
alter flashback archive fda_default set default;
-- !!! nicht vergessen, Fehler gibts sonst erst bei der Wiederherstellung!!!:
alter table t1 enable row movement;
alter table t1 flashback archive;
flashback table t1 to timestamp to_timestamp('18.08.19 17:24','dd.mm.yy hh24:mi:ss');
-- oder nach truncate:
truncate table t1;
flashback table t1 to timestamp to_timestamp('18.08.19 17:24','dd.mm.yy hh24:mi:ss');
					*
	ERROR at line 1:
	ORA-01466: unable to read data - table definition has changed
select * from t1  as of timestamp to_timestamp('18.08.19 17:24','dd.mm.yy hh24:mi:ss');

-------------------------------------------------------------------------------
--
-- Flashback Database - Nonguaranteed Restore Point or SCN, aus Flashbacklogs
--
-------------------------------------------------------------------------------
-- als SYS-User in Root-Container:
alter database flashback on
-- als SYS-User (hier für pdb (ab 12.2. moegl.)):
drop table scott.t1;
select * from scott.t1;
select * from v$flashback_database_log;
alter pluggable database pdb1 close;
flashback pluggable database pdb1 to scn 2125201;
alter pluggable database pdb1 open resetlogs;
select * from scott.t1;




-------------------------------------------------------------------------------
--
-- Flashback Database - Guaranteed Restore Point, aus Flashbacklogs
--
-------------------------------------------------------------------------------
-- kein Flashback-Modus benoetigt
-- als SYS-User (hier für pdb (ab 12.2. moegl.)):
select * from scott.t1;
create restore point restore_pdb1_20190818 guarantee flashback database;
drop table scott.t1 cascade constraints;
select * from scott.t1;
alter pluggable database pdb1 close;
flashback pluggable database pdb1 to restore point restore_pdb1_20190818;
alter pluggable database pdb1 open resetlogs;
select * from scott.t1;