0 Replies Latest reply: Aug 18, 2014 2:11 PM by kkovachki RSS

    install statspack in oracle 12.1.0.2.0 database - CDB

    kkovachki

      Hello

       

      I am trying to test STATSPACK in 12.1.0.2 and during installation i face a few issues.

       

      [oracle@ol7-12c ~]$ sqlplus /nolog

      SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 18 13:58:07 2014

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

      SQL> conn / as sysdba

      Connected.

      SQL> @?/rdbms/admin/spcreate.sql

       

      Choose the PERFSTAT user's password

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

      Not specifying a password will result in the installation FAILING

      Enter value for perfstat_password: perfstat

      perfstat

       

      Choose the Default tablespace for the PERFSTAT user

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

      Below is the list of online tablespaces in this database which can

      store user data.  Specifying the SYSTEM tablespace for the user's

      default tablespace will result in the installation FAILING, as

      using SYSTEM for performance data is not supported.

       

      Choose the PERFSTAT users's default tablespace.  This is the tablespace

      in which the STATSPACK tables and indexes will be created.

       

      TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE

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

      SYSAUX                         PERMANENT *

      USERS                          PERMANENT


      Pressing <return> will result in STATSPACK's recommended default

      tablespace (identified by *) being used.

       

      Enter value for default_tablespace: USERS

      Using tablespace USERS as PERFSTAT default tablespace.

       

      Choose the Temporary tablespace for the PERFSTAT user

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

      Below is the list of online tablespaces in this database which can

      store temporary data (e.g. for sort workareas).  Specifying the SYSTEM

      tablespace for the user's temporary tablespace will result in the

      installation FAILING, as using SYSTEM for workareas is not supported.

       

      Choose the PERFSTAT user's Temporary tablespace.

      TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE

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

      TEMP                           TEMPORARY *

      Pressing <return> will result in the database's default Temporary

      tablespace (identified by *) being used.

      Enter value for temporary_tablespace: TEMP

      Using tablespace TEMP as PERFSTAT temporary tablespace.

       

      ----- You can face issue with user creation descried in install statspack into oracle 12 database - CDB or PDB

       

      ... Creating PERFSTAT user

      ... Installing required packages

      ... Creating views

      ... Granting privileges

       

      #### creation stuck on below grant part of spcusr.sql script

      /*  Select privs for catalog objects - ROLES disabled in PL/SQL packages  */

      ->>>> grant select on GV_$INSTANCE  to PERFSTAT;<<<<<<

       

      If you disable that grant installation will complete successfully

       

      Strange situation

       

             SID    SERIAL# OSUSER                          STATUS   PROGRAM                   BLOCKING_INSTANCE BLOCKING_SESSION

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

             363      39155 oracle                         ACTIVE   sqlplus@ol7-12c.dba.bg (TNS V1-V3)     1               15

       

      After additional check its look like that we have library cache lock

      grant_lock.PNG

       

      Username   SERIAL#  SID Term   Table Name                     COMMAND                   Lock Held            Lock Requested       ID1 - ID2          Lock Type

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

      SYS          39155  363 pts/3  SYS.ORA$BASE                   GRANT                     Share                NONE                 133-0              AE - ????

       

      in order to resolve that you can restart instance!

       

      If someone face that kind of issue with will nice to share

       

      Regards,

      Krasimir Kovachki