Tuesday, September 25, 2018



Get a list of all functions and procedures in an Oracle database



SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE')
The column STATUS tells you whether the object is VALID or INVALID. If it is invalid, you have to try a recompile, ORACLE can't tell you if it will work before.

Thursday, September 20, 2018


OS Watcher Install & Run

Installing OSWbb

To install OSWbb:
  1. Log on to My Oracle Support (MOS) at https://support.oracle.com.
  2. Download the file oswbb601.tar, which is available at https://support.oracle.com/epmos/main/downloadattachmentprocessor?attachid=301137.1:OSW_file.
  3. Copy the file to the directory where you want to install OSWbb, and run the following command:
    # tar xvf oswbb601.tar

    Extracting the tar file creates a directory named oswbb, which contains all the directories and files that are associated with OSWbb, including the startOSWbb.sh script.
  4. If the ksh package is not already installed on your system, use yum to install it.
    # yum install ksh
  5. Create a symbolic link from /usr/bin/ksh to /bin/ksh.
    # ln -s /bin/ksh /usr/bin/ksh

    This link is required because the OSWbb scripts expect to find ksh in /usr/bin.
  6. To enable the collection of iostat information for NFS volumes, edit the OSWatcher.sh script in the oswbb directory, and set the value of nfs_collect to 1:
    nfs_collect=1

Running OSWbb

To start OSWbb, run the startOSWbb.sh script from the oswbb directory.
# ./startOSWbb.sh [frequency duration]
The optional frequency and duration arguments specifying how often in seconds OSWbb should collect data and the number of hours for which OSWbb should run. The default values are 30 seconds and 48 hours. The following example starts OSWbb recording data at intervals of 60 seconds, and has it record data for 12 hours:
# ./startOSWbb.sh 60 12
Testing for discovery of OS Utilities
.
.
.
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
NETSTAT found on your system.
TOP found on your system.
Testing for discovery of OS CPU COUNT
.
.
.
Starting Data Collection...
oswbb heartbeat: date/time
oswbb heartbeat: date/time + 60 seconds
.
.
.
To stop OSWbb prematurely, run the stopOSWbb.sh script from the oswbb directory.
# ./stopOSWbb.sh
OSWbb collects data in the following directories under the oswbb/archive directory:
Directory
Description
oswiostat
Contains output from the iostat utility.
oswmeminfo
Contains a listing of the contents of /proc/meminfo.
oswmpstat
Contains output from the mpstat utility.
oswnetstat
Contains output from the netstat utility.
oswprvtnet
If you have enable private network tracing for RAC, contains information about the status of the private networks.
oswps
Contains output from the ps utility.
oswslabinfo
Contains a listing of the contents of /proc/slabinfo.
oswtop
Contains output from the top utility.
oswvmstat
Contains output from the vmstat utility.
OSWbb stores data in hourly archive files named system_name_utility_name_timestamp.dat, and each entry in a file is preceded by the characters *** and a timestamp.


Tuesday, September 18, 2018


The Node Manager is already up.ERROR:
Unable to start up the managed server forms_server1

Oracle Apps R12 (12.2.2.5) Application startup after the clone

How to solve java.lang.OutOfMemoryError: unable to create new native thread


Solution

Check number of processes per user


$ ps -elf | wc -l

220


-------------------------------------------------------------------------------------
If you try to run ps with a -T you will see all of the threads as well:


$ ps -elfT | wc -l
385


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


you need to increase the number of processes for the user. This can be done with the following command:



$ ulimit -u 4096


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



Check your threads PID limit

Once that you have counted the number of threads, then you should verify that you are not hitting system limits, specified by the kernel.pid_max limit parameter. You can check this value by executing:


$ sysctl -a | grep kernel.pid_max

kernel.pid_max = 32768



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





Tuesday, September 4, 2018


AWR ADDM and ASH Report Generation in 11g | Oracle Performance Tuning


1) AWR Report:

Before creating any single report, make sure snapshot capturing is enable on your database. If not, you can run the below mentioned command to create one:

exec dbms_workload_repository.create_snapshot;

The Report will be generated on the basis of the snapshots taken. Take 4-5 snapshots or run the above mentioned command to create snapshots each in 5 minutes duration at least. Run the below mentioned query to check the snapshots created in your database,

SQL> select snap_id, snap_level, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin from dba_hist_snapshot order by 1;

   SNAP_ID SNAP_LEVEL BEGIN
---------- ---------- -----------------
       465          1 19/07/17 18:30:50
       466          1 19/07/17 18:41:57
       467          1 19/07/17 19:07:33
       468          1 19/07/17 19:08:30

Now find the below mentioned guidelines to check the creation of AWR Report,

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

You can create snap using the below
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Below query help to find snaps information
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;

2) ADDM Report:

Below mentioned is the script run to generate ADDM Report

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

3) ASH Report:

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql


Monday, September 3, 2018



ORA-01555: snapshot too old: rollback segment number 41 with name "_SYSSMU41_1930078339$" too small



SQL> select (62057/60)/60 query,(25000/60)/60 retention
  2    from dual
  3  /

     QUERY  RETENTION
---------- ----------
17.2380556 6.94444444
Yours query exectuing tenure is 17 hours while yours undo retention is approximate 7 hours,change yours retention period to 20% extra with yours query execution tenure and then check yours query again.

atleast set undo retention to 7500


SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

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

SQL>  alter system set undo_retention=75000 scope=both;

System altered.

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

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     75000



Tuesday, August 14, 2018


Configuring sudo Access

  1. Log in to the system as the root user.
  2. Create a normal user account using the useradd command. Replace USERNAME with the user name that you wish to create.
    # useradd USERNAME
  3. Set a password for the new user using the passwd command.
    # passwd USERNAME
    Changing password for user USERNAME.
    New password: 
    Retype new password: 
    passwd: all authentication tokens updated successfully.
  4. Run the visudo to edit the /etc/sudoers file. This file defines the policies applied by the sudo command.
    # visudo
  5. Find the lines in the file that grant sudo access to users in the group wheel when enabled.
    ## Allows people in group wheel to run all commands
    # %wheel        ALL=(ALL)       ALL
  6. Remove the comment character (#) at the start of the second line. This enables the configuration option.
  7. Save your changes and exit the editor.
  8. Add the user you created to the wheel group using the usermod command.
    # usermod -aG wheel USERNAME
  9. Test that the updated configuration allows the user you created to run commands using sudo.
    1. Use the su to switch to the new user account that you created.
      # su USERNAME -
    2. Use the groups to verify that the user is in the wheel group.
      $ groups
      USERNAME wheel
    3. Use the sudo command to run the whoami command. As this is the first time you have run a command using sudo from this user account the banner message will be displayed. You will be also be prompted to enter the password for the user account.
      $ sudo whoami
      We trust you have received the usual lecture from the local System
      Administrator. It usually boils down to these three things:
      
          #1) Respect the privacy of others.
          #2) Think before you type.
          #3) With great power comes great responsibility.
      
      [sudo] password for USERNAME:
      root
      The last line of the output is the user name returned by the whoami command. If sudo is configured correctly this value will be root.
You have successfully configured a user with sudo access. You can now log in to this user account and use sudoto run commands as if you were logged in to the account of the root user.

Thursday, August 2, 2018


Configure Postfix SMTP relay office365 on OEL 6 & 7


https://linuxhowtoguide.blogspot.com/2017/03/how-to-configure-postfix-smtp-relay.html
https://www.onceuponanipsum.com/relay-mail-with-office-365-and-postfix/

Install Postfix using command below

[root@localhost ~]# yum install postfix cyrus-sasl-plain mailx

1. vi /etc/postfix/main.cf

relayhost = [smtp.office365.com]:587
mynetworks = 127.0.0.0/8
inet_interfaces = loopback-only
smtp_use_tls = yes
smtp_always_send_ehlo = yes
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_sasl_tls_security_options = noanonymous
smtp_tls_security_level = encrypt
smtp_generic_maps = hash:/etc/postfix/generic
#smtp_tls_CAfile = /etc/ssl/certs/ca-certificates.crt

2. vi /etc/postfix/sasl_passwd
[smtp.office365.com]:587 o365test@expolanka.com:P@ssw0rd

3. postmap /etc/postfix/sasl_passwd

4. chown root:postfix /etc/postfix/sasl_passwd

5. chmod 640 /etc/postfix/sasl_passwd

6. vi /etc/postfix/generic
    root@edbtest.expolanka.com o365test@expolanka.com

7. chown root:root /etc/postfix/generic

8. chmod 0600 /etc/postfix/generic

9. postmap /etc/postfix/generic

10. vi /etc/aliases
mailer-daemon:  postmaster
postmaster:     root
root:   o365test@expolanka.com


11. [root@localhost ~]# newaliases


12. service postfix restart

13. echo "this is a test gmail" | mail -s "test message gmail" prmdth@gmail.com
    echo "this is a test o365" | mail -s "test message o365" o365test@expolanka.com
    echo "This is the body of the email"| mail -r"o365 mail<o365test@expolanka.com>" -s "This is the subject(E-Mail from SMTP Relay) line" pramudithad@itx360.com

14. Troubleshoot
    tail -f /var/log/maillog

 RMAN Recovery Catalog About Recovery Catalog RMAN recovery catalog Is another database which is out of your normal databases or which is o...