Datapump Interview Question - Export/Import

Datapump Interview Question  - Export/Import

Datapump Export/Import Modes:

1.Full Export Mode
expdp scott/tiger directory=data_pump_dir dumpfile=qadar.dmp FULL=y
It EXP_FULL_DATABASE role required.

2.Schema Mode
expdp scott/tiger directory=data_pump_dir dumpfile=qadar.dmp schemas=qadar, shoaib, rumaysa
It EXP_FULL_DATABASE role required.

3.Table Mode
expdp scott/tiger directory=data_pump_dir dumpfile=qadar.dmp tables=test1, test2
It EXP_FULL_DATABASE role required.

4.Tablespace Mode
expdp scott/tiger directory=data_pump_dir dumpfile=qadar.dmp tablespaces=users, apps_ts_data

5.Transportable Tablespace Mode
expdp scott/tiger directory=data_pump_dir dumpfile=qadar.dmp transportable_tablespaces=users, apps_ts_data
It EXP_FULL_DATABASE role required.

Before expdp commands you should perform some pre-checks ?

create directory, or else if you go with default directory
select * from dba_directories;

create or replace directory data_pump_dir AS 'c:\temp';
-- default is $ORACLE_BASE/admin/<database_name>/dpdump

gramt export full database to scott;

Note: Verify that the environment variables ORACLE_HOME and ORACLE_SID are set properly in your shell. If they are not set then you must set them at the command line to proceed.


How to check the datapump import jobs are running or not ?

1.Using the datapump client (expdp & impdp) STATUS command:-

When the export or import job is running press +C keys to get to the respective datapump client prompt OR you can use another session of datapump client and using the ATTACH clause attach to the running job and then issue the STATUS command:-
Export> status

2.Querying V$SESSION_LONGOPS & V$SESSION views
3.Querying V$SESSION_LONGOPS & V$DATAPUMP_JOB views
4.Querying DBA_RESUMABLE view.
5.Querying v$session-wait

Oracle introduced datapump from which version ?
Oracle Datapump was introduced with 10gR1 as a replacement to the classic export/import utilities


What are two important packages of Datapump ?
Oracle added two new packages DBMS_DATAPUMP & DBMS_METADATA which forms the base for the datapump export import operations.



What are the important table in datapump ?
Master Table
Master table structure created during an schema export job
SQL> desc "ADMIN"."SYS_EXPORT_SCHEMA_01

What are the important processes in datapump ?

Client Process
Shadow Process
Master Control Process
Master control process (MCP) which is created by shadow process upon START_JOB request from Datapump API is the master process that controls the execution and sequencing for the datapump job with the information fetched from master table. MCP has a process name as <Instance Name>_DMnn_<pid>



Worker process
The job of worker process is to unload/load the data and metadata from/to the database and also to update the master table. Worker process has a process name as <Instance Name>_DWnn_<pid>.

Parallel Query process
parallel processing specially in the case where table partitioning is involved, even the worker process creates some parallel query (PQ) processes and further assigns the unloading/loading assignment to these PQ processes and act as a query coordinator

Command and control queue

All the processes related to Datapump jobs described above have subscription of this queue. Command and control queue is used for interprocess communication between all the datapump processes to coordinate all the API commands, loading/unloading requests & responses, file requests, logging etc. This queue is named as KUPC$C_<job-unique>_<timestamp>.

Status queue

The shadow process subscribes to read from this queue and is used to receive error messages from MCP which is the only process that can write to this queue. This queue is named as KUPC$S_<job-unique>_<timestamp>.

what are data movement in datapump ?

Data Pump has 3 options for moving the data out/in from/to the database, and based on several performance related factors & user specified arguments, MCP chooses the fastest option for that particular operation.

The available options are:-
Direct Path Loads and Unloads.
External Tables.
Insert as Select over a network

How will you take consistent export backup? What is the use of flashback_scn ?
Ans:  To take a consistent export backup you can use the below method:
SQL: select to_char(current_scn) from v$database;
Expdp parfile content:

directory=OH_EXP_DIR dumpfile=exporahow_4apr_<yyyymmdd>.dmp logfile=exporahow_4apr_<yyyymmdd>.log  schemas=ORAHOW flashback_scn=<<current_scn>>

What is use of CONSISTENT option in exp?

When you export a table, you are guaranteed that the contents of that table will be consistent with the time that the export of that table was started. This means that if you start exporting the table at 12:00 and someone makes changes to the data in the table at 12:05 and your export of this table finishes at 12:10, then the export will not contain any of the changes made between 12:00 and 12:10. You cannot change this behavior with Oracle's export utility.

The CONSISTENT parameter controls whether or not the entire export is consistent, even between tables. If CONSISTENT=N (the default), then the export of a table will be consistent, but changes can occur between tables. If CONSISTENT=Y, then the entire dump file is consistent with the point in time that you started the export.

What is use of DIRECT=Y option in exp?

Normally export will follow the process of SELECT statement i.e data from disk will be copied to buffer cache and then it will be written to dump file. When we use direct path by specifying DIRECT=Y in export command, then oracle will copy data directly from disk to PGA and from there it is written to dumpfile.

Which are the common IMP/EXP problems?

ORA-00001: Unique constraint ... violated - Perhaps you are importing duplicate rows. Use IGNORE=N to skip tables that already exist (imp will give an error if the object is re-created) or the table could be dropped/ truncated and re-imported if we need to do a table refresh..
IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.
ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO (However this option could create possible referential problems, because the tables are not exported from one snapshot in time).
ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y (with an appropriate BUFFER parameter) while importing.

What is the Benefits of the Data Pump Technology?
The older export/import technology was client-based. The Data Pump technology is purely       server based. All dump, log, and other files are created on the server by default. Data Pump technology offers several benefits over the traditional export and import data utilities.

The following are the main benefits of the Data Pump technology:
Improved performance: The performance benefits are significant if you are transferring huge
amounts of data.
Ability to restart jobs: You can easily restart jobs that have stalled due to lack of space or have
failed for other reasons. You may also manually stop and restart jobs.
Parallel execution capabilities: By specifying a value for the PARALLEL parameter, you can choose the number of active execution threads for a Data Pump Export or Data Pump Import job.
Ability to attach to running jobs: You can attach to a running Data Pump job and interact with it from a different screen or location. This enables you to monitor jobs, as well as to modify certain parameters interactively. Data Pump is an integral part of the Oracle database server, and as such, it doesn’t need a client to run once it starts a job.
Ability to estimate space requirements: You can easily estimate the space requirements for your export jobs by using the default BLOCKS method or the ESTIMATES method, before running an actual export job (see the “Data Pump Export Parameters” section later in this chapter for details).
Network mode of operation: Once you create database links between two databases, you can perform exports from a remote database straight to a dump file set. You can also perform direct imports via the network using database links, without using any dump files. The network mode is a means of transferring data from one database directly into another database with the help of database links and without the need to stage it on disk.
Fine-grained data import capability: Oracle9i offered only the QUERY parameter, which enabled you to specify that the export utility extract a specified portion of a table’s rows. With Data Pump, you have access to a vastly improved fine-grained options arsenal, thanks to new parameters like INCLUDE and EXCLUDE.
Remapping capabilities: During a Data Pump import, you can remap schemas and tablespaces, as well as filenames, by using the new REMAP_ * parameters. Remapping capabilities enable you to modify objects during the process of importing data by changing old attributes to new values. For example, the REMAP_SCHEMA parameter enables you to map all of user HR’s schema to a new user, OE. The REMAP_SCHEMA parameter is similar to the TOUSER parameter in the old import utility

How to improve exp performance?

1. Set the BUFFER parameter to a high value. Default is 256KB.
2. Stop unnecessary applications to free the resources.
3. If you are running multiple sessions, make sure they write to different disks.
4. Do not export to NFS (Network File Share). Exporting to disk is faster.
5. Set the RECORDLENGTH parameter to a high value.
6. Use DIRECT=yes (direct mode export).

How to improve imp performance?

1. Place the file to be imported in separate disk from datafiles.
2. Increase the DB_CACHE_SIZE.
3. Set LOG_BUFFER to big size.
4. Stop redolog archiving, if possible.
5. Use COMMIT=n, if possible.
6. Set the BUFFER parameter to a high value. Default is 256KB.
7. It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
8. Use STATISTICS=NONE
9. Disable the INSERT triggers, as they fire during import.
10. Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.


What is COMPRESSION parameter in expdp?

The COMPRESSION parameter enables the user to specify which data to compress before writing theexport data to a dump file. By default, all metadata is compressed before it’s written out to an export dump file. You can disable compression by specifying a value of NONE for the COMPRESSION parameter, as shown here:
$ expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_comp.dmp COMPRESSION=NONE
The COMPRESSION parameter can take any of the following four values:
ALL: Enables compression for the entire operation.
DATA_ONLY: Specifies that all data should be written to the dump file in a compressed format.
METADATA_ONLY: Specifies all metadata be written to the dump file in a compressed format.
This is the default value.
NONE: Disables compression of all types.

What are Export Filtering Parameters in expdp?
Data Pump contains several parameters related to export filtering. Some of them are substitutes for old export parameters; others offer new functionality.

CONTENT
By using the CONTENT parameter, you can filter what goes into the export dump file. The CONTENT
parameter can take three values:
• ALL exports both table data and table and other object definitions (metadata).
• DATA_ONLY exports only table rows.
• METADATA_ONLY exports only metadata.

EXCLUDE and INCLUDE

The EXCLUDE and INCLUDE parameters are two mutually exclusive parameters that you can use to perform what is known as metadata filtering. Metadata filtering enables you to selectively leave out or include certain types of objects during a Data Pump Export or Import job. In the old export utility, you used the CONSTRAINTS, GRANTS, and INDEXES parameters to specify whether you wanted to export those objects. Using the EXCLUDE and INCLUDE parameters, you now can include or exclude many other kinds of objects besides the four objects you could filter previously. For example, if you don’t wish to export any packages during the export, you can specify this with the help of the EXCLUDE parameter.

QUERY

The QUERY parameter serves the same function as it does in the traditional export utility: it lets you selectively export table row data with the help of a SQL statement. The QUERY parameter permits you to qualify the SQL statement with a table name, so that it applies only to a particular table. Here’s an example:

QUERY=OE.ORDERS: "WHERE order_id > 100000"

In this example, only those rows in the orders table (owned by user OE) where the order_id is
greater than 100,000 are exported.

What is use of INDEXFILE option in imp?
Will write DDLs of the objects in the dumpfile into the specified file.

What is use of IGNORE option in imp?
Will ignore the errors during import and will continue the import.

What are the differences between expdp and exp (Data Pump or normal exp/imp)?

Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.

Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?

Data Pump is block mode, exp is byte mode.
Data Pump will do parallel execution.
Data Pump uses direct path API.

How to improve expdp performance?
Using parallel option which increases worker threads. This should be set based on the number of cpus.

How to improve impdp performance?
Using parallel option which increases worker threads. This should be set based on the number of cpus.

In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?

Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.
Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

What is the order of importing objects in impdp?

 Tablespaces
 Users
 Roles
 Database links
 Sequences
 Directories
 Synonyms
 Types
 Tables/Partitions
 Views
 Comments
 Packages/Procedures/Functions
 Materialized views

How to import only metadata?

CONTENT= METADATA_ONLY

How to import into different user/tablespace/datafile/table?

REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE
REMAP_DATA

You are getting undo tablespace error during import, how you will avoid it?
We can use COMMIT=Y option

Can we import a 11g dumpfile into 10g database using datapump?
Yes we can import from 11g to 10g using VERSION option.

How to determine the Schemas inside an Oracle Data Pump Export file ?
strings dumpfile.dmp | grep SCHEMA_LIST
(or)
$ strings myfile.dmp|more

How to suspend and Resum Export Jobs (Attaching and Re-Attaching to the Jobs) ?
parameter name JOB_NAME=myfullJob

press CTRL+C to enter into interactive mode. Then he will get the Export> prompt where he can type interactive commands
expdp / as sysdba attach=job_name
export>status
export>stop_job
export>start_jop
export>kill_job
export>continue_job

What are the Process involved in Expdp/ImpDP? at back end?
Client process: this process initiated by client utility and makes call data pump api. Once data pump is initiated this process is necessary for the job.
Shadow process:when client login to db foreground process is created. It services the client data pump api requests. This process creates the master table and creates advanced queues for communication. Once client process ends shadow process also go away.
Master control process :MCP controls the execution of data pump job.there is one MCP per job. MCP divides the data pump job into various metadata and data load or unload jobs and hand over them to worker processes.
Worker processes:MCP creates worked process based on the value of parallel parameter. The worker process performs the task requested by MCP.

How to get estimate size of dumpfile before exporting
Expdp estimate_only=y estimate=blocks | statistics

What would be compression ratio/percentage when we use compression param
It would be Around 80 percent when compared to dumps without compression.

What you will do when export is running slow?
We need to skip taking export of indexes, use BUFFER and DIRECT parameters

Import process is running slow, what can you do now to make it faster?
We can skip importing indexes as it is the most time taking job during import

You are observing undo tablespace error during import, how you will avoid it?
We can use COMMIT=Y option

How to re-organize schema?
We can use dbms_redefinition package for online re-organization of schema objects. Otherwise using import/export and data pump utility you can recreate or re-organize your schema.

How we can improve the EXP Performance?
1.Set the BUFFER parameter to a high value (e.g. 2M)
2.If you run multiple export sessions, ensure they write to different physical disks.

How can we find elapsed time for a particular objects during impdp or expdp ?
We have an undocumented parameter metrics in datapump to check how much it took to export different objects types.

Is it possible to export two tablespace at a time and import into a single tablespace ?
Maybe, at a time you can export but you can't import them at a time

What is exactly dumpfile contains? what happens if we specify direct=y and if we are not specifying also ?
If you specify direct=y then it will bypass the buffer cache, bypass the sql command processing layer.

Whats the content will be on dumpfile ?
impdp dumpfilename sqlfile=content.sql, this will create the schema & password of all the objects

How can we chec datapump file is corrupted or not ?
use SQLFILE parameter with import script to detect corruption.

While importing if we miss 1 row so how we get that particular row ?
reimport from the backup table, reimport with query option to import only particular rows

What is the meaning of table_exists_action=truncate in Expdp ?
truncate deletes existing rows and then loads rows from the source, or this says to truncate the existing table rows, leaving the table definition and replacing the rows from the expdp dumpfile file being imported

Oracle Apps DBA adutilities Interview Questions

Oracle Apps DBA adutilities Interview Questions

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. 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.

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

25.  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.

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

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

28. 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.

29. 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.

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

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

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

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

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

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

36. 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.

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

38. 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.

Oracle AppsDBA Patching Interview Questions

Oracle AppsDBA Patching Interview Questions

1. What is a one-off patch?
Ans : An one-off patch is a small patch (of without any pre-requisites) to fix a bug.

2. What is a mini pack?
Ans : A mini pack is one which will upgrade any product patchset level to next level (like AD.H to AD.I).

3. What is Family pack?
Ans : A Family pack is one which will upgrade the patchset level of all the products in that family to particular patchset level.

4. What is Maintenance pack?
Ans : A maintenance pack will upgrade applications from one version to another (like 11.5.8 to 11.5.9).

5. What is a Rollup patch?
Ans : A rollup patch is one which will deliver bug fixes identified after the release of any major application versions (like 11.5.10.2/12.1.0).

6. What is consolidated patch?
Ans: Consolidated patches will come into picture after upgrades from one version of applications to another, all post upgrade patches will a consolidated and given as consolidated patch.

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

7. How you apply a patch?
Ans : adpatch

8. How to find latest patchset level for module installed?
Ans : select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME;

9. How you will find whether a patch is applied/not?
Ans : Query ad_bugs.

10. What is the other table where you can query what are the patches applied?
Ans : ad_applied_patches.

11. How to find out which patch driver is applied (like c,d,g or u)?
Ans: Query ad_patch_drivers.

12. How to find out whether a language patch is applied for a particular patch?
Ans : Query ad_patch_driver_langs.

13. What is the difference between ad_bugs and ad_applied_patches?
Ans: A patch can deliver solution for more than one bug, so ad_applied_patches may not give the perfect information as in case of ad_bugs.

14. What inputs you need to apply a patch other than driver name and etc?
Ans : apps and system passwords

15. What are the tables adpatch will create and when?
Ans : Adpatch will create FND_INSTALL_PROCESSES and AD_DEFERRED_JOBS table when it will apply d,g and u drivers.

16. What is a patch and name some different types of patches?
Ans : Patch is a program which fixes the bug.

There are 4 different types of patches

• One-off patch:
o This is the simplest type of patch. It is created to resolve a particular problem.

• Mini pack Patch:
o It is a collection of one-off patches related to a particular module or product.
o Mini Pack version of module is denoted by Alphabetic characters.

• Family pack patch:
o This is a collection of mini pack patches in one family.
o Alphabetic characters denote the family pack version.

• Maintenance pack patch:
o This is a collection of family pack patches.
o Oracle Applications Release 11.5.10 is an example of Maintenance pack.
rollup patches,
consolidated patches.

17. What are the contents of patch and drivers present in patching?
Ans : Contents of patch:
• Readme files
• Driver files
• Metadata files
• Replacement files

Drivers present in patching:
• C (copy) driver - It copies all the files and links the executables.
• D (database) driver - It is responsible for running SQL scripts which updates the database.
• G (generate) driver - It is responsible for generating forms, reports and message files.
• U (unified) driver - It is a united driver containing all copy and database actions.

18. What do you do if patch fails?
Ans :
• Evaluate log files to determine the cause of the error.
• Repair the cause of error.
• Now restart adpatch.
• Answer 'Yes' when adpatch asks if you want to continue the previous session.
• Adpatch starts from where it left off by skipping all completed steps.

19. How can I determine the effects a patch will have on my application system?
Ans :This can be done by patch wizard in the Oracle Applications manager.
• Checking total number of files in the patch and which are installed.
• Products that contain updated files.
• Total number of files introduced by the patch.
• Files on the target system changed by the patch.
• Files which depend on patched files.

20. How can you reduce the downtime when you have to apply multiple patches?
Ans :
• You can reduce the downtime by merging all the patches into one single patch using admergepatch (admrgpch) tool.
• You can also apply multiple patches one by one by choosing options nocompiledb, nocompilejsp, and nolink.
• Compilation of invalid objects, jsp’s and relinking can be skipped till the last patch is applied.
• In the last patch you can compile all of them and then relink.
• You can also choose the max number of workers which your CPU permits.

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 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 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 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 contain 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 you r 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 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.

What is UNDO DATA?

What is UNDO DATA?

UNDO DATA is the copy of pre-modified data that is captured for every transactions that changes data

Why UNDO required?

* rollback transactions
* support read consistency
* support flashback operations
* recover from failed transactions

How long does UNDO data stay ?

It is retained until one of the following happens:

* users commits the transactions
* users rollback the transactions
* user execute the DDL statements (CREATE, DROP, ALTER, RENAME)
* user session is terminated abnormally (transactions rollback)
* user session terminates normally with an exit (transactions commits)

Where does UNDO data stay ?

* Stored in UNDO segments/UNDO tablespace
* Only 1 active tablespace for an instance
* They are owned by the user SYS

Starting and Stopping OEM Cloud Control 13c

Starting and Stopping OEM Cloud Control 13c

Starting OEM 13c Components

1) Start the listener.

[oracle@MQM ~]$ $ORACLE_HOME/bin/lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-JUN-2016 16:44:07

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/MQM/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MQM.ORACLE.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MQM.ORACLE.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                02-JUN-2016 16:44:07
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/MQM/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MQM.ORACLE.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

2) Start the repository database.

[oracle@MQM ~]$ $ORACLE_HOME/bin/sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 2 16:48:18 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL>startup
ORACLE instance started.

Total System Global Area 3003121664 bytes
Fixed Size                  2928920 bytes
Variable Size             771755752 bytes
Database Buffers         2214592512 bytes
Redo Buffers               13844480 bytes
Database mounted.
Database opened.

3) Start the OMS

[oracle@MQM ~]$ $OMS_HOME/bin/emctl start oms
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
JVMD Engine is Up
Starting BI Publisher Server ...
BI Publisher Server Successfully Started
BI Publisher Server is Up

4) Start the agent

[oracle@MQM ~]$ $AGENT_HOME/bin/emctl start agent
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Starting agent .................................................started.

Stoping OEM 13c Components

1) Stop the OMS

[oracle@MQM ~]$ $OMS_HOME/bin/emctl stop oms -all
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping Oracle Management Server...
WebTier Successfully Stopped
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
JVMD Engine is Down
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
AdminServer Successfully Stopped
BI Publisher Server is Down

2)Stop the agent.

[oracle@MQM ~]$ $AGENT_HOME/bin/emctl stop agent
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

3) Stop repository database

[oracle@MQM ~]$ $ORACLE_HOME/bin/sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 2 16:41:31 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Stop the listener

[oracle@MQM ~]$ $ORACLE_HOME/bin/lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 02-JUN-2016 16:43:15

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MQM.ORACLE.com)(PORT=1521))

Convert Single Instance to RAC using RCONFIG

Convert Single Instance to RAC using RCONFIG

In $ORACLE_HOME/assistants/rconfig/sampleXMLs directory, there are two templates ConvertToRAC_AdminManaged.xml and ConvertToRAC_PolicyManaged.xml that are used to convert a single instance database to RAC admin managed or policy managed database respectively.

Below are the prerequisites that should be completed.

1-> Ensure the clusterware is configured, up and running on the nodes.
2-> Oracle RDBMS binaries for RAC database must be installed.
3-> Active ASM instance across the nodes.
4-> Create and mount required ASM diskgroups.
5-> Stand alone database running on the local node.
6-> Stand alone DB and RAC must be of same db versions.

All the steps to be performed from RAC1 Node(cluster node)
Note: It is assumed that we are also having ORACLE_HOME for single instance (i.e dbhome_1) at RAC1

1-> #su - oracle
2-> $. oraenv
3-> $export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
4-> $export PATH=$ORACLE_HOME/bin:$PATH
5-> $dbca => for creating a database for single instance
6-> $export ORACLE_HOME=/u01/home/oracle/product/11.2.0/dbhome_1
7->  $export PATH=$ORACLE_HOME/bin:$PATH
8-> $cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_Adminmanged.xml  /convert1.xml

Convert parameters:

– Source $OH = OracleHome of non-rac database for SourceDBHome
– Destination $OH = OracleHome where the RAC database should be configured.
– SourceDBInfo SID
– List of nodes that should have rac instances running for the Admin Managed Cluster Database. Local Node should be the first node.
– Instance Prefix tag is optional starting with 11.2.
– Specify the type of storage to be used by RAC database (ASM or Cluster File system).
– Specify Database Area Location to be configured for RAC database.
– Specify Fast Recovery Area to be configured for RAC database.

Example:

9-> $vi convert1.xml
:se nu
7=> verify="ONLY" (for verify and after give NO for convert)
9=> /u01/app/oracle/product/11.2.0/db_1   (source db home location)
11=>/u01/app/oracle/product/11.2.0/db_1  (target db home location)
13=> SID="nonrac" (sid of non-rac db)
16=>password=manager
22=> name="rac1"    (cluster(rac) nodes)
23=> name="rac2"
28=> prefix>nonrac<    (after migration rconfig creates nonrac1,nonrac2 intances)
31=> type="ASM"      (storage type (only two are there (ASM|CFS)))
33=> +DATA (storage location)
35=> +DATA (FRA location)
:wq!

10-> $rconfig convert1.xml (Only for verifying whether successful migrate or not)

11-> Set ONLY->NO in convert1.xml and run the below again:

$rconfig convert1.xml (Now For Migration after giving NO or YES option)

Note:

The Convert verify option in the ConvertToRAC.xml file has three options:

Convert verify="YES": rconfig performs checks to ensure that the prerequisites for single-instance to Oracle RAC conversion have been met before it starts conversion
Convert verify="NO": rconfig does not perform prerequisite checks, and starts conversion
Convert verify="ONLY" rconfig only performs prerequisite checks; it does not start conversion after completing prerequisite checks

Post conversion steps:

1-> Adjust the SGA for the RAC database, at least 15% for buffer cache and shared pool.
2-> Create SERVICE and apply TAF & Load balancing options, if required.
3-> Backup the RAC database.
4-> Backup OCR.
5-> Remove single-instance database.

How to resume from a failed conversion:

1-> Simply rerun the command if the rconfig exits in the mid-of the conversion for any fatal reasons.
2-> Perform clean-up operations on converted instance before reinitializing the conversion procedure.
3-> You can manually add the resources to CRS, in case rconfig utility fails to perform the final steps.

Logs to monitor:

1-> Single instance database alert log file.
2-> RAC database alert log file.
3-> $ORACLE_BASE/cfgtools/rconfig or /u01/app/oracle/cfgtoollogs/rconfig [records entire operation step-by-step]
4-> For RMAN log and SQL log, check the respective directories under /u01/app/oracle/cfgtoollogs/rconfig/db


Rconfig FAQ's:

What is the best scenario for using RCONFIG?

RCONFIG invokes RMAN internally to back up  the database to proceed with converting non-ASM to ASM.  This is time consuming task if the DB is in Terabyte size  and it will cause longer outage.
Hence this is good option only for small database and customer allows significant amount downtime.

How do we improve the conversion time with RCONFIG?

We can increase RMAN backup channel to reduce the backup time. Again it depends on how many number of CPU you have in your node. Here is the command to adjust the parallelism.  The  channel can be adjusted according to your server capacity.

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 6;

What does RCONFIG do during the conversion?

Migrate the database to ASM storage if we specified in XML file
Create Database Instances on all nodes in the cluster
Configure Listener and NetService entries
Configure and register CRS resources
Start the instances on all nodes in the cluster.
What are the Pre-requisite for RCONFIG utility?
Cluster should  installed and all the cluster process is up and running on all the nodes.
The oracle RAC RDBMS is installed on all nodes.
ASM disk must be mounted across all nodes if we want to migrate from file system to ASM.
The source and target should be in same home and same OS version.
Make sure, ASM disk has enough storage to migrate all the data files
How do we test RCONFIG before we do real conversion?

We have convert verify parameter in XML file and it has below options. Please choose ONLY.

Convert verify="YES"
Runs through verification checks to ensure that the
prerequisites for single-instance to RAC db
conversion have been met before it acutually starts
the conversion.

Convert verify="NO"
Bypass the prerequisite verification checks, and
starts conversion straight away

Convert verify="ONLY"
Do not perform the acutal conversion process.
Rather, just run through the prerequisite verification
checks and ends.

What can do if it fails middle of the conversion?

Don't get panic.  This will perform the clean-up operation on converted Instance or will delete the files created by earlier run.

How do we monitor conversion process during the migration?

There are few logs and we can tail the log during the process. We can monitor (tail -f)
single instance alert log, RAC database alert log, rconfig alert log, rman logs. Log info will give better idea and we can see what is going on.

How do we convert using RCONFIG if the stand alone DB is in different host?

You would accomplish this task by first restoring single instance database  in one of RAC node and , then you would use RCONFIG  to convert to RAC option.