50 Essential MySQL/MariaDB DBA interview questions answers

MySQL/MariaDB DBA Interview Questions and Answers - Part 3

Q41. How do you monitor and identify slow queries in MySQL?

answer:

You can enable the slow query log in MySQL and set a threshold for query execution time using the long_query_time variable. Slow queries exceeding the threshold will be logged, allowing you to analyze and optimize them.

Q42. How do you enable slow query logging in MySQL?

answer:

To enable slow query logging, you need to set the slow_query_log variable to ON and configure the long_query_time variable to specify the threshold for query execution time.

Q43. How do you enable query caching in MySQL?

answer:

To enable query caching, you need to set the query_cache_type variable to a non-zero value (e.g., ON), and configure the query_cache_size variable to allocate memory for caching query results.

Q44. How do you import data from a CSV file into a MySQL table?

answer:

You can use the LOAD DATA INFILE statement to import data from a CSV file into a MySQL table. The statement allows you to specify the file path, field delimiters, and column mappings.

Q45. What is the purpose of the EXPLAIN statement in MySQL?

answer:

The EXPLAIN statement is used to analyze and optimize the execution plan of a query. It provides information about how MySQL executes a query, the order of table access, the indexes used, and estimated row counts.

Q46. What is the difference between MyISAM and InnoDB?

answer:

MyISAM is a non-transactional storage engine with high read performance and is suitable for read-heavy workloads. InnoDB is a transactional storage engine with support for ACID (Atomicity, Consistency, Isolation, Durability) properties, providing better data integrity and concurrency control.

Q47. How do you perform point-in-time recovery in MySQL?

answer:

To perform point-in-time recovery, you need to restore a backup taken before the desired point in time and then apply binary logs containing changes made after the backup. This process brings the database to a specific state as of the desired point in time.

Q48. What is the purpose of the --skip-lock-tables option in mysqldump?

answer:

The --skip-lock-tables option in mysqldump allows you to perform a logical backup of the database without acquiring read locks on the tables. It enables you to backup a database while it is still being actively used, avoiding interruptions.

Q49. What is the purpose of the REPAIR TABLE statement in MySQL?

answer:

The REPAIR TABLE statement is used to repair corrupted or damaged MyISAM tables. It automatically detects and fixes common table issues, such as incorrect links, missing rows, or index problems.

Q50. What is the purpose of the mysqlbinlog utility?

answer:

The mysqlbinlog utility is used to read and display the contents of binary log files. It allows you to view the logged changes, including SQL statements and events, facilitating analysis, troubleshooting, and replication tasks.

Related MySQL Articles:  Best MySQL DBA Interview Questions and Answers Part 1   MySQL DBA Basic/Advanced Interview Questions - Part 2

30 Top Cloud DBA Database Interview Questions

AWS Cloud DBA/Database Interview Questions - Part 1

Q1. What are the advantages of cloud computing?

answer:

Q2. What is the database storage in AWS cloud? where are the RDS instance datafiles stored?

answer:

Q3. What database metrics will you be monitoring?

answer:

Q4. How will you check database performance issues/bottlenecks in AWS console?

answer:

Q5. Which EC2 instance type/class is the best for databases?

answer:

Q6. How to enforce the security of RDS instances? 

answer:

Q7. How to encrypt data-at-rest in the cloud?

answer:

Q8. How to encrypt data-in-transit / data-in-motion in the cloud?

answer:

Q9. What is the difference/advantages between RDS instance and database running on EC2?

answer:

Q10. What is the difference between "General purpose" and "Provisioned IOPS" storage?

answer:

Q11. What is the advantage of Multi-AZ instance in AWS?

answer:

Q12. How do we export and import data/snapshots from production to non-production environments in different AWS accounts?

answer:

Q13. Please explain the main differences between RDS MySQL and AWS Aurora?

answer:

Q14. What are the main differences between RDS PostgreSQL and Amazon Aurora?

answer:

Q15. How to setup MySQL replication between two Amazon Aurora MySQL clusters?

answer:

Q16. How to setup logical replication between two AWS Aurora Postgres clusters?

answer:

Q17. How can you migrate data from MySQL RDS and Aurora MySQL?

answer:

Q18. How will you update the database parameter value of (Oracle) RDS instance?

answer:

Q19. 

answer:

Q20. 

answer:

Related DBA Articles: PostgreSQL/Aurora Postgres DBA Interview Questions   AWS DBA Database Interview Questions - Part 2

Best 50 MySQL DBA interview questions answers

MySQL/MariaDB DBA Interview Questions and Answers - Part 2

Q21. How a Galera cluster works?

answer:

Q22. What are the different types of replication in MySQL?

answer:

MySQL supports various replication topologies, including master-slave replication, master-master replication, and group replication. Each has its own benefits and use cases.

Q23. What is the purpose of the binary log in MySQL?

answer:

The binary log contains a record of all changes made to the MySQL database. It is used for various purposes, including replication, point-in-time recovery, and auditing.

Q24. How does MySQL Group Replication (GR) work?

answer:

Q25. What is the between MySQL Group Replication and Galera Cluster and Percona XtraDB Cluster?

answer: 

Q26. How to setup MySQL replication from AWS MySQL RDS to Amazon Aurora MySQL?

answer:

Q27. What are the advantages of the Percona XtraDB Cluster (PXC)?

answer:

Q28. What is the use of ProxySQL/HAProxy?

answer:

Q29. How to use the Percona tool kit and what utilities do you know in Percona tool kit?

answer:

Q30. What is the difference between IST (Incremental State Transfer) and SST (Snapshot State Transfer) in PXC (Percona XtraDB Cluster)?

answer:

Q31.  How do you restore a MySQL database from a backup?

answer: 

To restore a MySQL database from a backup, you can use the MySQL command-line client or a graphical tool like phpMyAdmin. The process typically involves creating an empty database, importing the backup file, and ensuring data consistency.

Q32. What are the efficient ways to do (online) schema changes?

answer:

Q33. What is the best way to do DDL changes without locking the table?

answer:

Q34. What are the different storage engines supported by MySQL?

answer: 

MySQL supports various storage engines, including InnoDB, MyISAM, MEMORY, CSV, ARCHIVE, and more. InnoDB is the default storage engine from MySQL version 5.5 onwards.

Q35. What is the purpose of the InnoDB storage engine?

answer: 

InnoDB is designed for high-performance and reliability, offering features such as transactions, row-level locking, foreign key constraints, and crash recovery.

Q36. How do you monitor the performance of a MySQL server?

answer: 

You can monitor a MySQL server using various tools, including MySQL Enterprise Monitor, Performance Schema, EXPLAIN statement, slow query logs, and third-party monitoring solutions.

Q37. What is a deadlock in MySQL?

answer: 

A deadlock occurs when two or more transactions wait indefinitely for each other to release resources, resulting in a state where none of the transactions can proceed. MySQL automatically detects and resolves deadlocks by rolling back one of the involved transactions.

Q38. What is the purpose of the ANALYZE TABLE statement?

answer: 

The ANALYZE TABLE statement is used to analyze and update index statistics for a table. It helps the MySQL optimizer make better decisions when creating query execution plans.

Q39. How do you secure a MySQL server? what are the best practices for securing a MySQL database?

answer: 

• Using strong passwords and avoiding default or easily guessable usernames.
• Limiting network access to the database server.
• Regularly applying security patches and updates.
• Using SSL/TLS for encrypted connections.
• Implementing firewall rules to restrict access.
• Applying the principle of least privilege.

Q40. What is the purpose of the mysqlcheck utility?

answer: 

The mysqlcheck utility is used to check, repair, optimize, and analyze MySQL tables. It performs maintenance tasks to ensure data integrity and improve performance.

Related MySQL Articles:  Top MySQL DBA Basic/advanced Interview Questions and Answers Part 1

Oracle Apps DBA adutilities Interview Questions with answers Part1 [ 2023 ]

25 Top Oracle Apps DBA EBS (E-Business Suite)
AD utilities Interview Questions/FAQs Part1

1.  What are AD Utilities?
Ans: AD Utilities are a group of tools designed to install, upgrade, maintain, and patch a specific set of products contained in a given release of Oracle Applications.

2. What is adctrl?
Ans: Adctrl is one of the adutilities, which is used to check the status of workers and to manage the workers.

3. What is adrelink?
Ans : adrelink will relink the executables with the libraries. Generally we will go for adrelink when some patch delivers some library files, or when executables were corrupted.

4. What is autoconfig?
Ans : Autoconfig is an adutility which is used to main application environment and configuration files.

5. What is adodfcmp utility?
Ans : This utility is used to recreate/repair corrupted database objects from odf (object definition files) files.

6.  What is AutoPatch (or adpatch)?

AutoPatch (adpatch) is a utility that is used to apply individual patches, mini-packs, or maintenance packs to an existing Oracle Applications instance.

7. What is adsplicer?
Ans : Adsplicer is a uitility used to register off cycle products. Adsplice in Oracle Apps is utility to add a new product to EBS.

8. What is adjkey? What files it will create?
Ans : adjkey is an adutility which will create digital signature, which will be used to sign all t" admin? mailto:adsign.txt@APPL_TOP>adsign.txt@APPL_TOP/adminappltop.cer@APPL_TOP/adminidentitydb.obj@applmgr home

9. What are the important AD tables and define their importance?
• AD_APPL_TOPS: It contains information about all APPL_TOP’s using the instance.
• AD_BUGS: It stores information about all different bug fixes applied.
• AD_PATCH_DRIVER_MINIPACKS: Mini_packs information will be stored here.
• AD_PATCH_RUN_BUGS: It stores information about all applied and updated bug fixes.
• AD_FILES: It stores information about files.

10. Difference between ad_bugs table and ad_applied_patches?
• When you apply the patch from adpatch utility ad_applied_patches table will get updated.
• ad_applied_patch doesn’t store the information which was not applied using adpatch.
• Such type of information will be stored in ad_bugs table.
• Also, if all the patches are applied using the adpatch utility, that information is also stored in the ad_bugs_table.

11. What is context file?
Ans : Context file is a central repositary, which stores all application configuration information. The name is like _ .xml

12. What are the menu options in adctrl?
Ans: Check the status of workers, tell manager that worker has quitted, restart a failed worker etc….

13. How to skip a worker and why?
Ans: We can skip a worker using option 8 in adctrl which is hidden. We will go for skipping a worker when we have executed the job which the worker is supposed to do.

14. What are the worker log file names and its location?
Ans : adwork01,adwork02…… and location is $APPL_TOP/admin/SID/log

15. What is the configuration file for adutilities (like adadmin, adconfig etc)?
Ans: adconfig.txt @APPL_TOP/admin

16. How to find the version of a file?
Ans :   1. adident Header 
2. strings -a filename grep Header

17. What are the parameter autoconfig will ask for?
Ans : Context file name and apps password

18. How you will find autoconfig is enabled/not for u r applications?
Ans: 1. Open any env / configuration files, the first few lines will tell u that this files are maintained by autoconfig.2. If contextname.xml file is there in $APPL_TOP/admin

19. How autoconfig will create env and configuration files?
Ans: Autoconfig will go to each and every top template directory take the templates from there and fill the values from xml file and create the required files.

20. In how many phases autoconfig will run?
Ans : Autoconfig will run in 3 phases.
1.INIT – Instantiate the drivers and templates
2.SETUP – Fill the templated with values from xml and create files
3.PROFILE – Update the profile values in database.

21. What is the location of adconfig log file?
Ans : APPL_TOP/admin//log/

22. Is it possible to restore a autoconfig run?
Ans :
Partially. Adconfig will create a restore.sh script at $APPL_TOP/admin//out/. This restore.sh will copy the backed up files before autoconfig run to its original locations. But the profile values updated in the database can’t be restored back.

23. Which adutility do you use regularly and why?


24. What is the pre-requisite to run any ad-utility?

25. What is the script that runs when Compile Apps Schema in adadmin is started?

AD utilities Interview Questions/FAQs Part2

Related Oracle Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions

Oracle Apps DBA Architecture Interview Questions/FAQs

Oracle Apps DBA EBS (E-Business Suite)
Architecture Interview Questions/FAQs

Q1. What are the components of Oracle Applications technology layer?
Ans:
Oracle Applications technology layer comprises the following products:
• Oracle Applications DBA (AD)
• Oracle Applications object library (FND)
• Oracle Applications Utilities (AU)
• Oracle Common Modules (AK)
• Oracle Alert (ALR)
• Oracle Workflow (WF)
• Oracle Applications Framework (FWK)
• Oracle XML Publisher (XML)


*** COMING SOON ***

Related Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions

50 Essential Oracle Apps DBA adutilities Interview Questions Part2

Oracle AppsDBA EBS (E-Business Suite)
AD utilities Interview Questions/FAQs Part2

26. How to run autoconfig in test mode?
Ans :
adchkcfg.sh script at AD_TOP/bin. This script will run autoconfig in test mode and create the difference file which tells us what is going to change, when u actually run autoconfig.

27. How to find autoconfig is enabled or not for database?
Ans:
If we have appsutil directory under RDBMS_ORACLE_HOME

28.  What is AD Administration (or adadmin)?
Ans: AD Administration (adadmin) performs maintenance tasks on an installed Oracle Applications system to ensure that it runs smoothly.  The tasks performed with this utility fall into two categories: database and file system.

29. What is for “validating apps schema” option in adadmin?
Ans: It will check for the corrupted objects in apps schema

30. What is “compile apps schema” option in adadmin?
Ans : It will compile the invalid database objects.

31. How to find MRC is enabled or not?
Ans: In adadmin if covert to MRC options is there, then MRC is not enabled, if maintain MRC options is there, then MRC is enabled.

32. How to find Multi-Org is enabled or not?
Ans : In adadmin if covert to Multi org option is there, then Multi-org is not enabled. If maintain multi-org options is there, then Multi-org is enabled.

33. What is the configuration file for adutilities (like adadmin,adconfig etc)?
Ans: adconfig.txt @APPL_TOP/admin

34. Why do you need system password for adadmin?

35. Where is adadmin log file located?

36. What is the other script by which u can start apache other than adapcctl.sh?
Ans : apachectl @IAS_ORACLE_HOME/Apache/bin

37. How to find adconfig is enabled for oracle operating system user/database?
Ans : If appsutil directory is there in RDBMS_ORACLE_HOME

38. What is licence manager?
Ans : Licence manager(adlicmgr) utility is used to licence/unlicence , enable new languages,enbale country specific functionality.

39. What is the other script by which u can start apache other than adapcctl.sh?
Ans : apachectl @IAS_ORACLE_HOME/Apache/bin

40. What are the post installation task?
Ans : Running adjkey –initialize and then runnning adadmin to regenerate jar files.

41. What are the real time problems you have encountered and how you troubleshooted that?
Ans: 1. Concurrent Program is erroring out with snapshot too old error. To resolve this we have added space to temp tablespace.2. Concurrent Program is erroing out with unable to extent a perticular tablespace by so and so extents. To resolve this we have added on more data file to that tablespace.3. When we are trying to start apache with adapcctl.sh script after a autoconfig run, its saying that “node id is not matching with the application server id”. To resolve this we have updated the server id column in fnd_nodes table with the server id value in dbc file.

42. How to skip copy portion while applying a patch?
Ans : Adpatch options=nocopyportion

43. How to merge patches and what type of patches can be merged?
Ans : admrgpch. We can merge any kind of application patches, if any of the patch contain a u-driver then merged patch will contain u_merged.drv otherwise c_merged.drv, d_merged.drv and g_merged.drv

44. What are the prompts you get when you invoke adadmin?

45. What  does adadmin do when it runs Re-create grants and synonyms for apps schema option?

46. How do you invoke adadmin session in non-interactive mode?

47. On a multi-node architecture, on which node do you invoke ad-utilities?(adadmin,apatch and adconfig)

48. Do you need Oracle EBS database to be up in order to run ad-utilities? Why?

49. What do you do when an adadmin session fails or errors out?

50. Can we invoke more than one adadmin session simultaneously? If so when? If not Why?



AD utilities Interview Questions/FAQs Part1

Related Oracle Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions

30 Best Oracle Apps DBA RDBMS Interview Questions/FAQs Part2

Oracle Apps DBA EBS (E-Business Suite)
RDBMS Interview Questions/FAQs Part2

21. How to enable trace for other session?
Answer : 
exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)
To enable trace for sql session
SQL> exec sys.dbms_system.set_sql_trace_in_session(66,2321,true);

To disable trace
SQL> exec sys.dbms_system.set_sql_trace_in_session(66,2321,false);

22.What is the location of init.ora ?
Answer : 
$RDBMS_ORACLE_HOME/dbs

23. What is that trace files contains and the utility used to read them?
Answer : 
Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. TKPROF utility is used to convert trace file into readable format.

24. What is the syntax for tkprof?
Ans: tkprof explain=apps/ sys=no

25. What is a view and how do you recreate a view which becomes invalid when you recreate a patch?
Answer :
• View is used for accessing a particular portion of a table.
• A view is also called as virtual table.
• For recreating the view you can search for the view name in the SQL directory.
• We can recreate the views running scripts located in the Product top/patch/115/sql directory.
• Most views are recreated using sql script.

26. You need to run autoconfig on Apps and DB tiers. Which tier will you run first?

27. Can you run auto config in DB tier while the database is up? Why?

28. What is a customization? How do you know there are customizations in your system?

29. How to preserve customizations?

30. Do you need to bring down your database while running auto config? Why or why not?

31. How to find Database version?
Answer :
SQL> select * from v$version;

32. How to find opatch Version?
Answer :
opatch is utility to apply database patch, to find opatch version execute ”$ORACLE_HOME/OPatch/opatch version”
You can check OPatch -lsinventory

Oracle AppsDBA (E-Business Suite) RDBMS Interview Questions/FAQs Part1

Related Oracle Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions

Oracle AppsDBA Patching Interview Questions for experienced Part2

Top 50+ Oracle Apps DBA EBS (E-Business Suite)
Patching Interview Questions/FAQs Part2

21. List out the Log Files created on running patch?
Ans : 
Running the patch creates the following log files
• Adpatch.log: Information about the patch run will be stored here.
• Adpatch.lgi: It contains information which has been discarded by adpatch.log. For example, the files which has not been copied by the adpatch.
• Adrelink.log: Relinking information performed by patch will be stored here.
• Adworkxx.log: Workers log details will be stored here.

22. Is it possible to revert the patch application? Explain in detail how?
Ans : 
• Yes, it’s possible to revert the patching but not 100% sure.
• In test environment it’s always recommended to take the full backup of the database, apply the patch and revert from backup.
• Adpatch is responsible for copying the files from the patch.
• From the patch log, drop the database objects created by the patch.
• Similarly forms in the G driver should be replaced from the backup taken by adpatch and should be manually generated.

23. What things you do to reduce patch timing?
Ans : 
• Admrgpch utility is used for merging all the patches.
• Various adpatch options like nocompiledb or nocompilejsp are used for reducing patch timing.
• By Using defaults file.
• By using, staged APPL_TOP for upgradation.
• By increasing the batch size, this might result into negative results.

24. What is copy driver (C driver) and what does it do?
Ans : 
• All the files in the patch are copied to APPL_TOP by C driver.
• It extracts all the appropriate files from c library.
• Oracle applications products are relinked by C driver.
• Regenerates the JAR files and compiles the Java server pages (JSP) files.
• Compares the files in the patch with the files in the $APPL_TOP.
• If all the files in the patch are of higher version, adpatch copies all the files from patch to $APPL_TOP.

25. What is database driver (D driver) and what does it do?
Ans : 
• It contains all the commands to change the database object.
• All the scripts copied by c driver are applied to the database.
• It makes a list of all the invalid objects that are there in the database.
• Runs SQL scripts which make changes to the database objects.
• Compiles all the invalid objects that are there in the database.

26. What is generate driver (G driver) and what does it do?
Ans : 
• Generate driver is named as g<patch number>.drv
• It regenerates all forms, reports and pl/sql libraries that have been affected by the patch.

27. What is unified driver (U driver) and what does it do?
Ans : 
• Unified driver is a combination of C,D, & G drivers.
• U driver is also named as u<patch_number>.drv
• It requires only a single execution of AutoPatch.

28. When I am applying a patch, can I open another session and run adadmin?
Ans:
Yes, unless you are running a process where workers are involved.

29. When I am applying a patch, can I open another session in another node and run adpatch?
Ans:
No

30. How often do you patch?
Ans: 
Usually for non-production the patching request comes around weekly 4-6 and the same patches will be applied to Production in the outage or maintenance window.
Production has weekly maintenance window (E.g. Sat 6PM to 9PM) where all the changes (patches) will applied on production.

31. How to find out if any patch except localization patch is applied or not, if applied, that what all drivers it contain and time of it's application?
Ans : 
select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

32. How to know that if the patch is applied successfully, applied on both node or not?
Ans : 
start time of patch application and end time of patch application, patch top location , session id ... patch run id
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3; 

33. How to get information related to how many times driver file is applied for bugs?
Ans : 
select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = '';

34. How to find what is being done by the patch?
Ans : 
select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run ID",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE;

35. How to find merged patch information from database in Oracle Applications?
Ans : 
select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );

36. How to know, what all has been done during application of PATCH?
Ans : 
Select J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G, AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and 
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID 
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and 
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and 
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR, 
D.FILENAME, E.ACTION_CODE;

37. How to find out the patch level of minipack?
Ans : 
Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';
Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex. 
AD - for Applications DBA , GL - for General Ledger, PO - Purchase Order

38. What is the significance of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table?
Ans: 
FND_INSTALL_PROCESSES table will store the worker information like what job is assigned to which worker and its status. AD_DEFERRED_JOBS will come into picture when some worker is failed, it will be moved to AD_DEFERRED_JOBS table, from where again adpatch will take that job and try to resign, after doing this 3 times if still that worker is failing, then adpatch will stop patching and throw the error that particular worker has failed. We need to troubleshoot and restart the worker.

39. If it is a multinode installation which driver we need to apply on which node?
Ans: 
c,d,g on concurrent node and c, g on web node. If it is u-driver we need to apply on all nodes.

40. While applying an application patch is that necessary that database and listener should be up?
Ans: 
Yes. Because adpatch will connect to database and update so many tables etc…

41. While applying a patch if that patch is failing because of a pre-req then how you will apply that pre-req patch and resume with the current patch?
Ans: 
We need to take the backup of FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables and restart directory at APPL_TOP/admin/SID and then use adctrl to quit all the workers. Then apply the pre-req patch, after that rename restart directory to its original name and create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS tables from the backup tables. Start adpatch session and take the options want to continue the previous session.

42. How adpatch knows what are the pre-req’s for the patch which it is applying?
Ans: 
With every patch a file called b.ldt file will be delivered which contain the pre-req information. adpatch load this into database using FNDLOAD and check, whether those pre-req patches were applied or not.

43. What c-driver will do?
Ans:
C-drive copies the files from patch unzipped directory to required location in the application file system. Before copying it will check the file version of the existing file at the file system with the file version of the file in the patch. If the patch file version is higher than what it is at file system level then only c-driver will copy that files.

44. How adpatch will know the file versions of the patch delivered files?
Ans:
With each patch a file with name f.ldt is delivered, which contains the file versions of the files delivered with the patch. adpatch will use this file to compare the file versions of files its delivering with the file on file system.

45. What is the adpatch log file location?
Ans : 
$APPL_TOP/admin/SID/log

46. How you will know what are the files the patch is going to change just my unzipping the patch?
Ans:
When we unzip a patch it will keep all the files related to a particular product under that directory inside u r patch directory for example if the patch delivering files related to FND product then it will create a sub directory under the patch directory with the name FND in which it will put all related files to that product

47. What is the significance of backup directory under patch directory?
Ans:
When we apply a patch it will keep the copy of the files which it’s going to change in file system.

48. What are the different modes you can run your adpatch?
Ans :
1.Interactive – default mode
2.Non interactive – Use defaults files to store prompt values (adpatch defaultsfile= interactive=no)
3. Test – Without actually applying a patch just to check what doing. (adpatch apply=no)
4. Pre-install – (adpatch preinstall=y)
This mode will be useful to decrease upgrade downtime as its applies bus fixes without running SQL, EXEC and generate portion of patch.

49. When a patch delivers java files what extra file you will get when you unzip the patch, other than driver and readme files?
Ans : j.zip52.

50. What is apps.zip/appsbrog2.zip file?
Ans : apps.zip/appsbrog2.zip is the patchable archive of all java class files required for oracle application. Apps.zip was used to old application version, but from 11.5.8 onwards its appsbrog2.zip

50. What is the location of apps.zip/appsbrog2.zip?
Ans : AU_TOP/java and JAVA_TOP

51. How to skip copy portion while applying a patch?
Ans : Adpatch options=nocopyportion

52. How to merge patches and what type of patches can be merged?
Ans : admrgpch. We can merge any kind of application patches, if any of the patch contain a u-driver then merged patch will contain u_merged.drv otherwise c_merged.drv, d_merged.drv and g_merged.drv

53. How to find opatch is enabled or not for your database?
Ans : If Opatch directory exists under RDBMS_ORACLE_HOME.

54. How to find out what are the rdbms patches applied to an oracle home?
Ans :
1. opatch lsinventory
2. $RDBMS_ORACLE_HOME/.patch_storage directory contains the directories with the rdbms patch numbers, which are applied to this oracle home.

55. Is it necessary to enable maintenance mode while applying a patch?
Ans : 
We can even apply a patch without enabling maintenance mode with the following option
adpatch options=hotpatch

56. While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not registered, what you will do, and how you will apply the patch?
Ans: 
We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory. If still it won’t work we will apply the patch with the following command:
opatch apply no_inventory

57. What are the patch errors, you have encountered?
Ans :
1) Patch fails with the error, unable to generate particular form, do u want to continue. We continue patching by saying “yes”, and then we manually regenerate the form using f60gen utility.
2) Unable to generate jar files under JAVA_TOP AutoPatch error: Failed to generate the product JAR files Solution: Run adjkey -initialize -----------to creat identitydb.obj file which will be used by adjava to sign jar files.

Related Oracle Articles:  Oracle DBA Interview Questions/FAQs  Oracle RAC Interview Questions  Oracle Exadata Interview Questions

Top 50 Oracle Apps DBA ad utilities Interview Questions answers Part 3

Oracle AppsDBA EBS (E-Business Suite)
AD utilities Interview Questions/FAQs Part3

51. Where are the executables located for ad-utilities?

52. adadmin is not accepting apps password even if it is correct password. Why and what is your course of action?

53. What does adchkcfg.sh do ?

54. Where are the output files created when we run adchkcfg.sh?

55. Where is adcfgclone.pl located ? Where is its log file created?

56.  What is ad-controller?

57. A worker hangs , What is your course of action?

58. Which table is used by AutoPatch as a staging area for the job information in parallel mode and needs to be dropped to start a new autopatch session if the earlier session failed?
Answer :
FND_INSTALL_PROCESSES

Related Oracle Apps Articles:  Oracle AppsDBA (E-Business Suite) AD utilities Interview Questions/FAQs Part2