According to many studies, 40% of application outages are caused by operator or user errors. Part of being human is making mistakes. But these errors are extremely difficult to avoid and can be particularly difficult to recover from without advance planning and the right technology. Such errors can result in "logical" data corruption, or cause downtime of one or more components of the IT infrastructure. While it is relatively simple to rectify the failure of an individual component, detection and repair of logical data corruption, such as accidental deletion of valuable data, is a time consuming operation that causes enormous loss of business productivity. Typical user-errors may include accidental deletion of valuable data, deleting the wrong data, and dropping the wrong table. Refer the below URL to get an understanding about Oracle Flashback technology.
http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm
Oracle Flashback Technology:Alternatives to Point-in-Time Recovery:
The flashback features of Oracle are more efficient than media recovery in most circumstances in which they are available. They can also be used to investigate past states of Most Flashback Technology features operate at the logical level, viewing and manipulating database objects, as follows:
Oracle Flashback Query lets you specify a target time and then run queries against your database, viewing results as they would have appeared at that time. To recover from an unwanted change like an erroneous update to a table, a user could choose a target time before the error and run a query to retrieve the contents of the lost rows.
Oracle Flashback Version Query lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start time, end time, operation, and transaction ID of the transaction that created the version. This feature can be used both to recover lost data values and to audit changes to the tables queried.
Oracle Flashback Transaction Query lets you view changes made by a single transaction, or by all the transactions during a period of time.
Oracle Flashback Table returns a table to its state at a previous point in time. You can restore table data while the database is online, undoing changes only to the specified table.
Oracle Flashback Drop reverses the effects of a DROP TABLE statement.
Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query all rely on undo data, records of the effects of each update to an Oracle database and values overwritten in the update. Used primarily for such purposes as providing read consistency for SQL queries and rolling back transactions, these undo records contain the information required to reconstruct data as it stood at a past time and examine the record of changes since that past time.
Flashback Drop is built around a mechanism called the Recycle Bin, which Oracle uses to manage dropped database objects until the space they occupied is needed to store new data.
Oracle Flashback Database provides a more efficient direct alternative to database point-in-time recovery. It is unlike the other flashback features in that it operates at a physical level. When you use Flashback Database, your current datafiles revert to their contents at a past time. The end product is much like the result of a database point-in-time recovery, but can be much faster because it does not require you to restore datafiles from backup, and requires only limited application of redo compared to media recovery.
Flashback Database uses flashback logs to access past versions of data blocks, as well as some information from the archived redo log. To have the option of using Flashback Database to repair your database, you must either configure your database to generate flashback logs, or use the related capability of guaranteed restore points to protect the contents of your database at a fixed point in time, such as immediately before a risky database change.
Performing Flashback and Database Point-in-Time Recovery:
It it sometimes necessary to return some objects in your database or the entire database to a previous state, following the effects of a mistaken database update. For example, a user or DBA might erroneously delete or update the contents of one or more tables, drop database objects that are still needed for a risky operation such as an update to an application or a large batch update might fail.
In addition to point-in-time restore and recovery of the entire database, Oracle provides a group of features known as Oracle Flashback Technology, that are often faster than point-in-time recovery, and less disruptive to database availability.
This URL presents a guide to investigating unwanted database changes, and selecting and carrying out an appropriate recovery strategy based upon Oracle Flashback Technology and database backups.
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr.htm#i1006083
Flashback Database:
Flashback Database is introduced with 10gr1, It is alternative for PITR (Point In Time Recovery).
It is a superb feature, it can also be called "rewind button" for database because through this option we can rewind database to any point of time.
1. How to ENABLE Flashback Database option.
By Default it is DISABLE so we need to enable this option.
2. Requirement for the flashback database
a: Database must run in archivelog mode
b: Following parameters are set in database initialization parameters:
- db_recovery_file_dest
- db_recovery_file_dest_size
- db_flashback_retention_target
a: connect with SYS user and shutdown database with normal
sqlplus / as sysdba
sql>shutdown
b: startup database with MOUNT mode
sql>startup mount
c: Enable FLASHBACK DATABASE
sql>alter database flashback on;
d: Open the database with normal operation
sql>alter database open
e: Check flashback database option enable for the database
sql> select flashback_on from v$database;
Test a Flashback Scenario:
Make sure the database flashback is enabled to do the below test.
1. shutdown the database
sql>select current_scn from v$database;
sql>drop table scott.emp purge;
sql>select current_scn from v$database;
2. flashback database before table was dropped
sql>shutdown immediate
sql>startup mount
sql>flashback database to scn 542151;
3. open database with read only
sql>alter database open read only;
sql>desc scott.emp
sql>! exp system/oracle file=/tmp/emp.dmp tables=scott.emp
5. shutdown and startup database with mount mode
sql>shutdown immediate;
sql> startup mount;
6. perform recover database
sql> recover database;
7. open database with normal operation without resetlogs
sql> alter database open;
sql>desc scott.emp
sql>! imp system/oracle file=/tmp/emp.dmp fromuser=scott touser=scott
sql>desc scott.emp (The table is back now)
Oracle Flashback Query: Recovering at the Row Level using Timestamp Feature:
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr002.htm#sthref599
RVWR Background Process:
Enabling Flashback Database starts a new RVWR background process. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.
The following list below shows all the background processes for “grid” instance:
$ ps -ef grep grid
oracle 25124 1 0 16:32:05 ? 0:00 ora_s000_grid
oracle 25116 1 0 16:32:04 ? 0:00 ora_reco_grid
oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid
oracle 25112 1 0 16:32:04 ? 0:00 ora_ckpt_grid
oracle 25110 1 0 16:32:04 ? 0:00 ora_lgwr_grid
oracle 25108 1 0 16:32:04 ? 0:00 ora_dbw0_grid
oracle 25114 1 0 16:32:04 ? 0:00 ora_smon_grid
oracle 25118 1 0 16:32:04 ? 0:00 ora_cjq0_grid
oracle 25120 1 0 16:32:04 ? 0:00 ora_rbal_grid
oracle 25122 1 0 16:32:04 ? 0:00 ora_d000_grid
oracle 25106 1 0 16:32:04 ? 0:00 ora_pmon_grid
Monitor logging in the Flashback Database logs:
SQL> select begin_time, flashback_data, db_data, redo_data, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_stat;
Monitor the Flashback Database retention target:
SQL> select from v$flashback_database_log;
Adjust recovery area disk quota:
SQL> select estimated_flashback_size from v$flashback_database_log;
Flashback a Database Using RMAN:
RMAN> FLASHBACK DATABASE
2> TO TIME = TO_DATE
3> (‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’);
Flashback a database using SQL command:
The database must be in mount state to issue these commands:
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 5/24);
SQL> FLASHBACK DATABASE TO SCN 76239;
You must issue the follow command afterwards:
SQL> ALTER DATABASE RESETLOGS;
Restoring a dropped object:
Recyclebin:-
You can view the dropped objects in the recycle bin from two dictionary views:
USER_RECYCLEBIN — list all dropped user objects.
DBA_RECYCLEBIN — list all dropped system-wide objects.
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:04:03 2004-02-21:19:04:41
SQL> flashback table “BIN$0+ktoVCgEmXgNAAADiUEHQ==$0” to before drop;
Dropping a table permanently:
This statement puts the table in the recycle bin:
SQL> drop table test purge;
Table dropped.
This statement removes the table permanently:
SQL> purge table "BIN$0+ktoVCgEmXgNAAADiUEHQ==$0";
Table purged.
Purging the Recycle Bin:
This statement purges the user recycle bin:
SQL> purge recyclebin;
Recyclebin purged.
This statement removes all objects from the recycle bin:
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
This statement purges all objects from tablespace users in the recycle bin:
SQL> purge tablespace users;
Tablespace purged.
Important Notes:
1) objects will go to recyclebin is completely based on recyclebin parameter settings.If I set alter system set recyclebin=off then object will not go in recycle bin.
2)Dropped SYS and SYSTEM schema objects don't go to recyclebin.
3)The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names.Views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.Like,
sql>SELECT OBJECT_NAME, ORIGINAL_NAME, TYPEFROM USER_RECYCLEBINWHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN WHERE ORIGINAL_NAME = 'TEST') AND ORIGINAL_NAME != 'TEST';
OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpdfanfgMAAAAAANPw==$0 TEST_01 INDEX
BIN$04LhcpdfanfgMAAAAAANPw==$0 TR_TEST TRIGGER
After the table is flashed-back, the indexes and triggers on the table TEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:
sql>ALTER INDEX "RECYCLE BIN NAME" RENAME TO TEST_01;
sql>ALTER TRIGGER "RECYCLE BIN NAME" RENAME TO TR_TEST;
But exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin and so they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.
4) To do flashback enable row movement must be enabled.
sql>alter table test enable row movement;
Flashback Standby Database:
If you have multiple standby sites, you may utilize the DELAY option in Data Guard to prevent physical/logical corruption or user errors in your primary database.
For example, the first scenario in the diagram below has only one standby database. Here, a logical or physical corruption in the primary database will cause an immediate corruption in the standby database. To avoid such a pitfall, you can implement a second standby database with the 'Delay' option (introducing a delay of minutes or hours on the second standby database for applying archive log changes). This will prevent the corruptions on the second standby database and allow recovery from a possible physical/logical corruption or user errors in the primary database.
You can issue the following command to accomplish this:
SQL> alter database recover managed standby database delay 60 disconnect;
However, in Oracle 10g, you can configure the standby database with Flashback Database to achieve the same benefit as the DELAY option. Therefore, there is no need to implement a second standby database with the DELAY option. Refer the below URL for more information:
http://www.orafaq.com/node/31
Flashback Re-instantiation: (Nice Feature of Oracle Flash Back)
In an Oracle9i Data Guard environment, a failover operation leads to a resetlogs. This operation invalidates the old primary database. Therefore, you need to perform a hot backup on the new primary database immediately, and then re-create a new standby database. This operation can take a long time, and your new primary database is vulnerable during this period.
The new Flashback Re-instantiation feature reduces the need to reinstantiate the old primary database following a failover. This feature allows you to quickly restore full resiliency after a failure. This is accomplished by using the SQL statement FLASHBACK DATABASE to roll back the old primary database in time to synchronize with the old standby database.
Here are the steps to perform Flashback Re-instantiation:
1: On your new primary database (Instance B):
SQL> select standby_became_primary_scn from v$database;
STANDBY_BECAME_P
----------------
2960985
2. Mount the old primary database (Instance A).
SQL> STARTUP MOUNT
3. Flashback the old primary database (Instance A) to the SCN.
SQL> flashback database to scn 2960985;
4. Disable Flashback on the old primary database (Instance A).
SQL> ALTER DATABASE FLASHBACK OFF;
5. On the old primary database (Instance A), create a standby control file.
SQL> alter database create standby controlfile as'/dba/standby/stbycf.f' reuse;
6. Shutdown the old primary database (Instance A), and replace the control files with the new standby control files.
SQL> shutdown immediate;
$ cp /dba/standby/stbyct.f /u02/oradata/sid/control01.ctl
$ cp /dba/standby/stbyct.f /u02/oradata/sid/control02.ctl
7. Bring up the old primary database as a new physical standby database (Instance A).
SQL> startup mount;
8. Turn flashback back on the new primary database (Instance A).
SQL> alter database flashback on;
9. Enable transport from the new primary database (Instance B)
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
10. On the new standby database (Instance A), start real time apply.
SQL> RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT;
11. The Managed Recovery process (MRP) will hit the End-Of-Redo and then need to be restarted.
SQL> RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT;
A beautiful document covering most of Flash back features of Oracle database.
http://www.orafaq.com/node/31
No comments:
Post a Comment