MADHAN APPS DBA

Best Practices for Performance for Concurrent Managers in E-Business Suite ============================================================== This Document contains 5 topics 1. Generic Tips 2. Transaction Manager (TM). 3. Parallel Concurrent Processing (PCP) Environment. 4. Tuning Output Post Processor (OPP). 5. Concurrent Processing Server Tuning. Generic Tips 1) Sleep Seconds - is the number of seconds your Concurrent manager waits between checking the list of pending concurrent requests (concurrent requests waiting to be started). A manager only sleeps if there are no runnable jobs in the queue. Tip: During peak time, when the number of requests submitted is expected to be high, Set the sleep time to a reasonable wait time(e.g. 30 seconds) dependent on the average run time and to prevent backlog. Otherwise set the sleep time to a high number (e.g. 2 minutes). This avoids constant polls to check for new requests. 2) Increase the cache size (number of requests cached)

What is the main difference between Lock , Block and Deadlock in oracle database: Answer : The Meaning of lock is : ======================== Lock is a done by database when any connection access a same piece of data concurrently. One connection need to access Piece of data . The Meaning of Block : ====================== It occurs when two connections need access to same piece of data concurrently and the meanwhile another is blocked because at a particular time, only one connection can have access. SQL knows that once the blocking process finishes the resource will be available and so the blocked process will wait (until it times out), but it won’t be killed. The Meaning of Deadlock : ========================= Deadlock occurs when one connection is blocked and waiting for a second to complete its work, and this situation is again with another process as it waiting for first connection to release the lock. Hence deadlock occurs. Example : i have 2 processes. P1 &

Useful Metalink Doc id for Oracle Apps and Core DBA : ============================================= R11i / R12: Component Version In Oracle Applications (Doc ID 1327288.1) How to Replace Oracle Logo with Company Logo on Applications 11i Sign-On Screen (Doc ID 119319.1) How To Set Concurrent Program To Run Exclusively From A Custom Manager (Doc ID 2268941.1) How to Create a Custom Concurrent Manager (Doc ID 170524.1) Cloning Oracle E-Business Suite Release 12.2 with Rapid Clone (Doc ID 1383621.1) How to Recover from a Lost or Deleted Datafile with Different Scenarios (Doc ID 198640.1) How To Change Log Archive Destination While the Database Is Open When the Archive Destination Is Full (Doc ID 160446.1) 12.2 E-Business Suite Applications DBA Steps To Create, Update or Rebuild The Central Inventory For Oracle Applications (Doc ID 1588609.1) E-Business Suite - ADOP Basic Usage Training Videos [Video] (Doc ID 2103131.1) How to Change IP Address in an Oracle Applicatio

Gather Schema Statistics R12.1/R12.2: ==================================================== What is Gather Schema Statistics? Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. The cost-based optimization (CBO) uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan. Why Run the Gather Schema Statistics? When Data is update, insert, delete in table of user end Like (Technical User ,Finance User ,Ect) It become necessary to run the Gather Schema Statistics It’s recommended to Run GSS in Weekly Once or twice. How to run Gather Schema Statistics? Login application with sysadmin user Submit Request Window Navigate to: Concurrent > Requests Enter the parameters This can be run for specific schemas by specifying the schema name or enter ‘ALL’ to gather statistics for every schema in the database Submit the Gather

Oracle E-Business Suite Release 12.2.8 Readme (Doc ID 2393248.1): ======================================================== Section 1: Preparation Section 2: Obsolete Products in Release 12.2.8 Section 3: Upgrade Database to 11.2.0.4 or higher Section 4: Apply Required Database Patches and Update Database Initialization Parameters 4.1 Apply Required Database Patches 4.2 Set Database Parameter (Conditional) Section 5: Apply Consolidated Seed Table Upgrade Patch (Required) Section 6: Apply the Latest AD and TXK Delta Release Update Packs Section 7: Perform Pre-Update Steps (Conditional) Section 8: Apply Oracle E-Business Suite 12.2.8 Release Update Pack 8.1 Path A — Upgrade and New Installation Customers upgrading to Oracle E-Business Suite 12.2.8 Release Update Pack 8.2 Path B — Existing Customers (Release 12.2.2, 12.2.3, 12.2.4, 12.2.5, 12.2.6 or 12.2.7) upgrading to Oracle E-Business Suite 12.2.8 Release Update Pack Section 9: Post-Update Steps Section 10: Apply Additional Critical

Oracle Database Exam Codes : -------------------------------------------------------------------------- Oracle Database 11gR2 : Oracle Database 11g: Administration II | 1Z0-053 Upgrade Oracle9i/10g to Oracle Database 11g OCP | 1Z0-034 Oracle Database 11g: New Features for Administrators | 1Z0-050 Oracle Database 11g: Administration I | 1Z0-052 Oracle Database 11g: Performance Tuning | 1Z0-054 Oracle Database 11g: Program with PL/SQL | 1Z0-144 Oracle Database 12cR1 : ================== Upgrade to Oracle Database 12c | 1Z0-060 Oracle Database 12c: SQL Fundamentals (retiring November 30, 2019) | 1Z0-061 Oracle Database 12c Administration | 1Z0-062 Oracle Database 12c: Advanced Administration | 1Z0-063 Oracle Database 12c: Performance Management and Tuning | 1Z0-064 Oracle Database 12c: Data Guard Administration | 1Z0-066 Upgrade Oracle9i/10g/11g OCA to Oracle Database 12c OCP | 1Z0-067 Oracle Database 12c: RAC and Grid Infrastructure Administration | 1Z0-068 Oracle Database 12c

10g/11g database architecture

RCONFIG Overview : ============================== RCONFIG is a non interactive command line utility for converting a Single-Instance database to RAC database.It is installed by default as a executable file under $GRID_HOME/bin and $ORACLE_HOME/bin. It takes XML file as input and process the conversion procedure. Benefits of using RCONFIG : --------------------------------------- It’s very light and easy to use utility for conversion of Single Instance database to RAC database . We do not need to do additional configuration , as it comes by default. It also provide testing capabilities in advance without actually converting it into RAC. It is fully automated conversion process. It has ability to migrate non-ASM DB to ASM file-system It creates RAC DB instance on all the specified nodes. It registers CRS resource to the cluster. It startup RAC database instances on the specified nodes. Disadvantage of using RCONFIG : ---------------------------------------------- Database do

SQL Server DBA Duties DAILY : ============================================ Action: Check Network Connectivity Reason: To check that hardware & server is up. To check that IP address & name have not been changed. Gives early warning if server fails. Checks IP address & name resolution (sometimes a problem with wins, dns, lmhosts). Method: 1. Ping sql servers every 15 mins with IP Sentry. 2. Use batch file to ping servers. 3. Use a server monitoring tool. Action: Check SQL services Reason: To check that SQL server is available. To check MSSQLserver & SQLexecutive/agent, DTS services are running. To check that we can connect. Method: SEM: Green lights if SEM, or connect to each server by clicking on the ‘+’ for each server & open sql executive. Action: Check Scheduled Tasks/ Jobs Reason: Backups, DBCC checks, etc. are run overnight as scheduled tasks. Method: SEM: Highlight server, servers | scheduled tasks Action: Check dba_tools..scheduledtasklog Reason:

OLSNODES provides information related RAC nodes: ================================================ 1. List of nodes in the cluster olsnodes prod54-1 prod55-1 2.Nodes with node number olsnodes -n prod54-1 1 prod55-1 2 3.Node with vip olsnodes -i prod54-1 prod54-1-vip prod55-1 prod55-1-vip 4. Nodes with status olsnodes -s -t prod54-1 Active Unpinned prod55-1 Active Unpinned 5.Leaf or Hub olsnodes -a prod54-1 Hub prod55-1 Hub 6.Getting private ip details of the local node olsnodes -l -p prod54-1 162.168.1.1,162.168.2.1 7. Get cluster name olsnodes -c prodd-cluster

Troubleshooting EBS login page issue in R12.2: ------------------------------------------------------------------------------------------------------------------- 1. If the login page doesn't comes up then we have to focus on the varios logs present under $LOG_HOME directory. In case of EBS 12.2(as it is the latest release), 1) OHS (apache) failure If OHS is not running or not responding, one would see a message as below. If OHS is not running then there will not be any messages in any EBS log file for this request. Firefox: “The connection was reset” Steps to take Check OHS has started OK adapcctl.sh status adapcctl.sh stop adapcctl.sh start 2 OACore JVM process not available If the OACore JVM is not running or not reachable, then one will likely see the following message in the browser: Failure of server APACHE bridge: No backend server available for connection: timed out after 10 seconds or idempotent set to OFF or method not idempotent. There could be two reas

Below are the ASM new features of Oracle 12c version: ========================================== 1. Connecting to ASM instance of remote node: Now we can connect to asmcmd prompt(asm instance) of remote node from local node. SYNTAX – asmcmd –inst < REMOTE ASM INSTANCE NAME> asmcmd --inst +ASM2 2.Move ASM password file using pwmove: We can move the asm pwfile from one diskgroup to another diskgroup online using pwmove command. Check the asm pwfile location $ crsctl stat res ora.asm -p|grep PWFILE PWFILE=+MGMTDB/orapwASM $ crsctl check crs CRS-4638: Oracle High Availability Services is online CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online Connect to asmcmd and run pwmove command $echo $ORACLE_HOME $/crsapp/app/oracle/product/grid12c echo $ORACLE_SID +ASM1 $ asmcmd ASMCMD> pwmove --asm +MGMTDB/orapwASM +B2BWMDB/orapwASM moving +MGMTDB/orapwASM -> +B2BWMDB/orapwASM Ch

What is OC4J and components: ====================================== -Through OC4J you can execute Servlets, Java Server Pages (JSP), Enterprise Java Beans (EJB) -OC4J replaces the older JServ implementation for running servlets on the web server -Oracle Application Server 10gR3 (10.1.3) is the latest production version -OC4J is based on J2EE standards:Specific directory structure,File requirements (content & naming conventions),XML file definition -OC4J instances run in JVM’s and communicate through mod_oc4j / Apache -R 12 creates 3 OC4J instances: Oacore: runs OA Framework-based applications Forms: runs Forms-base applications OAFM: runs web services, mapviewer, ascontrol Number of OC4J instances for each group will be determined by corresponding  nprocs context variable  ( s_oacore_nprocs, s_forms_nprocs/s_frmsrv_nprocs, s_oafm_nprocs). -Servlets deployed to OC4J server adhere to J2EE specification ===============================================================

EBS Application login Process in R12.1: ============================================= What happens when you login to Apps? 1 . Your login gets authenticated against a table named fnd_user for your username and password 2. DB connection is established using APPLSYSPUB user. This user “APPLSYSPUB” is having access to  fnd_user view and a small set of other public tables  needed to establish the initial connection. 3. Once DB connection is established FND_USER table is used to get ENCRYPTED_FOUNDATION_PASSWORD.  Using GUEST/ORACLE password combination and ENCRYPTED_FOUNDATION_PASSWORD,  we get Apps schema password 4.  The Application user password compared to the application user password entered by user in  login screen. If both passwords match then user is allowed to get into self-service 5. A list of responsibilities assigned to that user is shown.  The user picks one of the  responsibilities listed. 6. Each responsibility has a data group and application defined f

Diff Between DBTIER,DBTECHSTACK AND DB CONFIG in R12.1/R12.2: ======================================================= perl adcfgclone.pl dbTier ==============================  It will configure the ORACLE_HOME on the target database tier node and  recreate   the controlfiles.  This is specially used in case of standby database and/or hot backups.   It will take care of all the steps.   perl adcfgclone.pl dbTechStack  =============================== It will configure the ORACLE_HOME on the target database tier node only.  Relink the oracle home. The below steps has to be performed manually 1. Create the Target Database control files. 2. Start the Target System Database in open mode 3. Run the library update script against the Database cd $RDBMS_ORACLE_HOME/appsutil/install/[CONTEXT NAME] sqlplus "/ as sysdba" @adupdlib.sql [libext]  Where [libext] should be set to 'sl' for HP-UX, 'so' for any other UNIX platform,  or 'dll' for W

ADOP PHASES IN R12.2  / R12.2 PATCHING : =================================== Prepare phase ============== Gets patching filesystem ready to apply a patch. Checks that ETCC has been run against the database Oracle Home and that all required  patches are applied. synchronize filesystems - If any configuration changes have been made, this will  cause the prepare phase  to initiate a fs_clone of the current run filesystem to the patch filesystem. run autoconfig if necessary. check tablespaces for sufficient free space before starting. check APPS_FNDFS listener to make sure concurrent requests will launch. launch concurrent request - "Online Patching In Progress" (ADZDPATCH). synchronize snapshots between run and patch filesystems. Apply:($ adop phase=apply analytics=yes)  ======  merging patches - ADOP determines correct options automatically. patch repository location. ADOP log directories. how to run ADOP unattended by passing the required passwords into

DBA Performence Tuning Tips: ======================== Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial  execution of kernel code.  The LRU (least recently used) latches are used when seeking, adding,  or removing a buffer from the buffer cache, an action that can only be done by one process at a   time Latches occur and removed automatically internally and fully managed by Oracle database. i.e.,   A latch is a low-level internal lock used by Oracle to protect memory structures Column names should be provided instead of * in SELECT statements. Joins should be used in the place of sub-queries. EXISTS should be used instead of IN to verify the existence of data. UNION ALL should be used in the place of UNION. HAVING should be used only for filtering the resulted rows from the SQL query. The column CLUSTER_WAIT_TIME in V$SQLAREA represents the wait time incurred by individual SQL  statements for global cache events and will id

RMAN Commands  Part-2 =================== RMAN: ===== ·Obsolete means “not needed,” whereas expired means “not found.” ·A backup becomes obsolete based on retention policy, that it is not needed for recovery. ·A Backup becomes expired only when RMAN perform CROSSCHECK and can’t find the file.  (Assume file is deleted by OS). Difference Between OBSOLETE AND EXPIRED Backup: ======================================================= RMAN considers backups of datafiles and control files as obsolete, that is, no longer needed for  recovery, according to criteria that we specify in the CONFIGURE command.  We can then use the REPORT OBSOLETE command to view obsolete files  and DELETE OBSOLETE to delete them . For ex  :  we set our retention policy to redundancy 2. this means we always want to keep at  least 2 backup,  after 2 backup,  if we take an another backup oldest one become obsolete because there is 3 backup and we want   to keep  2.   if our flash recovery area