Thursday, 23 November 2017

Automatic Server and Database Monitoring mail from local machine

Automatic Server and Database Monitoring mail from local machine


Automatic mail from local machine of "Monitoring of AIX Servers and result of Database Queries"
Following files needed for implementing mail for  "Monitoring of AIX Servers and Database"

1) mailid.txt : You can put all mail ids in this file and system will read this file to send mail.

cat>mailid.txt
abc@example.com
xyz@example.com

2) monitor.sh : This file have all commands and Oracle queries whose result need to be sent by mail

format for monitor.sh file is :-

#!/bin/ksh
#mandatory environment variable need to export
export PATH=<Here Values from .profile/.bash_profile file>
export ORACLE_BASE=<value>
export ORACLE_HOME=<value>
export ORACLE_SID=<value>
export LD_LIBRARY_PATH=<value>

#All commands need to be put here one by one with proper echo details
echo "Start date and time of monitoring:"`date`
echo "Server UP Time:"`uptime`
echo "***********Mount points analysis*************"
df -g
echo "Established Connections Count:"`netstat -a|grep -i ESTABL|wc -l`
echo "Wait Connections Count:"`netstat -a|grep -i WAIT|wc -l`

dbuser=<Oracle Database User Name here>
dbuser=<Password for Database User Name here>
sqlplus -s $dbuser/$dbpass@$ORACLE_SID <<EOF
set feed off  echo off colsep '|' verify OFF wrap OFF trimspool ON;
set numwidth 15;
set linesize 30000;
set pagesize 50000;
set serveroutput on size 10000;
set timing off;

<Any valid sql query/queries here with prompt of column header>
exit;
EOF


3) mail.sh :- This file will connect the server, execute the above script and store the output of above script in one file and content of that file will be mailed to all mail IDs which are configured in the mailid.txt file

format for mail.sh file is :-

sshpass -p "<password>" ssh -q -oPort=922 <user>@ip < path_to_monitor.sh>monitoring.txt
cat path_to_monitoring.txt|mailx -r <from mailid here>  -s "<Subject of mail here>" `<cat path_to_mailid.txt>`

4) crontab.txt : This file have cron entry so that we can schedub.txtle cronjob

format for mail.sh file is :-

30 * * * * sh path_to_mail.sh>path_to_log_file

we can schedule cronjob by following command

crontab crontab.txt

 Note 1):- sshpass package should be install on your machine (if not installed then you can install by following command )

yum install sshpass

2) You system should be configure for ipv4 postfix (if not then you can follow following steps to configure)

vi /etc/postfix/main.cf

chnage the vale of "inet_protocols" from all to ipv4 and restart the postfix service by following commands

service postfix restart

xhost problem resolution during the Oracle installation in Linux

xhost problem resolution during the Oracle installation in Linux


Perform the following steps to avoid xhost problem during the installation of Oracle in Linux


1st Method :-

1.    log into console as root and open a terminal window within CDE
2.    DISPLAY=:0.0
3.    export DISPLAY
4.    xhost +
5.    su - oracle
6.    DISPLAY=:0.0
7.    export DISPLAY
8.    /usr/bin/xclock - Launched xclock this way to ensure it was working, and it was
9.    Changed back to the directory where the runInstaller was and launched.

2nd Method:-Install all prerequisites through root user then restart the system and login with Oracle user and do the installation , you will not get any error related to xhost.

3rd Method:- Do the silent mode installation to avoid xhost problem.

Collection in Oracle

 Collection in Oracle


Purpose of using different types of PL/SQL collections in Oracle
or
What is the main purpose of using collections in oracle ?
1)  Index by table (PLSQL Tables) Assosiate Array
2)  Nested table
3)  Variable size Array (Varray
Can you please explain the difference between the above types of collections ?

PL/SQL offers these collection types:-
Nested Tables:-
they are the most common form of collection. A nested table is a variable which can hold more than one instance of something,
often a record from a database table. They might be declared like this:

SQL>type emp_nt is table of emp%rowtype;
SQL>emp_rec_nt emp_nt;

They are useful whenever we want to store multiple instances of data against which we want to do the same thing.
 

The classic example is using BULK COLLECT to store multiple records:

select * bulk collect into emp_rec_nt from employees;

This gives us a source of data we can loop round; crucially we can navigate backwards as well as forwards, even skip to the end or the beginning,
which are things we cannot do with a cursor. Nested tables can be collections of any data type, including composites such as PL/SQL records or user-defined types.

Index By table:-Index By table is better called an Associative Array . These are simple collections of single attributes with an index.
Nested tables also have indexes but their indexes are just row counts. With an associative array the index can be meaningful, i.e. sourced from a data value.
So they are useful for caching data values for later use. The index can be a number, a string which can be very useful.
For instance, here is an associative array of salaries which is indexed by the employee identifier.

SQL>type emp_sal_aa is table of emp.sql%type index by emp.empno%type;
SQL>l_emp_sales emp_sal_aa;

Note that I could have declared that array using INDEX BY BINARY_INTEGER but it is clearer to use the %TYPE syntax instead. Elements of that array can identified
by an index value, in this case EMPNO:

SQL>l_emp_sals(l_emp_no) := l_emp_sal;

Other than caching reference tables or similar look-up values there aren't many use cases for associative arrays.

Variable arrays:-Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading:
they are actually fixed arrays. There's little we can do with VArrays which we can't do with nested tables (except constrain the number of elements and it's pretty rare that we would want to do that).
They are declared like this:

SQL>type emp_va is varray(14) of emp%rowtype;
SQL>emp_rec_va emp_va;

We can use bulk collect to populate a VArray ...

select * bulk collect into emp_rec_va from employees;

However we must be certain the query will return at most the number of elements specified in the VArray's declaration. Otherwise the SELECT will hurl ORA-22165.

There are no known use cases for variable arrays. Okay that's a bit harsh, but almost all of the time you will use nested tables instead. The one big advantage of VArrays over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a VArray.


Associative arrays:- also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.
Nested tables:- hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
Varrays:- (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

1. Choosing Between Nested Tables and Associative Arrays:-
Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation,
but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot.
Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers.

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).
 

2. Choosing Between Nested Tables and Varrays:-
Varrays are a good choice when: 

1) The number of elements is known in advance.
2) The elements are usually all accessed in sequence.

When stored in the database, varrays keep their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables are a good choice when:
1) The index values are not consecutive.
2) There is no set number of index values. However, a maximum limit is imposed.
3) You need to delete or update some elements, but not all the elements at once.

You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.

Nested table data is stored in a separate store table, a system-generated database table associated with the nested table. The database joins the tables for you when you access the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.

Automatic Server and Database Monitoring mail from local machine

Automatic Server and Database Monitoring mail from local machine Automatic mail from local machine of "Monitoring of AIX Servers an...