Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (2023)

by Arup Nanda

Transaction Management with LogMiner and Flashback Data Archive

Learn how to identify and roll back time by backing-out specific transactions and their dependents.

LogMiner is an often ignored yet very powerful tool in the Oracle Database. It is used to extract DML statements from the redo log files—the original SQL that caused the transaction and even the SQL that can undo the transactions. (For an introduction to LogMiner and how it works, refer to my Oracle Magazine article "Mining for Clues.") Until now, this powerful tool was commonly under-appreciated due to the lack of a simpler interface. In Oracle Database 11g, however, Oracle Enterprise Manager has a graphical interface to extract transaction from the redo logs using LogMiner, which makes it extremely easy to use the tool to examine and rollback transactions. (Note: As in previous versions, you can continue to use the DBMS_LOGMNR package to perform command line-driven log mining if you wish.)

Let's see an example how this is done. To enable log mining, you need only minor supplemental logging enabled for the database or at least the table. Flashback Transaction requires primary key logging. To enable it for the entire database, issue the following commands:

Copy

SQL> alter database add supplemental log data; Database altered. SQL> alter database add supplemental log data (primary key) columns; Database altered.

Now, consider the following statements issued by an application against your database:

Copy

SQL> insert into res values (100002,sysdate,12,1); 1 row created. SQL> commit; Commit complete.SQL> update res set hotel_id = 13 where res_id = 100002;1 row updated. SQL> commit; Commit complete.SQL> delete res where res_id = 100002; 1 row deleted. SQL> commit; Commit complete.

Note the statements carefully: each one is succeeded by a commit statement, which indicates that each statement is a transaction. Now let's see how you can examine the transactions in LogMiner in Oracle Database 11g Database Control.

In the Enterprise Manager screen, from the Database homepage, go to the tab labeled Availability.

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (1)

Click View and Manage Transactions, listed under Manage. This brings up the main LogMiner interface, as shown below:

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (2)

You can enter specific ranges in time or SCNs to search for transactions. In the figure above, I have entered a range of time to search in the Query Time Range. In the Query Filter, I have used only the SCOTT's transactions, because that was used to perform all the DMLs. In the Advanced Query section, you can enter any additional filter. After all fields are entered, click Continue.

This kicks up the Log Mining process that searches through the redologs (both online and archived, if needed) and finds the transactions issued by the user SCOTT. After the process is completed, you will see the results screen.

The top portion of the results screen looks like this:

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (3)

The results indicate that the search found two transactions by SCOTT, which affected two records.

The bottom portion of the screen shows the details of those transactions. Here is a partial view of the screen. You can see the transactions show as 1 ins (meaning "1 insert statement"). The leftmost column shows the transaction identifiers (XID), a number that uniquely identifies a transaction.

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (4)

If you click on that transaction identifier, you can see the details of that transaction as shown in the screen below:

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (5)

As you can see, you can use Database Control to search and identify the transactions. Click the buttons Previous Transaction and Next Transaction to scroll through all the transactions found by the search.

Use Cases

How can you use this feature? Well, several ways. The most important use may be to find out "who" did "what." If you don't have auditing enabled for performance reasons, or just haven't kept the audit records, all you have to do is to search for the clues in the LogMiner interface by mining the redo logs—online as well as archived ones. In the search screen, you can enter additional filtering conditions in the Advanced Query field under Query Filter.

Suppose you want to find a transaction where the record for the RES_ID = 100002 was inserted, deleted, or updated. You can search for a specific value in the redo stream by using the function column_present in the dbms_logmnr package as shown below:

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (6)

This function will extract all the transactions that involved 100002 in the RES_ID column in RES table under the SCOTT schema.

(Video) How and Why to Use Oracle Flashback Features

You can also use this feature to unearth the DDL commands issued against the database. To do that, select the radio button View DDL Only in the Query Filter section.

Backout of Selected Transactions

When you examine a transaction, what do you want do with it? One thought—perhaps the reason you are looking into the transaction in the first place—is that the transaction was made in error and you want to undo it. That's fairly simple; if the transaction is an insert, you just have to delete it; or if it is an update, then the undo will be updating the row to the older value.

However, note the transactions used in the example carefully. The first transaction inserts a row. The second one updates the row just inserted and the third one deletes that very row. The first one (the insert) is the transaction you want to backout. But, here is a problem; the row is already deleted by the subsequent transactions; so what is the undo transaction going to be, in this case?

This is where the Dependent Transaction viewing feature in Oracle Database 11g comes handy. Click Flashback Transaction. After some searches, it will present a screen similar to below:

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (7)

This screen shows you the dependent transactions and update and deletes as well. Now when you back-out the transaction, you can back-out the dependents as well. To do so, choose the Cascade radio button from the list below and click OK.

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (8)

It will show you the different transactions you want backed out; click the Transaction IDs to see that what SQL statements Oracle will issue to undo the specific transaction.

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (9)

For instance, to undo the insert, it has to issue a delete, as shown above. If you click on the next transaction (just below it), you will see the details of what needs to be done to back that one out:

Oracle Database 11g: The Top New Features for DBAs and Developers | Transaction Management with LogMiner and Flashback Data Archive (10)

You get the idea. Click Submit and all these transactions will be rolled back, in one sweep. This is the cleanest way to undo a transaction and its dependents.

Command Line Interface

What if you don't have access to the Enterprise Manager or perhaps you want this done through a script? The package DBMS_FLASHBACK, which is also present in Oracle Database 10g, has a new procedure called TRANSACTION_BACKOUT. This procedure is overloaded so you have to pass the value to the named parameters, as shown below.

Copy

declare trans_arr xid_array;begin trans_arr := xid_array('030003000D040000','F30003000D04010'); dbms_flashback.transaction_backout ( numtxns => 1, xids => trans_arr, options => dbms_flashback.cascade );end;

The type xid_array is also new in Oracle Database 11g. It is present to pass a series of transaction identifiers to the procedure.

Other LogMiner Improvements

If you have been using XMLType as a data type and you have more reasons to use it in Oracle Database 11g, you will be happy to see that the XML data is mined as well in LogMiner. It shows up both in SQL_REDO and SQL_UNDO columns.

You can set an option called SKIP_CORRUPTION while starting LogMiner which will skip the corrupt blocks in redo logs. So, you can still salvage valid data from the redo logs even if it is partially damaged. Here is how you can use the improved syntax:

Copy

begin dbms_logmnr.start_logmnr( options => dbms_logmnr.skip_corruption ) ;end;

Flashback Data Archive

Oracle9i Database Release 2 introduced the proverbial time machine in the form of the Flashback Query, which allows you to select the pre-changed version of the data. For example, had you changed a value from 100 to 200 and committed, you can still select the value as of two minutes ago even if the change was committed. This technology used the pre-change data from the undo segments. In Oracle Database 10g, this facility was enhanced with the introduction of Flashback Versions Query, where you can even track the changes made to a row as long as the changes are still present in the undo segments.

However, there was a little problem: When the database is recycled, the undo data is cleaned out and the pre-change values disappear. Even if the database is not recycled, the data may be aged out of the undo segments to make room for new changes.

Since pre-11g flashback operations depend on the undo data, which is available only for a short duration, you can't really use it over an extended period of time or for more permanent recording such as for auditing. As a workaround, we resorted to writing triggers to make more permanent records of the changes to the database.

Well, don't despair. In Oracle Database 11g, Flashback Data Archive combines the best of both worlds: it offers the simplicity and power of the flashback queries but does not rely on transient storage like the undo. Rather, it records changes in a more permanent location, the Flashback Recovery Area.

Let's look at an example. (Note: you need to activate Automatic Undo Management for Flashback Data Archive to work.) First, you create a Flashback Data Archive, as shown below:

Copy

SQL> create flashback archive near_term 2 tablespace far_near_term 3 retention 1 month 4 / Flashback archive created.

For the time being ignore the meaning of the term "retention"; we will revisit it later. (This is a location where the changes will be recorded.) The archive is created in the tablespace far_near_term.

Assume you have to record changes to a table called TRANS. All you need to do is enable the Flashback Data Archive status of the table to start recording the changes in that archive.

Copy

SQL> alter table trans flashback archive near_term;Table altered.
(Video) Introduction to Toad for Oracle for DBAs - in-depth tutorial

This puts the table into the Flashback Data Archive mode. All the changes to the rows of the table will be now tracked permanently. Let's see a demonstration.

First, select a specific row of the table.

Copy

SQL> select txn_amt from trans where trans_id = 2; TXN_AMT---------- 19325.67SQL> update trans set txn_amt = 2000 where trans_id = 2;1 row updated.SQL> commit;Commit complete.

Now, if you select the row, it will always display 2000 in this column. To find out the older value as of a certain time, you can use the Flashback query as shown below:

Copy

elect txn_amtfrom transas of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')where trans_id = 2; TXN_AMT---------- 19325.67 

Now, after some time, when the undo data has been purged out of the undo segments, query the flashback data again:

Copy

select txn_amtfrom transas of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss')where trans_id = 2;

It comes back with the result: 19325.67. The undo is gone, so where did the data come from?

Let's ask Oracle. You can do that using autotrace and see the execution plan:

Copy

SQL> set autotrace traceonly explainSQL> select txn_amt 2 from trans 3 as of timestamp to_timestamp ('07/18/2007 12:39:00','mm/dd/yyyy hh24:mi:ss') 4 where trans_id = 2;Execution Plan----------------------------------------------------------Plan hash value: 535458644----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop-------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 52 | 10 (10)| 00:00:01 | || 1 | VIEW | | 2 | 52 | 10 (10)| 00:00:01 | || 2 | UNION-ALL | | | | | | ||* 3 | FILTER | | | | | | || 4 | PARTITION RANGE SINGLE| | 1 | 52 | 3 (0)| 00:00:01 | 1 | 1|* 5 | TABLE ACCESS FULL | SYS_FBA_HIST_68909 | 1 | 52 | 3 (0)| 00:00:01 | 1 | 1|* 6 | FILTER | | | | | | ||* 7 | HASH JOIN OUTER | | 1 | 4053 | 10 (10)| 00:00:01 | ||* 8 | TABLE ACCESS FULL | TRANS | 1 | 38 | 6 (0)| 00:00:01 | || 9 | VIEW | | 2 | 8030 | 3 (0)| 00:00:01 | ||* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_68909 | 2 | 8056 | 3 (0)| 00:00:01 | |-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - filter(NULL IS NOT NULL) 5 - filter("TRANS_ID"=2 AND "ENDSCN">161508784336056 AND "ENDSCN"<=1073451 AND ("STARTSCN" IS NULL OR "STARTSCN"<=161508784336056)) 6 - filter("F"."STARTSCN"<=161508784336056 OR "F"."STARTSCN" IS NULL) 7 - access("T".ROWID=("F"."RID"(+))) 8 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."TRANS_ID"=2) 10 - filter(("ENDSCN" IS NULL OR "ENDSCN">1073451) AND ("STARTSCN" IS NULL OR "STARTSCN"<1073451))Note----- - dynamic sampling used for this statement

This output answers the riddle "Where did the data come from?"; it came from the table SYS_FBA_HIST_68909, which is a location in the Flashback Archive you defined earlier for that table. You can check the table but it's not supported by Oracle to directly peek at that data there. Anyway, I don't see a reason you would want to do that.

The data inside the archive is retained but until how long? This is where the retention period comes into play. It's retained up to that period. After that, when new data comes in, the older data will be purged. You can also purge it yourself, e.g.

alter flashback archive near_term purge before scn 1234567;

Release 2 Addendum:

In Release 2, FDA functionality is expanded to include DDL operations such as adding/dropping/renaming/modification of columns, Partition or Subpartition Drops/Truncates, Truncate Table, Rename Table and Adding/Dropping/Renaming of Constraints. When you perform these DDL operations the history tables are automatically updated. There is no need for manual intervention.

Let’s see that in action. We will need to enable flashback for some table. First we will create a tablespace to hold the flashback data (the history tables) and create a flashback archive in that tablespace:

Copy

SQL> create tablespace fda datafile '+DATA' size 100M;Tablespace created.SQL> create flashback archive near_termtablespace fdaretention 1 monthFlashback archive created.Now let’s create an example table.SQL> conn arup/arupConnected.SQL> create table trans( trans_id number, trans_dt date, product_id number(10), quantity number(5), trans_amt number(15,2))/

Let’s enable flashback for this table using the flashback archive created earlier and enable row movement as well:

Copy

SQL> alter table trans flashback archive near_term;Table altered.SQL> alter table trans enable row movement 2 /Table altered.
(Video) Franck Pachot: "19 features you will miss if you leave Oracle Database"

Now that this table is flashback enabled, let’s perform a simple DML operation:

Copy

SQL> insert into trans values (1,sysdate,1,1,1000);1 row created.SQL> commit;Commit complete.

Inserts do not create archive records; because they are not changes to data. Let’s perform an update:

Copy

SQL> update trans set trans_amt = 2000;1 row updated.SQL> commit;Commit complete.Now, let’s perform DDL operation to alter the structure:SQL> alter table trans add (price number(15,2));Table altered.

Note: the table was altered easily, even though it was flashback enabled. How would Oracle Database know about this new column in the history tables? To track this alteration, it actually creates several new tables for history data. Let’s take a look at those tables:

Copy

SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------SYS_FBA_DDL_COLMAP_79410 TABLESYS_FBA_HIST_79410 TABLESYS_FBA_TCRV_79410 TABLETRANS TABLE

Note these new tables with FBA in their names; these are the special history tables. The number 79410 is the object_id of the table TRANS. The table SYS_FBA_TCRV_79410 records the operations performed on this table, as you can see below. The Operation column shows I (for Insert) and U (for Update).

Copy

SQL> select * from SYS_FBA_TCRV_79410;RID-------------------------------------------------------------------------------- STARTSCN ENDSCN XID O---------- ---------- ---------------- -AAATYyAAEAAAATkAAA 13585319 13585472 09001B00CF430000 IAAATYyAAEAAAATkAAA 13585472 05001B000C440000 U

The table SYS_FBA_HIST_79410 records the changes to the rows across DDL operations.

Copy

SQL> select * from SYS_FBA_HIST_79410;RID-------------------------------------------------------------------------------- STARTSCN ENDSCN XID O TRANS_ID TRANS_DT PRODUCT_ID---------- ---------- ---------------- - ---------- --------- ---------- QUANTITY TRANS_AMT PRICE---------- ---------- ----------AAATYyAAEAAAATkAAA 13585319 13585472 09001B00CF430000 I 1 11-AUG-10 1 1 1000

Note that there is a PRICE column. It’s because that column was added to the main table and Oracle automatically added it to the history table as well. But when the update was done, there was no PRICE column; therefore the column value is null in this tracking table.

Finally, the table SYS_FBA_DDL_COLMAP_79410 tracks the column additions. If some column is renamed, this table tracks the old and new names:

Copy

SQL> select * from SYS_FBA_DDL_COLMAP_79410; STARTSCN ENDSCN XID O COLUMN_NAM TYPE HISTORICAL------------------- -------------------------- - ---------- ------------ ---------- 13585094 TRANS_ID NUMBER TRANS_ID 13585094 TRANS_DT DATE TRANS_DT 13585094 PRODUCT_ID NUMBER(10) PRODUCT_ID 13585094 QUANTITY NUMBER(5) QUANTITY 13585094 TRANS_AMT NUMBER(15,2) TRANS_AMT 13585719 PRICE NUMBER(15,2) PRICE6 rows selected.

Let’s see the effect of another modification – renaming the column TRANS_AMT to TOT_AMT:

Copy

SQL> alter table trans rename column trans_amt to tot_amt;Table altered.Checking the column mapping table:SQL> select * from SYS_FBA_DDL_COLMAP_79410; STARTSCN ENDSCN XID O COLUMN_NAM TYPE HISTORICAL ---------- ---------- ---------------- - ---------- ------------- ---------- 13585094 TRANS_ID NUMBER TRANS_ID 13585094 TRANS_DT DATE TRANS_DT 13585094 PRODUCT_ID NUMBER(10) PRODUCT_ID 13585094 QUANTITY NUMBER(5) QUANTITY 13585094 13587833 TOT_AMT NUMBER(15,2) TRANS_AMT 13585719 PRICE NUMBER(15,2) PRICE 13587833 TOT_AMT NUMBER(15,2) TOT_AMT7 rows selected.
(Video) Oracle 11g Data Guard(fal_server,fal_client): A Complete Look At DG Technology Tutorial 5

You can see the new row there that shows at what SCN the column was renamed. The column TOT_AMT shows its historical name was TRANS_AMT.

You still can’t drop a table that is flashback enabled. Well, if you drop the table it may not be relevant anyway. But one common operation may be the alter table exchange partition. How? By temporarily disassociating the table and then reassociating:

Copy

SQL> begin 2 dbms_flashback_archive.disassociate_fba('ARUP','TRANS'); 3 end; 4 /PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

After the operation, you can reassociate:

Copy

SQL> begin 2 dbms_flashback_archive.reassociate_fba('ARUP','TRANS'); 3 end; 4 /PL/SQL procedure successfully completed.

With this, the flashback archive status of a table doesn’t have to be temporarily suspended to allow DDLs, which are expected to happen quite frequently in a normal database operation.

Managing Flashback Archives

You can add more than one tablespace to an archive. Conversely you can remove a tablespace from one too. If you are planning to use a tablespace that has other user data as well, you run into the risk of crowding the tablespace with the Flashback Data Archive data and leaving no space for the user data. To reduce the risk, you can establish a quota on how much space the archive can take inside the tablespace. You can set the quota by:

alter flashback archive near_term modify tablespace far_near_term quota 10M;

You can check which tables have Flashback Data Archive turned on by querying the dictionary view:

Copy

SQL> select * from user_flashback_archived_tables;TABLE_NAME OWNER_NAME------------------------------ ------------------FLASHBACK_ARCHIVE_NAME-------------------------------------------------TRANS ARUPNEAR_TERM

You can find out about the archives by querying the dictionary view:

Copy

sql> select * from flashback_archives;FLASHBACK_ARCHI FLASHBACK_ARCHIVE# RETENTION_IN_DAYS PURGE_SCN STATUS--------------- ------------------ ----------------- ---------- -------NEAR_TERM 1 30 1042653MED_TERM 2 365 1042744LONG_TERM 3 1825 1042838

Using multiple archives lets you use them creatively in different situations. For instance, a hotel company's database may need one year of reservation data but three years of payments. So you can define multiple archives with different retention policies and assign them to the tables. Or if you have a uniform retention policy, you can define only one archive and make it the default.

alter flashback archive near_term set default;

When you don't need an archive for a table, you can turn it off with:

alter table trans no flashback archive;

As you can see, you just enabled a powerful change recording system without writing a single line of code.

Differences vs. Regular Auditing

How does Flashback Data Archive differ from regular auditing? First of all, the latter requires the audit_trail parameter be set to DB or DB_EXTENDED and the trails are written to the table called AUD$ in the SYSTEM tablespace. Flashback Data Archives can be defined on any tablespace (or more than one, even on parts of a tablespace where user data exists) and therefore can be defined on cheaper storage.

Second, auditing is based on autonomous transaction, which has some performance overhead. Flashback Data Archives are written by a dedicated background process called FBDA so there is less impact on performance.

Finally, Flashback Data Archives can be purged at regular intervals automatically. Audit trails must be manually maintained.

(Video) Managing Database Storage Structure | Oracle DBA Training | Uplatz

Use Cases

Flashback Data Archive is handy for many purposes. Here are some ideas:

  • To audit for recording how data changed
  • To enable an application to undo changes (correct mistakes)
  • To debug how data has been changed
  • To comply with some regulations that require data must not be changed after some time. Flashback Data Archives are not regular tables so they can't be changed by typical users.
  • Recording audit trails on cheaper storage thereby allowing more retention at less cost

Conclusion

Mistakes happen but now you can assert with authority that you will be able to identify the specific changes that caused the mistake, and you have tools to roll back those mistakes in entirety using the transaction back-out. But you are no longer limited to mining the changes from the archived and online redo logs only; the changes are recorded for perpetuity in the Flashback Archives. Now you can audit the changes the tables for all practical purposes in the Flashback Recovery Area, using just a few commands.

See Series TOC

FAQs

What are the features of Oracle 11g? ›

  • RMAN Enhancements in 11g.
  • Duplicating a Database.
  • Creating a Standby Database with the DUPLICATE Command.
  • Parallel Backup and Restore for Very Large Files.
  • IMPORT CATALOG RMAN Command.
  • RMAN Data Recovery Commands.
  • RMAN Security Enhancements.
  • Improved Integration of RMAN and Data Guard.

What is the use of flashback in Oracle? ›

Use the FLASHBACK DATABASE command to rewind the database to a target time, SCN, or log sequence number. This command works by undoing changes made by Oracle Database to the data files that exist when you run the command. Flashback can fix logical failures, but not physical failures.

What is Flashback Data Archive? ›

A flashback data archive consists of multiple tablespaces and stores historic data from all transactions against tracked tables. The data is stored in internal history tables. Flashback data archives retain historical data for the time duration specified using the RETENTION parameter.

Which new feature allows Oracle RMAN to back up larger databases more efficiently? ›

Parallel Backup of the Same Datafile

This RMAN command allocates two channels and backs up the users' tablespace in parallel on two channels. Each channel takes a 500MB section of the datafile and backs it up in parallel. This makes backup of large files faster.

What is new in Oracle 11g? ›

In Oracle Database 11g PL/SQL has a new construct called CONTINUE, which is used in a loop. The statement moves the logic to the end of the loop and then to the beginning of the loop. Here is a small example that shows how the control moves to the end of the loop when the counter is not a multiple of 10.

What is Oracle 11g used for? ›

Database 11g Release 1 (11.1) Doc

Oracle Database 11g delivers industry leading performance, scalability, security and reliability on a choice of clustered or single-servers running Windows, Linux, and UNIX.

Which of the following is an advantage of Oracle Database a portability B Flashback technology D All of the above? ›

Explanation: The following are the advantages of an Oracle database: Performance: Oracle has procedures and principles that help us to get high levels of database performance. Portability: The Oracle database can be ported on all different platforms than any of its competitors.

Which SQL statement from the following is used to create a new tablespace? ›

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE . You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace.

How do I backup my Oracle 11g database? ›

Backing Up the Database
  1. On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 11g Express Edition, and then select Backup Database.
  2. On Linux with Gnome: In the Applications menu, point to Oracle Database 11g Express Edition, and then select Backup Database.

What is RMAN in Oracle 11g? ›

Overview of the RMAN Environment

Recovery Manager (RMAN) is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies backing up, restoring, and recovering database files.

How many types of incremental backups are there? ›

There are 3 main types of backup: Full, differential and incremental.

What are the features of a flashback? ›

In fiction, a flashback is a scene that takes place before a story begins. Flashbacks interrupt the chronological order of the main narrative to take a reader back in time to the past events in a character's life.

Does Oracle Flashback use archive logs? ›

Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation. You must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area.

How do I create a flashback in Oracle? ›

How to Enable Oracle 12c Flashback Database
  1. Shut down the database and start up the database in mount mode in SQL*Plus:
  2. Confirm that the database is in ARCHIVELOG mode, which is required for Flashback Database, and enable ARCHIVELOG mode if needed.
  3. Set the flashback retention target to the desired value (in minutes).
30 Oct 2015

Is Oracle 11g still available? ›

From the 31st December 2020, Oracle are stopping extended support on 11g database. This could have a huge impact on your business if you're running 11g, as you'll no longer be supported. This carries a significant risk to your business continuity and performance, should you encounter a significant database incident.

Is Oracle Database 11g free? ›

Free Oracle Database for Everyone

You get an Oracle Database to use in any environment, plus the ability to embed and redistribute – all completely free!

Is Oracle 11g secure? ›

Security. Oracle Database 11g delivers a rich new security functionality—from case-sensitive passwords, to Transparent Tablespace Encryption, to Access Control Lists for UTL_TCP/HTTP/SMTP.

What type of database is Oracle 11g? ›

Relational Database Management System (RDBMS)

What is difference between Oracle 11g and 19C? ›

This release has simplified the syntax of JSON functions and allows users to perform partial JSON updates. It also offers SODA APIs for Node. js, C, Python, and Java. 19C provides a machine learning algorithm function that allows constant adjustment of the index.

When did Oracle 11g release date? ›

Oracle Corporation started beta testing Oracle database 11g in September 2006 and announced the new release on 11 July 2007 in New York City (Equitable Auditorium, 787 Seventh Avenue, NY-10019). The first release of Oracle 11g (Oracle 11g Release 1) was released for Linux on 9 August 2007.

What are 4 types of backups? ›

The most common backup types are a full backup, incremental backup and differential backup. Other backup types include synthetic full backups and mirroring. In the debate over cloud vs. local backup, there are some types of backup that are better in certain locations.

Which is the smallest unit of storage in an Oracle database? ›

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of I/O used by a database.

What is difference between restore and recovery in Oracle? ›

Restoring involves copying backup files from a secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. Recovery is the process of applying redo logs to the database to roll it forward.

How do I know if my database is PDB or CDB? ›

You can query the CDB column in the V$DATABASE view to determine whether a database is a CDB or a non-CDB. The CDB column returns YES if the current database is a CDB or NO if the current database is a non-CDB. The V$CONTAINERS view provides information about all containers in a CDB, including the root and all PDBs.

What is the difference between CDB and PDB in Oracle? ›

A CDB includes zero, one, or many customer-created pluggable databases (PDBs). A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

How many PDBs are in CDB? ›

In addition, Oracle Database 19c now supports up to 3 pluggable databases (PDBs) per container database (CDB) without requiring additional multitenant license.

What are the types of tablespaces in Oracle? ›

There are three types of tablespaces:
  • Permanent. You use permanent tablespaces to store your user and application data. ...
  • Undo. A database running in automatic undo management mode transparently creates and manages undo data in the undo tablespace. ...
  • Temporary.

What is the default tablespace in Oracle? ›

The system default is to create a smallfile tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespace types are always created using the system default type. Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.

Why do we need tablespace in Oracle? ›

Each tablespace in an Oracle database is comprised of one or more operating system files called datafiles. A tablespace's datafiles physically store the associated database data on disk. A database's data is collectively stored in the datafiles that constitute each tablespace of the database.

What are the features of Oracle? ›

3 Features of Oracle Database Gateways
  • SQL and PL/SQL Support.
  • Heterogeneous Replication.
  • Passthrough SQL.
  • Result Set Support.
  • Data Dictionary Translations.
  • Date-Time Data Types.
  • Two-Phase Commit Protocol.
  • Piecewise LONG Data Type.

What are the features of Oracle 10g? ›

1 Oracle Database 10g New Features
  • Application Development.
  • Availability.
  • Business Intelligence.
  • Clustering.
  • Content Management.
  • Database Overall.
  • Database Security.
  • Grid Computing.

What is difference between 11g and 12c in Oracle? ›

Oracle 12c is just upgraded version of the Oracle 11g with some new features like cloud support and pluggable database, kind of like master slave architecture. With the Oracle 12 c, you can plug your database to cloud anytime. It has multiple new features like JSON support, multitenant architecture and etc.

What are the new features of Oracle 12c? ›

  • Application Continuity.
  • Logical Replication.
  • Global Data Services.
  • Improved Resiliency.
  • Online Operations.
  • Oracle Data Guard Enhancements.
  • Data Guard Rolling Upgrade Enhancements.
  • Oracle Database Advanced Queuing Enhancements.

What is the difference between Oracle 11g and 19c? ›

This release has simplified the syntax of JSON functions and allows users to perform partial JSON updates. It also offers SODA APIs for Node. js, C, Python, and Java. 19C provides a machine learning algorithm function that allows constant adjustment of the index.

What is the difference between Oracle 11g and 10g? ›

Difference between Oracle 10g and Oracle 11g

Compared with Oracle 10g, the later version of Oracle 11g offers more simplified, automated, and advanced memory management tools and features. The latter is equipped with better abilities to diagnose faults via inbuilt infrastructure.

What is G and C in Oracle? ›

Oracle 10g was introduced with emphasis on the “g” for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit. Oracle 12c. In the Oracle Database 12c the "c" stands for "Cloud”

What's the latest version of Oracle Database? ›

Oracle Database 19c is the current long term release, and it provides the highest level of release stability and longest time-frame for support and bug fixes. Oracle Database 21c, also available for production use today as an innovation release, provides an early insight into the many enhancements and new capabilities.

What is difference between 11g 12c and 19c in Oracle? ›

Oracle has reintroduced Oracle Active Data Guard in Oracle Database 11g to help customers make better use of this standby database by running reports and backups. In Oracle Database 19c, Oracle adds an important twist to this functionality that allows you to perform transactions for the standby database.

What is difference between Oracle 11g and 21c? ›

In Essbase 11g On-Premise, only one filter can be granted per user per cube. In Essbase 21c, new filter assignments are combined with existing filter assignments. There is no need for essbase.

What is difference between 11g 12c 19c? ›

The main difference between Oracle 11g and 12c is that the Oracle 11g has no pluggable databases while Oracle 12c has pluggable databases. Moreover, Oracle 12c allows running multiple databases on the same hardware while maintaining the security and isolation among the databases.

What is the greatest feature in Oracle Database 12c? ›

Row Limiting with the TOP N Clause

One of the most useful features of Oracle 12c is the ability to limit rows easily. This is great for pagination and other similar requirements. In older versions of Oracle, you needed to have one or two nested subqueries with the ROWNUM function.

What are the new features in Oracle 19c? ›

  • Automatic Indexing.
  • SQL Diagnostics and Repair Enhancements.
  • Bitmap Based Count Distinct SQL Function.
  • Big Data and Performance Enhancements for In-Memory External Tables.
  • Automatic SQL Plan Management.
  • Real-Time Statistics.
  • High-Frequency Automatic Optimizer Statistics Collection.
  • Hybrid Partitioned Tables.

What are the some features of Oracle cloud 12c? ›

  • Target Monitoring and Discovery Enhancements.
  • Configuration Discovery and Collection Enhancements.
  • Configuration Management Enhancements.
  • Compliance Enhancements.
  • Database Provisioning Enhancements.
  • Patch Management Enhancements.
  • Database Upgrade Enhancements.
  • Database Change Management Enhancements.

Videos

1. Oracle Database 10g: New Features for Administrators
(critiquemusicale)
2. 19 Critical Features Lost If Leaving Oracle Database
(Oracle Developers)
3. Oracle Locking | Oracle DBA Certification Training | Become an Oracle Database Administrator| Uplatz
(Uplatz)
4. 05 Data Guard Logical standby databases
(Cloud Designer)
5. Oracle Basics: ARCHIVELOG mode
(Chris Ostrowski)
6. Heat Map in Oracle Database 12c
(Koenig Solutions)
Top Articles
Latest Posts
Article information

Author: Lidia Grady

Last Updated: 02/27/2023

Views: 6486

Rating: 4.4 / 5 (45 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Lidia Grady

Birthday: 1992-01-22

Address: Suite 493 356 Dale Fall, New Wanda, RI 52485

Phone: +29914464387516

Job: Customer Engineer

Hobby: Cryptography, Writing, Dowsing, Stand-up comedy, Calligraphy, Web surfing, Ghost hunting

Introduction: My name is Lidia Grady, I am a thankful, fine, glamorous, lucky, lively, pleasant, shiny person who loves writing and wants to share my knowledge and understanding with you.