Establishing a Connection from Oracle to SQL Server

Here are steps to make a connection from Oracle to SQL Server using Oracles' heterogeneous Services ODBC agent.

Oracle has a generic connectivity methodology that allows for the Oracle database server to access non-Oracle database systems through ODBC and SQL*Net services. Step-by-Step setup to query from a SQL Server database.


1. Define a Data Source Name (DSN) for SQL Server

--> The first step is to define a system DSN within the Windows ODBC Data Sources.

a.From the start menu click on Settings -> Control Panel and double click the ODBC icon.

b.Click on the System DSN tab and then click the Add button.

c.Choose the SQL Server driver since this will be a connection to SQL Server. Click Finish to continue with the data source definition.

d.Key in any name you would like to reference this ODBC data source. I have chose SS for simplistic reasons but it should be descriptive to the database you may be connecting to within SQL Server. You may also describe the data source in any way you wish. This is my remote SQL Server that I will be connecting to. Click Next to continue.

e.I selected Sql Server authentication and also provided Login ID and Password below. Then click Next to continue.

f.Typically, this window is populated with the default SQL Server database of "master." Click the check box to change the default database this ODBC connection should connect to and use the drop down list to select. I have chosen to use the my test Remshr database. Click Next to continue.

g.I also left current window alone and clicked Finish.

h.The next window then appears for you to look at the settings you have configured for the data source. Click Test Data Source to validate your definition.

i.TEST COMPLETED SUCCESSFULLY appears, in which case you have successfully configured the data source. Click OK to close all windows as you are done with the data source definition.

j.The end product should be a valid System DSN. You may in the future click on the DSN name and click the Configure button to change the definition if you like. I did this when I wanted to switch between databases. A small warning here on re-configuration of the DSN: you will need to drop and re-create the database link to activate the DSN. Click OK to exit the DSN administrator.


2. Create a Heterogeneous Services Initialization File

Oracle has provided a sample heterogeneous services init file within the %ORACLE_HOME\hs\admin directory. You will need to copy that file to a new file name within the same directory and edit it for the ODBC DSN you have just created. Below you will find the sample heterogeneous services file Oracle provides and then an edited version, which I have given a new name that corresponds to my DSN name.

%ORACLE_HOME\hs\admin\inithsodbc.ora sample file

(ORACLE_HOME=D:\oracle\product\10.1.0\Db_1)

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = (odbc data_source_name)
HS_FDS_TRACE_LEVEL = (trace_level)
#
# Environment variables required for the non-Oracle system
#
#set (env var)=(value)

%ORACLE_HOME\hs\admin\initSS.ora altered file

# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SS
HS_FDS_TRACE_LEVEL = OFF

3. Alter your listener.ora file

Here again Oracle has given us a sample listener.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my listener.ora file. I made five distinct changes..
Created my own listener name of LISTENERSS
Changed the Port number to 1522
Changed the SID_NAME to my DSN (SS)
Changed the ORACLE_HOME location
Changed the PROGRAM to hsodbc

%ORACLE_HOME\network\admin\listener.ora altered file

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Ntws205.rmsi.com)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)

LISTENERSS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSS=
(SID_LIST=
(SID_DESC=
(SID_NAME=SS)
(ORACLE_HOME = D:\oracle\product\10.1.0\Db_1)
(PROGRAM=hsodbc)
)
)

4. Alter your tnsnames.ora file

Here again Oracle has given us a sample tnsnames.ora file to follow for heterogeneous services within the %ORACLE_HOME\hs\admin directory. Below you will find the sample file and the additions I made to my tnsnames.ora file.

1. I made four distinct changes.Created a TNS entry named SS

2. Changed the Port number to 1522

3. Changed the SID to my DSN (SS)

4. Added OK to the HS= parameter

%ORACLE_HOME\network\admin\tnsnames.ora altered file

SS =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SS))
(HS=OK)
)

5. Start the new Listener

This should be self-explanatory but I provide the output here so that you can know what to expect when you start yours.

D:\>lsnrctl start listenerss

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 16:54:55

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Log messages written to d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
STATUS of the LISTENER
------------------------
Alias listenerss
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 15-FEB-2008 16:55:09
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File d:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File d:\oracle\product\10.1.0\db_1\network\log\listenerss.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
Services Summary...
Service "SS" has 1 instance(s).
Instance "SS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

6. Validate the connection to your DSN

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

D:\>tnsping ss

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 15-FEB-2008 17:14:57

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
D:\oracle\product\10.2.0\client_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=SS)) (HS=OK))
OK (10 msec)


7. Create a Database Link within Your Oracle Database

Since we would like to connect from our Oracle database and select information from the SQL Server database, we need to create a database link just as if we were connecting to any other remote Oracle database. I have used the default sa login from SQL Server but you may wish to create your own.

SQL> create database link ss
connect to sa identified sa by using 'SS';

Database link created.

This is the fun part as it is the culmination of what we were trying to do. You may describe the tables from SQL Server just as you have done with Oracle in the past and then use a SELECT command. Note that my select statement has double quotes and exact upper and lower cases for the SQL Server query. This is required, at least I tried every combination and none worked except this way.

SQL> desc tblemp@ss
Name Null? Type
----------------------------------------- -------- ----------------------------
EmpNo NOT NULL VARCHAR2(4)
EmpInit NOT NULL VARCHAR2(4)
EmpName VARCHAR2(50)
Salutation VARCHAR2(4)
Nationality VARCHAR2(15)
Sex VARCHAR2(1)
DOB DATE
DOW DATE
Qualification VARCHAR2(15)
-------
-------
-------

SQL>select "EmpNo","EmpInit","EmpName" from tblemp@ss;

Source of this article:
http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1

How to apply a patch ?

1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.

2.Make sure you have a good backup of database.

3. Make a note of all Invalid objects in the database prior to the patch.

4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

5. You MUST Backup your oracle Home and Inventory

tar cvf - $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz

6. Unzip the patch in $ORACLE_HOME/patches

7. cd to the patch direcory and do opatch -apply to apply the patch.

8. Read the output/log file to make sure there were no errors.

How to Rollback a patch?

opatch rollback -id (Patch Number)

I messed up my Oracle home/ opatch rollback is failing , what to do now?

Remember, we took the backup of the ORACLE_HOME and main importantly the Oracle inventory, you can just restore (uncompress) the software and the inventory and you are all set. Also for fixing the corrupt inventory, please refer to this OracleMetaLink Note:298906.1

Critical Patch Update JAN 2008 Linux

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 11.1.0.6.0
Patch Number : p6646866_111060_LINUX.zip

Download it from OracleMetaLink

SYSTEM Overview

Platforms : Oracle Enterprise Linux 5
Database : 11.1.0.6.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from
OracleMetaLink.

Check OPatch Version

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608, select the Oracle RDBMS version and download the patch.

[oracle@oracle11gr1]$ cd $ORACLE_HOME
[oracle@oracle11gr1 db_1]$ cd OPatch/
[oracle@oracle11gr1 OPatch]$ sh opatch version
Invoking OPatch 11.1.0.6.2

OPatch Version: 11.1.0.6.2

OPatch succeeded.

[oracle@oracle11gr1 OPatch]$ sh opatch napply /u01/app/oracle/product/11.1.0/db_1/6646866/ -skip_subset -skip_duplicate

Invoking OPatch 11.1.0.6.2

[Output Edited]
OPatch continues with these patches: 6646866 6650132 6650135 6731395

Do you want to proceed? [y|n]
Y
User Responded with: Y
Running prerequisite checks...
[Output Edited]

Is the local system ready for patching? [y|n]
Y
User Responded with: Y
[Output Edited]

Execution of 'sh /u01/app/oracle/product/11.1.0/db_1/6646866/6646866/custom/scripts/post -apply 6646866 ':

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Post Installation Instructions

[oracle@oracle11gr1 OPatch]$
After installing the patch, perform the following actions:

Loading Modified .sql Files into the Database

There are no steps to perform, because the CPU for Release 11.1.0.6 does not use catcpu.sql or related scripts.

You can go directly to Recompiling Views in the Database

You must recompile views for all databases except the following:

Databases created with Release 11.1.0.6 or later

Databases created with any release (for example, 10.2.0.3, 10.1.0.5, or 9.2.0.8) after CPUJan2008 or a later CPU has been applied

The time required to recompile the views and related objects depends on the total number of objects and on your system configuration. In one internal Oracle test with approximately 2000 views and 4000 objects, the total execution time for view_recompile_jan2008cpu.sql and utlrp.sql was about 30 minutes.

If you want to check whether view recompilation has already been performed for the database, execute the following statement.

SELECT * FROM registry$history where ID = '6452863';

[oracle@oracle11gr1]$ sqlplus /nolog

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
Database opened.
SQL>
SQL> SELECT * FROM registry$history where ID = '6452863';
no rows selected

If the view recompilation has been performed, this statement returns one row. If the view recompilation has not been performed, this statement returns no rows.

To recompile the views in the database, follow these steps:

-->Run the pre-check script, which reports the maximum number of views and objects that may be recompiled:

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @recompile_precheck_jan2008cpu.sql

Running precheck.sql...

Number of views to be recompiled :2715
-----------------------------------------------------------------------

Number of objects to be recompiled :5523
Please follow the README.txt instructions for running viewrecomp.sql

PL/SQL procedure successfully completed.

The purpose of this step is to help you determine whether view recompilation should be done at the same time as the CPU install, or scheduled later.

-->Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row created.

Commit complete.

No. of Invalid Objects is :31
Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_ORCL_04Feb2008_16_10_52.log

SQL> SHUTDOWN;
SQL> STARTUP;
SQL> exit

If any invalid objects were reported, run the utlrp.sql script as follows:

[oracle@oracle11gr1 view_recompile]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracle11gr1 admin]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-04 16:18:22

[Output Edited]
PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-04 16:18:50

PL/SQL procedure successfully completed.

[Output Edited]
OBJECTS WITH ERRORS
-------------------
0
[Output Edited]
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
04-FEB-08 04.13.43.702093 PM CPU 6452863 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:
SQL> alter package schemaname.packagename compile;

Critical Patch Update JAN 2008 on Windows

Oracle Critical Patch Update Advisory - January 2008
OracleCPU Advisory

Below procedure based on my test enviourment if you follow this instruction please go through README.TXT file that comes with PATCH SET.

Patch Installation Procedures for Oracle Database Release 10.1.0.5
Patch Number : p6637274_10105_WINNT.zip

SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0

Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.53 or later. You can download it from
OracleMetaLink with patch 2617419.

Check Oracle Inventory Setup

Oracle 10G onwards, the RDBMS software ships with OPatch and it can be loacated under $ORACLE_HOME/OPatch. To download, opatch you need to have an metalink.oracle.com account. On OracleMetaLink search for Patch 4898608 , select the Oracle RDBMS version and download the patch.

set ORACLE_SID=ARIES
set ORACLE_HOME=D:\oracle\product\10.1.0\Db_1
set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%
set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%

D:\patchset10gR1\6637274>cd D:\oracle\product\10.1.0\db_1\opatch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory
[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__02-05-2008_10-57-56.log"

Result:

There is no Interim Patch

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

==================
D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\patchset10gR1\6637274

[Output Edited]
Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\6637274\Apply_6637274_02-05-2008_11-06-12.log"

[Output Edited]
Is this system ready for updating?
Please respond Y|N >
Y
Executing the Apply pre-patch script (D:\patchset10gR1\6637274\custom\scripts\pre.bat)...
Applying patch 6637274...

[Output Edited]
Updating inventory...
Backing up comps.xml ...

********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************
Executing the Apply post-patch script (D:\patchset10gR1\6637274\custom\scripts\post.bat)...

OPatch succeeded.
OPatch returns with error code = 0

D:\oracle\product\10.1.0\Db_1\OPatch>

====================================
CD D:\oracle\product\10.1.0\db_1\CPU\cpujan2008

D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>sqlplus /nolog
SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$instance;

INSTANCE_NAME
---------------
aries
SQL> shutdown immediate;

SQL> STARTUP MIGRATE
ORACLE instance started.
Database opened.
SQL>@CATCPU.SQL

[OUTPUT EDITED]
Package body created.
Package body created.
Package body created.
Session altered.

1 row selected.

Session altered.

OWA_MESSAGE
--------------------------------------------------------------------------------
Installed OWA version is: 9.0.4.0.2

Backing up previous OWA packages to: MODPLSQL\owa_restore_aries.sql

OWA_MESSAGE
-------------------

OWALOAD File: MODPLSQL\owa_all\90\owaload_patch.sql

Logfile: MODPLSQL\owa_patch_aries.log

OWA_DBG_MSG

-------------------
Installed OWA version is: 9.0.4.0.2;
Shipped OWA version is : 9.0.4.0.6;
OWA packages v9.0.4.0.6 will be installed into your database v101050
Will install owadummy.sql and owacomm.sql
[Output Edited]
No errors.
No. of Invalid Objects is :115
Please refer to README.html to for instructions on validating these objects
Logfile for the current catcpu.sql session is : APPLY_ARIES_05Feb2008_11_53_51.log
not spooling currently
SQL> SHUTDOWN IMMEDIATE
SQL> exit
D:\oracle\product\10.1.0\Db_1\cpu\cpujan2008>

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows

cd D:\oracle\product\10.1.0\db_1\rdbms\admin

D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL> conn sys/sys as sysdba
Connected to an idle instance.
SQL> startup
Database opened.

SQL> @utlprp.sql 0

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2008-02-05 12:08:43

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';

no rows selected

SQL> exit

Run the CATCPU.sql and UTLRP.sql on all the instances in the ORACLE_HOME individually.

To view the details about the patch applied :

select * from dba_registry_history;

SELECT * FROM registry$history where ID = '6452863';

ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS
---------------------------- -------- ---------- -------- ---------- ----------
05-FEB-08 12.30.36.406000 PM CPU 6637274 CPUJan2008

Still if you find any invalid objects then, manually recompile them. For example:

SQL> alter package schemaname.packagename compile;

TNS less connection in Oracle.

I have tested this connection method in Oracle 8i, 9i, 10g and in 11g and it works fine.

D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 16:59:44 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
(Host=ntwssrv)(Port=1521))(CONNECT_DATA=(SID=ORCL)))
Connected.
SQL>

If you think about above connection, it is more like specifying complete address in the connect string as you have in the tnsnames.ora file.

For 10g and 11g you can use following method as well. This method does not work for 8i or 9i databases.

==========
D:\>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Jan 24 17:09:47 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott/tiger@ntwssrv:1521/aries
Connected.

===========
[oracle@oracle11gr1 oracle]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 24 17:27:40 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn scott/tiger@oracle11gr1:1521/orcl
Connected.
SQL>

Resolving ORA-12705 on Client

This tip resolves Oracle client error: “ORA-12705: Cannot access NLS data files or invalid environment specified“. According to Oracle documentation, “ORA-12705 can happen under the following conditions:

1. ORA-12705 with incorrect NLS_LANG parameter
2. ORA-12705 with incorrectly specified ORA_NLSx
3. ORA-12705 from incorrect Oracle install or configuration
4. ORA-12705 when using Special Character sets
5. ORA-12705 when connecting with SQL*Net
6. ORA-12705 during migrations
7. ORA-12705 when connecting with SQL*Plus
8. ORA-12705 when connecting with language pre-compilers
9. ORA-12705 during Export/Import

Try doing the following to fix this error on the client side. On the client Windows machine (this tip is only for Windows), The NLS_LANG must be unset in the Windows registry (re-naming works the best because you know what you changed). Look for the NLS_LANG subkey in the registry at \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, and rename it. Then try SQLPlus again.

Limit user sessions in Oracle

How can I limit the number of times a single user can connect to the database at any one time ?

We can do this by limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration:

D:\>sqlplus sys/sys as sysdba

SQL*Plus: Release 10.1.0.5.0 - Production on Mon Jan 21 10:46:28 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show parameter resource_limit

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean FALSE

SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 1;

Profile created.

SQL> create user sree identified by sree123
default tablespace sbdata profile sesslimit;

User created.

SQL> grant create session to sree;

Grant succeeded.

If you want to add this profile to existing user then:

SQL> alter user sree profile sesslimit;

SQL> connect sree/sree123
Connected.

-- I'll start second session in another terminal.

SQL> connect sree/sree123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit

==========

SQL> SELECT DISTINCT resource_name, limit
FROM dba_profiles
ORDER BY resource_name;

SQL> drop profile sesslimit cascade;

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production With the Partitioning, OLAP and Data Mining options

IMPORTANT LINKS

Oracle By Example (OBE)

The Oracle by Example (OBE) series provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. OBE solutions are built for practical real-world situations, allowing you to gain valuable hands-on experience as well as use the presented solutions as the foundation for production implementation, dramatically reducing time to deployment.
Direct Link to all Articles

Installing Oracle Database 11g on Linux
Direct Link to Article

Installing Oracle Database 11g on Windows
Direct Link to Article

Exploring Your Database with Enterprise Manager Database Control
Direct Link to Article

Using Database Replay to Perform Real-World Testing
Direct Link to Article

Creating a Physical Standby Database
Direct Link to Article

Oracle by Example Series: Oracle Database 11g
Direct Link to Articles

Oracle by Example Series: Oracle Database 10g
Direct Link to Articles

Automating Installation of Oracle Database 10g and Red Hat Enterprise Linux
Direct Link to Article

RAC LINKS

Biggest challenge in learning / understanding RAC is lack of hands on experience because of expensive hardware, network cards, interconnect and cluster file system. The following links cover step by step Oracle RAC installation and configuration
on single laptop/desktop using VMWare Server without investing anything extra on hardware (using two virtual machines, virtual network cards and virtual shared disk for Oracle RAC database shared files system ).

Download VMware Server (for Windows and Linux systems)
Download VMWARE

Installing Oracle Database 10g on Windows by Using Real Application Clusters (RAC) and Automated Storage Management (ASM)
Direct Link to Article

Steps for configuring/building Oracle RAC Database on laptop/desktop machine (without investing any thing on hardware).
Direct Link to Article

Oracle 10g RAC installation using Unbreakable Linux
Direct Link to Article

Installing Oracle RAC 10g Release 1 on Linux x86
Direct Link to Article

Installation of Oracle 10g release 2 (10.2.0.1) RAC on Linux (CentOS 4) using VMware Server with no additional shared disk devices.
Direct Link to Article

Installing Oracle RAC 10g Release 2 on Linux x86
Direct Link to Article

Oracle 10g RAC On Windows 2003 Using VMware Server
Direct Link to Article

Install Oracle RAC 10g on Oracle Enterprise Linux Using VMware Server
Direct Link to Article

The Oracle-on-Linux Installation Menu
Direct Link to Article

Installing Oracle Database 10g with Real Application Cluster (RAC) on Red Hat Enterprise Linux Advanced Server 3
Direct Link to Article

Convert a Single Instance Database to Oracle RAC 10g on RHEL3
Direct Link to Article

Code to shrink Your Tablespaces

Use it at your own risk.


SET verify OFF
COLUMN file_name format a50 WORD_WRAPPED
COLUMN smallest format 999,990 heading "Smallest|Size|Poss."
COLUMN currsize format 999,990 heading "Current|Size"
COLUMN savings format 999,990 heading "Poss.|Savings"
BREAK on REPORT
COMPUTE sum of savings on REPORT
COLUMN value new_val BLKSIZE
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size';

SELECT FILE_NAME,
CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SMALLEST,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) CURRSIZE,
CEIL(BLOCKS *&& BLKSIZE / 1024 / 1024) - CEIL((NVL(HWM,1) *&& BLKSIZE) / 1024 / 1024) SAVINGS
FROM DBA_DATA_FILES A,
(SELECT FILE_ID,
MAX(BLOCK_ID + BLOCKS - 1) HWM
FROM DBA_EXTENTS
GROUP BY FILE_ID) B
WHERE A.FILE_ID = B.FILE_ID (+);

SELECT 'alter database datafile '''
||File_Name
||''' resize '
||Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024)
||'m;' cmd
FROM dba_Data_Files a,
(SELECT File_Id,
MAX(Block_Id + Blocks - 1) hwm
FROM dba_Extents
GROUP BY File_Id) b
WHERE a.File_Id = b.File_Id (+)
AND Ceil(Blocks *&& blkSize / 1024 / 1024) - Ceil((Nvl(hwm,1) *&& blkSize) / 1024 / 1024) > 0;

Cool Scripts for daily DBA activities

I will be adding scripts to this post when ever I come across a good one.

Note: Test the scripts before using on a Production database.
Finely Format the SQL statements before use.

Calculate the Database Size

COLUMN "Total Mb" FORMAT 999,999,999.0
COLUMN "Redo Mb" FORMAT 999,999,999.0
COLUMN "Temp Mb" FORMAT 999,999,999.0
COLUMN "Data Mb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;
=========================================================
Heavy CPU SQL

This will generate the top SQL statements that produce heavy CPU usage

set termout on
set feedback on
set pagesize 132

#spool cpusql.lis

SELECT username,address, hash_value,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",sql_text
FROM v$sqlarea,dba_users
WHERE buffer_gets > 50000
and executions > 0
and v$sqlarea.parsing_user_id = dba_users.user_id
order by 4 desc;

#spool off;
=========================================================
Calculate the Table Size

SELECT Segment_Name Table_Name,
SUM(Bytes) / (1024 * 1024) Table_Size_Meg
FROM dba_Extents
WHERE Owner = 'SCOTT'
AND Segment_Name = 'DEPT'
AND Segment_Type = 'TABLE'
GROUP BY Segment_Name
/

=============================================================================
Determine Tablespace Usage

SELECT a.TableSpace_Name,
Round(a.Bytes_Alloc / 1024 / 1024,2) Megs_Alloc,
Round(Nvl(b.Bytes_Free,0) / 1024 / 1024,2) Megs_Free,
Round((a.Bytes_Alloc - Nvl(b.Bytes_Free,0)) / 1024 / 1024,
2) Megs_Used,
Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Free,
100 - Round((Nvl(b.Bytes_Free,0) / a.Bytes_Alloc) * 100,
2) pct_Used,
Round(MaxBytes / 1048576,2) MAX
FROM (SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Alloc,
SUM(DECODE(f.AutoexTensible,'YES',f.MaxBytes,
'NO',f.Bytes)) MaxBytes
FROM dba_Data_Files f
GROUP BY TableSpace_Name) a,
(SELECT f.TableSpace_Name,
SUM(f.Bytes) Bytes_Free
FROM dba_Free_Space f
GROUP BY TableSpace_Name) b
WHERE a.TableSpace_Name = b.TableSpace_Name (+)
UNION ALL
SELECT h.TableSpace_Name,
Round(SUM(h.Bytes_Free + h.Bytes_Used) / 1048576,
2) Megs_Alloc,
Round(SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / 1048576,
2) Megs_Free,
Round(SUM(Nvl(p.Bytes_Used,0)) / 1048576,2) Megs_Used,
Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Free,
100 - Round((SUM((h.Bytes_Free + h.Bytes_Used) - Nvl(p.Bytes_Used,0)) / SUM(h.Bytes_Used + h.Bytes_Free)) * 100,
2) pct_Used,
Round(SUM(f.MaxBytes) / 1048576,2) MAX
FROM sys.v_$temp_Space_Header h,
sys.v_$temp_Extent_Pool p,
dba_temp_Files f
WHERE p.File_Id (+) = h.File_Id
AND p.TableSpace_Name (+) = h.TableSpace_Name
AND f.File_Id = h.File_Id
AND f.TableSpace_Name = h.TableSpace_Name
GROUP BY h.TableSpace_Name
ORDER BY 1
-------------------------------------------------------------------------
SELECT TableSpace_Name "Tablespace",
COUNT(Bytes) "Pieces",
MIN(Bytes) "Min",
Round(Avg(Bytes)) "Average",
MAX(Bytes) "Max",
SUM(Bytes) "Total"
FROM sys.dba_Free_Space
GROUP BY TableSpace_Name

==========================================================================
Track your import process:

SELECT Substr(sql_Text,Instr(sql_Text,'INTO "'),30) Table_Name,
Rows_Processed,
Round((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) Minutes,
Trunc(Rows_Processed / ((SYSDATE - To_date(First_Load_Time,'yyyy-mm-dd hh24:mi:ss')) * 24 * 60)) Rows_Per_Minute
FROM sys.v_$sqlArea
WHERE sql_Text LIKE 'INSERT %INTO "%'
AND Command_Type = 2
AND Open_Versions > 0;
===============================================================================
Details of parameters for SPfile modifications

SELECT NAME,
Isses_modIfiAble,
Issys_modIfiAble,
IsInstance_modIfiAble
FROM v$Parameter
ORDER BY NAME;
==============================================================================
Query to find the difference between two dates omitting weekends and holidays.


SELECT (To_date('31/01/2008','dd/mm/rrrr') - To_date('01/01/2008','dd/mm/rrrr') + 1) - (SELECT COUNT(Days)
FROM (SELECT To_char(To_date('01/01/2008','dd/mm/rrrr') + LEVEL,'D') Days
FROM Dual CONNECT BY LEVEL <= 31) WHERE Days IN ('7','1')) DAY
FROM Dual;
===============================================================================

Deleting Duplicate Records

You may have come across numerous situations where duplicate records needs to be deleted from some table quickly. More often than not this is due to an application or data transformation issue and the number of duplicate rows are generally small related to the overall number of rows in the table. DBAs struggle with this task and spend way too much time and so coming up with elaborate scripts.

Assuming the table looks like this:

ACCOUNT
ACCOUNT_ID
STATUS_CODE
FIRST_NAME
LAST_NAME
STREET_ADDRESS

In this example, there is no primary key or unique constraint preventing duplicates from being inserted. The "logical primary key" is ACCOUNT_ID and STATUS_CODE.

First we might want to view the duplicate records to assist with troubleshooting and this can be done with this query:

SELECT Account_Id,
Status_Code,
COUNT(* ) OccurAnces
FROM ACCOUNT
GROUP BY Account_Id,Status_Code
HAVING COUNT(* ) > 1;

And to delete them we rely on Oracle’s rowed which is a unique "key" that exists on every table row:

DELETE
FROM ACCOUNT A
WHERE
(ACCOUNT_ID,STATUS_CODE) IN (
SELECT
ACCOUNT_ID,
STATUS_CODE,
FROM ACCOUNT
GROUP BY
ACCOUNT_ID,
STATUS_CODE
HAVING
COUNT(*) > 1) AND
ROWID != (
SELECT
MIN(ROWID)
FROM ACCOUNT
WHERE
ACCOUNT_ID = A.ACCOUNT_ID AND
STATUS_CODE = A.STATUS_CODE);

Alternatively this more simplified query can be used, but in some cases it might not execute as fast as the above query because of having to do more random I/O:

DELETE FROM ACCOUNT a
WHERE RowId != (SELECT MIN(RowId)
FROM ACCOUNT
WHERE Account_Id = a.Account_Id
AND Status_Code = a.Status_Code);

Please make sure that you thoroughly understand your duplicate record problem and what makes a record unique before deleting duplicate records in a table. Make sure you test the above SQL statements in a test environment with a representative test case before attempting this in a production environment

DBVERIFY

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored, or as a diagnostic aid when you have encountered data corruption problems.

DBVERIFY checks are limited to cache-managed blocks (that is, data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.


There are two command-line interfaces to DBVERIFY


1.Using DBVERIFY to Validate Disk Blocks of a Single Datafile

D:/>dbv FILE=D:\oracle\product\10.1.0\oradata\ORCL\users.dbf FEEDBACK=100

------------------------------------------------------------

2.Using DBVERIFY to Validate a Segment
If we want to check segment level you can use SEGMENT_ID

SEGMENT_ID = Segment ID (tsn.relfile.block)

we can get all three values from below query.

SQL> Grant sysdba to scott;

SQL>SELECT t.ts#,
s.Header_File,
s.Header_Block
FROM v$TableSpace t,
dba_Segments s
WHERE s.Owner = 'SCOTT'
AND s.Segment_Name = 'EMP'
AND t.NAME = s.TableSpace_Name;


TS# HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
4 4 27

SQL> host dbv userid=scott/tiger segment_id=4.4.27

New Year Wishes to all my Guests....

May the dawning of this New Year, fill your heart with new hopes, open up new horizons and bring for you promises of brighter tomorrows. May you have a great New Year - 2008.

Some of the Oracle Database 11g New Features

Oracle Database 11g Release 1 (11.1) New Features
=================================================

Case Sensitive Passwords

Beginning with Oracle Database 11g Release 1, database passwords are case sensitive.
You can disable this features by setting the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE.

=======================================
Readonly Tables

To place a table in read-only mode, you must have the ALTER TABLE privilege on thetable or the ALTER ANY

TABLE privilege. In addition, the COMPATIBILE initializationparameter must be set to 11.1.0 or greater.

The following example places the EMP table in read-only mode:

ALTER TABLE EMP READ ONLY;

The following example returns the table to read/write mode:

ALTER TABLE EMP READ WRITE;

=========================================

Invisible Indexes

An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it.

Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Altering Indexes

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

To create an invisible index, use the SQL statement CREATE INDEX with theINVISIBLE clause. The following statement creates an invisible index named IND_EMP_DEPT for the DEPT column of the EMP table:

CREATE INDEX ind_emp_dept ON emp(dept)INVISIBLE;

To make an invisible index visible, issue this statement:

ALTER INDEX ind_emp_dept VISIBLE;

To make a visible index invisible, issue this statement:

ALTER INDEX ind_emp_dept INVISIBLE;

To check whether an index is visible or invisible, query the dictionary views

USER[ALL][DBA]_INDEXES

For example, to determine if above index is invisible, issue the following query:

SELECT INDEX_NAME, VISIBILITY FROM
USER_INDEXES WHERE INDEX_NAME = 'IND_EMP_DEPT';

INDEX_NAME VISIBILITY
------------- ----------
IND_EMP_DEPT VISIBLE

===========================================

Automatic Stats Collection on Tables

As you know that you can enable DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. But, starting with Oracle Database 11g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. Information abouthow many rows are affected is maintained in the SGA, until periodically (about everythree hours) SMON incorporates the data into the data dictionary.

This data dictionary information is made visible through the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The database uses these
views to identify tables with stale statistics.

=================================================

Virtual columns

Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.

==============================================

Simplified and improved automatic memory management

You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.

===============================================

Database resident connection pooling

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of
database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.

==================================================

Tablespace-level encryption

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted.

All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.

==========================================================

Result cache in the system global area

Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The result cache occupies memory in the shared pool.

=============================================================

Enhanced online index creation and rebuild

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.

===============================================================

Ability to online redefine tables that have materialized view logs

Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

=====================================================================

Optimized ALTER TABLE...ADD COLUMN

For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:

Add a NOT NULL column with a default value

Add a nullable column without a default value

Add a virtual column
==========================================================

Oracle Express Editon

These are just some general points that might save you a bit of time and head scratching if you decide to give Oracle XE a go:

First, let's start with the good:

It's free to download and develop on
It's free to deploy (including distribution, as long as you make the other party aware of the licensing details)
It is the first really simple installation on either Windows or Linux that I have ever seen Oracle do
The included tools are superb and very modern and AJAXy

However, there are limitations:

--> Although you can install it on a multiple CPU machine, and it will run on one, it will only use one CPU on that machine.

--> It will only use a maximum 1 gigabyte of memory (again, it will run on machines with more memory; it just won't use it)

--> It has a database size limit of 4 gigabytes for user data (this is all available to the user; system data is counted outside of that 4 gig limit)

--> Only one instance of Oracle XE can run on any given computer.

You can download Oracle XE software from following link:
Download Link

Locally vs. Dictionary Managed Tablespaces

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM -- DICTIONARY -USER
UNDOTBS1 -- LOCAL -SYSTEM
TEMP -- LOCAL -UNIFORM
USERS -- LOCAL -SYSTEM

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.

Execute the following statement to create a dictionary managed tablespace:

SQL> CREATE TABLESPACE tblspc1 DATAFILE '/oradata/tblspc1_01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed tablespace:

SQL> CREATE TABLESPACE tblspc2 DATAFILE '/oradata/tblspc2_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL> CREATE TABLESPACE tblspc3 DATAFILE '/oradata/tblspc3_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Note the difference between AUTOALLOCATE and UNIFORM SIZE:

AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose "optimal" next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index, is created.

Also not, if you specify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.

Advantages of Locally Managed Tablespaces:

Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
Reduce contention on data dictionary tables (single ST enqueue)
Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
Changes to the extent bitmaps do not generate rollback information

Locally Managed SYSTEM Tablespace:

From Oracle9i release 9.2 one can change the SYSTEM tablespace to locally managed. Further, if you create a database with DBCA (Database Configuration Assistant), it will have a locally managed SYSTEM tablespace by default. The following restrictions apply:

No dictionary-managed tablespace in the database can be READ WRITE.
You cannot create new dictionary managed tablespaces
You cannot convert any dictionary managed tablespaces to local

Thus, it is best only to convert the SYSTEM tablespace to LMT after all other tablespaces are migrated to LMT.

Segment Space Management in LMT:

From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managed segments when setting Segment Space Management to AUTO for a tablespace. Look at this example:

SQL> CREATE TABLESPACE tblspc4 DATAFILE '/oradata/tblspc4_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects. The Automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELSITS. This is of particular importance when using RAC, or if "buffer busy waits" are deteted.

Convert between LMT and DMT:

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily convert between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('tblspc1');
PL/SQL procedure successfully completed.

SQL>exec dbms_space_admin.Tablespace_Migrate_FROM_Local('tblspc2');
PL/SQL procedure successfully completed.

Installing the April 2007 Critical Patch Update on Windows

This article describes the procedure to install the April 2007 CPU patch on Oracle Database Release 10.1.0.5. The Patch Number is p5907304_10105_WINNT.zip

SYSTEM Overview

Platforms : Windows XP SP2
Database : 10.1.0.5.0

OPatch Utility Information

You must use the OPatch utility release 1.0.0.0.57 or later. You can download it from OracleMetaLink with patch 2617419.

Check Oracle Inventory Setup

D:\>set ORACLE_SID=TEST

D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1

D:\>cd D:\oracle\product\10.1.0\db_1\opatch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.53

Note: if Opatch utility version is <= 1.0.0.0.53 then you have to download OPATCH utility from the Metalink site.
[output not attached]

Oracle Home = D:\oracle\product\10.1.0\db_1
Location of Oracle Universal Installer components = D:\oracle\product\10.1.0\db_1\oui
Location of OraInstaller.jar = "D:\oracle\product\10.1.0\db_1\oui\jlib"
Oracle Universal Installer shared library = D:\oracle\product\10.1.0\db_1\oui\lib\win32\oraInstaller.dll
Location of Oracle Inventory Pointer = N/A
Location of Oracle Inventory = D:\oracle\product\10.1.0\db_1\inventory
Path to Java = "D:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\java.exe"
Log file = D:\oracle\product\10.1.0\db_1/.patch_storage//*.log

Creating log file "D:\oracle\product\10.1.0\db_1\.patch_storage\LsInventory__12-13-2007_10-18-45.log"

Result:

There is no Interim Patch

OPatch succeeded.
OPatch returns with error code = 0

Preinstallation Instructions

1. Ensure that your Oracle Database installation is the same release for which you are applying this patch.

2. Back up the current database that is installed
-->ORACLE_HOME or ORA INVENTORY

3. Perform a clean shutdown of all Oracle services

D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.

OracleDBConsoleTEST

Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleTEST service is stopping...............
The OracleDBConsoleTEST service was stopped successfully.

The OracleServiceTEST service is stopping...........
The OracleServiceTEST service was stopped successfully.

D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping......
The OracleServiceRCAT service was stopped successfully.


D:\>LSNRCTL stop

LSNRCTL for 32-bit Windows: Version 10.1.0.5.0 - Production on 13-DEC-2007 10:30:50

Copyright (c) 1991, 2004, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

D:\>net stop OracleCSService

The OracleCSService service was stopped successfully.


D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

4. Set the PERL5LIB and PATH environment variables to point to Perl in Oracle Home

D:\>set PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\5.6.1\lib;%PERL5LIB%

D:\>set PATH=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86;%PATH%


5. set ORACLE_HOME variable

D:\>set ORACLE_HOME=D:\oracle\product\10.1.0\db_1

6. Check if the java and jar executables are present in your Oracle Home

-->JAVA = %ORACLE_HOME%\jre\\bin
-->JAR = %ORACLE_HOME%\jdk\bin

7. Go to directory where you downloaded the patch

D:\oracle\product\10.1.0\Db_1\OPatch>opatch apply D:\5907304

Post Installation Instructions

1. Start All Oracle Services.

D:\>net start OracleServiceTEST

D:\>net start OracleServiceRCAT

D:\>set ORACLE_SID=TEST

D:\>emctl start dbconsole

D:\>LSNRCTL start

D:\>net start OracleCSService

D:\>net start OracleOraDb10g_home1iSQL*Plus

2. For each database instance running out of the ORACLE_HOME being patched, connect to the database using SQL*Plus as SYSDBA and run catcpu.sql as follows:

D:\>rem go to CPU directory

D:\>CD D:\oracle\product\10.1.0\db_1\CPU\CPUApr2007

D:\oracle\product\10.1.0\Db_1\cpu\cpuapr2007>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:31:43 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MIGRATE
ORACLE instance started.

Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> @CATCPU.SQL

[output not attached]

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> quit

If catcpu.sql reports any Invalid Objects, compile the invalid objects as follows

D:\>cd D:\oracle\product\10.1.0\db_1\rdbms\admin

D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 10.1.0.5.0 - Production on Fri Dec 14 14:55:21 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> @utlprp.sql

SQL> select OBJECT_NAME from dba_objects where status = 'INVALID';

no rows selected

3. If any databases have been created without using DBCA (that is, using the command line), and you need to monitor such a database by using Oracle Enterprise Manager 10g, follow these steps:

$ sqlplus SYS / AS SYSDBA
SQL> ALTER USER DBSNMP ACCOUNT UNLOCK
SQL> PASSWORD DBSNMP
Changing password for DBSNMP
New password: new_password
Retype new password: new_password

Upgrade the Oracle database from 10.1.0.3.0 to 10.1.0.5.0

This article describes the process of upgrading the Oracle Database Server 10g Release 10.1.0.3 to Oracle Database 10g Release 10.1.0.5 on Windows XP SP2.

1. Patch Set Overview

You can apply the Oracle Database 10g release 10.1.0.5 patch set to the following Oracle Database 10g release 1 (10.1.0.2, 10.1.0.3, or 10.1.0.4) installations.

2. Oracle Universal Installer Version Requirements

This patch set includes Oracle Universal Installer release 10.1.0.5, which is also installed when you install this patch set.

3. System Requirements

Hardware requirement : Intel Pentium
OS requirement : Windows XP Prof SP1 or later
Oracle DB : 10gr1(10.1.0.2) or later

4. Pre-installation Tasks

1. Identify the Oracle Database Installation

This is not a complete software distribution. You must install it in an existing Oracle Database 10g Oracle home.

2. Download and extract the p4505133_10105_WINNT.zip patch set.

3. Shut Down Oracle Databases

D:\>sqlplus sys@TEST/pwd as sysdba
SQL>shutdown immediate;

D:\>sqlplus sys@RCAT/pwd as sysdba
SQL>shutdown immediate;

4. Stop All Services

D:\>net stop OracleOraDb10g_home1iSQL*Plus
..
The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.

D:\>net stop OracleServiceTEST
The following services are dependent on the OracleServiceTEST service.
Stopping the OracleServiceTEST service will also stop these services.

OracleDBConsoleTEST

Do you want to continue this operation? (Y/N) [N]: Y
The OracleDBConsoleTEST service is stopping....
The OracleDBConsoleTEST service was stopped successfully.

The OracleServiceTEST service is stopping.
The OracleServiceTEST service was stopped successfully.

D:\>net stop OracleServiceRCAT
The OracleServiceRCAT service is stopping.......
The OracleServiceRCAT service was stopped successfully.

5. Stop the listener:

D:\>LSNRCTL stop

LSNRCTL for 32-bit Windows: Version 10.1.0.3.0 - Production on 28-DEC-2007 11:05:13

Copyright (c) 1991, 2004, Oracle. All rights reserved.


Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

D:\>net stop OracleCSService

The OracleCSService service was stopped successfully.

6. Back up the SYSTEM.

Installation Tasks

1. Run SETUP.EXE from where you downloaded/extracted your patch.

In the Name field in the Destination section, select the 10.1.0.x Oracle home that you want to update from the drop down list, then click Next.

On the Summary screen, click Install.

Upgrading Release 10.1 to Oracle Database 10g Release 1 (10.1.0.5)


1.Check SYSTEM TBS should have 10mb Free Space.
2.SHARED_POOL_SIZE or JAVA_POOL_SIZE at least 150 MB.

SQL> alter tablespace SYSTEM
2 add datafile 'D:\oracle\product\10.1.0\oradata\TEST\system02.dbf' size 20m;

Tablespace altered.

SQL> alter system set SHARED_POOL_SIZE = 150M SCOPE=spfile;

System altered.

SQL> alter system set JAVA_POOL_SIZE = 150M SCOPE=spfile;

System altered.


SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 1023410176 bytes
Fixed Size 782304 bytes
Variable Size 74190880 bytes
Database Buffers 947912704 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

SQL> SPOOL patch.log

SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\catpatch.sql
DOC>######################################################################
DOC>######################################################################
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> terminate the current SQLPLUS session if the user is not SYS.
DOC> Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

PL/SQL procedure successfully completed.
....
[output not attached]

SQL> SPOOL OFF

Restart the database

SQL>SHUTDOWN
SQL>STARTUP

Run the utlrp.sql script to recompile all invalid PL/SQL packages

SQL> @D:\oracle\product\10.1.0\db_1\rdbms\admin\utlrp.sql

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production
TNS for 32-bit Windows: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

Known Issues

select dbms_java.full_ncomp_enabled from dual
*
ERROR at line 1:
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
instructions.
ORA-06512: at "SYS.DBMS_JAVA", line 236

IGNORE
---> If you do not use Java in the database, then ignore this error message.

Don't Ignore
--->If you use Java in the database, then install the NCOMP libraries by selecting the Oracle Database 10g Products installation type from the Oracle Database Companion CD.

If RMAN CATALOG is also configure then upgrade RMAN CATALOG

D:\>rman CATALOG=rman/cat@RCAT

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> UPGRADE CATALOG;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> UPGRADE CATALOG;

recovery catalog upgraded to version 10.01.00
DBMS_RCVMAN package upgraded to version 10.01.00
DBMS_RCVCAT package upgraded to version 10.01.00

Finding your OS and Database 32 bits or 64 bits

Want to know whether your Database / Operating System is running a 32-bit or 64-bit software?


DBAs often need to know if they should install Oracle 32-bit or 64-bit software on a given server. If the operating system support 64-bit applications, the 64-bit Oracle distribution can be loaded. Otherwise, the 32-bit distribution must be installed. The following Unix commands will tell you whether your OS is running 32-bits or 64-bits:

Solaris - isainfo -v (this command doesn't exist on Solaris 2.6 because it is only 32-bits)

HP-UX - getconf KERNEL_BITS

AIX - bootinfo -K

LINUX - uname -a

If your Linux OS is 32 bit the output will be like below:

Linux oraclesrvr 2.6.18-8.el5PAE #1 SMP Tue Jun 5 23:39:57 EDT 2007 i686 i686 i386 GNU/Linux

If your Linux OS is 64 bit the output will be like below:

Linux aptserver 2.6.18-8.el5xen #1 SMP Tue Jun 5 23:53:34 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux

If you need to know Oracle 32-bit or 64-bit software is currently installed on a system, connect using a command line utility like sqlplus and look at the banner. If you are running 64-bit software, it will be mentioned in the banner. If nothing is listed, you are running on a 32-bit base.


If your Oracle Software is 64 bit the output will be like below:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production


If your Oracle Software is 32 bit the output will be like below:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

Relocating Database Files

The CRD&T files are called as Database Files:

1. Control files
2. Redo log files
3. Datafiles & Tempfiles


At some moments we need to move or relocate the Database files from original(old) location to new location.

STEPS FOR RELOCATING THESE DATABASE FILES:

I.How to move DATAFILES from old to new location(not SYSTEM,UNDO & TEMP tablespaces'):

Step 1: Take tablespace offline
SQL> alter tablespace tbsname OFFLINE;

Step 2:Use OS level command to MOVE datafiles from old to new location.
$ mv 'old location' to 'new location'

Step 3: Rename datafiles in database.
SQL> alter database rename file 'old location' to 'new location';

Step 4: Take tablespace ONLINE
SQL> alter tablespace tbsname ONLINE;


II.For SYSTEM or UNDO tablespace:

Step 1: Shutdown database
SQL> shutdown immediate;

Step 2: Move datafiles from old to new location using OS command.
$ mv 'old location' 'new location'

Step 3: Startup database in mount stage.
SQL> startup mount;

Step 4: Rename the datafile
SQL> alter database rename file 'old location' to 'new location';

Step 5: Open the database for normal use
SQL> alter database open;


III.How to move control file from old to new location:

Step 1: Shutdown the Database
SQL> shutdown immediate;

Step 2: Move controlfile from old to new location
$ mv old location new location

Step 3: Edit CONTROL_FILES parameter in pfile and change location from old to new
CONTROL_FILES='new location'

Step 4: recreate SPFILE from PFILE
SQL> create spfile from pfile;

Step 5: startup database
SQL> startup;

IV.How to move REDO LOG MEMBER from old to new location

Step 1: shutdown database
SQL> shutdown immediate;

Step 2: Move redo log member from old to new location
$ mv oldlocation newlocation

Step 3: startup database with mount stage
SQL> startup mount;

Step 4: rename redo log member in database
SQL> alter database rename file 'old location' to 'new location';

Step 5: open database for normal use
SQL> alter database open;


V. We cannot relocate the tempfile, if we need to relocate it(tempfile) then the best option is to recreate the temp tablespace in the new location.