9 Replies Latest reply on Jun 6, 2011 6:51 AM by ACIDSCOUT

    Maintain Oracle DB?

    ACIDSCOUT Novice

      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

        • 1. Re: Maintain Oracle DB?
          JasonBurrell Hot Shot

          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

          • 3. Re: Maintain Oracle DB?
            ACIDSCOUT Novice

            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.html

             

            Kind regards

            ACIDSCOUT

            • 4. Re: Maintain Oracle DB?
              ACIDSCOUT Novice

              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=1032755

               

              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

              • 5. Re: Maintain Oracle DB?
                ACIDSCOUT Novice

                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

                • 6. Re: Maintain Oracle DB?
                  AndreTheGiant Guru
                  vExpert

                  Have you tried to open a call to VMware support?

                   

                  Andre

                  • 7. Re: Maintain Oracle DB?
                    ACIDSCOUT Novice

                    Hi Andre

                     

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

                     

                    regards
                    ACIDSCOUT

                    • 8. Re: Maintain Oracle DB?
                      AndreTheGiant Guru
                      vExpert

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

                      Maybe is too high.

                       

                      Andre

                      • 9. Re: Maintain Oracle DB?
                        ACIDSCOUT Novice

                        Hi

                         

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

                         

                        regards

                        ACIDSCOUT