Tuesday, December 15, 2009

Apache Installation on Linux

Quick Installation Guide



Step 1: Check the http RPM


# rpm -qa |grep http
httpd-suexec-2.0.52-25.ent
httpd-2.0.52-25.ent
system-config-httpd-1.3.1-1
httpd-manual-2.0.52-25.ent

Step 2: Configure /etc/httpd/conf/httpd.conf file



# vi /etc/httpd/conf/httpd.conf

Then Edit the following line

ServerAdmin root@localhost, u can use another mail account

#ServerName new.host.name:80 ;

If this is not set to valid DNS name for your host, Entry here your IP_ADDR and uncomment
UseCanonicalName Off
DocumentRoot "/var/www/html"
DirectoryIndex index.html index.html.var index.php

Step 3: Start the httpd service


# chkconfig httpd on
# /etc/init.d/httpd restart

Configure Named Virtual Hosting:


Step 1: Edit /etc/httpd/conf/httpd.conf file as


NameVirtualHost 203.188.190.28:80

<VirtualHost *:80>
    ServerName localhost.localdomain
    DocumentRoot /var/www/html
    DirectoryIndex index.php index.html index.htm
</VirtualHost>

<VirtualHost 203.188.190.28:80>
    ServerName mgbs.cyberbangla.com
   ServerAlias mgbs.cyberbangla.com
    DocumentRoot /var/www/html/joomla/
    DirectoryIndex index.php index.html index.htm
</VirtualHost>

<VirtualHost 203.188.190.28:80>
    ServerName mgbs1.cyberbangla.com
    ServerAlias mgbs1.cyberbangla.com
    DocumentRoot /var/www/html/joomla1/
    DirectoryIndex index.php index.html index.htm
</VirtualHost>

Step 2: Copy the website content in the Document Root


Make sure that index.php index.html index.htm are exist in root directory.

Step 3: Restart the httpd service


# chkconfig httpd on
# /etc/init.d/httpd restart

Step 4: Change the file permission in /var/www/html/joomla


# chmod –R 755 /home/www/joomla
# chmod –R 755 /home/www/joomla/
# chmod –R 755 /home/www/joomla/*

Step 5: Go to web browser and enter the site address or IP


http://mgbs1.cyberbangla.com

Configuration - Multiple Sites and IP Addresses


Step 1: Make DNS entry with IP aliases


Step 2: Edit /etc/httpd/conf/httpd.conf file as


NameVirtualHost 97.158.253.26
NameVirtualHost 97.158.253.27

<VirtualHost *>
    DocumentRoot /home/www/site1
</VirtualHost>

<VirtualHost 97.158.253.26>
    DocumentRoot /home/www/site2
   ServerName www.my-site.com
    ServerAlias my-site.com, www.my-cool-site.com
</VirtualHost>

<VirtualHost 97.158.253.27>
    DocumentRoot /home/www/site3
   ServerName www.test-site.com
</VirtualHost>

How To Protect Web Page Directories With Passwords


You can password protect content in both the main and subdirectories of your DocumentRoot fairly easily. I know people who allow normal access to their regular Web pages, but require passwords for directories or pages that show MRTG or Webalizer data. This example shows how to password protect the /home/www directory.

1) Use Apache's htpasswd password utility to create username/password combinations independent of your system login password for Web page access.


You have to specify the location of the password file, and if it doesn't yet exist, you have to include a -c, or create, switch on the command line. I recommend placing the file in your /etc/httpd/conf directory, away from the DocumentRoot tree where Web users could possibly view it. Here is an example for a first user named peter and a second named Paul:
[root@bigboy tmp]# htpasswd -c /etc/httpd/conf/.htpasswd peter
New password:
Re-type new password:
Adding password for user peter
[root@bigboy tmp]#

[root@bigboy tmp]# htpasswd /etc/httpd/conf/.htpasswd paul
New password:
Re-type new password:
Adding password for user paul
[root@bigboy tmp]#

2) Make the .htpasswd file readable by all users.


[root@bigboy tmp]# chmod 644 /etc/httpd/conf/.htpasswd

3) Create a .htaccess file in the directory to which you want password control with these entries.


AuthUserFile /etc/httpd/conf/.htpasswd
AuthGroupFile /dev/null
AuthName EnterPassword
AuthType Basic
require user peter

Remember this password protects the directory and all its subdirectories. The AuthUserFile tells Apache to use the .htpasswd file. The require user statement tells Apache that only user peter in the .htpasswd file should have access. If you want all .htpasswd users to have access, replace this line with require valid-user. AuthType Basic instructs Apache to accept basic unencrypted passwords from the remote users' Web browser.

4) Set the correct file protections on your new .htaccess file in the directory /home/www.


[root@bigboy tmp]# chmod 644 /home/www/.htaccess

5) Make sure your /etc/httpd/conf/http.conf file has an AllowOverride statement in a <Directory> directive for any directory in the tree above /home/www. In this example below, all directories below /var/www/ require password authorization.


<Directory /home/www/*>
   AllowOverride AuthConfig
</Directory>

6) Make sure that you have a <VirtualHost> directive that defines access to /home/www or another directory higher up in the tree.


<VirtualHost *>
   ServerName 97.158.253.26
   DocumentRoot /home/www
</VirtualHost>

7) Restart Apache.


# /etc/init.d/httpd restart

Try accessing the web site and you'll be prompted for a password.

Article Written By : Mahabub Bhai.

Monday, December 14, 2009

Listener-Broken pipe

I have successfully install Oracle on Linux, installed and configured my all Java and PHP application for a content Server and a SMS Gateway and everything is working fine. After several months later from an angry user saying that he cannot login to the application and the SMS getaway not working.

The error message indicates a problem connecting to the Oracle database. After some troubleshooting (lsntctl status) I have found the Oracle TNS listener is not running, I attempt to start it and get the following stack trace error:
[oracle@contentserver admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.1.0 - Production on 14-DEC-2009 08:44:43

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Starting /u01/app/oracle/product/9.2.0.1.0/bin/tnslsnr: please wait...

TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
  TNS-00517: Lost contact
   Linux Error: 32: Broken pipe

Then I have check with the SYS admin to only find out that nothing has changed with any of the network settings. Every this is ok.

This is one of those troubleshooting issues that can take several hours to track done. But as it turns out, the most common reason for this stack trace when attempting to start the listener is the log file for the listener ($ORACLE_HOME/network/log/listener.log) has reached its 2GB file size limit on Linux. To alleviate the problem, simply archive or delete the file and restart the listener.

How to solve this problem:


Switch to the network log directory
[oracle@contentserver log]$ cd /u01/app/oracle/product/9.2.0.1.0/network/log

See the size of the listener.log, here I have found 2.1 GB
[oracle@contentserver log]$ du -h listener.log
2.1G    listener.log

Copy the log file to the other directory if you need to backup this file for any kind of troubleshooting task.
[oracle@contentserver log]$ cp listener.log /home/oracle/

Then remove the file using OS command, to do this issue the following command.
[oracle@contentserver log]$ rm listener.log

Now start the listener service.
[oracle@contentserver log]$ lsnrctl start

Sunday, December 13, 2009

DBMS_JOB package

The DBMS_JOB package allows a user to schedule a job to run at a specified time.  A job is submitted to a job queue and runs at the specified time.  The user can also input a parameter that specifies how often the job should run.  A job can consist of any PL/SQL code.
Source: {ORACLE_HOME}/rdbms/admin/dbmsjob.sq

SNP Background Processes


SNP processes run in the background and implement database snapshots and job queues.  If an SNP process fails, Oracle restarts it without affecting the rest of the database.  An SNP process can run one job at a time with a maximum of ten SNP processes running simultaneously.  The INIT.ORA initialization file contains three parameters that control the behavior of the SNP processes:



















ParametersDescription
JOB_QUEUE_PROCESSESHow many processes to start.  If set to zero, no jobs are executed. Default is 0.  Range is 0..1000.
JOB_QUEUE_INTERVAL (obsolete in 9i)How long an interval the process will sleep before checking for a new job. Default is 60 sec.  Range is 1..3600 sec
JOB_QUEUE_KEEP_CONNECTIONSControls whether an SNP process closes any remote database connections. Default is False.  Range is True/False.

Job Owner


The person who submits a job to the job queue is considered the job's owner. The job's owner is the only one who can alter the job, force the job to run, or remove the job from the queue.  If you need to look up any information regarding a submitted job, DBA_JOBS and USER_JOBS contain a lot of information such as the job number, user, status, etc.

Dependencies


 job$            
·         dba_jobs      
·         all_jobs      
·         user_jobs
·         dba_jobs_running      
·         all_jobs_running      
·         user_jobs_running

Job Intervals


·         Execute daily          'SYSDATE + 1'
·         Execute once per week  'SYSDATE + 7'
·         Execute hourly         'SYSDATE + 1/24'
·         Execute every 10 min.  'SYSDATE + 10/1440'
·         Execute every 30 sec.  'SYSDATE + 30/86400'
·         Do not re-execute      NULL

Changing Scheduled Jobs


The DBMS_JOB.CHANGE procedure alters a specified job that has already been submitted to the job queue. The job description, the time at which the job will be run, the interval between executions of the job, and the instance and force of a job can all be altered.

Syntax:
DBMS_JOB.CHANGE (
job       IN  BINARY_INTEGER,
what      IN  VARCHAR2,
next_date IN  DATE,
interval  IN  VARCHAR2,
instance  IN  BINARY_INTEGER DEFAULT NULL,
force     IN  BOOLEAN DEFAULT FALSE);

job- Number of the job being run.
What - PL/SQL procedure to run.
next_date - Date of the next refresh.
interval - Date function evaluated immediately before the job starts running.
instance - When a job is submitted, specifies which instance can run the job.
This defaults to NULL, which indicates that instance affinity is not changed.
force - If this is FALSE, then the specified instance must be running.
Otherwise, the routine raises an exception.
If this is TRUE, then any positive integer is acceptable as the job instance.
SQL Code:
EXECUTE DBMS_JOB.CHANGE(1, null, sysdate+3, null);

Change the WHAT


The definition of a job can be altered by calling the DBMS_JOB.WHAT procedure.

Syntax:
 DBMS_JOB.CHANGE (
job    IN  BINARY_INTEGER,
what   IN  VARCHAR2);

SQL Code:
execute DBMS_JOB.WHAT(3,Begin SEND_MESSAGE(); End;');

Changing the Next Date


The DBMS_JOB.NEXT_DATE procedure alters the next execution time for a specified job.

Syntax:
DBMS_JOB.NEXT_DATE (JOB IN BINARY_INTEGER,
NEXT_DATE  IN DATE);

SQL Code:
execute DBMS_JOB.NEXT_DATE(4,SYSDATE+3);

Changing the Interval


The DBMS_JOB.INTERVAL procedure alters the interval between executions for a specified job. 

Syntax:
DBMS_JOB.INTERVAL (job         IN  BINARY_INTEGER,
                   interval  IN  VARCHAR2); 


Assign job to RAC instance


Assign a specific RAC instance to execute a job:
Syntax:
dbms_job.instance(
job      IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force    IN BOOLEAN DEFAULT FALSE);

SELECT instance_number FROM gv$instance;

SQL Code:
EXECUTE  DBMS_JOB.INSTANCE(job=>123, instance=>1);
 

SQL Code:
SELECT JOB,SUBSTR(WHAT,1,35),NEXT_DATE, NEXT_SEC,INTERVAL
FROM DBA_JOBS;

ISUBMIT


Submit a job with a user specified job number
Syntax:
dbms_job.isubmit (
job       IN BINARY_INTEGER,
what      IN VARCHAR2,
next_date IN DATE,
interval  IN VARCHAR2 DEFAULT 'NULL',
no_parse  IN BOOLEAN DEFAULT FALSE);

N.B: no_parse indicates whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)
exec dbms_job.isubmit(4242, 'MYPROC', SYSDATE);
 
IS_JOBQ
dbms_job.is_jobq RETURN BOOLEAN;


SQL Code:

set serveroutput on

DECLARE
 b BOOLEAN;
BEGIN
  IF dbms_job.is_jobq THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

BROKEN Jobs


How a job becomes 'broken'

  • Oracle has failed to successfully execute the job after 16 attempts.
    or

  • You have marked the job as broken, using the procedure DBMS_JOB.BROKEN



N.B:  Once a job has been marked as broken, Oracle will not attempt to execute the job until it is either marked not broken, or forced to be execute by calling the DBMS_JOB.RUN.
Syntax:
dbms_job.broken (
job       IN BINARY_INTEGER,
broken    IN BOOLEAN,
next_date IN DATE DEFAULT SYSDATE);

Run a job now


To force immediate job execution:
EXEC dbms_job.run(job_no);

Mark a job as not broken


If a job fails to successfully execute it will go into a broken state after 16 attempts. To reset the job use following command
EXEC dbms_job.broken(jobno, FALSE);

Monitor User jobs


See created jobs:
SQL Code:
SELECT  job, next_date,
        next_sec,
        failures,
        broken,
        SUBSTR(what,1,40) DESCRIPTION
FROM user_jobs; 

Jobs that are currently running:


SQL Code:
select
job                               j,
to_char ( last_date, 'hh24:mi:ss' ) last,
to_char ( this_date, 'hh24:mi:ss' ) this,
to_char ( next_date, 'hh24:mi:ss' ) next,
broken                            b,
failures                          f,
interval,
what
from user_jobs;

Remove a submitted job


Syntax: 
dbms_job.remove(job IN BINARY_INTEGER);
EXECUTE DBMS_JOB.REMOVE(jobno);

Example of DBMS_JOBS


--Job For Email 
declare
   jobno integer;
begin
   dbms_job.submit(
      job => jobno,
           what => 'begin PROC_SEND_EMAIL_AT_11_45(); end;',
         --       provide resolution to the second
         --                midnight        hour of  minute of         units per day
         --             of current day    day to run         hr to run         ( 1 second )
      next_date => trunc(sysdate) + ((23 * (60*60) + ( 60*45)) * ( 1/(60*60*24))),
      interval =>  'trunc(sysdate + 1) + ((23 * (60*60) + ( 60*50)) * ( 1/(60*60*24)))'
   );
   commit;
end;
/

What Sessions are Running the Jobs


set linesize 250
col sid            for 9999     head 'Session|ID'
col spid                        head 'O/S|Process|ID'
col serial#        for 9999999  head 'Session|Serial#'
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60
select j.sid,
s.spid,
s.serial#,
       j.log_user,
       j.job,
       j.broken,
       j.failures,
       j.last_date||':'||j.last_sec last_date,
       j.this_date||':'||j.this_sec this_date,
       j.next_date||':'||j.next_sec next_date,
       j.next_date - j.last_date interval,
       j.what
from (select djr.SID,
             dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj, dba_jobs_running djr
       where dj.job = djr.job ) j,
     (select p.spid, s.sid, s.serial#
          from v$process p, v$session s
         where p.addr  = s.paddr ) s
where j.sid = s.sid;

Viewing scheduled dbms_jobs


set linesize 250
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60

select j.log_user,
     j.job,
     j.broken,
     j.failures,
     j.last_date||':'||j.last_sec last_date,
     j.this_date||':'||j.this_sec this_date,
     j.next_date||':'||j.next_sec next_date,
     j.next_date - j.last_date interval,
     j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj) j;

What Jobs are Actually Running


set linesize 250
col sid            for 9999     head 'Session|ID'
col log_user       for a10
col job            for 9999999  head 'Job'
col broken         for a1       head 'B'
col failures       for 99       head "fail"
col last_date      for a18      head 'Last|Date'
col this_date      for a18      head 'This|Date'
col next_date      for a18      head 'Next|Date'
col interval       for 9999.000 head 'Run|Interval'
col what           for a60
select j.sid,
       j.log_user,
       j.job,
       j.broken,
       j.failures,
       j.last_date||':'||j.last_sec last_date,
       j.this_date||':'||j.this_sec this_date,
       j.next_date||':'||j.next_sec next_date,
       j.next_date - j.last_date interval,
       j.what
from (select djr.SID,
             dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
             dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
             dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
        from dba_jobs dj, dba_jobs_running djr
       where dj.job = djr.job ) j;

Thursday, December 10, 2009

Oracle UTL_SMTP And UTIL_MAIL Service

The UTL_MAIL or UTL_SMPT package is introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP.  In order to use Oracle UTL_MAIL package we have to set a new init.ora parameter or spinit.ora if database running in spfile “SMTP_OUT_SERVER” set to outgoing mail server. Make sure that there is a getaway for the outgoing mail.

Step 1: Send Mail in Linux Server


[root@vasdbsrv mail]# rpm -qa |grep sendmail
sendmail-8.13.1-3.RHEL4.5
sendmail-cf-8.13.1-3.RHEL4.5

To verify the mail service running in the local host
[oracle@vasdbsrv ~]$ telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
220 vasdbsrv.ring.com.bd ESMTP Sendmail 8.13.1/8.13.1; Thu, 10 Dec 2009 14:54:51 +0600

If sendmail not installed then install it by using following command.
$ rpm –ivh sendmail

Step 2: Make sure the send mail service run on kick start.


[root@vasdbsrv mail]# chkconfig --list |grep sendmail

sendmail        0:off   1:off   2:on    3:on    4:on    5:on    6:off

Add service in checkcofig:
$ chkconfig  --add sendmail
$ chkconfig  --level 345 sendmail on

Step 3: Configure the /etc/mail/sendmail.cf


If you are running mail server in private IP
[oracle@vasdbsrv ~]$ vi /etc/mail/sendmail.cf
# "Smart" relay host (may be null)
DSmail.dnsgroup.net

Step 4: Configure /etc/resolv.conf


[root@vasdbsrv mail]# vi /etc/resolv.conf
search ring.com.bd
nameserver 203.188.191.5

Follow the simple steps to send an email using UTL_MAIL package

Step 5: Install UTL_MAIL or UTL_SMTP package


To install the UTL_MAIL and UTL_SMTP package, run the below files as user "SYS"

Source:
$ORACLE_HOME/rdbms/admin/utlmail.sql
$ORACLE_HOME/rdbms/admin/utlsmtp.sql
$ORACLE_HOME/rdbms/admin/prvtmail.plb

Step 6: Grant permissions


Grants the execute permission on UTL_MAIL privilege to PUBLIC or the user which will use the package.

Run the beow command as user “SYS”
 SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
-or-
SQL> GRANT EXECUTE ON utl_smtp TO <USER NAME>;

Step 7: Set SMTP_OUT_SERVER parameter


 SQL> ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=both;

Step 8: Create procedure to send email


 create or replace PROCEDURE send_mail_smtp
(   sender     IN VARCHAR2,
recipient  IN VARCHAR2,
subject    IN VARCHAR2,
message    IN LONG
)
IS
   mailhost     VARCHAR2(30) := 'localhost';  -- -- host mail address
   mail_conn    utl_smtp.connection ;
   crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
   mesg long;
BEGIN
   mail_conn := utl_smtp.open_connection(mailhost, 25);

   mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
          'From:  <'||sender||'>' || crlf ||
          'Subject: '||subject || crlf ||
          'To: '||recipient || crlf ||
          '' || crlf || message;
   utl_smtp.helo(mail_conn, mailhost);
   utl_smtp.mail(mail_conn, sender);
   utl_smtp.rcpt(mail_conn, recipient);
   utl_smtp.data(mail_conn, mesg);
   utl_smtp.quit(mail_conn);

   EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
       dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
       dbms_output.put_line(' Temporary e-mail issue - try again');
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
       dbms_output.put_line(' Permanent Error Encountered.'); 
END;

To Run the SEND_MAIL_SMTP run the following code.
BEGIN
SEND_MAIL_SMTP(
'tamimdba@yahoo.com',           --Sender
'tamimdba@gmail.com',           --Recipient
'Test Mail',                    --Subject
'Send From Oracle10g Database'  --Message
);
END;
PL/SQL procedure successfully completed.

Step 5: Send email using UTL_MAIL with attachments


To send attachment we are using UTL_MAIL package of Oracle.
you must set UTL_FILE_DIR to a directory, where the attachment files exists
SQL> show parameter UTL_FILE_DIR
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /u02/attachment_dir

You must set the UTL_FILE_DIR to send the attachment. Also need to create a directory using create directory command.
 
SQL> Alter System Set UTL_FILE_DIR = ‘/u02/attachment_dir’;

create or replace
PROCEDURE send_mail_attach AS
BEGIN
  UTL_MAIL.SEND_ATTACH_VARCHAR2(
                      sender =>       'tamimdba@yahoo.com',
                      recipients =>   'tamimdba@gmail.com',
                      cc =>           'tamim@dnsgroup.net',
                      bcc =>          'tamimdba@hotmail.com',
                      subject =>      'Attachment Test Mail',
                      message =>      'This is just a test mail with attachment.',
                      attachment =>   'text',
                      att_filename => 'attach.txt');
  EXCEPTION
      WHEN UTL_SMTP.INVALID_OPERATION THEN
       dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
      WHEN UTL_SMTP.TRANSIENT_ERROR THEN
       dbms_output.put_line(' Temporary e-mail issue - try again');
      WHEN UTL_SMTP.PERMANENT_ERROR THEN
       dbms_output.put_line(' Permanent Error Encountered.'); 
      WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error has occurred: ' || sqlerrm);
END;

Changing Hostname on RHEL

It is possible to change the hostname without reboot; this is very helpful in Production environment.


Here I have change the hostname of the server without restarting on RHEL

Step 01: Change the HOSTNAME line in /etc/sysconfig/network


[root@aca800de root]# vi /etc/sysconfig/network
HOSTNAME=vasappserver1

Step 02: Change the hostname (FQDN and alias) in /etc/hosts


[root@aca800de root]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1         localhost.localdomain         localhost
172.168.0.222    vasappserver1.ring.com.bd     vasappserver1

Step 03: Run /bin/hostname new_hostname for the hostname change to take effect immediately.


[root@aca800de root]# /bin/hostname vasappserver1
[root@aca800de root]# hostname
vasappserver1

Step 04: Run /sbin/service syslog restart for syslog to log using the new hostname.


[root@aca800de root]# service syslog restart
Shutting down kernel logger:                               [  OK  ]
Shutting down system logger:                               [  OK  ]
Starting system logger:                                    [  OK  ]
Starting kernel logger:                                    [  OK  ]

Wednesday, December 9, 2009

Add OS User to the Wheel Group.

Step 01: Add user and set the password


[root@Teletalk ~]# adduser tamim
[root@Teletalk ~]# passwd  tamim
Changing password for user tamim.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:

Step 02: Add user Oracle and Tamim user to the wheel Group


visudo is a wheel user group config file User privilege specification
[root@Teletalk ~]# usermod -G wheel oracle
[root@contentserver root]# id oracle
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),10(wheel),501(dba)

[root@Teletalk ~]# usermod -G wheel tamim
[root@Teletalk ~]# id tamim
uid=500(tamim) gid=500(tamim) groups=500(tamim),10(wheel)

Step 03: Uncomment the Wheel group in /etc/pam.d/su


[root@Teletalk ~]# vi /etc/pam.d/su
auth       required     /lib/security/$ISA/pam_wheel.so use_uid

Step 04: Modify the the /etc/ssh/sshd_config file.


Add following Line (AllowUsers  oracle tamim) in the sshd_config file which allow oracle and tamim to login, Also uncomment PermitRootLogin and set PermitRootLogin no rather then yes
[root@Teletalk ~]# vi /etc/ssh/sshd_config
AllowUsers  oracle tamim

PermitRootLogin no

Save the file Ctrl+X then Y then enter

Step 05: Restart the sshd Service


[root@Teletalk ~]# service sshd restart
Stopping sshd:                                             [  OK  ]
Starting sshd:                                             [  OK  ]

Now you can’t login as a root user remotely by using ssh client or putty. You have to login as a oracle or tamim the swatch user to root.

How Can a DBA switch to another database user without a password?

Users normally use the "CONNECT" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to bridge Oracle's security.

Say for example we are connecting with Scott user without knowing the password, after complete the task we have to restore the password of Scott.
[oracle@vasdbsrv ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 9 13:07:58 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> CONNECT / as sysdba
Connected.
SQL> SELECT password FROM dba_users WHERE username='SCOTT';
PASSWORD
------------------------------
F894844C34402B67
SQL> ALTER USER scott IDENTIFIED BY tamimdba;
User altered.

SQL> CONNECT scott/tamimdba
Connected.

SQL> select * from emp;
[You will get data of scott user]

OK, we're in. Let's quickly change the password back before anybody notices.
SQL> ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';
User altered.

SQL> conn scott/tiger
Connected.


Friday, December 4, 2009

GATHER Schema, Table, Index STATS

PL/SQL Packages DBMS_STATS Gather, View, Modify or Delete optimizer statistics for database objects. From Oracle 8i the DBMS_STATS package is the preferred method Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers.

Oracle Cost Based Optimizer (CBO) requires statistics about data storage and distribution to generate accurate execution plans for queries. These statistics are generated using the DBMS_STATS package. Also, while using the CBO, histograms are used to store detailed information about data distributions which are non-uniform. This information helps the optimizer better estimate the selectivity of predicates which will result in more efficient execution plans. It is useful to create histograms when the application uses queries having:

Procedure Name Description
GATHER_TABLE_STATS Gathers statistics for a table and its indexes
GATHER_INDEX_STATS Gathers statistics for an index
GATHER_SCHEMA_STATS Gathers statistics for all objects in a schema
GATHER_DATABASE_STATS Gather statistics for all objects in the database

Syntax:

exec DBMS_STATS.GATHER_SCHEMA_STATS(

        ownname,
        estimate_percent,
        block_sample ,
        method_opt,
        degree,
        granularity,
        cascade,stattab,
        statid,options,
        statown ,
        no_invalidate,
        gather_temp,
        gather_fixed);
 
 
Code examples:
EXEC DBMS_STATS.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname =>'SCOTT',
               estimate_percent  =>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname =>'SCOTT',
               estimate_percent => 25);

To delete the schema stat information of Scott schema run following query

EXEC DBMS_STATS.delete_schema_stats('SCOTT');
 

To get Table Stats run the following query.

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');
select table_name,column_name,num_distinct,last_analyzed,num_buckets
from user_tab_cols
where table_name like ('EMP');
 
[N.B: you can also take information from user_tables data dictionary table]

To get Index Stats run the following query

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'PK_EMP');
select table_name,index_name,num_rows,last_analyzed
from user_indexes
where table_name like ('EMP');

Thursday, December 3, 2009

Export (exp) and Import (imp)

Oracle's export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database.
In order to use exp and imp in Oracle Database you have to run catexp.sql script. catexp.sql basically creates the exp_full_database and imp_full_database roles to the oracle user. We have to run catexp.sql for the time first time if database not create using DBCA. Because it is already executed if you create your database by DBCA.

Look for the "imp" and "exp" executables in your $ORACLE_HOME/bin directory. These parameters can be listed by executing the following commands: "exp help=yes" or "imp help=yes".

Source: $ORACLE_HOME/rdbms/admin/catexp.sql

Full database export:
The EXP_FULL_DATABASE and IMP_FULL_DATABASE respectively, are needed to perform a full export.
Use the full=yes export parameter for a full export.
exp scott/tiger file=emp.dmp full=yes statistics=none
imp system/manager file=emp.dmp fromuser=scott touser=scott

Tablespace:

Use the tablespaces export parameter for a tablespace export. It is only apply to transportable tablespaces.

exp userid='system/manager' tablespaces=users file=exp.dmp log=tbs.log statistics=none

Schema:
This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the fromuser import parameter for a user (owner) export-import.

Table:
Specific tables (and partitions) can be exported/imported with table export mode.
Use the tables export parameter for a table export.

Example :
exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
exp scott/tiger file=emp.dmp tables=(emp,dept)

imp scott/tiger file=emp.dmp full=yes
imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Using a parameter file:
exp userid=scott/tiger@orcl parfile=export.txt

export.txt contains:

BUFFER=100000
FILE=scott_data.dmp
FULL=n
OWNER=scott
GRANTS=y
COMPRESS=y

Take DDL output in Dump file:
imp scott/tiger file= emp.dmp indexfile=emp.txt

or, to see ddl into screen,
imp scott/tiger file=emp.dmp show=y

How can one improve Import/ Export performance?


EXPORT (exp):

  • Set the BUFFER parameter to a high value (e.g. 2Mb -- entered as an integer "2000000")

  • Set the RECORDLENGTH parameter to a high value (e.g. 64Kb -- entered as an integer "64000")

  • Use DIRECT=yes (direct mode export)

  • Stop unnecessary applications to free-up resources for your job.

  • If you run multiple export sessions, ensure they write to different physical disks.

  • DO NOT export to an NFS mounted file system.  It will take forever.


IMPORT (imp):

  • Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

  • Place the file to be imported on a separate physical disk from the oracle data files

  • Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file

  • Set the LOG_BUFFER to a big value and restart oracle.

  • Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)

  • Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

  • Use COMMIT=N in the import parameter file if you can afford it

  • Use STATISTICS=NONE in the import parameter file to avoid time consuming to import the statistics

  • Remember to run the indexfile previously created


What are the common Import/ Export problems?



  • ORA-00001: Unique constraint (...) violated

    • You are importing duplicate rows. Use IGNORE=YES to skip tables that already exist (imp will give an error if the object is re-created).



  • ORA-01555: Snapshot too old

    • Ask your users to STOP working while you are exporting or try using parameter CONSISTENT=NO



  • ORA-01562: Failed to extend rollback segment

    • Create bigger rollback segments or set parameter COMMIT=Y while importing



  • IMP-00015: Statement failed ... object already exists...

    • Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.





 Source www.orafaq.com

Tuesday, December 1, 2009

Oracle's PL/SQL Lock Timer

The amount of time a user or application has slept through the USER_LOCK.SLEEP or DBMS_LOCK.SLEEP procedures.
When investigating Oracle Wait-Events we often look at those events where applications consume precious system resources or are in direct contention with other users or applications. These resource waits revolve around excessive CPU, memory, or disk usage. Events that spin for Oracle internal structures such as latches or enquires can also be of concern. As their name suggests, Wait-Events DO 'wait' on some event to complete before a user or application can continue with its work. Contention for system resources will overwhelm Oracle's ability to immediately process the SQL and ultimately cause applications to accumulate time in resource specific Wait-Events.

On the flip-side of resource waits there are what are called idle events. These events do not wait for any specific resource but record the time Oracle is waiting for a work request from the application. Many practitioners of Oracle performance will not even look at these idle events because they do not consume resources and are not limiting what Oracle can do at any given point in time. Some will even go so far as to state that the idle events have no meaningful information. This is not necessarily true. Take for instance the idle event 'SQL*Net message from client'. This idle event is not inhibiting the database server from performing work but, as many agree, is an indication of poor response from client to database server. While idle events are not the result of direct resource contention, they are an accurate measure of accumulated delays in the application imposed by Oracle.

The 'idle' event 'PL/SQL lock timer' is worth watching because it points us in the direction of application response, throughput, and possible coding issues. The PL/SQL lock timer event is, as the command that issues it states, a sleep mandated by the application code. The application is idle and doing nothing. This means that if the application sleeps for a combined interval of 5 minutes, the response to the user or complete run of the application will take at least 5 minutes longer.

While this event does not require resources and thus is not an Oracle resource contention issue, it is our job to provide the quickest response to users and push applications through our system to increase the user's perceived performance. Most often, sleeps are put into an application for serialization of transactional events or spinning on queues until something happens. When the event occurs or a queue is populated, then the application continues its work. We should ask ourselves why an application is sleeping for any amount of time and provide alternatives if necessary to reduce this idle event. You will gain quicker responses if you trigger an action instead of the action waiting for an event. How to look at the PL/SQL lock timer event To initiate a sleep for the current session for five seconds.

Oracle has four separate ways to induce a sleep into PL/SQL.

Source : {ORACLE_HOME}/rdbms/admin/dbmslock.sql
 

Name             Description

nl_mode          NuLL Value 1

ss_mode          Sub Shared: used on an aggregate object to indicate that share locks are being acquired on subparts of the object value 2

sx_mode          Sub eXclusive: used on an aggregate object to indicate that exclusive locks are being acquired on sub-parts of the object value 3

s_mode            Shared: indicates that the entire aggregate object has a share lock, but some of the sub-parts may additionally have exclusive locks value 4

ssx_mod          Shared SubeXclusive value 5

x_mode            eXclusive value 6

Grant access DBMS_LOCK to all users
GRANT EXECUTE ON DBMS_LOCK TO PUBLIC

Code examples

BEGIN
  dbms_output.put_line( 'Start Time: ' || dbms_utility.get_time );
  dbms_lock.sleep(seconds => 10);
  dbms_output.put_line( 'End Time:   ' || dbms_utility.get_time );
END;
/
Start Time: 1119218
End Time:   1120218

SQL>  execute sys.dbms_lock.sleep(5);

To take a look at current sessions that are using the SLEEP command. Notice that the column P1 in V$SESSION does not represent the amount of time the session has slept but in fact represents the duration this session will sleep. Also note that this column is in centiseconds and as such the five seconds issued for a sleep has been translated in to 500 centiseconds.
SQL>  select osuser,event,p1
       from v$session
      where event = 'PL/SQL lock timer'

OSUSER       EVENT             P1
------------ ----------------- ---
oracle       PL/SQL lock timer 500

If you wanted to take a look at the total amount of time that has accumulated for this type of wait event, you can look at the V$SYSTEM_EVENT view. Also note that in this particular view the time waited is also in centiseconds but as you can see the time actually waited may be more that expected. On our test system this equated to about 1.024 seconds to 1 second requested.
SQL>  select time_waited
      from v$system_event
      where event = 'PL/SQL lock timer';

SQL>  select package_name, argument_name,
             data_type, data_length,
       data_precision, pls_type
from  all_arguments
where object_name = 'SLEEP'
order by 1;

To find the Dependencies
SQL>  SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_LOCK'
UNION
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_LOCK';

Oracle 11g's data dictionary defines the DBA_LOCK view using the following SQL
SQL> select
sid session_id,
decode(type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
type) lock_type,
decode(lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(lmode)) mode_held,
decode(request,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2,
ctime last_convert,
decode(block,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
to_char(block)) blocking_others
from v$lock

Data Dictionary Views of LOCK


DBA_LOCK
Source: $ORACLE_HOME/rdbms/admin/catblock.sql

Related Parameters:

_enqueue_locks
dml_locks                         Specifies the maximum number of DML locks--one for each table modified in a transaction.
enqueue_resources      Sets the number of resources that can be concurrently locked by the lock manager.

Related Commands:
ALTER TABLE DISABLE TABLE LOCK

Related Views:

V$_LOCK

V$ACCESS       Displays objects in the database that are currently locked and the sessions that are accessing them.

V$ENQUEUE_LOCK Displays all locks owned by enqueue state objects.

V$ENQUEUE_STAT Contains detailed enqueue statistics for each enqueue.

V$GLOBAL_BLOCKED_LOCKS Displays global blocked locks.

V$LOCK        Lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.

V$LOCKED_OBJECT    Lists all locks acquired by every transaction on the system.

V$RESOURCE         Contains resource name and address information

V$RESOURCE_LIMIT   Displays information about current and maximum global resource utilization for some system resources.

V$TRANSACTION_ENQUEUE   Displays locks owned by transaction state objects.

DBA_BLOCKERS       Shows all sessions holding a lock which is required by another process.

DBA_DDL_LOCKS      Lists all DDL locks held in the database and all outstanding requests for a DDL lock.

DBA_DML_LOCKS      Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.

DBA_KGLLOCK        Lists all the locks and pins held on KGL objects (objects in the Kernel Generic Library cache).

DBA_LOCK_INTERNAL Displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

DBA_WAITERS        Shows all sessions waiting for locks and the session that holds the lock.

DBMS_LOCK_ALLOCATED     Describes user-allocated locks.