Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Wednesday, December 01, 2010

Administring ASM Instance remotely (10gR2)

By default the ASM instance is blocked and remote connections and administration cannot be performed.  The blocked status is being set by the instance, indicating that the database is not able to accept connections. This can be caused by the instances not being mounted or mounted in a restricted state.
Since an ASM instance never mounts a database, it will always be shown in 'BLOCKED' status.
The administration of an ASM instance is intended to be done on the server itself and not remotely.
If you intend to manage the ASM instance remotely , following solution can be applied.

Solution:
Connecting to a 'BLOCKED' instance via a listener requires creating an entry in the tnsnames.ora file on the client side with 'UR=A' under CONNECT_DATA.
Also, creating a password file for the ASM instance will be required to connect as SYS.

Step 1: Check the ASM instance by connecting as sysdba on the server itself
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\inam>set ORACLE_SID=+ASM

C:\Documents and Settings\inam>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 1 10:51:32 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>
Step 2: Create entry for ASM instance in tnsnames.ora on client machine and connect remotely
+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
    )
  )

Try to access the asm instance with the new service
C:\Documents and Settings\inam>sqlplus sys/asmdb@+asm as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 1 10:17:49 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:


Step 3: Check status with lsnrctl
C:\Documents and Settings\inam>lsnrctl status listener

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 01-DEC-2010 10:18:49

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=srv2.domain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date                30-NOV-2010 10:10:40
Uptime                    1 days 0 hr. 8 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=8080))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv2.domain)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "+ASM_XPT" has 1 instance(s).
  Instance "+asm", status BLOCKED, has 1 handler(s) for this service...
Service "+asm" has 1 instance(s).
  Instance "+asm", status BLOCKED, has 1 handler(s) for this service...
Service "ASMDBXDB" has 1 instance(s).
  Instance "asmdb", status READY, has 1 handler(s) for this service...



Step 4: Create password file for ASM instance. For my example i did the following to create the password file.
- found password file in folder  "D:\oracle\product\10.2.0\db_1\database" for ASMDB database which is using ASM instance for storage.
-  copy . ie; 
D:\oracle\product\10.2.0\db_1\database>copy PWDASMDB.ORA "PWDASM.ORA"
        1 file(s) copied.
Now those users with SYSDBA privileges in the password file can be used as the ASMUSER.
WARNING: This will change the SYS password in the ASM instance to the value used in the database instance.
Step 5:  Following line must be in listener.ora on Server
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = +ASM)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
    )
  )       

Step 6: Update the tnsnames.ora on client
+ASM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.domain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = +ASM)
      (UR=A)
    )
  )


Step 7: Try now to connect to +ASM instance remotely using the sqlplus client.
C:\Documents and Settings\inam>sqlplus sys/asmdb@+asm as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 1 10:39:11 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> 

No comments: