UNDO_RETENTION:
select * from
v$parameter
where name = 'undo_retention'
Gives undo retention time
in seconds.
The
UNDO_RETENTION parameter can only be honored if the current undo tablespace has
enough space. If an active transaction requires undo space and the undo
tablespace does not have available space, then the system starts reusing
unexpired undo space. This action can potentially cause some queries to fail
with a "snapshot too old" message.
The
amount of time for which undo is retained for the Oracle Database for the
current undo tablespace can be obtained by querying the TUNED_UNDORETENTION
column of the V$UNDOSTAT dynamic performance view.
RETENTION GUARANTEE
clause for the UNDO table space guarantee the undo data is not overwritten.
Set UNDO_TETENTION by
changing the P file or by issuing the following command
ALTER SYSTEM SET UNDO_RETENTION = <seconds>;
DBMS_FLASHBACK:
In Oracle 11G Instance
DBMS_FLASHBACK.transaction_backout
is the new sub program added in 11g.
To
get SCN:
select dbms_flashback.get_system_change_number from dual
To
enable flashback:
EXEC DBMS_FLASHBACK.enable_at_system_change_number(13325367868729);
EXEC DBMS_FLASHBACK.enable_at_time('21-NOV-13 03:28:00');
To
disable flashback:
EXEC DBMS_FLASHBACK.disable;
How
to recover table data using SCN, TIMESTAMP:
create table emp_11 as select * from emp
select
dbms_flashback.get_system_change_number
from dual
13325367869040
Now, lets drop the table data
truncate table emp_11
select dbms_flashback.get_system_change_number
from dual
13325367869084
If DDL operations performed on the
table then we will get the following error.
ORA-01466:
unable to read data - table definition has changed
select * from
emp_11
as of scn 13325367869450
The above sql gives table data as of
the given SCN
select current_scn,systimestamp from
v$database
select * from
emp_20
as of timestamp TO_TIMESTAMP('21-11-2013 03:44:38', 'DD-MM-YYYY
HH:MI:SS')
Flashback
Version Query Row Data Pseudo columns:
Viewing
the data in a table across multiple timestamps:
create table emp_30 (empno varchar2(30), sal number);
insert into emp_30 values ('MAH', 100);
commit;
update emp_30 set sal = sal +13;
commit;
update emp_30 set sal = sal +40;
commit;
select current_scn, systimestamp
from v$database
13325367870050 21-11-2013 03:55:58.787005 -05:00
13325367870134 21-11-2013 03:56:33.899640 -05:00
13325367870147 21-11-2013 03:57:01.590055 -05:00
13325367870155 21-11-2013 03:57:15.211833 -05:00
13325367870222 21-11-2013 03:59:51.351191 -05:00
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
empno, sal
FROM emp_30 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('21-11-2013 03:55:58', 'DD-MM-YYYY HH:MI:SS')
AND TO_TIMESTAMP('21-11-2013
03:59:51', 'DD-MM-YYYY
HH:MI:SS')
Flashback transaction query can be used
to get extra information about the transactions listed by flashback version
queries. The VERSIONS_XID column values from a flashback version
query can be used to query the FLASHBACK_TRANSACTION_QUERY view.
Flashback Transaction Query
select * from
flashback_transaction_query
--where table_name = 'EMP_30'
where xid = HEXTORAW ('000E001E00090B71')
Flashback Table:
The FLASHBACK TABLE command allows point in time recovery of
individual tables subject to the following requirements.
·
You must have either the FLASHBACK ANY TABLE system privilege or have FLASHBACK object privilege on the table.
·
You must have SELECT, INSERT, DELETE, and
ALTER privileges on the table.
·
There must be enough information in the undo
tablespace to complete the operation.
·
Row movement must be enabled on the table (ALTER TABLE tablename ENABLE ROW MOVEMENT;).
create table emp21 as select * from emp where 1=11
select
current_scn from
v$database
13325367890251
13325367890262
select * from
emp21
insert into emp21 (select * from emp);
select count(*) from emp21 as of scn 13325367890251;
flashback table emp21 as of scn 13325367890482
gives error as ORA-08187: snapshot expression not allowed here
because the above syntax is wrong
flashback table emp21 to scn 13325367890482
ORA-08189:
cannot flashback the table because row movement is not enabled
alter table emp21 enable row movement
after altering the table to enable row movement then the
above flashback statement works fine.
Flashback Drop (Recycle Bin)
sql> purge recyclebin
2 /
Recyclebin purged.
sql> drop table emp21;
Table dropped.
sql> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
----------------
------------------------------ ------------ -------------------
EMP21 BIN$662Tc4VW3vzgRAAhKBTtkQ==$0
TABLE 2013-11-21:04:32:27
sql>
sql>
sql> flashback table
emp21 to before drop;
Flashback complete.
sql> show recyclebin;
sql>
sql>
sql>
sql> drop table emp21;
Table dropped.
sql> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------
------------ -------------------
EMP21 BIN$662Tc4VY3vzgRAAhKBTtkQ==$0
TABLE 2013-11-21:04:37:14
sql>
sql> purge recyclebin;
Recyclebin purged.
sql>
sql> flashback table
emp21 to before drop;
flashback table emp21 to
before drop
*
ERROR at line 1:
ORA-38305: object not in
RECYCLE BIN
PURGE TABLE tablename; -- Specific table.
PURGE INDEX indexname; -- Specific index.
PURGE TABLESPACE ts_name; -- All tables in a specific tablespace.
PURGE TABLESPACE ts_name USER username; -- All
tables in a specific tablespace for a specific user.
PURGE RECYCLEBIN; -- The current users entire recycle bin.
PURGE DBA_RECYCLEBIN;
-- The whole recycle
bin.
Restrictions to Recycle bin:
·
Only available for non-system, locally
managed tablespaces.
·
There is no fixed size for the recycle bin.
The time an object remains in the recycle bin can vary.
·
The objects in the recycle bin are restricted
to query operations only (no DDL or DML).
·
Flashback query operations must reference the
recycle bin name.
·
Tables and all dependent objects are placed
into, recovered and purged from the recycle bin at the same time.
·
Tables with Fine Grained Access policies aer
not protected by the recycle bin.
·
Partitioned index-organized tables are not
protected by the recycle bin.
·
The recycle bin does not preserve referential
integrity.
To enable/disable
recyclebin feature use the following commands:
-- Session level.
ALTER SESSION SET recyclebin = OFF;
ALTER SESSION SET recyclebin = ON;
-- System level.
ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = ON;
Flashback Database
The FLASHBACK
DATABASE command is a fast alternative to performing
an incomplete recovery. In order to flashback the database you must have SYSDBA privilege and the flash recovery area must
have been prepared in advance.
The FLASHBACK_ON column of the V$DATABASE view shows the current
status of flashback database.
If the database is in NOARCHIVELOG it must be switched to ARCHIVELOG mode.
CONN / AS SYSDBA
ALTER SYSTEM SET log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Flashback must be enabled before any flashback operations are
performed.
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE OPEN;
With flashback enabled the database can be switched back
to a previous point in time or SCN without the need for a manual incomplete
recovery. In the following example a table is created, the database is then
flashbacked to a time before the table was created.
-- Create a dummy table.
CONN
scott/tiger
CREATE TABLE
flashback_database_test (
id NUMBER(10)
);
-- Flashback 5 minutes.
CONN / AS SYSDBA
SHUTDOWN IMMEDIATE
STARTUP MOUNT EXCLUSIVE
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12);
ALTER DATABASE OPEN RESETLOGS;
-- Check that the table is gone.
CONN
scott/tiger
DESC flashback_database_test
Some other variations of the flashback database command
include.
FLASHBACK DATABASE TO TIMESTAMP my_date;
FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date;
FLASHBACK DATABASE TO SCN my_scn;
FLASHBACK DATABASE TO BEFORE SCN my_scn;
The window of time
that is available for flashback is determined by the
DB_FLASHBACK_RETENTION_TARGET parameter. The maximum flashback can be
determined by querying the V$FLASHBACK_DATABASE_LOG view. It is only possible
to flashback to a point in time after flashback was enabled on the database and
since the last RESETLOGS command.
In addition to using
SCNs and timestamps, we can create guaranteed restore points.
CREATE RESTORE POINT before_changes;
Existing restore
points can be displayed using the V$RESTORE_POINT view. To flashback to this
restore point, we would ssue the following command.
FLASHBACK DATABASE TO RESTORE POINT
before_changes;
Flashback Query Functions
The TIMESTAMP_TO_SCN
and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify
flashback operations.
SELECT *
FROM emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
SELECT *
FROM emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240);
DECLARE
l_scn
NUMBER;
l_timestamp
TIMESTAMP;
BEGIN
l_scn
:= TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/
How to get the code of dropped
package/procedure/function?
sql>
create procedure pxxxx as
2
begin
3
null;
4 end;
5 /
Procedure
created.
sql>
drop procedure pxxxx;
Procedure
dropped.
sql>
show recyclebin
Now,
if we observe the dropped procedure is permanently deleted. Its not there in
recyclebin. The reason is because the source of procedure/function/package
don’t have a separate database segment. They store in dba_source, user_source
tables.
To
retrieve the procedure we have to execute the following sql by connecting with
SYS.
select text from
dba_source
as of timestamp systimestamp - interval '5' minute
where name='PXXXX' order by line;
No comments:
Post a Comment