Monday, February 23, 2015

Oracle In-Memory Database Cache Overview

Oracle In-Memory Database Cache (IMDB Cache) is an Oracle Database product option ideal for caching a performance-critical subset of an Oracle database in the application tier for improved response time. Applications perform read/write operations on the cache tables using SQL and PL/SQL with automatic persistence, transactional consistency, and data synchronization with the Oracle database. (Product Data Sheet)
For many enterprise applications, the majority of data in the corporate databases is historical and infrequently accessed. However, buried within this data are pockets of information that must be instantly accessible. For example, current active customers/users, open orders, recent transactions, product catalogs, etc.; caching these data in memory can yield significant improvement for application response time.
Oracle In-Memory Database Cache is built using Oracle TimesTen In-Memory Database (TimesTen) and is deployed in the application tier for multi-user and multi-threaded applications. Applications connect to the cache database and access the cached tables using standard SQL via JDBC, ODBC, ADO.NET, Oracle Call Interface (OCI), Pro*C/C++, and Oracle PL/SQL programming interfaces. Cached tables operate like regular relational tables inside the TimesTen database and are persistent and recoverable.
Applications using IMDB Cache may choose to configure a combination of caching options:
  • Read-only caches - transactions are performed in the Oracle Database and the changes are refreshed to the TimesTen cache database.
  • Read-write (or write-through) caches – transactions are performed in the TimesTen cache database and then propagated to the Oracle Database.
  • On-demand and preloaded cached - data may be loaded on-demand or preloaded, and may be shared across the cache grid members, or reside only in a specific cache node.
Data synchronization with the Oracle Database is performed automatically.
  • Asynchronous write-through cache leverages the speed of TimesTen by first committing the transactions locally in the cache database, and asynchronously sending the updates to the Oracle Database. Asynchronous write-through cache groups provide faster application response time and higher transaction throughput.
  • Synchronous write-through cache will ensure that if the Oracle Database cannot accept the update(s), the transaction is rolled back from the cache database; with synchronous write-through, the application must wait for the commits to complete in both the Oracle Database and the TimesTen database.
  • For read-only caches, incremental updates in the Oracle Database are asynchronously refreshed to the in-memory cache tables in the application-tier at user-specified intervals.
IMDB Cache is designed to continue running even after the Oracle Database server or network connection has been lost. Committed transactions to the cache database are tracked and persisted; and once the connection to the Oracle Database is restored, the transactions are propagated to the Oracle Database. Similarly, committed transactions on the source tables in the Oracle Database are tracked and refreshed to the TimesTen database once connection between the databases is re-established.
IMDB Cache provides horizontal scalability in performance and capacity through the in-memory cache grid, which consists of a collection of IMDB Caches for an application’s cached data. Cached data is distributed among the grid members and is available to the application with location transparency and transactional consistency. Online addition and removal of cache grid members can be performed without service interruption to the application.
Depending on data access patterns and performance requirements, an application may choose to allocate specific data partitions to some grid members for locality optimizations, or make all data available to all grid members for location transparency. The cache grid software manages cache coherency and transactional consistency across the grid members.
Similar to the stand-alone TimesTen databases, IMDB Cache offers built-in mechanisms for transactional replication to provide high availability for the cache databases. Most enterprise applications cannot afford application down time, hence majority of the deployments add IMDB Cache replication for high availability and load balancing.


Saturday, April 24, 2010

Installing VMware, Linux and Oracle 10g

After spending long enough time on windows for Oracle, I have finally decided to use Oracle Database 10g on Linux for the first time. This requirement actually led to the many problems like dual booting my newly bought Windows 7 i3 machine with Linux O/S. I obvisouly did not want to mess up with my comp as a new bie to linux. So, I started looking for other alternatives. While I was discussing it with one of my friends, he gave me the suggestion for using Vmware server and Whooaaaa....I started working on it because Vmware server allows me to create virtual machines and on that virtual machine i can install Linux without messing up my machine.

You can install vmware server from intenet and can create a virtual machine almost instantly and is fairly easy as well. The only problem I encountered was I had to 'Disable driver signature authentication' to install and login successfully into vmware server. Press F8 while your machine is booting. You will see a screen with many option. Select 'Disable driver singature authentication' and then start installing vmware server. Yes I remember one more thing, I had to disable my machine's firewall to install it.You can easliy create a new virtual machine after installing it.

Next thing was to install linux on the virtual machine. You need to download .iso linux set up file to install Linux O/s on a virtual machine.Click on VM->Removeable device->Edit to choose either .iso from your hardrive or CD. I installed Oracle enterprise linux from Linux Installtion went smooth.

Stay tuned for steps for installing vmare server tools on Linux.

Thursday, June 12, 2008


Incremental export is an enhancement to export/import in v6 of the Rdbms. Thisnew feature of export allows, under certain conditions, the dba to shorten theexport time, but still have the reliability of export/import for recoverypurposes. Incremental exports will only export tables that have changedsince the last export taken with the inctype parameter. If one row of atable is updated, then the entire table is exported NOT just the changed data.The benefits occur when you have historical tables, or tables that areprimarily query only. This is where the savings in time occurs. Therefore,if all (or most) of the tables in your database are updated daily, littlebenefit is gained from incremental exports. Another important factor toconsider is the fact that incremental imports, at recovery time, will drop thetable before importing it. For a database that have very large tables thatchange everyday, recovery can be time consuming.

Three types of incremental exports exist, the differences between the threeis the amount of data they export.

1. Complete export (equivalent to full database export) exports all objects and clears the incremental export tables. Although complete exports will export the same information as full exports, complete will also mark the tables, which is essential for the next cumulative or incremental export.

2. Cumulative export will export all changed objects since the last'complete' or 'cumulative' export.

3. Incremental export will export all changed objects since the last complete', 'cumulative' or 'incremental' exports.

Each export will insert information into the incremental export tables, however,Complete exports will clear the tables before inserting new information.
For example, here is the export scheme for a mock company:

1. Complete export on the first day of the month.
2. Cumulative exports every Sunday.
3. Incremental exports every day.

Below is a picture of this backup scheme:
M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30X I I I I I C I I I I I I C I I I I I I C I I I I I I C I I

The cumulative export on the 7th, will include all the objects changed since thecomplete on the 1st. This cumulative contains the same information as incrementexports on the 2nd through the 6th and more, therefore cumulative exports arenot manditory, their benefit comes during recovery. Instead of six files toapply, there is only one.

In order to recover from incremental exports, you must initialize the database,and begin importing. Since many of the export files contain the same tables,incremental imports will always drop the object before importing. This prevents duplicate data from being imported. This is how an incrementalimport creates tables:


Before beginning to import the data, you must have the database structure backto the last export. This is done by importing the last incremental exportfirst with "inctype=system" specified. The only information imported aresystem objects including tablespaces, users, and rollback segments. Now thatthe database structure has been recovered and the users all exist, the datacan be imported. We begin with the complete export, with "inctype=restore"specified. Continue with the cumulative exports, and finally the incrementalexports.

NOTE: the last incremental export is imported twice, once at the beginningof the recovery with 'inctype=system' for system objects, and again with'inctype=restore' for the current copy of the data.

Recovery will consist of the following imports:

Below is a description of the incremental export tables owned by SYS and whatinformation they store:

SQL> desc sys.incexp Name Null? Type -------------------- -------- ---- OWNER# NOT NULL NUMBER - id # of owner = user_id in dba_users. NAME NOT NULL CHAR(30) - name of object*TYPE NOT NULL NUMBER(1) - type of object # CTIME DATE - date exported ITIME NOT NULL DATE - EXPID NOT NULL NUMBER(3) - id of file
*Key to column TYPE: 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE

SQL> desc sys.incvid Name Null? Type ----------------------------- ---- EXPID NOT NULL NUMBER(3) - HOLDS NEXT ID NUMBER.

SQL> desc sys.incfil Name Null? Type ----------------------------- ---- EXPID NOT NULL NUMBER(3) = (sys.incexp.EXPID)*EXPTYPE NOT NULL CHAR(1) - represents type of exp EXPFILE NOT NULL CHAR(100) - filename EXPDATE NOT NULL DATE - date exported EXPUSER NOT NULL CHAR(30) - user exported.
*Key to column EXPTYPE: X=Complete C=Cummulative I=Incremental


Wednesday, June 11, 2008

Setting Up Enterprise Manager Preferences

Learn Oracle - Setting Up Enterprise Manager Preferences

Enterprise manager enables Oracle DBA to set up preferences that help Oracle DBA manage the database. These include the following:

Notification---These are settings that enable Oracle to email Oracle DBA alert notifications. Alerts are notifications of when a database is in an undesirable state and needs your attention. By default, the Enterprise Manager home page lists all alerts. However, set up is required for email notification.

Blackout Administration---When Oracle DBA have planned downtime for database maintenance, you can indicate to Oracle that you do not want false alerts to be sent to you. To do so, you define a black out period. See Defining Blackout Time Periods in this section.

Preferred Credentials---Enterprise Manager can automatically execute many routine administrative tasks, such as backups on your behalf. This is done using sophisticated job scheduling system built into the Enterprise. To keep your environment secure, setting up tasks for automatic execution in Enterprise Manger requires Oracle DBA to provide login information for the machine and database. In order to avoid having to enter this information every time Oracle DBA create a job or task, Enterprise Manager enables Oracle DBA to save this information as preferred credentials. Preferred credentials are stored in the database in encrypted mode to protect it from unauthorized use. See Setting Preferred Credentials in this section.


Wednesday, June 4, 2008

Granting Database Control Administrative Privileges

Learn Oracle - Granting Database Control Administrative Privileges

When Oracle DBA log in to the Oracle Enterprise Manager Database Control using the SYS or SYSTEM user account, Oracle DBA are logging in as the Oracle Enterprise Manager super user. These are the only accounts that are automatically granted the roles and privileges required to access all the management functionality provided the Database Control.

To grant management access to other database users, use the following procedure:

1. Start your Web browser and log in to the Database Control as the SYS or SYSTEM database user.

2. Click Setup at the top of the Database Home page.

3. Click Administrators in the left navigation bar. If Oracle DBA need to create a new administrator, Oracle DBA must first create the user and then assign administrative privileges as described in this section.

4. Click Create to create a new Enterprise Manager user by assigning the management privileges to an existing database user.

5. Click the flashlight icon next to the Name field and select an existing database user from the pop-up window.

6. Enter the password for the selected user and click Finish. Enterprise Manager assigns the management privileges to the selected user. The selected database user is now included in the list of management users on the Setup Administrators page. All the users shown on this page can log in to the Database Control and perform database management tasks.

Wednesday, May 28, 2008

Accessing the oracle Enterprise Manager Database Control

Accessing the Oracle Enterprise Manager Database Control

At the end of a successful database creation, Oracle Enterprise Manager is automatically launched and the database is started. At other times, provided the dbconsole process is running on the server, Oracle DBA can navigate to your Database Control from your client browser as follows:

1. Point your web browser to the following URL: http://hostname:portnumber/em For example, if Oracle DBA installed the database on a host computer named comp42, and the installer indicated that your Enterprise Manager Console HTTP port number is 5500 (also recorded in the $ORACLE_HOME/install/portlist.ini file), enter the following URL: http://comp42:5500/em If the database is up, Enterprise Manager displays the Database Control Login page.

If the database is down and needs to be started, Enterprise Manager displays the Startup/Shutdown and Perform Recovery page. If this is the case, click the Startup/Shutdown button. Oracle DBA are then prompted for the host and target database login usernames and passwords, which Oracle DBA must enter. For the database user and password use SYS and the password Oracle DBA specified during installation.

Click OK to start the database. In the Confirmation screen, click YES to start the database in open mode.

2. Log in to the database using a username that is authorized to access the Database Control. This initially could be SYS or SYSTEM. Use the password Oracle DBA specified for the account during the database installation.

The property pages across the top of the page enable Oracle DBA to access performance, administration, and maintenance pages for managing your database. The functionality provided by these pages is discussed in other chapters of this book. The various sections of the Database Home page, and related links, provide a wealth of information about the database’s environment and health. For example, the Alerts, Related Alerts, and ADDM Analysis sections warn Oracle DBA of errors and performance problems that are impacting the operation of your database. Oracle DBA can click the provided links to see more detail about the problem area, and even to obtain recommendations and SQL for resolving the problem

Sunday, May 25, 2008

Starting the Oracle Enterprise Manager Console

Starting the Oracle Enterprise Manager Console

To access the Oracle Enterprise Manager Console from a client browser, the dbconsole process needs to be running on the server.

This process is automatically started after installation. However, if the process is not running because of a system restart or otherwise, Oracle DBA can manually start it at the command line.

Control Panel->Administrative Tools->Services. The Services page appears.

2. Oracle services begin with Oracle. The dbconsole service is listed as OracleDBConsole. The status of this process is listed in the Status column, either Started or Stopped. Double click the service. The property page appears.

3. In the properties page, ensure that the Startup Type is either Manual or Automatic and not Disabled. Click Start, if the process is not already started. Click OK. Oracle DBA can also use the Services page to Stop the process.