Oracle Backup & Recovery Series - 1 (Multiplexing Controlfile)

Production databases are prone to many types of corruption, disasters and crashes. To manage such a problematic situation's we always have our databases enabled for proper backups and recoverability. This blog is the first of the series explaining how to implement proper recoverability in our environments.

This is the first post in the series where I will be explaining how to multiplex our controlfile, If somebody does not know what is a controlfile or the importance of it, They should start learning the basics of Oracle database architecture.



1) Check for the controlfiles in our database, You should be seeing 2 controlfiles created on the same ASM diskgroup +DATA, We should be creating a copy of the controlfile into another diskgroup and this is called multiplexing. In case of any issues with ASM diskgroup, we will be having a surviving copy of controlfile at any point of time.

 2) Stop the database using srvctl.

3) Set the ASM environment and invoke ASCMD

 4) In ascmd command prompt, list the controlfiles that is placed under '+DATA' ASM diskgroup.

5) There is no controlfile under the '+BACKUP' diskgroup, but we will be creating a copy of controlfile under this diskgroup in next steps.

6) Create a ASM directory for having the copy of the controlfile. Using 'cp' command create a multiplexed copy of the controlfile under '+BACKUP' diskgroup.

7) Now the 'ls' command should list the newly copied controlfile copy.

8) Remove the old controlfile copy under '+DATA' diskgroup.

9) Find the SPFILE location.

 10)  Create a pfile from spfile.

11)  Open the pfile using a 'vi' editor, change the control_file parameter as shown below

Before:-

 After:-

12) Start the database using the pfile under $ORACLE_HOME/rdbms/dbs and then create an spfile.

13) We will need a final bounce to keep the database up with the newly created spfile.

HAPPY LEARNING!

Oracle Application Framework (OAF) - Performance Issue

Introduction :- E Business Suite versions are moving towards OAF leaving behind Forms. Oracle Applications Framework (OAF) is one of the in demand technology in the Oracle Application Tech Stack, Though not from the development community, I realized this couple of days back when 2 new EBS developers joined the company. Both of them were interested in knowing if they would get chance to work on OAF development activities. 

There was a regular complaint from OAF developers developing a new OAF screen, After every day development the DBA's go ahead with regular OACORE bounce, but next day, the new OAF development changes is not seen or the screen download never used to complete.

After some R&D found the fix as explained below.

HTML pages generated by OA Framework make extensive use of cascading style sheets to comply with well defined UI standards. These style sheets, mostly generated dynamically at runtime, are based on a number of factors such as the Look-and-Feel and the version of OA Framework and Oracle UIX in use. 

To avoid performance issues, these dynamically generated style sheets are stored on the file system in a cache, under $OA_HTML/cabo/styles/cache. It is recommended that you delete the contents of this directory every time you apply a new OA Framework patch to your system to ensure that these files get regenerated using the latest.

Initially, I was also in doubt that how does removing cache improve the performance, It was all against the concept of caching. But the real culprit is the stylesheets, which are cached and not allowing for the OAF components to download properly and causing performance issues.


Hope this helps some OAF developer and Apps DBA scratching his head even after OACORE bounce.

HAPPY LEARNING!

Oracle Timesten In-Memory Database 11.2.2 Installation

Oracle Timesten In-Memory Database was originally designed by Hewlett Packard and got split as a separate start up which enabled Oracle corp to acquire the technology. All data within a TimesTen database is located in physical memory (RAM), which means no disk I/O is required for any data operation.

I used to work for JP.Morgan Chase sometime back, where it was my first experience with this wonderful technology. In fact, this kind of In-Memory database is only suitable for small applications which needs a small and fast datastore and as per my observations so far, this database is mostly used in financial institutions.

I wanted to check the SQL Developer options for timesten database, and for that, I tried to install  the timesten database on my laptop, and how I did is shown below with the screenshots.

Note:- This installation is very simple and you might think this looks like a easy job (Installation of TimesTen DB) and what is great about a blogpost on this simple job, The fact is, the intent of this post is to make people aware of such a beautiful product's existence, I bet hardly people know about existence of TimesTen database from Oracle.

1) Download timesten database software from OTN.

2) You should be accepting the license agreement as usual before you download the timesten112280.win32.zip file  

 

3) Once the zip file is downloaded, you can unzip the file

 

4) After unzipping, you can see the setup.exe file in the extract directory.

 

5) Click on the "Setup.exe" file to start the installation, Rest is next - next driven installation in windows.

 

6) The below screen lists all the components we wish to install, that includes Timesten sample datastores, client

7) After installation you can see the timesten folder created in the startup menu as shown below:-

 
 8) Click on the "TimesTen Quick Start" link to open the detailed documentation and enjoy working with timesten databases.

Few tips to start with:-

How to connect the sampledb_1122 datastore we just installed?

Click on the ttISql under start menu Timesten Folder  again.

Below contents of the readme file is self -explanatory for start playing with TimesTen Database. 


The bin subdirectory contains TimesTen utilities that can be used to maintain TimesTen data stores.  They are described briefly here.

The TimesTen utilities included in this installation are described below.

ttAdmin
-------
This utility is used to administer RAM memory residency policies and to stop and start the replication agent.

ttBackup
--------
This utility is used to backup a data store. The data store is backed up into the directory specified on the command line. The ttRestore utility is used to restore the data store.

ttBulkCp
--------
This utility copies data between TimesTen tables and ASCII files. ttBulkCp has two modes: copy-in mode (ttBulkCp -i), which copies data from ASCII files into TimesTen tables, and copy-out mode (ttBulkCp-o), which copies data from TimesTen tables into ASCII files.

ttCapture
---------
This utility captures useful information about the current operating system, TimesTen installation, and TimesTen data stores in order to facilitate error diagnosis when communicating with TimesTen support.

ttCheck
-------
This utility checks the internal consistency of a data store, and can be useful for diagnosing problems. It is intended for use by TimesTen Support.

ttCWAdmin
---------
Manages TimesTen active standby pairs that take advantage of the high availability framework of Oracle Clusterware. This utility starts administrative processes, generates scripts and performs other functions to administer active standby pairs and the corresponding Clusterware resources.

ttDaemonAdmin
-------------
This utility is used to start and stop the TimesTen Server and Web Server.

ttDaemonLog
-----------
This utility shows the output of all logged TimesTen system events,optionally staying up to poll for further events.  Note that all events are displayed, including those for now-defunct data stores.

ttDestroy
---------
This utility destroys a data store, including the checkpoint files, log files, and daemon catalog entries.

ttIsql
------
This program runs SQL and some administrative commands, either interactively or via input from a file. There is a sample input file,ttIsql.inp, in the demo directory.

ttMigrate
---------
This utility saves and restores tables and index definitions between TimesTen data stores and binary data files. ttMigrate can be used to migrate data between different versions of TimesTen or between 32- and 64-bit platforms.

ttModInstall
------------
This script modifies the port number for an instance of TimesTen. 

ttRepAdmin
----------
This utility is used to configure replication.

ttRestore
---------
This utility is used to restore a data store which has been backed up using the ttBackup utility.

ttSchema
----------
This utility prints out the schema, or selected objects, of a data store. 

ttSize
------
This utility estimates the amount of space that indexes and tables occupy.  It can be useful when determining values for the initial and maximum size of a data store.

ttStats
-------
This utility runs as a wrapper to operate the TT_STATS PL/SQL package. It  calls the corresponding procedures in the package to capture a snapshot of system statistics, or generate a report based on the comparison between two snapshots.  It can also monitor the real-time system statistics in a mode that is independent of TT_STATS PL/SQL package.

ttStatsCS
---------
This utility runs as a wrapper to operate the TT_STATS PL/SQL package. It calls the corresponding procedures in the package to capture a snapshot of system statistics, or generate a report based on the comparison between two snapshots. It can also monitor the real-time system statistics in a mode that is independent of TT_STATS PL/SQL package.

ttStatus
--------
This utility displays some information regarding the TimesTen data stores on the system, including how many connections are open and which processes are connected.

ttTail
------
This utility fetches trace information from the data store. (Tracing can be enabled using ttTraceMon.)

ttTraceMon
----------
This utility can be used to enable and disable tracing of different TimesTen components at different levels.

ttUser
------
This utility encrypts a user password which can be, in turn, included in the connection string or as the value for the PWDCrypt attribute.

ttVersion
---------
This utility prints out the version of TimesTen.

ttXactAdmin
-----------
This utility displays information about active transactions, including which processes they correspond to, which locks they are holding, and which portions of the log they are associated with. ttXactAdmin can also be used to do heuristic commits and aborts for XA transactions.

ttXactLog
---------
This utility displays the contents of the log files for a data store. A range of log records can be specified, or only those related to a certain block or of a certain type. ttXactLog is primarily of use only to TimesTen Support.

Long time back, I had a post on the timesten datastore refreshing procedure which can be found in the below post, In fact this should have been the first post on TimesTen ideally- 

http://oracledbascriptsfromajith.blogspot.in/2013/11/refreshing-oracle-timesten-datastore.html

 


HAPPY LEARNING!