-- Entries to add to database's INIT.ORA:
###########################################
# Flashback Backup and Recovery settings
###########################################
db_recovery_file_dest_size = 2G # See article for suggested sizing guidelines
db_recovery_file_dest = '/oracle/odata/db' # Should be a separate area
db_flashback_retention_target = 2880 # Will hold two days (2880 minutes) worth of Flashback
# Activate this to transmit an extra copy of archived redo logs to Flash Recovery Area
log_archive_dest_2 = 'location=use_db_recovery_file_dest'
log_archive_dest_state_2 = enable
-----
-- Setting up the Flash Recovery Area - open database
-----
-- Be sure to set DB_FILE_RECOVERY_DEST_SIZE first ...
ALTER SYSTEM SET db_file_recovery_dest_size = '5G' SCOPE=BOTH SID='*';
-- ... and then set DB_FILE_RECOVERY_DEST and DB_FLASHBACK_RETENTION_TARGET
ALTER SYSTEM SET db_file_recovery_dest = '/oracle/odata/db' SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_flashback_retention_target = 2880;
======
show parameter recovery
alter system set db_recovery_file_dest_size=10G;
System altered.
ALTER SYSTEM SET db_recovery_file_dest_size=10g scope=both;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area' scope=both;
alter system set DB_FLASHBACK_RETENTION_TARGET = 1440 scope=both;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
DGMGRL> EDIT DATABASE "orcl_stdy" SET STATE ='APPLY-OFF';
Succeeded.
[-PHYSICAL STANDBY-]sys@orcl_stdy> ALTER DATABASE FLASHBACK ON;
Database altered.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION="USE_DB_RECOVERY_FILE_DEST"' SCOPE=BOTH;
data guard environments
on prod
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION="USE_DB_RECOVERY_FILE_DEST" VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' SCOPE=BOTH;
on standby
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION="USE_DB_RECOVERY_FILE_DEST" VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl_stdy' SCOPE=BOTH;
SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/ORCL
total 0
drwxr-x---. 2 oracle oinstall 60 Aug 23 06:28 flashback
drwxr-x---. 3 oracle oinstall 24 Aug 23 06:35 autobackup
drwxr-x---. 3 oracle oinstall 24 Aug 23 06:49 archivelog
SQL>
=====
-----
-- Flash Recovery status queries
-----
-- What Flashback options are currently enabled for this database?
TTITLE 'Flashback Options Currently Enabled:'
COL name FORMAT A32 HEADING 'Parameter'
COL value FORMAT A32 HEADING 'Setting'
SELECT
name
,value
FROM v$parameter
WHERE NAME LIKE '%flash%' OR NAME LIKE '%recovery%'
ORDER BY NAME;
-- What's the status of the Flash Recovery Area?
TTITLE 'Flash Recovery Area Status'
COL name FORMAT A32 HEADING 'File Name'
COL spc_lmt_mb FORMAT 9999.99 HEADING 'Space|Limit|(MB)'
COL spc_usd_mb FORMAT 9999.99 HEADING 'Space|Used|(MB)'
COL spc_rcl_mb FORMAT 9999.99 HEADING 'Reclm|Space|(MB)'
COL number_of_files FORMAT 99999 HEADING 'Files'
SELECT
name
,space_limit /(1024*1024) spc_lmt_mb
,space_used /(1024*1024) spc_usd_mb
,space_reclaimable /(1024*1024) spc_rcl_mb
,number_of_files
FROM v$recovery_file_dest;
-- Is Flashback Database currently activated for this database?
TTITLE 'Is Flashback Database Enabled?'
COL name FORMAT A12 HEADING 'Database'
COL current_scn FORMAT 9999999 HEADING 'Current|SCN #'
COL flashback_on FORMAT A8 HEADING 'Flash|Back On?'
SELECT
name
,current_scn
,flashback_on
FROM v$database;
-- What's the earliest point to which this database can be flashed back?
TTITLE 'Flashback Database Limits'
COL oldest_flashback_scn FORMAT 999999999 HEADING 'Oldest|Flashback|SCN #'
COL oldest_flashback_time FORMAT A20 HEADING 'Oldest|Flashback|Time'
COL retention_target FORMAT 999999999 HEADING 'Oldest|Flashback|SCN #'
COL flashback_size FORMAT 999999999 HEADING 'Oldest|Flashback|Size'
COL estimated_flashback_size FORMAT 999999999 HEADING 'Estimated|Flashback|Size'
SELECT
oldest_flashback_scn
,oldest_flashback_time
,retention_target
,flashback_size
,estimated_flashback_size
FROM v$flashback_database_log;
-----
-- Configuring RMAN to use Flash Recovery Area
-----
RUN {
# Configure RMAN specifically to use Flash Recovery Area features
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
}
-----
-- RMAN Daily Backup Scheme Using Image Copies
-----
RUN {
###############################################################################
# RMAN Script: DailyImageCopyBackup.rcv
# Creates a daily image copy of all datafiles and Level 1 incremental backups
# for use by the daily image copies
###############################################################################
# Roll forward any available changes to image copy files
# from the previous set of incremental Level 1 backups
RECOVER
COPY OF DATABASE
WITH TAG 'img_cpy_upd';
# Create incremental level 1 backup of all datafiles in the database
# for roll-forward application against image copies
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'img_cpy_upd'
DATABASE;
}
-----
-- Results of First Daily Backup
-----
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
41 1 A 07-DEC-04 2119100 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_SYSTEM_0VDM2NP9_.DBF
1 1 A 20-NOV-04 2006057 20-NOV-04 /RMANBKUP
43 2 A 07-DEC-04 2119143 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_UNDOTBS1_0VDM6MRV_.DBF
48 3 A 07-DEC-04 2119180 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_DRSYS_0VDM9OP2_.DBF
44 4 A 07-DEC-04 2119156 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_EXAMPLE_0VDM7S0X_.DBF
46 5 A 07-DEC-04 2119173 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_INDX_0VDM94ON_.DBF
50 6 A 07-DEC-04 2119186 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_TOOLS_0VDMB270_.DBF
47 7 A 07-DEC-04 2119176 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_USERS_0VDM9F8W_.DBF
45 8 A 07-DEC-04 2119166 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_XDB_0VDM8N66_.DBF
51 9 A 07-DEC-04 2119189 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_LMPT1_0VDMB6CL_.DBF
49 10 A 07-DEC-04 2119184 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_LMPT3_0VDM9Y6J_.DBF
53 11 A 07-DEC-04 2119193 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_LMPT2_0VDMBGJN_.DBF
52 12 A 07-DEC-04 2119191 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_LMPT4_0VDMBBGW_.DBF
42 13 A 07-DEC-04 2119127 07-DEC-04 /oracle/odata/db/DATAFILE/O1_MF_SYSAUX_0VDM53DD_.DBF
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
148 1 203 A 05-DEC-04 /oracle/odata/db/ARCHIVE/ora002030010493846599.ARC
149 1 204 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002040010493846599.ARC
150 1 205 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002050010493846599.ARC
151 1 206 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002060010493846599.ARC
152 1 207 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002070010493846599.ARC
153 1 208 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002080010493846599.ARC
154 1 209 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002090010493846599.ARC
155 1 209 A 06-DEC-04 /oracle/odata/db/ARCHIVELOG/2004_12_06/O1_MF_1_209_0V9Q1HHJ_.ARC
160 1 210 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002100010493846599.ARC
161 1 210 A 06-DEC-04 /oracle/odata/db/ARCHIVELOG/2004_12_08/O1_MF_1_210_0VH53GGG_.ARC
156 1 210 A 06-DEC-04 /oracle/odata/db/ARCHIVE/ora002100010493846599.ARC
157 1 210 A 06-DEC-04 /oracle/odata/db/ARCHIVELOG/2004_12_07/O1_MF_1_210_0VDOMOGQ_.ARC
162 1 211 A 07-DEC-04 /oracle/odata/db/ARCHIVE/ora002110010493846599.ARC
163 1 211 A 07-DEC-04 /oracle/odata/db/ARCHIVELOG/2004_12_08/O1_MF_1_211_0VH53NS2_.ARC
158 1 211 A 07-DEC-04 /oracle/odata/db/ARCHIVE/ora002110010493846599.ARC
159 1 211 A 07-DEC-04 /oracle/odata/db/ARCHIVELOG/2004_12_07/O1_MF_1_211_0VDOPPDT_.ARC
164 1 212 A 07-DEC-04 /oracle/odata/db/ARCHIVE/ora002120010493846599.ARC
165 1 212 A 07-DEC-04 /oracle/odata/db/ARCHIVELOG/2004_12_08/O1_MF_1_212_0VH53V2V_.ARC
-----
-- Flashback Log Query
-----
-- What Flashback Logs are available?
TTITLE 'Current Flashback Logs Available'
COL log# FORMAT 9999 HEADING 'FLB|Log#'
COL bytes FORMAT 99999999 HEADING 'Flshbck|Log Size'
COL first_change# FORMAT 99999999 HEADING 'Flshbck|SCN #'
COL first_time FORMAT A24 HEADING 'Flashback Start Time'
SELECT
LOG#
,bytes
,first_change#
,first_time
FROM v$flashback_database_logfile;
