Flash Back Concepts

Files stored in Flash Recovery Area(FRA)

Redo log file and control file are permant file resides in FRA remainig file are deleted by FRA
become obsolete or already backed up to media

(1)Flashback logs:
if  Flashback enabled option enabled in the database. every changes in blocks belongs to the datafile
will be recorded as a image copy in flash recovery area named flashback log.

(2)Control file:
The multiplexed copy of current control file will e stored in FRA

(3)Control file of Autobackups:
In RMAN settin "CONFIGURE CONTROLFILE AUTOBACKUP ON" then it copies
(RMAN automatically backs up the control file and the current server parameter file)
(a) when a successful backup must be recorded in the RMAN repository
(b) when a structural change to the database affects the contents of the control file
(c) Whenever you (add /drop) ,changing the status in(online/offline/read only),  (rename/resize,/increase /decrease) the size of datafileor tablespace
(d) When adding a new online redo log or adding a new redo thread.

(4)Online Redo log files:
multiplexed copy of redo log file are allow to store in FRA

(5)Archived Redo log files
FRA allow store your archived redo log file but any space issue in FRA Rman automatically will delete the files 

Flash Recovey feature of 11g

SQL> desc v$flashback_database_log;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 OLDEST_FLASHBACK_SCN          NUMBER - (minimum SCN to which you can flash back the database)  
 OLDEST_FLASHBACK_TIME                  DATE   - (earliest time to which you can flash back the database)
 RETENTION_TARGET                        NUMBER - (how long flashback logs are retained, in minutes)
 FLASHBACK_SIZE                              NUMBER - (size of flashback logs as of now)
 ESTIMATED_FLASHBACK_SIZE                  NUMBER - ( The estimated size of the total flashback     logs retained to satisfy the retention target at the end of this time interval, shown in the column END_TIME)

***To calculate (using logs available in flash recovery area) how far to rewind the database to the previous states.

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE
-------------------- --------- ---------------- --------------
ESTIMATED_FLASHBACK_SIZE
------------------------
             1040830 21-SEP-12             1440       15941632
                       0
The value of the column OLDEST_FLASHBACK_SCN is 1040830, which indicates you can
flash back to the SCN up to that number only, not before that.

***(OR)

SQL> select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss') from v$flashback_database_log;

TO_CHAR(OLDEST_FL
-----------------
09/21/12 16:57:23

***To Calculate the total Amount of Logs Generated in Flashback Area at several timestamps
SQL> select * from v$flashback_database_stat order by begin_time;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
21-SEP-12 21-SEP-12        2998272    2785280    1826816                        0

***(OR)-- To get the exact BEGIN TIME AND END TIME of the log generation

SQL> alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss';
Session altered.

SQL> select * from v$flashback_database_stat order by begin_time;

BEGIN_TIME        END_TIME          FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- -------------- ---------- ---------- ------------------------
09/21/12 16:57:23 09/21/12 17:20:49        2998272    2785280    1876992                        0


***To get the individual Size of Logs Generated in Flashback Area at every period

SQL> select end_time, estimated_flashback_size from v$flashback_database_stat order by 1;


END_TIME          ESTIMATED_FLASHBACK_SIZE
----------------- -----------------------0
09/21/12 17:22:04                154224128
09/25/12 20:37:57                137567258
10/25/12 22:28:20                194224128
              

***To Calculate the total Space used by Logs in the Flash Recovery Area

SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_ RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
             1040830 09/21/12 16:57:23             1440       15941632                169648128


***Flashing Back a Database from "RMAN Prompt" (or) "SQL prompt"

***Presteps for FRA using "RMAN Prompt" (or) "SQL prompt"
1.To calculate (using logs available in flash recovery area) how far to rewind the database to the previous states.
SQL> select * from v$flashback_database_log;
2. rman target=/
3. RMAN> shutdown immediate (or) SQL> shutdown immediate
4. RMAN> startup mount      (or) SQL> startup mount

***Poststeps of FRA using "RMAN Prompt" (or) "SQL prompt"
1. RMAN> alter database open read only (or) SQL> alter database open read only
NOTE: to verify desired information available or againg follow the presteps of FRA THEN FOLLOW STEP 2
2.RMAN> alter database open resetlogs;  (or) SQL> alter database open resetlogs;
Note: always use open the database with resetlogs to create new incarnation of scn number

***Flashing Back a Database using specific point-in-time, specified by date and time
1.presteps of FRA
2.RMAN (or) SQL> flashback database to timestamp to_date('5/18/2007 00:00:00','mm/dd/yyyy hh24:mi:ss');
(OR)
RMAN> flashback database to time 'sysdate-2/60/24';
3.poststep of FRA


***Flashing Back a Database using specific SCN number
SQL> select current_scn from v$database;

1.prestep of FRA
2.RMAN (or) SQL> flashback database to scn 4587962;
3.poststep of FRA

***Flashing Back a Database using last Resetlogs operation

SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
1548632

1.prestep of FRA
2.RMAN> flashback database to before resetlogs;
3.poststep of FRA

***Flashing Back a Database using named Restore-Point
1.prestep of FRA
RMAN (or) SQL> flashback database to restore point restore_point1;
3.poststep of FRA

Note: restore point can be (create, create with guarantee, list, drop)
SQL> create restore point rp1;
SQL> create restore point rp2 guarantee flashback database;-- create guarantee RS-point
SQL> select * from v$restore_point order by 2,1; --- list the RS-point
SQL> drop restore point rp2;


Recovering a object from Recycle bin
SQL> show recyclebin
SQL> select * from user_recyclebin;
SQL> alter session set recyclebin = on; ---The recycle bin is enabled by default.
SQL> alter session set recyclebin = off;
SQL> alter system set recyclebin = off; ---To disable for the entire database (not recommended):
SQL> alter system set recyclebin = on;

SQL> purge recyclebin;-- remove all dropped objects from the recyclebin (system wide):


***Recovering table related objects( constraint, index, trigger, )
***Display the Constraints of the table:

SQL> select constraint_type, constraint_name from user_constraints where table_name = 'SAMPLE';
C CONSTRAINT_NAME
- -----------------------------
P BIN$sdlkolirkfjHDFsdfskjks==$0 ---- P(PRIMARY)
C BIN$584ASRDJGThTINjOISVM3Q==$0 ---- C(CHECK )

SQL> alter table accounts rename constraint "BIN$sdlkolirkfjHDFsdfskjks==$0" to pk_SAMPLE;
Table altered.

SQL> alter table accounts rename constraint "BIN$584ASRDJGThTINjOISVM3Q" to ck_SAMPLE_01;
Table altered.


***Display the Index of the table:

SQL> select index_name from user_indexes where table_name = 'SAMPLE';
INDEX_NAME
-----------------------------
BIN$sdlkolirkfjHDFsdfskjks==$0
BIN$584ASRDJGThTINjOISVM3Q==$0

SQL> alter index "BIN$sdlkolirkfjHDFsdfskjks==$0" rename to IN_SAMPLE_01;
Index altered.

SQL> alter index "BIN$584ASRDJGThTINjOISVM3Q==$0" rename to IN_SAMPLE_02;
Index altered.



***To check the index restored properly

Finally, make sure the indexes are in place and have correct names:
SQL> select index_name from user_indexes where table_name = 'SAMPLE';

INDEX_NAME
-----------------------------
IN_SAMPLE_01
IN_SAMPLE_02

***Display the trigger of the table:

SQL> select trigger_name from user_triggers;
TRIGGER_NAME
-----------------------------
"BIN$sdlkolirkfjHDFsdfskjks==$0"


***Rename triggers to their original names:

SQL> alter trigger "BIN$sdlkolirkfjHDFsdfskjks==$0" rename to tr_sample_01;
Trigger altered.

*Check the triggers now to make sure they are named as they were originally:
SQL> select trigger_name from user_triggers;
TRIGGER_NAME
-----------------------------
TR_SAMPLE_01

Identify and fix table fragmentation in Oracle 10g - how?

Table fragmentation – when?

If a table is only subject to inserts, there will not be any fragmentation.
Fragmentation comes with when we update/delete data in table.
The space which gets freed up during non-insert DML operations is not immediately re-used (or sometimes, may not get reuse ever at all). This leaves behind holes in table which results in table fragmentation.

To understand it more clearly, we need to be clear on how oracle manages space for tables.

“High water mark” of table actually defines the border line between (ever) used and unused (never) space. While performing full table scan, Oracle will always read the data up to HWM. And if there is lot of free space with-in HWM, that is read too, and hence degrading the performance of FTS.

Now lets see how to identify HWM, unused (never used) space and free space (used but deleted/updated) and then take a call whether the concerned table is candidate for a reorganization or not.

SQL> create table test as select * from dba_tables; -- Create a table

Table created.

SQL> analyze table test compute statistics; -- Analyze it

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
2 from user_tables where table_name='TEST'; -- The number of blocks used/free

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 1680

SQL> delete from test where owner='SYS'; --- Im deleting almost half the number of rows.

764 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics; -- Analyze it again

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"

2 from user_tables where table_name='TEST'; -- No difference in blocks usage

Ever Used Never Used Total rows
---------- ---------- ----------
49 6 916

PL/SQL procedure successfully completed.

Even though you deleted almost half the rows, the above shows that table HWM is up to 49 blocks, and to perform any FTS, Oracle will go up to 49 blocks to search the data. If your application is so-written that there are many FTS on this table, you may consider, reorganizing this table.


Reasons to reorganization

a) Slower response time (from that table)
b) High number of chained (actually migrated) rows.
c) Table has grown many folds and the old space is not getting reused.

Note: Index based queries may not get that much benefited by reorg as compared to queries which does Full table scan.

How to reorganize?

Before Oracle 10g, there were mainly 2 ways to do the reorganization of the table
a) Export, drop, and import.
b) Alter table move (to another tablespace, or same tablespace).

Oracle 10g provides us a new way of reorganizing the data.

Shrink command: This command is only applicable for tables which are tablespace with auto segment space management.

Before using this command, you should have row movement enabled.

SQL> alter table test enable row movement;
Table altered.

There are 2 ways of using this command.

1. Break in two parts: In first part rearrange rows and in second part reset the HWM.

Part 1: Rearrange (All DML's can happen during this time)

SQL> alter table test shrink space compact;
Table altered.

Part 2: Reset HWM (No DML can happen. but this is fairly quick, infact goes unnoticed.)

SQL> alter table sa shrink space;
Table altered.

2. Do it in one go:

SQL> alter table sa shrink space; (Both rearrange and restting HWM happens in one statement)
Table altered.

Few advantages over the conventional methods
1. Unlike "alter table move ..", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.

2. Its an online operation, So you dont need downtime to do this reorg.

3. It doesnot require any extra space for the process to complete.


Conclusion
Its a new 10g feature to shrink (reorg) the tables (almost) online which can be used with automatic segment space management.