Introduction - Oracle Apps DBA

What is ERP?
ERP stands for Enterprise resource planning software. ERP combines all Legacy systems of different departments in a company together into a single, integrated software program that runs off a single database so that the various departments can more easily share information and communicate with each other.

Ex: Oracle E -Business Suite, SAP. 

Who is an Oracle Apps DBA?
In simple Terms, He/She is a person who manages Oracle E-Business Suite Applications .Apps DBA Must have knowledge of Oracle Application Components.
Like..

DB Tier:
Oracle Database 9i/10g/11g/12c/19c DBA.

Application Tier:
Oracle Techstack Components (Apache,Forms,Reports,CMs),Weblogic.

Desktop Tier:
Sun JRE

+Basic Unix Skills : Must be able to Easily navigate through application file systems .

Based on the Organization ,you re expected to know all the above or only some .i e , a Big Organizations might have specialized  people for DBA,Apps & Desktops separately and in a small Organizations ,you might be expected to knw all three DB,Apps  and Desktop support.

Common Tasks of APPS DBA :

Installation : Single node and Multi node installation .

Configure:  License Manager ,adsplice,autoconfig,Contex editor ,Converting to       MRC  and Multi -Org etc.

Maintaining : Adadmin ,adrelink,analyzing the database for CBO,serve process using  adstrtal,adcmctl,..etc.

Patching,upgrading and Cloning :
Upgrding applications and Database ,interactive patching ,non -interactive patching,Merging Patches,Troubleshooting and restarting worker process and cloning using rapid clone utility.

Adop patching ,Migrations ,Deployments ,RMAN backups ,RMAN cloning ,Dataguard ,DR recovery .

Sysadmin Tasks : Creating users ,Assigning Responsibilities ,Registering Forms &  Concurrent Programs ,Long running requests .

Based on the state of the Application projects is in ,we can call Apps DBA as 

   1. Implementations DBA 
   2. Production DBA 
​
Implementation DBA :
Apps DBA working on implementation phase customer ,i e ,Customer did not start using the Apps instance for daily use. They are in process of customizing the apps to fit to their requirement and testing of different modules might be in progress .Most customers don't switch to ERP/Oracle Apps in a single shot.., They go live in different phases.

​In each phase they implement one or couple of modules and the corresponding department will start using ERP from the point of time .

​Usual length of an implementation project is 2 years .

Daily Activities:

1.Installation and Preparing of CRP,QA,UAT and GOLD instances .

2. Registering Menus, Responsibilities ,Forms, Concurrent Managers .

3. Tuning of Oracle DB and Different components of Oracle Applications thechstack for better performance based on QA/UAT results .

4. Follow -up with Oracle Product Support through Metalink for any issues that            functional consultants raise which requires file system level log files etc.

5. Cloning of Oracle Apps Instance .

6. Configuration and performing daily ,Monthly backups .

7. Applying Apps Patches using adpatch ,adop ,registering and licensing products  that customer wats to use .

Production DBA:

Apps DBA maintaining an Apps instance which is already in daily user by Customer.
Main goal will be to take care of performance of the system 

1. Cloning of PROD instance to TEST,DEV,UAT to reproduce and troubleshoot an error  that cane up in PROD and for users testing .

2. Applying oracle Security patches (CPU-Cumulative Patch Update) which are  released quarterly.

3. Applying Bug fix patches which are requested by users /customers .

4. Troubleshoot performance issues that might have come up with data growth. 

5. Cleaning up of log files of different processes like Apache, Concurrent Managers.

6. Making sure that scheduled backup jobs of PROD instance are going fine.

7.  Monitoring the Concurrent Managers queue and DB level free space in each Tablespace. 

8. Incase Standby DB is configured ,Need to Monitor the archive log  shipping between the sites also. 

RMAN Block Change Tracking Overview

     RMAN Block Change Tracking Overview During incremental backup, RMAN checks every data block in all datafiles of the database and compares its scn number with the SCN value that is at the incremental 0 backup.  If the first value is greater than the second, it means that the data block has been changed after the last backup and needs to be backed up, so RMAN writes it to the backup file.  This procedure is done for every data block in the database.  Prior to 10gR2, RMAN was backing up the data block in case it was written once by any Oracle process even though it was empty.  But in 10gR2, RMAN backs up only used extents. 

       So as RMAN checks all data blocks and writes down only changed ones during incremental backup, it takes more time for the checking procedure. 

Starting from Oracle 10gR1, the new feature called Block Change Tracking was presented, which uses a new process called Change Tracking Writer (CTWR). This tracks the changes made to the data blocks as redo is generated and stores their addresses to the special tracking file.  When incremental backups are done, RMAN uses the tracking file and backs up only those data blocks that are written in this file, thus making incremental backups much faster, and it does not scan the unchanged data blocks.  This feature is disabled by default.  Only one tracking file is used for one database.

To determine if block change tracking is enabled, check the STATUS and FILENAME columns in the V$BLOCK_CHANGE_TRACKING view, using the following statement from the SQL or RMAN prompt:

SQL>  SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;

STATUS     FILENAME
---------- ------------------------------
DISABLED

SQL>

To enable block change tracking:

1. Determine the current location of the database data files by submitting the following query:

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/ua1001/DEV/app/oradev/oradata/DEV/DEV/system01.dbf
/ua1001/DEV/app/oradev/oradata/DEV/DEV/sysaux01.dbf
/ua1001/DEV/app/oradev/oradata/DEV/DEV/undotbs01.dbf
/ua1001/DEV/app/oradev/oradata/DEV/DEV/users01.dbf

SQL>

2.In this example, the query results show that data files are stored in the file system in the  directory /ua1001/DEV/app/oradev/oradata/.

Data files might also be stored in an Oracle Automatic Storage Management disk group.
​
3.Set the DB_CREATE_FILE_DEST initialization parameter to specify the location where new  database files, including the block change tracking file, must be stored.

You can specify the same directory shown in query results from the previous step, with the final portion of the path the database SID—stripped, as shown in the following example, or designate a new directory. Any directory that you specify must have the write permission for the Oracle software owner.
​
​The following command specifies that new database files must be stored in the directory /ua1001/DEV/app/oradev/oradata/.
​
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/ua1001/DEV/app/oradev/oradata/';

System altered.


Enable block change tracking for the database using the following command:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

We can also specify file name using below command .

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE
'/ua1001/DEV/app/oradev/oradata/DEV/changetracking/block_change_TESTDB.log';

If there is already a file named block_change_TESTDB.log and it needs to be overwritten, add a REUSE clause as follows:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE
'/ua1001/DEV/app/oradev/oradata/DEV/changetracking/block_change_TESTDB.log'
REUSE;

​And the new CTWR process will be created to track the changes:

[DEV|oradev@nadhans DEV]$ ps -ef | grep ctwr
oradev    15306      1  0 16:32 ?        00:00:00 ora_ctwr_DEV
oradev    15885   3798  0 16:41 pts/1    00:00:00 grep --color=auto ctwr

And you can see Physical file here .

[DEV|oradev@nadhans changetracking]$ ls -ltr
total 11348
-rw-r----- 1 oradev dba 11600384 Aug 19 16:49 o1_mf_jkwgl317_.chg
[DEV|oradev@nadhans changetracking]$ pwd
/ua1001/DEV/app/oradev/oradata/DEV/changetracking
[DEV|oradev@nadhans changetracking]$

 

 

BI Discoverer /Plus Unable To Connect To Oracle Applications Database: Invalid Username/Password

 

 ERROR:

Solution:

1. Telnet to the Application tier server from the Application tier operating system user.

2. Source application tier environment file <APPL_TOP>/APPS<SID>_<hostname>.env.

3. Run the following command:

java oracle.apps.fnd.security.AdminAppServer apps/<pwd> AUTHENTICATION OFF DBC=$FND_SECURE/<SID>.dbc