Resolved ! POCFH: Fill Employee Hierarchy: Routine Fdpsql Cannot Read The File Poxpofeh.sql

Error:-

Routine fdpsql cannot read the file /data01/apps/apps_st/appl/po/12.0.0/sql/poxpofeh.sql.

Check if the file exists under correct product directory.

Check if the person who started the Concurrent Manager has read privileges

Concurrent Manager encountered an error while running SQL*Plus for your concurrent request 38239806.

Review your concurrent request log and/or report output file for more detailed information.


The file poxpofeh.sql appear to be mising. To resolve this issue need to be apply the following patch on R12.1.x.x.



1. Download and review the readme and pre-requisites for Patch 21965300:R12.PO.B - In Oracle Purchasing, Fill Employee Hierarchy program enhancement

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
   poxpofeh.sql 120.1.12010000.8

5. Retest the issue.

6. Migrate the solution as appropriate to other environments


In my instance issue was resolve after apply the above patches.

what is control file in oracle and how to multiplex controlfiles on separate disk

Here we will discuss 'what is control file' and 'how to multiplex control files on separate disk'.

Control file is a binary file which stores Database name, associated data files, redo files, DB creation time and current log sequence number. Without control file database cannot be started and can hamper data recovery.

The control file is used to keep track of the database's status and physical structure. Oracle strongly recommended that database has minimum two control file and they are stored in separate disk. We can create control file up to a maximum of 8.

Show Control File:-

SQL> show parameter control_files;

SQL> SELECT name FROM v$controlfile;

Check Content of control file.

SQL>SELECT * FROM v$controlfile_record_section;

Backup existing  control file in UDUMP;

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Make a binary copy of the control file:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/tmp/control.bkp';

Generate an SQL file to create the control file:

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/create_control.sql';

Multiplex control file.

Make sure that the new control file resides on a separate physical disk.

The purpose of multiple control files is to protect the database in case of a disk failure.

SQL>ALTER SYSTEM SET CONTROL_FILES=

’/u01/app/oracle/oradata/db01/control01.ctl’,

'/u02/app/oracle/fast_recovery_area/db01/control02.ctl’,

‘/u03/oradata/db01/control03.ctl’,

‘/u04/oradata/db01/control04.ctl’

SCOPE=spfile;

SQL> SELECT name FROM v$controlfile;

We will discuss on next topic ‘How to recreate control file‘ .

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

In this post we will covers 'How to resolve ORA-12514: TNS:listener does not currently know of service requested in connect descriptor'.


ERROR DESCRIPTION:
(*******FATAL ERROR*******
PROGRAM : (*******/fs1/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkPrepareACTables.pl)
TIME    : Sat Sep  8 10:25:42 2018
FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]
MESSAGES:
Invalid Connect string - cannot connect to database
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor



Here we are facing the issue while applying the patch.


Unable to connect database using system user in patch edition file system
Oracle APPS 12.2.5


Unable to connect database using system user in patch edition file system.


Check the file edition.


$echo $FILE_EDITION


$patch


Error will reproduce as below.


$sqlplus apps/apps


ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Connect db server:

sqlplus / as sysdba


Corrective action:-


SQL> alter system set service_names='<SID>','ebs_patch'  scope=memory;

SQL>alter system register;



alter system set service_names='<SID>','ebs_patch','<SID>_patch','<SID>_ebs_patch' scope=memory;


SQL>alter system set LOCAL_LISTENER=<SID> scope=memory;

System altered.

SQL> alter system set LOCAL_LISTENER=<SID> scope=spfile;

System altered.

SQL> alter system set LOCAL_LISTENER=<SID> scope=both;

System altered.

Then check for if ebs_patch listner service is running or not.

lsnrctl status SID



$tnsping SID ---->from both DB and apps server.


Now try to connect the Database from patch env using apps user.



If connecting database successfully , you may proceed patching.

How do you switch and create spfile from pfile.

In this article we will discuss 'how to create pfile from spfile'. Spfile is a binary file.  By default, a new Oracle database will be working on a pfile,  so the spfile must be created from the pfile using SQL command.

URL:- https://youtu.be/G5eE79poQzU

Check spfile exist or not.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string     

It means no spfile created in your database. Hence we can create the spfile from pfile.

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /data05/11.2.0.4/dbs/spfilePROD.ora

Create spfile on default location ($ORACLE_HOME/dbs)

SQL> create spfile from pfile;

File created

Create spfile on non default location.

SQL>Crete spfile ‘/data02/prod/spfilePROD.ora’ from pfile;

Create spfile from non default pfile location.

SQL>Create spfile from pfile ‘‘/data02/prod/initPROD.ora’;

Now we can switch the database from pfile to spfile;

SQL> shutdown immediate

SQL> startup spfile; 

or

SQL> startup;  (by default database will start using spfile , if spfile exist on default location)

In case of non default location.

SQL> startup spfile=‘/data02/prod/spfilePROD.ora’;

                                                           

Some useful configuration files and directories in linux

Below are useful file in Linux. Every DBA must aware for daily operation.

File Name	Description
/etc/passwd	/etc/passwd file is used to keep track of every registered user that has access to a system. File that contains the following information: User name. Encrypted password. User ID number (UID)
/etc/hosts	hosts file is an operating system file that maps hostnames to IP addresses.
/etc/group	The /etc/group file contains basic group attributes. This is an ASCII file that contains records for system groups. Each record appears on a single line and is the following format: Name:Password:ID:User1,User2,...,Usern
/etc/issue	/etc/issue is a text file which contains a message or system identification to be printed before the login prompt
/etc/resolv.conf	The /etc/resolv.conf is resolver configuration file for Linux and UNIX like operating systems.The file /etc/resolv.conf file contains information that is read by the resolver routines the first time they are invoked by a process.
/etc/yum.conf	The main configuration file for yum is /etc/yum.conf.The /etc/yum.conf configuration file contains exactly one [main] section. You can add many additional options under the [main] section heading in /etc/yum.conf.
/var/log/yum.log	if you install packages using the yum command, this log stores all related information, which can be useful in determining whether a package and all components were correctly installed.
/etc/named.conf	The /etc/named.conf file is the configuration file for named.The /etc/named.conf file establishes the server as a master, slave, or cache-only name server.
/etc/exports	The /etc/exports file controls which file systems are exported to remote hosts and specifies options.The file /etc/exports contains a table of local physical file systems on an NFS server that are accessible to NFS clients. The contents of the file are maintained by the server's system administrator.
/etc/sudo.conf	/etc/sudo.conf configuration file are allowed to use the sudo command and the command is executed in the user's shell, not a root shell.
/etc/cups/snmp.conf	The snmp.conf file configures how the standard CUPS network backends (http, https, ipp, ipps, lpd, snmp, and socket) access printer information using SNMPv1 and is normally located in the /etc/cups directory.
/etc/cups/cupsd.conf	The /etc/cups/cupsd.conf file contains configuration directives that control how the server functions.
/etc/cups/subscriptions.conf	The subscriptions.conf file defines the local event notification        subscriptions that are active.
/etc/cups/printers.conf	/etc/cups/printers.conf file to store the list of available printers. This is the printer configuration file with entries for each print queue.
/etc/cups/client.conf	The client.conf file configures the CUPS client and is normally located in the /etc/cups or ~/.cups directory.
/etc/oracle-release	This shows you any files that might give a hint on the operating system version.
/etc/oraInst.loc	The Oracle inventory directory is used by the installer to keep track of all Oracle products installed on the computer. The inventory directory is stored in a file called oraInst.loc.
/etc/ntp.conf	The NTP program is configured using either the /etc/ntp.conf
/etc/sysctl.conf	As the /etc/sysctl.conf file is used to override default kernel parameter values  and therefore only contains a few of the possible parameters.
/home/.bash_history	The bash history is a log file that contains all commands that the user executed on the Linux shell.
/home/.bash_profile	The ~/.bash_profile file is a configuration file for configuring user environments. The users can modify the default settings and add any extra configurations in it.
/home/.bash_logout	The ~/.bash_logout file contains instructions for the logout procedure.
/home/.bash_login	The ~/.bash_login file contains specific settings that are executed when a user logs in to the system.
/var/log/maillog	Stores all logs related to mail servers
/var/log/messages
/var/log/cron	Stores all Crond-related messages (cron jobs), such as when the cron daemon initiated a job, related failure messages, etc.
/etc/shadow
/var/log/kern	Stores Kernel logs and warning data. This log is valuable for troubleshooting custom kernels as well.
/var/log/dmesg	Messages relating to device drivers. The command dmesg can be used to view messages in this file.
/var/log/faillog	Contains information all failed login attempts, which is useful for gaining insights on attempted security breaches, such as those attempting to hack login credentials as well as brute-force attacks.
/var/log/httpd/	A directory containing error_log and access_log files of the Apache httpd daemon
/var/log/boot.log	A repository of all information related to booting and any messages logged during startup.
/var/log/syslog or /var/log/messages	General messages, as well as system-related information. Essentially, this log stores all activity data across the global system.

List of useful directories:-

Directory	Description
/opt	According to the Filesystem Hierarchy Standard, /opt is for "the installation of add-on application software packages
/bin	All binaries needed for the boot process and to run the system in single-user mode, including essential commands such as cd, ls, etc.
/boot	contains files used for system startup including the kernel
/dev	Contains device files for all hardware devices on the system
/etc	Files used by application subsystems such as mail, the Oracle database, etc.
/etc/init.d	Contains various service startup scripts
/etc/profile.d	Holds application setup scripts run by /etc/profile upon login
/etc/rc.d	Contains subdirectories which contain run level specific scripts
/etc/rc.d/init.d	run level initialization scripts
/etc/rc.d/rc.d	Where ‘?’ is a number corresponding to the default run level. Contains symbolic links to scripts which are in /etc/rc.d/init.d. for services to be started and stopped at the indicated run level.
/etc/skel	Holds example dot files used to populate a new user’s home directory.
/etc/X11	Contains subdirectories and configuration files for the X Window system
/home	User home directories
/lib	Some shared library directories, files, and links
/mnt	The typical mount point for the user-mountable devices such as floppy drives and CDROM
/proc	Virtual file system that provides system statistics.  It doesn’t contain real files but provides an interface to runtime system information.
/sbin	Commands used by the super user for system administrative functions
/tmp	A standard repository for temporary files created by applications and users.
/usr	Directory contains subdirectories with source code, programs, libraries, documentation, etc.
/usr/bin	Contains commands available to normal users
/usr/bin/X11	X Window system  binaries
/usr/include	Holds include files used in C programs
/usr/share	Contains shared directories for man files, info files, etc.
/usr/lib	Library files searched by the linker when programs are compiled
/usr/local/bin	Common executable application files local to this system
/usr/sbin	Commands used by the super user for system administrative functions
/var	Administrative files such as log files, locks, spool files, and temporary files used by various utilities
/root	This is the home directory of root user and should never be confused with ‘/‘
/run	This directory is the only clean solution for early-runtime-dir problem.
/sys	Modern Linux distributions include a /sys directory as a virtual filesystem, which stores and allows modification of the devices connected to the system.

Difference between a hot backup and a cold backup in oracle.

In Oracle database we are doing either hot (inconsistent) or cold (consistent) backup. Suppose we have requirement to do the backup in working hours. In this situation we have to plan hot backup. For cold or consistent backup, we have to plan it in night because cold backup requires the database to be offline and therefore unavailable.

Lets began to understand difference between Hot and Cold backup in oracle database. We have aware that database backup is very important and mandatory to recover database. There are several method available for backup oracle database. In Oracle Database, there are two types of backup i.e. Physical and Logical backup. In physical backup we can perform Hot and Cold backup. We always prefer RMAN utility to do the Physical backup.

Database backup can be done in two ways. Either it can be taken off or keeping the database up and running.

Cold Backup:- Cold backup is consistent backup and taken while there is no user activity going on the ERP system. This has also called offline backup, is taken while database shutdown hence application will not available to user access. In this method of backup database does not require being in archive log mode.

The benefit of taking Cold backup is that it is typically easier to administer the backup and recovery process. For cold backup database does not require being in archive log mode.

Hot Backup :- Hot backup or online backup means backing up database while the database still online and available for user access. Hot backups do not require any downtime, but are a little more complex and require archived redo logs.

The benefit of taking a hot backup is that the database is available for use while the backup is occurring and one can recover the database to any point in time.