Files stored in
Flash Recovery Area (FRA)
Redo log file and control file are permit file resides in FRA remaining 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 an image copy in flash recovery area named flashback log.
(2)Control file:
The multiplexed copy of current control file will be stored in FRA
(3)Control file of Autobackups:
In RMAN setting "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 datafile or 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 Recovery 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
No comments:
Post a Comment