12 Replies Latest reply on Jul 5, 2013 8:37 AM by Hemant K Chitale

    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

        • 1. Re: install statspack into oracle 12 database - CDB or PDB
          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.

          • 2. Re: install statspack into oracle 12 database - CDB or PDB
            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

            • 3. Re: install statspack into oracle 12 database - CDB or PDB

              The statspack needs to be installed into a PDB.


              • 4. Re: install statspack into oracle 12 database - CDB or 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


                • 5. Re: install statspack into oracle 12 database - CDB or PDB
                  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

                  • 6. Re: install statspack into oracle 12 database - CDB or PDB
                    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.

                    • 7. Re: install statspack into oracle 12 database - CDB or PDB
                      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.

                      • 8. Re: install statspack into oracle 12 database - CDB or PDB
                        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


                        • 9. Re: install statspack into oracle 12 database - CDB or PDB
                          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;

                          • 10. Re: install statspack into oracle 12 database - CDB or PDB
                            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

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

                              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

                                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