Announcement

Collapse
No announcement yet.

[SOLVED] Error when trying to use DB scripts provided with OTM

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [SOLVED] Error when trying to use DB scripts provided with OTM

    I have just finished installing Oracle 10g Enterprise Edition (10.2.0.3) and would like to run the Tablespace creation scripts that were provided with the OTM install.

    I have logged into SQLPLUS as SYSTEM and am within the Script8 directory. I am able to start off with the create_gc3_tablespaces script and answer all the prompted questions but when I tell it to create the tablespaces now, it immediately gives me these errors:

    ================================================== ====
    *
    ERROR at line 1:
    ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
    ORA-06512: at line 1


    BEGIN create_tablespace.set_partition_db('n'); END;

    *
    ERROR at line 1:
    ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
    ORA-06512: at line 1


    BEGIN create_tablespace.set_dbtype('n'); END;

    *
    ERROR at line 1:
    ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
    ORA-06512: at line 1


    BEGIN create_tablespace.set_auto_extend('y'); END;

    *
    ERROR at line 1:
    ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
    ORA-06512: at line 1


    BEGIN create_tablespace.set_execute('y'); END;

    *
    ERROR at line 1:
    ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
    ORA-06512: at line 1


    BEGIN create_tablespace.create_all_ts('D:\oracle\oradata ', '1024000K','2048000K'
    ); END;

    *
    ERROR at line 1:
    ORA-04063: package body "SYSTEM.CREATE_TABLESPACE" has errors
    ORA-06508: PL/SQL: could not find program unit being called: "SYSTEM.CREATE_TABLESPACE"
    ORA-06512: at line 1

    ================================================== ====

    I am definitely not a DBA and I see the different sql script files the original create_gc3_tablespaces script calls on within the same directory.

    Can somebody give me some sugestions? Just to note, none of the scripts in this Script8 directory have been modified in any way.

    Thanks

  • #2
    Re: Error when trying to use DB scripts provided with OTM

    Hi Carmelo,

    I think your problem is that you need to login as user SYS not as SYSTEM.

    Thanks,
    Alan

    Comment


    • #3
      Re: Error when trying to use DB scripts provided with OTM

      Thanks Alan...it has been too long since I did a full install (DB + OTM), especially one in a Windows Environment.

      I logged in as SYS and it looks to be doing it now.

      Comment


      • #4
        Re: Error when trying to use DB scripts provided with OTM

        I am able to get past the tablespace creation step now.

        However, when I get to the step to create_public_synonyms, I receive the following error:

        SQL> @create_public_synonyms
        SELECT owner, table_owner FROM DBA_SYNONYMS WHERE SYNONYM_NAME = p_synonym_name;
        *
        ERROR at line 8:
        ORA-06550: line 8, column 35:
        PL/SQL: ORA-00942: table or view does not exist
        ORA-06550: line 8, column 4:
        PL/SQL: SQL Statement ignored

        I am logged in as glogowner as per the admin guide while running this script. the previous 2 scripts in this section ran without error.

        Comment


        • #5
          Re: Error when trying to use DB scripts provided with OTM

          Carmelo,

          I think you're missing some grants to glogowner. Did you encounter any errors with create_glog_roles.sql and create_glog_users.sql?

          Thanks,
          Alan

          Comment


          • #6
            Re: Error when trying to use DB scripts provided with OTM

            I re-ran all the scripts again and it seems to have worked this time. I didn't have any errors when running the create roles and users scripts however I did run into errors when I ran the create synonyms script.

            I ran them all again and after the recompile, there are o invalid objects.

            Thanks.

            Comment


            • #7
              Re: Error when trying to use DB scripts provided with OTM

              I spoke too soon. I see in the logs that when I was creating the tablespaces using the create_gc3_tablespaces script, it was able to create all the tablespaces but at the end of the script, I see the following error:

              BEGIN create_tablespace.create_all_ts('d:\oracle\oradata ', '1024000K','2048000K'
              ); END;

              *
              ERROR at line 1:
              ORA-29339: tablespace block size 16384 does not match configured block sizes
              ORA-06512: at "SYS.CREATE_TABLESPACE", line 136
              ORA-06512: at "SYS.CREATE_TABLESPACE", line 497
              ORA-06512: at "SYS.CREATE_TABLESPACE", line 187
              ORA-06512: at "SYS.CREATE_TABLESPACE", line 159
              ORA-06512: at line 1

              Comment


              • #8
                Re: Error when trying to use DB scripts provided with OTM

                Sounds like your database was created with a different block size. I believe the default under windows is 8K. You can do the following instead but for performance reasons it would be better to rebuild the DB with a 16k block size in the first place. I guess if its not going to be a production instance you can get away with this.

                from the Admin guide... pg 3-19

                This script creates LOB tablespaces with 16 KB block size. This is the recommended block size for
                optimal performance. In order to create a tablespace with 16 KB block size, you should have the
                following init.ora parameter set if your database standard block size is not 16 KB. Change the cache
                size as needed for your database.
                db_16k_cache_size = 104857600 # 100MB for 16k block buffers

                Comment


                • #9
                  Re: Error when trying to use DB scripts provided with OTM

                  Thanks Alan, I think for now I will leave it as is seeing how this will not be a PROD server. Next time I re-install OTM on this sandbox server I will go for the bigger block size.

                  Looks like OTM is up and running now. And in case I didn't say it today, I hate installing and running OTM on Windows!

                  Thanks for your help.

                  Carmelo

                  Comment


                  • #10
                    Re: Error when trying to use DB scripts provided with OTM

                    You could also just put that parameter in and bounce the database. The next time you create the tablespace it should work.
                    If my post was helpful please click on the Thanks! button

                    MavenWire Hosting Admin
                    15 years of OTM experience

                    Comment


                    • #11
                      Re: Error when trying to use DB scripts provided with OTM

                      I now get as far as creating the reportowner grants. In the next step, it tells me to create the public synonyms (connected as REPORTOWNER). When I run this script, I get the following error:

                      SQL> @create_public_synonyms
                      SELECT owner, table_owner FROM DBA_SYNONYMS WHERE SYNONYM_NAME = p_synonym_name;
                      *
                      ERROR at line 8:
                      ORA-06550: line 8, column 35:
                      PL/SQL: ORA-00942: table or view does not exist
                      ORA-06550: line 8, column 4:
                      PL/SQL: SQL Statement ignored

                      In the previous step I was able to run this error-free when connacted as GLOGOWNER. When I check in each of the GLOGOWNER and REPORTOWNER instances, I see that PUBLIC.DBA_SYNONYMS exist.

                      Up until this step, all other scripts ran error-free.

                      Comment


                      • #12
                        Re: Error when trying to use DB scripts provided with OTM

                        You probably have an error in the import. Post the import log file and I'll see if I can help.
                        If my post was helpful please click on the Thanks! button

                        MavenWire Hosting Admin
                        15 years of OTM experience

                        Comment


                        • #13
                          Re: Error when trying to use DB scripts provided with OTM

                          Hi Nick...I have zipped up the import log file and attached it here. I see that there are a lot of errors but I assumed that this is because I was importing (again) and that the errors are caused by a table or object that already exists. But then again, I could be way off.

                          Thanks for your help.
                          Attached Files

                          Comment


                          • #14
                            Re: Error when trying to use DB scripts provided with OTM

                            You need to run the create_glog_roles.sql and create_glog_users.sql scripts. This will wipe out all of the data in the database to ensure a clean import. You may get some IMP errors at the end of the import but you can ignore those. If you get any errors while the tables are importing that is bad and needs to be addressed.

                            Try it again and you should be good to go.
                            If my post was helpful please click on the Thanks! button

                            MavenWire Hosting Admin
                            15 years of OTM experience

                            Comment


                            • #15
                              Re: Error when trying to use DB scripts provided with OTM

                              Oh and one other thing:

                              ORA-01658: unable to create INITIAL extent for segment in tablespace INDX


                              You ran out of tablespace for the INDX datafile. Make sure you have these set to autoextend on or make them large enough to import the database. You need at least 5 gigs to the INDX and DATA tablespaces on a GA import. It will be higher if you are copying from a production instance.
                              If my post was helpful please click on the Thanks! button

                              MavenWire Hosting Admin
                              15 years of OTM experience

                              Comment

                              Working...
                              X