Showing posts from 2023

Opatch is getting faild for GI patch with error code 2 while doing prereq

OPatch failed with error code 2  When checked the patch conflict for a patch, got below error. ]$opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version Copyright (c) 2020, Oracle Corporation.  All rights reserved. PREREQ session Oracle Home       : /u01/oracle/product/ Central Inventory : /u01/oracle/oraInventory    from           : /u01/oracle/product/ OPatch version    : OUI version       : Log file location : /oragrid/orahome/product/19.3.0/cfgtoollogs/opatchauto/core/opatch/opatch2023-12-22_09-37-15AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" is not executed. The details are: Unable to create Patch Object. Exception occured : /orastage/OCT23_GRID_CPU/35642822/33575402/etc/config/actions.xml with Version field of the component "delete" in actions file cannot be <null> or empty. Please check p

How to check UTL_FILE and FND_FILE are working fine or not.

Below are the scripts to check whether UTL_FILE is working or breoken. In order to fix the issue need to run the utilSunc ad DB node. set serveroutput on DECLARE file_location VARCHAR2(256) := ' '; file_name VARCHAR2(256) := 'utlfile1.lst'; file_text VARCHAR2(256) := 'THIS IS A TEST'; file_id UTL_FILE.file_type; BEGIN file_id := UTL_FILE.fopen(file_Location, file_name, 'W'); UTL_FILE.put_line(file_id, file_text); UTL_FILE.fclose(file_id); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN dbms_output.put_line('Invalid path ' || SQLERRM); WHEN OTHERS THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM); END; / References: Metalink (MOS) Note ID: 261693.1 FND_FILE set serveroutput on exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

Oracle Value Chain Planning (VCP) Instance Setup

VCP modules can be implemented on the same instance where other modules like Order Management, Purchasing, Field Service etc are implemented. But, the regular practice is to use a distributed system. In a distributed environment we implement VCP on a separate instance (called the destination instance) and bring in the data required for VCP from another instance called the Source Instance. VCP Instance setup steps Create the source instance entry into VCP. A single VCP instance can plan for many source instances. Every source instance has a unique 'Instance Code' which we give as the entry while creating the 'Application Instances'. Functional Setup: Navigation - Service Supply Chain Administrator --> Admin --> Instances Important Fields: Instance Code -- Unique Source Instance Code Version -- Version of Source Instance. (Ex: R12.1) From Source to APS -- Database Link to get data from source

Get FND Profile Values in Oracle EBS 12.2

SELECT FND_PROFILE.VALUE (‘MFG_ORGANIZATION_ID’), FND_PROFILE.VALUE (‘ORG_ID’), FND_PROFILE.VALUE (‘LOGIN_ID’), FND_PROFILE.VALUE (‘USER_ID’), FND_PROFILE.VALUE (‘USERNAME’), FND_PROFILE.VALUE (‘CONCURRENT_REQUEST_ID’), FND_PROFILE.VALUE (‘GL_SET_OF_BKS_ID’), FND_PROFILE.VALUE (‘SO_ORGANIZATION_ID’), FND_PROFILE.VALUE (‘APPL_SHRT_NAME’), FND_PROFILE.VALUE (‘RESP_NAME’), FND_PROFILE.VALUE (‘RESP_ID’) FROM DUAL; FND_PROFILE.PUT Summary procedure FND_PROFILE.PUT (name IN varchar2, valueIN varchar2); Location FNDSQF library and database (stored procedure) Description Puts a value to the specified user profile option. If the option does not exist, you can also create it with PUT. All PUT operations are local -- in other words, a PUT on the server affects only the server-side profile cache, and a PUT on the client affects only the client-side cache. By using PUT, you destroy the synchrony between server-side and client-side profile caches. As a result, we do not recommen

End of Support date for Oracle WebLogic Server

  WebLogic Server 12.1.x Releases Version Release Date Announcement End of Grace Period WLS Dec-11 n/a JUL 2015 (b) FMW Jul-13 Note 1567707.1 JUN 2016 (b) FMW Jun-14 Note 1567707.1 (a)(b)(c)   (a) End of Error Correction is determined by a subsequent Patch Set release to set the beginning of the grace period. Without a subsequent Patch Set release, this version has error correction support as per the Lifetime Support Policy document’s Premier and Extended Support dates. Look for “12.1.x”. (b) For 12c Release 1 (12.1), the grace period date is set based on the third digit or minor version, (e.g., 12.1.1, 12.1.2, 12.1.3) with a two year grace period for these 12.1.x releases. No Patch Sets were released for WebLogic Server 12.1.x. For WLS 12.1.x, the grace period date is set based on the third digit or minor version, e.g., 12.1.1, 12.1.2, 12.1.3. See Error Correction Policy Note 209768.1 and look for Oracle Fusion Middleware Appendix A.2. (c) Update Nov 2

How to generate Hang Analyze Report

  Sometimes database hangs due to many reasons and higher managements look for for RCA. It is important to have all trace file to find the issue for hanging the database or fine the RCA. Get a hang analyze report by running below commands  SQL> sqlplus / as sysdba SQL> oradebug setmypid SQL> oradebug unlimit SQL> oradebug hanganalyze 3 # Wait at least 2-3 minutes to give time to identify process state changes. SQL> oradebug hanganalyze 3 # Wait at least 2-3 minutes to give time to identify process state changes. SQL> oradebug hanganalyze 3 SQL> oradebug tracefile_name SQL> select name, value from v$diag_info where name='Default Trace File'; In case database can't be connected  at all then please do  to the following commands  connect / as sysdba oradebug setmypid oradebug unlimit oradebug dump systemstate 266 -g; oradebug dump systemstate 266 -g; oradebug dump systemstate 266 -g; To generate HANGANALYZE for RAC: SQL> oradebug setmypid S

How To Generate AWR Report In the RAC Enviornments.

AWR report can be generating in RAC database using 2 scripts awrrpt.sql or awrrpti.sql and It can be generated at the CDB and PDB levels. awrrpt.sql  – > This will generate the one report for the database across all the nodes. awrrpti.sql  – > This will generates report for a particular instance levels. [demantra@dsiddem admin]$ sqlplus "/as sysdba" SQL*Plus: Release - Production on Fri Jul 28 13:05:32 2023 Version Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release - Production Version SQL> @ $ORACLE_HOME/rdbms/admin/awrrpt.sql   Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ AWR reports can be generated in the following formats. Please enter the name of the format at the prompt. Default value is 'html'. 'html' HTML format (default) 'text' Text format 'active-html' Includes Performance Hub active re

Few Important steps of Oracle Database Clone

 Make Sure oraInst.loc location is correct and accessble. [demantra@dsiddem ~]$ cat /etc/oraInst.loc inventory_loc=/orastage/oraInventory inst_group=dba [demantra@dsiddem ~]$ cd /orastage/oraInventory [demantra@dsiddem oraInventory]$ ls -ltr total 16 drwxrwx---. 5 demantra dba 4096 Jul 22 19:37 backup drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 ContentsXML drwxrwx---. 2 demantra dba 4096 Jul 22 19:37 locks drwxrwx---. 3 demantra dba 4096 Jul 23 10:08 logs Remove Oracle Home from Inventory [demantra@dsiddem oraInventory]$ cd $ORACLE_HOME/oui/bin [demantra@dsiddem bin]$ ./runInstaller -detachHome ORACLE_HOME=$ORACLE_HOME Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB.   Actual 3039 MB    Passed The inventory pointer is located at /etc/oraInst.loc Run [demantra@dsiddem bin]$ echo $ORACLE_HOME /dnbsid/demantra/oracle/19.0.0 [demantra@dsiddem bin]$ echo $ORACLE_BASE /dnbsid/demantra [demantra@dsiddem bin]$ E01=ORACLE_HOME=$ORACLE_HOME [dema

How To Move Or Rename The Tempfile In Oracle

 Log in SYSDBA  Check  Tempfile  status SQL> SELECT v.file#, t.file_name, v.status 2 FROM dba_temp_files t, v$tempfile v 3 WHERE t.file_id = v.file#; FILE# FILE_NAME STATUS ---------- ------------------------- ------- 1 /appsolworld/temp01.dbf ONLINE 2 /appsolworld/temp02.dbf ONLINE 3 /appsolworld/temp03.dbf ONLINE  Make it OFFLINE the tempfile that has to move SQL> ALTER DATABASE TEMPFILE '/appsolworld/temp01.dbf' OFFLINE; We should make offline the tempfile that has to move, if not then will get this error : SQL> ALTER DATABASE RENAME FILE '/appsolworld/temp01.dbf' TO '/u02/temp01.dbf'; ALTER DATABASE RENAME FILE '/appsolwolrd/temp01.dbf' TO '/u02/temp01.dbf' * ERROR at line 1: ORA-01511: error in renaming log/data files ORA-01121: cannot rename database file 1026 - file is in use or recovery ORA-01110: data file 1026: '/appsolworld/

How To Text The File Using Linux Cat Command

 Overview ======== Here we look at how to write text into a file using the Linux cat command. The cat Command ================ Cat command's common usages is to print the content of a file onto the standard output stream. Other than that, the cat command allows us to write some texts into a file. The Syntax ========== Please look at the general syntax of the cat command: cat [OPTION] [FILE] Copy First, OPTION is a list of flags we can apply to modify the command’s printing behavior,  whereas FILE is a list of files we want the command to read. Making cat Read From stdin ========================== Let’s execute the cat command: cat Now try to enter some texts into the terminal: cat This is a appsolworld This is a appsolworld Once we are done, we can terminate the command by pressing CTRL+D. [demantra@dsiddem demantra]$ cat >> a appsolworld ^C [demantra@dsiddem demantra]$ cat a sfsfsfsd appsolworld [demantra@dsiddem demantra]$ cat > a this [demantra@dsiddem demantra]$ cat a

How to use the _FIX_CONTROL hidden parameter

  _FIX_CONTROL is a special hidden dynamic parameter which can be used to enable or disable certain bug fixes. This is typically used in the optimizer area to allow specific fixes to be turned on or off as some fixes can have a significant  effect on execution plans.  we are using the parameter _FIX_CONTROL to  enable or disable specific patches.  The syntax for using _FIX_CONTROL is: To enable: "_fix_control"='Bugno:ON'    (OR)   "_fix_control"="Bugno:1" To disable: "_fix_control"='Bugno:OFF'  (OR)   "_fix_control"="Bugno:0"   For _FIX_CONTROL to work, several conditions must be met:  The patch that is referenced must have the option to use _FIX_CONTROL.  Using _FIX_CONTROL can't be used to back out any patch.  The patch (usually an Optimizer patch)  has to be enabled to use the _FIX_CONTROL parameter. The patch must be installed and visible in the V$SYSTEM_FIX_CONTROL view.  To check this:            SQL&

Manually creating and Installing the HR Schema in 19c

Below steps to manually install HR schema in the database: However it can be done during DB creation through DBCA. 1. Log on to sqlplus as SYS AS SYSDBA: [oracle@shsdnbty01 admin]$ sqlplus "/as sysdba" SQL*Plus: Release - Production on Fri May 5 22:54:00 2023 Version Copyright (c) 1982, 2019, Oracle.  All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release - Production Version SQL> show pdbs;     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 DB1                            READ WRITE NO SQL> alter session set container="DB1"; 2. Run  hr_main.sql  script using the following command: SQL> @?/demo/schema/human_resources/hr_main.sql specify password for HR as parameter 1: Enter value for 1: hr specify default tablespeace for HR as parameter 2: Enter v