OCP 10g ch5 - Understanding the Flashback Database

Review Questions

1. What type of recovery is the Flashback Database best suited for?
(Choose all that apply.)
A. User error
B. Physical corruption
C. Logical corruption
D. Media failure
--
Ans. A, C.
A Flashback Database recovery is best suited for a user error such as a
truncated table or a logical corruption like an incomplete batch
transaction affecting many tables. Media recovery situations cannot be
performed with the Flashback Database recovery method.

2. Flashback Database recovery can recover from which of the following
failure
scenarios?
A. Loss of control file
B. Dropping a tablespace through RESETLOGS
C. A user error that resized datafiles to a smaller size
D. A large truncated table or group of tables
--
Ans: D.
A Flashback Database recovery can recover a large truncated table or
group of tables.

3. What new background process is responsible for writing before block
images and recovering from the Flashback Database log?
A. RWVR
B. RVWR
C. RWRV
D. RVRW
---
Ans B.
The RVWR process is responsible for writing the "before" image
information tothe Flashback Database log. The Flashback Database log is
read to perform theFlashback Database recovery.

4. What are the benefits of the flash recovery area in the recovery and
backupprocess?
A. Recovery efficiency is improved because all files are stored on tape
media for fast access.
B. Recovery efficiency is improved because the files are stored in
multiple
locations.
C. Recovery efficiency is improved because the files are stored in one
location on tape.
D. Recovery efficiency is improved because the files are stored in one
location on disk.
---
Ans D.
The flash recovery area is a centralized storage area for backups on
disk. This
allows for a more efficient recovery process because the required files
are in one
location and are stored on disk instead of tape.

5. Where is information about the status of the flash recovery area
displayed?
(Choose all that apply.)
A. Alert log
B. Background trace files
C. V$_OUSTANDING_ALERTS
D. DBA_OUTSTANDING_ALERTS
---
Ans: A, D.
The Alert log reports space usage and other information about the flash
recovery area. DBA_OUTSTANDING_ALERTS also show the information state of
the
flash recovery area.

6. How is the size of the flash recovery area determined? (Choose all
that apply.)
A. The size is automatically allocated at 2 gigabytes.
B. Using the ALTER SYSTEM command to dynamically set the size.
C. With the initialization parameter DB_RECOVERY_FILE_DEST_SIZE.
D. Using the ALTER TABLESPACE command.
---
Ans: B, C.
The flash recovery area can be determined by either setting the
initialization parameter DB_RECOVERY_FILE_DEST_SIZE or using the ALTER
SYSTEM command.

7. What type of backup commands can the flash recovery area be used for?
(Choose all that apply.)
A. BACKUP COPY
B. BACKUP IMAGE
C. BACKUP
D. BACKUPSET
---
Ans: A, C.
The flash recovery area supports both the BACKUP and BACKUP COPY
commands, which perform backup sets and image copies.

8. The flash recovery area space utilization and stored files can be
identified by what method?
A. DBA_OUTSTANDING_ALERTS
B. V$OUTSTANDING_ALERTS
C. V$RECOVERY_FILE_DEST
D. DBA_RECOVERY_FILE_DEST
---
Ans: C.
The dynamic view V$RECOVERY_FILE_DEST displays the space utilization and
the
amount of files that make up the flash recovery area.

9. What parameter determines the length of time that the Flashback
Database will store "before" images that can be used in the recovery
process?
A. DB_FLASHBACK_RETENTION_POLICY
B. DB_FLASHBACK_RETENTION_TIME
C. DB_FLASHBACK_RETENTION_STORE
D. DB_FLASHBACK_RETENTION_TARGET
--
Ans: D.
The DB_FLASHBACK_RETENTION_TARGET parameter determines how much data is
available to recover.

10. How is the DB_FLASHBACK_RETENTION_TARGET parameter measured?
A. By SCN
B. By redo log sequences
C. By time in minutes
D. By redo log sequence and threads
---
Ans: C.
DB_FLASHBACK_RETENTION_TARGET is a parameter that is measured in
minutes.
This value determines how many minutes the Flashback Database should
write data before this data gets overwritten.

11. To enable the Flashback Database, what must be done to the database?
(Choose all that apply.)
A. It must be mounted.
B. It must be opened with RESETLOGS.
C. The flash recovery area must be created.
D. The database must be in ARCHIVELOG mode.
---
Ans: A, C, D.
To enable the Flashback Database, the flash recovery area must be
created.
The database must be mounted but not opened to turn on the Flashback
Database.
The database must also be in ARCHIVELOG mode.

12. When using the Flashback Database in a recovery situation, what
information would be useful to know? (Choose all that apply.)
A. Information about the smallest SCN number that is stored in the
Flashback Database log
B. Information about the earliest timestamp that is stored in the
Flashback
Database log
C. Information about the greatest SCN number that is stored in the
Flashback Database log
D. Information about the latest timestamp that is stored in the
Flashback
Database log
---
Ans: A, B.
The earliest timestamp and smallest SCN will tell you how far back you
can recover the database. These values can be queried by the
V$FLASHBACK_DATABASE_LOG dynamic view.

13. How can you determine if the Flashback Database is turned on?
A. Query the DBA_FLASHBACK_DATABASE view.
B. Query the V$DATABASE dynamic view.
C. Check the initialization parameters.
D. Check the alert log.
---
Ans: B.
The V$DATABASE dynamic view has a new column called FLASHBACK_ON, which
contains a value of YES or NO.

14. Which of the following views can determine how much space you may
need in the future in the Flashback Database logs?
A. V$DATABASE
B. V$FLASHBACK_DATABASE_STAT
C. V$FLASHBCK_DATABASE_LOG
D. DBA_FLASHBACK_LOG
----
Ans: B.
The V$FLASHBACK_DATABASE_STAT dynamic view shows the daily growth and
utilization of the Flashback Database log. You can match daily
activities to the daily utilization of the Flashback Database log.

15. What is the default size of a redo log file created in the flash
recovery area?
A. 100MB
B. 150MB
C. 10MB
D. 50MB
---
Ans: A.
The default size of a redo log file created in the flash recovery area
is 100MB.

16. Which initialization parameter will not create archive logs to the
flash recovery area?
A. ARCHIVE_LOG_DEST
B. ARCHIVE_DUPLEX_DEST
C. ARCHIVE_LOG_DEST_n
D. LOG_ARCHIVE_DEST_n
---
Ans: D.
The LOG_ARCHIVE_DEST_n is the only initialization parameter that will
create or write archive logs to the flash recovery area.

17. Which database files are permanently stored in the flash recovery
area?
Choose all that apply.)
A. Datafiles
B. RMAN files
C. Control files
D. Current online redo logs
E. Archive logs
----
Ans: C, D.
Control files and redo logs area considered permanent files in the flash
recovery area. These files are not made obsolete and deleted, even when
backed up to tape.

18. Which files will not be backed up in the flash recovery area when
you're using the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES
commands?
(Choose all that apply.)
A. Control files
B. Redo logs
C. Datafiles
D. Permanent files
E. Flashback logs
---
Ans: A, B, D, E.
The flash recovery area will not back up redo log files, control files,
or
flashback logs with the BACKUP RECOVERY AREA and BACKUP RECOVERY FILES
commands. Permanent files are considered to be current online redo logs
and control
files.

19. What is responsible for applying the "before" images to the database
during a Flashback Database recovery?
A. LGWR
B. SMON
C. DBWR
D. RWVR
--
Ans: D.
The RWVR process is responsible for writing the "before" images to the
Flashback Database log. This process is also responsible for applying
these to the database during a recovery.

20. What administrative database activity cannot be undone with the
Flashback Database recovery?
A. Dropped table
B. Dropped user
C. Resized datafiles to smaller size
D. Dropped tablespace
---
Ans: C.
Resizing of a tablespace or datafiles to a smaller size cannot be undone
with Flashback Database recovery.

Flash Recovery Practice

Flash Recovery Practice
=========================
Env: Oracle 10gR2

1. Display init.ora parameter

sys@TOY10G> show parameter db_recover

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_recovery_file_dest string
/ora01/orabkup/GENQ/.temp_toy1

0g/ora01/orarcv/TOY10G
db_recovery_file_dest_size big integer 2G

2. Connect to target database

dfsdb1:TOY10G: /ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G >
rman

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Aug 14
08:54:08 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target

connected to target database: TOY10G (DBID=3330944552)

RMAN>

3. Check default backup location is same as db_recovery_file_dest
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
# default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; #
default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #
default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT
'/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/%rec_area_%s_%p.ba
k';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/oracle/product/10.2.0/db_1/dbs/snapcf_TOY10G.f'; # default

4. Create a dummy table t1 from all_objects

scott@TOY10G> create table t1 as select * from all_objects;

Table created.

scott@TOY10G> select count(*) from t1;

COUNT(*)
----------
9531

5. Perform a backup of target database

RMAN> backup as copy database;
Starting backup at 14-AUG-2007 09:25:12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=30 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001
name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf
output
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/%rec_area_
33_1.bak tag=TAG20070814T092512 recid=21 stamp=630581119
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003
name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf
output
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/%rec_area_
34_1.bak tag=TAG20070814T092512 recid=22 stamp=630581126
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004
name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf
output
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/%rec_area_
35_1.bak tag=TAG20070814T092512 recid=23 stamp=630581127
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005
name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf
output
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/%rec_area_
36_1.bak tag=TAG20070814T092512 recid=24 stamp=630581128
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002
name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf
output
filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/%rec_area_
37_1.bak tag=TAG20070814T092512 recid=25 stamp=630581129
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-AUG-2007 09:25:30

Starting Control File and SPFILE Autobackup at 14-AUG-2007 09:25:30
piece
handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autob
ackup/2007_08_14/o1_mf_s_630581130_3d3gttsp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-AUG-2007 09:25:32

6. Query the V$RECOVERY_FILE_DEST to determine if the SPACE_USED column
(verify we have backups)

sys@TOY10G> select * from v$recovery_file_dest;

NAME

------------------------------------------------------------------------
----------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G
2147483648 44187648 37781504 7

sys@TOY10G> ho ls -lh
/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G
total 2215666
-rw-r----- 1 oracle dba 500M Aug 14 09:25
%rec_area_33_1.bak
-rw-r----- 1 oracle dba 500M Aug 14 09:25
%rec_area_34_1.bak
-rw-r----- 1 oracle dba 46M Aug 14 09:25
%rec_area_35_1.bak
-rw-r----- 1 oracle dba 25M Aug 14 09:25
%rec_area_36_1.bak
-rw-r----- 1 oracle dba 10M Aug 14 09:25
%rec_area_37_1.bak
drwxr-x--- 3 oracle dba 512 Jun 12 11:04 TOY10G

7. Enable flash back feature

sys@TOY10G> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

7.1 Start the database in MOUNT mode:
7.2 Set the DB_FLASHBACK_RETENTION_TARGET parameter to the desired value
(in min).
sys@TOY10G> show parameter db_flash

NAME TYPE VALUE
------------------------------------ -----------
------------------------------
db_flashback_retention_target integer 1440

sys@TOY10G> alter system set db_flashback_retention_target=1445;

7.3. Enable the flashback capability:
sys@TOY10G> alter database flashback on;

7.4 Now the database can be opened for normal use:

sys@TOY10G> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

8. Drop table t1 in SCOTT schema

scott@TOY10G> drop table t1;

Table dropped.

9. performing a Flashback Database recovery to a SCN
9.1 Query the V$FLASHBACK_DATABASE_LOG view to retrieve the
OLDEST_FLASHBACK_SCN:
Login as SYS

sys@TOY10G> select oldest_flashback_scn, oldest_flashback_time from
v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIM
-------------------- --------------------
4235495 14-AUG-2007 09:45:02

9.2 shut down and start the database in MOUNT mode

9.3 issue the Flashback Database recovery from RMAN:
RMAN> connect target

connected to target database: TOY10G (DBID=3330944552, not open)

RMAN> flashback database to scn=4235495;

Starting flashback at 14-AUG-2007 10:19:48
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=45 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished flashback at 14-AUG-2007 10:19:52

9.4 Open the database with the RESETLOGS option, because you recovered
to a time prior to the current database:
sys@TOY10G> alter database open resetlogs;

Database altered.

10. Check table t1 now:

scott@TOY10G> select count(*) from t1;

COUNT(*)
----------
9531

statspack survival guide

http://www.akadia.com/services/ora_statspack_survival_guide.html

Table function - pipleined vs non-pipelined

Env: Oracle 10g

1. Create a table called ORDERS  as follows:

   create table orders (
      order_number,
      create_date,
      assign_date,
      close_date,
      region_id )
   as select
  object_id,
  created,
  last_ddl_time,
  last_ddl_time +7,
  mod(object_id,8)
      from dba_objects
   /

scott@ORCL> select count(*) from orders;

  COUNT(*)
----------
     50377
  
2. Create a non-pipelined table function:

CREATE TYPE order_date_o AS object (
   order_number number,
   date_type char(1),   -- O, A, C
   year  number,
   quarter number,
   month  number
)
/

create type order_date_t as table of order_date_o
/


CREATE OR REPLACE FUNCTION date_parse ( p_curs SYS_REFCURS
                  RETURN order_date_t AS
   v_order_rec orders%ROWTYPE;
   v_ret_val order_date_t := order_date_t( );
BEGIN
   -- for every order in the cursor ...
   LOOP
      FETCH p_curs INTO v_order_rec;
      EXIT WHEN p_curs%NOTFOUND;
      -- extend the array by 3 and populate with component
      -- orders creation, assignment and close date
      v_ret_val.EXTEND(3);
      v_ret_val(v_ret_val.LAST-2) :=
         order_date_o(v_order_rec.order_number,
         'O',
         TO_CHAR(v_order_rec.create_date,'YYYY'),
         TO_CHAR(v_order_rec.create_date,'Q'),
         TO_CHAR(v_order_rec.create_date,'MM'));
      v_ret_val(v_ret_val.LAST - 1) :=
         order_date_o(v_order_rec.order_number,
         'A',
         TO_CHAR(v_order_rec.assign_date,'YYYY'),
         TO_CHAR(v_order_rec.assign_date,'Q'),
         TO_CHAR(v_order_rec.assign_date,'MM'));
      v_ret_val(v_ret_val.LAST) :=
         order_date_o(v_order_rec.order_number,
         'C',
         TO_CHAR(v_order_rec.close_date,'YYYY'),
         TO_CHAR(v_order_rec.close_date,'Q'),
         TO_CHAR(v_order_rec.close_date,'MM'));
   END LOOP;
   RETURN(v_ret_val);
END;
/


3. Create a pipelined table function

CREATE OR REPLACE FUNCTION date_parse_pl ( p_curs SYS_REFCURSOR )
                RETURN order_date_t
                PIPELINED AS

v_order_rec orders%ROWTYPE;

BEGIN
-- for every order in the cursor...
LOOP
  FETCH p_curs INTO v_order_rec;
  EXIT WHEN p_curs%NOTFOUND;

  -- pipe out the components of the orders open date
  PIPE ROW(order_date_o(v_order_rec.order_number,
                         'O',
                         TO_CHAR(v_order_rec.create_date,'YYYY'),
                         TO_CHAR(v_order_rec.create_date,'Q'),
                         TO_CHAR(v_order_rec.create_date,'MM')));

  -- pipe out the components of the orders assign date
  PIPE ROW(order_date_o(v_order_rec.order_number,
                        'A',
                        TO_CHAR(v_order_rec.assign_date,'YYYY'),
                        TO_CHAR(v_order_rec.assign_date,'Q'),
                        TO_CHAR(v_order_rec.assign_date,'MM')));

  -- pipe out the components of the orders close date
  PIPE ROW(order_date_o(v_order_rec.order_number,
                        'C',
                        TO_CHAR(v_order_rec.close_date,'YYYY'),
                        TO_CHAR(v_order_rec.close_date,'Q'),
                        TO_CHAR(v_order_rec.close_date,'MM')));

END LOOP;  -- every order in the cursor

RETURN;

END;
/


4. Comparing of the execution time for the following two statments in different session::

(a)
SELECT *
   FROM TABLE(date_parse(CURSOR(SELECT * FROM orders)))
WHERE ROWNUM <=10;   scott@ORCL> /

ORDER_NUMBER D       YEAR    QUARTER      MONTH
------------ - ---------- ---------- ----------
          20 O       2005          3          8
          20 A       2005          3          8
          20 C       2005          3          9
          44 O       2005          3          8
          44 A       2005          3          8
          44 C       2005          3          9
          28 O       2005          3          8
          28 A       2005          3          8
          28 C       2005          3          9
          15 O       2005          3          8

10 rows selected.

Elapsed: 00:00:07.51

(b)
/* test pipelined table function */
SELECT *
   FROM TABLE(date_parse_pl(CURSOR(SELECT * FROM orders)))
WHERE ROWNUM <=10;  scott@ORCL> @date_parse_pl.tst

ORDER_NUMBER D       YEAR    QUARTER      MONTH
------------ - ---------- ---------- ----------
          20 O       2005          3          8
          20 A       2005          3          8
          20 C       2005          3          9
          44 O       2005          3          8
          44 A       2005          3          8
          44 C       2005          3          9
          28 O       2005          3          8
          28 A       2005          3          8
          28 C       2005          3          9
          15 O       2005          3          8

10 rows selected.
Elapsed: 00:00:00.04


0.04 vs 7.51
~

SQLPLUS sample login.sql

--- login.sql ---
define_editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) '@' substr( global_name, 1, decode( dot, 0,
length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name
);
set sqlprompt '&gname> '
set termout on

~
---- conn.sql ---
set termout off
connect &1
@login
set termout on