/*
|| Oracle 10g RMAN Listing 2
||
|| Contains examples of new Oracle 10g FlashBack Recovery Area and
|| Flashback Database features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| FlashBack Recovery Area and Flashback Database features and should be
|| carefully proofread before executing it against any existing Oracle database || to insure that no potential damage can occur.
||
*/
 
-----
-- Listing 2.1: Setting up the Flash Recovery Area - closed database
-----

-- 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 = 'c:\oracle\fbrdata\zdcdb' # Should be a separate area of disk
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

----- 
-- Listing 2.2: 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 = 'c:\oracle\fbrdata\zdcdb' SCOPE=BOTH SID='*';
ALTER SYSTEM SET db_flashback_retention_target = 2880;

----- 
-- Listing 2.3: 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;


----- 
-- Listing 2.4: 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;
}

----- 
-- Listing 2.5: 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;
}

----- 
-- Listing 2.6: 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       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_SYSTEM_0VDM2NP9_.DBF
1       1    A 20-NOV-04       2006057    20-NOV-04       C:\RMANBKUP
43      2    A 07-DEC-04       2119143    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_UNDOTBS1_0VDM6MRV_.DBF
48      3    A 07-DEC-04       2119180    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_DRSYS_0VDM9OP2_.DBF
44      4    A 07-DEC-04       2119156    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_EXAMPLE_0VDM7S0X_.DBF
46      5    A 07-DEC-04       2119173    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_INDX_0VDM94ON_.DBF
50      6    A 07-DEC-04       2119186    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_TOOLS_0VDMB270_.DBF
47      7    A 07-DEC-04       2119176    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_USERS_0VDM9F8W_.DBF
45      8    A 07-DEC-04       2119166    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_XDB_0VDM8N66_.DBF
51      9    A 07-DEC-04       2119189    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT1_0VDMB6CL_.DBF
49      10   A 07-DEC-04       2119184    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT3_0VDM9Y6J_.DBF
53      11   A 07-DEC-04       2119193    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT2_0VDMBGJN_.DBF
52      12   A 07-DEC-04       2119191    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\DATAFILE\O1_MF_LMPT4_0VDMBBGW_.DBF
42      13   A 07-DEC-04       2119127    07-DEC-04       C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\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 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002030010493846599.ARC
149     1    204     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002040010493846599.ARC
150     1    205     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002050010493846599.ARC
151     1    206     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002060010493846599.ARC
152     1    207     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002070010493846599.ARC
153     1    208     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002080010493846599.ARC
154     1    209     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002090010493846599.ARC
155     1    209     A 06-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_06\O1_MF_1_209_0V9Q1HHJ_.ARC
160     1    210     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002100010493846599.ARC
161     1    210     A 06-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_08\O1_MF_1_210_0VH53GGG_.ARC
156     1    210     A 06-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002100010493846599.ARC
157     1    210     A 06-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_07\O1_MF_1_210_0VDOMOGQ_.ARC
162     1    211     A 07-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002110010493846599.ARC
163     1    211     A 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_08\O1_MF_1_211_0VH53NS2_.ARC
158     1    211     A 07-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002110010493846599.ARC
159     1    211     A 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_07\O1_MF_1_211_0VDOPPDT_.ARC
164     1    212     A 07-DEC-04 C:\ORACLE\ORADATA\ZDCDB\ARCHIVE\ZDC002120010493846599.ARC
165     1    212     A 07-DEC-04 C:\ORACLE\FBRDATA\ZDCDB\ZDCDB\ARCHIVELOG\2004_12_08\O1_MF_1_212_0VH53V2V_.ARC

----- 
-- Listing 2.7: 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;