Sunday, February 21, 2021

How To Kill runaway processes After Terminating Concurrent Request

 

Every concurrent Request uses some resources for running.

 

If we decided and terminate the conc request which is taking long time and not progress any more further after terminate it from front end, some times the resources may not be released soon. these processes are called runway process which will be create blocking other sessions.


so we need to manually kill the processes at DB and OS level.

 

Once terminate the concurrent request from the front end.

 

Then


SQL>select request_id,oracle_process_id,os_process_id from fnd_concurrent_requests where request_id=’&Req_Id’;
SQL>select p.spid , s.sid , s.serial# from v$session s , v$process p where s.paddr = p.addr and s.process = &os_process_id ;

 

Kill session id in Database

SQL> alter system kill session ‘session-id,session-serial’

 

killpid at OS level
$ kill -9 <server pid>

 

 

 

Wednesday, February 17, 2021

Installation of Grid Infrastructure for a Standalone server (Oracle Restart) 12c (12.1.0.2)

 

 

Stage-1 Oracle Grid and DB Installation Prerequisites


Step:-1 Oracle Installation Prerequisites

 

Besides installating the dependent packages, preinstall package would automatically set the kernel parameters and create the 'oracle' user and user groups such as 'oinstall' and 'dba'.

yum install oracle-rdbms-server-12cR1-preinstall –y

[root@oracleasm Share]# yum install oracle-rdbms-server-12cR1-preinstall -y

Loaded plugins: refresh-packagekit, security

ol6_UEK_latest/primary_db                                                                                                             |  68 MB     03:01    

ol6_latest                                                                                                                            | 2.7 kB     00:00    

ol6_latest/primary_db                                                                                                                 |  23 MB     01:01    

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-17.el6 will be installed

--> Processing Dependency: ksh for package: oracle-rdbms-server-12cR1-preinstall-1.0-17.el6.x86_64

--> Processing Dependency: libaio-devel for package: oracle-rdbms-server-12cR1-preinstall-1.0-17.el6.x86_64

--> Running transaction check

---> Package ksh.x86_64 0:20120801-38.el6_10 will be installed

---> Package libaio-devel.x86_64 0:0.3.107-10.el6 will be installed

--> Finished Dependency Resolution

 

Dependencies Resolved

 

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

 Package                                                 Arch                      Version                               Repository                     Size

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

Installing:

 oracle-rdbms-server-12cR1-preinstall                    x86_64                    1.0-17.el6                            ol6_latest                     20 k

Installing for dependencies:

 ksh                                                     x86_64                    20120801-38.el6_10                    ol6_latest                    761 k

 libaio-devel                                            x86_64                    0.3.107-10.el6                        ol6_latest                     13 k

 

Transaction Summary

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

Install       3 Package(s)

 

Total download size: 794 k

Installed size: 1.7 M

Downloading Packages:

(1/3): ksh-20120801-38.el6_10.x86_64.rpm                                                                                              | 761 kB     00:02    

(2/3): libaio-devel-0.3.107-10.el6.x86_64.rpm                                                                                         |  13 kB     00:00    

(3/3): oracle-rdbms-server-12cR1-preinstall-1.0-17.el6.x86_64.rpm                                                                     |  20 kB     00:00    

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

Total                                                                                                                        280 kB/s | 794 kB     00:02    

warning: rpmts_HdrFromFdno: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY

Retrieving key from http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

Importing GPG key 0xEC551F03:

 Userid: "Oracle OSS group (Open Source Software group) <build@oss.oracle.com>"

 From  : http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : libaio-devel-0.3.107-10.el6.x86_64                                                                                                        1/3

  Installing : ksh-20120801-38.el6_10.x86_64                                                                                                             2/3

  Installing : oracle-rdbms-server-12cR1-preinstall-1.0-17.el6.x86_64                                                                                    3/3

  Verifying  : oracle-rdbms-server-12cR1-preinstall-1.0-17.el6.x86_64                                                                                    1/3

  Verifying  : ksh-20120801-38.el6_10.x86_64                                                                                                             2/3

  Verifying  : libaio-devel-0.3.107-10.el6.x86_64                                                                                                        3/3

 

Installed:

  oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-17.el6                                                                                                  

 

Dependency Installed:

  ksh.x86_64 0:20120801-38.el6_10                                            libaio-devel.x86_64 0:0.3.107-10.el6                                          

 

Complete!

 

 

Step:-2 Oracle ASM Prerequisites

 

Install ASM Packages for ASm Configuration

yum install oracleasm-support

yum install kmod-oracleasm

 

 

[root@oracleasm Share]# yum install oracleasm-support

Loaded plugins: refresh-packagekit, security

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package oracleasm-support.x86_64 0:2.1.11-2.el6 will be installed

--> Finished Dependency Resolution

 

Dependencies Resolved

 

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

 Package                                    Arch                            Version                                Repository                           Size

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

Installing:

 oracleasm-support                          x86_64                          2.1.11-2.el6                           ol6_latest                           78 k

 

Transaction Summary

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

Install       1 Package(s)

 

Total download size: 78 k

Installed size: 233 k

Is this ok [y/N]: y

Downloading Packages:

oracleasm-support-2.1.11-2.el6.x86_64.rpm                                                                                             |  78 kB     00:00    

Running rpm_check_debug

Running Transaction Test

Transaction Test Succeeded

Running Transaction

  Installing : oracleasm-support-2.1.11-2.el6.x86_64                                                                                                     1/1

  Verifying  : oracleasm-support-2.1.11-2.el6.x86_64                                                                                                     1/1

 

Installed:

  oracleasm-support.x86_64 0:2.1.11-2.el6                                                                                                                   

 

Complete!

 

[root@oracleasm Share]# yum install kmod-oracleasm

Loaded plugins: refresh-packagekit, security

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package kmod-oracleasm.x86_64 0:2.0.8-16.1.el6_10 will be installed

--> Processing Dependency: kernel(__x86_indirect_thunk_rax) = 0x2ea2c95c for package: kmod-oracleasm-2.0.8-16.1.el6_10.x86_64

--> Processing Dependency: kernel(get_user_pages_fast) = 0xa2046a95 for package: kmod-oracleasm-2.0.8-16.1.el6_10.x86_64

--> Processing Dependency: kernel >= 2.6.32-754.el6 for package: kmod-oracleasm-2.0.8-16.1.el6_10.x86_64

--> Running transaction check

---> Package kernel.x86_64 0:2.6.32-754.29.1.el6 will be installed

--> Processing Dependency: kernel-firmware >= 2.6.32-754.29.1.el6 for package: kernel-2.6.32-754.29.1.el6.x86_64

--> Processing Dependency: dracut-kernel >= 004-408.el6 for package: kernel-2.6.32-754.29.1.el6.x86_64

--> Running transaction check

---> Package dracut-kernel.noarch 0:004-303.0.1.el6 will be updated

---> Package dracut-kernel.noarch 0:004-411.0.4.el6 will be an update

--> Processing Dependency: dracut = 004-411.0.4.el6 for package: dracut-kernel-004-411.0.4.el6.noarch

---> Package kernel-firmware.noarch 0:2.6.32-358.el6 will be updated

---> Package kernel-firmware.noarch 0:2.6.32-754.29.1.el6 will be an update

--> Running transaction check

---> Package dracut.noarch 0:004-303.0.1.el6 will be updated

---> Package dracut.noarch 0:004-411.0.4.el6 will be an update

--> Processing Conflict: kernel-2.6.32-754.29.1.el6.x86_64 conflicts bfa-firmware < 3.2.21.1-2

--> Restarting Dependency Resolution with new changes.

--> Running transaction check

---> Package bfa-firmware.noarch 0:3.0.3.1-1.el6 will be updated

---> Package bfa-firmware.noarch 0:3.2.23.0-2.el6 will be an update

--> Finished Dependency Resolution

 

Dependencies Resolved

 

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

 Package                                 Arch                           Version                                     Repository                          Size

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

Installing:

 kmod-oracleasm                          x86_64                         2.0.8-16.1.el6_10                           ol6_latest                          36 k

Updating:

 bfa-firmware                            noarch                         3.2.23.0-2.el6                              ol6_latest                         3.2 M

Installing for dependencies:

 kernel                                  x86_64                         2.6.32-754.29.1.el6                         ol6_latest                          32 M

Updating for dependencies:

 dracut                                  noarch                         004-411.0.4.el6                             ol6_latest                         129 k

 dracut-kernel                           noarch                         004-411.0.4.el6                             ol6_latest                          29 k

 kernel-firmware                         noarch                         2.6.32-754.29.1.el6                         ol6_latest                          29 M

 

Transaction Summary

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

Install       2 Package(s)

Upgrade       4 Package(s)

 

Total download size: 65 M

Is this ok [y/N]: y

Downloading Packages:

(1/6): bfa-firmware-3.2.23.0-2.el6.noarch.rpm                                                                                         | 3.2 MB     00:08    

(2/6): dracut-004-411.0.4.el6.noarch.rpm                                                                                              | 129 kB     00:00    

(3/6): dracut-kernel-004-411.0.4.el6.noarch.rpm                                                                                       |  29 kB     00:00    

(4/6): kernel-2.6.32-754.29.1.el6.x86_64.rpm                                                                                          |  32 MB     01:25    

(5/6): kernel-firmware-2.6.32-754.29.1.el6.noarch.rpm                                                                                 |  29 MB     01:20    

(6/6): kmod-oracleasm-2.0.8-16.1.el6_10.x86_64.rpm                                                                                    |  36 kB     00:00    

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

Total                                                                                                                        377 kB/s |  65 MB     02:55    

Running rpm_check_debug

Running Transaction Test

 

The oracleasmlib package cannot be downloaded from YUM repository:

# yum install oracleasmlib
Loaded plugins: security
ol6_latest                                               | 1.4 kB     00:00
ol6_u3_base                                              | 1.4 kB     00:00
ol6_u3_base/primary                                      | 2.7 MB     00:07
ol6_u3_base                                                           8452/8452
Setting up Install Process
No package oracleasmlib available.
Error: Nothing to do
#

 

However, we need to download oracleasmlib from Oracle’s website and install it using rpm. To download it directly within terminal using wget utility, we can use below command.

 

# wget http://download.oracle.com/otn_software/asmlib/oracleasmlib-2.0.12-1.el6.x86_64.rpm

After downloading we will have to install it using rpm.

                                                 ( Or)

Download- Intel EM64T (x86_64) Architecture

https://www.oracle.com/linux/downloads/linux-asmlib-v6-downloads.html

                                     

 

[root@oracleasm Share]# rpm -iv oracleasmlib-2.0.12-1.el6.x86_64.rpm

Preparing packages for installation...

oracleasmlib-2.0.12-1.el6

 

 

Step:-3 Set Static IP Address and enter hosts file

 

 

After installation the RPM’s using Yum.Change to static IP and restart

 

Go to System->Preferences-Network Connections-IPV4-Change Manaul

Service network restart.

Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

 

[root@oracleasm ~]# service network restart

Shutting down interface eth0:  Device state: 3 (disconnected)

                                                           [  OK  ]

Shutting down loopback interface:                          [  OK  ]

Bringing up loopback interface:                            [  OK  ]

 

 

[root@oracleasm ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

 

192.168.1.24 oracleasm

 

Step:-4 Set the password for the "oracle" user.

[root@oracleasm Share]# passwd oracle

Changing password for user oracle.

New password:

BAD PASSWORD: it is based on a dictionary word

BAD PASSWORD: is too simple

Retype new password:

passwd: all authentication tokens updated successfully.

 

Step:-5 Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

 

SELINUX=permissive

 

[root@oracleasm ~]# vi /etc/selinux/config

[root@oracleasm ~]# cat /etc/selinux/config

 

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

#     enforcing - SELinux security policy is enforced.

#     permissive - SELinux prints warnings instead of enforcing.

#     disabled - No SELinux policy is loaded.

SELINUX=permissive

# SELINUXTYPE= can take one of these two values:

#     targeted - Targeted processes are protected,

#     mls - Multi Level Security protection.

SELINUXTYPE=targeted

 

Once the change is complete, run the following command.

 

# setenforce Permissive

 

Step:-6 If you have the Linux firewall enabled, you will need to disable or configure it, as shown here or here. To disable it, do the following.

 

# systemctl stop firewalld

# systemctl disable firewalld

 

 

Step:-7 Create the directory structures and set ownership and permissions for Grid and Oracle software will be installed.

 

[root@oracleasm 121]# mkdir -p  /u01/app/oracle/12.1.0.2/grid

[root@oracleasm Share]# mkdir -p /u01/app/oracle/product/12.1.0.2/db_1

[root@oracleasm Share]# chown -R oracle:oinstall /u01

 [root@oracleasm Share]# chmod -R 775 /u01

 

 

Step:-8 Setting up the .bash_profile for both 'grid' and 'oracle' user.

 

Log in as the "oracle" user and add the following lines at the end of the "/home/oracle/.bash_profile" file.

 

[root@oracleasm 121]# su - oracle

[oracle@oracleasm ~]$ vi .bash_profile

[oracle@oracleasm ~]$ cat .bash_profile

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

                . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

 

# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP

 

export ORACLE_HOSTNAME=oracleasm

export ORACLE_UNQNAME=dbwrasm

export ORACLE_BASE=/u01/app/oracle

export GRID_HOME=/u01/app/oracle/12.1.0.2/grid

export DB_HOME=$ORACLE_BASE/product/12.1.0.2/db_1

export ORACLE_HOME=$DB_HOME

export ORACLE_SID=dbwrasm

export ORACLE_TERM=xterm

export BASE_PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$BASE_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

 

alias grid='. /home/oracle/grid_env'

alias db='. /home/oracle/db_env'

 

Grid

Create a file called "/home/oracle/grid_env" with the following contents.

 

export ORACLE_SID=+ASM

export ORACLE_HOME=$GRID_HOME

export PATH=$ORACLE_HOME/bin:$BASE_PATH

 

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

 

Database

Create a file called "/home/oracle/db_env" with the following contents.

 

export ORACLE_SID=dbwrasm

export ORACLE_HOME=$DB_HOME

export PATH=$ORACLE_HOME/bin:$BASE_PATH

 

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

 

Once the "/home/oracle/.bash_profile" has been run, you will be able to switch between environments as follows.

 

[oracle@oracleasm ~]$ . .bash_profile

[oracle@oracleasm ~]$ . db_env

[oracle@oracleasm ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/12.1.0.2/db_1

[oracle@oracleasm ~]$ . grid_env

[oracle@oracleasm ~]$ echo $ORACLE_HOME

/u01/app/oracle/12.1.0.2/grid

 

Step:-9 Adding Disk in Vmware

 

 

Shutdown VMWare

 

Hard Disk And Click Add Button

Add Hardware Wizard and click next

 

Select SCSCI and click Next

 

Select create new virtual disk and click Next

 

 

Enter ASM Disk size and click Next

 

 

 

Create folder ASMDISK and browse location and Save Next

 

Click Finish to create

 

 

ASM Disk is creating now

 

 

 

 

After disk is created click Advanced button

 

Select Independent and click Ok

 

 

Power on the Vmware  

 

 

 

Login in to Root User and  check fdisk –l

 

 

Partition the disks

 

[root@oracleasm ~]# fdisk /dev/sdb

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0xc85c6b6e.

Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won't be recoverable.

 

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

 

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

         switch off the mode (command 'c') and change display units to

         sectors (command 'u').

 

Command (m for help): n

Command action

   e   extended

   p   primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-2610, default 1): enter

Using default value 1

Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): enter

Using default value 2610

 

Command (m for help): w

The partition table has been altered!

 

Calling ioctl() to re-read partition table.

Syncing disks.

 

Check fdisk –l

 

 

Stage 2 Grid Installation ---- Continue

 

 

Table import and export.

Create Data Pump Directory. The first step in Oracle Data Pump is to create an OS level directory which will be used by Oracle for perform...