Fix ORA-15097: cannot SHUTDOWN ASM instance with connected client (process %s)


Error Description:
ASM database instance shutdown failed with following error.
SQL> conn / as sysasm
Connected.
SQL> shut immediate;

ORA-15097: cannot SHUTDOWN ASM instance with connected client (process 9956)

Solution Description:
The error is because the ASM database instance is not idle. It is connected with client databases. For ASM instance normal shutdown, it should be disconnected from all the client databases. At least any one of the orphan process from any of the client database will stop the ASM instance shutdown.  
You can check ASM status using following methods.
  1. Connect asmcmd prompt and type lsct command.
asmcmd -p
ASMCMD [+] > lsct
DB_Name   Status     Software_Version  Compatible_version  Instance_Name  Disk_Group
PRODQA01  CONNECTED        11.2.0.2.0          11.2.0.0.0  PRODQA01       DATA
PRODQA02  CONNECTED        11.2.0.2.0          11.2.0.0.0  PRODQA02       DATA
PRODQA03  CONNECTED        11.2.0.2.0          11.2.0.0.0  PRODQA03       DATA

  1. Connect to sqlplus as sysasm and run the following query.
$ sqlplus / as sysasm

SQL> select GROUP_NUMBER, INSTANCE_NAME, DB_NAME, STATUS from V$ASM_CLIENT;

GROUP_NUMBER INSTANCE_NAME   DB_NAME  STATUS
------------ --------------- -------- ------------
           1 PRODQA03        PRODQA03 CONNECTED
           1 PRODQA02        PRODQA02 CONNECTED
           1 PRODQA01        PRODQA01 CONNECTED

Shutdown all ASM client database and try to bring down the database. If you still finding issues in shutdown check any process from any of the client database using ps –ef command and kill (kill -9 process id) it.

SQL> shut immediate;
ASM diskgroups dismounted
ASM instance shutdown

In case all of above options fails, last option is shutdown abort which will stop the instance abruptly.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google