Connect the oracle client to the ASM instance

For Oracle network configuration, we usually use negmgr or netca to connect the client to the database instance. You can also connect to the ASM instance from a client. However, Oracle does not provide a tool for us to complete the configuration. We can manually configure the listener and the client tnsnames. This section describes and provides examples.

1. server and client Environments

# Server environment and host information

Oracle @ bo2dbp: ~> Cat/etc/hosts | grep vip

192.168.7.61 bo2dbp-vip.2gotrade.com bo2dbp-vip

192.168.7.62 bo2dbs-vip.2gotrade.com bo2dbs-vip

# Operating system and Oracle version

Oracle @ bo2dbp: ~> Cat/etc/issue

Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64)-Kernel \ r (\ l ).

Oracle @ bo2dbp: ~> Sqlplus-v

SQL * Plus: Release 10.2.0.3.0-Production

# Server environment and cluster information

Oracle @ bo2dbp: ~> ./Crs_stat.sh

Resource name Target State

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

Ora. GOBO4.GOBO4A. inst ONLINE on bo2dbp

Ora. GOBO4.GOBO4B. inst ONLINE on bo2dbs

Ora. GOBO4.db ONLINE on bo2dbp

Ora. bo2dbp. ASM1.asm ONLINE on bo2dbp

Ora. bo2dbp. LISTENER_BO2DBP.lsnr ONLINE on bo2dbp

Ora. bo2dbp. gsd ONLINE on bo2dbp

Ora. bo2dbp. ons ONLINE on bo2dbp

Ora. bo2dbp. vip ONLINE on bo2dbp

Ora. bo2dbs. ASM2.asm ONLINE on bo2dbs

Ora. bo2dbs. LISTENER_BO2DBS.lsnr ONLINE on bo2dbs

Ora. bo2dbs. gsd ONLINE on bo2dbs

Ora. bo2dbs. ons ONLINE on bo2dbs

Ora. bo2dbs. vip ONLINE on bo2dbs

# Client information

C: \ Users \ robinson. cheng> systeminfo

Host Name: PC39

OS Name: Microsoft Windows 7 Professional

OS Version: 6.1.7600 N/A Build 7600

OS Manufacturer: Microsoft Corporation

System Type: x64-based PC

C: \ Users \ robinson. cheng> sqlplus-v

SQL * Plus: Release 10.2.0.3.0-Production

2. Listener status

Oracle @ bo2dbs: ~> Lsnrctl status LISTENER_BO2DBS | grep ASM

Service "+ ASM" has 1 instance (s). # You can see that the ASM instance is in the BLOCKED status.

Instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service...

Service "+ ASM_XPT" has 1 instance (s ).

Instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service...

3. Allocate the ASM SID

# Use the following ASM configuration information to modify the Listener Configuration File

Item Node1 Node2

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

Hostname bo2dbp bo2dbs

Oracle SID GOBO4A GOBO4B

Asm sid + ASM1 + ASM2

ASM Global DB Name (service name) + ASM

4. Modify the listener Configuration File listener. ora

# Add the subitem SID_DESC for Listener Configuration.

# The following content is added.

Oracle @ bo2dbp: ~> More $ ORACLE_HOME/network/admin/listener. ora

# Listener. ora. bo2dbp Network Configuration File:/u01/oracle/db/network/admin/listener. ora. bo2dbp

# Generated by Oracle configuration tools.

LISTENER_BO2DBP =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = bo2dbp-vip.2gotrade.com) (PORT = 1521) (IP = FIRST ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.51) (PORT = 1521) (IP = FIRST ))

)

)

SID_LIST_LISTENER_BO2DBP =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/u01/oracle/db)

(PROGRAM = extproc)

(SID_DESC = # This entire SID_DESC item is the newly added entry of the ASM1 instance.

(SID_NAME = + ASM1)

(GLOBAL_DBNAME = + ASM)

(ORACLE_HOME =/u01/oracle/db)

)

)

Oracle @ bo2dbs: ~> More $ ORACLE_HOME/network/admin/listener. ora

# Listener. ora. bo2dbs Network Configuration File:/u01/oracle/db/network/admin/listener. ora. bo2dbs

# Generated by Oracle configuration tools.

LISTENER_BO2DBS =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = bo2dbs-vip.2gotrade.com) (PORT = 1521) (IP = FIRST ))

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.52) (PORT = 1521) (IP = FIRST ))

)

)

SID_LIST_LISTENER_BO2DBS =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/u01/oracle/db)

(PROGRAM = extproc)

)

(SID_DESC = # This entire SID_DESC item is the newly added entry of the ASM2 instance.

(SID_NAME = + ASM2)

(GLOBAL_DBNAME = + ASM)

(ORACLE_HOME =/u01/oracle/db)

)

)

5. Restart the listener.

Oracle @ bo2dbp: ~> Srvctl stop listener-n bo2dbp

Oracle @ bo2dbp: ~> Srvctl start listener-n bo2dbp

Oracle @ bo2dbp: ~> Srvctl stop listener-n bo2dbs

Oracle @ bo2dbp: ~> Srvctl start listener-n bo2dbs

# Viewing the listener status

Oracle @ bo2dbp: ~> Lsnrctl status LISTENER_BO2DBP | grep ASM

Service "+ ASM" has 2 instance (s). # You can see that one of the multiple nodes is in the UNKNOWN state, indicating that static registration is used.

Instance "+ ASM1", status UNKNOWN, has 1 handler (s) for this service...

Instance "+ ASM1", status BLOCKED, has 1 handler (s) for this service...

Service "+ ASM_XPT" has 1 instance (s ).

Instance "+ ASM1", status BLOCKED, has 1 handler (s) for this service...

Oracle @ bo2dbs: ~> Lsnrctl status LISTENER_BO2DBS | grep ASM

Service "+ ASM" has 2 instance (s). # in example 2, the listener also has an UNKNOWN + ASM2 instance.

Instance "+ ASM2", status UNKNOWN, has 1 handler (s) for this service...

Instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service...

Service "+ ASM_XPT" has 1 instance (s ).

Instance "+ ASM2", status BLOCKED, has 1 handler (s) for this service...

# Author: Robinson

6. Configure the client tnsnames

# Add the following configuration entries to the Windons client tnsnames. ora

GOBO4_ASM1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.61) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = + ASM)

)

)

GOBO4_ASM2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.7.62) (PORT = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = + ASM)

)

)

7. test connection to the ASM instance

C: \ Users \ robinson. cheng> sqlplus-S sys/oracle @ GOBO4_ASM1 as sysdba @ inst

INSTANCE_NAME HOST_NAME STATUS

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

+ ASM1 bo2dbp STARTED

C: \ Users \ robinson. cheng> sqlplus-S sys/oracle @ GOBO4_ASM2 as sysdba @ inst

INSTANCE_NAME HOST_NAME STATUS

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

+ ASM2 bo2dbs STARTED

Oracle video tutorial follow: http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html