Error from migrate database from UNIX to LINUX
- Thread starter devilson
- Start date
Kishor Chari
Member
The problem is caused by a missing privilege grant on an Oracle system object. This privilege is required for all MFG/PRO versions in which the User Count functionality is implemented. This grant must be performed during the upgrade to MFG/PRO version 8.5 release G and above, as well as to version 8.6 release D and above. The grant command was omitted from the 8.5G and 8.6E upgrade procedure and SQL script that creates the Oracle database.
This problem was corrected by adding the necessary command to the Oracle SQL scripts beginning with MFG/PRO 9.0. If this error occurs in MFG/PRO versions after 8.6E, including 9.0, eB and eB2, review the log files created by each Oracle database creation (crdb1*.sql and crdb2*.sql) and the oraempty-idx.sql scripts for errors indicating the script failed to complete successfully. Common causes of problems are running the scripts as the wrong Oracle user, failing to run oraempty-idx.sql, and having inadequate disk space. Correct any problems and rerun the affected SQL script(s).
For MFG/PRO versions 8.5G, 8.6D and 8.6E, grant access to this Oracle system object then rerun lvorasp.plb using the following procedure:
Login to the database server as the Oracle software owner.
Login to the Oracle instance as the user "SYS" and grant the missing privilege to the Oracle object:
In Oracle svrmgr (for Oracle versions that use the Server Manager tool):
SVRMGR> connect internal;
SVRMGR> grant select on v_$session to public;
OR
In SQL*Plus (for Oracle versions that do not use the Server Manager tool):
Start SQL*Plus: sqlplus "/ as sysdba"
SQLPLUS> grant select on v_$session to public;
Start a SQL*Plus session as the Oracle schema owner for MFG/PRO and run the lvorasp.plb PL/SQL procedure. This procedure implements MFG/PRO's User Count functionality in the Oracle database. For example:
SQLPLUS> connect qad/qad;
SQLPLUS> @lvorasp.plb;
NOTE: In SQL*PLUS, make sure to connect to the Oracle database using the appropriate schema owner ID and password used to create the MFG/PRO database objects (qad/qad in the above example).
This problem was corrected by adding the necessary command to the Oracle SQL scripts beginning with MFG/PRO 9.0. If this error occurs in MFG/PRO versions after 8.6E, including 9.0, eB and eB2, review the log files created by each Oracle database creation (crdb1*.sql and crdb2*.sql) and the oraempty-idx.sql scripts for errors indicating the script failed to complete successfully. Common causes of problems are running the scripts as the wrong Oracle user, failing to run oraempty-idx.sql, and having inadequate disk space. Correct any problems and rerun the affected SQL script(s).
For MFG/PRO versions 8.5G, 8.6D and 8.6E, grant access to this Oracle system object then rerun lvorasp.plb using the following procedure:
Login to the database server as the Oracle software owner.
Login to the Oracle instance as the user "SYS" and grant the missing privilege to the Oracle object:
In Oracle svrmgr (for Oracle versions that use the Server Manager tool):
SVRMGR> connect internal;
SVRMGR> grant select on v_$session to public;
OR
In SQL*Plus (for Oracle versions that do not use the Server Manager tool):
Start SQL*Plus: sqlplus "/ as sysdba"
SQLPLUS> grant select on v_$session to public;
Start a SQL*Plus session as the Oracle schema owner for MFG/PRO and run the lvorasp.plb PL/SQL procedure. This procedure implements MFG/PRO's User Count functionality in the Oracle database. For example:
SQLPLUS> connect qad/qad;
SQLPLUS> @lvorasp.plb;
NOTE: In SQL*PLUS, make sure to connect to the Oracle database using the appropriate schema owner ID and password used to create the MFG/PRO database objects (qad/qad in the above example).
Kishor Chari
Member
Failure to execute lvorasp.plb PL/SQL procedure library to create the COUNT_RETURN stored procedure in the Oracle database.
Make sure that the MFG/PRO oraempty-idx.sql script (primary Oracle index creation script) completed successfully. The last task in this script creates the COUNT_RETURN stored procedure on Oracle by executing the lvorasp.plb PL/SQL procedure library. This stored procedure implements MFG/PRO's User Count functionality. Review the oraempty-idx.lst file to determine if the SQL script ran to completion or if an error occurred while running the PL/SQL procedure library. If all indexes were not created, correct the problem indicated by the error messages in the oraempty-idx.lst file and rerun the script or the failed portion of the script. If all indexes were created successfully and only lvorasp.plb failed, execute the library script independently. For example:
Start a SQL*Plus session as the Oracle schema owner for MFG/PRO
SQLPLUS> connect qad/qad;
SQLPLUS> @lvorasp.plb;
NOTE 1: In SQL*PLUS, make sure to connect to the Oracle database as the appropriate schema owner ID and password used to create the MFG/PRO database objects (qad/qad in the above example).
NOTE 2: Start the SQL session from the directory containing lvorasp.plb (for example: the MFGPROServerInstall/db directory), else provide the full path to lvorasp.plb after the @ symbol.
When lvorasp.plb runs successfully the stored procedure will exist in the Oracle database and be accessible to MFG/PRO clients at startup. You can verify this by querying the USER_PROCEDURES or DBA_PROCEDURES view in Oracle.
Other Possible Causes and Solutions:
---------------------------------------------
If the COUNT_RETURN procedure exists for the Oracle schema owner then the ORA-06550 error can occur when the Progress schema holder's FOREIGN-OWNER value is incorrect. The runtime schema holder must specify the Oracle user ID (by default, QAD) that owns the MFG/PRO schema in each table's FOREIGN-OWNER attribute in the .df file. It is a common mistake to place the Oracle database name in the FOREIGN-OWNER attribute instead of the actual Oracle user ID. So determine the Oracle database name that Progress uses to connect to the Oracle instance as well as the MFG/PRO schema owner then review the schema holder's .df to ensure all FOREIGN-OWNER attributes use the Oracle owner ID. Alternatively, FOREIGN-OWNER can contain ? (the Progress Unknown value) instead of the actual Oracle owner ID. See QAD Kbase qad4447: "How the DataServer connects to an Oracle database and determines the values of Oracle table and sequence owners for Progress client sessions. "for more information on this topic.
If running MFG/PRO on Progress and connecting to a side Oracle database, the ORA-06550 error can occur if the side Oracle database contains triggers which call a stored procedure which either does not exist or has become corrupt. In such cases the stored procedure must be identified and created properly.
Make sure that the MFG/PRO oraempty-idx.sql script (primary Oracle index creation script) completed successfully. The last task in this script creates the COUNT_RETURN stored procedure on Oracle by executing the lvorasp.plb PL/SQL procedure library. This stored procedure implements MFG/PRO's User Count functionality. Review the oraempty-idx.lst file to determine if the SQL script ran to completion or if an error occurred while running the PL/SQL procedure library. If all indexes were not created, correct the problem indicated by the error messages in the oraempty-idx.lst file and rerun the script or the failed portion of the script. If all indexes were created successfully and only lvorasp.plb failed, execute the library script independently. For example:
Start a SQL*Plus session as the Oracle schema owner for MFG/PRO
SQLPLUS> connect qad/qad;
SQLPLUS> @lvorasp.plb;
NOTE 1: In SQL*PLUS, make sure to connect to the Oracle database as the appropriate schema owner ID and password used to create the MFG/PRO database objects (qad/qad in the above example).
NOTE 2: Start the SQL session from the directory containing lvorasp.plb (for example: the MFGPROServerInstall/db directory), else provide the full path to lvorasp.plb after the @ symbol.
When lvorasp.plb runs successfully the stored procedure will exist in the Oracle database and be accessible to MFG/PRO clients at startup. You can verify this by querying the USER_PROCEDURES or DBA_PROCEDURES view in Oracle.
Other Possible Causes and Solutions:
---------------------------------------------
If the COUNT_RETURN procedure exists for the Oracle schema owner then the ORA-06550 error can occur when the Progress schema holder's FOREIGN-OWNER value is incorrect. The runtime schema holder must specify the Oracle user ID (by default, QAD) that owns the MFG/PRO schema in each table's FOREIGN-OWNER attribute in the .df file. It is a common mistake to place the Oracle database name in the FOREIGN-OWNER attribute instead of the actual Oracle user ID. So determine the Oracle database name that Progress uses to connect to the Oracle instance as well as the MFG/PRO schema owner then review the schema holder's .df to ensure all FOREIGN-OWNER attributes use the Oracle owner ID. Alternatively, FOREIGN-OWNER can contain ? (the Progress Unknown value) instead of the actual Oracle owner ID. See QAD Kbase qad4447: "How the DataServer connects to an Oracle database and determines the values of Oracle table and sequence owners for Progress client sessions. "for more information on this topic.
If running MFG/PRO on Progress and connecting to a side Oracle database, the ORA-06550 error can occur if the side Oracle database contains triggers which call a stored procedure which either does not exist or has become corrupt. In such cases the stored procedure must be identified and created properly.
