Санамсаргүй хүснэгт устгах, мэдээлэл устгах зэрэг үйлдлүүдэд зориулагдан түлхүү ашиглагддаг бөгөөд тохируулсан хугацаанд байсан мэдээллийг харах, сэргээх боломжийг олгоно.
FLASHBACK идэвхжүүлэхийн тулд дараахь коммандыг ашиглана.
SQL> -- recovery area мэдээллийг харах
SQL> set lin100
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .91 .91 9
BACKUP PIECE 96.2 35.06 21
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
SQL>
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> -- recovery area мэдээллийг шалгах
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .91 .91 9
BACKUP PIECE 96.2 35.06 21
IMAGE COPY 0 0 0
FLASHBACK LOG .2 0 1
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
SQL>
Recovery area нийт хэрэглээг харах
SQL>
SQL> SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest; 2 3 4 5 6 7
NAME SIZE_M USED_M PCT_USED
---------------------------------------- ---------- ---------- ----------
/u01/app/oracle/flash_recovery_area 3852 3749 98
SQL>
98% ашиглагдсан байгаа тул дараах коммандаар хэмжээг ихэсгэе.
SQL>
SQL> show parameter recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 3852M
recovery_parallelism integer 0
SQL> alter system set db_recovery_file_dest_size=10G ;
System altered.
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G
SQL>
Дараах жагсаалт бүхий төрлүүдээр жишээг бэлтгэсэн болно.
- QUERY
- VERSIONS
- TRANSACTION BACKOUT
- FLASHBAK TABLE
- FLASHBACK DATABASE
- COMMANDS
alter database add supplemental log data;
alter database flashback on;
select * from v$flash_recovery_area_usage;
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest;
grant dba to usr identified by usr;
conn usr/usr
--------QUERY-------------------
create table fb_query (name varchar2(20));
insert into fb_query values('testing...');
commit;
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate,dbms_flashback.get_system_change_number() SCN from dual;
select current_scn from v$database;
delete from fb_query;
commit;
select * from fb_query;
select * from fb_query as of timestamp to_timestamp('11-12-2016 19:46:41','dd-mm-yyyy hh24:mi:ss');
select * from fb_query as of scn
---------VERSIONS------------------
create table fb_versions (id number, name varchar2(20));
insert into fb_versions values(1,'testttttt');
select current_scn from v$database;
update fb_versions set name='version1' where id=1;
commit;
update fb_versions set name='version2' where id=1;
commit;
delete from fb_versions where id=1;
commit;
select current_scn from v$database;
select to_char(versions_starttime, 'hh24:mi:ss') start_time, to_char(versions_endtime, 'hh24:mi:ss') end_time, versions_xid xid, versions_operation vo, versions_startscn start_scn, versions_endscn end_scn, id, name from fb_versions versions between scn 1141401 AND 1141574;
col undo_sql format a30
col logon_user format a10
col operation format a10
col table_name format a10
col table_owner format a5
set pagesize 100
set linesize 150
select start_scn,start_timestamp, logon_user, operation, table_name, table_owner, undo_sql from flashback_transaction_query WHERE table_name='FB_VERSIONS' order by 1;
--------TRANSACTION BACKOUT ----------------------
create table fb_tr_backout (id number primary key, name varchar2(20));
insert into fb_tr_backout values(&a,'&b');
commit;
update fb_tr_backout set name='&a' WHERE id=&b;
commit;
delete from fb_tr_backout WHERE id=1;
#xid nemj songono
select xid,start_scn,start_timestamp, logon_user, operation, table_name, table_owner, undo_sql from flashback_transaction_query WHERE table_name='FB_TR_BACKOUT' order by 1;
#07001F00CA020000 id sergeeh scripts
declare
trans_arr xid_array;
begin
trans_arr := xid_array('09000B0012040000');
dbms_flashback.transaction_backout(
numtxns=>1,
xids=>trans_arr,
options=>dbms_flashback.cascade
);
end;
/
#manually commit
commit;
#flashback iin info harah
select * from user_flashback_txn_state;
---------------FLASHBAK TABLE---------------------
create table fb_table as select * from dba_objects;
#rowid uurchlogdoj bdag tul enable hiih
alter table fb_table enable row movement;
select current_scn from v$database;
delete from fb_table;
commit;
select count(*) from fb_table;
select count(*) from fb_table as of scn 1116787;
flashback table fb_table to scn 1116787;
select count(*) from fb_table;
drop table fb_table;
select owner, object_name, original_name from dba_recyclebin where original_name='FB_TABLE';
flashback table fb_table to before drop;
select owner, object_name, original_name from dba_recyclebin where original_name='FB_TABLE';
#herev ug table bgaad bval neriig solij sergeej bolno
flashback table mytable1 to before drop rename to mytable2;
----------------FLASHBACK DATABASE ----------------------
show parameter db_recovery;
#confirm archive log is enabled
archive log list;
#flashback database config
alter database flashback on;
select flashback_on from v$database;
create table fb_db (id number);
insert into fb_db values(1);
commit;
select current_scn from v$database;
drop table fb_db purge;
flashback table fb_db to before drop; ---> not in recyclebin because of purge;
select owner, object_name, original_name from dba_recyclebin where original_name='FB_DB';
shu immediate
startup mount;
flashback database to scn 1117384;
alter database open read only;
shu immediate
startup mount
alter database open resetlogs;
---------
create user fl_admin identified by fl_admin;
grant connect,resource, flashback archive administer to fl_admin;
create tablespace testtbs datafile '/db1/a.dbf' size 10m;
create flashback archive fl_archive tablespace testtbs retention 1year;
create table tbl_fl_archive (id number, name varchar2(20));
insert into tbl_fl_archive values(1,'testttt');
commit;
alter table tbl_fl_archive flashback archive fl_archive;
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') ddate from dual;
delete from tbl_fl_archive;
commit;
select * from tbl_fl_archive;
select * from tbl_fl_archive as of timestamp to_timestamp('17-12-2015 16:11:21','dd-mm-yyyy hh24:mi:ss');
conn / as sysdba
create undo tablespace undotbs2 datafile '/db1/undotbs2.dbf' size 100m;
alter system set undo_tablespace=undotbs2;
--------------COMMANDS------------------
select timestamp_to_scn(to_timestamp('DEC-27-16 14:10:33','MM-DD-YY HH24:MI:SS')) from dual;
SELECT
to_char(oldest_flashback_scn) ,
oldest_flashback_time,
ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 "ESTI_ GB",
RETENTION_TARGET/60/24 "days"
FROM v$flashback_database_log;
SELECT
SPACE_LIMIT/1024/1024 "limit MB",
SPACE_USED/1024/1024 "used MB",
SPACE_RECLAIMABLE,
NUMBER_OF_FILES
FROM v$recovery_file_dest;
#archive log uusseniig udruur harah
SELECT TO_CHAR(first_time,'DD-MON-YYYY') "Date",
SUM(bytes)/1024/1024/1024 "GB"
FROM (SELECT UNIQUE
blocks * block_size bytes,
thread#,
sequence#,
resetlogs_change#,
first_time
FROM v$archived_log)
GROUP BY TO_CHAR(first_time, 'DD-MON-YYYY')
ORDER BY 2 ASC;
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE WHERE PERCENT_SPACE_USED > 0 ;
--
-- List Flashback Log Details.
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN "Log NO" FOR 9,999
COLUMN "Thread No" FOR 99
COLUMN "Seq No" FOR 99
COLUMN name FOR A50
COLUMN "Size(GB)" FOR 999,999
COLUMN "First Chg No" FOR 999,999,999,999,999,999
ALTER SESSION
SET nls_date_format='DD MON YYYY hh24:mi:ss'
/
SELECT
log# as "Log No",
thread# as "Thread No",
sequence# as "Seq No",
name,
bytes/1024/1024/1024 as "Size(GB)",
first_change# as "First Chg No",
first_time
FROM
v$flashback_database_logfile
/
Зав багатай учир бэлтгэсэн жишээг шууд оруулав. Дараагийн удаад илүү дэлгэрүүлж засварлах болно.