My experience, findings and thoughts in my daily work with Oracle products
Luca Canali and ASM Metadata Internals
Published Wednesday, June 14, 2006 by Radoslav Rusinov.
In a recent technical paper Luca Canali explains how to find the mapping between physical storage, ASM allocation units, and database files by querying the undocumented X$ tables.
By using the information retrieved from X$KFFXP table he succeeded to:
Luca Canali is working in the IT Department of CERN - European Organization for Nuclear Research Geneva, Switzerland.
Under HAandPerf section of the PSS Group you can find more very interesting white papers and presentations authored by Luca. Links to some of them:
Another interesting link with lot of technical information:
DBA Area of the Physics Database Services Support Group
Permanent Link @ 0 Comments on This Post @ Comment Here
By using the information retrieved from X$KFFXP table he succeeded to:
- Retrieve a file directly from ASM disks
- Monitor how the extents in a datafile are allocated across ASM disks
- Monitor how a datafile is spread across ASM disks due rebalancing operations
Luca Canali is working in the IT Department of CERN - European Organization for Nuclear Research Geneva, Switzerland.
Under HAandPerf section of the PSS Group you can find more very interesting white papers and presentations authored by Luca. Links to some of them:
- I/O measurements with Orion - I/O performance measurements using Oracle's ORION testing tool
- I/O Performance Measurements with RAC - Oracle 10g RAC and ASM on Linux - storage performance measured with Oracle's SQL
- Experience with RAC/Linux on no-brand hardware
- ASM-based storage to scale out the Database Services for Physics
- Oracle 10gR2 configuration
Another interesting link with lot of technical information:
DBA Area of the Physics Database Services Support Group
Labels: Link
Permanent Link @ 0 Comments on This Post @ Comment Here
Christo Kutrovsky's Blog
Published Sunday, June 11, 2006 by Radoslav Rusinov.
If you’ve missed to notice – Christo Kutrovsky started his own professional blog dedicated mainly to the Oracle database.
Christo Kutrovsky is an Bulgarian Oracle professional currently working inCanada
His blog is a part of the corporate blog of The Pythian Group.
The blog is only two-months old but already has several very good and detailed technical posts:
The blog is still not included in the two most popular Oracle Aggregators: OraBlogs and OraNA :: Oracle News Aggregator but I hope this will change soon.
Permanent Link @ 0 Comments on This Post @ Comment Here
Christo Kutrovsky is an Bulgarian Oracle professional currently working in
His blog is a part of the corporate blog of The Pythian Group.
The blog is only two-months old but already has several very good and detailed technical posts:
- Traceless DB Time Diagnosis -
Discusses a method of how to find where the database is spending its time for a specific process/operation without using of sql_trace. - Quantifying Commit Time -
Discusses how new 10g commit parameters "batch" and "nowait" can affect performance and how they can be used for measuring of the average commit time. - ASM multi-disk performance -
Very interesting post about how different disk configurations using ASM can affect performance of the I/O operations. This post has already been mentioned by Doug Burns in his blog post Blog Round-Up. - Oracle 10g R2 RAC - vip on wrong interface -
Discusses how to change used network interface for VIPs or private interconnect interface in a 10g RAC environment.
The blog is still not included in the two most popular Oracle Aggregators: OraBlogs and OraNA :: Oracle News Aggregator but I hope this will change soon.
Labels: Link
Permanent Link @ 0 Comments on This Post @ Comment Here
The Flashback Database Feature
Published Thursday, May 25, 2006 by Radoslav Rusinov.
Introduction
The Flashback Database is one of the most powerful new features in Oracle 10g coming as a part of Backup & Recovery enhancements.
In this post you can find the result of my research and shared experience about this feature.
What is the Oracle Flashback Database?
Flashback Database is a part of the backup & recovery enhancements in Oracle 10g Database that are called Flashback Features.
The main purpose of Flashback Technology is to let you quickly recover from logical corruptions or user errors.
Oracle Flashback Features include the following:
What says Oracle’s documentation about the Flashback Database Feature:
Oracle Database Backup and Recovery Basics -
10g Release 2 (10.2) - Chapter 5.1 - Restore Points and Flashback Database: Concepts
According to many studies and reports, Human Error accounts for 30-35% of data loss episodes. This makes Human Errors one of the biggest single causes of downtime.
With Flashback Database feature Oracle is trying to fight against user and operator errors in an extremely fast and effective way.
You can find more info about most common causes of data loss in the following sources:
For more info about the recovery speed advantages of Flashback Database, you can use the following source:
Oracle Flashback Technology, an Oracle article by Ron Weiss, Director of Product Management in Oracle's Database Kernel Development Group
Flashback Database provides:
I will try to discuss all of them in the sections below.
The Flashback Database is not just your database “rewind” button. It is a “Time Machine” for your Database data that is one single command away from you.
The Flashback Database Architecture
Flashback Database uses its own type of log files, called Flashback Database Log Files.
To support this mechanism, Oracle uses new background process called RVWR (Recovery Writer) and a new buffer in the SGA, called Flashback Buffer.
The Oracle database periodically logs before images of data blocks in the flashback buffer. The flashback buffer records images of all changed data blocks in the database. This means that every time a data block in the database is altered, the database writes a before image of this block to the flashback buffer. This before image can be used to reconstruct a datafile to the current point of time.
The maximum allowed memory for the flashback buffer is 16 MB. You don’t have direct control on its size. The flashback buffer size depends on the size of the current redo log buffer that is controlled by Oracle. Starting at 10g R2, the log buffer size cannot be controlled manually by setting the initialization parameter LOG_BUFFER.
In 10G R2, Oracle combines fixed SGA area and redo buffer together. If there is a free space after Oracle puts the combined buffers into a granule, that space is added to the redo buffer. The sizing of the redo log buffer is fully controlled by Oracle. According to SGA and its atomic sizing by granules, Oracle will calculate automatically the size of the log buffer depending of the current granule size. For smaller SGA size and 4 MB granules, it is possible redo log buffer size + fixed SGA size to be multiple of the granule size. For SGAs bigger than 128 MB, the granule size is 16 MB.
You can see current size of the redo log buffer, fixed SGA and granule by querying the V$SGAINFO view.
You can query the V$SGASTAT view to display detailed information on the SGA and its structures.
To find current size of the flashback buffer, you can use the following query:
SQL> SELECT * FROM v$sgastat WHERE NAME = 'flashback generation buff';
There is no official information from Oracle that confirms the relation between 'flashback generation buff' structure in SGA and the real flashback buffer structure. This is only a suggestion.
A similar message message is written to the alertSID.log file during opening of the database:
For more info about the granule model in the SGA (available since Oracle9i), see Metalink Note: 148495.1: Oracle9i New Feature: Dynamic SGA
The flashback buffer size is at least two times bigger than the log buffer size. This assertion can be found in OCP Oracle Database 10g : New Features for Administrators by Sam Alapati
Although flashback buffer size is expected to be at least two times bigger than log buffer size, its real size can be smaller. In tested 10g R2 environment, flashback buffer has around two times smaller size as the redo log buffer.
RVWR writes periodically flashback buffer contents to flashback database logs. It is an asynchronous process and you don’t have control over it. All available sources are saying that RVWR writes periodically to flashback logs. The explanation for this behavior is that Oracle is trying to reduce the I/O and CPU overhead that can be an issue in many production environments.
The RVWR writing mechanism and its behavior needs more investigation that will not be discussed here. It can be seen that it is controlled by few hidden parameters in a similar way like writing to redo log files – depending of generated flashback redo and time interval.
Flashback log files can be created only under the Flash Recovery Area (that must be configured before enabling the Flashback Database functionality). RVWR creates flashback log files into a directory named “FLASHBACK” under FRA. The size of every generated flashback log file is again under Oracle’s control. According to current Oracle environment – during normal database activity flashback log files have size of 8200192 bytes. It is very close value to the current redo log buffer size. The size of a generated flashback log file can differs during shutdown and startup database activities. Flashback log file sizes can differ during high intensive write activity as well.
Flashback log files can be written only under FRA (Flash Recovery Area). FRA is closely related and is built on top of Oracle Managed Files (OMF). OMF is a service that automates naming, location, creation and deletion of database files. By using OMF and FRA, Oracle manages easily flashback log files. They are created with automatically generated names with extension .FLB. For instance, this is the name of one flashback log file: O1_MF_26ZYS69S_.FLB
By its nature flashback logs are similar to redo log files. LGWR writes contents of the redo log buffer to online redo log files, RVWR writes contents of the flashback buffer to flashback database log files. Redo log files contain all changes that are performed in the database, that data is needed in case of media or instance recovery. Flashback log files contain only changes that are needed in case of flashback operation. The main differences between redo log files and flashback log files are:
There is a new record section within the control file header that is named FLASHBACK LOGFILE RECORDS. It is similar to LOG FILE RECORDS section and contains info about the lowest and highest SCN contained in every particular flashback database log file
When you perform a flashback operation, Oracle needs all flashback logs from now on to the desired time. They will be applied consecutively starting from the newest to the oldest.
For instance, if you want to flashback the database to SCN 4123376440, Oracle will read flsahback logfile section in control file and will check for the availability of all needed flashback log files. The last needed flashback log should be this with Low scn and High scn values between the desired SCN 4123376440.
In current environment this is the file with name: O1_MF_26YSTQ6S_.FLB and with values of:
The reason for this: Flashback log files contain information about before-images of data blocks, related to some SCN (System Change Number). When you perform flashback operation to SCN 4123376440, Oracle cannot apply all needed flashback logs and to complete successfully the operation because it applying before-images of data. Oracle needs to restore each data block copy (by applying flashback log files) to its state at a closest possible point in time before SCN 4123376440. This will guarantee that the subsequent “redo apply” operation will forward the database to SCN 4123376440 and the database will be in consistent state. After applying flashback logs, Oracle will perform a forward operation by applying all needed archive log files (in this case redo information from the file: ARC00097_0587681349.001) that will forward the database state to the desired SCN.
Oracle cannot start applying redo log files before to be sure that all data blocks are returned to their state before the desired point in time. So, if desired restore point of time is 10:00 AM and the oldest restored data block is from 09:47 AM then you will need all archived log files that contain redo data for the time interval between 09:47 AM and 10:00 AM. Without that redo data, the flashback operation cannot succeed.
A short explanation of this issue can be found in the documentation:
Oracle Database Backup and Recovery Basics -
10g Release 2 (10.2) - Chapter 5.1 - Restore Points and Flashback Database: Concepts
There is another source that is trying to explain this behavior in a comprehensible way:
OCP Oracle Database 10g: New Features for Administrators – Chapter 9: Flashback Enhancements, by Sam Alapati
Flashback logs are not independent. They can be used only with the redo data that contains database changes around the desired SCN. This means that if you want to have working flashback window (and to be able to restore the database to any point in time within this window) you need to ensure the availability of redo logs as well.
If you are familiar with this information then you will be able to work in a better way with this feature and to ensure that it will help you to perform faster recovery without unexpected problems.
Availability within Oracle’s Editions
Oracle Flashback Database feature is available only in Oracle Enterprise and Oracle Personal Editions.
For more info, see Metalink Note: 271886.1: Differences Between Different Editions of Oracle Database 10G
How to Configure Flashback Database?
1. Before to configure Flashback Database, you must ensure that:
2. Consider the value for flashback database window.
It can be set by an initialization parameter: DB_FLASHBACK_RETENTION_TARGET. The range of SCNs for which there is currently enough flashback log data to support the FLASHBACK DATABASE command is called the Flashback Database Window.
The flashback window depends of the current environment requirement but usually it can be between 24 and 72 hours. Don’t consider too big values for flashback window because if you need to perform flashback database operation to a point in time older than several days it can be slower and much more time-consuming operation than using of media recovery method.
Flashback Database is efficient and fast for smaller time spans when the error is immediately (or within few hours) discovered and performing of a Database Point-in-Time Recovery is the only one way to recover from this human error.
Default value for DB_FLASHBACK_RETENTION_TARGET is 1440. It is measured in minutes, so by default flashback window is 24 hours.
Keep in mind that flashback logs use additional space within the FRA. For more info about sizing of FRA to include Flashback Logs, see:
Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 5.3 Setup and Maintenance for Oracle Flashback Database - Sizing the Flash Recovery Area to Include Flashback Logs
3. Connect as user SYS with SYSDBA privileges.
4. Configure DB_FLASHBACK_RETENTION_TARGET:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;
5. Shut down the database:
SQL> SHUTDOWN IMMEDIATE
6. Start the database in MOUNT stage.
To enable Flashback Database feature, your database must be in MOUNT mode.
SQL> STARTUP MOUNT
For RAC environments, use:
SQL> STARTUP MOUNT EXCLUSIVE
7. Enable Flashback Database Feature:
SQL> ALTER DATABASE FLASHBACK ON;
8. Check whether the Flashback Database feature is enabled or not:
SQL> SELECT flashback_on FROM V$DATABASE;
9. Open the database with enabled Flashback Database feature:
SQL> ALTER DATABASE OPEN;
Configuration with Enterprise Manager:
The Flashback Database Feature can be enabled via Enterprise Manager as well.
Go to: Maintenance Tab -> Backup/Recovery Settings -> Recovery Settings
Excluding Tablespaces from Flashback Logging
By default, flashback logs are generated for all permanent tablespaces. If you want, you can disable flashback logging for specific tablespaces.
For example, if you want disable flashback generation for specific tablespace you can use the following command:
SQL> ALTER TABLESPACE USERS FLASHBACK OFF;
If you want to re-enable flashback logging for this tablespace, you can do it only in MOUNT stage.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER TABLESPACE USERS FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
During a Flashback Operation you must use more actions to handle with missing flashback data for these tablespaces. For more info, see section: Dealing with Excluded Tablespaces During Flashback Operations
Using of Flashback Database
Database can be flashed back either through SQL*Plus or RMAN.
1. Ensure that you are not dealing with some kind of media failure. The Flashback Database is useful only when your data is logically corrupted.
2. Find the desired point in time for the flashback operation. Usually it is he time just before the time when some human error caused a logical damage of the database data. You can calculate this as SCN or a Date (TIMESTAMP expression). Additionally, you can use a restore point or a time just before last RESETLOGS operation. A Log Sequence Number can be used in 10g R1 but it is not available as an option in 10g R2 (via SQL*Plus). It is still available as an option in RMAN.
3. Find the current oldest SCN and time to which the database can be backwarded:
SQL> SELECT oldest_flashback_scn,oldest_flashback_time FROM v$flashback_database_log;
If the desired time is after the oldest time currently recorded in the flashback log files then go to the next step
4. Ensure that there are not tablespaces with disabled flashback logging:
SQL> SELECT NAME tbs_name,file_name db_file,flashback_on fb_logging, online_status status FROM v$tablespace,dba_data_files WHERE NAME=tablespace_name;
If there are files with no important data that are excluded from flashback logging then you can follow steps from the section: Dealing with Excluded Tablespaces in case of Flashback Database Operations
Otherwise, go to the next step.
5. Ensure that all needed archived and redo logs (covering the whole time period between oldest_flashback_time column value and the time of failure) are available:
5.1 Check physical availability:
RMAN> CROSSCHECK ARCHIVELOG FROM SCN oldest_flashback_scn;
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
5.2 Check logical availability:
RMAN> REPORT UNRECOVERABLE;
REPORT UNRECOVERABLE command reports all datafiles that cannot be recovered from existing backups because redo may be missing.
You can check the logical availability of archived redo logs with the following SQL as well:
SQL> SELECT * FROM v$archived_log WHERE first_change# > = oldest_flashback_scn AND status = 'A';
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
6. Find and write down the current SCN (or create a normal restore point if you are using 10g R2). Don’t forget that the Flashback Database is a change on the database and it must be recorded if some failure occurs during this operation.
SQL> SELECT current_scn FROM v$database;
or
SQL> CREATE RESTORE POINT before_flashback_operation;
To map a time value and SCN, you can use the built-in SQL functions (available since 10g):
SCN_TO_TIMESTAMP – to find associated timestamp with this SCN
TIMESTAMP_TO_SCN – to find associated SCN with this timestamp
SQL> SELECT current_scn, scn_to_timestamp(current_scn) FROM v$database;
This timestamp mapping information is recorded in the SMON_SCN_TIME table.
Oracle keeps the information in SMON_SCN_TIME table for a period of 5 days.
7. Restart the database in MOUNT stage
7.1 SQL> SHUTDOWN IMMEDIATE
7.2 SQL> STARTUP MOUNT
For RAC environments, use:
SQL> STARTUP MOUNT EXCLUSIVE
If you want to be able to return the database to its state just as it was closed, you can create a restore point at this moment (restore points can be created even in MOUNT stage):
SQL> CREATE RESTORE POINT just_after_shutdown;
Or to find the last checkpointed SCN:
SQL> SELECT checkpoint_change# FROM v$database;
In the next step, if you perform a few flashback operations and you need to return the database to its state just before to be closed, you will need the above SCN.
8. Flashback the Database
For this example the point of time before the logical corruption is:
Different types of usage:
8.1. Flashes back the database to a point in time just as in the specified SCN:
SQL> FLASHBACK DATABASE TO SCN 4125962274; /* returns database to its state as in the point in time marked with SCN 4125962274 */
8.2. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of custom TIMESTAMP value):
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi'); /* returns database to its state as in the point in time 24.05.2006 15:10 */
8.3. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of SYSTIMESTAMP)
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP -1/24); /* returns database to its state as in the point in time one hour ago */
8.4. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of SYSTIMESTAMP and INTERVAL):
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE); /* returns database to its state as in the point in time one minute ago */
8.5. Flashes back the database to a point in time just as in the specified restore point
SQL> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; /* returns database to its state as in the point in time marked with the restore point with name before_flashback_operation */
8.6. Flashbacks the database to the point in time just before the last RESETLOGS operation
SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;
8.7. Flashes back the database to a SCN just before the specified SCN
SQL> FLASHBACK DATABASE TO BEFORE SCN 4125962274;
8.8. Flashes back the database to a point in time one second before the specified TIMESTAMP
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi');
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSDATE -1/24); SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);
8.9. Flashes back the database to a point in time just before the specified restore point
SQL> FLASHBACK DATABASE TO BEFORE RESTORE POINT before_flashback_operation;
8.10. Flashing back of the database via RMAN
RMAN> FLASHBACK DATABASE TO SCN 4125962274; RMAN> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); RMAN> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi');
RMAN> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;
Let’s demonstrate the ‘Time Machine’ capabilities of this feature:
As it can be seen, the Flashback feature allows you to shift within the time as it doesn’t exist. In some articles it is called a “rewind” button but its abilities as a “forward” button make this feature to behave as a “Time Machine” device for your data.
I want to note here again that you can move forward in time only with applying of redo logs. You can backward in time by applying flashback logs along with smaller amount of redo data.
Be aware of that you will be able to travel through the time only if you are well prepared for it. You must have all flashback and archived redo logs for the whole time period. With the Flashback Database you can return database to its state as in every point in time in an extremely fast way. The speed of this operation depends only of the number of the applied flashback logs and this mainly depends of the number of the data block changes within the database for this time period. It doesn’t depend on the database size because no restore operation is performed. Usually this operation can complete even within several seconds or minutes and can be between 25 to 105 times faster than usual incomplete media recovery.
Flashback logs are still populated during every performed flashback operation. RVWR doesn’t care if the database is in either MOUNT or OPEN stage. Flashback logs are generated because the data blocks within datafiles are changing and the before-images of data must be recorded as well even that the database is not opened. You can see this behavior in the following example:
9. Open the Database with OPEN RESETLOGS clause:
SQL> ALTER DATABASE OPEN RESETLOGS;
Perform Flashback via Enterprise Manager
You can perform a Flashback Database operation via Enterprise Manager as well.
Go to Tab: Maintenance -> Section: Backup/Recovery -> Link: Perform Recovery
Dealing with Excluded Tablespaces in case of Flashback Database Operations
If some of your tablespaces are excluded from flashback logging it means that they are not with critical importance for your system and the database is allowed to be open without the data contained within them.
If you want to perform a Flashback Database operation with excluded tablespaces, try to follow these steps:
1. Find the desired point in time for the flashback operation. Usually it is the time just before the time when some human error caused a logical damage of the database data. You can calculate this as SCN or a Date (TIMESTAMP expression). Additionally, you can use a restore point or a time just before last RESETLOGS operation. A Log Sequence Number can be used in 10g R1 but it is not available as an option in 10g R2.
2. Find all datafiles that are with disabled flashback logging:
SQL> SELECT NAME tbs_name,file_name db_file,flashback_on fb_logging, online_status status FROM v$tablespace,dba_data_files WHERE NAME=tablespace_name;
3. Ensure that you have available backups for all datafiles that have disabled flashback logging:
RMAN> LIST BACKUP BY FILE;
If you have a RMAN catalog, you can query the view: RC_BACKUP_DATAFILE_SUMMARY
4. Ensure that all needed archived and redo logs (covering the whole time period between oldest_flashback_time column value and the time of failure) are physically available:
4.1 Check the physical availability:
RMAN> CROSSCHECK ARCHIVELOG FROM SCN oldest_flashback_scn;
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
4.2 Check the logical availability:
RMAN> REPORT UNRECOVERABLE;
REPORT UNRECOVERABLE command reports all datafiles that cannot be recovered from existing backups because redo may be missing.
You can check the logical availability of archived redo logs with the following SQL as well:
SQL> SELECT * FROM v$archived_log WHERE first_change# > = oldest_flashback_scn AND status = 'A';
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
5. Find and write down the current SCN (or create a normal restore point if you are using 10g R2). Don’t forget that the Flashback Database is a change on the database and it must be recorded if some failure occurs within this operation.
SQL> SELECT current_scn FROM v$database;
or
SQL> CREATE RESTORE POINT before_flashback_operation;
To map a time value and SCN, you can use the built-in SQL functions (available since 10g):
SCN_TO_TIMESTAMP – to find associated timestamp with this SCN
TIMESTAMP_TO_SCN – to find associated SCN with this timestamp
SQL> SELECT current_scn, scn_to_timestamp(current_scn) FROM v$database;
This timestamp mapping information is recorded in the SMON_SCN_TIME table.
Oracle keeps the information in SMON_SCN_TIME table for a period of 5 days.
6. Restart the database in MOUNT stage:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
7. Put the datafiles with disables flashback logging in OFFLINE mode but with the DROP option.
If you try to use immediately the Flashback Database command, it will return an error. You should put all excluded from flashback logging datafiles in OFFLINE mode:
Now, if you try to do Flashback, the Flashback Database operation will succeed but another error will be returned:
You must again put the needed datafiles in OFFLINE mode but with the DROP option. After this command the offlined datafiles cannot be recovered without performing of a datafile media recovery.
If you want to perform a Flashback Database operation (when some tablespaces are excluded from flashback logging), you should use this command:
SQL> ALTER DATABASE DATAFILE ‘E:\ORACLE\ORADATA\ORCL102\FB_TEST01.DBF’ OFFLINE FOR DROP;
This will let the flashback operation to complete successfully and the database to be opened as fast as possible.
8. Execute the FLASHBACK DATABASE command
9. Open the Database with RESETLOGS clause:
SQL> ALTER DATABASE OPEN RESETLOGS;
10. Recover offline tablespaces.
At this moment, when all important data is already available and accessible to the end users, you can try to perform media recovery of all offlined datafiles while the database is open. You can do this with RMAN:
RMAN> RESTORE TABLESPACE fb_users;
RMAN> RECOVER TABLESPACE fb_users;
Now you can bring recovered datafiles in ONLINE mode:
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORCL102\FB_TEST01.DBF' ONLINE;
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORCL102\FB_TEST02.DBF' ONLINE;
It is possible SYSTEM tablespace to be excluded from flashback logging:
In this case you will not be able to use the Flashback Database operation to perform fast database point-in-time recovery because you cannot open the database without to perform media recovery of the whole database:
Disabling the SYSTEM tablespace will make the Flashback Database feature useless because the only way a DBITR to be performed is by using the standard media recovery with restore of datafile and applying of redo logs.
According to all tests – excluding of tablespaces from flashback logging can safe some space on your storage but in many cases will lead to problems and serious difficulties of using of the Flashback Database feature. Exclude a tablespace from flashback logging only if you are sure that you will be able to perform fast database point-in-time recovery in case of need.
Space Pressure Issues
When you’ve configured and enabled the Flashback Database you should keep in mind that the generated flashback log files will occupy part of the space allocated for the FRA. You should add extra space to FRA or to ensure that it have enough free space to hold flashback log files.
A copy of the old block version is written to the flashback log. If, over the course of a day, 10% of the database blocks are updated, then the size of flashback logs for 24 hours is approximately one-tenth the size of your database.
The size of all generated flashback logs depends on the amount of changes within the specific database and the chosen flashback database window by the initialization parameter: DB_FLASHBACK_RETENTION_TARGET. If there is no space pressure over FRA the RVWR process will start to reuse old flashback logs only if they contain data outside of the spanned time period by the flashback window. Flashback log files cannot be backed up.
There are two types of files within the FRA: transient and permanent. The only permanent files within FRA (there are a few exception of this rule in very specific cases, related to archived redo logs) are multiplexed copies of current control file and online redo log files. Permanent files are never deleted from FRA even in case of space pressure.
For more info about transient and permanent type of files within FRA, see Oracle documentation:
Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 3.5 Setting Up a Flash Recovery Area for RMAN
Flashback log files are treated as transient files in FRA (and this is the only one place where they can reside) and can be deleted in case of space pressure.
In case of space pressure over FRA the RVWR will not be able to allocate additional space for new flashback logs and will start to reuse old flashback logs. In this case the flashback window can become smaller than the defined value in DB_FLASHBACK_RETENTION_TARGET. Be aware that this is a TARGET parameter and Oracle is not obligated to keep it. This parameter only asks it to keep the defined flashback database window if it is possible.
If there is defined RMAN recovery window specified by days, then in case of space pressure, Oracle can delete all files from FRA that are transient and are not needed for a complete database media recovery within the specified amount of days. If there is continuous space pressure (usually if it is configured only one archivelog destination and it resides within FRA), Oracle can delete all archived redo logs that are outside the defined recovery window. In this case, if an archived redo log file is automatically deleted, then the flashback log file that contains related flashback information (contained within the archived redo log) will also be automatically deleted.
To summarize, in case of space pressure, Oracle can automatically delete an existent flashback log only if its related archived redo log is already deleted.
If you want to monitor currently used and estimated size for flashback log files, use the following query (the returned value is in bytes):
SQL> SELECT flashback_size, estimated_flashback_size FROM v$flashback_database_log;
To monitor used and available space for the whole FRA, use the following two views:
- V$RECOVERY_FILE_DEST - displays information about the disk quota and current disk usage in the flash recovery area. Here you can find the space limit and currently used space in FRA.
- V$FLASH_RECOVERY_AREA_USAGE - displays usage information about flashback recovery areas separated by file types. Here you can view the percentage of FRA that is used from every type of file including flashback log files. This view is available in 10g R2 only.
Recommendations to limit space pressures related to flashback log files:
Monitoring of Flashback Database with Data Dictionary Views
Flashback Database feature is designed in way that usually doesn’t impact the I/O or CPU performance in a significant way. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead. Although this assertion this feature can lead to a performance problems if it is not properly configured or is used in an inappropriate environment (for example, in a highly write-intensive database).
You can monitor the performance impact of the Flashback Database feature over your environment by generating an AWR report. The most significant wait event that you should care of is: “flashback buf free by RVWR”. If this event is one of the top wait events that can be a sign that Oracle cannot write flashback logs very quickly.
All important wait events related to this feature are:
For better performance, Oracle recommends using file systems that don’t use operating system file caching. Large flashback logs add overhead when using operating system file caching.
To increase disk throughput, Oracle recommends using multiple disk spindles, with small stripe sizes (128K) for FRA.
For more info about performance impact of the Flashback Database, see Oracle documentation;
Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 5.3 Setup and Maintenance for Oracle Flashback Database - Monitoring Flashback Database Performance Impact
For the full list of all wait events related to Flashback Database, see: Wait Event Enhancements in Oracle 10g, article by by Terry Sutton and Roger Schrag
Notes
The used Oracle environment is:
The Flashback Database is one of the most powerful backup & recovery enhancements in Oracle 10g. It can help you to fight against human errors in a very effective and fast way and with minimum downtime. It is adding a minimal overhead to your database and provides great advantages.
Using of the Flashback Database must be part of your Backup & Recovery Strategy.
Used References and Recommended Readings
Permanent Link @ 0 Comments on This Post @ Comment Here
The Flashback Database is one of the most powerful new features in Oracle 10g coming as a part of Backup & Recovery enhancements.
In this post you can find the result of my research and shared experience about this feature.
What is the Oracle Flashback Database?
Flashback Database is a part of the backup & recovery enhancements in Oracle 10g Database that are called Flashback Features.
The main purpose of Flashback Technology is to let you quickly recover from logical corruptions or user errors.
Oracle Flashback Features include the following:
- Flashback DATABASE
- Flashback DROP
- Flashback TABLE
- Flashback QUERY
- Flashback VERSIONS QUERY
- Flashback TRANSACTION QUERY
What says Oracle’s documentation about the Flashback Database Feature:
Oracle Database Backup and Recovery Basics -
10g Release 2 (10.2) - Chapter 5.1 - Restore Points and Flashback Database: Concepts
Flashback Database enables you to wind your entire database backward in time, reversing the effects of unwanted database changes within a given time window. The effects are similar to database point-in-time recovery.And:
It is similar to conventional point in time recovery in its effects, allowing you to return a database to its state at a time in the recent past.And:
Flashback Database can be used to reverse most unwanted changes to a database, as long as the datafiles are intact.From Oracle Database Concepts - 10g Release 2 (10.2) - Chapter 15 - Backup & Recovery
Oracle Flashback Database lets you quickly recover an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors.Another quote from Metalink Note 268197.1: New Background Processes In 10g:
- This a new feature introduced in 10gWhat are the Benefits?
- Flashbacking a database means going back to a previous database state
- The Flashback Database feature provides a way to quickly revert an entire Oracle database to the state it was in at a past point in time
- This is different from traditional point in time recovery
…
- One can use Flashback Database to back out changes that:
- Have resulted in logical data corruptions
- Are a result of user error
- This feature is not applicable for recovering the database in case of media failure
According to many studies and reports, Human Error accounts for 30-35% of data loss episodes. This makes Human Errors one of the biggest single causes of downtime.
With Flashback Database feature Oracle is trying to fight against user and operator errors in an extremely fast and effective way.
You can find more info about most common causes of data loss in the following sources:
- The Data Recovery Solution, a white paper by ONTRACK Data Recovery, Inc.
- The Business Case for Disaster Recovery Planning: Calculating the Cost of Downtime, a white paper by Iron Mountain
- Data Recovery Report, by Ontrack Japan
- The Cost of Lost Data, an article by David M. Smith, Ph.D.
- Critical Data Protection, a white paper by TIDF, LLC
For more info about the recovery speed advantages of Flashback Database, you can use the following source:
Oracle Flashback Technology, an Oracle article by Ron Weiss, Director of Product Management in Oracle's Database Kernel Development Group
Flashback Database provides:
- Very effective way to recover from complex human errors
- Faster database point-in-time recovery
- Simplified management and administration
- Little performance overhead
I will try to discuss all of them in the sections below.
The Flashback Database is not just your database “rewind” button. It is a “Time Machine” for your Database data that is one single command away from you.
The Flashback Database Architecture
Flashback Database uses its own type of log files, called Flashback Database Log Files.
To support this mechanism, Oracle uses new background process called RVWR (Recovery Writer) and a new buffer in the SGA, called Flashback Buffer.
The Oracle database periodically logs before images of data blocks in the flashback buffer. The flashback buffer records images of all changed data blocks in the database. This means that every time a data block in the database is altered, the database writes a before image of this block to the flashback buffer. This before image can be used to reconstruct a datafile to the current point of time.
The maximum allowed memory for the flashback buffer is 16 MB. You don’t have direct control on its size. The flashback buffer size depends on the size of the current redo log buffer that is controlled by Oracle. Starting at 10g R2, the log buffer size cannot be controlled manually by setting the initialization parameter LOG_BUFFER.
In 10G R2, Oracle combines fixed SGA area and redo buffer together. If there is a free space after Oracle puts the combined buffers into a granule, that space is added to the redo buffer. The sizing of the redo log buffer is fully controlled by Oracle. According to SGA and its atomic sizing by granules, Oracle will calculate automatically the size of the log buffer depending of the current granule size. For smaller SGA size and 4 MB granules, it is possible redo log buffer size + fixed SGA size to be multiple of the granule size. For SGAs bigger than 128 MB, the granule size is 16 MB.
You can see current size of the redo log buffer, fixed SGA and granule by querying the V$SGAINFO view.
You can query the V$SGASTAT view to display detailed information on the SGA and its structures.
To find current size of the flashback buffer, you can use the following query:
SQL> SELECT * FROM v$sgastat WHERE NAME = 'flashback generation buff';
There is no official information from Oracle that confirms the relation between 'flashback generation buff' structure in SGA and the real flashback buffer structure. This is only a suggestion.
A similar message message is written to the alertSID.log file during opening of the database:
Allocated 3981204 bytes in shared pool for flashback generation buffer Starting background process RVWR RVWR started with pid=16, OS id=5392For more info about the sizing restrictions of log buffer in 10g R2, see Metalink Note: 351857.1: The Log_buffer Cannot be Changed In 10g R2
For more info about the granule model in the SGA (available since Oracle9i), see Metalink Note: 148495.1: Oracle9i New Feature: Dynamic SGA
The flashback buffer size is at least two times bigger than the log buffer size. This assertion can be found in OCP Oracle Database 10g : New Features for Administrators by Sam Alapati
Although flashback buffer size is expected to be at least two times bigger than log buffer size, its real size can be smaller. In tested 10g R2 environment, flashback buffer has around two times smaller size as the redo log buffer.
Granule Size: 4194304This rule is mentioned indirectly in the Oracle documentation: Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 5.3 Setup and Maintenance for Oracle Flashback Database - Performance Tuning for Flashback Database:
Fixed SGA Size: 1289832
Log Buffer: 7098368
Flashback Buffer (flashback generation buff): 3981204
For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.The RVWR background process has responsibility to write the contents of flashback buffer to flashback log files. This process is started automatically when flashback database feature is enabled.
RVWR writes periodically flashback buffer contents to flashback database logs. It is an asynchronous process and you don’t have control over it. All available sources are saying that RVWR writes periodically to flashback logs. The explanation for this behavior is that Oracle is trying to reduce the I/O and CPU overhead that can be an issue in many production environments.
The RVWR writing mechanism and its behavior needs more investigation that will not be discussed here. It can be seen that it is controlled by few hidden parameters in a similar way like writing to redo log files – depending of generated flashback redo and time interval.
Flashback log files can be created only under the Flash Recovery Area (that must be configured before enabling the Flashback Database functionality). RVWR creates flashback log files into a directory named “FLASHBACK” under FRA. The size of every generated flashback log file is again under Oracle’s control. According to current Oracle environment – during normal database activity flashback log files have size of 8200192 bytes. It is very close value to the current redo log buffer size. The size of a generated flashback log file can differs during shutdown and startup database activities. Flashback log file sizes can differ during high intensive write activity as well.
Flashback log files can be written only under FRA (Flash Recovery Area). FRA is closely related and is built on top of Oracle Managed Files (OMF). OMF is a service that automates naming, location, creation and deletion of database files. By using OMF and FRA, Oracle manages easily flashback log files. They are created with automatically generated names with extension .FLB. For instance, this is the name of one flashback log file: O1_MF_26ZYS69S_.FLB
By its nature flashback logs are similar to redo log files. LGWR writes contents of the redo log buffer to online redo log files, RVWR writes contents of the flashback buffer to flashback database log files. Redo log files contain all changes that are performed in the database, that data is needed in case of media or instance recovery. Flashback log files contain only changes that are needed in case of flashback operation. The main differences between redo log files and flashback log files are:
- Flashback log files are never archived - they are reused in a circular manner.
- Redo log files are used to forward changes in case of recovery while flashback log files are used to backward changes in case of flashback operation
- UNDO data doesn’t contain all changes that are performed in the database while flashback logs contain all altered blocks in the database
- UNDO data is used to backward changes at the transaction level while flashback logs are used to backward changes at the database level
There is a new record section within the control file header that is named FLASHBACK LOGFILE RECORDS. It is similar to LOG FILE RECORDS section and contains info about the lowest and highest SCN contained in every particular flashback database log file***************************************************************************What does a Flashback Database operation?
FLASHBACK LOGFILE RECORDS
***************************************************************************
(size = 84, compat size = 84, section max = 2048, section in-use = 136,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 139, numrecs = 2048)
FLASHBACK LOG FILE #1:
(name #4) E:\ORACLE\FLASH_RECOVERY_AREA\ORCL102\FLASHBACK\O1_MF_26YR1CQ4_.FLB
Thread 1 flashback log links: forward: 2 backward: 26
size: 1000 seq: 1 bsz: 8192 nab: 0x3e9 flg: 0x0 magic: 3 dup: 1
Low scn: 0x0000.f5c5a505 05/20/2006 21:30:04
High scn: 0x0000.f5c5b325 05/20/2006 22:00:38
When you perform a flashback operation, Oracle needs all flashback logs from now on to the desired time. They will be applied consecutively starting from the newest to the oldest.
For instance, if you want to flashback the database to SCN 4123376440, Oracle will read flsahback logfile section in control file and will check for the availability of all needed flashback log files. The last needed flashback log should be this with Low scn and High scn values between the desired SCN 4123376440.
In current environment this is the file with name: O1_MF_26YSTQ6S_.FLB and with values of:
Low SCN: 4123374373Note: If you want to perform successfully a flashback operation you will always need to have available at least one archived (or online redo) log file. This is a particular file that contains redo log information about changes around the desired flashback point in time (SCN 4123376440). In this case, this is the archived redo log with name: ARC00097_0587681349.001 that has values of:
High SCN: 4123376446
First change#: 4123361850The flashback operation will not succeed without this particular archived redo log.
Next change#: 4123380675
The reason for this: Flashback log files contain information about before-images of data blocks, related to some SCN (System Change Number). When you perform flashback operation to SCN 4123376440, Oracle cannot apply all needed flashback logs and to complete successfully the operation because it applying before-images of data. Oracle needs to restore each data block copy (by applying flashback log files) to its state at a closest possible point in time before SCN 4123376440. This will guarantee that the subsequent “redo apply” operation will forward the database to SCN 4123376440 and the database will be in consistent state. After applying flashback logs, Oracle will perform a forward operation by applying all needed archive log files (in this case redo information from the file: ARC00097_0587681349.001) that will forward the database state to the desired SCN.
Oracle cannot start applying redo log files before to be sure that all data blocks are returned to their state before the desired point in time. So, if desired restore point of time is 10:00 AM and the oldest restored data block is from 09:47 AM then you will need all archived log files that contain redo data for the time interval between 09:47 AM and 10:00 AM. Without that redo data, the flashback operation cannot succeed.
A short explanation of this issue can be found in the documentation:
Oracle Database Backup and Recovery Basics -
10g Release 2 (10.2) - Chapter 5.1 - Restore Points and Flashback Database: Concepts
When a database is restored to its state at some past target time using Flashback Database, each block changed since that time is restored from the copy of the block in the flashback logs most immediately prior to the desired target time. The redo log is then used to re-apply changes since the time that block was copied to the flashback logs.
Note: Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. (In practice, however, redo logs are generally needed much longer than the flashback retention target to support point-in-time recovery.)There is a lack of information about this specific behavior of the Flashback Database feature. The above quote is the only one place in Oracle documentation where it is mentioned.
There is another source that is trying to explain this behavior in a comprehensible way:
OCP Oracle Database 10g: New Features for Administrators – Chapter 9: Flashback Enhancements, by Sam Alapati
Flashback logs are not independent. They can be used only with the redo data that contains database changes around the desired SCN. This means that if you want to have working flashback window (and to be able to restore the database to any point in time within this window) you need to ensure the availability of redo logs as well.
If you are familiar with this information then you will be able to work in a better way with this feature and to ensure that it will help you to perform faster recovery without unexpected problems.
Availability within Oracle’s Editions
Oracle Flashback Database feature is available only in Oracle Enterprise and Oracle Personal Editions.
For more info, see Metalink Note: 271886.1: Differences Between Different Editions of Oracle Database 10G
How to Configure Flashback Database?
1. Before to configure Flashback Database, you must ensure that:
- Your database is running in ARCHIVELOG mode
- FRA (Flash Recovery Area) is configured
- FRA is configured by two parameters:
- DB_RECOVERY_FILE_DEST
- DB_RECOVERY_FILE_DEST_SIZE
- COMPATIBILITY initialization parameter is set to 10.0 or higher value
2. Consider the value for flashback database window.
It can be set by an initialization parameter: DB_FLASHBACK_RETENTION_TARGET. The range of SCNs for which there is currently enough flashback log data to support the FLASHBACK DATABASE command is called the Flashback Database Window.
The flashback window depends of the current environment requirement but usually it can be between 24 and 72 hours. Don’t consider too big values for flashback window because if you need to perform flashback database operation to a point in time older than several days it can be slower and much more time-consuming operation than using of media recovery method.
Flashback Database is efficient and fast for smaller time spans when the error is immediately (or within few hours) discovered and performing of a Database Point-in-Time Recovery is the only one way to recover from this human error.
Default value for DB_FLASHBACK_RETENTION_TARGET is 1440. It is measured in minutes, so by default flashback window is 24 hours.
Keep in mind that flashback logs use additional space within the FRA. For more info about sizing of FRA to include Flashback Logs, see:
Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 5.3 Setup and Maintenance for Oracle Flashback Database - Sizing the Flash Recovery Area to Include Flashback Logs
3. Connect as user SYS with SYSDBA privileges.
4. Configure DB_FLASHBACK_RETENTION_TARGET:
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440 SCOPE=BOTH;
5. Shut down the database:
SQL> SHUTDOWN IMMEDIATE
6. Start the database in MOUNT stage.
To enable Flashback Database feature, your database must be in MOUNT mode.
SQL> STARTUP MOUNT
For RAC environments, use:
SQL> STARTUP MOUNT EXCLUSIVE
7. Enable Flashback Database Feature:
SQL> ALTER DATABASE FLASHBACK ON;
8. Check whether the Flashback Database feature is enabled or not:
SQL> SELECT flashback_on FROM V$DATABASE;
9. Open the database with enabled Flashback Database feature:
SQL> ALTER DATABASE OPEN;
Configuration with Enterprise Manager:
The Flashback Database Feature can be enabled via Enterprise Manager as well.
Go to: Maintenance Tab -> Backup/Recovery Settings -> Recovery Settings
Excluding Tablespaces from Flashback Logging
By default, flashback logs are generated for all permanent tablespaces. If you want, you can disable flashback logging for specific tablespaces.
For example, if you want disable flashback generation for specific tablespace you can use the following command:
SQL> ALTER TABLESPACE USERS FLASHBACK OFF;
If you want to re-enable flashback logging for this tablespace, you can do it only in MOUNT stage.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER TABLESPACE USERS FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
During a Flashback Operation you must use more actions to handle with missing flashback data for these tablespaces. For more info, see section: Dealing with Excluded Tablespaces During Flashback Operations
Using of Flashback Database
Database can be flashed back either through SQL*Plus or RMAN.
- In SQL*Plus, you can use this feature by issuing of the SQL command FLASHBACK DATABASE statement. For the command syntax, see: Oracle Database SQL Reference - 10g Release 2 (10.2) - Chapter 18 SQL Statements: DROP SEQUENCE to ROLLBACK - FLASHBACK DATABASE
- In RMAN, you can use this feature by issuing of the command FLASHBACK. For the command syntax, see: Oracle Database Backup and Recovery Reference - 10g Release 2 (10.2) - Chapter 2 RMAN Commands - FLASHBACK Command
1. Ensure that you are not dealing with some kind of media failure. The Flashback Database is useful only when your data is logically corrupted.
2. Find the desired point in time for the flashback operation. Usually it is he time just before the time when some human error caused a logical damage of the database data. You can calculate this as SCN or a Date (TIMESTAMP expression). Additionally, you can use a restore point or a time just before last RESETLOGS operation. A Log Sequence Number can be used in 10g R1 but it is not available as an option in 10g R2 (via SQL*Plus). It is still available as an option in RMAN.
3. Find the current oldest SCN and time to which the database can be backwarded:
SQL> SELECT oldest_flashback_scn,oldest_flashback_time FROM v$flashback_database_log;
If the desired time is after the oldest time currently recorded in the flashback log files then go to the next step
4. Ensure that there are not tablespaces with disabled flashback logging:
SQL> SELECT NAME tbs_name,file_name db_file,flashback_on fb_logging, online_status status FROM v$tablespace,dba_data_files WHERE NAME=tablespace_name;
If there are files with no important data that are excluded from flashback logging then you can follow steps from the section: Dealing with Excluded Tablespaces in case of Flashback Database Operations
Otherwise, go to the next step.
5. Ensure that all needed archived and redo logs (covering the whole time period between oldest_flashback_time column value and the time of failure) are available:
5.1 Check physical availability:
RMAN> CROSSCHECK ARCHIVELOG FROM SCN oldest_flashback_scn;
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
5.2 Check logical availability:
RMAN> REPORT UNRECOVERABLE;
REPORT UNRECOVERABLE command reports all datafiles that cannot be recovered from existing backups because redo may be missing.
You can check the logical availability of archived redo logs with the following SQL as well:
SQL> SELECT * FROM v$archived_log WHERE first_change# > = oldest_flashback_scn AND status = 'A';
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
6. Find and write down the current SCN (or create a normal restore point if you are using 10g R2). Don’t forget that the Flashback Database is a change on the database and it must be recorded if some failure occurs during this operation.
SQL> SELECT current_scn FROM v$database;
or
SQL> CREATE RESTORE POINT before_flashback_operation;
To map a time value and SCN, you can use the built-in SQL functions (available since 10g):
SCN_TO_TIMESTAMP – to find associated timestamp with this SCN
TIMESTAMP_TO_SCN – to find associated SCN with this timestamp
SQL> SELECT current_scn, scn_to_timestamp(current_scn) FROM v$database;
This timestamp mapping information is recorded in the SMON_SCN_TIME table.
Oracle keeps the information in SMON_SCN_TIME table for a period of 5 days.
7. Restart the database in MOUNT stage
7.1 SQL> SHUTDOWN IMMEDIATE
7.2 SQL> STARTUP MOUNT
For RAC environments, use:
SQL> STARTUP MOUNT EXCLUSIVE
If you want to be able to return the database to its state just as it was closed, you can create a restore point at this moment (restore points can be created even in MOUNT stage):
SQL> CREATE RESTORE POINT just_after_shutdown;
Or to find the last checkpointed SCN:
SQL> SELECT checkpoint_change# FROM v$database;
In the next step, if you perform a few flashback operations and you need to return the database to its state just before to be closed, you will need the above SCN.
8. Flashback the Database
For this example the point of time before the logical corruption is:
As SCN: 4125962274In SQL it can be done by FLASHBACK DATABASE command. In RMAN it can be done by FLASHBACK command.
As Timestamp: 24.05.2006 15:10
Different types of usage:
8.1. Flashes back the database to a point in time just as in the specified SCN:
SQL> FLASHBACK DATABASE TO SCN 4125962274; /* returns database to its state as in the point in time marked with SCN 4125962274 */
8.2. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of custom TIMESTAMP value):
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi'); /* returns database to its state as in the point in time 24.05.2006 15:10 */
8.3. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of SYSTIMESTAMP)
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP -1/24); /* returns database to its state as in the point in time one hour ago */
8.4. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of SYSTIMESTAMP and INTERVAL):
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE); /* returns database to its state as in the point in time one minute ago */
8.5. Flashes back the database to a point in time just as in the specified restore point
SQL> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; /* returns database to its state as in the point in time marked with the restore point with name before_flashback_operation */
8.6. Flashbacks the database to the point in time just before the last RESETLOGS operation
SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;
8.7. Flashes back the database to a SCN just before the specified SCN
SQL> FLASHBACK DATABASE TO BEFORE SCN 4125962274;
8.8. Flashes back the database to a point in time one second before the specified TIMESTAMP
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi');
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSDATE -1/24); SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);
8.9. Flashes back the database to a point in time just before the specified restore point
SQL> FLASHBACK DATABASE TO BEFORE RESTORE POINT before_flashback_operation;
8.10. Flashing back of the database via RMAN
RMAN> FLASHBACK DATABASE TO SCN 4125962274; RMAN> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); RMAN> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi');
RMAN> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;
Let’s demonstrate the ‘Time Machine’ capabilities of this feature:
As it can be seen, the Flashback feature allows you to shift within the time as it doesn’t exist. In some articles it is called a “rewind” button but its abilities as a “forward” button make this feature to behave as a “Time Machine” device for your data.
I want to note here again that you can move forward in time only with applying of redo logs. You can backward in time by applying flashback logs along with smaller amount of redo data.
Be aware of that you will be able to travel through the time only if you are well prepared for it. You must have all flashback and archived redo logs for the whole time period. With the Flashback Database you can return database to its state as in every point in time in an extremely fast way. The speed of this operation depends only of the number of the applied flashback logs and this mainly depends of the number of the data block changes within the database for this time period. It doesn’t depend on the database size because no restore operation is performed. Usually this operation can complete even within several seconds or minutes and can be between 25 to 105 times faster than usual incomplete media recovery.
Flashback logs are still populated during every performed flashback operation. RVWR doesn’t care if the database is in either MOUNT or OPEN stage. Flashback logs are generated because the data blocks within datafiles are changing and the before-images of data must be recorded as well even that the database is not opened. You can see this behavior in the following example:
9. Open the Database with OPEN RESETLOGS clause:
SQL> ALTER DATABASE OPEN RESETLOGS;
Perform Flashback via Enterprise Manager
You can perform a Flashback Database operation via Enterprise Manager as well.
Go to Tab: Maintenance -> Section: Backup/Recovery -> Link: Perform Recovery
Dealing with Excluded Tablespaces in case of Flashback Database Operations
If some of your tablespaces are excluded from flashback logging it means that they are not with critical importance for your system and the database is allowed to be open without the data contained within them.
If you want to perform a Flashback Database operation with excluded tablespaces, try to follow these steps:
1. Find the desired point in time for the flashback operation. Usually it is the time just before the time when some human error caused a logical damage of the database data. You can calculate this as SCN or a Date (TIMESTAMP expression). Additionally, you can use a restore point or a time just before last RESETLOGS operation. A Log Sequence Number can be used in 10g R1 but it is not available as an option in 10g R2.
2. Find all datafiles that are with disabled flashback logging:
SQL> SELECT NAME tbs_name,file_name db_file,flashback_on fb_logging, online_status status FROM v$tablespace,dba_data_files WHERE NAME=tablespace_name;
3. Ensure that you have available backups for all datafiles that have disabled flashback logging:
RMAN> LIST BACKUP BY FILE;
If you have a RMAN catalog, you can query the view: RC_BACKUP_DATAFILE_SUMMARY
4. Ensure that all needed archived and redo logs (covering the whole time period between oldest_flashback_time column value and the time of failure) are physically available:
4.1 Check the physical availability:
RMAN> CROSSCHECK ARCHIVELOG FROM SCN oldest_flashback_scn;
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
4.2 Check the logical availability:
RMAN> REPORT UNRECOVERABLE;
REPORT UNRECOVERABLE command reports all datafiles that cannot be recovered from existing backups because redo may be missing.
You can check the logical availability of archived redo logs with the following SQL as well:
SQL> SELECT * FROM v$archived_log WHERE first_change# > = oldest_flashback_scn AND status = 'A';
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
5. Find and write down the current SCN (or create a normal restore point if you are using 10g R2). Don’t forget that the Flashback Database is a change on the database and it must be recorded if some failure occurs within this operation.
SQL> SELECT current_scn FROM v$database;
or
SQL> CREATE RESTORE POINT before_flashback_operation;
To map a time value and SCN, you can use the built-in SQL functions (available since 10g):
SCN_TO_TIMESTAMP – to find associated timestamp with this SCN
TIMESTAMP_TO_SCN – to find associated SCN with this timestamp
SQL> SELECT current_scn, scn_to_timestamp(current_scn) FROM v$database;
This timestamp mapping information is recorded in the SMON_SCN_TIME table.
Oracle keeps the information in SMON_SCN_TIME table for a period of 5 days.
6. Restart the database in MOUNT stage:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
7. Put the datafiles with disables flashback logging in OFFLINE mode but with the DROP option.
If you try to use immediately the Flashback Database command, it will return an error. You should put all excluded from flashback logging datafiles in OFFLINE mode:
Now, if you try to do Flashback, the Flashback Database operation will succeed but another error will be returned:
You must again put the needed datafiles in OFFLINE mode but with the DROP option. After this command the offlined datafiles cannot be recovered without performing of a datafile media recovery.
If you want to perform a Flashback Database operation (when some tablespaces are excluded from flashback logging), you should use this command:SQL> ALTER DATABASE DATAFILE ‘E:\ORACLE\ORADATA\ORCL102\FB_TEST01.DBF’ OFFLINE FOR DROP;
This will let the flashback operation to complete successfully and the database to be opened as fast as possible.
8. Execute the FLASHBACK DATABASE command
9. Open the Database with RESETLOGS clause:
SQL> ALTER DATABASE OPEN RESETLOGS;
10. Recover offline tablespaces.
At this moment, when all important data is already available and accessible to the end users, you can try to perform media recovery of all offlined datafiles while the database is open. You can do this with RMAN:
RMAN> RESTORE TABLESPACE fb_users;
RMAN> RECOVER TABLESPACE fb_users;
Now you can bring recovered datafiles in ONLINE mode:
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORCL102\FB_TEST01.DBF' ONLINE;
SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\ORADATA\ORCL102\FB_TEST02.DBF' ONLINE;
It is possible SYSTEM tablespace to be excluded from flashback logging:
In this case you will not be able to use the Flashback Database operation to perform fast database point-in-time recovery because you cannot open the database without to perform media recovery of the whole database:
Disabling the SYSTEM tablespace will make the Flashback Database feature useless because the only way a DBITR to be performed is by using the standard media recovery with restore of datafile and applying of redo logs.According to all tests – excluding of tablespaces from flashback logging can safe some space on your storage but in many cases will lead to problems and serious difficulties of using of the Flashback Database feature. Exclude a tablespace from flashback logging only if you are sure that you will be able to perform fast database point-in-time recovery in case of need.
Space Pressure Issues
When you’ve configured and enabled the Flashback Database you should keep in mind that the generated flashback log files will occupy part of the space allocated for the FRA. You should add extra space to FRA or to ensure that it have enough free space to hold flashback log files.
A copy of the old block version is written to the flashback log. If, over the course of a day, 10% of the database blocks are updated, then the size of flashback logs for 24 hours is approximately one-tenth the size of your database.
The size of all generated flashback logs depends on the amount of changes within the specific database and the chosen flashback database window by the initialization parameter: DB_FLASHBACK_RETENTION_TARGET. If there is no space pressure over FRA the RVWR process will start to reuse old flashback logs only if they contain data outside of the spanned time period by the flashback window. Flashback log files cannot be backed up.
There are two types of files within the FRA: transient and permanent. The only permanent files within FRA (there are a few exception of this rule in very specific cases, related to archived redo logs) are multiplexed copies of current control file and online redo log files. Permanent files are never deleted from FRA even in case of space pressure.
For more info about transient and permanent type of files within FRA, see Oracle documentation:
Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 3.5 Setting Up a Flash Recovery Area for RMAN
Flashback log files are treated as transient files in FRA (and this is the only one place where they can reside) and can be deleted in case of space pressure.
In case of space pressure over FRA the RVWR will not be able to allocate additional space for new flashback logs and will start to reuse old flashback logs. In this case the flashback window can become smaller than the defined value in DB_FLASHBACK_RETENTION_TARGET. Be aware that this is a TARGET parameter and Oracle is not obligated to keep it. This parameter only asks it to keep the defined flashback database window if it is possible.
If there is defined RMAN recovery window specified by days, then in case of space pressure, Oracle can delete all files from FRA that are transient and are not needed for a complete database media recovery within the specified amount of days. If there is continuous space pressure (usually if it is configured only one archivelog destination and it resides within FRA), Oracle can delete all archived redo logs that are outside the defined recovery window. In this case, if an archived redo log file is automatically deleted, then the flashback log file that contains related flashback information (contained within the archived redo log) will also be automatically deleted.
To summarize, in case of space pressure, Oracle can automatically delete an existent flashback log only if its related archived redo log is already deleted.
If you want to monitor currently used and estimated size for flashback log files, use the following query (the returned value is in bytes):
SQL> SELECT flashback_size, estimated_flashback_size FROM v$flashback_database_log;
To monitor used and available space for the whole FRA, use the following two views:
- V$RECOVERY_FILE_DEST - displays information about the disk quota and current disk usage in the flash recovery area. Here you can find the space limit and currently used space in FRA.
- V$FLASH_RECOVERY_AREA_USAGE - displays usage information about flashback recovery areas separated by file types. Here you can view the percentage of FRA that is used from every type of file including flashback log files. This view is available in 10g R2 only.
Recommendations to limit space pressures related to flashback log files:
- Set flashback database window to a reasonable value. It should be no more than few days. Otherwise it will become a much slower operation than the normal media recovery operation.
- Do not configure flashback database window bigger than RMAN recovery window. Use values that are smaller than the RMAN recovery window. This will ensure that an existing flashback log file will not be deleted in case of non-existence of its related archived redo log. RMAN recovery window will guarantee for that.
- Configure the FRA size to be at most 90% of the real physical available space. This will help you to resolve immediately a space pressure problem by changing the initialization parameter DB_RECOVERY_FILE_DEST_SIZE to a bigger value.
- If you need to free immediately all space occupied by flashback logs, you need to disable flashback logging feature. In this case, Oracle will delete immediately all existing flashback logs: SQL> ALTER DATABASE FLASHBACK OFF;
Monitoring of Flashback Database with Data Dictionary Views
- V$FLASHBACK_DATABASE_LOG, GV$FLASHBACK_DATABASE_LOG (for RAC) - displays information about the flashback data. Use this view to help estimate the amount of flashback space required for the current workload.
- V$FLASHBACK_DATABASE_STAT, GV$FLASHBACK_DATABASE_STAT (for RAC) - displays statistics for monitoring the I/O overhead of logging flashback data. This view also displays the estimated flashback space needed based on previous workloads.
- V$RECOVERY_FILE_DEST - displays information about the disk quota and current disk usage in the flash recovery area. Here you can find the space limit and currently used space in FRA.
- V$FLASHBACK_DATABASE_LOGFILE, GV$FLASHBACK_DATABASE_LOGFILE (for RAC) - display detailed info about flashback log files. Here you can find the start SCN for every flashback log file.
- V$FLASH_RECOVERY_AREA_USAGE - displays usage information about flashback recovery areas separated by file types. Here you can view the percentage of FRA that is used from every type of file including flashback log files. This view is available in 10g R2 only.
- V$ARCHIVED_LOG, V$LOG_HISTORY, GV$ARCHIVED_LOG, GV$LOG_HISTORY - displays archived log information from the control file, including archive log names. You can use it check availability of the needed archived redo log files before starting of a Flashback Database operation.
- V$LOGFILE, V$LOG, GV$LOGFILE, GV$LOG - displays information about redo log files. You can use it check availability of the needed archived redo log files before starting of a Flashback Database operation.
- V$DATABASE – query the FLASHBACK_ON column to find whether the Flashback Database feature is enabled or not at the database level
- V$TABLESPACE – query the FLASHBACK_ON column to find whether the flashback logging is enabled or not for a specific tablespace
- V$RESTORE_POINT - displays information about restore points. Here you can find the map between a specific restore point, SCN and TIMESTAMP value.
- V$SGAINFO - to find the current size of the redo log buffer, fixed SGA and the granule.
- V$SGASTAT - displays detailed information on the SGA and its structures.
Flashback Database feature is designed in way that usually doesn’t impact the I/O or CPU performance in a significant way. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead. Although this assertion this feature can lead to a performance problems if it is not properly configured or is used in an inappropriate environment (for example, in a highly write-intensive database).
You can monitor the performance impact of the Flashback Database feature over your environment by generating an AWR report. The most significant wait event that you should care of is: “flashback buf free by RVWR”. If this event is one of the top wait events that can be a sign that Oracle cannot write flashback logs very quickly.
All important wait events related to this feature are:
- flashback buf free by RVWR
- flashback free VI log
- flashback log switch
- RVWR wait for flashback copy
For better performance, Oracle recommends using file systems that don’t use operating system file caching. Large flashback logs add overhead when using operating system file caching.
To increase disk throughput, Oracle recommends using multiple disk spindles, with small stripe sizes (128K) for FRA.
For more info about performance impact of the Flashback Database, see Oracle documentation;
Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2) - Chapter 5.3 Setup and Maintenance for Oracle Flashback Database - Monitoring Flashback Database Performance Impact
For the full list of all wait events related to Flashback Database, see: Wait Event Enhancements in Oracle 10g, article by by Terry Sutton and Roger Schrag
Notes
- The Flashback Database should be part of your Backup & Recovery Strategy but it not supersedes the normal physical backup & recovery strategy. It is only an additional protection of your database data.
- The Flashback Database can be used to flashes back a database to its state to any point in time into the flashback window, only if all flashback logs and their related archived redo logs for the spanned time period are physically available and accessible.
- Always ensure that archived redo logs covering the flashback window are available on either the tape or disk.
- You cannot perform flashback database operation if you have media failure. In this case you must use the traditional database point-in-time media recovery method.
- Always write down the current SCN or/and create a restore point (10g R2) before any significant change over your database: applying of patches, running of batch jobs that can can corrupt the data, etc. As you know: The most common cause for downtime is change.
- Always write down the current SCN or/and create a restore point (10g R2) before to start a flashback operation .
- Flashback database is the only one flashback operation that can be performed to undone result of a TRUNCATE command (FLASHBACK DROP, FLASHBACK TABLE, or FLASHBACK QUERY cannot be used for this).
- Dropping of tablespace cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.
- Shrink a datafile cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.
- Resizing of datafile cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation. If you need to perform flashback operation in this time period, you must offline this datafile before performing of flashback operation.
- Recreating or restoring of control file prevents using of Flashback Database before this point of time.
- You can flashback database to a point in time before a RESETLOGS operation. This feature is available from 10g R2 because the flashback log files are not deleted after RESETLOGS operation. You cannot do this in 10g R1 because old flashback logs are deleted immediately after an RESETLOGS operation.
- Don’t exclude the SYSTEM tablespace from flashback logging. Otherwise you will not be able to flashback the database.
- The DB_FLASHBACK_RETENTION_TARGET parameter is a TARGET parameter. It doesn’t guarantee the flashback database window. Your proper configuration of the Flashback Database should guarantee it.
- Monitor regularly the size of the FRA and generated flashback logs to ensure that there is no space pressure and the flashback log data is within the desired flashback window
The used Oracle environment is:
- Oracle Database Version: 10.2.0.2
- Operating System: Windows XP SP2
The Flashback Database is one of the most powerful backup & recovery enhancements in Oracle 10g. It can help you to fight against human errors in a very effective and fast way and with minimum downtime. It is adding a minimal overhead to your database and provides great advantages.
Using of the Flashback Database must be part of your Backup & Recovery Strategy.
Used References and Recommended Readings
- Oracle Database Backup and Recovery Basics - 10g Release 2 (10.2)
- Oracle Database Backup and Recovery Advanced User's Guide - 10g Release 2 (10.2)
- Oracle Database Backup and Recovery Reference - 10g Release 2 (10.2)
- Oracle Database SQL Reference - 10g Release 2 (10.2)
- Oracle Database Reference - 10g Release 2 (10.2)
- Oracle Database Administrator's Guide - 10g Release 2 (10.2)
- OCP Oracle Database 10g : New Features for Administrators, by Sam Alapati, 2004
- The Log_buffer Cannot be Changed In 10g R2, a Metalink Note: 351857.1, 2006
- Oracle9i New Feature: Dynamic SGA, a Metalink Note: 148495.1, 2004
- Wait Event Enhancements in Oracle 10g, an article by Terry Sutton and Roger Schrag, 2005
- Oracle Database 10g Administration: Workshop II – ILT Course - Student Guide
- Flashback Technology: Recovering from Human Errors, an Oracle White Paper by Tammy Bednar, 2003
- Leveraging Flashback Technology, a presentation given by George Trujillo at Oracle Open World 2005, Session ID: 317
- Oracle10g Flashback Enhancements, a presentation by Dave Anderson, 2005
- Oracle 10g RMAN and Flashback - Turning Back Time, a presentation by Maria Anderson, Calgary Oracle Users Group, 2006
- All About 10g Flashing Back, a presentation by Tom Kyte, Calgary Oracle Users Group, 2005
- Oracle Database Backup & Recovery,Flashback Whatever, Data Guard, a presentation by Tammy Bednar & Ashish Ray, 18th Annual International zSeries Oracle SIG Conference, 2005
- 10g Release 2 – New Features, a presentation by Rick Pulliam, Oracle Open World 2005, Session ID 623
- Oracle 10.2 - What's In It For You?, a presentation by Julian Dyke, 2005, Oracle User Group UNIX SIG, 2005
- Oracle 10g Backup and Recovery New Features, a presentation by Daniel T. Liu, Northern California Oracle Users Group, 2005
- Oracle Database 10g: Top Features for DBAs - Release 2 Features Addendum - Part 5: Backup and Availability Features, an article by Arup Nanda, 2005
- How Oracle Database 10g Revolutionizes Availability and Enables the Grid, an article by Ron Weiss, Oracle Open World 2003, Paper ID: 40164
- Differences Between Different Editions of Oracle Database 10G, Metalink Note: 271886.1, 2006
- Flash Back to a Better Time, an article by Arup Nanda, Oracle Magazine May/June 2004
- Oracle Flashback Database - Version 10.2, an article from Puget Sound Oracle Users Group
- New Background Processes In 10g, a Metalink Note 268197.1
- Oracle 10g: New Flashback Features - Part 1, an article by Daniel T. Liu
- Flashback the Entire Database, notes by Guy Lambregts
- Oracle Flashback Technology, an Oracle article
- Oracle Flashback Technology, an Oracle article by Ron Weiss
- Flashback any error with Oracle 10g release 2?, an article by Lutz Hartmann, 2006
Labels: Backup and Recovery
Permanent Link @ 0 Comments on This Post @ Comment Here
Backup & Recovery Presentation
Published Monday, May 01, 2006 by Radoslav Rusinov.
Two weeks ago I had a presentation in front of Bulgarian Oracle User Group about Backup & Recovery Enhancements in Oracle 10g.
The agenda of the presentation:
Many assertions in this presentation are either in result of tests performed on a specific environment or expressing my current views about how a particular feature works.
I want to put some notes here:
- Always use Oracle Documentation and Metalink Support Site as your main technical source.
- Although many technical presentations about this topic are a great additional source, I can highly recommend to everyone testing of every questionable assertion that they contains.
- If you are interested in some of mentioned enhancements and features – test them very carefully on your specific environment before to decide to use them on your production environment. Test not just the feature but its behavior in real-life situations (for instance: space pressure situations, etc.)
In few additional posts I will try to present most of the Backup & Recovery Enhancements in Oracle 10g in much deeper details and especially Flashback Enhancements. Flashback Enhancements improve in a great way the Oracle’s ability to respond of Human Errors and are very powerful.
The link for the presentation: Backup & Recovery Enhancements in Oracle 10g
It can be found on the right frame on the blog as well, under section: Articles & Presentations
Permanent Link @ 0 Comments on This Post @ Comment Here
The agenda of the presentation:
- Flash Recovery Area
- Flashback Technology Enhancements
- Space Pressure & Database Hangs
- Physical Backup & Recovery Enhancements
Many assertions in this presentation are either in result of tests performed on a specific environment or expressing my current views about how a particular feature works.
I want to put some notes here:
- Always use Oracle Documentation and Metalink Support Site as your main technical source.
- Although many technical presentations about this topic are a great additional source, I can highly recommend to everyone testing of every questionable assertion that they contains.
- If you are interested in some of mentioned enhancements and features – test them very carefully on your specific environment before to decide to use them on your production environment. Test not just the feature but its behavior in real-life situations (for instance: space pressure situations, etc.)
In few additional posts I will try to present most of the Backup & Recovery Enhancements in Oracle 10g in much deeper details and especially Flashback Enhancements. Flashback Enhancements improve in a great way the Oracle’s ability to respond of Human Errors and are very powerful.
The link for the presentation: Backup & Recovery Enhancements in Oracle 10g
It can be found on the right frame on the blog as well, under section: Articles & Presentations
Labels: Backup and Recovery
Permanent Link @ 0 Comments on This Post @ Comment Here
Back to Blogging
Published by Radoslav Rusinov.
Finally, after 6 months of silence I am writing my first post.
In the past several months I didn’t have any free time to update my blog but now I am trying to change this. I hope that I will be able to write here much more frequently.
I have a lot of interesting topics to write about. Some of them:
- Presentation about Backup & Recovery Enhancements in Oracle 10g that I gave in front of the Bulgarian Oracle User Group in April
- Detailed posts about Flashback Enhancements in Oracle 10g and especially for the Flashback Database feature
- Paper about Installation and Configuration of 10g RAC with ASM under Windows 2003 – it is almost finished and I hope that I will be able to put it for download soon
- Source code for instrumentarium of PL/SQL-based code. Along with the information about execution and wait time it gives ability for tracing of the execution of every PL/SQL unit plus raised exceptions.
- Source code for report functionality about the used and unused space from segments within the database, including a simple “space advice” functionality according user-configurable parameters
Additionally, the blog has completely new design.
Enjoy!
Permanent Link @ 0 Comments on This Post @ Comment Here
In the past several months I didn’t have any free time to update my blog but now I am trying to change this. I hope that I will be able to write here much more frequently.
I have a lot of interesting topics to write about. Some of them:
- Presentation about Backup & Recovery Enhancements in Oracle 10g that I gave in front of the Bulgarian Oracle User Group in April
- Detailed posts about Flashback Enhancements in Oracle 10g and especially for the Flashback Database feature
- Paper about Installation and Configuration of 10g RAC with ASM under Windows 2003 – it is almost finished and I hope that I will be able to put it for download soon
- Source code for instrumentarium of PL/SQL-based code. Along with the information about execution and wait time it gives ability for tracing of the execution of every PL/SQL unit plus raised exceptions.
- Source code for report functionality about the used and unused space from segments within the database, including a simple “space advice” functionality according user-configurable parameters
Additionally, the blog has completely new design.
Enjoy!
Permanent Link @ 0 Comments on This Post @ Comment Here
About me
- » I'm Radoslav Rusinov
- » From Sofia, Bulgaria
- » Employer TechnoLogica Ltd.
- » I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
- » The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
- » My profile
RSS 2.0 Feed
Search This Blog with Google
Search This Blog with Free Find
Search This Blog with Technorati
Recent Posts
- Luca Canali and ASM Metadata Internals
- Christo Kutrovsky's Blog
- The Flashback Database Feature
- Backup & Recovery Presentation
- Back to Blogging
- A Simple Chapter from the "Cost-Based Oracle Funda...
- How to test our Oracle database environment for free
- Oracle Database 10g: Best Practices for Windows De...
- Oracle Database 10g Release 2 for Windows
- Oracle 10g ASM vs. Linux Filesystems, Part 2
Articles & Presentations
- Backup & Recovery Enhancements in Oracle 10g - pdf (EN)
- How to Cope with an ORA-01000 Error - pdf (EN)
- How to Cope with an ORA-01000 Error - doc (EN)
- Oracle Tools & Utilities, System and Oracle Environment Variables, Oracle Registry Parameters and Their Relations - pdf (BG)
- Development of Oracle9i-based Information System - pdf (BG)
- Windows NLS Considerations - pdf (EN)
- Windows NLS Considerations - doc (EN)
Discover Bulgaria
Oracle News & Blogs Aggregators
- Oracle Blogs - Brian Duff
- OraNA Oracle News - Eddie Awad
- Oracle Blogs - Oracle
- Oracle Blogs - Pete Finnigan
- OraFAQ Blog Aggregator
- Oracle Related Tags - del.icio.us - Joshua Schachter
- Oracle 10g Related Tags - del.icio.us - Joshua Schachter
- PL/SQL Related Tags - del.icio.us - Joshua Schachter
- Oracle News - del.icio.us - Joshua Schachter
Oracle Resources
- AppsDBA.com Web Site - Andy Rivenes
- Ask Tom - Thomas Kyte
- Battle Against Any RAID Five (BAARF)
- Christopher Lawson
- Centrex Consulting Corporation - Wolfgang Breitling
- Chesham Database Systems - Mark A. Williams
- Go-Faster Consultancy Ltd. - David Kurtz
- Database Specialists, Inc. - Roger Schrag
- DBAZine Database Portal
- Dizwell Informatics - Howard Rogers
- EvDBT.Com - Tim Gorman
- Hotsos - Carry Millsap
- jDUL/DUDE (Database Unloading by Data Extraction)
- Jeff Hunter's Oracle - Software Engineering Site
- Jeremiah Wilton Consultancy - Jeremiah Wilton
- JL Computer Consultancy - Jonathan Lewis
- integrid.info - Tanel Poder
- Ixora - Steve Adams
- Metalink Oracle Support Site
- Miracle A-S - Mogens Norgaard
- Natural Join B.V. - Lex de Haan
- Oracle and SQL Performance Tuning Specialists - Mark Gurry
- ORACLE-BASE - Tim Hall
- oracledba.co.uk - Connor McDonald
- Oracle FAQ
- Oracle Internals - Julian Dyke
- Oracle PL/SQL Programming - Steven Feuerstein
- Oracle RAC SIG
- OraPerf - Anjo Kolk
- oraperf.sourceforge.net - Kyle Hailey
- OraPub, Inc. - Craig Shallahamer
- OTN - DBA and Sysadmin
- puschitz.com - Werner Puschitz
- Scale Abilities - James Morle
- The OakTable Network
- The Pythian Group - Paul Vallee
- TUSC - The Oracle Experts
Oracle User Groups
Oracle Blogs
- Abhinav Agarwal - Business Intelligence
- Alan Nolan-Davies
- Alejandro Vargas - Scripts and Tips
- Alex Gorbachev
- Amarjyoti Dewri
- AMIS Technology
- Andrej Koelewijn
- Andrew Clarke
- Andy Campbell
- Andy Rivenes - AppsDBA
- Anjo Kolk - Performance
- Bill Thater
- Brian Duff
- Carl Backstrom - Oracle Application Express
- Carl Reitschuster
- Catherine Devlin - Python and Oracle
- Chris Foot
- Christo Kutrovsky
- Christopher Jones - PHP, Linux, OPAL
- Colin Maxwell - Oracle Product Technology Services
- David Aldridge - Data Warehousing
- David Litchfield - Security
- Devidas Dumbre - Oracle Applications
- Dimitri Gielis - Application Express
- Doug Bourns
- Edward Stangler
- Eddie Awad
- Edward Whalen - Oracle on Windows
- Egor Starostin
- Eric S. Emrick
- Frank van Bortel
- Francois Degrelle
- Fuad Arshad
- Gary Myers
- Grant Ronald - Forms and J2EE
- Howard Rogers
- Jeff Hunter
- Jeffrey Kemp
- Jeff Moss
- John Baughman
- Justin Kestelyn - OTN TechBlog
- Laurent Schneider
- Lewis R Cunningham
- Lisa Dobson - Newbies DBA
- Lisa Vaas - Oracle Watch at eWeek
- Lutz Hartmann
- Mark Coleman - Oracle and Unix
- Mark Rittman - Business Intelligence and DW
- Marcos M. Campos - Data Mining
- Mark Wilcox - Identity Management
- Mary Ann Davidson - Security
- Matt Penny - Oracle on Windows
- Michael Armstrong-Smith - Discoverer
- Mike Ault
- Mogens Norgaard
- Niall Litchfield Blog
- Nicholas Goodman - Business Intelligence
- Nigel Thomas
- Nilesh Jethwa - Applications Business Intelligence
- Oracle Security Team
- Oracle WTF - Curious Perversions in Oracle World
- Pete Finnigan - Security
- Peter K
- Peter Lewis
- Peter Scott
- Pythian Group
- Robert Baillie
- Robert Vollman
- Roderick Manalac
- Scott Spendolini - Application Express
- Sergio Leunissen - Application Express
- Steven Feuerstein - PL/SQL Development
- Stefan Roesch - Oracle RAC
- Sue Harper
- Tarry Singh
- Tarry Singh - Performance
- Tim Hall
- Tom Kyte
- Tony Andrews
- Vadim Bobrov - Oracle Development
- Wilfred (OraTransplant)
- Wim Coekaerts - Linux/Open Source
- Yasin Baskan
Oracle Forums
Security Resources
- Argeniss - Cesar Cerrudo
- Black Hat Briefings, Training and Consulting - Jeff Moss
- Database Security - David Litchfield
- Fortify Software - Source Code Security - John M. Jack
- Johny Hack Stuff
- Internet Security Systems (ISS) - Christopher Klaus
- Nessus Open Source Vulnerability Scanner Project
- NGS Software - David Litchfield
- Oracle and Oracle security information - Pete Finnigan
- OWASP - Open Web Application Security Project
- Protegrity Data Protection Solutions - Gordon O. Rapkin
- Red Database Security - Alexander Kornbrust
- SecurityFocus Portal
- SPI Dynamics - Web Application Security and Testing
- Sys-Security - Ofir Arkin
Professional CV
Blog Statistics
