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##
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
Thats correct You can create the common user with prefix C## ref Doc:Managing Security for Oracle&nbsp;Database&nbsp;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.
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:
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.
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.
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.
ok - this works. Thanks.
added following parameter to pfile:
bounced database and created common user perfstat:
SQL> shutdown immediate
ORACLE instance shut down.
SQL> create spfile from pfile;
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
SQL> create user perfstat identified by perfstat container=ALL;
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.
Also had to make add the CONTAINER=ALL to the create user statement in spcusr.sql
prompt ... Creating PERFSTAT user
create user perfstat
identified by &&perfstat_password
default tablespace &&default_tablespace
temporary tablespace &&temporary_tablespace container=ALL;
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;
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.