Tuesday, June 28, 2011

Cloning of database through OEM

It  is a very simple and fastest way to clone a database through OEM(Oracle Enterprise Manager)

First of all set  DB_CREATE_FILE_DEST as follows on target database.

SQL>Alter system  set DB_CREATE_FILE_DEST=   'D:\app\Bishwanath\product\11.2.0\dbhome_1\oradata';

After this follow the wizard one by one as provided in the link below:


Saturday, June 18, 2011

Cloning Oracle database software or Oracle home to different server

In a situation where you need to create testing Oracle database environment, the best option is to have exactly same ORACLE_HOME that is in production because many running productions are heavily patched.

Here in this post we will see how to clone the oracle home from server 1 to server 2:

Source database server (sour)
ORACLE_HOME location is: D:\app\oracle\product\11.2.0\dbhome_1
·         Zip this complete folder i.e. dbhome_1

Target database server (tart)
Create the directory: D:\app\vishwanaths\product\11.2.0\clone
·         Copy the zipped file and uncompress in the above location.

Since we have moved files to different directory and different server, many Oracle paths are wrong. Beside that many libraries point to invalid place what will prevent Oracle form normal functioning. So this will be fixed with clone.pl script, which is placed in $ORACLE_HOME/clone/bin directory:


Goto D:\app\oracle\product\11.2.0\clone\dbhome_1\clone\bin\clone.pl

C :\>:perl D:\app\oracle\product\11.2.0\clone\dbhome_1\clone\bin\clone.pl ORACLE_BASE=D:\app\oracle ORACLE_HOME=D:\app\oracle\product\11.2.0\clone\dbhome_1 ORACLE_HOME_NAME=11GR2_HOME
....................................................................................................Dll to load is C:\DOCUME~1\VISHWA~1.RAM\LOCALS~1\Temp\OraInstall2011-06-17_04-39-37PM\Win32RunProcesses.dll
Loading psapi.dll from C:\DOCUME~1\VISHWA~1.RAM\LOCALS~1\Temp\OraInstall2011-06-17_04-39-37PM\psapi.dll
 100% Done.

Installation in progress (Friday, June 17, 2011 4:57:27 PM IST)
.............................................................................                                                   77% Done.
Install successful

Setup in progress (Friday, June 17, 2011 4:58:31 PM IST)

Unable to open symbol file D:\app\oracle\product\11.2.0\clone\dbhome_1\dbs\opctrn11.hSetup successful

End of install phases.(Friday, June 17, 2011 5:19:16 PM IST)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
The "D:\app\oracle\product\11.2.0\clone\dbhome_1\cfgtoollogs\configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "D:\app\oracle\product\11.2.0\clone\dbhome_1\cfgtoollogs\configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

The cloning of 11GR2_HOME was successful.
Please check 'C:\Program Files\Oracle\Inventory\logs\cloneActions2011-06-17_04-39-37PM.log' for more details.

How to check that cloning was successful?

Goto C:\Program Files\Oracle\Inventory\ContentsXML\

Open inventory.xml file…

<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2009, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<HOME NAME="OraDb11g_home1" LOC="D:\app\oracle\product\11.2.0\dbhome_1" TYPE="O" IDX="1"/>
<HOME NAME="11GR2_HOME" LOC="D:\app\oracle\product\11.2.0\clone\dbhome_1" TYPE="O" IDX="2"/>

Above  marked text shows that new home with name “11GR2_HOME” has been created.


Friday, June 17, 2011

Locks and Latches...mystery unfolds

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. The resources can be either user objects, such as tables and rows, or system objects not visible to users, such as shared data structures in memory and data dictionary rows. Oracle Database automatically obtains and manages necessary locks when executing SQL statements, so you need not be concerned with such details. However, the database also lets you lock data manually.
A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work. Oracle Database automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks

Need for locks and latches
To access shared resources concurrently by other processes requiring access to the same resources.
To protect the contents of database objects while they are being modified or inspected by other processes
To serialize access to SGA data structures
Differences between locks and latches

Provide only exclusive access to protected data structures
Request are not queued, if a request fails, process may try later

Allow serialized access to some resources
Requests for locks are queued and serviced in order


Thursday, June 16, 2011

Difference between dedicated and shared server configuration:

In order to communicate with oracle database, oracle users need a program such as SQL *Plus which can issue SQL statements and few processes which can execute these SQL statements. These processes are divided into User Process, Server Process and Background Processes. User process runs user application like SQL *Plus. Server process manages oracle user process’s requests. Server process executes SQL statements and returns result to user process. Background processes are the core of oracle database which handle over all database operations.

Dedicated server connection:

A dedicated process has one to one relationship with user process. The user connected with dedicated server connection has the dedicated resource that is whether or not the user is doing work the connection remains allocated to that user. Thus if your user is not working and if there is less network resource then this user is blocking another user to connect or to execute the user request by making this user works queued. This connection is preferable when you have lot of resource or to some administrative user like "SYS" , "SYS" for administrative works and for batch jobs,

Shared server connection: 

Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue. Network Listener process listens the user process request. If user process request requires a dedicated server process, listener process starts a dedicated server process. If the request can be assigned to a shared server process, then the request is forwarded to dispatcher process. Shared server configuration requires at least on dispatcher process. Dispatcher process places the request on request queue. Request Queue is created in SGA and shared by all dispatcher processes. On of free Shared server process picks up the request from request queue. After processing the request, shared server process places the result in response queue. Each dispatcher process has its own response queue in SGA. Dispatcher knows about the user process which placed the request, so the response is returned back to user process.

When there is shared server the user connection is shared, that is user is connecting by dispatchers and when a user is idle his resource can be used by other users, thus lessen the load on system, this is more likely in a environment where user is mostly idle, like in a order entry system.

So, what should I choose?

If your environment is OLTP then shared is preferable because the tasks need short time. Whether is Warehouse, OLAP, Data Mining the users are running time consuming and heavy load query where it is best to use dedicated server.

How to configure a shared server configuration?

Below are the two links that helps in configuring the same.

Summarized way to know how oracle works…

  • An instance has started on the computer running Oracle (often called the host or database server).

  • A computer running an application (a local computer or client workstation) runs the application in a user process. The client application attempts to establish a connection to the server using the proper Oracle Net Services driver.

  • The server is running the proper Oracle Net Services driver. The server detects the connection request from the application and creates a dedicated server process on behalf of the user process.

  • The user runs a SQL statement and commits the transaction. For example, the user changes a name in a row of a table.

  • The server process receives the statement and checks the shared pool for any shared SQL area that contains a similar SQL statement. If a shared SQL area is found, then the server process checks the user's access privileges to the requested data, and the previously existing shared SQL area is used to process the statement. If not, then a new shared SQL area is allocated for the statement, so it can be parsed and processed.

  • The server process retrieves any necessary data values from the actual datafile (table) or those stored in the SGA.

  • The server process modifies data in the system global area. The DBWn process writes modified blocks permanently to disk when doing so is efficient. Because the transaction is committed, the LGWR process immediately records the transaction in the redo log file.

  • If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.

  • Throughout this entire procedure, the other background processes run, watching for conditions that require intervention. In addition, the database server manages other users' transactions and prevents contention between transactions that request the same data.