CA Data Manager DB Tablespace

[David_Tsatsi]

Hi Guys

 

My DB is growing at a rapid rate and I just wanted to find out what could be the cause especially the USERS tablespace. What kind of data does that table contain?

 

These are my current DB settings:

 

NUMBER_DAYS_DAILY_DATA_RETAINED = 90

NUMBER_DAYS_DATA_RETAINED = 120

NUMBER_DAYS_HOURLY_DATA_RETAINED = 90

 

Do these settings also purge data in the users table as well?

 

Regards

David

[David_Tsatsi]

Hi Gents SurendranathKS, DirkBleyenberg, ashbyatx, AdrianJohnsonUK

 

Below is my current DB table space sizes and as you can see I am faced with issues. How can I reduce this spaces. I currently have available +/- 400GB in the oracle file-system on my DB server and another option is to increase the table space sizes from the available storage. Please assist.

 

[Legacy User]

David,

  The ID that Data Manager logs into the DB with should be the Schema Owner.

That Schema owner USERID should have a "Default Table Space" set and it should be the one you created for Data Manager.

The first of the four tablespaces is called "HYP_DM_TBL" for this example script shown below.

It appears that your default TableSpace is set to USERS, instead of the Data Manager TableSpaces.

It is creating all the views and things in your "default" tablespace for the ID you login with, which may be Users.

This would explain why your Users tablespace is filling up instead of the DM table spaces.

 

See this script below is an example of how the Schema Owner should be setup:  (See Bold Line)

-- Create UserID that will be Schema Owner

-- ID for this example will be "HYP_CCC3" search and replace to something else if desired.

 

 

Prompt User HYP_CCC3;

--

-- HYP_CCC3  (User)

--

Prompt User HYP_CCC3;

CREATE USER HYP_CCC3

  IDENTIFIED BY <password>

  DEFAULT TABLESPACE HYP_DM_TBL

  TEMPORARY TABLESPACE TEMP2

  PROFILE DEFAULT

  ACCOUNT UNLOCK

/

  -- 2 Roles for HYP_CCC3

  GRANT CONNECT TO HYP_CCC3

/

--  GRANT DBA TO HYP_CCC3    -- This is not required...but it makes the installation easier!

--/

--  ALTER USER HYP_CCC3 DEFAULT ROLE ALL

--/

 

 

-- 14 System Privileges for HYP_CCC3

  GRANT ANALYZE ANY TO HYP_CCC3

/

  GRANT CREATE CLUSTER TO HYP_CCC3

/

  GRANT CREATE INDEXTYPE TO HYP_CCC3

/

  GRANT CREATE JOB TO HYP_CCC3

/

  GRANT CREATE OPERATOR TO HYP_CCC3

/

  GRANT CREATE PROCEDURE TO HYP_CCC3

/

  GRANT CREATE SEQUENCE TO HYP_CCC3

/

  GRANT CREATE SYNONYM TO HYP_CCC3

/

  GRANT CREATE TABLE TO HYP_CCC3

/

  GRANT CREATE MATERIALIZED VIEW TO HYP_CCC3

/

  GRANT CREATE TRIGGER TO HYP_CCC3

/

  GRANT CREATE TYPE TO HYP_CCC3

/

  GRANT CREATE VIEW TO HYP_CCC3

/

  GRANT SELECT ANY DICTIONARY TO HYP_CCC3

/

  GRANT UNLIMITED TABLESPACE TO HYP_CCC3

/

  -- 3 Object Privileges for HYP_CCC3

  Prompt Privs on PACKAGE SYS.DBMS_RANDOM to HYP_CCC3;

  GRANT EXECUTE ON SYS.DBMS_RANDOM TO HYP_CCC3

/

  Prompt Privs on PACKAGE SYS.DBMS_SCHEDULER to HYP_CCC3;

  GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO HYP_CCC3

/

  Prompt Privs on PACKAGE SYS.DBMS_XMLGEN to HYP_CCC3;

  GRANT EXECUTE ON SYS.DBMS_XMLGEN TO HYP_CCC3

/

[David_Tsatsi]

Hi David

 

Thanks for your response. What does this mean and how do I fix it?

 

Regards

David

[Legacy User]

Hi David

 

While waiting for a response from your DBA about the users table, there are a couple of things to do in the meantime:

 

1) Unless you have a very obscure use case, there seems little point in having raw data around for longer than the rolled up data - I would suggest dropping NUMBER_DAYS_DATA_RETAINED to 90 (same as your rolled up data), or even 30 if you are comfortable with that (?).  All CCC reports and almost all of the OOTB CCR reports now target the rolled up data and so we really don't need to access the raw data most of the time, once it has been rolled up.  (If you are an active user of the Capacity Manager thick client, you may want to keep it for longer, but I would think 90days is sufficient for most purposes).

 

- this will buy you back some space in your main datamart table spaces whilst you figure out what else is happening.

- the space will be reclaimed immediately during the next overnight run if you have partitioning enabled (the old partitions should just get dropped).

- if you do not have partitioning enabled, the space will still be allocated the tables and will not become available for other tables to use automatically.  However, it will allow you to add more data back into the same tables.

 

2) Review the CCR Diagnostics 'Tablespace Reports' page - the 'Tablespace Details' will show you the largest tables, and which tablespaces they are in - this will help address the question raised by David Dupré in the above post.

 

3) I would also recommend that all sites do the following: Add and configure the attached Oracle_DB_Size_v1.6.xml data adapter.  This should be configured in Data Manager to pull from the DM database itself, once per day for the previous day (I usually set it to run at 07.00 or 08.00 after the previous days overnight run has completed).

- over time this will create a history in Diverse Metrics of tablespace usage over time (one same per day), and you should see how your tablespace usage is growing over time from this point onwards (sadly, it can't go back in time and tell you how you got here...      ).

- If you add the 'Health - Tablespaces Current Summary' view to the 'Tablespace Reports' page, you will see a drill-down to take you to details of how the tablespace grows over time

 

4) If you have access to do so, you can also add the attached 'DMH_Table_Metrics_View' to the DM database.  This provides simple and easy access to the 'per table' data loaded by the above data adapter.  From there, you will be able to determine which are the largest tables, where they reside (which tablespace), and how fast they are growing (including any associated indexes).  Unfortunately, there is not currently a set of published CCR reports that go along with this data, but at least it is now there....

 

I hope this is helpful to keep you going in this regard.

 

Please let us know any updates, and we'll try to follow up with you again shortly.

 

Regards,

 

Adrian

Attachment(s)

Oracle_DB_Size_v1.6.xml.zip   2 KB 1 version

Create_DMH_Table_Metrics_View_140203.sql.zip   687 B 1 version

[David_Tsatsi]

Hi Adrian

 

Thanks once again for your assistance, I just wanted to confirm a few things;

1. I assume I would need to run the script on the DB using SQL developer to create that view. What user permissions will this require?
2. Do I need to add this XML file in DM under manage data source types and then create a data source under manage data source and use the data adapter I just created?
3. Must I copy this XML on CCR as well as I saw a similar one on CCR under the NETQOS\portal\Integration\DataManager\Config folder.

 

Regards

David

[Kip_Lamb]

Hey David,

Ade has helped me with this DA before.

1. Yes. You will need to run the script against the Data Manager Oracle database using either sqlplus or SQL Developer.

The schema owner user (aka dmuser) should have permissions to create view, else that is what would be needed in this case, like:

grant create view to DMUSER;

2. Yes.

3. No. This particular XML file (Oracle_DB_Size_v1.6.xml) is in a Data Manager Data Adapter file format and not a CCR report XML format.

There are reports already included in the CCR 1.5 reports that will populate once you pull in data with this adapter.

 

Hope this helps

>KIP

[David_Tsatsi]

Thanks Kip

 

I will do if first thing tomorrow morning.

 

Regards

David T.