alter session set NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS';
alter session set NLS_DATE_FORMAT='DD-MM-YYYY:HH:MI:SS';
alter session set NLS_DATE_FORMAT='YYYY-MM-DD:HH:MI:SS';
Using Flashback Transaction Query and Flashback Version Query
Using TIMESTAMP
set line 400
col undo_sql format a60
SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid IN (
SELECT versions_xid
FROM agap.employees
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2017-09-16:14:59:27', 'YYYY-MM-DD HH24:MI:SS')
and
TO_TIMESTAMP('2017-09-16:15:00:06', 'YYYY-MM-DD HH24:MI:SS'));
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- --------------- ---------- ---------- --------------- ------------------------------------------------------------
01000F00AC020000 UPDATE 10257398 10257463 SYS update "AGAP"."EMPLOYEES" set "VALUE" = '3' where ROWID = 'A
AAFKqAAEAAAACEAAB';
01000F00AC020000 UPDATE 10257398 10257463 SYS update "AGAP"."EMPLOYEES" set "VALUE" = '2' where ROWID = 'A
AAFKqAAEAAAACEAAB';
01000F00AC020000 BEGIN 10257398 10257463 SYS
Using scn
set line 400
col undo_sql format a60
SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid IN (
SELECT versions_xid
FROM agap.employees
VERSIONS BETWEEN scn 10257444 and 10257467);
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- --------------- ---------- ---------- --------------- ------------------------------------------------------------
01000F00AC020000 UPDATE 10257398 10257463 SYS update "AGAP"."EMPLOYEES" set "VALUE" = '3' where ROWID = 'A
AAFKqAAEAAAACEAAB';
01000F00AC020000 UPDATE 10257398 10257463 SYS update "AGAP"."EMPLOYEES" set "VALUE" = '2' where ROWID = 'A
AAFKqAAEAAAACEAAB';
01000F00AC020000 BEGIN 10257398 10257463 SYS
You have xid (versions_xid) with Flashback Version Query -link
SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0200110003030000')
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- --------------- ---------- ---------- --------------- ------------------------------------------------------------
0200110003030000 UPDATE 10255635 10255636 SYS update "AGAP"."EMPLOYEES" set "VALUE" = '4' where ROWID = 'A
AAFKqAAEAAAACEAAB';
0200110003030000 BEGIN 10255635 10255636 SYS
SET RETENTION POLICY
This set ensures that you can recover the database to any point within the last week:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
SET REDUNDANCY
-How many backups of each datafile and control file that RMAN should keep
-The default retention policy is REDUNDANCY=1
-RMAN retains all archived logs and incremental backups that are needed to recover the nonobsolete backups
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
SHOW RETENTION
RMAN> SHOW RETENTION POLICY;
DISABLING RETENTION
RMAN>CONFIGURE RETENTION POLICY TO NONE;
WHEN FILE ELIGIBLE FOR DELETING FROM FLAS RECOVERY AREA
- Permanent files are never eligible for deletion.
- Files that are obsolete under the configured retention policy are eligible for deletion.
- Transient files that have been copied to tape are eligible for deletion.
- In a Data Guard environment, archived redo log deletion policy governs when archived redo log files can be deleted from the flash recovery area. See Oracle Data Guard Concepts and Administration for details on archived redo log deletion policy.
DEFAULT WARNING AT 15% AND CRITICAL 3%
YOU CAN SEE IN DBA_OUTSTANDING_ALERTS
the result is an database hang for space
