Oracle Database In Our Hand: 2015

Wednesday, December 16, 2015

Oracle BEQUEATH connection

Connecting to Oracle bypassing the listener (aka a "bequeath connection")

A bequeath connection

runs on your local host

bypasses the listener

the protocol creates the server process for you directly

An IPC (Inter-Process Communication) connection

will use the native protocol on each OS, but uses the generic term "IPC" for all of them

can only be used when the Client and Server reside on the same host

can only be used by having the Client connect through the Oracle Listener

the Database Listener must be configured to listen on an IPC endpoint

the listener spawns the server process for you

Whenever you connect to Oracle from a client running on the same machine as the database without specifying a net service name in the connect string, then you can connect to Oracle bypassing the listener. This is called a Bequeath connection. For example, when you dont specify @<net service name>, then you connect to Oracle without a listener. In the following example I have set the environment variable ORACLE_SID, so as to connect to a specific instance:

$ sqlplus HR/HR

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 21:55:38 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

HR@HRDB>

Now I issue the command:

$lsnrctl services

The lsnrctl services command displays information about the services, such as whether

the services have any dedicated, prespawned server processes or dispatched processes asso-

ciated with them, and how many connections have been accepted and rejected per service. In our case, we are only interested for the connections established.

As you show above I have connected to the database. Still the listener "sees" no connections, since we have bypassed the listener:

$ lsnrctl services

LSNRCTL LINUX X64 bit: Version 12.1.0.1.7 - Production on 02-JAN-2015 21:55:43

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HR-lap)(PORT=1521)))

Services Summary...

...

Instance "HRdb", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:0 refused:0 state:ready

LOCAL SERVER

...

The command completed successfully.

Now lets try once again, but this time specifying a net service name to be resolved on the client with the a tnsnames.ora file (This is called Local Naming method):

HR@HRDB> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus HR/HR@HRdb

SQL*Plus: Release 12.1.0.1 Production on Wed Jan 2 21:56:03 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

HR@HRDB>

And lets check the listener again:

$ lsnrctl services

LSNRCTL for 64 bit LINUX: Version 12.1.0.1.7 - Production on 02-JAN-2015 21:56:07

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=HR-lap)(PORT=1521)))

Services Summary...

...

Instance "HRdb", status READY, has 1 handler(s) for this service...

Handler(s):

"DEDICATED" established:1 refused:0 state:ready

LOCAL SERVER

...

The command completed successfully

One connection via the listener this time.

Another way to monitor the type of network connection, is via the v$session_connect_info dynamic performance view. Here is an example:

$ sqlplus HR/HR

SQL*Plus: Release 12.1.0.1.7Production on Wed Dec 4 10:51:08 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

HR@HRDB> select network_service_banner from v$session_connect_info where sid=sys_context('userenv','sid');

NETWORK_SERVICE_BANNER

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

Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production

Authentication service for Linux: Version 12.1.0.1.0 - Production

Encryption service for Linux: Version 12.1.0.1.0 - Production

Crypto-checksumming service for Linux: Version 12.1.0.1.0 - Production

and using the listener:

$ sqlplus HR/HR@HRdb

SQL*Plus: Release 12.1.0.1.7 Production on Wed Dec 4 10:55:27 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.7 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

HR@HRDB> select network_service_banner from v$session_connect_info where sid=sys_context('userenv','sid');

NETWORK_SERVICE_BANNER

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

TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.1.0 - Production

Encryption service for Linux: Version 12.1.0.1.0 - Production

Crypto-checksumming service for Linux: Version 12.1.0.1.0 - Production

Check the connections and their types:

SELECT S.SID, S.OSUSER,S.PROGRAM,S.USERNAME,S.MACHINE, SCI.NETWORK_SERVICE_BANNER,S.LOGON_TIME,S.STATUS
FROM V$SESSION S INNER JOIN V$SESSION_CONNECT_INFO SCI
ON S.SID = SCI.SID
WHERE S.USERNAME = UPPER('scott')
AND SCI.NETWORK_SERVICE_BANNER LIKE '%IPC%'
OR SCI.NETWORK_SERVICE_BANNER LIKE INITCAP('%BEQ%')
AND S.TYPE <> 'BACKGROUND'
ORDER BY LOGON_TIME;

SID	OSUSER	PROGRAM	USERNAME	MACHINE	NETWORK_SERVICE_BANNER	LOGON_TIME	STATUS
9	oracle	sqlplus@linux01 (TNS V1-V3)	SCOTT	linux01	Oracle Bequeath NT Protocol Adapter for Linux: Version 12.1.0.1.7- Production	22.12.2015 15:35:49	INACTIVE
1160	oracle	sqlplus@linux01 (TNS V1-V3)	SCOTT	linux01	Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 12.1.0.1.7 - Production	22.12.2015 15:40:44	INACTIVE

Friday, July 24, 2015

Passwordless Connection between RAC NOdes

Hi Team,

During the add node or cluster upgradation,rdbms upgradation or cluster installation or rdbms installation if you want to check any pre-requisites using runcluvfy.sh script or cluvfy.sh script it required password less connection between the RAC nodes for same user.

About SSH Keys

SSH keys provide a more secure way of logging into a virtual private server with SSH than using a password alone. While a password can eventually be cracked with a brute force attack, SSH keys are nearly impossible to decipher by brute force alone. Generating a key pair provides you with two long string of characters: a public and a private key. You can place the public key on any server, and then unlock it by connecting to it with a client that already has the private key. When the two match up, the system unlocks without the need for a password. You can increase security even more by protecting the private key with a passphrase.

Step1: Passwordless connection between RAC NODES or same OS user across Nodes

[root@localhost ~]#

[root@localhost ~]# su - grid --DO for all Nodes

[grid@localhost ~]$ pwd

/home/grid

[grid@localhost ~]$ ls -lrt

total 0

[grid@localhost ~]$ cd .ssh

-bash: cd: .ssh: No such file or directory

[grid@localhost ~]$ mkdir .ssh

[grid@localhost ~]$ ls -lart

-rw-r--r-- 1 grid grid 124 Jul 24 11:01 .bashrc

-rw-r--r-- 1 grid grid 176 Jul 24 11:01 .bash_profile

-rw-r--r-- 1 grid grid 33 Jul 24 11:01 .bash_logout

drwxr-xr-x 4 root root 4096 Jul 24 11:01 ..

-rw------- 1 grid grid 17 Jul 24 11:02 .bash_history

drwxrwxr-x 2 grid grid 4096 Jul 24 11:03 .ssh

drwx------ 5 grid grid 4096 Jul 24 11:03 .

[grid@localhost ~]$

Note: If password less connection required for two different OS users across nodes then make sure the file perssion

By default drwxrwxr-x 2 grid grid 4096 Jul 24 11:03 .ssh

[grid@localhost ~]$ chmod 700 .ssh

it should be

drwx______ 2 grid grid 4096 Jul 24 11:03 .ssh

Step2: Generate the SSH KEY

[grid@localhost .ssh]$ ssh-keygen -t rsa --Do for all Nodes

Generating public/private rsa key pair.

Enter file in which to save the key (/home/grid/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/grid/.ssh/id_rsa.

Your public key has been saved in /home/grid/.ssh/id_rsa.pub.

The key fingerprint is:

da:b0:2e:46:c1:54:87:ba:ec:ae:88:25:bf:17:f4:ca grid@localhost.localdomain

step3: COpy the Key

grid@linux01:~$ ssh-copy-id -i grid@Node2 --repeat for all nodes , do from all other nodes else it will be one way no password less.

The authenticity of host 'remotecomputer (66.147.244.84)' can't be established.

RSA key fingerprint is : : : : : : : : : : : : : : : :.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'remotecomputer,10.20.30.40' (RSA) to the list of known hosts.

grid@remotecomputer's password:

Now try logging into the machine, with "ssh 'grid@remotecomputer.com'", and check in:

~/.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

grid@linux01:~$

Tuesday, July 21, 2015

ASM password FILE

Pre 11gR2, each node in the cluster had ASM password file named as orapw<SID> .Starting 11gR2, password file in a RAC cluster is named using the format orapw<+’_asmsid’> . “_asmsid” is an underscore parameter that defines default SID/Name of ASM instance.

If there are two nodes with +ASM1 running on node 1 and +ASM2 running on node2.

Pre 11gR2 --

Password file on Node1: orapw+ASM1

Password file on Node2: orapw+ASM2

11gR2 --

Password file on Node1: orapw+ASM

Password file on Node2: orapw+ASM

So on 11gR2, when password is changed for a privileged user on ASM instance of one node in a cluster, it is immediately reflected on all other nodes in the cluster. But who does it ? Here is a simple test:

[oracle@node1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 29 22:25:44 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Real Application Clusters and Automatic Storage Management options

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS

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

SYS TRUE TRUE TRUE

ASMSNMP TRUE FALSE FALSE

On Node2, lets check who is accessing the file

[root@node2 ~]# while true; do lsof | grep orapw+ASM; done

-- No Output so far--

-- lsof command means: lists open file descriptors.

-- This script will list processes which are currently accessing the file

orapw+ASM on node 2

Lets change password on ASM instance on Node 1 and check the output of shell script on Node2

SQL> alter user sys identified by ********;

User altered.

-- On Node 2 --

[root@node2 ~]# while true; do lsof | grep orapw+ASM; done

oracle 4863 oracle 22u REG 8,16 1536 198556

/u02/app/11.2.0/grid/dbs/orapw+ASM

[root@node2 ~]# ps -ef |grep 4863

oracle 4863 1 0 22:22 ? 00:00:00 asm_ckpt_+ASM2

Another check that can be performed is:

On one of the nodes, suspend the ASM CKPT process. Then try to execute a password change command (on ASM) from any other node in the cluster and it would hang.

ASMSNMP user in ASM 11g (user creation/password change)

Could not validate ASMSNMP password due to following error- “ORA-01031: insufficient privileges”
ORA-01990: error opening password file ‘/u02/app/11.2.0/grid/dbs/orapw’
ORA-15306: ASM password file update failed on at least one node
ORA-01918: user ‘ASMSNMP’ does not exist

Solutions:

Mostly the reason is missing asmsnmp user or missing password file in ASM instances.

Node1:

#su – grid

$. Oraenv

[ORACLE_SID]+ASM1

$sqlplus ‘/as sysasm’

SQL> select * from v$pwfile_users;

no rows selected

Node1 :

#su - grid

$. Oraenv +ASM1

$asmcmd

ASMCMD> lspwusr

Username sysdba sysoper sysasm

This shows no user are present it means ASM password file is missing. We have to recreate the password file.

Node1:

#su - grid

$. oraenv +ASM1

$ cd $ORACLE_HOME/dbs

$ls -lrt

check whether orapw+ASM file exist or not . If not recreate it

$orapwd file=/u01/app/11.2.0.4/grid/dbs/orapw+ASM password=oracle force=y

copy the same file to rest all nodes .

$ scp orapw+ASM <Node2>:/u01/app/11.2.0.4/grid/dbs/

copy the password files to other nodes if you have more than 2 nodes in the RAC.

Now chek

#su - grid

$. oraenv +ASM1

$sqlplus '/as sysasm'

SQL>select * from v$pwfile_users;

USERNAME SYSDB SYSOP SYSAS
——————————————————————————————————————— —– —– —————————————————————————————————————–
SYS TRUE TRUE TRUE
ASMSNMP TRUE FALSE FALSE

if still ASMSNMP user not exist, you create user under ASM instance

SQL>

create user and give sysdba privileges

SQL>create user asmsnmp identified by <make_asmsnmp_password>;

SQL> grant sysdba to asmsnmp;

SQL> show parameter pass

NAME TYPE VALUE
———————————— —————————————————————————————– ——————————————————————————————————
remote_login_passwordfile string EXCLUSIVE

The ASM instance parameter “REMOTE_LOGIN_PASSWORDFILE” has to be set to EXCLUSIVE or you will get an ORA-01999 error.

Also you can check user detials from asmcmd

ASMCMD> lspwusr
Username sysdba sysoper sysasm

SYS TRUE TRUE FALSE
ASMSNMP TRUE FALSE FALSE

How to Change password for ASMSNMP

——————————————————–

ASMCMD> lspwusr

Username sysdba sysoper sysasm
SYS TRUE TRUE TRUE
ASMSNMP TRUE FALSE FALSE

ASMCMD> orapwusr --modify --password ASMSNMP

Enter password: *******
(give new password and press enter)

orapwusr attempts to update passwords on all nodes in a cluster. The command requires the SYSASM privilege to run. A user logged in as SYSDBA cannot change its password using this command.

Test new asmsnmp password >>

————————–

$ sqlplus asmsnmp/asmsnmp as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 6 12:57:30 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options

Note: You can visit for RAC/ASM/PT/RMAN/Golden gate classes in www.amktechs.com

Friday, July 10, 2015

How To Setup ASM & ASMLIB On Native Linux Multipath Mapper disks

How To Setup ASM & ASMLIB On Native Linux Multipath Mapper disks

After installed and configured the Multipath, follow bellow:

1. Download and Install ASMLIB :
http://www.oracle.com/technetwork/topics/linux/asmlib/index-101839.html

We installed proper oracleasm rpms

2. Check the disks :

[root@linux01 ~]#ls -l /dev/mapper

brw-rw---- 1 root disk 253, 46 Jul 27 17:19 Mpatha01

brw-rw---- 1 root disk 253, 67 Jul 27 17:59 Mpatha02

brw-rw---- 1 root disk 253, 36 Jul 27 17:19 Mpatha03

brw-rw---- 1 root disk 253, 58 Jul 27 18:00 Mpatha04

Note: above disks are partitioned from Luns.

3. Create the ASMLIB disks on mapper partitions as follow:

[root@linux01 ~]# /etc/init.d/oracleasm createdisk DSKORA1 /dev/mapper/Mpatha01

[root@linux01 ~]# /etc/init.d/oracleasm createdisk DSKORA2 /dev/mapper/Mpatha02

After create the disks, the ASM put a mark on the disks to know which are your own.

4. If this is a RAC configuration, then from each node execute:

[root@linux01 ~]# /etc/init.d/oracleasm scandisks

[root@linux01 ~]# /etc/init.d/oracleasm listdisks

5. Configure ASMLIB to use multipath (from each node on RAC environments):

[root@linux01 grid]# /usr/sbin/oracleasm configure

ORACLEASM_ENABLED=true

ORACLEASM_UID=grid

ORACLEASM_GID=asmadmin

ORACLEASM_SCANBOOT=true

ORACLEASM_SCANORDER=" "

ORACLEASM_SCANEXCLUDE=" "

ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"

Note: Now we have to update above two bold character parameters else ASM diskgroup creation or next ASM startup will not work properly for Disk Group.

Option1:

Modify in /etc/sysconfig/oracleasm :
ORACLEASM_SCANORDER=”dm”
ORACLEASM_SCANEXCLUDE=”sd”

Restart ASMLIB (from each node on RAC environments):
/etc/init.d/oracleasm stop
/etc/init.d/oracleasm start

Option2:

[root@linux01 grid]# /usr/sbin/oracleasm configure -i -e -u grid -g asmadmin -o "dm" -x "sd"

[root@linux01 grid]# /usr/sbin/oracleasm exit

[root@linux01 grid]# /usr/sbin/oracleasm init

[root@linux01 grid]# /usr/sbin/oracleasm scandisks

[root@linux01 grid]# /usr/sbin/oracleasm configure

ORACLEASM_ENABLED=true

ORACLEASM_UID=grid

ORACLEASM_GID=asmadmin

ORACLEASM_SCANBOOT=true

ORACLEASM_SCANORDER="dm"

ORACLEASM_SCANEXCLUDE="sd"

ORACLEASM_USE_LOGICAL_BLOCK_SIZE="false"

6. Verify if the configuration is correct:

6.1 During the disk discovery, ASMLIB uses file /proc/partitions, see :

[root@linux01 grid]# cat /proc/partitions

8 0 877264896 sda

8 1 104391 sda1

8 2 877157032 sda2

8 16 209715200 sdb

8 32 382730240 sdc

8 48 379596800 sdd

8 64 2097152 sde

8 80 2097152 sdf

8 96 1169776640 sdg

253 6 209715200 dm-6

253 7 382730240 dm-7

253 8 379596800 dm-8

253 9 2097152 dm-9

253 10 2097152 dm-10

6.2 The ASMLIB mount disks at /dev/oracleasm/disks, see:

# ls -la /dev/oracleasm/disks

brw-rw---- 1 grid asmadmin 253, 6 Ago 16 16:33 DSKORA1

brw-rw---- 1 grid asmadmin 253, 7 Ago 16 16:33 DSKORA2

6.3 Check if major and minor of disks “dm” not “sd”, is the same in /proc/partitions and /dev/oracleasm/disks , see “253″ and “6″ bellow:

[root@linux01 grid]# cat /proc/partitions

253 6 209715200 dm-6

[root@linux01 grid]# ll /dev/oracleasm/disks

brw-rw---- 1 grid asmadmin 253, 6 Ago 16 16:33 DSKORA1

You can check using querydisk too :

[root@linux01 grid]# /etc/init.d/oracleasm querydisk -d DSKORA1

Disk "DSKORA1" is valid ASM disk on device [253, 6]