How do I drop an Oracle database including datafiles

 

How do I drop an Oracle database including datafiles?

1. Make a connection with the database as a sys user.

[oracle@db01 ~]$export ORACLE_SID=CDB
[oracle@db01 ~]$sqlplus '/ as sysdba'

2. Shutdown the database.

SQL> shu immediate

3. Start Database in Exclusive mode till mount stage. Start the process to drop the oracle database manually.

SQL> startup mount exclusive restrict;

4. Now Drop the Database.

SQL> drop database;

CDB database has been dropped successfully.

Read More

Unable to create files in the following UTL_FILE_DIR locations

 

----------------------------------------------------------------------------------------------------------------- Warning: Unable to create files in the following UTL_FILE_DIR locations: - /u01/app/oratest1/oracle/product/temp/TEST1 - /u01/app/oratest1/oracle/product/19.0.0.0/temp/TEST1 - /u01/app/oratest1/temp Error in initialize_files at step 5: Unable to identify a valid output directory for UTL_FILE Error in print_log: ORA-29282: invalid file ID Error encountered: ORA-29282: invalid file ID PL/SQL procedure successfully completed.

1) Source the PDB environment file 

   

    . $ORACLE_HOME/<<pdb_name>>_<<hostname>>.env

2) Create the new directory path. 

    For example: $mkdir /u01/debuglogdir

3) run the txkCfgUtlfileDir.pl script in addUtlFileDir mode

    $ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=addUtlFileDir

      a) When prompted, enter the passwords for the APPS user (the Oracle E-Business Suite database user) and for the SYSTEM user of the root container database (CDB).

      b) When prompted, enter the physical directory path you want to add.

4) Finally, synchronize the modified UTL_FILE_DIR value with the database context file. To do so, run the txkCfgUtlfileDir.pl script in syncUtlFileDir mode using the following commands:

    $ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=syncUtlFileDir

    

Still getting error.  Try following to fix the issue

There are 4 steps for addressing the ORA-20100 error:

1.  utl_file_dir parameter is not set. Run following SQL to check: 

select value from v$parameter where name = 'utl_file_dir';

If no value is returned, make the setup according to Doc ID 2525754.1, sections:

3.1.2: Updating UTL_FILE_DIR in an Oracle E-Business Suite Instance on Oracle Database 19c

OR

3.2: Referencing a Physical Directory Path Without Using UTL_FILE_DIR

 

2.  First value from above SQL should match to value of $APPLPTMP environment parameter.

 

3.  Event 10946 should be set at Container Database (CDB) level, as explained in Note 2525754.1:

alter system set events '10946 trace name context forever, level 8454144';
alter system set event='10946 trace name context forever, level 8454144' scope=spfile;

 Bounce application and database after setting the event.

 

4. There is a concern with the synonyms. If issue persists please restart of the server and follow the steps below:

a. Execute select owner,object_name,object_type,status,created from dba_objects where object_name='FND_FILE'; to verify the synonym details

b. Recreate the synonym that exists for Custom schema
create or replace SYNONYM schema_name.FND_FILE for apps.FND_FILE; (schema_name should replace with custom schema name)

c. Provide grant by executing below command:
grant execute on directory EBS_DB_DIR_UTIL to public;
grant read, write on directory EBS_DB_DIR_UTIL to public;

d. Provide grant to custom schema:
grant execute on fnd_file to custom schema name;

e. Recompile the Custom package that is used in the Custom program.

f.  Retest the issue by submitting the concurrent program.

Read More

ORA-20100: Temporary file creation for FND_FILE failed

 APP-AR-11526: ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.

SOLUTION

There are 4 steps for addressing the ORA-20100 error:

1.  utl_file_dir parameter is not set. Run following SQL to check: 

select value from v$parameter where name = 'utl_file_dir';

If no value is returned, make the setup according to Doc ID 2525754.1, sections:

3.1.2: Updating UTL_FILE_DIR in an Oracle E-Business Suite Instance on Oracle Database 19c

OR

3.2: Referencing a Physical Directory Path Without Using UTL_FILE_DIR

 

2.  First value from above SQL should match to value of $APPLPTMP environment parameter.

 

3.  Event 10946 should be set at Container Database (CDB) level, as explained in Note 2525754.1:

alter system set events '10946 trace name context forever, level 8454144';
alter system set event='10946 trace name context forever, level 8454144' scope=spfile;

 Bounce application and database after setting the event.

 

4. There is a concern with the synonyms. If issue persists please restart of the server and follow the steps below:

a. Execute select owner,object_name,object_type,status,created from dba_objects where object_name='FND_FILE'; to verify the synonym details

b. Recreate the synonym that exists for Custom schema
create or replace SYNONYM schema_name.FND_FILE for apps.FND_FILE; (schema_name should replace with custom schema name)

c. Provide grant by executing below command:
grant execute on directory EBS_DB_DIR_UTIL to public;
grant read, write on directory EBS_DB_DIR_UTIL to public;

d. Provide grant to custom schema:
grant execute on fnd_file to custom schema name;

e. Recompile the Custom package that is used in the Custom program.

f.  Retest the issue by submitting the concurrent program.

Note:  FND_FILE is a wrapper for UTL_FILE. Therefore, first make sure UTL_FILE is valid.

 

Read More

Collecting Version and Patch Inventory Details for Oracle Identity Manager 11g Issues

 

Gathering version and patch details for issues in OIM 11g: 

These steps assume Linux variables and pathing.  For Windows environments, adjust your steps accordingly.  For OIM 11g clustered domains which are *not* using shared storage, these commands must be run on each cluster nodes installation location.

1. For the WLS home, set the domain environment variables using the script in the $DOMAIN_HOME/bin directory, then check the list of applied patches using the BSU utility, printing the results to a text file for readability:
   [oracle@machine1 bin] .  setDomainEnv.sh
   [oracle@machine1 oim_domain]$ cd $MW_HOME/utils/bsu
   [oracle@machine1 bsu]$ ./bsu.sh -view -status=applied -prod_dir=$WL_HOME > WLSpatches.txt
2. From the same terminal window, set the ORACLE_HOME to use the oracle_common home and set the path to include the path to OPatch, then run the opatch lsinventory -detail command, printing the output to a text file for readability:
   [oracle@machine1 bsu]$ export ORACLE_HOME=$COMMON_COMPONENTS_HOME
   [oracle@machine1 bsu]$ export PATH=$PATH:$ORACLE_HOME/OPatch
   [oracle@machine1 bsu]$ opatch lsinventory -detail > ASdetail.txt
3. From the same terminal window, perform similar steps for the SOA home, setting the ORACLE_HOME to use the soa home and running the opatch lsinventory -all command, printing the output to a text file for readability:
   [oracle@machine1 bsu]$ export ORACLE_HOME=$SOA_ORACLE_HOME
   [oracle@machine1 bsu]$ opatch lsinventory -all > SOAall.txt
4. From the same terminal window, use similar steps for the OIM home:
   [oracle@machine1 bsu]$ export ORACLE_HOME=$OIM_ORACLE_HOME
   [oracle@machine1 bsu]$ opatch lsinventory > OIMinv.txt
5. Verify the files from steps 1 - 4 contain the component version and patch details, then copy the files for upload to Oracle Support.
6. Save these files to a location of your choice and be sure to keep the current version of these files available for future uploads to Oracle Support.

Read More

Change APPS password in EBS R12.2.x Weblogic Admin server

1.         Start AdminServer. Do not start any other application tier services.

adadminsrvctl.sh start

adadminsrvctl.sh status

 [oracle@erptest05app01 scripts]$ adadminsrvctl.sh status

You are running adadminsrvctl.sh version 120.10.12020000.11

Enter the WebLogic Admin password:
Enter the APPS Schema password:

 The AdminServer is running

AdminServer logs are located at /u01/install/APPS/fs2/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs

adadminsrvctl.sh: exiting with status 0

adadminsrvctl.sh: check the logfile /u01/install/APPS/fs2/inst/apps/TEST5_erptest05app01/logs/appl/admin/log/adadminsrvctl.txt for more information ...

2.         Change the APPS password in WLS Data Source by running the following script as shown:

[oracle@erptest05app01 scripts]$ perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
Please select from list of valid options
        updateDSPassword - Update WebLogic Datasource Password
        updateDSJdbcUrl  - Update WebLogic Datasource Connection String
Enter Your Choice : updateDSPassword
Enter the full path of Applications Context File [DEFAULT - /u01/install/APPS/fs2/inst/apps/TEST5_erptest05app01/appl/admin/TEST5_erptest05app01.xml]:
Enter weblogic admin server password:
Enter the APPS user password:


---------------------------------------------
Performing Steps for option=updateDSPassword
---------------------------------------------

Updating WLS DataSource Password
--------------------------------

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Connecting to t3://erptest05app01.prodapp.sgfysavcn01.oraclevcn.com:7001 with userid weblogic ...
Successfully connected to Admin Server 'AdminServer' that belongs to domain 'EBS_domain'.

Warning: An insecure protocol was used to connect to the
server. To ensure on-the-wire security, the SSL port or
Admin port should be used instead.

Domain Update successful
------------------------
[oracle@erptest05app01 scripts]$

3.         Stop AdminServer

adadminsrvctl.sh stop

4.         Start all the application tier services using the below script

  adstrtal.sh

 

Read More

Oracle EBS R12 on 19c Database

Oracle has recently announced the release of Oracle E-Business Suite 19c Database. With the Database 19c certification, EBS 12.2 On-Premises databases are now certified with the CDB architecture (multitenant architecture).

 

Background

With the launch of database 12c in 2013, Oracle introduced a new architectural concept, called “Multitenant Databases”, where you have one super database (=container; CDB) and one or more sub-databases (= pluggable; PDB).

EBS (R12.2) Architecture Overview

Architecture mainly consists of:

·         Application Tier

·         Database Tier

·         Client Tier

Overview of Oracle Database 19c

Oracle Database 19c, is the long term support release of the Oracle Database 12c and 18c family of products, offering customers Premier and Extended Support through to March 2023 and March 2026 respectively. It is available on Linux, Windows, Solaris, HP/UX and AIX platforms as well as the Oracle Cloud.

Oracle Database 19c offers customers the best performance, scalability, reliability, and security for all their operational and analytical workloads.

 

What’s new for EBS with Oracle Database 19c

There are several mandatory architecture updates when you upgrade your Oracle E-Business Suite database to Oracle Database 19c.  The following is a summary of the key required architectural changes:

·         Container Database (CDB) Architecture: With the Database 19c certification, EBS 12.2 on-premises databases are now certified with the CDB architecture (multitenant architecture). As part of the upgrade to Database 19c, you will convert your EBS database to the CDB architecture with a single pluggable database (PDB).

·         PL/SQL File Input/Output Directories: With Database 19c, the UTL_FILE_DIR database initialization parameter has been desupported. As part of the upgrade to Database 19c, you must migrate all UTL_FILE_DIR database initialization parameters to database directory objects.  EBS development has created a utility to migrate and manage your defined PL/SQL file input/output directories.

·         ADG DML Redirection:  Previously, you could only update the Global Temp Tables on Active Data Guard standby database. From Oracle 19c, you can also update regular tables.
ADG_REDIRECT_DML enables DML redirection from Oracle Active Data Guard.

 

 Oracle Database 19c Support for EBS R12.x

·         Oracle Database 19c(19.3) has been recently supported for E-business R12.1.3 and R12.2.3 +

·         Only Multitenant Architecture is supported

·         Only One PDB in a CDB is supported for E-business

·         De-Support of UTL_FILE_DIR initialization parameter

·         Pluggable Database support

·         Oracle Linux 7.5 & above

Why Upgrade To Database 19c?

·         Oracle Database 11.2.0.4/12.1.0.2 Extended Support Waived Only Till Dec 2020 (To know more about this, check Extended Support Fee Waived for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite)

·         All Customers Should Upgrade Their Database By Then To Avoid Extra Cost

·         At The Moment Only EBS R12.1.3 & R12.2.3+ Is Supported

·         Support For R12.0 And 11i Is Expected On A Later Date

Some of the High-Level Steps about Upgrade.

·         Configure DB Home for EBS

·         If R12.2.x, ensure that no ADOP cycle is active, Upgrade Database to 19c

·         Prepare for PDB Conversion & Convert to PDB

·         Execute Post PDB Conversion script txkPostPDBCreationTasks.pl

·         MOS Doc Id. 396009.1 Modify Database Initialization parameters following

·         Run autoconfig on the Application tier

·         Rewrite customizations depending on UTL_FILE_DIR

·         Rewrite customizations if impacted due to CDB/PDB model

·         Start  Application services and carry out complete Health Check

I hope you find this blog useful in understanding the 19c Database upgrade process in Oracle EBS R12 if you have any doubts regarding the 19c database upgrade post in the comment section.

References

·         Doc Id. 1594274.1 Install Oracle Database binaries 19c and apply EBS overlay patches as per

·         Doc Id. 2552181.1 Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c

·         Doc ID 396009.1 Database Initialization Parameters for Oracle E-Business Suite Release 12

·         Doc ID 2530665.1 Using Oracle Database 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.2

·         Doc ID 2554156.1 Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19c

 

Read More