Far Sync (Oracle 12c New Feature)

Oracle Far Sync is an Oracle 12c new feature for Oracle Data Guard. This feature is meant to resolve the performance problems induced by network latency when you maintain a standby database geographically distant of the primary database. In this type of situation you sometimes have to make a compromise between performance and data loss. The Far Sync feature offer you both.

How does Far Sync works ?

The principe is fairly simple, the Far Sync instance receive data synchronously from the primary database and then forward it asynchronously to up de 29 remote destinations.

Data Guard Far Sync
Continue reading

Average Active Sessions (AAS)

Description

The Average Active Sessions (AAS) metric is a very good indicator of the database activity.
This metric represents the number of sessions, either working or waiting for a resource at a specific point in time.
Idle sessions are not included in the calculation of this metric.
To calculate AAS, we need another metric called “DB Time” which represents the total time spent in the database by sessions, either working or waiting (The real work done in the database).
This DB time is divided by the clock elapsed time to obtain the Average Active Sessions.
The following script will calculate the Average Active Sessions from v$active_session_history view.

Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

Average Active Sessions from v$active_session_history

You can restrict period of time analyzed by filtering with the sample_time column. Here is AAS from the last hour :

This metric can be correlated with the Graph from v$active_session_history

Top 10 sessions from v$active_session_history

Description

This query returns the top 10 sessions from v$active_session_history. The result is ordered by total resources consumed by the session including I/O, WAITS and CPU
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

top 10 sessions from v$active_session_history

You can restrict period of time analyzed by filtering with the sample_time column.

Oracle instance activity repartition

Description

This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

Instance activity repartition

This query returns percentage of I/O, CPU and waits consumed by the instance for the total time covered by v$active_session_history. You can specify a time interval by adding a clause in the subquery specifying a range for sample_time.

Top 10 queries from v$active_session_history

Description

This query return the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history.
Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.

top 10 queries from v$active_session_history

This query return top queries by resources consumed, you can easily return the top I/O, WAITS or CPU queries by changing the order by clause.

You can see my other post about how to build a graph from the v$active_session_history view here

Recover standby database over the network (Oracle 12c new feature)

recover-standby-network-oracle-12c

In this article, I’ll explain how to manualy recover a standby database using the oracle RMAN new feature “RECOVER FROM SERVICE”.

Configuration for the test

HOSTS : 2 Virtual Machines with 2 GB of Memory
OS : Oracle Linux 6.4
ORACLE VERSION : Oracle 12.1.0.1 Standard Edition

A database named DB12C in archivelog mode has been created on the first host using DBCA, a listener is running on each server on the default port 1521 and the tnsnames.ora file has been defined as follow :

Continue reading

which archivelog sequence contains your SCN number ?

Description

This query returns the archivelog sequence containing a scn number. This could be usefull in case you want to manually recover a standby database until a scn or just want to know which arvhivelog you should copy to recover the standby.

Archivelog containing your SCN

Recover a table with RMAN (Oracle 12c new feature)

Before oracle 12c, recover a table from loss or corruption was a difficult affair.
Most of the time you notice that the table is lost or corrupted too late to use the flashback technology and then the only way to go is to duplicate your database to a time the table was still valid, export this table and reimport it in your production database. That was a long drawn out affair.
Oracle 12c still procede the same way, it creates an auxiliary database with just the needed tablespace(s) containing the table to restore, export the table using datapump and reimport it to the database. All these steps are now automated. This feature is only available in Enterprise Edition as for the Tablespace Point in time recovery.

Recover a table with RMAN

As for any RMAN recovery you can restore your table by specifying :

  • UNTIL SCN
  • UNTIL SEQUENCE
  • UNTIL TIME

Continue reading

Graph from v$active_session_history

Description

This query generate the data needed to build the load graph from v$active session_history, the output represent what sessions did in your database for the last hour, be careful, the v$active_session_history is part of the diagnostic pack, if you don’t have a license for this pack you should not run this query.

Query to get graph from v$active_session_history

The previous query will only work in 11g and more as it uses the PIVOT keywork, the following will work with 10g.

Here is a sample graph I generated using SQL Server Reporting Services:

ash

I would like to thank Yannick Jaquier, for providing these queries, you can find his orginal post here

Managing Oracle Central Inventory

Maybe have you ever been in a situation in which the Oracle central inventory is missing a Home or a Home is in it and doesn’t exists anymore or even the central Inventory doesn’t exists at all. Here is the way to consolidate you inventory or create it if it doesn’t exists.

Create the central inventory

You can create the central inventory by attaching a home with the following command:

Be sure you oraInst.loc file is pointing to a valid directory. You can use the same procedure to add an ORACLE_HOME to a existing Central Inventory.

Detach an ORACLE_HOME from the central inventory

If you want to detach an ORACLE_HOME to attach it to a new inventory you can use the following command:

When you detach an ORACLE_HOME from the central inventory you can verify the status by checking at the xml file :
/ContentsXML/inventory.xml, the home you just detached should have the flag REMOVED at the end of the line like the following:

You should take a backup of the inventory every time you add or remove a home.

For more information you can look at the Oracle Documentation: Managing Oracle Homes