select flashback_on from v$database;
select * from v$restore_point;
RMAN> LIST RESTORE POINT ALL;
drop restore point Before_GT_conversion_15_dec;
select name, space_used/space_limit*100 perc from v$RECOVERY_FILE_DEST;
SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
select to_char(OLDEST_FLASHBACK_TIME,'DD-MM-YYYY HH24:MI:SS') from V$FLASHBACK_DATABASE_LOG;
set lines 300
select * from V$FLASH_RECOVERY_AREA_USAGE;
CREATE RESTORE POINT RSP_EE_MIG_KEEP GUARANTEE FLASHBACK DATABASE;
Oldest Flashback Time
select to_char(OLDEST_FLASHBACK_TIME,'DD-MM-YYYY HH24:MI:SS') from V$FLASHBACK_DATABASE_LOG;
Limitations of Flashback Database
Because Flashback Database works by undoing changes to the data files that exist at the moment when you run the command,
it has the following limitations:
- Flashback Database can only undo changes to a data file made by Oracle Database. It cannot be used to repair media failures,
or to recover from accidental deletion of data files.
You cannot use Flashback Database alone to retrieve a dropped data file. If you flash back a database to a time when a
dropped data file existed in the database, only the data file entry is added to the control file. You can only recover the
dropped data file by using RMAN to fully restore and recover the data file.
- If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded.
You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
- When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely
in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT
operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later use Flashback Database
to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with
block corruption after the Flashback Database operation completes.
- If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also,
perform a full or incremental backup of the affected data files immediately after any NOLOGGING operation to ensure
recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time
during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
- Only FLASHBACK DATABASE requires flashback to be ‘ON’ since only FLASHBACK DATABASE uses flashback logs in the flash_recovery_area. All other forms of FLASHBACK use the recycle bin and undo_tablespace.
Important info:
- You cannot ‘flashback table to before drop’ a table which has been created in the SYSTEM tablespace.
- The table is sent to the recyclebin only if it existed in some other tablespace other than SYSTEM tablespace
and that tablespace must be locally managed.
- When you drop a table, the objects are temporarily placed in a ‘recycle bin’ and still belong to the owner.
- The space used by recycle bin objects is never reclaimed unless there is space pressure.
- The space associated with the dropped object is not immediately reclaimable although it appears in the DBA_FREE_SPACE view.
- Query the dba_recyclebin view as SYS or just recyclebin as the user for information about the recycle bin.
- Flashback drop allows you to recover a dropped table.
Example.
Connect arjun/arjun
Create table tempp (col_1 number(10)) tablespace users;
Insert into tempp values (10);
1 row created.
SQL> drop table tempp;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEMPP BIN$AI2Gp/QiZsPgQAw5yUImEA==$0 TABLE 2005-09-12:10:49:16
SQL> flashback table tempp to before drop;
Flashback complete.
SQL> select * from tempp;
COL_1
———-
10
In case the table is created in the system tablespace :
SQL> show user
USER is “SYS”…..Default tablespace SYSTEM
SQL> create table arjun1 (col_1 number(10));
Table created.
SQL> drop table arjun1;
Table dropped.
SQL> select * from dba_recyclebin;
no rows selected
SQL> flashback table arjun1 to before drop;
flashback table arjun1 to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
Permanently drop without recycle bin
Drop table test purge;
QUERYING DROPPED TABLES
Dropped tables can be queried from the recycle bin. No DML or DDL operations are allowed on the table.
SQL> drop table tempp;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEMPP BIN$AI9AwvFRdf7gQAw5yUIsGA==$0 TABLE 2005-09-12:13:15:22
While querying the recycle bin, make sure the system generated table name is enclosed in double quotes.
SQL> select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0;
select * from BIN$AI9AwvFRdf7gQAw5yUIsGA==$0
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from ‘BIN$AI9AwvFRdf7gQAw5yUIsGA==$0’;
select * from ‘BIN$AI9AwvFRdf7gQAw5yUIsGA==$0’
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from “BIN$AI9AwvFRdf7gQAw5yUIsGA==$0”;
COL_1
———-
10
You cannot run any DML or DDL on dropped tables ….
SQL> Insert into tempp values(20);
Insert into tempp values(20)
*
ERROR at line 1:
ORA-00942: table or view does not exist
FLASHBACK TABLE TO A TIME IN THE PAST.
Firstly enable row movement for table test..
SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;
TIME : 08:00:00
SQL> select * from test;
SALARY
———-
5000
TIME :08:00:01
SQL> update test set salary =6000;
1 row updated.
SQL> select * from test;
SALARY
———-
6000
SQL> commit;
Commit complete.
Now flashback table to time 08:00:00
SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( ‘2005-09-13 08:00:00′,’YYYY-MM-DD HH24:MI:SS’);
Flashback complete.
SQL> SELECT * FROM TEST;
SALARY
———-
5000
Monitoring Fast Recovery Area Space Usage
How do I fix this ORA-19809 error?
Answer: The ORA-19809 error involves adjusting a parameter and bouncing your instance. The storage in db_recovery_file_dest is disk, not RAM. The db_recovery_file_dest is located instance-wide, you it is not specified for a specific instance:
In Windows: c:\oracle\app\oracle\flash_recovery_area\
In UNIX/Linux /u01/app/oracle/flash_recovery_area
The trick to increasing the limit for db_recovery_file_dest_size was to:
1 — Start the database, then mount the database (Do not open the database)
2 — Use RMAN to run a crosscheck on the archive logs and then update the RMAN catalog to delete the expired redo logs that I had manually deleted via the OS to free-up space in the disk filesystem directory. See these notes on using the RMAN crosscheck command.
The following scripts will check space utilization for db_recovery_file_dest_size:
col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;
The V$RECOVERY_AREA_USAGE view contains the percentage of disk quota used by different type of files, and the percentage of space that can be reclaimed by deleting files that are obsolete, redundant, or backed up to tape. Querying the V$RECOVER_AREA_USAGE view produces the following output.
SELECT * FROM V$RECOVERY_AREA_USAGE;
SELECT * FROM V$RECOVERY_FILE_DEST;
db_recovery_file_dest tips
Question: What does the db_recovery_file_dest parameter do?
Answer: The db_recovery_file_dest parameter defines the location of the Flash Recovery Area (FRA) and the db_recovery_file_dest parameter specifies the default location for the recovery area. The recovery area contains multiplexed copies of the following files:
— Control files
— Online redo logs
— Archived redo logs
— Flashback logs
— RMAN backups
When you use the db_recovery_file_dest parameter to specify the destination of your flash recovery area, you can use a directory, file system, or ASM disk group as your destination.
Dynamically Defining the Flash Recovery Area
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 2G SCOPE=BOTH
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘C:\ORACLE\RECOVERY_AREA’ SCOPE=BOTH
db_recovery_file_dest and control files
If you haven’t set the control_files parameter, Oracle will create the control files in various default locations, according to the following rules:
If you specify the db_create_online_log_dest_n parameter, Oracle will create an OMF-based control file in n number of locations, with the first directory holding the primary control file.
If you specify the db_create_file_dest and db_recovery_file_dest parameters, Oracle will create an OMF based control file in both of these locations.
If you just specify the db_recovery_file_dest parameter, Oracle will create an OMF-based control file in the flash recovery area only.
Specifying this parameter without also specifying the db_recovery_file_dest_size initialization parameter is not allowed.
Setting up a Flash Recovery Area
The recovery area is defined by setting two initialization parameters. These two parameters can be dynamically altered or disabled.
The db_recovery_file_dest_size sets the disk limit, expressed in bytes
The db_recovery_file_dest sets the location for the recovery area
Enabling a Flash Recovery Area
This statement sets the disk limit for recovery area to 100 GB:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G
This statement sets the recovery area destination:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/dba/backup/’;
Alter a Flash Recovery Area
This statement alters the size of the recovery area:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G;
Disabling a Flash Recovery Area
This statement disables a recovery area:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘ ’;
Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard?
Maybe you also use the Fast Recovery Area as archive log destination. That’s good practice! But did you ever check that it works as expected?
What I mean is this:
The archived logs that you don’t need are reclaimable by the FRA when space is needed
And the archived logs that are required for availability (standby or backup) are not deleted.
It’s not an easy thing to check because Oracle doesn’t show which archive log is reclaimable.
Only the total reclaimable space is shown in v$recovery_area_usage. But that is not sufficient to
validate which archivelog sequence is concerned. I’ll show you below a query that returns the reclaimable status from the archived logs. And you will see that until 12c the APPLIED ON ALL STANDBY does not work as expected. You’ve probably seen a FRA full at standby site and solved it by deleting archived logs. But this is not the right solution because the FRA is supposed to do that.
Let’s look at an example I encountered recently. The archivelog deletion policy is set correctly:
RMAN> show archivelog deletion policy;
RMAN configuration parameters for database with db_unique_name DATABASE_SITE2 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
This configuration, an 11g feature, allows to delete an archive log as soon as it is applied to all standby destinations. Note that it works if I manually do a ‘delete archivelog all;’ but I expect that the archivelogs in the FRA becomes reclaimable automatically.
Unfortunately, this is not the case and the FRA is growing:
SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG';
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG 61.11 43.02 467
Let’s check everything. We are on the standby database:
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
The archivelogs are going to the Fast Recovery Area:
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST, valid_for=(ALL_LOGFILES,
ALL_ROLES)
All archived logs are applied (we are in SYNC AFFIRM):
DGMGRL> show database 'DATABASE_SITE2';
Database - DATABASE_SITE2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
DATABASE
Database Status:
SUCCESS
Well, with that configuration, I expect that all archivelogs are reclaimable – except the current one.
Let’s investigate. V$RECOVERY_AREA_USAGE is an aggregate view. If we check its definition, we see that the reclaimable size comes from x$kccagf.rectype.
So I’ll use it in in conjunction with v$archived_log in order to give the detail about which archived logs are reclaimable:
SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
,count(*),min(sequence#),max(sequence#)
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES' and name is not null
group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/
APPLIED DELETED RECLAIMABLE COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES NO YES 429 5938 6366
YES NO NO 37 6367 6403
IN-MEMORY NO NO 1 6404 6404
The problem is there: Because of a bug (Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA) the archivelogs are not marked as reclaimable when the database is in mount mode.
The workaround is to execute dbms_backup_restore.refreshagedfiles. This is what must be scheduled (maybe daily) on the standby. It can be a good idea to do it at the same time as a daily ‘delete obsolete’, so here is the way to call it from RMAN:
RMAN> sql "begin dbms_backup_restore.refreshagedfiles; end;";
But I’ve found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag – even when there is no change.
Then, you can run a daily job that does CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY on your database, whatever the role is.
It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archivelog is deleted: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
This is a good way to prevent anyone from changing the configuration and keep it close to the backup scripts. At dbi services, we advise to keep the same configuration on all Data Guard sites for the same database so that a switchover can be done without any problem. For this reason, having a script that depends on the place where the backups are done is a better alternative than a configuration that depends on the database role.
Finally, here is the state of our reclaimable archivelogs after any of these solutions:
APPLIED DELETED RECLAIMABLE COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES NO YES 466 5938 6403
IN-MEMORY NO NO 1 6404 6404
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG 61.11 61.09 467
All applied archived logs are reclaimable and the FRA will never be full.
You can check the primary as well. Are you sure that Oracle will never delete an archived log that has not been backed up ? Check your deletion policy.
Here is the full query I use for that:
column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
,decode(rectype,11,'YES','NO') reclaimable,count(*)
,to_char(min(completion_time),'dd-mon hh24:mi') first_time
,to_char(max(completion_time),'dd-mon hh24:mi') last_time
,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/
This is the result on primary where the last archivelog backup has run around 21:00
APPLIED DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME LAST_TIME FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
NO YES 1 NO 277 15-jan 17:56 19-jan 09:49 5936 6212
NO NO 1 YES 339 19-jan 10:09 22-jan 21:07 6213 6516
NO NO 0 NO 33 22-jan 21:27 23-jan 07:57 6517 6549
That is fine according to my policy APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK
And here is the result on standby where the workaround job has run around 06:00 and redo apply is in SYNC
APPLIED DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME LAST_TIME FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
YES YES 0 NO 746 07-jan 13:27 17-jan 11:17 5320 6065
YES NO 0 YES 477 17-jan 11:37 23-jan 05:37 6066 6542
YES NO 0 NO 8 23-jan 05:57 23-jan 08:14 6543 6550
IN-MEMORY NO 0 NO 1 23-jan 08:15 23-jan 08:15 6551 6551
This is good for my policy APPLIED ON ALL STANDBY – except that because of the bug mentioned above, redo applied since 06:00 are not yet reclaimable.
Update SEP-17
When troubleshooting archivelog deletion policy issue, here is a better query which counts the number of backups for each sequence:
set linesize 200 pagesize 1000
column is_recovery_dest_file format a21
select
deleted,status,is_recovery_dest_file,thread#,min(sequence#),max(sequence#),min(first_time),max(next_time),count(distinct sequence#),archived,applied,backup_count,count("x$kccagf")
from (
select deleted,thread#,sequence#,status,name ,first_time, next_time,case x$kccagf.rectype when 11 then recid end "x$kccagf"
,count(case archived when 'YES' then 'YES' end)over(partition by thread#,sequence#) archived
,count(case applied when 'YES' then 'YES' end)over(partition by thread#,sequence#) applied
,sum(backup_count)over(partition by thread#,sequence#) backup_count
,listagg(is_recovery_dest_file||':'||dest_id,',')within group(order by dest_id)over(partition by thread#,sequence#) is_recovery_dest_file
from v$archived_log left outer join sys.x$kccagf using(recid)
) group by deleted,status,is_recovery_dest_file,thread#,archived,applied,backup_count
order by max(sequence#),min(sequence#),thread#,deleted desc,status;
With the following output:
DEL S IS_RECOVERY_DEST_FILE THREAD# MIN(SEQUENCE#) MAX(SEQUENCE#) MIN(FIRST MAX(NEXT_ COUNT(DISTINCTSEQUENCE#) ARCHIVED APPLIED BACKUP_COUNT COUNT("X$KCCAGF")
--- - --------------------- ---------- -------------- -------------- --------- --------- ------------------------ ---------- ---------- ------------ -----------------
NO A YES:1 1 3233 3233 23-JUN-17 23-JUN-17 1 1 0 1 1
NO A YES:1,NO:2 1 3234 5387 23-JUN-17 21-JUL-17 2154 2 1 1 2154
NO A YES:1,NO:2 1 5388 11596 21-JUL-17 10-OCT-17 6209 2 1 0 6208
NO A YES:1,NO:2 1 11597 11597 10-OCT-17 10-OCT-17
https://www.dbi-services.com/blog/archivelog-deletion-policy-for-standby-database-in-oracle-data-guard/
HOME