Topic: Database Administration >> Flashback drop
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| ajay |
Posted: Sep 04, 2007 09:31:41 AM |
Total Post: 24
Joined: Mar, 2007
|
hi dba's
i have small confusion
1)Recyclebin is allocated to each an every user
2)recyclebin is logically allocated on locallay managed tablespace witch can be used by all the database users
3)there will be only one recyclebin for all the database users
which one is true
Help me dba's
thanks
uday |
|
|
 Mohammed Taj |
| Posted: Sep 04, 2007 09:52:19 AM |  |
|
Total Post: 746
Joined: Jul, 2007
|
1)Recyclebin is allocated to each an every user
Yes.
2)recyclebin is logically allocated on locallay managed tablespace witch can be used by all the database users
Yes.
3)there will be only one recyclebin for all the database users
No.
Regards
Taj
|
|
|
|
|
| ajay |
| Posted: Sep 04, 2007 11:57:56 PM | |
|
Total Post: 24
Joined: Mar, 2007
|
hi taj....
one more confusion
how it works...is it like when we create users and allocate quota on any tablespace recyclebin will be created for that user according to quota allocated.... is it right
it means if the quota is allocated for 10 users in any tablespace then we will have 10 recyclebin.........am i right
thanks
Regards
uday
|
|
|
|
|
| nick |
| Posted: Sep 11, 2007 11:43:02 AM | |
|
Total Post: 30
Joined: Sep, 2007
|
If you have created 10 users on a tablespace and recyclebin parameter is ON in pfile, all users will get the seprate recyclebin.
alter system set recyclebin = on
You may also need to set mcople of more parameters:
DB_RECOVERY_FILE_DEST_SIZE
DB_RECOVERY_FILE_DEST
|
|
|
|
|
| Vinod |
| Posted: Oct 20, 2007 02:01:20 AM | |
|
Total Post: 54
Joined: Oct, 2007
|
* What Do All Flashback Features Rely on ?
* Differences Between New Oracle 10g Flashback Technologies
* Limitations of Flashback Database
* Limitations and Restrictions on Flashback Drop
* Limitations and Restrictions on Flashback Tables
Solution
The New Oracle 10g Flashback Technologies Consist Of The Following :
* Flashback Database
* Flashback Drop
* Flashback Table
* Flashback Versions Query
* Flashback Transaction Query
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 all changes since that time.
There is Two main differences with these flashback technologies and the new Oracle 10g,
The Flashback Database relies on before images in the flashback logs, but traditional flashback technology relies on the undo data, Also 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.
* Flashback Database :
The Flashback Database allows you to flash the entire database back to a specific point-intime.
It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is not based on undo data but on flashback logs.
If flashback database is enabled, its flashback logs are stored in the Flash Recovery area.
Flashback logs are written sequentially During normal database operation, and they are not archived. Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.
The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.
DB_FLASHBACK_RETENTION_TARGET A parameter value that determines how far back in time you can recover the flashback database, This value is in minutes.
The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.
The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:
SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;
Limitations of Flashback Database :
Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:
* Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.
* You cannot use Flashback Database to undo a shrink datafile operation.
* If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.
* When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation completes.
If possible, avoid using Flashback Database with a target time or SCN that coincides with a NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use Flashback Database to return to a point in time during an operation such as a direct-path INSERT, consider performing the operation in LOGGING mode.
* Flashback Drop :
This Feature provides a way to restore accidentally dropped tables.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle places it in a recycle bin. Objects in the recycle bin remain there until the user decides to permanently remove them or until space limitations begin to occur on the tablespace containing the table. The recycle bin is a virtual container where all dropped objects reside. Users view the recycle bin and undrop the dropped table and its dependent objects.
Flashback Drop is available even after restarting the database.
Limitations and Restrictions on Flashback Drop :
* The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.
* There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
* While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects in the recycle bin.
* You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
* A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together , It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
* Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
* Partitioned index-organized tables are not protected by the recycle bin.
* The recycle bin does not preserve referential constraints on a table (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.
* Flashback Table :
Flashback Table happens in place by rolling back only the changes made to the table or tables and their dependent objects, such as indexes. Note that Flashback Table is different from Flashback Drop: Flashback Table undoes recent transactions to an existing table whereas Flashback Drop recovers a dropped table; Flashback Table uses data in the undo tablespace whereas Flashback Drop uses the recycle bin.
The FLASHBACK TABLE command brings one or more tables back to a point in time before any number of logical corruptions have occurred on the tables. To be able to flashback a table, you must enable row movement for the table; because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. As a result, Flashback Table is not a viable option for
applications that depend on the table’s ROWIDs to remain constant.
Before performing the Flashback Table operation, you first enable row movement in the affected tables, as in the following syntax:
SQL> alter table table_name enable row movement;
Limitations and Restrictions on Flashback Tables :
* Flashback Table operations are not valid for the following type objects: tables that are part of a cluster, materialized views, Advanced Queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables, or individual table partitions or subpartitions.
* The following DDL operations change the structure of a table, so that you cannot subsequently use the TO SCN or TO TIMESTAMP clause to flash the table back to a time preceding the operation: upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).
* Flashback Query :
Flashback Query, available in Oracle 9i, has been enhanced to include two new types of queries:
Flashback Versions Query and Flashback Transaction Query.
Flashback Versions Query allows a user or the DBA to see all versions of a table’s row between two times, and with Flashback Transaction Query you can see all transactions that changed a row between two times.
Flashback Versions Query provides an easy way to show all versions of all rows in a table between two SCNs or time stamps, whether the rows were inserted, deleted, or updated. Even if a row was deleted and reinserted several times, all of these changes are available with Flashback Versions Query.
Flashback Transaction Query, in contrast, drills down into the history of table changes based on a transaction ID. Using Flashback Versions Query, you found out which transaction changed the salary information, but you don’t know who made the change. Flashback Transaction Query provides this additional level of detail.
In contrast to referencing the actual table in Flashback Versions Query, Flashback Transaction Query uses the data dictionary view FLASHBACK_TRANSACTION_QUERY to retrieve transaction information for all tables involved in a transaction. This view provides the SQL statements that you can use to undo the changes made by a particular transaction.
======================================================================
Thanks & Regards,
Vinod Sadanandan
Oracle DBA
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
