Error accessing PRODUCT_USER_PROFILE

PRODUCT_USER_PROFILE:

You may occasionally find some errors like the following when new users you have created try to access the database through SQL*Plus:

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

The product_user_profile table is a table Oracle maintains to control access to the database through SQL*Plus. Make sure you are logged in as the SYSTEM user and run the following script to ensure that this table can be accessed properly by all the database users exist, so that their SQL*Plus privileges can be checked properly.

SQL> @ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
.............
.............
.............
Synonym created.
SQL>

ORA-12162 TNS:net service name is incorrectly specified

I just setup Orale 11g R2 ASM based on ASMLib and it went fine during installation and was working well.

When I rebooted my system then I was not able to connect to ASM Instance since it's PMON service was up and running.


[grid@ora11gsrv grid]$ ps -ef | grep pmon
grid     10957     1  0 09:47 ?        00:00:00 asm_pmon_+ASM
grid     11890 11866  0 10:12 pts/1    00:00:00 grep pmon

[grid@ora11gsrv grid]$ crs_stat
NAME=ora.DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
STATE=ONLINE on ora11gsrv

NAME=ora.LISTENER.lsnr
TYPE=ora.listener.type
TARGET=ONLINE
STATE=ONLINE on ora11gsrv

NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on ora11gsrv

NAME=ora.cssd
TYPE=ora.cssd.type
TARGET=ONLINE
STATE=ONLINE on ora11gsrv

NAME=ora.diskmon
TYPE=ora.diskmon.type
TARGET=ONLINE
STATE=ONLINE on ora11gsrv

So, this problem was all about related to environment variables so nothing to do with listener, ASM instance and database, all we need to do is to just set the proper env variables.


Just go to the user's home directroy installed ASM and set the following:


[grid@ora11gsrv grid]$ vi /home/grid/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

ORACLE_SID=+ASM

export ORACLE_SID

ORACLE_HOME=/u01/app/grid/product/11.2.0/grid

export ORACLE_HOME

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin

export PATH
~        

:wq!



Now go back to your terminal and walk with following steps:



[grid@ora11gsrv grid]$ ps -ef | grep pmon
grid     10957     1  0 09:47 ?        00:00:00 asm_pmon_+ASM
grid     11890 11866  0 10:12 pts/1    00:00:00 grep pmon



[grid@ora11gsrv grid]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base for ORACLE_HOME=/u01/app/grid/product/11.2.0/grid is /u01/app/grid




[grid@ora11gsrv grid]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 9 10:13:14 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> set linesize 220
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS     SHU DATABASE_STATUS
--------------- ---------------- ---------------------------------------------------------------- ----------------- --------- ------------ --- ---------- ------- --------------- ---------- --- -----------------
INSTANCE_ROLE      ACTIVE_ST BLO
------------------ --------- ---
              1 +ASM             ora11gsrv                                                        11.2.0.1.0        09-APR-15 STARTED      NO           0 STOPPED        ALLOWED    NO  ACTIVE
UNKNOWN            NORMAL    NO

 

Now, I'm able to connect to ASM instance.