* GSS for all schemas failed
* ORACLE error 20005 in FDPSTP
Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 805
ORA-06512: at line 1
==>select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;
==>
To unlock schema exec dbms_stats.unlock_schema_stats ('AR');
To unlock Table exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');
ISSUE
Issue Clarification
===================
+ SYSAUX Tablespace growing abnormally
Action plan Note.852028.1 / SR .1319894591
===========
+ Take cold backup
+ change retention to 4 minutes (currently it is 7 minutes)
* SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 5760);
Here retention is in minutes so 4 x 24 x 60 = 5760 minutes
+ Purging snaps from dec 17 to dec 31
* exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 22760, high_snap_id=>24129);
+ Set statistics_level parameter to TYPICAL
* SQL> alter system set statistics_level=TYPICAL;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Note 752057.1
AD Administration error:
The following ORACLE error:
ORA-28000: the account is locked
occurred while executing the SQL statement:
CONNECT APPLSYS/*****
AD Administration error:
Error getting configuration information in adadmin_main()
You should check the file
/dcab1i/applmgr/1200/admin/DCAB1I/log/adadmin.log
select * from dba_profiles where PROFILE='DEFAULT';
created new profile assigned to APPLSYS
we can revert the back to DEFAULT once the action is done
SQL> ALTER USER APPLSYS PROFILE appusr_default;
User altered.
SQL> ALTER USER APPLSYS ACCOUNT UNLOCK;
User altered.
After the action plan completed pls execute as system user
+ALTER USER APPLSYS PROFILE DEFAULT;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
utlrp failing
error
====
ERROR:
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_OUTPUT
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT"
ORA-06512: at line 1
Tried Workaround
==============
ref rfc .3BK7JUR
+ Disabled the trigger NAMECHECK_BEFORE_DDL_DB_TRG
SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,STATUS from dba_triggers where TRIGGER_NAME='NAMECHECK_BEFORE_DDL_DB_TRG';
OWNER TRIGGER_NAME TRIGGER_TYPE STATUS
------------------------------ ------------------------------ ---------------- --------
SYS NAMECHECK_BEFORE_DDL_DB_TRG BEFORE EVENT ENABLED
alter trigger NAMECHECK_BEFORE_DDL_DB_TRG disable;
OWNER TRIGGER_NAME TRIGGER_TYPE STATUS
------------------------------ ------------------------------ ---------------- --------
SYS NAMECHECK_BEFORE_DDL_DB_TRG BEFORE EVENT DISABLED
utlrp running fine now
SQL> select count (*) from dba_objects where status='INVALID';
COUNT(*)
----------
160341
SQL> /
COUNT(*)
----------
131268
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Gather Schema Statistics -> completed with error
Cause: FDPSTP failed due to ORA-20000: Unable to analyze TABLE "APPS"."CPDEFERSAMESITE371_TAB", insufficient privileges or does not exist
ORA-06512: at "APPS.FND_STATS", line 768
ORA-06512: at line 1
.
The SQL statement bein
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 20 degree = 3 internal_flag= NOBACKUP
ORA-20000: Unable to analyze TABLE "APPS"."CPDEFERSAMESITE371_TAB", insufficient privileges or does not exist
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where object_name like '%COSTPARAMETERS372%'
2 ;
no rows selected
The Following tables are created with lowe case hence GSS is unable to pick the tables.
SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where object_name like '%372%';
APPS costParameters372_TAB
APPS costParameters372_TAB$xd
The above two tables might have created using quotes '"'
Action Plan
-------------
Get approval from customer to rename below two tables
+ connect as APPS
+ rename below tables
costParameters372_TAB to COSTPARAMETERS372_TAB
costParameters372_TAB$xd to COSTPARAMETERS372_TAB$XD
+and rerun the GSS once again.
Thanks
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue Description : OS 64 Bi migration Done, After installing software sqlplus
error out
bash-3.00$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 29 15:54:00 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Solution
=========
whenever there is an OS upgrade, the cc image add this huge group that does not let RDBMS connect to the DB
for it can only read up to 132 characters
solution is to move these huge groups to the end of the file
move huge groups to the end of the file (in /etc/group file)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue :
alter index GEOR_COA_MAP.GEOR_GEFA_GNW_COA_B_XPK rebuild tablespace intfx parallel 16
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance
aohsgeor89:TGEO2I1 (1)
ORA-01950: no privileges on tablespace 'INTFX'
Solution
========
SQL> ALTER USER GEOR_COA_MAP QUOTA 100M ON INTFX;
User altered.
SQL> alter index GEOR_COA_MAP.GEOR_GEFA_GNW_COA_B_XPK rebuild tablespace intfx parallel 16;
Index altered.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue : adstats.sql ==> failed
--- adstats.sql started at 2011-01-29 21:49:31 ---
Please don't start any services until this script finish
declare
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
[1350], [1], [23], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 20509
ORA-06512: at "SYS.DBMS_STATS", line 20958
ORA-06512: at "SYS.DBMS_STATS", line 21499
ORA-06512: at line 5
ORA-06512: at line 17
-bash-3.2$ ls -ld $ORACLE_HOME/nls/data/9idata
drwxr-xr-x+ 2 ordppgri dbdppgri 560 Jan 28 02:46 /dppgri/oracle/product/112/nls/data/9idata
-bash-3.2$
SQL> echo $ORA_NLS10
Solution
======
-bash-3.2$ export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
-bash-3.2$ echo $ORA_NLS10
/dppgri/oracle/product/112/nls/data/9idata
Reran the script
SQL> @adstats.sql
Connected.
System altered.
--------------------------------------------------
--- adstats.sql started at 2011-01-29 23:36:42 ---
--------------------------------------------------
ANAYYZE table partition issue
-----------------------------
Following NOTE ID 111990.1
$ pwd
/pleari/oracle/product/102/rdbms/admin
$ ls -l utlvalid.sql
-rw-rw-r-- 1 orpleari dbpleari 747 Jun 24 1998 utlvalid.sql
SQL> sho user
USER is "APPLSYS"
SQL> @utlvalid.sql
Table created.
SQL>
SQL> select count(*) from invalid_rows;
COUNT(*)
----------
0
SQL>
SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='WF_USER_ROLE_ASSIGNMENTS'
2 ;
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_USER_ROLE_ASSIGNMENTS AMV_APPR
APPLSYS WF_USER_ROLE_ASSIGNMENTS AMV_CHN
APPLSYS WF_USER_ROLE_ASSIGNMENTS ENG_LIST
APPLSYS WF_USER_ROLE_ASSIGNMENTS FND_RESP
APPLSYS WF_USER_ROLE_ASSIGNMENTS FND_USR
APPLSYS WF_USER_ROLE_ASSIGNMENTS GBX
APPLSYS WF_USER_ROLE_ASSIGNMENTS HTB_SEC
APPLSYS WF_USER_ROLE_ASSIGNMENTS HZ_GROUP
APPLSYS WF_USER_ROLE_ASSIGNMENTS HZ_PARTY
APPLSYS WF_USER_ROLE_ASSIGNMENTS PER_ROLE
APPLSYS WF_USER_ROLE_ASSIGNMENTS POS
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_USER_ROLE_ASSIGNMENTS PQH_ROLE
APPLSYS WF_USER_ROLE_ASSIGNMENTS UMX
APPLSYS WF_USER_ROLE_ASSIGNMENTS WF_LOCAL_ROLES
14 rows selected.
SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='WF_LOCAL_USER_ROLES';
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_LOCAL_USER_ROLES AMV_APPR
APPLSYS WF_LOCAL_USER_ROLES AMV_CHN
APPLSYS WF_LOCAL_USER_ROLES ENG_LIST
APPLSYS WF_LOCAL_USER_ROLES FND_RESP
APPLSYS WF_LOCAL_USER_ROLES FND_USR
APPLSYS WF_LOCAL_USER_ROLES GBX
APPLSYS WF_LOCAL_USER_ROLES HTB_SEC
APPLSYS WF_LOCAL_USER_ROLES HZ_GROUP
APPLSYS WF_LOCAL_USER_ROLES HZ_PARTY
APPLSYS WF_LOCAL_USER_ROLES PER_ROLE
APPLSYS WF_LOCAL_USER_ROLES POS
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_LOCAL_USER_ROLES PQH_ROLE
APPLSYS WF_LOCAL_USER_ROLES UMX
APPLSYS WF_LOCAL_USER_ROLES WF_LOCAL_ROLES
14 rows selected.
+There are 14 Partitions for tables WF_LOCAL_USER_ROLES and WF_USER_ROLE_ASSIGNMENTS;
SQL> analyze table WF_USER_ROLE_ASSIGNMENTS partition (AMV_APPR) validate structure cascade into invalid_rows;
Table analyzed.
+ Requesting Analyst to Alalyze for rest of the partitions:
syntax
-------
analyze table WF_USER_ROLE_ASSIGNMENTS partition (partition_name) validate structure cascade into invalid_rows;
Thanks
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue: some one changed apps pwd using alter user
Solution:
updated FND_ORACLE_USERID to have same ENCRYPTED_ORACLE_PASSWORD for APPLSYS and APPS
SQL> SELECT oracle_id, read_only_flag, ORACLE_USERNAME, encrypted_oracle_password FROM apps.FND_ORACLE_USERID WHERE ORACLE_USERNAME in ( 'APPLSYS', 'APPS' ) ;
ORACLE_ID R ORACLE_USERNAME
---------- - ------------------------------
ENCRYPTED_ORACLE_PASSWORD
--------------------------------------------------------------------------------
0 E APPLSYS
ZG91C91B5F3A04D35A0B3420DE7A3DC1DE35B8D6B914F65744DB55B77DE7F3753163267210CCC9CA
E2C876B9FDC96B834E78
900 U APPS
ZG91C91B5F3A04D35A0B3420DE7A3DC1DE35B8D6B914F65744DB55B77DE7F3753163267210CCC9CA
E2C876B9FDC96B834E78
+ ALTER USER APPS IDENTIFIED BY < applsys pwd which is working>
retest the issue
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Db autoconfig failing
=====================
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
<filename> <phase> <return code where appropriate>
[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /pexc5i/oracle/product/111/appsutil/install/PEXC5I1_aohsexcc25
afdbprf.sh INSTE8_PRF 1
[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /pexc5i/oracle/product/111/appsutil/install/PEXC5I1_aohsexcc25
adcrobj.sh INSTE8_APPLY 1
AutoConfig is exiting with status 2
Solution
=========
=> Resolved the afdbprf.sh issue by editing the template for afdbprf.sql as follows :-
bash-3.2$ pwd
/pexc5i/oracle/product/111/appsutil/template
bash-3.2$ ls -l afdbprf.sql
-rw-r--r-- 1 orpexc5i dboracrs 2797 Mar 13 05:55 afdbprf.sql
Before
-------
32 connect &1/&2@&3
After
-----
32 REM connect &1/&2@&3
33 connect &1/&2
=> Resolved the adcrobj.sh issue by editing the template for adcrobj.sql as follows :-
bash-3.2$ pwd
/pexc5i/oracle/product/111/appsutil/template
bash-3.2$ cp adcrobj.sql adcrobj.sql_bkp
Before
-------
32 connect &1/&2@&3
After
-----
32 REM connect &1/&2@&3
33 connect &1/&2
=> AC on DB tier completed successfully
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SSUE:
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1.8840E+10 bytes
Fixed Size 2167576 bytes
Variable Size 7247760616 bytes
Database Buffers 1.1543E+10 bytes
Redo Buffers 47550464 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 17654
Session ID: 1200 Serial number: 5
alert log
==========
ORA-1092 : opitsk aborting process
Tue Mar 22 18:16:47 2011
Starting ORACLE instance (normal)
Oracle image stored on remote server adc36stor04-nas
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
From trace file
-------------------
*** 2011-0.22 18:14:45.344
Validate domain 0
Validated domain 0, flags = 0x0
*** 2011-0.22 18:14:51.551
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
*** 2011-0.22 18:14:51.557
USER (ospid: 20836): terminating the instance due to error 39701
+ Workaround.
SQL> sho parameter CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
System altered.
SQL> sho parameter CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1.8840E+10 bytes
Fixed Size 2167576 bytes
Variable Size 7247760616 bytes
Database Buffers 1.1543E+10 bytes
Redo Buffers 47550464 bytes
Database mounted.
Database opened.
Note : After utlirp.sql is completed set CLUSTER_DATABASE back to TRUE.
ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
OLAP Catalog VALID 10.2.0.4.0 is missing after migration
Action plan
-----------
+ Observed that olap.sql was not ran as '@' was missing at the begning of the script olap.sql
Run /olap/admin/olap.sql SYSAUX TEMP
+ @?/olap/admin/olap.sql SYSAUX TEMP
+ Bounce database
+ run utlrp.sql
Thanks
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE OLAP catalog showa INVALID
I've tried solution from
Invalid Olap Catalog And Mising CWM$ Tables (Doc ID 1308148.1)
but that did not resolve the issue.
Then noticed that there are many OLAP objects duplicated - INVLIDS in SYS while valid in OLAPSYS.
Comparing to PROD:
SQL> select object_name, object_type, count(*) from dba_objects where owner in
('SYS','OLAPSYS') and object_type in ('VIEW','PACKAGE','PACKAGE BODY') group by object_name,
object_type having count(*) > 1;
no rows selected
The same query on TEST returned many rows.
So I've basically dropped all the objects from that list owned by SYS. Then Ran utlrp.
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
OLAP Catalog VALID 10.2.0.4.0
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Doc ID 358015.1
Queries on dba_registry results with this error:
ORA-04063: View "SYS.DBA_REGISTRY" has errors..
Cause
DBA_REGISTRY is invalid and errors out when compiled
ORA-04063: View "SYS.DBA_REGISTRY" Has Errors. " (Doc ID 358015.1)
Modified29-JUL-2010TypePROBLEMStatusMODERATED(EXTERNAL)Priority3To Bottom
In this Document
Symptoms
Cause
Solution
Platforms: 1-914CU;
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.8 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2010***
Symptoms
Queries on dba_registry results with this error:
ORA-04063: View "SYS.DBA_REGISTRY" has errors..
.
Cause
DBA_REGISTRY is invalid and errors out when compiled
Compiling view DBA_REGISTRY results in this error:
OWNER NAME TYPE SEQUENCE LINE POSITION
--------------- ------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
SYS DBA_REGISTRY VIEW 1 0 0
ORA-00904: "DBMS_REGISTRY"."SCHEMA_LIST_STRING": invalid identifier
.
Solution
Recreate the DBMS_REGISTRY package and the DBA_REGISTRY view. As sysdba run :
$ORACLE_HOME/rdbms/admin/prvtcr.plb
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
System tablespace conversion is failing
QL> EXEC dbms_space_admin.tablespace_migrate_to_local('SYSTEM');
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, APPS_UNDOTS1, TEMP2 not found in read
only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
Analysis:
=========
The temporary tablespace assigned to users is tablespace group TEMP:
SQL> select * from DBA_TABLESPACE_GROUPS;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2
TEMP TEMP3
TEMP TEMP4
SQL> select * from v$tempfile;
Currently all those temporary tablespaces are online and causes the package
dbms_space_admin.tablespace_migrate_to_local to fail
need to create one new default temporary tablespacefor the action and put offline the temp
tablespace components:
Action done:
============
SQL> create temporary tablespace temptemp tempfile '/tidbki/oradata/data02/temptemp.dbf' size
2000M;
Tablespace created.
SQL> drop tablespace temp4;
Tablespace dropped.
SQL> drop tablespace temp3;
Tablespace dropped.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> drop tablespace temp1;
Tablespace dropped.
------------------------------------------
Please restart the conversion script
------------------------------------------
When the script completes, pleate create back the temporary tablespaces as it was:
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue
=======
SQL> @conv_system.sql
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, APPS_UNDOTS1, TEMP not found in read
only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
Findings
=======
SQL> select TABLESPACE_NAME,STATUS from dba_Tablespaces where status not like 'READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP1 ONLINE
SYSAUX ONLINE
APPS_UNDOTS1 ONLINE
APPS_UNDOTS2 ONLINE
APPS_4K_UNDOTS1 ONLINE
Solution
=======
Before converting system to local
1) offline the sysaux tablespace
2) drop APPS_UNDOTS2 and APPS_4K_UNDOTS1
then convert system to local
bring sysaux to online
SME
SQL> @/ood_repository/patches/DSDCVI/DATABASE11G_AUTOMATION_11R20X_WRK/sql/conv_system.sql
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, APPS_UNDOTS1, APPSTEMP not found in
read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
SQL> select TABLESPACE_NAME,STATUS from dba_Tablespaces where status not like 'READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
APPS_UNDOTS1 ONLINE
SYSAUX OFFLINE
TMP1 ONLINE
APPSTEMP ONLINE
SQL>
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where DEFAULT_TABLESPACE like '%TMP%';
no rows selected
dropped nondefault tablespace
SQL> drop tablespace TMP1 including contents and datafiles;
Tablespace dropped.
SQL> @/ood_repository/patches/DSDCVI/DATABASE11G_AUTOMATION_11R20X_WRK/sql/conv_system.sql
PL/SQL procedure successfully completed.
SQL>
+ Proceed with further steps
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue : aflobbld.sql failing
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in dreii0fsh
ORA-01653: unable to extend table APPLSYS.DR$FND_LOBS_CTX$I by 8192 in
tablespace APPS_TS_TX_DATA
ORA-06512: at line 277
TABLESPACE_NAME Allocated Used/Unused MB Allocated_Free MB %Allocated_Free MB
Space_AutoExtensible MB MaxChunk MB
-------------------- ------------------------ ----------------- ------------------
----------------------- -----------
APPS_TS_TX_DATA 395000 127906 32
0 512
APPS_TS_TX_DATA_OATM 508544 508173 100
671456 2359.875
Workaround
---------------
1. Take a snapshot of the original state of the fnd_lobs entries for comfort sake:
select
FILE_FORMAT,
count(*)
from
fnd_lobs
group by FILE_FORMAT;
2. Set the file_format column of all rows other than FND_HELP so that they won't get indexed:
update fnd_lobs
set FILE_FORMAT = 'IGNORE'
where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ;
commit;
3. Most versions of aflobbld.sql, as written, will only rebuild the FND_LOBS_CTX index if the
status of the index is invalid or missing. To force the rebuilding of the index we may first have
to drop it.
Check the status of the fnd_lobs_ctx index. If it is valid, then drop it.
select status
from all_indexes
where owner='APPLSYS'
and index_name='FND_LOBS_CTX'
and table_name='FND_LOBS';
If this returns a status of "valid", then "drop index applsys.fnd_lobs_ctx
force"
4. Run aflobbld.sql. This takes two parameters; the name of the applsys schema and the name of the
apps schema.
Typically "@aflobbld.sql applsys apps".
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Database not starting
SQL> startup
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11953
Session ID: 505 Serial number: 3
ARC0: Error 19504 Creating archive log file to '/sid/arch/sid_1_5272_771798806.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance sid - Archival Error
ORA-16038: log 4 sequence# 5272 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 4 thread 1: '/sid/oradata/data03/log04a.dbf'
ORA-00312: online log 4 thread 1: '/sid/oradata/data03/log04b.dbf'
Wed Feb 29 19:05:45 2012
Solution
========
Changed the archive destination from /sid/arch to other location available location eg /sid/log/arch
and started database,
db started sucessfully
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue
sid/oracle> sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue May 22 05:08:15 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
On node 30:
/sid/oracle/product/112/rdbms/lib> strings config.o
dbpworti
--- this is post refresh issue.
Fixed as follows:
/sid/oracle/product/112/rdbms/lib> rm config.o
/sid/oracle/product/112/rdbms/lib> make -f ins_rdbms.mk ioracle
chmod 755 /sid/oracle/product/112/bin
- Linking Oracle
rm -f /sid/oracle/product/112/rdbms/lib/oracle
gcc -o /sid/oracle/product/112/rdbms/lib/oracle -m64 -L/sid/oracle/product/112/rdbms/lib/
-L/sid/oracle/product/112/lib/ -L/sid/oracle/product/112/lib/stubs/ -Wl,-E
/sid/oracle/product/112/rdbms/lib/opimai.o /sid/oracle/product/112/rdbms/lib/ssoraed.o
/sid/oracle/product/112/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv11
-lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged
-lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11
-lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11
-lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11
-lcommon11 -lcore11 -laio `cat /sid/oracle/product/112/lib/sysliblist`
-Wl,-rpath,/sid/oracle/product/112/lib -lm `cat /sid/oracle/product/112/lib/sysliblist`
-ldl -lm -L/sid/oracle/product/112/lib
test ! -f /sid/oracle/product/112/bin/oracle ||\
mv -f /sid/oracle/product/112/bin/oracle /sid/oracle/product/112/bin/oracleO
mv /sid/oracle/product/112/rdbms/lib/oracle /sid/oracle/product/112/bin/oracle
chmod 6751 /sid/oracle/product/112/bin/oracle
/sid/oracle/product/112/rdbms/lib> sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Tue May 22 05:36:03 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
on MT
----
$ sqlplus sys/X0zTJ0qh as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 24 21:55:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Solution :
Create password file correctly
$ORACLE_HOME/dbs/orapwd FILE=orapwDTWRI password=syspassword entries=10
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Oracle Real Application Clusters 11.2.0.3.0 INVALID
SQL> startup pfile='/tmp/pfile-rac'
ORA-00439: feature not enabled: Real Application Clusters
ORA-01078: failure in processing system parameters
Because => An Oracle Home disabled RAC.
solution
Before that modified '/tmp/pfile-rac' file use *.cluster_database=true to support RAC
So, we should relink library to enable RAC on this Oracle Home
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+ unable to start listner
bash-3.2$ lsnrctl start $ORACLE_SID
lsnrctl: symbol lookup error: lsnrctl: undefined symbol: nlgh
+ tried to relink
-bash-3.2$ ./relink all
writing relink log to: /sid/oracle/product/112/install/relink.log
./relink: line 181: 5562 Segmentation fault $RUNINSTALLER $ARGS > $LOGFILE 2>&1
Solution:
-bash-3.2$ cd $ORACLE_HOME/network/lib
-bash-3.2$ make -f ins_net_server.mk install
+ start listner lsnrctl start $ORACLE_SID
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue
Done step prepare_db_for_migration
Running step undo_reorg
ERROR : undo_reorg.pl FAILED
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_ERR_TRIGGER
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_TOPO_DROP_FTBL
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
occurred when try to drop TABLESPACE APPS_UNDOTS1
issue fixed as follow
=============
1-alter trigger NAMECHECK_BEFORE_DDL_DB_TRG disable;
2-alter system set undo_tablespace=APPS_UNDOTS1 scope=both
.drop TABLESPACE BOB_UNDO INCLUDING CONTENTS AND DATAFILES;
4-rerun the automation again starting from step 8
after the upgrade completed please enable the trigger again
Thanks and Regards
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+ issue unable to create spfile.
observation
===========
SQL> create spfile='/sid/oradata/data01/spfilesid.ora' from pfile='/sid/oracle/product/112/dbs/initsid.ora';
create spfile='/sid/oradata/data01/spfilesid.ora' from pfile='/sid/oracle/product/112/dbs/initsid.ora'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12658
Session ID: 10 Serial number: 36
spfiles is getting deleted after creating..
tried to create pfile from theother non-prod
+ parameter file got corrupted
+ created new parameter file
+ now able to create spfile from pfile
SQL> startup pfile='/sid/oracle/product/112/dbs/initsid.ora';
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 2080378712 bytes
Database Buffers 2147483648 bytes
Redo Buffers 45699072 bytes
Database mounted.
Database opened.
SQL> create spfile='/sid/oradata/data01/spfilesid.ora' from pfile='/sid/oracle/product/112/dbs/initsid.ora';
File created.
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE:
db upgrade
run migration failed
----
=============================
begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ERROR:
ORA-03114: not connected to ORACLE
WORK AROUND
============
Ran below steps manually (ref (Doc ID 852917.1))
------------------------
SQL> begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
initjvmaux.rollbacksetup;
commit;
initjvmaux.rollbackset;
initjvmaux.exec('create or replace java system');
commit;
2 3 4 5 6 7 initjvmaux.rollbackcleanup;
initjvmaux.endstep;
end if; end;
/
8 9 10
PL/SQL procedure successfully completed.
SQL>
Try to restart the automation
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue :
--------
5
Running step recompile_invalids
ERROR : recompile_invalids.pl FAILED
Analysis :
----------
1) Is same issue occurred in non-prod ( Yes/No/NA) :NA
2) Reviewed Non-prod RFC ( Yes / No /NA ) :NA
3) Reviewed issue resolution portal ( Yes / No ) :yes
4) Observations :-
LOG : Command result : Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
LOG : Checking logfile /ood_repository/patches/TCPUSI/DATABASE11G_AUTOMATION_11R204_WRK/TCPUSI/CONSOLE_LOGS/../MIGRATION_LOGS/SQL_LOG/rdbms_utlrp.log for errors ....
LOG : Found ERROR : ORA-03113: end-of-file on communication channel
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
ERROR : Errors found while running utlrcmp.sql
ERROR : sql file that was run is /ood_repository/patches/TCPUSI/DATABASE11G_AUTOMATION_11R204_WRK/TCPUSI/CONSOLE_LOGS/../MIGRATION_LOGS/SQL_LOG/rdbms_utlrp.sql
ERROR : log file generated is /ood_repository/patches/TCPUSI/DATABASE11G_AUTOMATION_11R204_WRK/TCPUSI/CONSOLE_LOGS/../MIGRATION_LOGS/SQL_LOG/rdbms_utlrp.log
ERROR : Please check log file to see if the errors are ignorable
ERROR : If the errors can be ignored, you can restart the run
ERROR : If running in standalone mode, restart will require
ERROR : editing the script run_upgrade.sh
LOG : CURRENT STEP FAILED
Findings
-----------
SQL> select ACTIVE_STATE,DATABASE_STATUS,STATUS,STARTUP_TIME from v$instance;
ACTIVE_ST DATABASE_STATUS STATUS STARTUP_T
--------- ----------------- ------------ ---------
NORMAL ACTIVE OPEN 10-FEB-15
SQL> sho parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
Solution
---------
SQL> alter system set compatible='11.2.0.4.0' scope=spfile sid='*';
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 977076224 bytes
Fixed Size 2259200 bytes
Variable Size 348129024 bytes
Database Buffers 612368384 bytes
Redo Buffers 14319616 bytes
Database mounted.
Database opened.
SQL>
SQL> sho parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
SQL>
+ Restart the automation form step " 5. START FROM recompile_invalids"
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SSUE:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
+ serveroutput size in script is set to 1000000
Made below changes in the script
bash-3.2$ diff ProcessMonitorSubtypeFix_v1.1.sql ProcessMonitorSubtypeFix_v1.1.sql_bkp12dec13
1c1
< set serveroutput on size unlimited;
---
> set serveroutput on size 1000000
bash-3.2$
+ request to rerun the script now
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE : unable to conect sqlplus
bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 23 19:54:00 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Observation:
bash-3.2$ cd /sid/oracle/product/112/rdbms/lib
bash-3.2$ ls -lrt config.o
-rw-r--r-- 1 orsid dbsid 1328 Sep 14 23:01 config.o
bash-3.2$ strings config.o
dbpavcri <<<<<<<<<<<<
Solution:
==========
bash-3.2$ cp config.o config.o_abkp
bash-3.2$ rm config.o
bash-3.2$ make -f ins_rdbms.mk ioracle
+ Able to connect now
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
observed that apps and applsys are not connecting with same password
SQL> conn apps/****
Connected.
SQL> conn applsys/****
ERROR:
ORA-04045: errors during recompilation/revalidation of
XXPEPSICO_IBERICA.XXPEPIB_ON_LOGON_TRIGGER
ORA-01031: insufficient privileges
CAUSE:TRIGGER" XXPEPSICO_IBERICA.XXPEPIB_ON_LOGON_TRIGGER" IS INVALID AND CAUSING TO FAIL WHILE CONNECTING TO APPLSYS
SOLUTION:
---------
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------------------ ------------------- -------
XXPEPSICO_IBERICA XXPEPIB_ON_LOGON_TRIGGER TRIGGER INVALID
SQL> ALTER TRIGGER XXPEPSICO_IBERICA.XXPEPIB_ON_LOGON_TRIGGER COMPILE;
Trigger altered.
SQL> conn applsys/***
Connected.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE:SQL> startup restrict;
ORA-01078: failure in processing system parameters
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGE
SOLUTION:
==========
SQL> alter system set memory_target=0 scope= spfile;
System altered.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 4526469120 bytes
Fixed Size 2220720 bytes
Variable Size 2197818704 bytes
Database Buffers 2315255808 bytes
Redo Buffers 11173888 bytes
Database mounted.
Database opened.
SQL>
Shutdown and started normally
to execution analyst
+ After the action plan
+ revert memory_target to 6G
alter system set memory_target=6G scope= spfile;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/sid/oradata/data02/system01.dbf'
resolved after nfs locks clearing
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE
-bash-3.2$ ls -lrt /sid/oradata/data01/intfd02.dbf
ls: /sid/oradata/data01/intfd02.dbf: No such file or directory
-bash-3.2$ cd /sid/oradata/data01/
-bash-3.2$ ls -lrt intfd02.dbf
-rw-r----- 1 orsid dbsid 104865792 Sep 15 01:34 intfd02.dbf
-bash-3.2$
SQL> select FILE_NAME,FILE_ID from dba_data_files where
FILE_NAME='/sid/oradata/data01/intfd02.dbf';
no rows selected
SQL> select FILE_NAME,FILE_ID from dba_data_files where FILE_NAME like '%intfd%';
FILE_NAME FILE_ID
------------------------------------------------------------ ----------
/sid/oradata/data01/intfd01.dbf 12
/sid/oradata/data01/intfd02.dbf 118
SQL> select FILE_NAME,FILE_ID from dba_data_files where
FILE_NAME='/sid/oradata/data01/intfd02.dbf ';
FILE_NAME FILE_ID
------------------------------------------------------------ ----------
/sid/oradata/data01/intfd02.dbf 118
+ Findings
Datafile was created with space at the end of the file
Action Plan
-----------
sqlplus: shutdown;
linux# mv '/sid/oradata/data01/intfd02.dbf ' '/sid/oradata/data01/intfd02.dbf'
startup mount;
ALTER DATABASE RENAME FILE '/sid/oradata/data01/intfd02.dbf ' TO
'/sid/oradata/data01/intfd02.dbf'
ALTER DATABASE OPEN;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue
===
BEGIN
move_qt_pkg.move_queue_table('ASO','ASO_ORDER_FEEDBACK_T','APPS_TS_QUEUES','DUMMY');
END;
/ 2 3 4
BEGIN
*
ERROR at line 1:
ORA-32426: materialized view log on "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_S" in DST
upgrade
ORA-06512: at "SYS.MOVE_QT_PKG", line 441
ORA-06512: at "SYS.MOVE_QT_PKG", line 108
ORA-06512: at line 2
SQL> set lines 400
SQL> col PROPERTY_NAME for a30
SQL> col VALUE for a20
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
Solution:
=========
* if DST_UPGRADE_STATE is "UPGRADE" then an upgrade is already in progress, check if an
other DBA is doing a upgrade, if not then issue:
CONN / as sysdba
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE: unable to start database
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_sid'
FINDINGS/OBSERVATION:
----------------------
SQL> create pfile='/sid/oracle/product/112/dbs/sidfromspfile.ora' from
spfile='/sid/oracle/product/112/dbs/spfilesid.ora';
File created.
$ grep -i local /sid/oracle/product/112/dbs/sidfromspfile.ora
*.local_listener='LISTENER_sid'
$
Removed *.local_listener='LISTENER_sid' from pfile and recreated spfile
SQL> create spfile='/sid/oracle/product/112/dbs/spfilesid.ora' from
pfile='/sid/oracle/product/112/dbs/sidfromspfile.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 8351150080 bytes
Fixed Size 2244992 bytes
Variable Size 6006247040 bytes
Database Buffers 2315255808 bytes
Redo Buffers 27402240 bytes
Database mounted.
Database opened.
SQL>
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
* ORACLE error 20005 in FDPSTP
Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 805
ORA-06512: at line 1
==>select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;
==>
To unlock schema exec dbms_stats.unlock_schema_stats ('AR');
To unlock Table exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');
ISSUE
Issue Clarification
===================
+ SYSAUX Tablespace growing abnormally
Action plan Note.852028.1 / SR .1319894591
===========
+ Take cold backup
+ change retention to 4 minutes (currently it is 7 minutes)
* SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 5760);
Here retention is in minutes so 4 x 24 x 60 = 5760 minutes
+ Purging snaps from dec 17 to dec 31
* exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 22760, high_snap_id=>24129);
+ Set statistics_level parameter to TYPICAL
* SQL> alter system set statistics_level=TYPICAL;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Note 752057.1
AD Administration error:
The following ORACLE error:
ORA-28000: the account is locked
occurred while executing the SQL statement:
CONNECT APPLSYS/*****
AD Administration error:
Error getting configuration information in adadmin_main()
You should check the file
/dcab1i/applmgr/1200/admin/DCAB1I/log/adadmin.log
select * from dba_profiles where PROFILE='DEFAULT';
created new profile assigned to APPLSYS
we can revert the back to DEFAULT once the action is done
SQL> ALTER USER APPLSYS PROFILE appusr_default;
User altered.
SQL> ALTER USER APPLSYS ACCOUNT UNLOCK;
User altered.
After the action plan completed pls execute as system user
+ALTER USER APPLSYS PROFILE DEFAULT;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
utlrp failing
error
====
ERROR:
ORA-04045: errors during recompilation/revalidation of SYS.DBMS_OUTPUT
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT"
ORA-06512: at line 1
Tried Workaround
==============
ref rfc .3BK7JUR
+ Disabled the trigger NAMECHECK_BEFORE_DDL_DB_TRG
SQL> select OWNER,TRIGGER_NAME,TRIGGER_TYPE,STATUS from dba_triggers where TRIGGER_NAME='NAMECHECK_BEFORE_DDL_DB_TRG';
OWNER TRIGGER_NAME TRIGGER_TYPE STATUS
------------------------------ ------------------------------ ---------------- --------
SYS NAMECHECK_BEFORE_DDL_DB_TRG BEFORE EVENT ENABLED
alter trigger NAMECHECK_BEFORE_DDL_DB_TRG disable;
OWNER TRIGGER_NAME TRIGGER_TYPE STATUS
------------------------------ ------------------------------ ---------------- --------
SYS NAMECHECK_BEFORE_DDL_DB_TRG BEFORE EVENT DISABLED
utlrp running fine now
SQL> select count (*) from dba_objects where status='INVALID';
COUNT(*)
----------
160341
SQL> /
COUNT(*)
----------
131268
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Gather Schema Statistics -> completed with error
Cause: FDPSTP failed due to ORA-20000: Unable to analyze TABLE "APPS"."CPDEFERSAMESITE371_TAB", insufficient privileges or does not exist
ORA-06512: at "APPS.FND_STATS", line 768
ORA-06512: at line 1
.
The SQL statement bein
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= ALL percent= 20 degree = 3 internal_flag= NOBACKUP
ORA-20000: Unable to analyze TABLE "APPS"."CPDEFERSAMESITE371_TAB", insufficient privileges or does not exist
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where object_name like '%COSTPARAMETERS372%'
2 ;
no rows selected
The Following tables are created with lowe case hence GSS is unable to pick the tables.
SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where object_name like '%372%';
APPS costParameters372_TAB
APPS costParameters372_TAB$xd
The above two tables might have created using quotes '"'
Action Plan
-------------
Get approval from customer to rename below two tables
+ connect as APPS
+ rename below tables
costParameters372_TAB to COSTPARAMETERS372_TAB
costParameters372_TAB$xd to COSTPARAMETERS372_TAB$XD
+and rerun the GSS once again.
Thanks
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue Description : OS 64 Bi migration Done, After installing software sqlplus
error out
bash-3.00$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 29 15:54:00 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Solution
=========
whenever there is an OS upgrade, the cc image add this huge group that does not let RDBMS connect to the DB
for it can only read up to 132 characters
solution is to move these huge groups to the end of the file
move huge groups to the end of the file (in /etc/group file)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue :
alter index GEOR_COA_MAP.GEOR_GEFA_GNW_COA_B_XPK rebuild tablespace intfx parallel 16
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000, instance
aohsgeor89:TGEO2I1 (1)
ORA-01950: no privileges on tablespace 'INTFX'
Solution
========
SQL> ALTER USER GEOR_COA_MAP QUOTA 100M ON INTFX;
User altered.
SQL> alter index GEOR_COA_MAP.GEOR_GEFA_GNW_COA_B_XPK rebuild tablespace intfx parallel 16;
Index altered.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue : adstats.sql ==> failed
--- adstats.sql started at 2011-01-29 21:49:31 ---
Please don't start any services until this script finish
declare
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-00600: internal error code, arguments:
[1350], [1], [23], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 20509
ORA-06512: at "SYS.DBMS_STATS", line 20958
ORA-06512: at "SYS.DBMS_STATS", line 21499
ORA-06512: at line 5
ORA-06512: at line 17
-bash-3.2$ ls -ld $ORACLE_HOME/nls/data/9idata
drwxr-xr-x+ 2 ordppgri dbdppgri 560 Jan 28 02:46 /dppgri/oracle/product/112/nls/data/9idata
-bash-3.2$
SQL> echo $ORA_NLS10
Solution
======
-bash-3.2$ export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
-bash-3.2$ echo $ORA_NLS10
/dppgri/oracle/product/112/nls/data/9idata
Reran the script
SQL> @adstats.sql
Connected.
System altered.
--------------------------------------------------
--- adstats.sql started at 2011-01-29 23:36:42 ---
--------------------------------------------------
ANAYYZE table partition issue
-----------------------------
Following NOTE ID 111990.1
$ pwd
/pleari/oracle/product/102/rdbms/admin
$ ls -l utlvalid.sql
-rw-rw-r-- 1 orpleari dbpleari 747 Jun 24 1998 utlvalid.sql
SQL> sho user
USER is "APPLSYS"
SQL> @utlvalid.sql
Table created.
SQL>
SQL> select count(*) from invalid_rows;
COUNT(*)
----------
0
SQL>
SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='WF_USER_ROLE_ASSIGNMENTS'
2 ;
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_USER_ROLE_ASSIGNMENTS AMV_APPR
APPLSYS WF_USER_ROLE_ASSIGNMENTS AMV_CHN
APPLSYS WF_USER_ROLE_ASSIGNMENTS ENG_LIST
APPLSYS WF_USER_ROLE_ASSIGNMENTS FND_RESP
APPLSYS WF_USER_ROLE_ASSIGNMENTS FND_USR
APPLSYS WF_USER_ROLE_ASSIGNMENTS GBX
APPLSYS WF_USER_ROLE_ASSIGNMENTS HTB_SEC
APPLSYS WF_USER_ROLE_ASSIGNMENTS HZ_GROUP
APPLSYS WF_USER_ROLE_ASSIGNMENTS HZ_PARTY
APPLSYS WF_USER_ROLE_ASSIGNMENTS PER_ROLE
APPLSYS WF_USER_ROLE_ASSIGNMENTS POS
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_USER_ROLE_ASSIGNMENTS PQH_ROLE
APPLSYS WF_USER_ROLE_ASSIGNMENTS UMX
APPLSYS WF_USER_ROLE_ASSIGNMENTS WF_LOCAL_ROLES
14 rows selected.
SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='WF_LOCAL_USER_ROLES';
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_LOCAL_USER_ROLES AMV_APPR
APPLSYS WF_LOCAL_USER_ROLES AMV_CHN
APPLSYS WF_LOCAL_USER_ROLES ENG_LIST
APPLSYS WF_LOCAL_USER_ROLES FND_RESP
APPLSYS WF_LOCAL_USER_ROLES FND_USR
APPLSYS WF_LOCAL_USER_ROLES GBX
APPLSYS WF_LOCAL_USER_ROLES HTB_SEC
APPLSYS WF_LOCAL_USER_ROLES HZ_GROUP
APPLSYS WF_LOCAL_USER_ROLES HZ_PARTY
APPLSYS WF_LOCAL_USER_ROLES PER_ROLE
APPLSYS WF_LOCAL_USER_ROLES POS
TABLE_OWNER TABLE_NAME PARTITION_NAME
-------------------- ------------------------------ ------------------------------
APPLSYS WF_LOCAL_USER_ROLES PQH_ROLE
APPLSYS WF_LOCAL_USER_ROLES UMX
APPLSYS WF_LOCAL_USER_ROLES WF_LOCAL_ROLES
14 rows selected.
+There are 14 Partitions for tables WF_LOCAL_USER_ROLES and WF_USER_ROLE_ASSIGNMENTS;
SQL> analyze table WF_USER_ROLE_ASSIGNMENTS partition (AMV_APPR) validate structure cascade into invalid_rows;
Table analyzed.
+ Requesting Analyst to Alalyze for rest of the partitions:
syntax
-------
analyze table WF_USER_ROLE_ASSIGNMENTS partition (partition_name) validate structure cascade into invalid_rows;
Thanks
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue: some one changed apps pwd using alter user
Solution:
updated FND_ORACLE_USERID to have same ENCRYPTED_ORACLE_PASSWORD for APPLSYS and APPS
SQL> SELECT oracle_id, read_only_flag, ORACLE_USERNAME, encrypted_oracle_password FROM apps.FND_ORACLE_USERID WHERE ORACLE_USERNAME in ( 'APPLSYS', 'APPS' ) ;
ORACLE_ID R ORACLE_USERNAME
---------- - ------------------------------
ENCRYPTED_ORACLE_PASSWORD
--------------------------------------------------------------------------------
0 E APPLSYS
ZG91C91B5F3A04D35A0B3420DE7A3DC1DE35B8D6B914F65744DB55B77DE7F3753163267210CCC9CA
E2C876B9FDC96B834E78
900 U APPS
ZG91C91B5F3A04D35A0B3420DE7A3DC1DE35B8D6B914F65744DB55B77DE7F3753163267210CCC9CA
E2C876B9FDC96B834E78
+ ALTER USER APPS IDENTIFIED BY < applsys pwd which is working>
retest the issue
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Db autoconfig failing
=====================
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution. Errors are grouped by directory and phase.
The report format is:
<filename> <phase> <return code where appropriate>
[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /pexc5i/oracle/product/111/appsutil/install/PEXC5I1_aohsexcc25
afdbprf.sh INSTE8_PRF 1
[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /pexc5i/oracle/product/111/appsutil/install/PEXC5I1_aohsexcc25
adcrobj.sh INSTE8_APPLY 1
AutoConfig is exiting with status 2
Solution
=========
=> Resolved the afdbprf.sh issue by editing the template for afdbprf.sql as follows :-
bash-3.2$ pwd
/pexc5i/oracle/product/111/appsutil/template
bash-3.2$ ls -l afdbprf.sql
-rw-r--r-- 1 orpexc5i dboracrs 2797 Mar 13 05:55 afdbprf.sql
Before
-------
32 connect &1/&2@&3
After
-----
32 REM connect &1/&2@&3
33 connect &1/&2
=> Resolved the adcrobj.sh issue by editing the template for adcrobj.sql as follows :-
bash-3.2$ pwd
/pexc5i/oracle/product/111/appsutil/template
bash-3.2$ cp adcrobj.sql adcrobj.sql_bkp
Before
-------
32 connect &1/&2@&3
After
-----
32 REM connect &1/&2@&3
33 connect &1/&2
=> AC on DB tier completed successfully
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SSUE:
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1.8840E+10 bytes
Fixed Size 2167576 bytes
Variable Size 7247760616 bytes
Database Buffers 1.1543E+10 bytes
Redo Buffers 47550464 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 17654
Session ID: 1200 Serial number: 5
alert log
==========
ORA-1092 : opitsk aborting process
Tue Mar 22 18:16:47 2011
Starting ORACLE instance (normal)
Oracle image stored on remote server adc36stor04-nas
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
From trace file
-------------------
*** 2011-0.22 18:14:45.344
Validate domain 0
Validated domain 0, flags = 0x0
*** 2011-0.22 18:14:51.551
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
*** 2011-0.22 18:14:51.557
USER (ospid: 20836): terminating the instance due to error 39701
+ Workaround.
SQL> sho parameter CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
System altered.
SQL> sho parameter CLUSTER_DATABASE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1.8840E+10 bytes
Fixed Size 2167576 bytes
Variable Size 7247760616 bytes
Database Buffers 1.1543E+10 bytes
Redo Buffers 47550464 bytes
Database mounted.
Database opened.
Note : After utlirp.sql is completed set CLUSTER_DATABASE back to TRUE.
ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
OLAP Catalog VALID 10.2.0.4.0 is missing after migration
Action plan
-----------
+ Observed that olap.sql was not ran as '@' was missing at the begning of the script olap.sql
Run /olap/admin/olap.sql SYSAUX TEMP
+ @?/olap/admin/olap.sql SYSAUX TEMP
+ Bounce database
+ run utlrp.sql
Thanks
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE OLAP catalog showa INVALID
I've tried solution from
Invalid Olap Catalog And Mising CWM$ Tables (Doc ID 1308148.1)
but that did not resolve the issue.
Then noticed that there are many OLAP objects duplicated - INVLIDS in SYS while valid in OLAPSYS.
Comparing to PROD:
SQL> select object_name, object_type, count(*) from dba_objects where owner in
('SYS','OLAPSYS') and object_type in ('VIEW','PACKAGE','PACKAGE BODY') group by object_name,
object_type having count(*) > 1;
no rows selected
The same query on TEST returned many rows.
So I've basically dropped all the objects from that list owned by SYS. Then Ran utlrp.
SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry;
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
OLAP Catalog VALID 10.2.0.4.0
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Doc ID 358015.1
Queries on dba_registry results with this error:
ORA-04063: View "SYS.DBA_REGISTRY" has errors..
Cause
DBA_REGISTRY is invalid and errors out when compiled
ORA-04063: View "SYS.DBA_REGISTRY" Has Errors. " (Doc ID 358015.1)
Modified29-JUL-2010TypePROBLEMStatusMODERATED(EXTERNAL)Priority3To Bottom
In this Document
Symptoms
Cause
Solution
Platforms: 1-914CU;
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.8 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2010***
Symptoms
Queries on dba_registry results with this error:
ORA-04063: View "SYS.DBA_REGISTRY" has errors..
.
Cause
DBA_REGISTRY is invalid and errors out when compiled
Compiling view DBA_REGISTRY results in this error:
OWNER NAME TYPE SEQUENCE LINE POSITION
--------------- ------------------------------ ------------ ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
SYS DBA_REGISTRY VIEW 1 0 0
ORA-00904: "DBMS_REGISTRY"."SCHEMA_LIST_STRING": invalid identifier
.
Solution
Recreate the DBMS_REGISTRY package and the DBA_REGISTRY view. As sysdba run :
$ORACLE_HOME/rdbms/admin/prvtcr.plb
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
System tablespace conversion is failing
QL> EXEC dbms_space_admin.tablespace_migrate_to_local('SYSTEM');
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, APPS_UNDOTS1, TEMP2 not found in read
only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
Analysis:
=========
The temporary tablespace assigned to users is tablespace group TEMP:
SQL> select * from DBA_TABLESPACE_GROUPS;
GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP TEMP1
TEMP TEMP2
TEMP TEMP3
TEMP TEMP4
SQL> select * from v$tempfile;
Currently all those temporary tablespaces are online and causes the package
dbms_space_admin.tablespace_migrate_to_local to fail
need to create one new default temporary tablespacefor the action and put offline the temp
tablespace components:
Action done:
============
SQL> create temporary tablespace temptemp tempfile '/tidbki/oradata/data02/temptemp.dbf' size
2000M;
Tablespace created.
SQL> drop tablespace temp4;
Tablespace dropped.
SQL> drop tablespace temp3;
Tablespace dropped.
SQL> drop tablespace temp2;
Tablespace dropped.
SQL> drop tablespace temp1;
Tablespace dropped.
------------------------------------------
Please restart the conversion script
------------------------------------------
When the script completes, pleate create back the temporary tablespaces as it was:
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue
=======
SQL> @conv_system.sql
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, APPS_UNDOTS1, TEMP not found in read
only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
Findings
=======
SQL> select TABLESPACE_NAME,STATUS from dba_Tablespaces where status not like 'READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
TEMP1 ONLINE
SYSAUX ONLINE
APPS_UNDOTS1 ONLINE
APPS_UNDOTS2 ONLINE
APPS_4K_UNDOTS1 ONLINE
Solution
=======
Before converting system to local
1) offline the sysaux tablespace
2) drop APPS_UNDOTS2 and APPS_4K_UNDOTS1
then convert system to local
bring sysaux to online
SME
SQL> @/ood_repository/patches/DSDCVI/DATABASE11G_AUTOMATION_11R20X_WRK/sql/conv_system.sql
BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, APPS_UNDOTS1, APPSTEMP not found in
read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1
SQL> select TABLESPACE_NAME,STATUS from dba_Tablespaces where status not like 'READ ONLY';
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
APPS_UNDOTS1 ONLINE
SYSAUX OFFLINE
TMP1 ONLINE
APPSTEMP ONLINE
SQL>
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where DEFAULT_TABLESPACE like '%TMP%';
no rows selected
dropped nondefault tablespace
SQL> drop tablespace TMP1 including contents and datafiles;
Tablespace dropped.
SQL> @/ood_repository/patches/DSDCVI/DATABASE11G_AUTOMATION_11R20X_WRK/sql/conv_system.sql
PL/SQL procedure successfully completed.
SQL>
+ Proceed with further steps
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue : aflobbld.sql failing
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in dreii0fsh
ORA-01653: unable to extend table APPLSYS.DR$FND_LOBS_CTX$I by 8192 in
tablespace APPS_TS_TX_DATA
ORA-06512: at line 277
TABLESPACE_NAME Allocated Used/Unused MB Allocated_Free MB %Allocated_Free MB
Space_AutoExtensible MB MaxChunk MB
-------------------- ------------------------ ----------------- ------------------
----------------------- -----------
APPS_TS_TX_DATA 395000 127906 32
0 512
APPS_TS_TX_DATA_OATM 508544 508173 100
671456 2359.875
Workaround
---------------
1. Take a snapshot of the original state of the fnd_lobs entries for comfort sake:
select
FILE_FORMAT,
count(*)
from
fnd_lobs
group by FILE_FORMAT;
2. Set the file_format column of all rows other than FND_HELP so that they won't get indexed:
update fnd_lobs
set FILE_FORMAT = 'IGNORE'
where NVL(PROGRAM_NAME,'NULL') != 'FND_HELP' ;
commit;
3. Most versions of aflobbld.sql, as written, will only rebuild the FND_LOBS_CTX index if the
status of the index is invalid or missing. To force the rebuilding of the index we may first have
to drop it.
Check the status of the fnd_lobs_ctx index. If it is valid, then drop it.
select status
from all_indexes
where owner='APPLSYS'
and index_name='FND_LOBS_CTX'
and table_name='FND_LOBS';
If this returns a status of "valid", then "drop index applsys.fnd_lobs_ctx
force"
4. Run aflobbld.sql. This takes two parameters; the name of the applsys schema and the name of the
apps schema.
Typically "@aflobbld.sql applsys apps".
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Database not starting
SQL> startup
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 11953
Session ID: 505 Serial number: 3
ARC0: Error 19504 Creating archive log file to '/sid/arch/sid_1_5272_771798806.arc'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance sid - Archival Error
ORA-16038: log 4 sequence# 5272 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 4 thread 1: '/sid/oradata/data03/log04a.dbf'
ORA-00312: online log 4 thread 1: '/sid/oradata/data03/log04b.dbf'
Wed Feb 29 19:05:45 2012
Solution
========
Changed the archive destination from /sid/arch to other location available location eg /sid/log/arch
and started database,
db started sucessfully
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue
sid/oracle> sqlplus "/ as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Tue May 22 05:08:15 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
On node 30:
/sid/oracle/product/112/rdbms/lib> strings config.o
dbpworti
--- this is post refresh issue.
Fixed as follows:
/sid/oracle/product/112/rdbms/lib> rm config.o
/sid/oracle/product/112/rdbms/lib> make -f ins_rdbms.mk ioracle
chmod 755 /sid/oracle/product/112/bin
- Linking Oracle
rm -f /sid/oracle/product/112/rdbms/lib/oracle
gcc -o /sid/oracle/product/112/rdbms/lib/oracle -m64 -L/sid/oracle/product/112/rdbms/lib/
-L/sid/oracle/product/112/lib/ -L/sid/oracle/product/112/lib/stubs/ -Wl,-E
/sid/oracle/product/112/rdbms/lib/opimai.o /sid/oracle/product/112/rdbms/lib/ssoraed.o
/sid/oracle/product/112/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv11
-lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged
-lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11
-lsnls11 -lnls11 -lcore11 -lnls11 -lsnls11 -lunls11 -lsnls11 -lnls11 -lcore11 -lsnls11 -lnls11
-lcore11 -lsnls11 -lnls11 -lxml11 -lcore11 -lunls11 -lsnls11 -lnls11 -lcore11 -lnls11 -lasmclnt11
-lcommon11 -lcore11 -laio `cat /sid/oracle/product/112/lib/sysliblist`
-Wl,-rpath,/sid/oracle/product/112/lib -lm `cat /sid/oracle/product/112/lib/sysliblist`
-ldl -lm -L/sid/oracle/product/112/lib
test ! -f /sid/oracle/product/112/bin/oracle ||\
mv -f /sid/oracle/product/112/bin/oracle /sid/oracle/product/112/bin/oracleO
mv /sid/oracle/product/112/rdbms/lib/oracle /sid/oracle/product/112/bin/oracle
chmod 6751 /sid/oracle/product/112/bin/oracle
/sid/oracle/product/112/rdbms/lib> sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Tue May 22 05:36:03 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
on MT
----
$ sqlplus sys/X0zTJ0qh as sysdba
SQL*Plus: Release 10.1.0.5.0 - Production on Thu May 24 21:55:44 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Solution :
Create password file correctly
$ORACLE_HOME/dbs/orapwd FILE=orapwDTWRI password=syspassword entries=10
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Oracle Real Application Clusters 11.2.0.3.0 INVALID
SQL> startup pfile='/tmp/pfile-rac'
ORA-00439: feature not enabled: Real Application Clusters
ORA-01078: failure in processing system parameters
Because => An Oracle Home disabled RAC.
solution
Before that modified '/tmp/pfile-rac' file use *.cluster_database=true to support RAC
So, we should relink library to enable RAC on this Oracle Home
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+ unable to start listner
bash-3.2$ lsnrctl start $ORACLE_SID
lsnrctl: symbol lookup error: lsnrctl: undefined symbol: nlgh
+ tried to relink
-bash-3.2$ ./relink all
writing relink log to: /sid/oracle/product/112/install/relink.log
./relink: line 181: 5562 Segmentation fault $RUNINSTALLER $ARGS > $LOGFILE 2>&1
Solution:
-bash-3.2$ cd $ORACLE_HOME/network/lib
-bash-3.2$ make -f ins_net_server.mk install
+ start listner lsnrctl start $ORACLE_SID
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
issue
Done step prepare_db_for_migration
Running step undo_reorg
ERROR : undo_reorg.pl FAILED
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_GEOR_ERR_TRIGGER
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-04045: errors during recompilation/revalidation of MDSYS.SDO_TOPO_DROP_FTBL
ORA-04067: not executed, package body "SYS.NAME_SECURITY" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: "SYS.NAME_SECURITY"
ORA-06512: at line 2
occurred when try to drop TABLESPACE APPS_UNDOTS1
issue fixed as follow
=============
1-alter trigger NAMECHECK_BEFORE_DDL_DB_TRG disable;
2-alter system set undo_tablespace=APPS_UNDOTS1 scope=both
.drop TABLESPACE BOB_UNDO INCLUDING CONTENTS AND DATAFILES;
4-rerun the automation again starting from step 8
after the upgrade completed please enable the trigger again
Thanks and Regards
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
+ issue unable to create spfile.
observation
===========
SQL> create spfile='/sid/oradata/data01/spfilesid.ora' from pfile='/sid/oracle/product/112/dbs/initsid.ora';
create spfile='/sid/oradata/data01/spfilesid.ora' from pfile='/sid/oracle/product/112/dbs/initsid.ora'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 12658
Session ID: 10 Serial number: 36
spfiles is getting deleted after creating..
tried to create pfile from theother non-prod
+ parameter file got corrupted
+ created new parameter file
+ now able to create spfile from pfile
SQL> startup pfile='/sid/oracle/product/112/dbs/initsid.ora';
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2220200 bytes
Variable Size 2080378712 bytes
Database Buffers 2147483648 bytes
Redo Buffers 45699072 bytes
Database mounted.
Database opened.
SQL> create spfile='/sid/oradata/data01/spfilesid.ora' from pfile='/sid/oracle/product/112/dbs/initsid.ora';
File created.
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE:
db upgrade
run migration failed
----
=============================
begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
ERROR:
ORA-03114: not connected to ORACLE
WORK AROUND
============
Ran below steps manually (ref (Doc ID 852917.1))
------------------------
SQL> begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
initjvmaux.rollbacksetup;
commit;
initjvmaux.rollbackset;
initjvmaux.exec('create or replace java system');
commit;
2 3 4 5 6 7 initjvmaux.rollbackcleanup;
initjvmaux.endstep;
end if; end;
/
8 9 10
PL/SQL procedure successfully completed.
SQL>
Try to restart the automation
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue :
--------
5
Running step recompile_invalids
ERROR : recompile_invalids.pl FAILED
Analysis :
----------
1) Is same issue occurred in non-prod ( Yes/No/NA) :NA
2) Reviewed Non-prod RFC ( Yes / No /NA ) :NA
3) Reviewed issue resolution portal ( Yes / No ) :yes
4) Observations :-
LOG : Command result : Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
LOG : Checking logfile /ood_repository/patches/TCPUSI/DATABASE11G_AUTOMATION_11R204_WRK/TCPUSI/CONSOLE_LOGS/../MIGRATION_LOGS/SQL_LOG/rdbms_utlrp.log for errors ....
LOG : Found ERROR : ORA-03113: end-of-file on communication channel
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
LOG : Found ERROR : ORA-03114: not connected to ORACLE
ERROR : Errors found while running utlrcmp.sql
ERROR : sql file that was run is /ood_repository/patches/TCPUSI/DATABASE11G_AUTOMATION_11R204_WRK/TCPUSI/CONSOLE_LOGS/../MIGRATION_LOGS/SQL_LOG/rdbms_utlrp.sql
ERROR : log file generated is /ood_repository/patches/TCPUSI/DATABASE11G_AUTOMATION_11R204_WRK/TCPUSI/CONSOLE_LOGS/../MIGRATION_LOGS/SQL_LOG/rdbms_utlrp.log
ERROR : Please check log file to see if the errors are ignorable
ERROR : If the errors can be ignored, you can restart the run
ERROR : If running in standalone mode, restart will require
ERROR : editing the script run_upgrade.sh
LOG : CURRENT STEP FAILED
Findings
-----------
SQL> select ACTIVE_STATE,DATABASE_STATUS,STATUS,STARTUP_TIME from v$instance;
ACTIVE_ST DATABASE_STATUS STATUS STARTUP_T
--------- ----------------- ------------ ---------
NORMAL ACTIVE OPEN 10-FEB-15
SQL> sho parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
Solution
---------
SQL> alter system set compatible='11.2.0.4.0' scope=spfile sid='*';
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 977076224 bytes
Fixed Size 2259200 bytes
Variable Size 348129024 bytes
Database Buffers 612368384 bytes
Redo Buffers 14319616 bytes
Database mounted.
Database opened.
SQL>
SQL> sho parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0
SQL>
+ Restart the automation form step " 5. START FROM recompile_invalids"
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SSUE:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
+ serveroutput size in script is set to 1000000
Made below changes in the script
bash-3.2$ diff ProcessMonitorSubtypeFix_v1.1.sql ProcessMonitorSubtypeFix_v1.1.sql_bkp12dec13
1c1
< set serveroutput on size unlimited;
---
> set serveroutput on size 1000000
bash-3.2$
+ request to rerun the script now
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE : unable to conect sqlplus
bash-3.2$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 23 19:54:00 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Observation:
bash-3.2$ cd /sid/oracle/product/112/rdbms/lib
bash-3.2$ ls -lrt config.o
-rw-r--r-- 1 orsid dbsid 1328 Sep 14 23:01 config.o
bash-3.2$ strings config.o
dbpavcri <<<<<<<<<<<<
Solution:
==========
bash-3.2$ cp config.o config.o_abkp
bash-3.2$ rm config.o
bash-3.2$ make -f ins_rdbms.mk ioracle
+ Able to connect now
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
observed that apps and applsys are not connecting with same password
SQL> conn apps/****
Connected.
SQL> conn applsys/****
ERROR:
ORA-04045: errors during recompilation/revalidation of
XXPEPSICO_IBERICA.XXPEPIB_ON_LOGON_TRIGGER
ORA-01031: insufficient privileges
CAUSE:TRIGGER" XXPEPSICO_IBERICA.XXPEPIB_ON_LOGON_TRIGGER" IS INVALID AND CAUSING TO FAIL WHILE CONNECTING TO APPLSYS
SOLUTION:
---------
OWNER OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------------------ ------------------- -------
XXPEPSICO_IBERICA XXPEPIB_ON_LOGON_TRIGGER TRIGGER INVALID
SQL> ALTER TRIGGER XXPEPSICO_IBERICA.XXPEPIB_ON_LOGON_TRIGGER COMPILE;
Trigger altered.
SQL> conn applsys/***
Connected.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE:SQL> startup restrict;
ORA-01078: failure in processing system parameters
ORA-00824: cannot set SGA_TARGET or MEMORY_TARGET due to existing internal settings
ORA-00848: STATISTICS_LEVEL cannot be set to BASIC with SGA_TARGET or MEMORY_TARGE
SOLUTION:
==========
SQL> alter system set memory_target=0 scope= spfile;
System altered.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 4526469120 bytes
Fixed Size 2220720 bytes
Variable Size 2197818704 bytes
Database Buffers 2315255808 bytes
Redo Buffers 11173888 bytes
Database mounted.
Database opened.
SQL>
Shutdown and started normally
to execution analyst
+ After the action plan
+ revert memory_target to 6G
alter system set memory_target=6G scope= spfile;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/sid/oradata/data02/system01.dbf'
resolved after nfs locks clearing
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE
-bash-3.2$ ls -lrt /sid/oradata/data01/intfd02.dbf
ls: /sid/oradata/data01/intfd02.dbf: No such file or directory
-bash-3.2$ cd /sid/oradata/data01/
-bash-3.2$ ls -lrt intfd02.dbf
-rw-r----- 1 orsid dbsid 104865792 Sep 15 01:34 intfd02.dbf
-bash-3.2$
SQL> select FILE_NAME,FILE_ID from dba_data_files where
FILE_NAME='/sid/oradata/data01/intfd02.dbf';
no rows selected
SQL> select FILE_NAME,FILE_ID from dba_data_files where FILE_NAME like '%intfd%';
FILE_NAME FILE_ID
------------------------------------------------------------ ----------
/sid/oradata/data01/intfd01.dbf 12
/sid/oradata/data01/intfd02.dbf 118
SQL> select FILE_NAME,FILE_ID from dba_data_files where
FILE_NAME='/sid/oradata/data01/intfd02.dbf ';
FILE_NAME FILE_ID
------------------------------------------------------------ ----------
/sid/oradata/data01/intfd02.dbf 118
+ Findings
Datafile was created with space at the end of the file
Action Plan
-----------
sqlplus: shutdown;
linux# mv '/sid/oradata/data01/intfd02.dbf ' '/sid/oradata/data01/intfd02.dbf'
startup mount;
ALTER DATABASE RENAME FILE '/sid/oradata/data01/intfd02.dbf ' TO
'/sid/oradata/data01/intfd02.dbf'
ALTER DATABASE OPEN;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Issue
===
BEGIN
move_qt_pkg.move_queue_table('ASO','ASO_ORDER_FEEDBACK_T','APPS_TS_QUEUES','DUMMY');
END;
/ 2 3 4
BEGIN
*
ERROR at line 1:
ORA-32426: materialized view log on "ASO"."AQ$_ASO_ORDER_FEEDBACK_T_S" in DST
upgrade
ORA-06512: at "SYS.MOVE_QT_PKG", line 441
ORA-06512: at "SYS.MOVE_QT_PKG", line 108
ORA-06512: at line 2
SQL> set lines 400
SQL> col PROPERTY_NAME for a30
SQL> col VALUE for a20
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE
Solution:
=========
* if DST_UPGRADE_STATE is "UPGRADE" then an upgrade is already in progress, check if an
other DBA is doing a upgrade, if not then issue:
CONN / as sysdba
alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
set serveroutput on
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
ISSUE: unable to start database
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_sid'
FINDINGS/OBSERVATION:
----------------------
SQL> create pfile='/sid/oracle/product/112/dbs/sidfromspfile.ora' from
spfile='/sid/oracle/product/112/dbs/spfilesid.ora';
File created.
$ grep -i local /sid/oracle/product/112/dbs/sidfromspfile.ora
*.local_listener='LISTENER_sid'
$
Removed *.local_listener='LISTENER_sid' from pfile and recreated spfile
SQL> create spfile='/sid/oracle/product/112/dbs/spfilesid.ora' from
pfile='/sid/oracle/product/112/dbs/sidfromspfile.ora';
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 8351150080 bytes
Fixed Size 2244992 bytes
Variable Size 6006247040 bytes
Database Buffers 2315255808 bytes
Redo Buffers 27402240 bytes
Database mounted.
Database opened.
SQL>
Thanks,
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
No comments:
Post a Comment