oraenv

The oraenv and coraenv utilities both aid in setting the Oracle environment on UNIX systems (other utilities exist on Windows platform that enable the Oracle Home to be set.) The coraenv utility is appropriate for the UNIX C Shell; oraenv should be used with either the Bourne or Korn shells.



Database operations require the ORACLE_HOME to be set before the user may access the database. If ORACLE_HOME is not set, commands such as sqlplus, exp, or any other utility for that matter, will not be found.



Both utilities are shell scripts that do the same thing in the different UNIX shells. They will prompt for a SID of the database unless ORAENV_ASK is set to N. The utility will also append the ORACLE_HOME value to the path, marking the location of the utility.



The oraenv command will prompt for the SID of the database that you wish $ORACLE_HOME to access.

$ . oraenv
ORACLE_SID = [] ?



if its already set in oracle user's profile or export earlier in the same session then it behave as below:

$oraenv

ORACLE_SID = [IMANYDME] ?



The dbhome utility can now be used to verify that $ORACLE_HOME is correct.
$ dbhome
/data1/oracle/oracle/product/10.2.0/db_1



The “dot space” part of the command is required to make the environment change with the parent shell, as opposed to entering a command without it which would only affect the subshell running that process.

These commands can be used to avoid specifying the network service name when issuing commands. For instance, without using oraenv, a sqlplus command would look like:
$ sqlplus system/manager@nameofservice as sysdba

whereas after oraenv has been executed, the following command would work:
$ sqlplus system/manager as sysdba

emctl

How to configure Enterprise Manager (EM) Console:



You can verify EM console service is up or not by running below command:

$ emctl status dbconsole

TZ set to US/Pacific

Oracle Enterprise Manager 10g Database Control Release 10.2.0.4.0

Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.

https://rwcash.imany.com:1158/em/console/aboutApplication

Oracle Enterprise Manager 10g is running.

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

Logs are generated in directory /home/oracle/product/10.2.0/db_1/10.50.0.68_ENG10G01/sysman/log

or ps-ef grep dbconsole

in windows you can verify it in service window as -> oracleDBconsole"sid name" service.



*but before run above command you must export ORACLE_SID.

$ export ORACLE_SID=ENG10G01



if in status display as "NOT RUNNING" then you need to start as below:

$ emctl start dbconsole

if you want to stop dbconsole then specify "stop" instead of "start" in above command.



if you find any error in starting EM console you need to follow below step to reconfigure it:

- export/set oracle_sid=YourInstanceName
- emctl stop dbconsole
- emca -deconfig dbcontrol db
- emca -repos recreate (optional)
- emca -config dbcontrol db



Note : before run the emca -config dbcontrol db , you must know the oracle_sid, listenerport, sys, system ,DBSNMP and SYSMAN user’s password. Otherwise login to sqlplus with sysdba and change all users password including sys user also.



You can also configure this service in auto start mode on server reboot.

lsnrctl

The lsnrctl utility manages the Oracle listener processes. The Oracle listener process is required for database applications to access the database through SQL*Net or Net8. lsnrctl requires entries in the listener.ora file that specify the port for that listener. The listener.ora file is the configuration file for the network listener. It resides on the server and defines the network listener address, the SID for the database for which it listens, and other optional parameters for tracing and logging.



The lsnrctl command can be executed without parameters, in which the lsnrctl shell will be invoked, or it can execute commands directly when specified on the command line.



$lsnrctl

LSNRCTL for Solaris: Version 10.2.0.4.0 - Production on 27-AUG-2009 18:04:02

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

start

stop

status

services

version

reload

save_config

trace

spawn

change_password

quit

exit

set*

show*



set – Changes the value of any parameter. Everything that can be shown can be set.
show – Displays current parameter settings.



The start command will start the default listener (named LISTENER); otherwise the name can be specified as the second parameter. Once started, the status can be determined using the status command:

LSNRCTL> status

it display listnername,version,start date,uptime,log file etc.

if the status command display as "NO Listener" then you can start the listenr using "start" command as below.

LSNRCTL> start

same way you can stop the listener as below:

LSNRCTL> stop



on unix box you can verify the listener service is up and runnign as below:

ps -ef grep tnslsnr

DBV

dbv
The Database Verify utility (dbv) provides a mechanism to validate the structure of Oracle data files at the operating system level. It should be used on a regular basis to inspect data files for signs of corruption.

Although it can be used against open data files, the primary purpose of dbv is to verify the integrity of cold datafiles that would be used for a backup. The utility can only be used against datafiles however, not control files or archived redo logs.



dbv Command Line Options


File – The name of the Oracle datafile to verify. The file must be specified with full path name and file name including extension (.dbf).
Start – The block within the file to begin the verification. If none is specified, dbv will begin at the first block in the file. This parameter should be used when processing large files in which the entire file does not need scanning.
End – The last block in the data file to verify. If none is specified, dbv will process to the end of the file. This parameter should be used when processing large files in which the entire file does not need scanning.
Blocksize – The database blocksize of the file that needs verification. This must be set to the v$datafile.db_block_size value for the data file.
If the blocksize is not specified, it will default to 2K. If the blocksize for the datafile does not equal the blocksize specified, dbv will terminate and print an error message:
dbv-00103: Specified BLOCKSIZE (2048) differs from actual (8192)

Logfile – The name of the file to direct the dbv output. If none is specified, the output will be sent to the terminal. When scheduling a dbv-based shell script, it will be this file that needs to be checked for corruption errors.
Feedback – A progress meter that displays a dot for n pages examined in the file (FEEDBACK=10000). Use this to provide a status indicator when dbv is executed against large files. This is needed to indicate that the dbv process is actively processing a file. This option is obviously not needed when executing dbv through a scheduled shell script.
Parfile - A parameter file that can contain any of these options. The parfile should be created once and used with every dbv command.
Segment_ID – A parameter that will scan a segment regardless of the number of files it spans. The format is segment_id=tsn.segfile.segblock
Userid – Used only in combination with segment_id to specify the username/password for the database connection.



Executing dbv and Interpreting the Output
dbv can be executed by specifying the file name and blocksize of the datafile. All other parameters are optional.

$ dbv file=/data1/oracle/oradata/IMANYDME/Edge_app.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Thu Aug 27 17:44:36 2009

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

DBVERIFY - Verification starting : FILE = /data1/oracle/oradata/IMANYDME/Edge_app.dbf


Once executed, dbv provides the following output for each file it verifies:

DBVERIFY - Verification complete

Total Pages Examined : 695312

Total Pages Processed (Data) : 456902

Total Pages Failing (Data) : 0

Total Pages Processed (Index) : 211585

Total Pages Failing (Index) : 0

Total Pages Processed (Other) : 5796

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 21029

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Highest block SCN : 283596686 (2.283596686)



Alternative Block Checking Mechanisms
analyze table … validate structure – The analyze command can do things that dbv cannot and vice-versa. The analyze command can validate that tables and indexes are in sync with each other. However, the analyze command only processes an object up to the point of its high water mark (HWM), whereas dbv processes all blocks in a file. Block corruption can occur in blocks above the HWM.

The analyze command would have to be executed against an open database for each object in the database. dbv can work against offline files and is much faster since it is strictly at the file level. In addition, the analyze table command places an exclusive lock on the object being analyzed. Alternatively, dbv works outside of the database in “read only” mode against the datafiles and does not lock anything. Any errors encountered by the analyze table command are reported in the session trace file in the user dump destination directory.

dbstart/dbshut

dbstart / dbshut
Oracle provides two UNIX scripts that assist DBAs with starting and stopping the database: dbstart and dbshut. For Windows platforms, the oradim utility is provided for starting and stopping the Oracle instance.

The dbstart utility reads the oratab file, shown in the example below. The oratab file will reside in either /etc or /var/opt/oracle, depending on the UNIX version. It contains three data items separated by colons:

ORCL:/data1/oracle/oracle/product/10.2.0/db_1:Y
IMANYDME:/data1/oracle/oracle/product/10.2.0/db_1:Y
dmedemo1:/data1/oracle/oracle/product/10.2.0/db_1:Y

The first field is the Oracle SID. The second field is the home directory for that Oracle SID. The Y or N instructs Oracle whether to start or stop the particular database when either the dbstart or dbshut command is issued. The dbstart command simply parses the oratab file and starts those databases that have a Y in the third field. It also uses the ORACLE_HOME specified in the file to connect internally to the database and issue the startup command.

The dbstart command can be added to the UNIX servers’ initialization or run level scripts. This enables dbstart to be executed each time the machine is booted or when it changes run levels. The method for implementing this is platform specific, as we see below.

Auto Start on HP-UX and Solaris
For HP-UX version 10 and above, the system initialization scripts are contained in /etc/rc.d directories, where “n” is the operating system run-level. These directories contain scripts that begin with a K or S, followed by a number, and then a file name (S75cron). All scripts that begin with “S” are executed at system startup in ascending order of their number. Scripts beginning with “K” (Kill) are called at system shutdown time.

As a general rule of thumb, the Oracle startup script should have a high sequence number (S99dbstart), which will ensure that other system processes have been started prior to Oracle. Likewise, the kill scripts should have a low sequence number in order to shutdown Oracle early in the process (K01dbshut).

Auto Start on AIX
For AIX servers, the system initialization file is /etc/inittab and the initialization script is /etc/rc. A utility (/usr/sbin/mkitab) can be used to make an entry in the inittab file. The shutdown script for AIX is /usr/sbin/shutdown, although it should not be modified to support dbshut.

ping,tnsping

ping, tnsping

The three main things to check for diagnosing remote database connection problems are the machine, the listener, and the database. The utilities that can be used to test each one of these include ping , tnsping , and a database connection, as depicted below:

The ping utility is used to test the connectivity to a remote machine. ping will indicate whether a remote server is accessible and responding. If the ping command indicates that a machine cannot be accessed, the other connectivity tests will also fail.

The ping utility is usually found in /usr/sbin on UNIX machines and simply reports the health of the remote machine specified:

$ ping rwcash
PING rwcash.imany.com (10.50.0.68) 56(84) bytes of data.

64 bytes from 10.50.0.68: icmp_seq=0 ttl=64 time=0.146 ms

64 bytes from 10.50.0.68: icmp_seq=1 ttl=64 time=0.132 ms

--- rwcash.imany.com ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 2000msrtt min/avg/max/mdev = 0.107/0.109/0.112/0.002 ms, pipe 2

The ping command can also be executed at the DOS prompt on Windows machines to test client-to-server connectivity:

D:\> ping rwcash
Once connectivity to the host is confirmed with ping, the next connection to test is the listener. The tnsping utility is used to determine whether or not an Oracle service can be successfully reached. If a connection can be established from a client to a server (or server to server), tnsping will report the number of milliseconds it took to reach the remote service. If unsuccessful, a network error will be displayed. However, tnsping will only report if the listener process is up and provides no indication of the state of the database.

$ tnsping "netservice name" "count"
The “net service name” must exist in the tnsnames.ora file. This file is used by clients and database servers to identify server destinations. It stores the service names and database addresses. The “count” parameter is optional and will show the number of times the command should try to connect to the specified service name.

$ ./tnsping rwcash_eng10g01 3

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 27-AUG-2009 04:34:56

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =rwcash.imany.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ENG10G01)))

OK (60 msec)

OK (10 msec)

OK (10 msec)

The result from the tnsping command above shows 60 milliseconds (ms) were required for the first “ping”. During this time period, the alias rwcash_eng10g01 from the local tnsnames.ora file was retrieved, a DNS of the host “rwcash” was resolved, and the TNS connect and refuse packets were transported. The second trip took only 10 ms because all of the connection information was already cached.

tnsping can be used to test listener connectivity but not database performance. While ping usually returns faster than tnsping, it gives no indication whether or not SQL*Net is performing. The ping utility simply uses IP to try to reach a destination, whereas tnsping uses TCP (a socket) and transfers data between two nodes. As a result, the ping utility will always be faster. A slow tnsping round trip could indicate any number of problems, including a very active server or a slow network.

Once the host and listener connectivity have been verified, the final connectivity test is the database itself. The host could be accessible and the listener active, yet the database might still be inaccessible. Granted, this final test could be performed first and the others (ping, tnsping) performed only if this test fails, since if the database is accessible, so are the database host and listener. Any type of database connection can be used to confirm database connectivity (SQL*Plus, JDBC, ODBC, Pro*C).