Archive for 09/01/2008 - 10/01/2008

ASM INSTANCE

Initialization Parameters for ASM Instances

The following initialization parameters relate to an ASM instance. Parameters that start with ASM_ cannot be set in database instances.

Name

Description

INSTANCE_TYPE

Must be set to ASM

Note: This is the only required parameter. All other parameters take suitable defaults for most environments.

ASM_POWER_LIMIT

The default power for disk rebalancing.

Default: 1, Range: 0 – 11

ASM_DISKSTRING

A comma-separated list of strings that limits the set of disks that ASM discovers. May include wildcard characters. Only disks that match one of the strings are discovered. String format depends on the ASM library in use and on the operating system. The standard system library for ASM supports glob pattern matching.

For example, on a Solaris server that does not use ASMLib, to limit discovery to disks that are in the /dev/rdsk/ directory, ASM_DISKSTRING would be set to:

/dev/rdsk/*

another example:

/dev/rdsk/*s3,/dev/rdsk/*s4

(This could be simplified to:) /dev/rdsk/*s[34]

Default: NULL. A NULL value causes ASM to search a default path for all disks in the system to which the ASM instance has read/write access. The default search path is platform-specific.

ASM_DISKGROUPS

A list of the names of disk groups to be mounted by an ASM instance at startup, or when the ALTER DISKGROUP ALL MOUNT statement is used.

Default: NULL (If this parameter is not specified, then no disk groups are mounted.)

This parameter is dynamic, and if you are using a server parameter file (SPFILE), you should not need to manually alter this value. ASM automatically adds a disk group to this parameter when the disk group is successfully created or mounted, and automatically removes a disk group from this parameter when the disk group is dropped or dismounted.


ASM Instance Memory Requirements

ASM instances are smaller than database instances. A 64 MB SGA should be sufficient for all but the largest ASM installations. Total memory footprint for a typical ASM instance is approximately 100 MB.

ASM REBALANCING

Dynamic Storage Configuration

ASM enables you to change the storage configuration without having to take the database offline. It automatically rebalances—redistributes file data evenly across all the disks of the disk group—after you add disks to or drop disks from a disk group.

Should a disk failure occur, ASM automatically rebalances to restore full redundancy for files that had extents on the failed disk. When you replace the failed disk with a new disk, ASM rebalances the disk group to spread data evenly across all disks, including the replacement disk.

Tuning Rebalance Operations

The V$ASM_OPERATION view provides information that can be used for adjusting ASM_POWER_LIMIT and the resulting power of rebalance operations. The V$ASM_OPERATION view also gives an estimate in the EST_MINUTES column of the amount of time remaining for the rebalance operation to complete. You can see the effect of changing the rebalance power by observing the change in the time estimate.

Effects of Adding and Dropping Disks from a Disk Group

ASM automatically rebalances whenever disks are added or dropped. For a normal drop operation (without the FORCE option), a disk is not released from a disk group until data is moved off of the disk through rebalancing. Likewise, a newly added disk cannot support its share of the I/O workload until rebalancing completes. It is more efficient to add or drop multiple disks at the same time so that they are rebalanced as a single operation. This avoids unnecessary movement of data.

For a drop operation, when rebalance is complete, ASM takes the disk offline momentarily, and then drops it, setting disk header status to FORMER.

You can add or drop disks without shutting down the database. However, a performance impact on I/O activity may result.

ASM DISKS – DISKGROUPS

Automatic Storage Management (ASM) is an integrated file system and volume manager expressly built for Oracle database files. ASM provides the performance of raw I/O with the easy management of a file system. It simplifies database administration by eliminating the need for you to directly manage potentially thousands of Oracle database files. It does this by enabling you to divide all available storage into disk groups. You manage a small set of disk groups and ASM automates the placement of the database files within those disk groups.

Disk Groups

The primary component of ASM is the disk group. A disk group consists of a grouping of disks that are managed together as a unit. You configure ASM by creating disk groups to store database files. Oracle provides SQL statements that create and manage disk groups, their contents, and their metadata.

Disks

The disks in a disk group are referred to as ASM disks. On Windows operating systems, an ASM disk is always a partition. On all other platforms, an ASM disk can be

A partition of a logical unit number (LUN)

A network-attached file

Note: Although you can also present a volume (a logical collection of disks) for management by ASM, it is not recommended to run ASM on top of another host-based volume manager.

When an ASM instance starts, it automatically discovers all available ASM disks. Discovery is the process of determining every disk device to which the ASM instance has been given I/O permissions (by some operating system mechanism), and of examining the contents of the first block of such disks to see if they are recognized as belonging to a disk group. ASM discovers disks in the paths that are listed in an initialization parameter, or if the parameter is NULL, in an operating system–dependent default path.

Discovery Rules

· ASM discovers no more than 10,000 disks. That is, if more than 10,000 disks match the ASM_DISKSTRING initialization parameter, only the first 10,000 are discovered.

· ASM does not discover a disk that contains an operating system partition table, even if the disk is in an ASM_DISKSTRING search path and ASM has read/write permission on the disk.

· If ASM recognizes a disk header as that of an Oracle object, such as the header of an Oracle datafile, the disk is discovered, but can only be added to a disk group with the FORCE keyword. Such a disk appears in V$ASM_DISK with a header status of FOREIGN.

In addition, ASM identifies the following configuration errors during discovery:

· Multiple paths to the same disk : In this case, if the disk is part of a disk group, disk group mount fails. If the disk is being added to a disk group with the ADD DISK or CREATE DISKGROUP command, the command fails. To correct the error, restrict ASM_DISKSTRING so that it does not include multiple paths to the same disk, or if you are using multipathing software, ensure that you include only the pseudo-device in ASM_DISKSTRING.

· Multiple ASM disks with the same disk header: This can be caused by a bit copy of one disk onto another. In this case, disk group mount fails.

Determining the Number of Disk Groups

Disks in a given disk group should have similar size and performance characteristics. If you have several different types of disks in terms of size and performance, then it would be better to form several disk groups accordingly.

For recovery reasons, you might feel more comfortable having separate disk groups for your database files and flash recovery area files. Using this approach, even with the loss of one disk group, the database would still be intact.

How ASM Handles Disk Failures

Disk failure in this context means individual spindle failure or failure of another disk subsystem component, such as power supply, a controller, or host bus adapter. Here are the rules for how ASM handles disk failures:

  • A failure group is considered to have failed if at least one disk in the failure group fails.
  • A normal redundancy disk group can tolerate the failure of one failure group. If only one failure group fails, the disk group remains mounted and serviceable, and ASM performs a rebalance of the surviving disks (including the surviving disks in the failed failure group) to restore redundancy for the data in the failed disks. If more than one failure group fails, ASM dismounts the disk group.
  • A high redundancy disk group can tolerate the failure of two failure groups. If one or two failure groups fail, the disk group remains mounted and serviceable, and ASM performs a rebalance of the surviving disks to restore redundancy for the data in the failed disks. If more than two failure groups fail, ASM dismounts the disk group.
  • An external redundancy disk group cannot tolerate the failure of any disks in the disk group. Any kind of disk failure causes ASM to dismount the disk group.

Database Automatic Startup on HP-UX

1- Edit /etc/oratab

$ vi /etc/oratab

"/etc/oratab" 24 lines, 765 characters
#
# 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::
#
# 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.
#
#
ORABCKP:/oracle/app/oracle/product/10.2.0:N <-------- will be Y

(ORABCKP:/oracle/app/oracle/product/10.2.0:Y)
~

2- Create a script under /sbin/init.d

$vi /sbin/init.d/oracle

#!/sbin/sh
#
# @(#)
#
# NOTE: This script is not configurable! Any changes made to this
# scipt will be overwritten when you upgrade to the next
# release of HP-UX.
#
# WARNING: Changing this script in any way may lead to a system that
# is unbootable. Do not modify this script.
#
# NOTE:
# For ORACLE:
#
PATH=/usr/sbin:/usr/bin:/sbin
export PATH
ORA_HOME="/oracle/app/oracle/product/10.2.0"
ORA_OWNR="oracle"
rval=0
set_return() {
x=$?
if [ $x -ne 0 ]; then
echo "EXIT CODE: $x"
rval=1
fi
}

case $1 in
start)
# Oracle listener and instance startup
echo -n "Starting Oracle: "
su - $ORA_OWNR -c "$ORA_HOME/bin/dbstart /oracle/app/oracle/product/10.2.0"
echo "OK DB started"
;;
stop)
# Oracle listener and instance shutdown
echo -n "Shutdown Oracle: "
su - $ORA_OWNR -c "$ORA_HOME/bin/dbshut /oracle/app/oracle/product/10.2.0"
echo "OK DB shutdown"
;;
reload|restart)
$0 stop
$0 start
;;
*)
echo "usage: $0 {start|stop}"
;;
esac

exit $rval


3- Decide the runlevel and create soft links for the script we created.

Under /sbin/rc3.d
ln -s /sbin/init.d/oracle S999oracle

Under /sbin/rc1.d and /sbin/rc2.d
ln -s /sbin/init.d/oracle K9oracle

Audit Vault Installation Guide





Oracle Audit Vault provides a secure and highly scalable audit warehouse, enabling simplified reporting, analysis, and threat detection on audit data. Database audit settings are centrally managed and monitored.

Following document is an installation guide for audit vault 10.2.2.1.0 on hp-ux platform. It also includes necessary configuration on server and on client to start communication.

OPEN PDF FILE (2.86 MB , it may take a while to load the document)

Enterprise Manager Configuration for Active/Passive Clustered Database

If you have an active/passive clustered environment, also known as Cold Failover Cluster (CFC) environments (HP/UX Service Guard in this case), the enterprise manager database control fails to communicate with agent, listener and cannot connect to database because listener listens from cluster IP. In this case EM shows db, listener and agent down. Here is a workaround for this problem.

Change the hostname of the database server to cluster IP (192.168.1.3 in this example) in the following files. Also you should check LISTENER_PORT and LISTENER_NAME variables and change if it's not correct. If management agent is running restart it. (emctl stop dbconsole / emctl start dbconsole)

(assume that ORACLE_HOME is /oracle/product/10.2.0 ; ORACLE_SID=ORCL ; CLUSTER_IP=192.168.1.3 ; LISTENER_NAME=LISTENER_ORCL ; LISTENER_PORT = 1521)
vi /oracle/product/10.2.0/dbserver_ORCL/sysman/config/emoms.properties

#Fri Sep 05 11:51:30 EEST 2008
oracle.sysman.emSDK.svlt.ConsoleServerName=dbserver_Management_Service
oracle.sysman.eml.mntr.emdRepPwd=662e901ada770e41
emdrep.ping.pingCommand=/usr/sbin/ping
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=ORCL
oracle.sysman.eml.mntr.emdRepPwdSeed=1893515180231278974
oracle.sysman.emSDK.svlt.ConsoleMode=standalone
oracle.sysman.emRep.dbConn.statementCacheSize=30
oracle.sysman.db.isqlplusUrl=http\://192.168.1.3\:5560/isqlplus/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerPort=1158
oracle.sysman.eml.mntr.emdRepRAC=FALSE
oracle.sysman.emSDK.emd.rt.useMonitoringCred=true
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
oracle.sysman.db.isqlplusWebDBAUrl=http\://192.168.1.3\:5560/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=192.168.1.3
oracle.sysman.eml.mntr.emdRepDBID=3824679565
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=1158
oracle.sysman.eml.mntr.emdRepServer=192.168.1.3
oracle.sysman.eml.mntr.emdRepSID=ORCL
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=192.168.1.3)(PORT\=1521)
))(CONNECT_DATA\=(SERVICE_NAME\=ORCL)))
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.db.adm.conn.statementCacheSize=2
oracle.sysman.db.perf.conn.statementCacheSize=30



vi /oracle/product/10.2.0/dbserver_ORCL/sysman/config/emd.properties

REPOSITORY_URL=http://192.168.1.3:1158/em/upload/
emdWalletSrcUrl=http://192.168.1.3:1158/em/wallets/emd
EMD_URL=http://192.168.1.3:3938/emd/main



vi /oracle/product/10.2.0/dbserver_ORCL/sysman/emd/targets.xml

Targets AGENT_SEED="-1761825416">
Target TYPE="oracle_emd" NAME="192.168.1.3:3938"/>
Target TYPE="host" NAME="10.6.111.36"/>
Target TYPE="oracle_database" NAME="ORCL">
Property NAME="MachineName" VALUE="192.168.1.3"/>
Property NAME="Port" VALUE="1521"/>
Property NAME="SID" VALUE="ORCL"/>
Property NAME="OracleHome" VALUE="/oracle/product/10.2.0"/>
Property NAME="UserName" VALUE="6f7af2088af7c514" ENCRYPTED="TRUE"/>
Property NAME="password" VALUE="ffdb6ed655af4c51" ENCRYPTED="TRUE"/>
/Target>
Target TYPE="oracle_listener" NAME="LISTENER_ORCL">
Property NAME="Machine" VALUE="192.168.1.3"/>
Property NAME="LsnrName" VALUE="LISTENER_ORCL"/>
Property NAME="Port" VALUE="1521"/>
Property NAME="OracleHome" VALUE="/oracle/product/10.2.0"/>
Property NAME="ListenerOraDir" VALUE="/oracle/product/10.2.0/network/admin"/>
/Target>
/Targets>

Important Note: These steps will not make your enterprise manager and cluster environment fully adapted, after a failover operation enterprise manager will not work. For a fully adapted environment refer to http://download.oracle.com/docs/cd/B19306_01/em.102/b40002/actpass_env.htm#CHDBBEAB

Script to Restart the OEM Agent When It Has Failed

Gaurav Batta has published this nice script to restart the OEM agent when it has failed:


$ cat agent_check.sh
#!/usr/bin/ksh
#
agent_status=`ps -ef|awk '{print $8}'|grep agent10g/bin/emagent`

if [[ -z $agent_status ]]
then
$AGENT_HOME/bin/emctl start agent
echo Agent started on server `hostname` at `date`
else
echo Agent is running fine on server `hostname` at `date`
fi


It is running every 30min.
crontab –l

00,30 * * * * /home/oemagent/agent_check.sh >> /home/oemagent/agent_status.log

COMMONLY KNOWN TNS ERRORS

Error: 32: Broken pipe

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12547: TNS:lost contact

TNS-12560: TNS:protocol adapter error

TNS-00517: Lost contact

IBM/AIX RISC System/6000 Error: 32: Broken pipe

Cause:

The error 32 indicates the communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process.

Action:

1. One of reason would be processes parameter being low, and can be verified by the v$resource_limit view.

2. In Shared Server mode, check the 'lsnrctl services' output and see if the dispatcher has refused any connections, if so, then consider increasing the number of dispatchers.

3. Check the alert log for any possible errors.

4. Memory resource is also another cause for this issue. Check the swap, memory usage of the OS.

______________________________________________________________________________________________________________________________________

Error: 11: Resource temporarily unavailable

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12549: TNS:operating system resource quota exceeded

TNS-12560: TNS:protocol adapter error

TNS-00519: Operating system resource quota exceeded

IBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable

Cause:

As the error indicates operating system resource has exceeded.

Action:

1. Increase the appropriate OS kernal parameters for 'maximum number of processes allowed per user'.

For example for HP-UX the parameters are maxuprc and nproc.

______________________________________________________________________________________________________________________________________

Error: 12: Not enough space

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12549: TNS:operating system resource quota exceeded

TNS-12560: TNS:protocol adapter error

TNS-00519: Operating system resource quota exceeded

IBM/AIX RISC System/6000 Error: 12: Not enough space

Cause:

This is a memory related issue. The error indicates that there is not enough memory available to spawn and hand off the client connections.

Typical problems are:

- Out of system memory / swap

- Out of process slots in the process table

- Streams resources depleted

- Out of File Handles

- sga memory usage

Action:

1. Check in the alert log for any possible memory related error.

2. Increase swap/Virtual memory if possible the available memory.

3. SGA and PGA can be reduced to address the memory consumption.

4. MTS mode can be used to reduce the amount of process and memory consumption.

________________________________________________________________________________________________________________________________________

Error: Connection Pooling limit reached

Error stack in listener log:

TNS-12518 TNS:listener could not hand off client connection

TNS-12564 TNS:connection refused

TNS-12602 TNS: Connection Pooling limit reached

Action:

1. Try increasing initial number of dispatcher.

_______________________________________________________________________________________________________________________________________

Error: 2: No such file or directory

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12560: TNS:proto adapter error

TNS-00530: Proto adapter error

32-bit Windows Error: 2: No such file or directory

Error Description:

ERROR_FILE_NOT_FOUND

2

The system cannot find the file specified.

Cause:

This indicates the database service is not actually available

Ation:

1.Verify if the inteneded database really up and accepting local BEQ connections.

________________________________________________________________________________________________________________________________________

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

Section III: Errors Specific to Windows

It is also recommended that you refer the Section I above for a generic troubleshooting approach to the error TNS-12518.

This section briefly describes about the errors that are encountered on Windows Operating System. TNS-12518 most commonly occurs on 32-bit OS due to its memory constraint, however TNS-12518 can occur on 64-bit OS as well.

_______________________________________________________________________________________________________________________________________

Error: 233: Unknown error

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

32-bit Windows Error: 233: Unknown error

Error Description:

ERROR_PIPE_NOT_CONNECTED

233

No process is on the other end of the pipe.

Cause:

The communication has been broken while the listener is trying to hand off the client connection to the server process or dispatcher process.

Action:

Refer Note 371983.1

_______________________________________________________________________________________________________________________________________

Error: 54: Unknown error

Error stack in listener log:

TNS-12518: TNS:listener could not hand off client connection

TNS-12571: TNS:packet writer failure

TNS-12560: TNS:protocol adapter error

TNS-00530: Protocol adapter error

32-bit Windows Error: 54: Unknown error

Error Description:

ERROR_NETWORK_BUSY

54

0x36

The network is busy.

Cause:

This indicates a bottleneck at the network layer(TCP/IP).

Action:

1.Try increasing dispatchers and shared servers.

Powered by Blogger.

Page Views

- Copyright © Emre Baransel - Oracle Blog -Metrominimalist- Powered by Blogger - Designed by Johanes Djogan -