Oracle/Database/Notes

Java

Oracle - Oracle JDBC Frequently Asked Questions

Oracle JDBC Drivers release 11.1.0.6.0 Downloads

TNS Listener

TNS Listener - show services

/opt/oracle/product/11.1/db_1/bin/lsnrctl services

If you see this, then you are good to go:

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-NOV-2009 03:37:50

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: rhel53x86, pid: 9159>
         (ADDRESS=(PROTOCOL=tcp)(HOST=rhel53x86)(PORT=59832))
Service "orcl_XPT" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

If you see this, then your database is not started:

LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 04-NOV-2009 03:33:47

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

SQL*Plus:

/opt/oracle/product/11.1/db_1/bin/sqlplus

To start the database:

sqlplus / as sysdba
> conn sys/[PASSWORD] as sysdba
> startup

Remove IPC (Inter Process Communication - ie Named Pipes) from:

/opt/oracle/product/11.1/db_1/network/admin/listener.ora

To stop and start the TNS Listener:

/opt/oracle/product/11.1/db_1/bin/lsnrctl stop
/opt/oracle/product/11.1/db_1/bin/lsnrctl start

Issues

automount messages

If you are seeing this in the /var/log/messages:

Jan 30 03:32:10 ws-194-120 automount[3108]: create_udp_client: hostname lookup failed: Operation not permitted
Jan 30 03:32:10 ws-194-120 automount[3108]: create_tcp_client: hostname lookup failed: Operation not permitted
Jan 30 03:32:10 ws-194-120 automount[3108]: lookup_mount: exports lookup failed for stawj12

Turn off the autofs service: [1]

service autofs stop

queue size

so it works with the settings at # of processes 20,000 and the QUEUESIZE = 2,000

QUEUESIZE is changed in 2 files:

$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/tnsnames.ora

Broken Oracle

See Broken Oracle

Auto mount and open broken

See Oracle - Why won't the mount and open happen automatically

Other Notes

[ORACLE-BASE - Oracle 11g Release 1 RAC On Linux Using NFS http://www.oracle-base.com/articles]:/11g/OracleDB11gR1RACInstallationOnLinuxUsingNFS.php

$ sqlplus / as sysdba
SQL> CONN sys/password@rac1 AS SYSDBA

OraFAQ Forum: Networking and Gateways => How to configure listener.ora:

tnsping newDB

Edit /etc/oratab and add 'Y' to get it to auto start with dbstart

/etc/oratab:

#



# This file is used by ORACLE utilities.  It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively.  The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
orcl:/opt/oracle/product/11.1/db_1:Y

Listener list:

/opt/oracle/product/11.1/db_1/network/admin/listener.ora

Killing sessions:

• ORACLE-BASE - Killing Oracle Sessions
• ORACLE - DBA Tips Corner

killall -9 oracle

OTN Discussion Forums : ORA-27101: shared memory realm does not ...:

When Ever you try to connect DB via Java Program or SQLPlus following error will ap is found:

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory

Solution :
You need to Manually startup the database

check ORACLE_SID has value

sqlplus "sys/password as sysdba"
startup;
Connected to an idle instance.

SQL> startORACLE instance started.
SQL> exit

$ export ORACLE_SID=ANOTHER_SID
$ sqlplus "sys/password as sysdba"

SQL> startup;
SQL> exit

$ export ORACLE_SID=jcrdb
$ sqlplus "sys/password as sysdba"
SQL> startup;
SQL> exit

Thanks
--Nazeer

Oracle - Configuring and Administering Oracle Net Listener

"Oracle Net Listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server. This chapter describes how to configure the listener to accept client connections."

This chapter includes the following topics:

• Oracle Net Listener Configuration Overview
• Oracle Net Listener Configuration During Installation
• Customizing Oracle Net Listener Configuration
• Configuring Service Registration
• Listener Administration

• Oracle - 10 Configuring and Administering the Listener
• Oracle - Configuring and Administering the Listener

Oracle Listener Service Connect:

set an encrypted listener password

c:\oracle\product\ora102\bin> lsnrctl start
LSNRCTL> change_password
Old password:
New password:
Reenter new password:
LSNRCTL> save_config

kill on *NIX

ps -ef|grep tnslsnr|awk '{ print $2; }'|xargs kill -9

view the current listener services

c:\oracle\product\ora10\bin> lsnrctl services

start the listener

c:\oracle\product\ora10\bin> lsnrctl start

status view the current listener status

c:\oracle\product\ora10\bin> lsnrctl status

stop stop the listener

c:\oracle\product\ora10\bin> lsnrctl stop

set

...

"ORA-12519, TNS:no appropriate service handler found"

ORA-12519, TNS:no appropriate service handler found - NewInstance:

"Googling around I found that this is a bug on how OracleXE monitors processes, and issuing the following command at SQL command line will fix it (after restarting the listener): “ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;”.

Worked for me. This saved me to install Oracle Enterprise on my laptop."

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE
#and then restart the db.

Show current number of processes: [2]

show parameters processes; 
alter system set processes = 100 scope=spfile; 

OTN Discussion Forums : ALTER SYSTEM SET processes=1000 ...:

I have exactly the same problem. I've changed PROCESS parameter to 3000 and Oracle does not start anymore. How can I change the parameter back to the previous value if Oracle is not started?

Solution:

open the initSID.ora file from
$ORACLE_HOME/database (on windows)
$ORACLE_HOME/dbs (on unix)
and change the parameter

processes=100 (your required value)

then try to start the instance.

If there is no such parameter in initSID.ora, then you are using spfile. Do the following:
set ORACLE_SID=YOUR_SID
sqlplus "/as sysdba"
create pfile from spfile; -- without starting the instance
edit the pfile to change the processes parameter, delete spfile
startup nomount
create spfile from pfile
startup force

check if your database is running or not on unix enter this command
ps -ef|grep smon

if it show you smon_YOURDBSID then you db is running.

DZone Snippets - Change sessions, transaction and processes parameters in Oracle 10g

alter system set PROCESSES=100 scope=SPFILE;
alter system set TRANSACTIONS=126 scope=SPFILE;
alter system set SESSIONS=115 scope=SPFILE;

amm_setup.sql

drop tablespace tbssga including contents and datafiles;

create tablespace tbssga datafile 'tbssga12.dbf' size 20m;

drop tablespace mytemp including contents and datafiles;

create temporary tablespace mytemp tempfile 'temp12.f' size 40m reuse;

drop user amm cascade;

create user amm identified by amm default tablespace tbssga temporary tablespace mytemp;

grant connect,resource,dba to amm;

select substr(COMPONENT, 0, 10) COMP, CURRENT_SIZE CS, USER_SPECIFIED_SIZE US from v$memory_dynamic_components where CURRENT_SIZE!=0;

connect amm/amm

drop table tabsga purge;

create table tabsga(a number, b number) tablespace tbssga;

begin
  for i in 1..100000 loop
    insert into tabsga values (i, i);
  end loop;
end;
/

commit;

alter table tabsga parallel 64;

create or replace procedure testpga( psize number ) as
begin
declare
  TYPE nAllotment_tabtyp    IS TABLE OF char(2048) INDEX BY BINARY_INTEGER;
  myarray nAllotment_tabtyp;
begin
  for i in 1..psize loop
    myarray(i) := to_char(i);
  end loop;
end;
end;
/

show errors

select substr(COMPONENT, 0, 10) COMP, CURRENT_SIZE CS, USER_SPECIFIED_SIZE US from v$memory_dynamic_components where CURRENT_SIZE!=0;

connect / as sysdba

alter system set "_PX_use_large_pool" = TRUE SCOPE=SPFILE;

alter system set "_memory_broker_stat_interval" = 5 SCOPE=SPFILE;

alter system set "_memory_management_tracing" = 31 SCOPE=SPFILE;

alter system set "parallel_execution_message_size" = 36864 SCOPE=SPFILE;

alter system set "parallel_max_servers" = 200 SCOPE=SPFILE;

alter system set "parallel_adaptive_multi_user" = FALSE SCOPE=SPFILE;

alter system set "processes" = 200 SCOPE=SPFILE;

alter system set "pga_aggregate_target" = 0 SCOPE=SPFILE;

alter system set "sga_target" = 0 SCOPE=SPFILE;

alter system set "memory_target" = 280M SCOPE=SPFILE;

shutdown immediate;

startup;

QUEUESIZE

How big should the Listener queue size be? : Oaracle, Oracle, 10g, Windows 2000

http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10775/listenercfg.htm#i483130

Handling Large Volumes of Concurrent Connection Requests
If you expect the listener to handle large volumes of concurrent connection requests, then you can specify a listener queue size for its TCP/IP or IPC listening endpoints. To specify the listener queue size, specify the QUEUESIZE parameter at the end of the protocol address with its value set to the expected number of concurrent requests. For example:

Example 10-2 listener.ora File with Queue Size

LISTENER=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)(QUEUESIZE=20)))
Note:

The default number of concurrent connection requests is operating-system specific. The defaults for TCP/IP on the Solaris Operating System and Windows follow:

Solaris Operating System: 5
Windows NT 4.0 Workstation: 5
Windows NT 4.0 Server: 50

Oracle Configuration Parameters : oracle, queuesize

Using MS MTS to access oracle:

try to set these parameter to a larger value, considering you have 100+ users...

You need to set these values in INIT<SID>.ORA FILE on the database server:


sessions = 600
distributed_transactions = 600
open_cursors =1000
processes =600


You need to restart the database by shutdown immediate so that these changes will take effect.


you also need to set the QUEUESIZE in the LISTENER.ORA file on the database server

QUEUESIZE = 150


and then restart the listener process
by

lsnrctl stop
lsnrctl start

Database management: Optimizing your Oracle*Net configuration

The queuesize parameter in listener.ora

The number of requests the listener can store while Oracle is working to establish a connection is determined by the undocumented queuesize parameter. This parameter is used only for very high-volume databases, where the listener spawns thousands of connections per hour. The number of expected simultaneous connections should be equal to the size of the queuesize parameter. Here's an example of this parameter in the listener.ora file:

  LISTENER =
    (ADDRESS_LIST =
          (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = marvin)
            (PORT = 1521)
            (QUEUESIZE = 32)
          )
    )

A disadvantage of this parameter is that it preallocates resources for anticipated requests, therefore using more system memory and resources. You may want to consider using MTS and prespawned Oracle connections if you have high-volume connections into a dedicated listener. Also, note that some versions of UNIX do not allow queues greater than five, and there are some restrictions of the MTS queue size.

Oracle Hardware Design queuesize listener.ora Connection Pooling Network Performance:

Do not use automatic service registration. Set the init.ora parameter, local_listener, to use a different TCP port than the one defined in your listener.ora file.

The queuesize parameter in listener.ora

If it is expected that the listener will receive large numbers of requests for connection, a queue may be specified for the process. This enables the listener to handle larger numbers of simultaneous connection requests. The number of requests the listener can store while Oracle works to establish a connection is specified by the queuesize parameter. The value of this parameter should be equivalent to the number of expected simultaneous connections. Below is an example of the queuesize parameter in the listener.ora file:

  LISTENER =
    (ADDRESS_LIST =
          (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = marvin)
            (PORT = 1521)
            (QUEUESIZE = 32)
          )
    )

Use of queuesize can be disadvantageous since more resources and memory is used. The parameter pre-allocates resources for anticipated connection requests. For this reason, if high-volume connections into a dedicated listener are anticipated, it may be beneficial to implement the Multi-Threaded Server (MTS) and use pre-spawned Oracle connections.

Cluster Testing Notes

Connect with sqlplus

Using SQL*PLUS to connect to a Oracle database:

CONN[ECT] <user>/<password>@<connect string>

Connect strinng is the Oracle instance name

Sample:

SQL> CONNECT SYSTEM/MANEDJ@EDJ
Connected.

Peoplecnc.com - How connect to a Oracle Database using sql*plus

Show instance status

Connected to an idle instance:

SQL> select status from v$instance;

You can also check oracle instance from UNIX command line by running:

#ps -aef |grep pmon

News: How to check if Oracle instance is up or down ?

Check Oracle instance from UNIX/Linux command line

hpuxdev# ps -aef |grep ora_ |grep -v grep

News: How to check if Oracle instance is up or down ?

List tablespaces

How to list the Oracle Tablespaces

SQL> select * from v$tablespace;

To list the free space for all Oracle Tablespaces within a Database

SQL> select * from dba_free_space;

To list the free space for a specific Oracle TablespaceDATABASE>'

SQL> select * from dba_free_space where TABLESPACE_NAME = 'IATOR';

To list the space ALREADY USED for a specific Oracle Tablespace

SQL> select * from DBA_SEGMENTS where TABLESPACE_NAME = 'mytablespace';

Oracle: How to list extents for segments?

SQL> select tablespace_name, count(*), max(blocks), sum(blocks) from dba_free_space group by tablespace_name;

Peoplecnc.com - How to list the Oracle Tablespaces and free space

List instances

select instance_name,status from gv$instance;

OTN Discussion Forums : oracle instance status check ...

Drop tablespace

Drop tablespace useless;
Drop tablespace useless including contents;
Drop tablespace useless including contents and datafiles;

Oracle DROP TABLESPACE tips

Oracle Concepts - Administering Tablespaces

Drop bad tablespace (forced)

"Accident may happens, even though it’s costly one. This is especially the case in Oracle database, which stores the database objects physically in data files on hard disk. Fast hand administrators may accidentally delete some of the datafiles, or the datafiles may get corrupted or unreadable on hard disk failure. You may get also some missing datafiles after database recovery process. In any case, missing a or a few datafiles will cause Oracle database to fail to startup, rendering the whole DB inaccessible.

The solution to the missing datafiles is to drop the affected tablespace where has incomplete datafiles, and then recreate the tablespace and import the data into the tablespace from backup. However, the steps are not so straight forward."

1. Run SQL*Plus.

2. Connect to database as SYSDBA with this query:

CONNECT / AS SYSDBA

3. Mount the database instead of starting it up:

STARTUP MOUNT;

4. Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:

ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;

Repeat the command for every datafiles that unaccounted for.

5. Now start the database proper:

ALTER DATABASE OPEN;

6. As the tablespace has damaged, drop it to recreate from fresh backup.

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

7. Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.

Tip: How to check datafiles that belong to tablespace.

8. Continue with the recovery process.

Source: How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile » My Digital Life

ORA-00257: archiver error

ORA-00257: archiver error. Connect internal only, until fr...

ORA-00257: archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message is the destination device is out of space to store the redo log file.

Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.

Oracle STATSPACK installation steps

"The STATSPACK utility requires an isolated tablespace to contain all of the objects and data. For uniformity, it is suggested that the tablespace be called perfstat, the same name as the schema owner for the STATSPACK tables. Note that I have deliberately not used the AUTOEXTEND option. It is important for the Oracle DBA to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space. We will talk about adjusting the STATSPACK thresholds later in this chapter."

we create a tablespace called perfstat with at least 180 megabytes of space in the datafile:

SQL> create tablespace perfstat 
   datafile '/u03/oradata/prodb1/perfstat.dbf'
   size 500m;

Source: Oracle STATSPACK installation steps

Oracle RAC

See Oracle RAC

keywords

oracle