VMware Cloud Community
ACIDSCOUT
Contributor
Contributor

Maintain Oracle DB?

Hi

we use oracle 11 as db for our virtual center. Now after 6 months the db  size is nearly 20GB.

ls -al /u01/app/oracle/oradata/vcenter/
total 18069556
drwxr-x--- 2 oracle oinstall       4096 May 23 09:55 .
drwxr-x--- 3 oracle oinstall       4096 Sep 21  2010 ..
-rw-r----- 1 oracle oinstall   14467072 May 27 11:28 control01.ctl
-rw-r----- 1 oracle oinstall  209723392 May 27 11:10 mgmt_ad4j.dbf
-rw-r----- 1 oracle oinstall  996155392 May 27 11:27 mgmt.dbf
-rw-r----- 1 oracle oinstall   20979712 May 27 11:26 mgmt_ecm_depot1.dbf
-rw-r----- 1 oracle oinstall   52429312 May 27 11:28 redo01.log
-rw-r----- 1 oracle oinstall   52429312 May 27 11:05 redo02.log
-rw-r----- 1 oracle oinstall   52429312 May 27 11:26 redo03.log
-rw-r----- 1 oracle oinstall 1038098432 May 27 11:26 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1268785152 May 27 11:27 system01.dbf
-rw-r----- 1 oracle oinstall 5087698944 May 27 11:28 temp03.dbf
-rw-r----- 1 oracle oinstall  419438592 May 27 11:27 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 May 27 11:10 users01.dbf
-rw-r----- 1 oracle oinstall 8204066816 May 27 11:28 vpx01.dbf
-rw-r----- 1 oracle oinstall 1073750016 May 27 11:10 vpx_up01.dbf

$ du -h /u01/app/oracle/oradata/vcenter/
18G     /u01/app/oracle/oradata/vcenter/

So how can we reduze or maintain the oralce db that the size not growing up so fast? My problem is that my experience with oracle are less. One point is the temp file, i create every few weeks a new one cause it grows over 10GB. Is there any option to reduce it or automaticly create a new file after a few days? I also change some values in the vc administration / server settings

database retention policy = 60 days

database = maximum number 10

logging options = warning ( erros and warnings)

What possibilities i have also to reduce the database size?

THX for any hints

ACIDSCOUT

Reply
0 Kudos
9 Replies
JasonBurrell
Enthusiast
Enthusiast

Acidscout,

Up to 90% of database growth is due to statistics gathering.  If you go into the vCenter Server Settings you can use the database size calculator to see how much of a difference changing the statistics level would be. The default is 1 1 1 1 wich should grow the database at a very slow rate compared to the higher levels.  One thing to note is that the lower the statistics level the less information you will have to troubleshoot performance with.

-Jason

Reply
0 Kudos
AndreTheGiant
Immortal
Immortal

See: http://kb.vmware.com/kb/1000125

Andrew | http://about.me/amauro | http://vinfrastructure.it/ | @Andrea_Mauro
Reply
0 Kudos
ACIDSCOUT
Contributor
Contributor

Hi Andre

so i followed your link and read the instructions and start the sql script but i got this error

@ VCDB_HIST_STAT_CLEANUP_ORACLE_V4.X.sql
V_SAMPLE_ID VPX_SAMPLE_TIME4.TIME_ID%TYPE;
            *
ERROR at line 27:
ORA-06550: line 27, column 13:
PLS-00201: identifier 'VPX_SAMPLE_TIME4.TIME_ID' must be declared
ORA-06550: line 27, column 13:
PL/SQL: Item ignored
ORA-06550: line 50, column 34:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 50, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 63, column 33:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 63, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 83, column 47:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 83, column 27:
PL/SQL: SQL Statement ignored
ORA-06550: line 86, column 32:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 86, column 9:
PL/SQL: SQL Statement ignored
ORA-06550: line 92, column 27:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 92, column 15:
PL/SQL: SQL Statement ignored
ORA-06550: line 98, column 25:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 98, column 13:
PL/SQL: SQL Statement ignored

I can not understand why table pp not exist, when i create the oracle db i followed these instructions

http://pubs.vmware.com/vsp40/wwhelp/wwhimpl/js/html/wwhelp.htm#href=install/t_create_oracle_script.h...

Kind regards

ACIDSCOUT

Reply
0 Kudos
ACIDSCOUT
Contributor
Contributor

Hi

so i googled a lot of this error but i can´t find a solution. I found these kb article

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=103275...

but this also fails with this

@create.sql
drop index VPX_SAMPLE_TIME1_M1
           *
ERROR at line 1:
ORA-01418: specified index does not exist


CREATE INDEX VPX_SAMPLE_TIME1_M1 ON VPX_SAMPLE_TIME1
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


drop index VPX_SAMPLE_TIME2_M1
           *
ERROR at line 1:
ORA-01418: specified index does not exist


CREATE INDEX VPX_SAMPLE_TIME2_M1 ON VPX_SAMPLE_TIME2
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


drop index VPX_SAMPLE_TIME3_M1
           *
ERROR at line 1:
ORA-01418: specified index does not exist


CREATE INDEX VPX_SAMPLE_TIME3_M1 ON VPX_SAMPLE_TIME3
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


drop index VPX_SAMPLE_TIME4_M1
           *
ERROR at line 1:
ORA-01418: specified index does not exist


CREATE INDEX VPX_SAMPLE_TIME4_M1 ON VPX_SAMPLE_TIME4
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

Any ideas about that?

Kind regards

ACIDSCOUT

Reply
0 Kudos
ACIDSCOUT
Contributor
Contributor

Hi

is nobody usindg oracle db? I can´t find a solution to stop the fast grow of the db especially the temp tablespace grows in a week up to 10GB or more?

regards

ACIDSCOUT

Reply
0 Kudos
AndreTheGiant
Immortal
Immortal

Have you tried to open a call to VMware support?

Andre

Andrew | http://about.me/amauro | http://vinfrastructure.it/ | @Andrea_Mauro
Reply
0 Kudos
ACIDSCOUT
Contributor
Contributor

Hi Andre

no that shold be the last option. I think now it´s time for the last option;-)

regards
ACIDSCOUT

Reply
0 Kudos
AndreTheGiant
Immortal
Immortal

About the reason of the grow you must check how is set the log level for statistics.

Maybe is too high.

Andre

Andrew | http://about.me/amauro | http://vinfrastructure.it/ | @Andrea_Mauro
Reply
0 Kudos
ACIDSCOUT
Contributor
Contributor

Hi

i think that is the lowest level 1 1 1 1.

regards

ACIDSCOUT

Reply
0 Kudos