foalabs

Scripts to update the Concurrent Node Details when migrating from One server to other If we are migrating from once server to other, or during DR switch over, we need to update the concurrent node server details. We can use below scripts to update the same. Scripts: update FND_CONCURRENT_REQUESTS set OUTFILE_NODE_NAME = 'NEW_SERVER' where OUTFILE_NODE_NAME = 'OLD_SERVER';  update FND_CONCURRENT_REQUESTS set LOGFILE_NODE_NAME = 'NEW_SERVER' where LOGFILE_NODE_NAME = 'OLD_SERVER';  commit; update FND_CONCURRENT_QUEUES set NODE_NAME = 'NEW_SERVER' where NODE_NAME = 'OLD_SERVER';  commit; update FND_CONCURRENT_REQUESTS set LOGFILE_NAME = REPLACE(LOGFILE_NAME, '/old_path/', '/new_path/') where LOGFILE_NAME like '/old_path/%'; update FND_CONCURRENT_REQUESTS set OUTFILE_NAME = REPLACE(OUTFILE_NAME, '/old_path/', '/new_path/') where OUTFILE_NAME like '/old_path/%'; commit; update FND_CONC_REQ_OUTPUT...

ORA-20000 APPS_DDL/APPS_ARRAY_DDL Package(s) Missing Or Invalid In Schema CTXSYS Error: When attempting to apply a Patch the following error occurs: sqlplus -s APPS/***** @$AD_TOP/12.0.0/patch/115/sql/adgrnctx.sql &systempwd &un_fnd CTXSYS &un_icx ICX Connected. User altered. declare * ERROR at line 1: ORA-20000: APPS_DDL/APPS_ARRAY_DDL package(s) missing or invalid in schema CTXSYS ORA-06512: at "SYSTEM.AD_DDL", line 165 ORA-06512: at line 19 The issue is caused by the missing AD_DLL object in the Database. Please execute following script, to verif sqlplus apps/<pwd> SELECT owner, object_name FROM all_objects WHERE object_name= 'AD_CTX_DDL' AND object_type = 'PACKAGE'; select owner,SYNONYM_NAME from dba_synonyms where table_owner='CTXSYS' and TABLE_NAME='AD_CTX_DDL'; SOLUTION: To implement the solution, please execute the following steps : 1. Execute the script adctxpkg.sql located under the $AD_TOP/patch/115/sql directory to...

Error DRG-10758 When Creating Text Index Using File or URL Datastore Error: When trying to creating a Text index using a file or URL datastore, it may fail with the error DRG-10758: index owner does not have the privilege to use file or URL datastore, see below: begin   ctx_ddl.create_preference('COMMON_DIR','FILE_DATASTORE'); end; / create index doc_index on documents (filename) indextype is ctxsys.context parameters ('DATASTORE COMMON_DIR'); * ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: Oracle Text error: DRG-10758: index owner does not have the privilege to use file or URL datastore ORA-06512: at "CTXSYS.DRUE", line 160 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366 Solution: To change file_access_role, you must be connected as SYSDBA. SQL> connect as sysdba SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public') If you like please follow and commen...

Autoconfig fails with DRG-10502: Index JTF.JTF_AMV_ITEMS_URL_CTX Error: Autoconfig failing with the below error:   Executing script in InstantiateFile: jtfictx.sh script returned: **************************************************** jtfictx.sh started at Fri Jul 7 18:32:47 UTC 2023 SQL*Plus: Release 10.1.0.5.0 - Production on Fri Jul 7 18:32:48 2023 Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter value for 1: Enter value for 2: Enter value for 3: Connected. DECLARE * ERROR at line 1: ORA-20000: Oracle Text error: DRG-10502: index JTF.JTF_AMV_ITEMS_URL_CTX does not exist ORA-06512: at "CTXSYS.DRUE", line 186 ORA-06512: at "CTXSYS.CTX_DDL", line 1231 ORA-06512: at "CTXSYS.AD_CTX_DDL", line 336 ORA-06512: at line 96 ORA-06512: at line 96 Solution: 1. Connect to Mid-tier node to directory: $JTF_TOP/patch/115/sql 2. sqlplus apps/apps_pwd @jtfiimt.sql jtf jtf @jtfiaitd.sql jtf jtf @jtfiaitn.sql jtf jtf @jtfiaitt.sql jtf jtf @jtfiappr.sql apps @jtfia...

ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted Issue: ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 1 with process name "DW00" prematurely terminated ORA-31671: Worker process DW00 had an unhandled exception. ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted ], [], [], [], [], [], [], [], [], [], [], [] ORA-39014: One or more workers have prematurely exited. ORA-39029: worker 2 with process name "DW00" prematurely terminated ORA-31671: Worker process DW00 had an unhandled exception. ORA-00600: internal error code, arguments: [pesldl03_MMap: errno 1 errmsg Operation not permitted Reason The issue occurs since 11g as we are now using /dev/shm when using native PLSQL compilation. This requires that /dev/shm is of type tmpfs and is mounted with the following options:With rw and exec permissions set on it Without noexec or nosuid set on it If /de...

ORA-00600 [pesldl02_Make_Object: not writable: errno 28 errmsg No space lef] Running the utlrp.sql The utlrp.sql execution fails with the errors: ORA-07445: exception encountered: core dump [__intel_new_memcpy()+5497] [SIGBUS] [ADDR:0x2AD587BC7000] [PC:0x47F9BB9] [Non-existent physical address] [] ORA-00600: internal error code, arguments: [pesldl02_Make_Object: not writable: errno 28 errmsg No space lef], [], [], [], [], [], [], [], [], [], [], [] When the plsql_code_type is set to NATIVE, the native cache is "stored" in tmpfs - > /dev/shm So, if the device has not enough space, the errors are returned   SOLUTION 1. Increase the space in /dev/shm or 2. Change the native cache to other directory wit enough space using the parameter _ncomp_shared_objects_dir If you like please follow and comment

How To Manually Install Data Mining In Oracle 11g and 12C Oracle Data Mining 11gRelease 1 (11.1) has a tight integration with Oracle Database. Data Mining metadata and PL/SQL packages have been migrated from DMSYS to SYS. The DMSYS schema no longer exists in Oracle Database 11g Release 1 (11.1) fresh installations. The DMSYS schema no longer exists in Oracle Database 11g releases unless the database has been upgraded from earlier version. Also there is no longer an 'odm' directory in the ORACLE_HOME. If the database has been upgraded from earlier version, then DMSYS schema could be there. There is no longer an 'odm' directory in the ORACLE_HOME, and no entry for Data Mining in the DBA_REGISTRY but there is a value in V$OPTION:  SQL> col value format a30 SQL> col parameter format a30 SQL> select * from v$option where PARAMETER = 'Data Mining'; PARAMETER                      VALUE ------------------------------ ---------...

Invalid OLAP Catalog And Missing CWM$ Tables Error: , cwm$classificationentry ce * ERROR at line 10: ORA-00942: table or view does not exist Reason: This will occur if you upgrade the database when Olap Catalog is Invalid but could also occur under different circumstances. The cwm$ tables are created by onetabs.sql. Here is the logic in olap.sql. select count(*) into isthere from dba_users where username ='OLAPSYS'; if ( isthere > 0 ) then :file_name := 'amdrelod.sql'; else :file_name := 'catamd.sql'; end if;  The call to onetabs.sql is in catamd.sql. If the user OLAPSYS does not exist, then catamd.sql  is executed. but if OLAPSYS user exists  amdrelod.sql is executed. Solution: conn / as sysdba drop user "OLAPSYS" CASCADE; Then re-run the olap.sql commands: SQL> spool add_olap.log SQL> select name from v$database; SQL> @?/olap/admin/olap.sql SYSAUX TEMP; SQL>@?/rdbms/admin/utlrp.sql; SQL> column object_name format a35 SQL> column ...

The status of the OLAP components in the dba_registry is LOADED OR UPGRADED For 9i OLAP is still functional in UPGRADED or LOADED Status. For 10G/11g, OLAP is only functional with a VALID status. To check the status of the components, use the following Sql command: SQL> select substr(comp_id,1,20)"COMP_ID",substr(comp_name,1,25)"Component Name",substr(version,1,10) "version",status from dba_registry;   The results:   COMP_ID     Component                               Name version              STATUS -------------------- ------------------------- ---------- --------------------------------------------- APS            OLAP Analytic Workspace           10.2.0.3                     LOADED XOQ           Orac...

How to Install and Deinstall Oracle Text CTXSYS in 11g Below are the steps to install Oracle Text on our Oracle Database 11gR2 My DB version is 11.2.0.4 Installation Steps Execute following script to install oracle text and CTXSYS user while connecting as sysdba.              sqlplus ‘/as sysdba’ spool /local/oracle/admin/tmp/text_install.txt @$ORACLE_HOME/ctx/admin/catctx.sql oracle123 SYSAUX TEMP NOLOCK Where : oracle123 – is the ctxsys user password SYSAUX – is the default tablespace for ctxsys TEMP – is the temporary tablespace for ctxsys LOCK|NOLOCK – ctxsys user account will be locked or not The next step is to install appropriate language-specific default preferences.There is script which creates language-specific default preferences for every language Oracle Text supports in $O_H/ctx/admin/defaults directory, such as English(US), Danish(DK), Dutch(NL), Finnish(SF), French(F), German(D), Italian(IT),...

Uploading Manifest File Errors With The submitted manifest is not valid Error: When attempting to upload manifest file for platform migration to https://updates.oracle.com/PlatformMigration, the following error occurs. ERROR The submitted manifest is not valid. Please review MetaLink Note 438086.1 and verify that the file manifest was correctly generated. The submitted manifest for Release R12 does not contain the required information on baseline. Solution: 1. Backup the file adgenpsf.txt and update as follows From # extension current baseline ad To # extension current baseline ad A Save changes. 2. Go to https://updates.oracle.com/PlatformMigration Upload manifest. You should receive such a confirmation: The migration patch for <platform> will be generated using patch <NNNNN>. When the migration patch is available, an email will be sent to you with the password and instructions on how to download. In case of 12.1.x, the update could be "ad B" and in case of 12.2....

11gR2 installation failed with Error in CreateOUIProcess(): 13 : Permission denied  Error: Below error message reported when when installing 11gR2 client or running clone.pl Error in CreateOUIProcess(): 13 : Permission denied” INFO: Error Message:PRVF-4001 : Check: Space available on "/tmp" INFO: Cause: Could not determine mount point for location specified. INFO: Action: Ensure location specified is available. Starting Oracle Universal Installer... Checking swap space: must be greater than 500 MB.   Actual 5118 MB    Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2023-10-21_12-08-16PM. Please wait ...Error in CreateOUIProcess(): 13 : Permission denied Reason: /tmp directory is mounted with noexec option. You can verify this with following command: $mount on /tmp type ext3 (rw,noexec,nosuid,nodev) Solution: 1) With help of System Administrator and Remove “noexec” on /tmp folder For example, to temporarily disable the noexec flag: $ mou...

How to Boot a System into Rescue Mode from Installation DVD/ISO on OEL/CentOS 7 Booting a system into rescue mode from an installation DVD or ISO on CentOS 7 is a useful way to recover or repair a Linux system. Steps: 1.(Attach an ISO image): Insert bootable media such as USB or DVD or ISO image, I would like to use ISO image because it can be easily mounted on the system without going to the data center. reboot 2. Boot up the CentOS 7 system from the ISO image and wait for the boot menu to appear. Select the ‘Troubleshooting’ option and press enter. 3. In the next screen, select the ‘Rescue a CentOS Linux system’ option and press enter. 4. At the next screen, input '1' and then press enter to continue. The rescue environment will now attempt to find your Linux installation and mount it under the directory '/mnt/sysimage', but in read-only mode. 5.Now, your system has been mounted under ‘/mnt/sysimage’. Even if your file system is mounted, the default root partition whi...

How to change expiration date of GPG subkey Changing the expiration date for a subkey in your GPG (GNU Privacy Guard) keypair can be done using the gpg --edit-key command. Please keep in mind that you'll need to have the private key for the subkey to make changes to its expiration date.  Steps: Open a terminal or command prompt. 1) Start the key editing session by running: gpg --edit-key your_key_id 2) Replace your_key_id with the ID or fingerprint of your keypair. List your subkeys by typing: list This command will display a list of subkeys associated with your keypair. 3) Select the subkey you want to modify by typing: key 1 Replace 1 with the number that corresponds to the subkey you want to edit, based on the list from the previous step. First key would be 0, then 1, then 2.. 4) Set the expiration date for the subkey. You can do this by using the expire command: expire 5) You will be prompted to enter a new expiration date for the subkey. You can set the expiration date in a re...

Srvctl Start Database open mode and role modification with Examples In Oracle Grid Infrastructure, the srvctl command is used to manage Oracle Real Application Clusters (RAC) and Automatic Storage Management (ASM) components, including Oracle databases. To modify the open mode of an Oracle database using srvctl, you can follow these steps.  Step 1: Connect to the Server Connect to the server where the database is located using SSH or a terminal session. Step 2: Log in as the Oracle Software Owner Log in as the Oracle software owner (typically, "oracle") or a user with the necessary privileges to modify the open mode of the database. su - oracle Step 3: Modify the Open Mode Use the srvctl modify database command to change the open mode of the database. The command syntax is as follows: srvctl modify database -db <DBUniqueName> -startoption <open_mode> -role <role> -db <DBUniqueName>: Replace <DBUniqueName> with the unique name of your Oracle datab...

Adop Prepare/Abort Phase Hung While Executing AD_ZD_SEED.PREPARE.FND_PROFILE_OPTION_VALUES Error: adop prepare/abort phase is hanging and not completing even after 52 hours. From the ADZDSHOWLOG.out file we can notice the below where it is stuck while executing synchronize task: FND_PROFILE_OPTION_VALUES_DELETE.  489121669 12:58:59 00:00:00 ad.plsql.ad_zd_seed.run_in_parallel        PROCEDURE  begin: FND_PROFILE_OPTION_VALUES_DELETE  489121669 12:58:59 00:00:00 ad.plsql.ad_zd_seed.run_in_parallel        STATEMENT  Create Task FND_PROFILE_OPTION_VALUES_DELETE  489121669 12:59:00 00:00:01 ad.plsql.ad_zd_seed.run_in_parallel        STATEMENT  Executing synchronize task: FND_PROFILE_OPTION_VALUES_DELETE, chunks: 5  489121649 12:59:51 00:00:50 ad.bin.adop                                EVENT      preparin...

How  To Shutdown(stop)/Startup(start) CRS, OHAS, ASM, RDBMS  Services on a RAC Cluster or OCI DBCS System I have used below steps to restarting services on GRID in DBCS. 1) Connect to node #1, then please check if the CRS/OHAS & services are enabled to autostart as follow (repeat this step on each node): # $GRID_ORACLE_HOME/bin/crsctl config crs 2) If not, then you can enable it as follow (repeat this step on each node): # $GRID_ORACLE_HOME/bin/crsctl enable crs 3) Shutdown the services on each node as follow: # $GRID_ORACLE_HOME/bin/crsctl stop crs 4) Verify the services were/are down (repeat this step on each node): # $GRID_ORACLE_HOME/bin/crsctl status resource -t 5) Then start the services on node #1 as follow (only on first node): # $GRID_ORACLE_HOME/bin/crsctl start crs 6) Wait 1 minute, then validate the services started & diskgroups were mounted (only on first node): # $GRID_ORACLE_HOME/bin/crsctl status resource -t 7) Then start the services on node #2 as foll...

RMAN-08591: warning: invalid archived log deletion policy Error: While taking archive backup we were getting below error: RMAN-08591: warning: invalid archived log deletion policy Solution: After check the RMAN configuration policy, we found: RMAN> SHOW ARCHIVELOG DELETION  POLICY; RMAN configuration parameters for database with db_unique_name ORCL are:  CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY; While no dataguard still the  retention policy of the data guard  Change the archive log deletion policy  New Value to be set as below RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;  If you like please follow and comment

How to Stop and Start DCS-AGENT in OCI Oracle Cloud Open ssh session to DB system(DBCS) as root user and execute the following commands : -For OEL6 Operating system     # initctl status initdcsagent     # initctl stop initdcsagent     # initctl start initdcsagent     # initctl status initdcsagent     -For OEL7 and above Operating system # systemctl status initdcsagent # systemctl stop initdcsagent # systemctl start initdcsagent # systemctl status initdcsagent If you like please follow and comment

Important Queries Related to DBMS Scheduler in Oracle Oracle DBMS Scheduler is a powerful tool for managing scheduled jobs, tasks, and programs within an Oracle database. Here are some important queries related to DBMS_Scheduler jobs in Oracle: 1. List All DBMS Scheduler Jobs: SELECT job_name, job_type, enabled, state  FROM dba_scheduler_jobs; 2. List Job Details and Job Actions: SELECT job_name, job_action  FROM dba_scheduler_jobs; 3. List Job History: select * from DBA_SCHEDULER_JOB_RUN_DETAILS  where job_name='JOB_NAME' order by log_id desc; 4. List Enabled Jobs: SELECT job_name  FROM dba_scheduler_jobs WHERE enabled = 'TRUE'; 5. List Disabled Jobs: SELECT job_name  FROM dba_scheduler_jobs WHERE enabled = 'FALSE'; 6. List Running Jobs: SELECT job_name FROM dba_scheduler_running_jobs; 7. Check the Last Run of a Job: SELECT job_name, last_start_date, last_run_duration  FROM dba_scheduler_jobs; 8. List Jobs with Specific Attributes (e.g., job class, cr...