Common Oracle TNS Errors

In this post , I am trying to cover common TNS errors and possible corrective measures.

ORA-12154: TNS:could not resolve service name

SQL> connect SYSTEM/manager@DEV
ERROR:
ORA-12154: TNS:could not resolve service name

The most common TNS error mostly due to multiple ORACLE HOME, tnsnames.ora and sqlnet.ora conflicts.

  • Check that the instance or database name is being typed in correctly. Check ORACLE_SID,ORACLE_HOME
  • Make sure listener is up and running
  • lsnrctl > status           if down then

    lsnrctl > start

  • PING the server to see if network is up
  • ping 168.192.1.22

  • TNSPING the server and then try connecting
  • tnsping DEV

  • Check the tns names.ora file to validate that the instance name has been entered correctly. Make sure that all entries are properly terminated with parenthesis.
  • Validate the domain name entry in sqlnet.ora is not conflicting with the full database name resolution.
  • If nothings works, delete and recreate the entry to eliminate any character or control character issues.

For more details on ORA-12154 causes and actions

 

TNS-03505: Failed to resolve name

Message 3511 not found; product=network; facility=TNS
TNS-03505: Failed to resolve name

This is quite similar to the above error so you have to again check the environmental variables like ORACLE_HOME and PATH and make sure their values are set correctly.
The issue usually occurs when the tns entiries we provide has some unwanted characters or spaces etc.Please also check the tns entry(alias) is pointing to the correct database with sid hostname.

For more details on TNS-03505 reasons and remedy actions

ORA-12537: TNS: connection closed error

It happens if the connection has been closed most likely due to the following reasons:

  • Firewall has disconnected idle connections
  • Configuration problem in sqlnet.ora or listener.ora
  • Time out occurred on client connection
  • Path name that is too long for the Oracle TNS client on windows

Try the following to fix this issue:

  1. Install the exe in a shorter named directory.
  2. Modify the TNS Names entry so that it is shorter.
  3. Replace domain names with IP Addresses if that shortens and vice versa.
  4. Remove any parameters from the entry that are not needed.
  5. Patch the Database Listener to the most recent version

For more details on ORA-12537 reasons and remedy actions

 

Troubleshooting Tips from the Field for Oracle Net Services

Here are some tips you may find helpful when you are having difficulty diagnosing network problems from Oracle documentation:

  • Use the node or network address during configuration instead of the name of the server computer

    This eliminates any internal lookup problems and make the connection slightly faster.

  • If you are using TCP/IP addresses, use the IP address rather than host name

    For example, change the (HOST=server_name) line in the tnsnames.ora file with the internet address, for example (HOST=198.32.3.5).

  • Perform a loopback test

    Perform a loopback test on the server as described in “Testing Configuration on the Database Server”. If the test passes, ftp the tnsnames.ora and sqlnet.ora files to the client.

  • Check what is between you and the server

    If it is a wide area network (WAN), identify any intermediate systems that may not work correctly. If all computers are fine, the problem may be a timing issue.

  • Verify whether or not there is a timing issue

    Timing issues are associated with an ORA-12535 error in the client log files.

    To resolve this, try speeding up the connection by using exact addresses instead of names and increase the INBOUND_CONNECT_TIMEOUT_listener_name parameter in the listener.ora file. The default value for this parameter is 10 seconds.

  • Determine which Oracle applications are failing

    SQL*Plus may work, but CASE tools may not. If you determine the problem is a data volume issue, try to transfer a large (5 MB) file with the base connectivity.

 

Resolving most common Error messages for Oracle Net Services

 

Advertisements

Oracle Parameter Files

Parameter Files

Oracle must read either an initialization parameter file or a server
parameter file(SPFILE) to start an instance. These files contain list of initialization parameters and a value for each parameter.

In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the following order:

  • spfile$ORACLE_SID.ora
  • spfile.ora
  • init$ORACLE_SID.ora

Initialization Parameters

 

Initialization parameters are divided into two groups: basic and advanced. In the majority of cases, it is necessary to set and tune only the basic parameters to get reasonable performance. In rare situations, modification to the advanced parameters may be needed for optimal performance.

Complete List of Initialization Parameters (10g)

Basic Parameters

There are about 29 basic parameters. Only a few of these parameters must be explicitly set because the default values are adequate in the majority of cases.

DB_DOMAIN NLS_LANGUAGE
DB_NAME , DB_UNIQUE_NAME NLS_TERRITORY
DB_RECOVERY_FILE_DEST OPEN_CURSORS
DB_RECOVERY_FILE_DEST_SIZE PGA_AGGREGATE_TARGET
DB_CREATE_ONLINE_LOG_DEST_n PROCESSES
COMPATIBLE REMOTE_LISTENER
CONTROL_FILES REMOTE_LOGIN_PASSWORDFILE
CLUSTER_DATABASE ROLLBACK_SEGMENTS
DB_BLOCK_SIZE SESSIONS
DB_CREATE_FILE_DEST SGA_TARGET
INSTANCE_NUMBER SHARED_SERVERS
JOB_QUEUE_PROCESSES STAR_TRANSFORMATION_ENABLED
LOG_ARCHIVE_DEST_n UNDO_MANAGEMENT
LOG_ARCHIVE_DEST_STATE_n UNDO_TABLESPACE

  

Advanced Parameters

Other than basic parameters, several parameters are used for database tuning and other advance services. Few examples of parameters

SESSION_CACHED_CURSORS
CURSOR_SHARING
DB_KEEP_CACHE_SIZE
OPEN_LINKS
AUDIT_FILE_DEST
JAVA_POOL_SIZE
LARGE_POOL_SIZE
STREAMS_POOL_SIZE
QUERY_REWRITE_ENABLED
 

Hidden/Undocumented Parameters

Hidden parameters should only be used as the last resort and preferably when advised by Oracle Support.

_abort_recovery_on_join

_alert_message_purge

_backup_io_pool_size

_cursor_plan_enabled

_log_checkpoint_recovery_check

Complete List of Hidden Parameters(10g)

 

Viewing and Changing Parameters Settings

  • SHOW PARAMETERS command from SQL*PLUS can be used to to view parameter values
    • SQL> SHOW PARAMTERS CONTROL_FILES

  • V$PARAMETER, V$PARAMETER2 views is used to display current parameter values
  • V$SPPARAMETER view is used to display contents of server parameter file.

CHANGING PARAMETER VALUES 

  PFILE can be edited with any text editor but the SPFILE is a binary file and its parameter values can be changed using “ALTER SYSTEM SET” and “ALTER SYSTEM RESET”commands.

Removing a Parameter from SPFILE: 

SQL> ALTER SYSTEM RESET timed_statistics SCOPE=SPFILE SID=‘*’;

 

Parameter Setting to SPFILE / Memory/ Both

SQL> ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE;

SQL> ALTER SYSTEM SET timed_statistics=TRUE SCOPE=BOTH SID='*';

 

The scope parameter can be set to SPFILE, MEMORY or BOTH. Memory is default if PFILE was used at STARTUP and BOTH is default if SPFILE was used at STARTUP.

  • MEMORY:  The parameter value is set for the current instance only.
  • SPFILE: The parameter value is updated in SPFILE and takes effect at next STARTUP.
  • BOTH: The parameter value is set for current instance and persists to the SPFILE


Oracle Shutdown Concepts

Shut down the database to make operating system offline backups of all physical structures and to have modified static initialization parameters take effect when restarted.

SHUTDOWN [NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]

SHUTDOWN NORMAL

Normal is the default shutdown mode.Normal database shutdown proceeds with the
following conditions:

• No new connections can be made.
• The Oracle server waits for all users to disconnect before completing the shutdown.
• Database and redo buffers are written to disk.
• Background processes are terminated, and the SGA is removed from memory.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>



SHUTDOWN TRANSACTIONAL

A transactional shutdown prevents clients from losing work. A transactional database

shutdown proceeds with the following conditions:

• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction that is in progress.
• When all transactions have finished, a shutdown occurs immediately.
• The next startup does not require an instance recovery.

SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SHUTDOWN IMMEDIATE

Immediate database shutdown proceeds with the following conditions:

• Current SQL statements being processed by Oracle are not completed.
• The Oracle server does not wait for the users, who are currently connected to the database, to disconnect.
• Oracle rolls back active transactions and disconnects all connected users.
• Oracle closes and dismounts the database before shutting down the instance.
• The next startup does not require an instance recovery.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SHUTDOWN ABORT

If no shutdown option works then database instance can be shutdown using abort option. Aborting an instance proceeds with the following conditions:

• Current SQL statements being processed by the Oracle server are immediately terminated.
• Oracle does not wait for users currently connected to the database to disconnect.
• Database and redo buffers are not written to disk.
• Uncommitted transactions are not rolled back.
• The instance is terminated without closing the files.
• The database is not closed or dismounted.
• The next startup requires instance recovery, which occurs automatically.

SQL> shutdown abort

ORACLE instance shut down.

SQL>

All shutdown options except ABORT are clean shutdown where no instance recovery is required. Shutdown abort is a dirty shutdown as it requires instance recovery before getting back up.

Oracle Streams Basics (10g)

What Is Streams?

Oracle Streams captures database changes at a source database, stages the changes, propagates the changes to one or more destination databases, and then applies the changes at the destination database(s). Using Oracle Streams, enterprise systems can capture, propagate, and apply information as follows:

  • Within an Oracle database
  • Between two Oracle databases
  • Among multiple Oracle databases
  • Between an Oracle database and a non-Oracle database

Oracle9i Database introduced the Change Data Capture (CDC) feature.

Oracle 10g introduces Asynchronous Change Data Capture, which instead of using triggers uses the database log files to capture changes and apply them to collection tables. Asynchronous requires both source and destination databases to be Oracle 10g.

 

Streams Features

  • Distinct capture/propagation/apply services
  • Message Queueing (via AQ)
  • Publish Subscribe
  • Rules based Capture/Transform/Apply
  • Database integration
  • Easy configuration
  • Flexible
  • Inline transformations

 

Streams Overview

There are three basic tasks of a stream

  • Capture
  • Staging /Propagation
  • Apply / Consumption

Capture Process:

  • A capture process captures a database change(DML or DDL changes from Redo logs) and converts it into a specific message format called a logical change record (LCR) or user messages.
  • After capturing an LCR, a capture process enqueues a message containing the LCR into a queue(local or remote).
  • Captured LCRs can be sent to queues in the same database or other databases by propagations.
  • A capture process running on source database is a local capture process while if it runs on a remote database, the capture process is a downstream capture process. (Check Local and Downstream Capture for Details

Further Readings:  Local and DownStream Capture(10g) , Summary of Information Capture Information in Oracle 11g

Staging Process:

  • Oracle Streams uses queues to stage messages. Staged messages can be consumed or propagated, or both.
  • Staged messages can be consumed by an apply process, a messaging client, or a user application.
  • A running apply process implicitly dequeues messages, but messaging clients and user applications explicitly dequeue messages.

Further Readings: Message Propagation between Queues,

Propagation Process:

  • Oracle Streams uses  Oracle scheduler jobs to propagate messages between two queues.
  • A propagation is always between a source queue and a destination queue.
  • Only one propagation is allowed between a particular source queue and a particular destination queue.
  • A single source queue can propagate messages to multiple destination queues, and a single destination queue can receive messages from multiple source queues. A single propagation can propagate LCRs and user messages.
  • A propagation either propagates or discards messages based on rules defined.
  • A propagation can be queue-to-queue or queue-to-database link. Propagation to the remote queue uses database links over Oracle Net Services.

Apply/Consumption Process:

  • An apply process dequeues messages (LCR or user messages) from a specific queue
  • An apply process either applies each message directly or passes it as a parameter to an apply handler. 
  • Auser-enqueued message dequeued by an apply process is of type ANYDATA and can contain any message, including an LCR or a user message.
  • An apply process can only dequeue messages from an ANYDATA queue, not a typed queue.

Further Readings: Message Processing with Apply Process

Streams Process Architecture

In simple five steps the whole Streams process is as follows:

  • Capture changes at a database(source/target)
  • Enqueue events in a queue
  • Propagate events from one queue to another
  • Dequeue events
  • Apply events at a database

Streams Rules

  • Rule is a database object that enables a client to perform an action when an event occurs and a condition is satisfied.
  • Rules are used to control which information to share and where to share it.
  • Rules can be used during capture, propagate and apply process.
  • Rule consists of Rule condition and optionally evaluation context and Action extent . It can be defined in three levels:
    1. Table
    2. Schema
    3. Global

Database Configuration

Following parameters should be set at each site:

  • COMPATIBLE
  • PROCESSES
  • SHARED_POOL_SIZE
  • STREAMS_POOL_SIZE
  • TIMED_STATISTICS

DB parameters that must be set at source site:

  • LOG_ARCHIVE_DEST_n
  • LOG_ARCHIVE_DEST_STATE_n
  • REMOTE_ARCHIVE_ENABLE
  • UNDO_RETENTION

DB parameters specific to propagation:

  • GLOBAL_NAMES
  • JOB_QUEUE_PROCESSES

Databases capturing changes via Streams must be in archived mode whereas databases applying changes are not required in archived mode.

Oracle Streams Administration Tools

Oracle-Supplied PL/SQL Packages

DBMS_STREAMS_ADM Package

DBMS_CAPTURE_ADM Package

DBMS_PROPAGATION_ADM Package

DBMS_APPLY_ADM Package

DBMS_STREAMS_MESSAGING Package

DBMS_RULE_ADM Package

DBMS_RULE Package

DBMS_STREAMS Package

DBMS_STREAMS_TABLESPACE_ADM

DBMS_STREAMS_AUTH Package

Streams Data Dictionary Views

Streams Static Data Dictionary Views

Streams Dynamic Performance Views

Oracle Enterprise Manager Console

Oracle provides a Streams tool in the Oracle Enterprise Manager Console to help configure , administer and monitor Streams environments.

Starting and Altering Database

Starting Up a Database

There are different states in which database can start up. The following scenarios describe different stages of starting up an instance starting with shutdown.

 

SHUTDOWN

NOMOUNT

MOUNT

OPEN

 

Starting the Instance (NOMOUNT)

An instance is started in the NOMOUNT stage only during database creation or the re-creation
of control files.
Starting an instance includes the following tasks:

• Reading the initialization file from $ORACLE_HOME/dbs in the following order:
        – First spfileSID.ora
        – If not found then, spfile.ora
        – If not found then, initSID.ora
• Allocating the SGA
• Starting the background processes
• Opening the alertSID.log file and the trace files

STARTUP NOMOUNT

Specifying the PFILE parameter with STARTUP overrides the default behavior

STARTUP NOMOUNT PFILE = /u01/oracle/dbs/init.ora
 

Mounting the Database (MOUNT)

To perform specific maintenance operations, you start an instance and mount a database but
do not open the database.

For example, the database must be mounted but not open during the following tasks:
• Renaming data files (data files for an offline tablespace can be renamed when the
database is open)
• Enabling and disabling online redo log file archiving options
• Performing full database recovery
Mounting a database includes the following tasks:
• Associating a database with a previously started instance
• Locating and opening the control files specified in the parameter file
• Reading the control files to obtain the names and status of the data files and online
redo log files. However, no checks are performed to verify the existence of the data
files and online redo log files at this time.

STARTUP MOUNT

 

Opening the Database (OPEN)

Normal database operation means that an instance is started and the database is mounted and
open. Opening the database includes the following tasks:

• Opening the online data files
• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the
database, the Oracle server returns an error. Oracle server verifies that all the data files and online redo log
files can be opened and checks the consistency of the database. If necessary, the SMON
background process initiates instance recovery.

STARTUP

 

Restricting Access to an Instance at Startup

You can start an instance, and optionally mount and open a database, in restricted mode so that the instance is available only to administrative personnel (not general database users). Use this mode of instance startup when you need to accomplish one of the following tasks:

  • Perform an export or import of data

  • Perform a data load (with SQL*Loader)

  • Temporarily prevent typical users from using data

  • Perform certain migration or upgrade operations

STARTUP RESTRICT

 

Forcing Instance to Start / Instance with Media Recovery

         You cannot shut down the current instance with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, or SHUTDOWN TRANSACTIONAL commands. Use the STARTUP command with the FORCE clause:

STARTUP FORCE

If media recovery is required, start by using the STARTUP command with the RECOVER clause:

STARTUP OPEN RECOVER
 

Altering Database Availability

You can alter the availability of a database. You may want to do this in order to restrict access for maintenance reasons or to make the database read only. The following sections explain how to alter the availability of a database:

 

Database can be altered to mount state from nomount state but cannot be altered directly to open state.

ALTER DATABASE MOUNT;

To open a mounted database, use the ALTER DATABASE statement with the OPEN clause:

ALTER DATABASE OPEN;

 

Opening a database in read-only mode enables you to query an open database while eliminating any potential for online data content changes.

ALTER DATABASE OPEN READ ONLY;

You can also open a database in read/write mode as follows which is the default mode:

ALTER DATABASE OPEN READ WRITE;

Increasing Recovery Area Size

To verify this run the following query. It will show the size of the recovery area and how full it is:

set lines 100
col name format a60
select	name
,	floor(space_limit / 1024 / 1024) "Size MB"
,	ceil(space_used  / 1024 / 1024) "Used MB"
from	v$recovery_file_dest
order by name
/

To fix the problem, you need to either make the flash recovery area larger, or remove some files from it.

If you have the disk space available, make the recovery area larger:

alter system set db_recovery_file_dest_size=<size> scope=both
/

To remove files you must use RMAN. Manually moving or deleting files will have no effect as oracle will be unaware. The obvious choice is to backup and remove some archive log files. However, if you usually write your RMAN backups to disk, this could prove tricky. RMAN will attempt to write the backup to the flash recovery area…which is full. You could try sending the backup elsewhere using a command such as this:

rman target / catalog user/pass@rmancat

run {
allocate channel t1 type disk;
backup archivelog all delete input format '/<temp backup location>/arch_%d_%u_%s';
release channel t1;
}

This will backup all archive log files to a location of your choice and then remove them.

http://www.shutdownabort.com/errors/ORA-19809.php

Enhanced by Zemanta

Recommended init parameters for Websphere Portal

Recommended init parameters for IBM Websphere Portal

wspprd.__db_cache_size=587202560
wspprd.__java_pool_size=16777216
wspprd.__large_pool_size=16777216
wspprd.__shared_pool_size=939524096
wspprd.__streams_pool_size=33554432
*.compatible=’10.2.0.3.0′
*.db_block_size=8192
*.db_cache_size=524288000
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_files=1024
*.db_recovery_file_dest=’/wspprd/oracle/product/10.2.0/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.job_queue_processes=10
*.open_cursors=1600
*.pga_aggregate_target=314572800
*.pre_page_sga=TRUE
*.processes=600
*.sessions=665
*.sga_max_size=1610612736
*.sga_target=1610612736
*.shared_pool_size=536870912
*.db_writer_processes=20
*.sort_area_size=65536
*.cursor_space_for_time=TRUE
*.session_cached_cursors=500
*.log_checkpoints_to_alert=TRUE
*.log_checkpoint_interval=0
*.log_checkpoint_timeout=0
*.fast_start_mttr_target=3600
*.log_buffer=419430400

Enhanced by Zemanta