Thursday, March 31, 2011

About Bind Variable

http://umardba.blogspot.com/2009/11/bind-variables-key-to-application.html

Bind variable peeking:

http://www.dbspecialists.com/specialists/specialist2003-11.html

Statistics gathering..

http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/stats.htm

Objects are considered stale when 10% of the total rows have been changed. When you issue GATHER_TABLE_STATS with GATHER STALE, the procedure checks the USER_TAB_MODIFICATIONS view. If a monitored table has been modified more than 10%, then statistics are gathered again. The information about changes of tables, as shown in the USER_TAB_MODIFICATIONS view, can be flushed from the SGA into the data dictionary with the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure.

Monday, March 28, 2011

OEM Grid Control..

nice presentation

http://oukc.oracle.com/static05/opn/oracle9i_database/40263/013107_40263/index.htm

http://www.oracle.com/technetwork/articles/havewala-gridcontrol-088685.html

Oracle Grid control componenets:

- Repository database
- OMS, oralce management service
- EM agent

OMS (This is a J2EE application deployed on Oracle Application Server 10g), components of OMS:

- Oracle HTTP Server
- Oracle Application Server Containers for Java (OC4J)
- OracleAS Web Cache.

Therefore, Grid Control is a reduced version of Oracle Application Server itself.

use Oracle Process Management Notification Control , OPMNCTL
or dcmctl (Distributed Configuration Management Control).
This is in addition to the Enterprise Manager Control (emctl) utility

/app/oracle/instancename/oms10g/opmn/bin > ./opmnctl status

Processes in Instance: EnterpriseManager0.hostname
-------------------+--------------------+---------+---------
ias-component | process-type | pid | status
-------------------+--------------------+---------+---------
HTTP_Server | HTTP_Server | 23058 | Alive
LogLoader | logloaderd | N/A | Down
dcm-daemon | dcm-daemon | N/A | Down
OC4J | home | 23059 | Alive
OC4J | OC4J_EMPROV | 23060 | Alive
OC4J | OC4J_EM | 23061 | Alive
OC4J | OCMRepeater | 23064 | Alive
WebCache | WebCache | 23071 | Alive
WebCache | WebCacheAdmin | 23070 | Alive
DSA | DSA | N/A | Down

here OC4J_EM is only a single Unix process with its own PID

troubleshooting:

http://www.myoraclesupports.com/content/grid-control-target-maintenance-steps-diagnose-issues-related-agent-unreachable-status

http://www.myoraclesupports.com/content/how-troubleshoot-communication-between-oracle-management-service-oms-and-grid-agent-componen

Configuring the management agent:

•The emd.properties file in the sysman/ directory

This file contains the global variables for the Management Agent. For most installations, the following two parameters need to be modified:

◦REPOSITORY_URL=http://host:port/em/upload/

This is the location of the Oracle Management Service where you want the Management Agent to register.

◦EMD_URL=http://local_host:port/emd/main/

This is the location and port where you want the Management Agent to listen for communications from clients.

•The targets.xml file in the sysman/directory

This file contains the information about the local system and its database instances. It is in XML format and needs to be customized. Although the Management Agent attempts to configure this file, z/OS differences may not permit it to do so.

emctl start agent

emctl upload

emctl stop agent

Number of Files to Upload:
This metric shows the number of XML files that are in the $ORACLE_HOME/sysman/emd/upload directory waiting to be uploaded to the repository

Friday, March 25, 2011

CHECKPOINT TUNING AND ERROR HANDLING

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=147468.1

How to Check Why Shutdown Immediate Hangs? (Doc ID 164504.1)

fix:

In order to see why shutdown immediate is hanging we need to do the following:

A. While shutdown immediate is hanging

Start Server Manager (or SQL*Plus for 8i or higher)
SVRMGRL> connect internal (or SYSDBA for 8i or higher)
SVRMGRL> select * from x$ktuxe where ktuxecfl = 'DEAD';

This shows dead transactions that smon is looking to rollback.

B. Plan to shutdown again and gather some information. Before issuing the
shutdown immediate command set some events as follows:

SVRMGRL> connect internal
SVRMGRL> alter session set events '10046 trace name context forever,level 12';
SVRMGRL> alter session set events '10400 trace name context forever, level 1';
SVRMGRL> shutdown immediate;

10046 turns on extended SQL_TRACE for the shutdown process.
10400 dumps a systemstate every 5 minutes.

Let the shutdown go for around 15 minutes and then send the traces to Oracle
Support. The traces are written to the location specified by user_dump_dest parameter.
The trace files should show where the time is going.

Note :
Shut down immediate may hang because of various reasons.
- processes still continue to be connected to the database and do not terminate.
- SMON is cleaning temp segments or performing delayed block cleanouts.
- Uncommitted transactions are being rolled back.

ora - 7445

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=211909.1

Wednesday, March 23, 2011

NLS characterset in oracle

metalink:

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=60134.1

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=227330.1

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=306411.1

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=333489.1

This section of the discussion starts with the assumption that the
Oracle database is set up with US7ASCII character set.

Character sets are used to actually STORE locale-dependant data.
When clients from different locales connect to the database, automatic
conversion of character sets takes place in the background to
display the data properly (based on the NLS_LANG setting on the client).
When a client in a different locale sends
data back to Oracle, same conversion takes place before data is actually
stored. Data is ALWAYS stored in the character set specified in the
CREATE DATABASE statement (or default, if not explicitly specified).

During these character set conversions back-and-forth, any characters that
cannot be mapped are replaced with "replacement characters".
The following is an excerpt from the Oracle8i National Language Support Guide,
Release 8.1.5 --

If, for example, a server used US7ASCII and a German client WE8ISO8859P1,
the German character ß would be replaced with "?" and the character ä would
be replaced with "a".

Replacement characters may be defined for specific characters as part of a
character set definition. Where a specific replacement character is not defined,
a default replacement character is used. To avoid the use of replacement characters
when converting from client to database character set, the server character set
should be a superset (or equivalent) of all the client character sets. In the above
example, the server's character set was not chosen wisely. If German data is expected
to be stored on the server, a character set which supports German letters is needed,
for example, WE8ISO8859P1 for both the server and the client.


If a character got STORED as a "?" (or some other incorrect conversion), there is
nothing you can do through init.ora to fix it.
Parameters from init.ora do not touch the structure and/or contents of data.
The only way to fix this issue would be to change the database character set.
Init.ora will allow you to modify the language and the territory but not the character set.


--------------------------------------------------------------------------------
NLS often defaults to American which can cause problems in a European or Global context.
During conversion from one language to another,
any character which has no equivalent is replaced by
a character which is character-set dependent.
--------------------------------------------------------------------------------
The Oracle Parameter to change is NLS_CHARACTERSET,(equivalent to NLS_LANGUAGE).
--------------------------------------------------------------------------------
This can be done in a number of ways, all of which are discussed below.
--------------------------------------------------------------------------------


There are four ways to specify NLS parameters in general --
1. As initialization parameters on the server.
Parameters can be included in the initialization file (INIT.ORA) to specify a default server NLS environment.
These settings have no effect on the client side; they control only the server's behavior.
For example: NLS_TERRITORY = "CZECH REPUBLIC"
2. As environment variables on the client. NLS parameters can be used to specify
locale-dependent behavior for the client, overriding the defaults set for the server
in the initialization file.For example: on a UNIX system:
% setenv NLS_SORT FRENCH
3. As ALTER SESSION parameters. NLS parameters set in an ALTER SESSION statement can be used
to override the defaults set for the server in the initialization file,
or set by the client with environment variables. For example:
SVRMGR> ALTER SESSION SET NLS_SORT = FRENCH
4. As an SQL function parameter. NLS parameters can be used explicitly to hardcode
NLS behavior within a SQL function. Doing so will override the defaults set for the
server in the initialization file, the client with environment variables, or
ALTER SESSION on the client.For example:
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

* The following NLS parameters can be initialization parameters, environment variables,
and ALTER SESSION parameters: NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_ LANGUAGE
NLS_ISO_CURRENCY NLS_NUMERIC_CHARACTERS NLS_SORT
* The following parameters can be specified as initialization parameters and
ALTER SESSION parameters, but not as environment variables: NLS_LANGUAGE
NLS_TERRITORY
* The following NLS parameters can be set only as environment variables:
NLS_CREDIT NLS_DEBIT NLS_LANG NLS_LIST_SEPARATOR NLS_MONETARY_CHARACTERS
NLS_NCHAR

The above is a good overview about the NLS environment in Oracle.

Now turning to the problem on hand,
I am assuming that your analysis that you need to change the character set
is correct (there are a few issues with Microsoft's DTS and OLEDB and ODBC drivers
for Oracle, which you can find on MSDN, if you don't hit the proverbial
nail on the head with character sets).

Changing Character Sets cannot be done through init.ora :-(
NLS_LANG is about the only thing that will allow you to use a different
character set for a session.
IDEALLY, the server's character set should be a superset of all client locales.
Since it sounds like your server is currently set up with US7ASCII, and US7ASCII
is actually a SUBSET of WE8ISO8859P1.
It is better to change the server's character set instead of using NLS_LANG
for a client session. It is possible to change character sets if the new
character set is a STRICT SUPERSET of the old character set, without recreating
the database.
In this case, WE8ISO8859P1 is a strict superset of US7ASCII.
With that in mind, try the following -- CHANGE ON THE SERVER
====================
The syntax to do this is through the ALTER DATABASE statement --
ALTER DATABASE [] CHARACTER SET ;
ALTER DATABASE [] NATIONAL CHARACTER SET ;
To change the database character set, perform the following steps. Not all of them are absolutely necessary,
but they are recommended: SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET WE8ISO8859P1;
SQL> SHUTDOWN IMMEDIATE; -- or NORMALSQL> STARTUP;
If you want to try using NLS_LANG on the client --CHANGE ON THE CLIENT
====================
To change NLS_LANG for clients only, for example, using MS SQL Server,
this discusses how to do this for Windows :-
Use REGEDIT to modify the registry setting of NLS_LANG
(probably, HKEY_LOCAL_MACHINE\Software\Oracle). Do a "Find" through RegEdit on NLS_LANG
to make sure.)
The recommendation would be to use ALTER DATABASE since for the character
sets involved, it is a SAFE operation.


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


There are five approaches :-

1) Edit init.ora by changing :-
from NLS_CHARACTERSET=USASCII7
to NLS_CHARACTERSET=WE8ISO8859P1

Then Shutdown Oracle ...
svrmgrl>connect internal as sysdba
svrmgrl>shutdown immediate
Start-up Oracle ...
svrmgrl>connect internal as sysdba
svrmgrl>startup open DatabaseName

Check that this solves the problem, and if not, it may be necessary to re-Import the Database.

2)
svrmgrl>Connect as sys
svrmgrl>update props$
set value$='WE8ISO8859P1'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Be sure to take a cold backup of the database before this change, because if you set it to a Non-existing Character Set, the database will not start after shutdown.

3) Specify the Character Set as part of the Create Database
US7ASCII is the default 'character set' at database creation.
To change this add the line CHARACTER SET WE8ISO8859PI to the CREATE DATABASE command.
In fact, it is good practice to include this line in every create database.
After a Database has been created, it may be necessary to export, recreate the database and re-import it.
In which case, change the NLKS_CHARACTERSET,(equivalent to NLS_LANGUAGE), parameter values in the init.ora file before recreating the database.

4) Since we8iso8859p1 is a strict superset with us7ascii it is possible to alter the Character Set for a Database without recreating it.
This facility is undocumented in Oracle 8.0.5 but try looking on MetaLink for Technical Note :66320.1
(V8: Changing the database or national character set).
This is the command that is required :-
alter database character set we8iso8859p1;
Then do the shutdown and startup again.

5) Alternatively, ALTER SESSION to alter the Character Set for the Session.
Check settings at any time at different levels as follows :-
SELECT * FROM NLS_SESSION_PARAMETERS, NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.



6) Use this Character Set:

AMERICAN_AMERICA.WE8ISO8859P1

As per Oracle Documentation for Specifying Character Sets :

The character encoding scheme used by the database is defined at database creation as
part of the CREATE DATABASE statement.

All data columns of type CHAR, VARCHAR2, and LONG, including columns in the data dictionary,
have their data stored in the database character set.

In addition, the choice of character set determines which characters can name objects in the database.
Once the database is created, the database character set cannot be changed without re-creating the database.
Hence, it is important to consider carefully which character set to use.

The database character set should always be a superset or equivalent of the operating system’s
native character set.

The character sets used by client applications that access the database will usually determine
which superset is the best choice.

If all client applications use the same character set, then this is the normal choice for
the database character set.

When client applications use different character sets, the database character set should be a
superset (or equivalent) of all the client character sets.
This will ensure that every character is represented when converting from a client character set
to the database character set.

When a client application operates with a terminal that uses a different character set,
then the client application’s characters must be converted to the database character set, and vice versa.

This conversion is performed automatically, and is transparent to the client application.

The character set used by the client application is defined by the NLS_LANG parameter.

WE8ISO8859P1 ISO 8859 - 1 West European
EE8ISO8859P2 ISO 8859 - 2 East European
SE8ISO8859P3 ISO 8859 - 3 South European
NEE8ISO8859P4 ISO 8859 - 4 North and North-East European

Most NLS parameters can be used in three ways:

- As initialization parameters to specify language-dependent
behavior defaults for the server.
For example, in your INIT.ORA file, include
NLS_TERRITORY = FRANCE

- As environment variables on client machines to specify
language-dependant behavior defaults for a session. These
defaults override the defaults set for the server.
For example, on a UNIX system
setenv NLS_TERRITORY FRANCE

- As ALTER SESSION parameters to change the
language-dependent behavior of a session.
These parameters override the defaults set for the session or for the server.
For example:
ALTER SESSION SET NLS_TERRITORY = FRANCE

nice article from.. http://databaseanswers.org/ora_nls_support.htm

NLS_CHARACTERSET is a database parameter that declares what
character set encoding is used to store CHAR, VARCHAR2, LONG,
and CLOB datatypes (including any SQL and PL/SQL source,
which is internally stored and processed more or less as VARCHAR2).
This parameter can be changed only in limited circumstances.

NLS_LANG is a client-side parameter that declares the character
set encoding of text that OCI gets from applications or sends to them
as content of character bind/define variables or as [PL/]SQL statements
to be executed by the DB server.
The NLS_LANG value can be overridden internally by OCI applications.

As most Oracle tools are OCI-based, NLS_LANG declares the character
set encoding in which they work, display error messages, accept
parameter files, etc.

NLS_LANG defines also language and territory conventions to be used
by many Oracle client tools and by database sessions.

Both parameters are crucial for correct working of DB in internationalized
environment. Changing one of them does not influence the other directly
but it does influence the communication between client and server
and the character set conversion that Oracle performs based
on what it thinks an application wants.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/whatsnew.htm

Friday, March 18, 2011

Oracle Clusterware Components

Oracle Clusterware is the software, which enables the nodes to communicate with each other, and forms the cluster and makes the nodes as single logical server. Oracle Clusterware is run by Cluster Ready Services (CRS) using two key components. They are Oracle Cluster Registryvoting disk, which acts a tiebreaker during communication failures. Consistent heartbeat information from all the nodes is sent to voting disk when the cluster is running. CRS service has four components namely OPROCd, CRS Daemon (crsd), Oracle Cluster Synchronization Service Daemon (OCSSD) and Event Volume Manager Daemon (evmd) and each handles a variety of functions. Failure or death of the CRS daemon can cause the node failure and it automatically reboots the nodes to avoid the data corruption because of the possible communication failure between the nodes. The CRS daemon runs as the super userUNIX platforms and runs as a service in the windows platforms. (OCR), which records and maintains the cluster and node membership information. The other component is ‘root’ in the

The following functionalities are covered by Oracle Cluster Ready Services

•CRS is installed and run from a different oracle home known as ORA CRS HOME, which is independent from ORACLE HOME.

CRSD --


•CRSd manages the resources like starting and stopping the services and failing-over the application resources. It spawns separate processes to manage application resources.
•CRS daemon has two modes of running. During startup and after a shutdown. During planned clusterware start it is started as ‘reboot’ mode. It is started as ‘restart’ mode after unplanned shutdown.
•In reboot mode it ‘auto’ starts all the resources under its management. In restart mode it prevails the previous state and brings back the resources to it previous state before shutdown
•Manages the Oracle Cluster Registry and stores the current known state in the Oracle Cluster Registry
•Runs as ‘root’ on Unix and ‘LocalSystem’ on windows and automatically restarts in case of failure.
•CRS requires the public interface, private interface and the Virtual IP (VIP) for the operation. All these interfaces should be up and running, should be able to ping each other before starting CRS Installation. Without the above network infrastructure CRS cannot be installed.

OCSSD ---


Oracle Cluster Synchronization Services Daemon (ocssd) is the component, which provides the synchronization services between the nodes. OCSSD provides the access to the node membership. It also enables basic cluster services including cluster group services and cluster locking. It can also run without integration with vendor clusterware. Failure of ocssd causes the machine to reboot to avoid split-brain situation. This is also required in a single instance configuration if Automatic Storage Management (ASM) is used. Automatic Storage management was a new feature in oracle database 10g. Ocssd runs as ‘oracle’ user. The following functionalities are covered by CSS daemon

•CSS provides basic ‘group services’ support. Group Services is a distributed group membership system that allows the applications to coordinate activities to achieve a common result.
•’Group services’ use vendor clusterware group services when vendor clusterware is available. But it is capable of working independently if there is no vendor clusterware group services available
•‘Lock services’ is another service from the CSS daemon. Lock services provide the basic cluster wide serialization locking functions. It uses FIFO mechanism to manage locking
•Node Services is the third service produced by the CSSD. It uses OCR to store the data and updates the information during reconfiguration. It also manages the OCR data, which is static otherwise.

EVMD --

The third component in OCS is called Event Management Logger. Event Management logger also runs as daemon process ‘evmd’. The daemon process ‘evmd’ spawns a permanent child process called ‘evmlogger’ and generates the events when things happen. EVMD child process ‘evmlogger’ spawns new children processes on demand and scans the callout directory to invoke callouts. It will restart automatically on failures and death of the evmd process does not halt the instance. Evmd runs as ‘oracle’ user.

OPROCD--


Oprocd provides the server fencing solution for the Oracle Clusterware. It is the process monitor for the oracle clusterware and it uses the hang check timer or watchdog timer (depending on the implementation) for the cluster integrity. Oprocd is locked in the memory and runs as a real time process. This sleeps for a fixed time and runs as ‘root’ user. Failure of the Oprocd process causes the node to restart.

Monday, March 14, 2011

ASSM

ASSM (Automatic Segment Space Management) is a method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters like PCTUSED, Freelists and Freelist groups for objects created in the tablespace.
ASSM was first introduced with Oracle 9i. Starting with 10g Release 2, ASSM will be enabled by default when you create a new tablespace.

Free space tracking
Instead of trying to track the exact space inside a block (with a freelist), Oracle marks the block as one of six types, tracked by a bitmap. The 6 different "freeness statuses" are:
0 = unformatted
1 = logically full
2 = 0-25% free
3 = 25-50% free
4 = 50%-75% free
5 = 75-100% free
Example
Create an ASSM managed tablespace by specifying "SEGMENT SPACE MANAGEMENT AUTO":
CREATE TABLESPACE ts1
DATAFILE '/app/orafata/ts1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL -- Enable LMT
SEGMENT SPACE MANAGEMENT AUTO -- Enable ASSM
/
Test
Here is a simple test to see if a tablesapce is freelist or ASSM managed:
SQL> SELECT tablespace_name, extent_management, segment_space_management
2 FROM dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
SYSTEM LOCAL MANUAL
SYSAUX LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
EXAMPLE LOCAL AUTO

6 rows selected.

and x$table list below :

http://www.orafaq.com/wiki/X%24_Table

LMT Vs DMT

Locally Managed Tablespace (LMT) is one of the key features in Oracle database. These have been made available since Oracle 8i. It is worth using LMTs considering the benefits in doing so. I have put forward some scenarios that may be worth noting, for systems that are already using LMTs or planning to shift to LMTs.

Benefits of LMTs
Below are the key benefits offered by LMTs. Not all are achievable when migrating to LMTs.

Dictionary contention is reduced.

Extent management in DMTs is maintained and carried out at the data dictionary level. This requires exclusive locks on dictionary tables. Heavy data processing that results in extent allocation/deallocation may sometimes result in contentions in the dictionary.

Extents are managed at the datafile level in LMTs. Dictionary tables are no longer used for storing extent allocation/deallocation information. The only information still maintained in the dictionary for LMTs is the tablespace quota for users.

Space wastage removed.

In DMTs, there is no implied mechanism to enforce uniform extent sizes. The extent sizes may vary depending on the storage clause provided at the object level or the tablespace level, resulting in space wastage and fragmentation.

Oracle enforces the uniform extents allocation in the LMTs (when created with UNIFORM SIZE clause). Space wastage is removed, as this would result in all the same sized extents in the tablespace.

No Rollback generated.

In DMTs, all extent allocations and deallocations are recorded in the data dictionary. This generates undo information thus using vital resources and may compete with other processes.

In LMTs, no rollback is generated for space allocation and deallocation activities.

ST enqueue contention reduced.

In DMTs, Space Transaction (ST) enqueue is acquired when there is a need for extent allocations in DMTs. It is also exclusively acquired by SMON process for coalescing free space in DMTs. Only one such enqueue exists per instance, and may sometimes result in contention and performance issues if heavy extent processing is being carried out. The following error is common in such scenario.

ORA-01575: timeout warning for space management resource
As ST enqueue is not used by LMTs it reduces the overall ST enqueue contention.

Recursive space management operations removed.

In DMTs, SMON process wakes up every 5 minutes for coalescing free space in DMTs. Optionally, the ALTER TABLESPACE COALESCE command is also used to coalesce DMTs and reduce fragmentation.

On the other hand, LMTs avoid recursive space management operations and automatically track adjacent free space, thus eliminating the need to coalesce free extents. This further reduces fragmentation.

Fragmentation reduced.

Fragmentation is reduced in LMTs but not completely eliminated. Since adjacent free spaces are automatically tracked, there is no need to do coalescing, as is required in the case of DMTs.

Management of Extents in LMTs
Oracle maintains a bitmap in each datafile to track used and free space availability in an LMT. The initial blocks in the datafiles are allocated as File Space Bitmap blocks to maintain the extent allocation information present in the datafile. Each bit stored in the bitmap corresponds to a block or a group of blocks. Whenever the extents are allocated or freed, oracle changes the bitmap values to reflect the new status. Such updates in the bitmap header do not generate any rollback information.

The number of blocks that a bit represents in a bitmap depends on the database block size and the uniform extent size allocated to the tablespace. For example, if the DB_BLOCK_SIZE parameter is set to 8K, and the tablespace is created with uniform extent sizing of 64K, then 1 bit will map to one 64K extent, i.e., 64K (extent size)/8K (block size) = 8 database blocks.

......

LOCALLY MANAGED TABLESPACES IN ORACLE

Introduction

Traditionally, up to Oracle8 Release 8.0.5 the management of free and used extents of a tablespace was done in the data dictionary. These “traditional” tablespaces are also called “Dictionary Managed Tablespaces”, in order to differentiate them from a new type of table space called Locally Managed Tablespaces introduced in Oracle8i Release 8.1.5. All extent information in Locally Managed Tablespaces is tracked by bitmaps in the datafiles of a tablespace.

Dictionary Managed Tablespaces

In the traditional or Dictionary Managed Tablespaces, the data dictionary does extent management. When extents are allocated or freed, data dictionary tables are updated and rollback information about each dictionary table update is maintained. All data dictionary operations are subject to the same space management considerations as any other “user” space maintenance – for example, rollback activity due to updates of these dictionary tables could cause the rollback segment to extend causing more space management operations. This was the only option available before release 8.1.5.

Locally Managed Tablespaces

Bitmaps manage space allocation very efficiently, and require no dictionary access to allocate an extent to an object. In Locally Managed Tablespaces, bitmaps provide extent management. When extents are allocated or freed, a bitmap is updated to indicate the freed or used status of blocks in a datafile. Each datafile in a locally managed tablespace has its own bitmap. Each bit in the bitmap tracks a block or a group of blocks in an extent. When an extent is allocated or freed, the bitmap is changed to reflect the new status of these blocks in that extent. These bitmap changes do not generate rollback information for these “space management” operations. This is a new option available from Oracle8i Release 8.1.5.

Extent Allocation in Locally Managed Tablespaces

The LOCAL clause of the EXTENT MANAGEMENT clause specifies this method of space management in the tablespace CREATE statement.

Extents in Locally Managed Tablespaces can be created specifying either

UNIFORM SIZE - where all extents are the same size, or, AUTOALLOCATE - where extents are automatically sized by the system at tablespace creation time.

If AUTOALLOCATE or UNIFORM is not specified, then AUTOALLOCATE is the default.

Sunday, March 13, 2011

Remote listener Vs Local Listener

the remote_listener parameter points to your SCAN listener(s) which will do the load balancing, and local_listener points to the node listener which will do the spawn-and-bequeath.

Starting with Oracle 11g Release 2 Oracle introduced a new cluster database connection concept: SCAN – “Single Client Access Name”.
SCAN on the server side
When installing a 11g Release 2 grid infrastructure you are asked for the cluster name which will be part of the SCAN. The notation for the SCAN is:

-scan.For instance if your cluster is named “rac” and the domain “regner.de” the SCAN name will be “rac-scan.regner.de”.

In order to successful install grid infrastructure you need to configure your DNS (hosts file entries will not work!) prior installing grid infrastructure to resolve the name accordingly. Oracle requires at least one, better three IPs configured for the scan name. Your DNS zone file might look like this:



In the example zone file above we configured three IPs for the scan: 172.23.15.3, 172.23.15.4 and 172.23.15.5.

ora doc:

http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf

Oracle SIDs Vs Oracle SERVICE NAMES

What is the difference between Oracle SIDs and Oracle SERVICE NAMES?

Oracle SID is the unique name that uniquely identifies your instance/database where as Service name is the TNS alias that you give when you remotely connect to your database and this Service name is recorded in Tnsnames.ora file on your clients and it can be the same as SID and you can also give it any other name you want.

SERVICE_NAME is the new feature from oracle 8i onwards in which database can register itself with listener. If database is registered with listener in this way then you can use SERVICE_NAME parameter in tnsnames.ora otherwise - use SID in tnsnames.ora.

Also if you have OPS (RAC) you will have different SERVICE_NAME for each instance.

SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can specify multiple services names in order to distinguish among different uses of the same database. For example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

You can also use service names to identify a single service that is available from two different databases through the use of replication.

In an Oracle Parallel Server environment, you must set this parameter for every instance.

In short: SID = the unique name of your DB, ServiceName = the alias used when connecting

db_name - This is created when you issue your "create database" statement and it can be seen in the v$database view.

SQL> CREATE DATABASE mysid DATAFILE SIZE 400M
2> DEFAULT TEMPORARY TABLESPACE temp_ts
3> TEMPFILE SIZE 10M
4> UNDO TABLESPACE undo_ts DATAFILE SIZE 10M;

select
name,
value
from
v$parameter
where
name = 'db_name';

NAME VALUE
------- ----------------------------------------------
db_name mysid

instance_name - In the init.ora file

init.ora
INSTANCE_NAME=mysid

select
instance_number,
instance_name,
host_name
from
v$instance

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- ----------------------1 mysid MYSERVER

service_name - The service_name is in the tnsnames.ora file to allow you to register an instance with the listener:

mysid=
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = myserver)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mysid)
)
)


sid_name - We find the sid_name values listed in the listener.ora file:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = mysid)
(ORACLE_HOME = /u01/app/oracle)
)
(SID_DESC =
(GLOBAL_DBNAME = remote.dba.net)
(ORACLE_HOME =/u01/app/oracle/product/10.1.2)
(SID_NAME = mysid)
)
)
)



global_name - If you are using global_names (global_names = TRUE), the global_name is the combination of the db_name and db_domain:

select *
from
global_name;

GLOBAL_NAME
---------------------------------------
MYSID.REMOTE.DBA.NET



A service name is more flexible than a SID would be.


A database can dynamically register with a listener using one or more service names. In fact, more than one database can register with a listener using the same service name (think about a clustered environment where you have multiple instances that all are the same database under the covers).

A database on the other hand has a single SID. And a single SID goes to a single database. It is a pure 1:1 relationship.

A service is a many to many relationship.


Service names are used with dynamic registration - the data registers with the listener after it starts up. Once it does that, you can connect.


With the SID - that is more like telling the listener "I want you to connect to this specific database, I know the 'address', here you go"

With the SERVICE - you are asking the listener to put you in touch with a database that can service your request, a database that registers using that service.

---

Service registration offers the following benefits:

Simplified configuration

Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.


Note:

The SID_LIST_listener_name parameter is still required if you are using Oracle Enterprise Manager to manage the database.


Connect-time failover

Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

Connection load balancing

Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.

Resumable_timeout

RESUMABLE_TIMEOUT is new parameter in 10g which allows DBAs to enable/disable resumable statements and specify resumable timeout at the system level. In 9i this parameter could only be set on session level.
Sometimes DBAs does not know how much space is required for UNDO,TEMP or datafile to complete a big job.By setting this parameter, any space issue does not break the job.It reports an error in Alert log and the job automatically resumes once DBA has fixed the issue.

Here is a test scenario from Metalink :

1. Make the datafile autoextend off

2. Create a big table on the tablespace where the datafile is autoextend off

3. Insert rows into the table until it exceeds the space required by the INSERT operation.

4. Monitor the alert.log:
you find the following info:
statement in resumable session 'User SYS(0), Session 17, Instance 1' was suspended due to ORA-01653: unable to extend table SYS.T1 by 100 in tablespace TEST1

5. Check the table WRI$_ALERT_OUTSTANDING or view DBA_OUTSTANDING_ALERTS : SQL> select decode(message_level, 5, 'WARNING', 1, 'CRITICAL') alert_level, reason from dba_outstanding_alerts where reason like '%resumable%';

6. Modify the datafile autoextend to ON and check the alert.log.


from... nice articles below :

http://oradbatips.blogspot.com/2007/02/tip-24-resumabletimeout-in-10g.html

http://www.dbapool.com/articles/122705.html