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
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
See: http://kb.vmware.com/kb/1000125
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
Kind regards
ACIDSCOUT
Hi
so i googled a lot of this error but i can´t find a solution. I found these kb article
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
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
Have you tried to open a call to VMware support?
Andre
Hi Andre
no that shold be the last option. I think now it´s time for the last option;-)
regards
ACIDSCOUT
About the reason of the grow you must check how is set the log level for statistics.
Maybe is too high.
Andre
Hi
i think that is the lowest level 1 1 1 1.
regards
ACIDSCOUT