Tag: 11g Home

Gather Schema Statistics fails with Ora-20001

Posted on by Osama Mustafa in Database
Cause: FDPSTP failed due to ORA-06550: line 1, column 7:
PLS-00307: too many declarations of ‘ GATHER_ALL_COLUMN_STATS’ match this call
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored.

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Solution :
To Check which objects or tables are locked
sql > SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED
FROM DBA_TAB_STATISTICS
WHERE STATTYPE_LOCKED IS NOT NULL;

TO Unlock all the tables in a schema at once :
sql> exec dbms_stats.unlock_schema_stats(‘schema_owner’);
e.g : sql> exec dbms_stats.unlock_schema_stats(‘apps’);
TO Unlock all Individual tables in a schema at once
sql> exec dbms_stats.unlock_schema_stats(‘table_owner’,’table_name’);
e.g : sql > exec dbms_stats.unlock_schema_stats(‘AR’,’AR_REV_REC_QT’); 
There are two reasons for that error message:
1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.
Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.
Following SQL should have returned one row , not two.
SQL> select a.column_name, nvl(a.hsize,254) hsize
from FND_HISTOGRAM_COLS a
where table_name = ‘JE_BE_LINE_TYPE_MAP’
order by column_name;
COLUMN_NAME HSIZE
—————————— ———-
SOURCE 254
SOURCE 254
2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.
Solution:
Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.
Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.
— identify duplicate rows
select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
group by table_name, column_name
having count(*) > 1;
— Use above results on the following SQL to delete duplicates
delete from FND_HISTOGRAM_COLS
where table_name = ‘&TABLE_NAME’
and column_name = ‘&COLUMN_NAME’
and rownum=1;
— Use following SQL to delete obsoleted rows
delete from FND_HISTOGRAM_COLS
where (table_name, column_name) in
(
select hc.table_name, hc.column_name
from FND_HISTOGRAM_COLS hc , dba_tab_columns tc
where hc.table_name =’&TABLE_NAME’
and hc.table_name= tc.table_name (+)
and hc.column_name = tc.column_name (+)
and tc.column_name is null
);

Upgrade Steps From 10g to 11gR2

Posted on by Osama Mustafa in Articles, Database
Please Follow the Below Steps For Upgrading 10g to 11gR2 , Before Doing Anything you need to take backup for the following Files :
1.      BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED.
2.      COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0.  THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Step-1 :
 Install  New Oracle Database Software & Apply Any Patches Necessary.

Step-2 :

Run The Pre-Upgrade Information Tools : (Mandatory) 


1.      Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.
2.      Set your environment to the one that is being upgraded.  Assuming 10g.
3.      Change directory to /tmp that you copied utlu112i.sql to in Step 1.
4.      Start SQL*Plus and login as ‘/ as sysdba’
5.      Spool the results to a log file:
o   SQL> SPOOL upgrade_info.log
6.      Run the Pre-Upgrade Information Tool:
o   SQL> @utlul12i.sql
o   SQL> SPOOL OFF 

Open The Spool File for Checking Purpose :
 Oracle recommends gathering stats before the upgrade:  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network Utility Packages
■ Database Links with passwords
■ TIMESTAMP WITH TIME ZONE Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise Manager Database Control Data
■ Complete Materialized View Refreshes
■ Ensure No Files Need Media Recovery
■ Ensure No Files Are in Backup Mode
■ Resolve Outstanding Distributed Transactions
■ Sync Standby Database with the Primary Database
■ Purging the Database Recycle Bin

Step-3 : 
Prepare 11gR2 Home :

1.      Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.
2.      Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3.      Update any relative path names in parameter file to fully path names.

Step-4 : 
After all the Above Steps , you will start the upgrade Database for 10g Home :

1.      Shutdown the database:
o   SQL> SHUTDOWN IMMEDIATE;
2.      Make sure the following checks:
o   The oratab file points to Oracle Database 11g Release 2 Oracle Home
o   The following environment variables point to the Oracle 11g Release 2 directories:
§  ORACLE_HOME
§  PATH
3.      Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
o   sqlplus ‘/ as sysdba’
4.      Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):
o   SQL>  STARTUP UPGRADE;
o   SQL>  SPOOL upgrade.log
5.      Run the catupgrd.sql script:
o   SQL>  @catupgrd.sql
6.      Once completed, shutdown the database and restart it.
o   SQL>  SHUTDOWN IMMEDIATE;
o   SQL>  STARTUP;
7.      Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)
o   SQL> @utlul12s.sql
8.      Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:
o   SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql
9.      Run utlrp to recompile any remaining stored PL/SQL and other objects.
o   SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
10.  Verify that all objects are valid:
o   SQL> SELECT count(*) FROM dba_invalid_objects;
o   SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thank you 
Osama mustafa