This discussion is archived
12 Replies Latest reply: Jul 5, 2013 1:37 AM by Hemant K Chitale RSS

install statspack into oracle 12 database - CDB or PDB

willirob Newbie
Currently Being Moderated

Hi All

 

I am trying to install statspack into a new ORACLE 12c DB.

 

The install fails creating perfstat user in the ROOT container.

 

My question I guess is whether statspack can be installed as a common user - the rdbms/admin scripts don't do this since common users need to be prefixed with c##

 

eg. c##perfstat

 

spdoc.txt contains this entry:

 

9.1.  Changes between 11.1  and 12.1

  o  Idle Events

     - Added Idle Events that span LogMiner, PQ, SQL*Net, Capture Reply

  o  Consolidated DB/Pluggable DB

     - Consolidated DB and Statspack Reporting at the CDB Root Level

  • 1. Re: install statspack into oracle 12 database - CDB or PDB
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    Thats correct You can create the common user with prefix C## ref Doc:Managing Security for Oracle Database Users

    A common user is a user that has the same identity in the root and in every existing and future PDB. A common user can log in to the root and any PDB in which it has privileges.

  • 2. Re: install statspack into oracle 12 database - CDB or PDB
    user10921795 Newbie
    Currently Being Moderated

    DK2010,

     

    I faced a similar problem and found something of a work around. Though Statspack is now functioning, it seem a little odd. Certainly not "documented" in the spcreate.sql scripts.

     

    logging in as sys and running spcreate  I get:

    1

     

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    23

    24

    25

    26

     

    ... Creating PERFSTAT user

     

    create user perfstat

                *

    ERROR at line 1:

    ORA-65096: invalid common user or role name

     

    Where sys is in "root" container

    SQL> show con_name

     

    CON_NAME

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

    CDB$ROOT

     

    and the configuration has

    SQL> select con_id,dbid,NAME,OPEN_MODE from v$containers;

     

        CON_ID   DBID NAME               OPEN_MODE

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

         1 1347503191 CDB$ROOT               READ WRITE

         2 4062285978 PDB$SEED               READ ONLY

         3 2266865794 PDBORCL                READ WRITE

     

    On

    SQL> alter session set container=pdborcl;

     

    Session altered.

    The spcreate.sql runs to completion and spauto.sql will run and set up the job.

     

    If the real work around is to alter the spcreate.sql and the scripts it calls to create and work with a user c##perfstat, then hopeful Oracle will supply these scripts.

     

    Spauto.sql was run as sys and is running as scheduled. I changed spauto to run every 15 minutes.

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

     

    SQL> select job, log_user, schema_user, priv_user

     

      2  from dba_jobs;

     

           JOB LOG_USER   SCHEMA_USER  PRIV_USER

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

         1 SYS        PERFSTAT     SYS

     

    SQL> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')

      2  "Date/Time" from stats$snapshot,v$database;

     

    NAME         SNAP_ID Date/Time

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

    ORCL           1 03.07.2013:07:09:22

    ORCL           2 03.07.2013:07:24:04

    ORCL          11 03.07.2013:07:39:02

  • 3. Re: install statspack into oracle 12 database - CDB or PDB
    rp0428 Guru
    Currently Being Moderated

    The statspack needs to be installed into a PDB.


  • 4. Re: install statspack into oracle 12 database - CDB or PDB
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    I don't have a 12.1 install available  ............... but have you checked $ORACLE_HOME/rdbms/admin/spdoc.txt  for instructions ?

     

    Hemant K Chitale


  • 5. Re: install statspack into oracle 12 database - CDB or PDB
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    rp0428 wrote:

     

    The statspack needs to be installed into a PDB.

     

    True at present, but apart from the note in spdoc.txt there's a line in spcreate.sql that vaguely suggests that there may be (or may have been) a way of allowing perfstat to be a common user (there's a line to alter a hidden parameter).

     

    I'm not planning to experiment, but I note there's a hidden parameter "_common_user_prefix" which defines the "C##" that has to prefix a common user name. It's not modifiable at the system or session level, but I did wonder whether it would be possible to set this to null, bounce the database, install perfstat, then set it back to 'C##' and restart the database with perfstat in CDB$ROOT.

     

    Regards

    Jonathan Lewis

  • 6. Re: install statspack into oracle 12 database - CDB or PDB
    willirob Newbie
    Currently Being Moderated

    was thinking along those lines myself, other common users are created without c## prefix - will give it a go and feedback. Doesn't make sense to me to install into each PDB.

     

    I know statspack is not high on the list of priorities, but for many of us DBAs "in the trenches" it is indespensable.

  • 7. Re: install statspack into oracle 12 database - CDB or PDB
    willirob Newbie
    Currently Being Moderated

    ok - this works. Thanks.

     

    added following parameter to pfile:

     

    _common_user_prefix=''

     

    bounced database and created common user perfstat:

     

    SQL> shutdown immediate

    Database closed.

    Database dismounted.

    ORACLE instance shut down.

    SQL> create spfile from pfile;

     

     

    File created.

     

     

    SQL> startup

    ORACLE instance started.

     

     

    Total System Global Area 1636814848 bytes

    Fixed Size                  2288968 bytes

    Variable Size             973079224 bytes

    Database Buffers          654311424 bytes

    Redo Buffers                7135232 bytes

    Database mounted.

    Database opened.

    SQL> create user perfstat identified by perfstat container=ALL;

     

     

    User created.

  • 8. Re: install statspack into oracle 12 database - CDB or PDB
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Thanks to a commentator on my blog, I now know there's an easier option.

     

    The spcreate.sql script has a line

    alter session set "_oracle_script"=true;

     

    Run this statement in the CDB and you will be able to create a user called perfstat;

     

    However if you try running the spcreate script it would still fail because the spcusr.sql script that it calls includes the option "container=current" in the "create user" statement - so before running spcreate.sql you have to edit spcusr.sql to comment out this option.

     

    Regards

    Jonathan Lewis


  • 9. Re: install statspack into oracle 12 database - CDB or PDB
    willirob Newbie
    Currently Being Moderated

    Also had to make add the CONTAINER=ALL to the create user statement in spcusr.sql

     

    prompt

    prompt

    prompt ... Creating PERFSTAT user

     

     

    create user perfstat

      identified by &&perfstat_password

      default tablespace &&default_tablespace

      temporary tablespace &&temporary_tablespace container=ALL;

  • 10. Re: install statspack into oracle 12 database - CDB or PDB
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    willirob wrote:

     

    Also had to make add the CONTAINER=ALL to the create user statement in spcusr.sql

     

    container=ALL;

     

    I thought the default was ALL if the container was not set CURRENT and you were logged on to CDB$ROOT.

     

     

    Regards

    Jonathan Lewis

  • 11. Re: install statspack into oracle 12 database - CDB or PDB
    willirob Newbie
    Currently Being Moderated

    You are right, ran some tests. Default is container=ALL if you are logged on CDB$ROOT. Makes sense too.

     

    I did this, setting the session parameter and then running the spcreate as follows:

     

    SQL> conn / as sysdba

    SQL> alter session set "_oracle_script"=true;

    SQL> @?rdbms/admin/spcreate

     

    No need to edit the oracle supplied scripts. They work out of the box.

     

    One thing to note is that to run spdrop.sql (to uninstall) you have to again set the "_oracle_script" parameter, But this makes sense too I guess.

  • 12. Re: install statspack into oracle 12 database - CDB or PDB
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Makes one wonder.... What does "_oracle_script"=TRUE   actually do ?  i.e.  what if you use this in a non statspack-install context.  What can you do with it ? Could you break some things if you use it in a session ?

     

    Hemant  K Chitale

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points