install statspack into oracle 12 database - CDB or PDB

willirob

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

Jonathan Lewis

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

DK2010

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.

user10921795

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

Unknown

The statspack needs to be installed into a PDB.

Hemant K Chitale

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

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

willirob

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

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

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

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

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