Friday 11 February 2022

OEM 13c - emctl start oms failing with error - java.io.IOException: Cannot run program "null/common/bin/wlst.sh": error=2, No such file or directory

After the abnormal server reboot, we are unable to startup the OEM services and encounter the below error.

Error:

[oracle@krishna bin]$ ./emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
[oracle@krishna bin]$ ./emctl status oms
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
WebTier is Down
[oracle@krishna bin]$ ./emctl stop oms -all -force
Oracle Enterprise Manager Cloud Control 13c Release 5
Copyright (c) 1996, 2021 Oracle Corporation. All rights reserved.
Stopping Oracle Management Server...
Error Occurred: Cannot run program "null/common/bin/wlst.sh": error=2, No such file or directory
Please check /u01/oracle/product/13.5.0/em/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details
[oracle@krishna bin]$

Solution:

OPTION1:
Restore the file emgc.properties file from valid backup.

OPTION2:
1. A typical emgc.properties in EM13c would have sample entries such as below:
  
COMMON_ORACLE_HOME=<OMS_HOME>/oracle_common
oracle.sysman.emSDK.svlt.ConsoleServerName=<OMS_HOST>\:<PORT>_Management_Service
JBO_POOL_TTL=-1
AS_PORT=0
EM_UPLOAD_HTTP_PORT=<EM_UPLOAD_HTTP_PORT>
EM_NODEMGR_HOME=<GC_INST>/user_projects/domains/GCDomain/nodemanager
MSPORT=<MSPORT>
ADMIN_SERVER_NAME=EMGC_ADMINSERVER
AS_HTTPS_PORT=7102
EM_REPOS_CONNECTDESCRIPTOR=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=<REPO_DB_HOSTNAME>)(PORT\=<REPO_DB_LSNR_PORT>)))(CONNECT_DATA\=(SID\=<REPO_DB_SID>)))
OHS_ADMIN_PORT=<OHS_ADMIN_PORT>
OMS_HEAP_MAX=1740M
EM_CONSOLE_HTTP_PORT=<EM_CONSOLE_HTTP_PORT>
WLS_HOME=<OMS_HOME>/wlserver
JBO_LAZY_LOAD=true
EM_UPLOAD_HTTPS_PORT=<EM_UPLOAD_HTTPS_PORT>
oracle.sysman.emRep.repositoryMode=repository
AS_HOST=<OMS_HOSTNAME>
OMS_PERMGEN_MAX=768M
EM_DOMAIN_HOME=<GC_INST>/user_projects/domains/GCDomain
EM_CONSOLE_HTTPS_PORT=<EM_CONSOLE_HTTPS_PORT>
CURRENT_OMS_MODE=default
EM_NODEMGR_PORT=<EM_NODEMGR_PORT>
EM_DOMAIN_NAME=GCDomain
MS_HTTPS_PORT=<MS_HTTPS_PORT>
EM_INSTANCE_HOST=<OMS_HOSTNAME>
AS_COHERENCE_PORT=<AS_COHERENCE_PORT>
WEBTIER_ORACLE_HOME=<OMS_HOME>/ohs
MW_HOME=<OMS_HOME>
JBO_MIN_POOL_SIZE=1
EM_INSTANCE_HOME=<GC_INST>/em/EMGC_OMSn
JBO_MAX_POOL_SIZE=50
IS_ADMIN_HOST=true
AS_USERNAME=weblogic
OMS_HEAP_MIN=256M
ORACLE_HOME=<OMS_HOME>
NM_USER=nodemanager
OPSS_USER=SYSMAN_OPSS
OMSNAME=EMGC_OMSn
JBO_MAX_CURSORS=5
EM_REPOS_USER=SYSMAN
JBO_RECYC_THRESHOLD=50
OMS_PERMGEN_MIN=128M
OHS_COMP_NAME=ohsn
 
Note: The port numbers and other settings are OMS specific and the above-depicted entries are just a sample.
Recreate the file using the entries from one of the EMGC_OMSn.out* file under <gc_inst>/user_projects/domains/GCDomain/servers/EMGC_OMSn/logs on the respective OMS node.

2. Start the OMS

Reference - EM 12c: Emctl start/stop/status oms Failed With Error Exception in thread "main" java.lang.NoClassDefFoundError: org.apache.log4j.Logger (Doc ID 2065293.1)

Wednesday 16 June 2021

Auto Extend Tablespace scripts

1 - A script for summation of datafiles in tablespaces, ie a tablespace space usage script.

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

2- A script that can give accurate tablespace usage statistics that include auto extend. It determines the space usage WITH auto-extend has taken into account, not just current free space.

select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_data_files a,V$DATAFILE b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID
union
select substr(tablespace_name,1,15) "tbs",substr(file_name,1,65) "auto extend data file",(MAXBYTES/1024)/1024 "MAXMEGA",
case when b.block_size=2048 then increment_by*2/1024
when b.block_size=4096 then increment_by*4/1024
when b.block_size=8192 then increment_by*8/1024
when b.block_size=16384 then increment_by*16/1024
when b.block_size=32768 then increment_by*32/1024 end "incr. /MB"
,(b.CREATE_BYTES/1024)/1024 "CREATED MEGA",(a.BYTES/1024)/1024 "MB REACHED",b.block_size
from dba_temp_files a,V$tempfile b
where AUTOEXTENSIBLE='YES'AND b.FILE#=a.FILE_ID;

Reference: Auto Extend Tablespace scripts (Doc ID 1908578.1)

APP-FND-01564: ORACLE error 1455 in afpodbinit

 The issue started after cloning the Oracle Database from an earlier release of 12.1.0.2 to 12.1.0.2 [ODA to ODA]

This issue was caused by the newly enabled Unified Auditing feature in the target database server.

Error:

Cause: afpodbinit failed due to ORA-01455: converting column overflows integer datatype.

The SQL statement being executed at the time of the error was: begin fnd_global.bless_next_init('FND_PERMIT_0000'); fnd_global.initialize(:session_id, :user_id, :resp_id, :resp_appl_id, :security_group_id, :site_id, :login_id, :conc_login_id, :prog_appl_id, :conc_program_id, :conc_request_id, :conc_priority_request, :form_id, :form_application_id, :conc_process_id, :conc_queue_id, :queue_appl_id, :server_id); fnd_profile.put('ORG_ID', :org_id); fnd_profile.put('MFG_ORGANIZATION_ID', :mfg_org_id); fnd_profile.put('MFG_CHART_OF_ACCOUNTS_ID', :coa); fnd_profile.put('APPS_MAINTENANCE_MODE', :amm); end; and was executed from the file &ERRFILE.

Fix:

With Oracle Database 12c, Unified Auditing is introduced. Prior to Oracle Database 12c, auditing is configured using the AUDIT_TRAIL database parameter, 

which is called traditional auditing. Unified Auditing is not certified with E-Business Suite. Bug 25540183 is logged for this Enhancement Request.

To disable the new Unified Auditing functionality:

1. Take the Apps and DB tier offline

2. Source RDBMS environment

3. cd $RDBMS_ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

4. Verify the output of the following query is FALSE

SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

5. Take the DB and Apps tier online

Reference: APP-FND-01564 AND ORA-1455 Errors In Afpodbinit When A EBS Form Is Called Or Responsibility Selected (Doc ID 1963969.1)

Sunday 4 April 2021

RMAN Duplicate Fails ORA-38862: Flashback Database Logs Are In Use

 RMAN backup restore is failing with the following error when the flashback is turned on in the source database.

Error:
Errors in memory script
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01507: database not mounted
ORA-06512: at "SYS.X$DBMS_RCVMAN", line 14894
ORA-06512: at line 1
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 04/04/2021 23:37:08
RMAN-20000: abnormal termination of job step
RMAN-11003: failure during parse/execution of SQL statement: alter database flashback off
RMAN-11001: Oracle Error:
ORA-38862: Flashback database logs are in use.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/04/2021 23:37:21
RMAN-05501: aborting duplication of target database
Recovery Manager complete.

Fix:

Update the below values on the target database and retry the clone.

SQL> alter system set db_flashback_retention_target=0 scope=both;
SQL> alter system set db_recovery_file_dest='' scope=both;
Reference  -  (Doc ID 2418803.1)

Monday 1 July 2019

How to Synchronize the AD tables FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 when adop fails.

Adop failing with the Error adop exiting with status = 255 (Fail), due to fnd_nodes table has NULL for domain entry of this valid server.

Error:

adop failing with below error.

FUNCTION: ADOP::GlobalVars::_GetMandatoryArgs [ Level 1 ]
ERRORMSG: adop is not able to detect any valid application tier nodes in ADOP_VALID_NODES table.

Ensure autoconfig is run on all nodes.

[STATEMENT] Please run adopscanlog utility, using the command
"adopscanlog -latest=yes"
to get the list of the log files along with snippet of the error message corresponding to each log file.
adop exiting with status = 255 (Fail)

In this case, The fnd_nodes table has NULL for domain entry of this valid server.

Fix:

Test the following steps in a cloned instance first, and then migrate accordingly once the desired result is confirmed:

NOTE: 1. Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is EXPECTED / REQUIRED that the Applications have been shutdown.

                  The only thing running should be the Database Tier.

           2. A full backup should be taken before any testing begins.


1. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables, and if on AD/TXK 8 or higher; ad_nodes_config_status in the EBS env nodes:

      Linux#> sqlplus applsys/<pwd>

      SQLPlus#> create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;

      SQLPlus#> create table fnd_nodes_bk as select * from fnd_nodes;

        SQLPlus#> create table adop_valid_nodes_bk as select * from adop_valid_nodes;

      If on AD/TXK 8 or higher:

      SQLPlus#> create table ad_nodes_config_status_bk as select * from ad_nodes_config_status;


 2. Truncate the following tables (Continue from step 1):

      SQLPlus#> truncate table fnd_oam_context_files;

      SQLPlus#> truncate table fnd_nodes;

      SQLPlus#> truncate table adop_valid_nodes;

      If on AD/TXK 8 or higher:

      SQLPlus#> truncate table ad_nodes_config_status;


3.  Run AutoConfig on the DB tier:

     Source the <RDBMS_ORACLE_HOME> home.

     Linux#> cd <RDBMS_ORACLE_HOME>/appsutil/scripts/<SID>_<HOSTNAME>/

     Linux#> ./adautocfg.sh

  ... Confirm Autoconfig completes successfully.
  ... If RAC, Repeat step 3 on all RAC nodes.

4.  Run Autoconfig on the run file system.

    Linux#> source <EBS_BASE>/EBSapps.env run

    Linux#> cd $ADMIN_SCRIPTS_HOME

    Linux#> ./adautocfg.sh

      ... Confirm Autoconfig completes successfully.

      ... If more than one EBS node, repeat step 4 on all EBS nodes.


5.  Run Autoconfig on the patch file system

Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled
After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.

    a. Disable the ebs_login trigger using the following SQL.

    Linux#> sqlplus system/<pwd>

    SQLPlus#> alter trigger ebs_logon disable;


    b. At this time Run autoconfig with the patch env sourced .       

    Linux#> source <EBS_BASE>/EBSapps.env patch

    Linux#> cd $ADMIN_SCRIPTS_HOME

    Linux#> ./adautocfg.sh

     ... Confirm Autoconfig completes successfully

     ... If more than one EBS node, repeat step 5.b on all EBS nodes.


    c. Enable the ebs_login trigger using the following SQL.

    Linux#> sqlplus system/<pwd>

    SQLPlus#> alter trigger ebs_logon enable;

 6.  After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:

SQL>    set pagesize 5
        set linesize 132
        col node_name format a15
        col server_id format a8
        col server_address format a15
        col platform_code format a4
        col webhost format a12
        col domain format a20
        col virtual_ip format a12

        select node_id, platform_code, support_db D, support_cp C, support_admin A, support_forms F, support_web W, node_name, server_id,
               server_address, domain, webhost, virtual_ip, status
          from fnd_nodes order by node_id;

SQL>    set pagesize 5
        set linesize 132
        col NAME format A20
        col VERSION format A12
        col PATH format A110
        col STATUS format A10

        select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;

How to create the patch file system in oracle apps R12.2

Below is an action plan to recreate the patch file system. You will need to make sure to have a valid backup of your system before you execute it as this plan will imply to remove information from your system and we might need to go back to the point the action plan is executed in case anything may not go as expected.

1) Adpreclone:

On the RUN File-System, source RUN File-System:
$ source EBSapps.env run

# RUN Admin server MUST be up for this step
# check status:

$ $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh status

# start if needed:

$ $ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start

# execute adpreclone:

$ cd $INST_TOP/admin/scripts
$ perl adpreclone.pl appsTier


2) Detach PATCH homes:

# This step is performed from the RUN file-system detaching oracle_homes on the PATCH file-system:
$ cd $FMW_HOME/oracle_common/oui/bin

$ ./runInstaller -detachhome ORACLE_HOME={oracle home} -silent

 # for each PATCH oracle_home

./runInstaller -detachhome ORACLE_HOME=/u01/app/fs1/FMW_Home/webtier -silent

./runInstaller -detachhome ORACLE_HOME=/u01/app/fs1/FMW_Home/Oracle_EBS-app1 -silent

./runInstaller -detachhome ORACLE_HOME=/u01/app/fs1/FMW_Home/oracle_common -silent


# The 10.1.2 Oracle Home needs a different syntax

$ ./runInstaller -removeHome ORACLE_HOME=<s_tools_oh> -silent

$ ./runInstaller -removeHome ORACLE_HOME=/u01/app/fs1/EBSapps/10.1.2 -silent


3) Copy RUN to PATCH:

# it may be helpful for RUN environment to be DOWN for this step
 # copy each of these locations from RUN to PATCH
 <APPL_TOP>                              [ /u03/oracle/VIS/fs1/EBSapps/appl ]
 <COMMON_TOP>                            [ /u03/oracle/VIS/fs1/EBSapps/comn ]
 <OracleAS Tools 10.1.2 ORACLE_HOME>     [ /u03/oracle/VIS/fs1/EBSapps/10.1.2 ]

4) Remove PATCH FMW_HOME directory:

[ /u03/oracle/VIS/fs1/FMW_Home ]

5) Disable ebs_login:

Linux#> sqlplus system/<pwd>
SQLPlus#> alter trigger ebs_logon disable;


6) adcfgclone:

$ cd <PATCH COMMON_TOP>/clone/bin$ perl adcfgclone.pl appsTier

For the prompts in this step:

Do you want to add a node (yes/no) [no] : no

Enter the full path of Run File System Context file : <absolute path to the Run File System Context file>
RC-00217: Warning: Configuration home directory (s_config_home) evaluates to <PATCH s_config_home>. A directory with this name already exists and is not empty.
Do you want to continue (y/n)   : y

This is expected as part of the rebuild.

Target System Port Pool [0-99] : <desired PATCH File System Port Pool>

This will be the PATCH File System port pool. Would suggest that the RUN and PATCH port pools be different by approx twice number of managed servers on this node.

7) Verify work:

In the new terminal - source PATCH file-system
$ EBSapps.env patch
$ echo $FILE_EDITION

In new terminal - source RUN file-system

# Run Admin server MUST be up for this step

$ EBSapps.env run
$ adop phase=fs_clone force=yes

8) Enable ebs_login:

Linux#> sqlplus system/<pwd>
SQLPlus#> alter trigger ebs_logon enable;

Tuesday 29 January 2019

OEM 12c: ORA-28001: the password has expired The Connect Descriptor was (DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=)))(CONNECT_DATA=(SID=)(SERVER=DEDICATED)))

This error occurred on DB home page in OEM 12c.
ORA-28001: the password has expired The Connect Descriptor was (DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=<host>)(PORT=<port>)))(CONNECT_DATA=(SID=<sid>)(SERVER=DEDICATED)))

Cause:

The cause of this issue is monitoring user "DBSNMP" password expired.

select username, account_status from dba_users where username='DBSNMP';

Solution:

To resolve this issue we need to reset the password and unlock the user account using the below steps.

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' from dba_users d, sys.user$ u where d.username = upper('DBSNMP') andu.user# = d.user_id;

'ALTERUSER"'||D.USERNAME||'"IDENTIFIEDBYVALUES'''||U.PASSWORD||''';'
--------------------------------------------------------------------------------
alter user "DBSNMP" identified by values '31B5G2AQ92890956';

SQL> alter user "DBSNMP" identified by values '31B5G2AQ92890956';

Note: If you want to keep the old password then you no need to do any changes in OEM but if you want to update the new password then follow the below steps in OEM.
1.      Click Targets->Databases.
2.      Select radio button for Database Instance Name and then Click Configure
3.      Change the Monitor Password, Click Next and Submit


Wednesday 5 September 2018

Disconnected: No supported authentication methods available (server sent: publickey, gssapi-with-mic)

When we trying to connect to an Oracle Cloud Linux/UNIX-Like Instance through Open SSH, the SSH client returns one of the following errors:

Issue:

"Disconnected: No supported authentication methods available (server sent: publickey)"
"Disconnected: No supported authentication methods available (server sent: publickey, gssapi-with-mic)"

Fix:

There are multiple issues that could cause such an error, please read below what the troubleshoot steps are for the most common causes.

1. Check that the IP address against which you try to connect is the address of the correct instance. This can be performed either through the Compute Console or the REST API:
Listing Instances in the Compute Console
Retrieving Details of an Instance using the REST API
Note that, unless reserved, the IP addresses of instances could change after reboots: Reserving a Public IP Address

2. Check that a SSH private key is configured in the SSH client you use or it is specified to the SSH/SCP/SFTP command.

3. Check that the SSH client/SSH agent (e.g. WinSCP, PuTTY, Pageant, etc) you use points to the correct directory containing the private SSH keys.

4. Check that the .pem private SSH key file has been converted successfuly to the .ppk format.

5. If you installed a new version of openssh-client or openssh-server, try to erase/purge the already installed version before installing the new one, for example:

sudo yum remove openssh-server
sudo yum -y install openssh-server openssh-clients
or, on some other Linux distros:

sudo apt-get purge openssh-server
sudo apt-get install openssh-server openssh-client
6. If you have another active SSH session/terminal open, check that the SSH daemon is running, by calling "sudo service sshd status" or, on some other distros, "sudo service ssh status". If it's stopped, try to start it.

7. If no other terminal is opened, generally an instance restart should restart the SSH daemon as well. You can also check in the web console whether or not the SSH daemon started during the boot: Viewing the Boot Log of an Instance

Thursday 19 July 2018

Unable to create an Oracle ASM database lower than 12.1.0.2

Unable to create an Oracle ASM database lower than 12.1.0.2.17814 PSU (12.1.2.12).

Hardware Models:

Oracle Database Appliance X6-2-HA, X5-2, X4-2, X3-2, and V1.

Workaround:

There is not a workaround. If you have Oracle Database 11.2 or 12.1 that is using Oracle Automatic Storage Management (Oracle ASM) and you want to upgrade to a higher release of Oracle Database, then you must be on at least Oracle Database Appliance 12.1.2.12.0 and Database Home 12.1.0.2.170814.

The upgrade path for Oracle Database 11.2 or 12.1 Oracle ASM is as follows:


  • If you are on Oracle Database Appliance version 12.1.2.6.0 or later, then upgrade to 12.1.2.12 or higher before upgrading your database.
  • If you are on Oracle Database Appliance version 12.1.2.5 or earlier, then upgrade to 12.1.2.6.0, and then upgrade again to 12.1.2.12 or higher before upgrading your database.


This issue is tracked with Oracle bug 21626377, 27682997, and 21780146. The issues are fixed in Oracle Database 12.1.0.2.170814.

Error when upgrading virtualized platforms to 12.2.1.4.0 in ODA

When upgrading Oracle Database Appliance hardware models with virtualized platforms to 12.2.1.4.0, perform these manual steps before upgrading to release 12.1.2.12.

If you upgrade Oracle Database Appliance hardware models with a virtualized platform, to 12.1.2.12, then there is an error when using the driver domain functionality. To upgrade to Oracle Database Appliance release 12.2.1.4.0, follow the steps documented in the workaround section.

Hardware Models:

Oracle Database Appliance X7-2-HA, X6-2-HA, X5-2, X4-2, X3-2, and V1 with a virtualized platform that use driver domain virtual machines

Workaround:

Follow these steps to upgrade to Oracle Database Appliance release 12.2.1.4.0:


  • Deploy Oracle Database Appliance release 12.1.2.11 and create multiple virtual machines with driver domain enabled.
  • Shut down all running virtual machines and repository.
  • Apply the release 12.1.2.12 patch bundle.
  • Apply the release 12.2.1.4.0 patch bundle.
  • Start the repository. If you encounter the error OAKERR:5015 Start repo operation has been disabled by flag, then run the following command: 
    • "oakcli enable startrepo -node 0/1"
  • Start the virtual machines and confirm that they have started.

Tuesday 3 July 2018

ODA: 'oakcli update -patch --database' Does Not Run Datapatch

Upon running /opt/oracle/oak/bin/oakcli update -patch 12.1.2.6.0 -database, some of the databases do not receive the update. The database homes are patched correctly and there are no errors in the update logs.

Symptoms:
select * from dba_registry_sqlpatch;

This either shows no rows selected or some previous entry.

Cause:
There are multiple bugs around this where ODA is still trying to run catbundle instead of datapatch, and will be fixed in future release.

Fix:
Workaround
Please set your oracle home and SID for each database one at a time, cd to
$ORACLE_HOME/OPatch directory and run
./datapatch

After it is run get the following from each DB
select * from dba_registry_sqlpatch;

This should show the current output with "Success"

Wednesday 4 April 2018

ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or DB_RECOVERY_FILE_DEST

Below error occurred while changing the destination of archive log files in oracle database.

Error:

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both;
ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Fix:

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     437
Next log sequence to archive   439
Current log sequence           439
SQL> alter system set DB_RECOVERY_FILE_DEST='';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/archive' scope=both;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive
Oldest online log sequence     437
Next log sequence to archive   439
Current log sequence           439

Monday 19 February 2018

Errors while applying 12.2.7 RUP Patch (24690690) in Oracle E-Business Suite 12.2

System got hanged while applying 12.2.7 RUP Patch (24690690) and adop exits with the errors.

Error:

1) All workers are failed with the Java error..
2) Invalid objects count is increased to 56k.

Troubleshooting:

1) Cleared the invalid objects by using utlrp.sql
2) Run the auto config on DB node
3) Start the patching activity again.

In step (3) while running auto config in database node exists with the below error.

ORA-01400: cannot insert NULL into ("APPLSYS"."FND_NODES"."NODE_NAME")

Adop error message: 

ERRORMSG: Cannot identify any valid application tier nodes in ADOP_VALID_NODES table. Ensure AutoConfig has been run on all nodes.

To resolve this error need to follow the below steps:

Step 1:
drop synonym apps.fnd_nodes;
create synonym apps.fnd_nodes for applsys.fnd_nodes;
drop synonym apps.fnd_oam_context_files;
create synonym apps.fnd_oam_context_files for applsys.fnd_oam_context_files;

Step 2:
Change the worker status to restart using adcrtl utility.

Step 3:
Run auto config again in database node. (Auto config completed without any errors)

Step 4:
Restart the patching activity.

adop phase=apply apply_mode=downtime patches=24690690 abandon=no restart=yes workers=16


Thursday 15 February 2018

ORA-04063: package body "SYS.DBMS_STATS" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"

After applying the patch (26925311: DATABASE PATCH SET UPDATE 12.1.0.2.180116)  in Oracle Database 12.1.0.2. We have faced below errors while executing the post patch installation steps.

Error:

We try to compile the invalid objects using utlrp.sql after applying the patch got the below error.

Warning: XDB now invalid, could not find xdbconfig
ORDIM INVALID OBJECTS: ORDIMDPCALLOUTS - INVALID - PACKAGE BODY

ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at "SYS.UTL_RECOMP", line 875
ORA-06512: at line 4

SQL> select comp_name, version, status from dba_registry;

--------------------------------------------------------------------------------------------------
COMP_NAME VERSION                                                           STATUS
------------------------------ -------------------------------------------------------------------
Oracle Machine Generated Data  12.1.0.2.0                                VALID
Oracle Text  12.1.0.2.0                                                                 VALID
OLAP Catalog 11.2.0.3.0                                                             OPTION OFF
Spatial 12.1.0.2.0                                                                          INVALID
Oracle Multimedia 12.1.0.2.0                                                       INVALID
Oracle XML Database 12.1.0.2.0                                                 INVALID
Oracle Database Catalog Views 12.1.0.2.0                                   VALID
Oracle Database Packages and Types 12.1.0.2.0                          INVALID
Oracle Real Application Clusters 12.1.0.2.0                                OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0                                    VALID
Oracle XDK 12.1.0.2.0                                                                 VALID
Oracle Database Java Packages 12.1.0.2.0                                   VALID
OLAP Analytic Workspace 12.1.0.2.0                                         VALID
Oracle OLAP API 12.1.0.2.0                                                       INVALID


Fix:

SQL> shutdown immediate;
SQL> startup restrict
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

Shutdown and restart the database in normal mode.

SQL> select comp_name, version, status from dba_registry;

--------------------------------------------------------------------------------------------------
COMP_NAME VERSION                                                           STATUS
------------------------------ -------------------------------------------------------------------
Oracle Machine Generated Data  12.1.0.2.0                                VALID
Oracle Text  12.1.0.2.0                                                                 VALID
OLAP Catalog 11.2.0.3.0                                                             OPTION OFF
Spatial 12.1.0.2.0                                                                         VALID
Oracle Multimedia 12.1.0.2.0                                                     VALID
Oracle XML Database 12.1.0.2.0                                               VALID
Oracle Database Catalog Views 12.1.0.2.0                                   VALID
Oracle Database Packages and Types 12.1.0.2.0                      VALID
Oracle Real Application Clusters 12.1.0.2.0                                OPTION OFF
JServer JAVA Virtual Machine 12.1.0.2.0                                    VALID
Oracle XDK 12.1.0.2.0                                                                 VALID
Oracle Database Java Packages 12.1.0.2.0                                   VALID
OLAP Analytic Workspace 12.1.0.2.0                                         VALID
Oracle OLAP API 12.1.0.2.0                                                      VALID

ORA-29548: Java system class reported: release of Java system classes in the database (12.1.0.2.0 1.6) does not match that of the oracle executable (12.1.0.2.180116 1.6)

We have faced the below error while applying the below patch in Oracle E-Business Suite 12.2.7 (Upgrade).

Patch: adop phase=apply patches=25828573 hotpatch=yes

Issue: 
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
ORA-29548: Java system class reported: release of Java system classes in the
database (12.1.0.2.0 1.6) does not match that of the oracle executable

(12.1.0.2.180116 1.6)

Fix:
SQL> sho user
USER is "SYS"
SQL> @?/javavm/install/update_javavm_db.sql
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL>
SQL> -- If Java is installed, do CJS.
SQL>
SQL> -- If CJS can deal with the SROs inconsistent with the new JDK,
SQL> -- the drop_sros() call here can be removed.
SQL> call initjvmaux.drop_sros();

Call completed.

SQL>
SQL> create or replace java system;
  2  /

Java created.

SQL>
SQL> update dependency$
  2    set p_timestamp=(select stime from obj$ where obj#=p_obj#)
  3    where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
  4          (select type# from obj$ where obj#=p_obj#)=29  and
  5          (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
--------------------------------------------------------------------------------
1.6.0_181

1 row selected.


SQL> select dbms_java.longname('TEST') from dual;

DBMS_JAVA.LONGNAME('TEST')
--------------------------------------------------------------------------------
TEST

1 row selected.