Forum Stats

  • 3,837,476 Users
  • 2,262,262 Discussions
  • 7,900,297 Comments

Discussions

install statspack into oracle 12 database - CDB or PDB

willirob
willirob Member Posts: 40 Blue Ribbon
edited Jul 5, 2013 4:37AM in General Database Discussions

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

DK2010

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    Answer ✓

    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


«1

Answers

  • DK2010
    DK2010 Member Posts: 1,542 Silver Trophy

    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.

  • 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

  • The statspack needs to be installed into a PDB.


  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

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

    Hemant K Chitale


  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    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

    DK2010
  • willirob
    willirob Member Posts: 40 Blue Ribbon

    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.

  • willirob
    willirob Member Posts: 40 Blue Ribbon
    edited Jul 4, 2013 7:35AM

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    Answer ✓

    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


  • willirob
    willirob Member Posts: 40 Blue Ribbon

    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;

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,998 Blue Diamond
    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

This discussion has been closed.