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

[cspadola]

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

[acuartero]

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

[cspadola]

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.

[cspadola]

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.

[acuartero]

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

[cspadola]

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.

[cspadola]

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

[acuartero]

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

[cspadola]

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

[nick.polites]

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.

[cspadola]

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.

[nick.polites]

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.

[cspadola]

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.

[nick.polites]

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.

[nick.polites]

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.