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:
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:
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.
Click View and Manage Transactions, listed under Manage. This brings up the main LogMiner interface, as shown below:
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:
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.
If you click on that transaction identifier, you can see the details of that transaction as shown in the screen below:
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.
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:
This function will extract all the transactions that involved 100002 in the RES_ID column in RES table under the SCOTT schema.
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:
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.
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.
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:
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.
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:
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:
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.
SQL> alter table trans flashback archive near_term;Table altered.
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.
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:
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:
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:
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:
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:
SQL> alter table trans flashback archive near_term;Table altered.SQL> alter table trans enable row movement 2 /Table altered.
Now that this table is flashback enabled, let’s perform a simple DML operation:
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:
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:
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).
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.
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:
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:
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.
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:
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:
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:
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:
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.
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
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
- 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.
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.
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.
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? ›
- On Windows: Click Start, point to Programs (or All Programs), point to Oracle Database 11g Express Edition, and then select Backup Database.
- On Linux with Gnome: In the Applications menu, point to Oracle Database 11g Express Edition, and then select Backup Database.
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.
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? ›
- Shut down the database and start up the database in mount mode in SQL*Plus:
- Confirm that the database is in ARCHIVELOG mode, which is required for Flashback Database, and enable ARCHIVELOG mode if needed.
- Set the flashback retention target to the desired value (in minutes).
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!
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.
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? ›
- 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. ...
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? ›
- 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.
- Application Development.
- Business Intelligence.
- Content Management.
- Database Overall.
- Database Security.
- Grid Computing.
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.
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.
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.
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.
- 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.
- 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.