Applications DBA

1. What is FNDSM?

FNDSM is executable & core component in GSM (Generic Service Management Framework). You start FNDSM services via APPS listener on all Nodes in Application Tier in E-Business Suite.

2. What is iAS Patch ?

iAS Patch are patches released to fix bugs associated with IAS_ORACLE_HOME (Web Server Component) Usually these are shipped as Shell scripts & you apply iAS patches by executing Shell script.

Explain Architecture of Oracle Apps 11i?

Apps 11i is Mutli Tier architecture with Desktop Tier (Client Tier), Middle Tier (Application Tier) and Database Tier.

3. What are various components in Application/Middle Tier?

In Application Tier various components are Web Server, Forms Server, Reports Server, Concurrent Manager, Admin Server & Discoverer Server.

4. What is APPL_TOP, COMN_TOP, ORA_TOP.....?

XXX_TOP is top level directory in Oracle Application 11i for respective Component.

5. What is US directory in $AD_TOP or under various product TOP's?

US directory is default language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for Arabic.

6. What are the main concurrent Manager types?

ICM - Internal Concurrent Manager which manages concurrent managers

Standard Managers - Manages processing of requests

CRM - Conflict Resolution Managers, resolve conflicts in case of incompatibility

7. Where is Concurrent Manager Log file location?

By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

8. Where would I find .rf9 file, and what exactly it does?

These files are used during restart of a patch in case of patch failure because of some reason.

9. Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored & why is it used?

This file is defined by environment variable FORMS60_WEB_CONFIG_FILE. This is usually in directory $OA_HTML/bin on forms tier. This file is used by any forms client session. When a user tries to access forms, f60webmx picks up this file and based on this configuration file creates a forms session to user/client.

10. What is multi node system?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is database, Concurrent Manager on one machine and Forms Server, Web Server on second machine.

11. Can you clone from multi node system to single node system & vice versa?

Yes, this is now supported via Rapid Clone, Check if your system has all prerequisite patches for Rapid Clone and you are on latest rapid clone patch.

12. Does rapid clone takes care of Updating Global oraInventory or you have to register manually in Global oraInventory after clone?

Rapid Clone automatically updates Global oraInventory during configuration phase. You don't have to do anything manually for Global oraInventory.

13. What is .dbc file, where is it stored, what is the use of .dbc file?

DBC. as name suggests is a database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE.

14. What things you do to reduce patch timing?

Merging patches via admrgpch

Use various adpatch options like nocompiledb or nocompilejsp

Use defaults file

Staged APPL_TOP during upgrades

Increase batch size (Might result into negative)

15. How you put Applications 11i in Maintenance mode?

Use adadmin to change maintenance mode in EBS. If you don't want to put EBS in maintenance mode you can use adpatch options=hotpatch feature.

16. Can you apply patch without putting Applications 11i in Maintenance mode?

Yes, use options=hotpatch as mentioned above with adpatch.

17. What are various options available with adpatch?

Various options available with adpatch depending on your AD version are

autoconfig, check_exclusive, checkfile, compiledb, compilejsp, copyportion, databaseportion, generateportion, hotpatch, integrity, maintainmrc, parallel, prereq, validate

18. adident utility is used for what?

adident utility in oracle apps is used to find version of any file . For example “adident Header <filename>”

What is adsplice utility?

adsplice in oracle apps is utility to add a new product to EBS.

How can you license a product after installation?

You can use ad utility adlicmgr to license product in Oracle Apps.

19. What is MRC? What you do as Apps DBA for MRC?

MRC also called as Multiple Reporting Currency in Oracle Apps. By default you have currency in US Dollars but if your organization’s operating books are in other currency then you as apps DBA need to enable MRC in Apps.

20. What is JVM (Java Virtual Machine) and which component uses JVM?

JVM stands for Java Virtual Machine.

21. What is access_log in apache, what entries are recorded in access_log? Where is the default location of this file?

access_log in Oracle Application Server records all users accessing oracle applications 11i. Its file location is defined in httpd.conf with default location at $IAS_ORACLE_HOME/Apache/Apache/logs.

22. Where is Jserv configuration files stored?

Jserv configuration files are stored in $IAS_ORACLE_HOME/Apache/Jserv/etc.

23. Where is applications start/stop scripts stored?

Applications start/stop scripts are in directory $COMMON_TOP/admin/scripts/$CONTEXT_NAME

24. What are main configuration files in Web Server (Apache)?

Main configuration files in Oracle Apps Web Server are

httpd.conf, apps.conf, oracle_apache.conf, httpd_pls.conf, jserv.conf, ssp_init.txt, jserv.properties, zone.properties

plsql.conf, wdbsvr.app, plsql.conf

25. How to check if Apps 11i System is Autoconfig enabled?

Under $AD_TOP/bin check for file adcfginfo.sh and if this exists use adcfginfo.sh contextfile=<CONTEXT> show=enabled

If this file is not there, look for any configuration file under APPL_TOP if system is Autoconfig enabled then you will see entry like

# AutoConfig automatically generates this file. It will be read and.......

26. How to check if Oracle Apps 11i System is Rapid Clone enabled?

For system to be Rapid Clone enabled, it should be Autoconfig enabled. You should have Rapid Clone Patches applied.

27. What is the difference between two env files in <CONTEXT>.env and APPS<CONTEXT>.env under $APPL_TOP?

APPS<CONTEXT>.env is main environment file which in turn calls other environment files like <CONTEXT>.env under $APPL_TOP

28. What is your Oracle Apps 11i Webserver Version and how to find it?

In order to find version under IAS_ORACLE_HOME/Apache/Apache/bin

Execute ./httpd -version

29. What is plssql database cache?

In order to improve performance mod_pls (Apache component) caches some database content to file. This plsql cache is used to store session information. plsql cache is used to store plsql cache i.e. used by mod_pls

30. Where is database plssql cache stored?

plssql & session cache are stored under $IAS_ORACLE_HOME/ Apache/modplsql/cache directory

31. How to determine Oracle Apps 11i Version?

select RELEASE_NAME from fnd_product_groups;

You should see output like

RELEASE_NAME

11.5.9 or 11.5.10.2

32. What is content of dbc file & why is it important?

DBC file is quite important as whenever Java or any other program like forms want to connect to database it uses dbc file. Typical entry in dbc file is

GUEST_USER_PWD

APPS_JDBC_URL

DB_HOST

33. There are lot of dbc file under $FND_SECURE, How is it determined that which dbc file to use from $FND_SECURE?

This value is determined from profile option "Applications Database ID"

34. What is RRA/FNDFS?

Report Review Agent (RRA) also referred by executable FNDFS is default text viewer in Oracle Applications 11i for viewing output files & log files.

35. What is PCP is Oracle Applications 11i?

PCP is acronym for Parallel Concurrent Processing. Usually you have one Concurrent Manager executing your requests but you can configure Concurrent Manager running on two machines. So for some of your requests primary CM Node is on machine1 and secondary CM node on machine2 and for some requests primary CM is on machine2 & secondary CM on machine1.

36. Why I need two Concurrent Processing Nodes or in what scenarios PCP is used?

If you are running GL month end reports or taxation reports annually these reports might take couple of days. Some of these requests are very resource intensive so you can have one node running long running resource intensive requests while other processing your day to day short running requests. Another scenario is when your requests are very critical and you want high resilience for your Concurrent Processing Node, you can configure PCP. So if node1 goes down you still have CM node available processing your requests.

37. How to confirm if Report Server is Up & Running?

Report Server is started by executable rwmts60 on concurrent manager Node & this file is under $ORACLE_HOME/bin Execute command on your server like

ps -ef | grep rwmts60

38. What is difference between ICM, Standard Managers and CRM in Concurrent Manager?

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down, it checks & tries to restart them. You can say it is an administrator to other concurrent managers. Standard Manager: These are normal managers which performs actions on the requests and does batch or single request processing. CRM is acronym for Conflict Resolution Manager and is used to resolve conflicts between managers & requests. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are assigned to CRM for incompatibilities & conflict resolution.

39. What is use of Apps listener?

Apps Listener usually run on All Oracle Applications 11i Nodes with listener alias as APPS_$SID is mainly used for listening requests for services like FNDFS & FNDSM.

40. How to start Apps listener?

In Oracle 11i, you have script adalnctl.sh which will start your apps listener. You can also start it by command lsnrctl start APPS_$SID (Replace sid by your Instance SID Name)

41. How to confirm if Apps Listener is Up & Running?

lsnrctl start APPS_$SID (replace SID with your Instance Name) so if your SID is VISION then use lsnrctl status APPS_VISION

42. What is Web Listener?

Web Listener is Web Server listener which is listening for web Services (HTTP) request. This listener is started by adapcctl.sh & defined by directive (Listen, Port) in httpd.conf for Web Server.

43. How will you find total number of invalid objects in database?

SQLPLUS> select count(*) from dba_objects where status like 'INVALID';

44. How to compile Invalid Objects in database?

You can use adadmin utility to compile or you can use utlrp.sql script shipped with Oracle Database to compile Invalid Database Objects.

45. How to compile JSP in Oracle Apps?

You can use ojspCompile.pl perl script shipped with Oracle apps to compile JSP files. This script is under $JTF_TOP/admin/scripts. Sample compilation method is

perl ojspCompile.pl --compile --quiet

46. What is difference between adpatch & opatch?

# adpatch is utility to apply oracle apps Patches whereas

# opatch is utility to apply database patches

47. Can you use both adpatch & opatch in Apps?

Yes you have to use both in apps, for apps patches you will use adpatch utility and for applying database patch in apps you will use opatch utility.

48. Where will you find forms configuration details apart from xml file?

Forms configuration at time of startup is in script adfrmctl.sh and appsweb_$CONTEXT_NAME.cfg for forms client connection used each time a user initiates forms connection.

49. What is forms server executable Name?

f60srvm

50. What are different modes of forms in which you can start Forms Server and which one is default?

You can start forms server in SOCKET or SERVLET by default Forms are configured to start in socket mode.

51. How you will start Discoverer in Oracle Apps 11i?

In order to start discoverer you can use script addisctl.sh under $OAD_TOP/admin/scripts/$CONTEXT_NAME or startal.sh under $ORACLE_HOME/discwb4/util (under Middle/Application Tier)

52. How many ORACLE HOME are in Oracle Apps and what is the significance of each?

There are three $ORACLE_HOME in Oracle Apps, Two for Application Tier (Middle Tier) and One in Database Tier.

# ORACLE_HOME 1: On Application Tier used to store 8.0.6 techstack software. This is used by forms, reports & discoverer.

# ORACLE_HOME 2: On Application Tier used by iAS (Web Server) techstack software. This is used by Web Listener & contains Apache.

# ORACLE_HOME 3: On Database Tier used by Database Software usually 8i, 9i or 10g database.

53. Where is HTML Cache stored in Oracle Apps Server?

Oracle HTML Cache is available at $COMMON_TOP/_pages for some previous versions you might find it in $OA_HTML/_pages

54. What happens if you don't give cache size while defining Concurrent Manager?

Lets first understand what is cache size in Concurrent Manager. When Manager picks request from FND CONCURRENT REQUESTS Queues, it will pick up number of requests defined by cache size in one shot & will work on them before going to sleep. So in my views if you don't define cache size while defining CM then it will take default value 1, i.e. picking up one request per cycle.

55. What are few profile options which you update after cloning?

Rapid clone updates profile options specific to site level. If you have any profile option set at other levels like server, responsibility, user....level then reset them.

56. How to retrieve SYSADMIN password?

If forgot password link is enabled and sysadmin account is configured with mail id user forget password link else you can reset sysadmin password via FNDCPASS.

57. If you have done two nodes installation, first machine: Database and concurrent processing server. 2nd machine: form, web. Which machine has admin server/node?

Admin Server will be on First machine with concurrent processing server.

58. What is TWO_TASK in Oracle Database?

TWO_TASK mocks your TNS alias which you are going to use to connect to database. Let’s assume you have database client with TNS alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD; now if you don't want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to TNS alias define by value PROD i.e. TWO_TASK

59. What is GWYUID?

GWYUID stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

60. Where GWYUID defined & why is it used in Oracle Applications?

GWYUID is defined in dbc i.e. Database Connect Descriptor file. It is used to connect thin clients to database.

61. What is difference between GUEST_USER_PWD (GUEST/ORACLE) & GWYUID?

GUEST_USER_PWD(Guest/Oracle) is used by JDBC Thin Client where as GWYUID is used by Thick Clients like via Forms Connections.

62. What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD?

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where 0 is request id. 'Y' indicates the method of invocation i.e. it is directly invoked from the command-line not from the Submit Request Form.

63. In a Multi Node Installation, How will you find which node is running what Services?

You can query for table FND_NODES and check for column, SUPPORT_CP (for Concurrent Manager) SUPPORT_FORMS (for forms server), SUPPPORT_WEB (Web Server), SUPPORT_ADMIN (Admin Server), and SUPPORT_DB for database tier. You can also check same from CONTEXT File (xml file under APPL_TOP/admin)

64. If your system has more than one Jinitiator, how will the system know, which one to pick?

When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE.

65. While applying Apps patch using adpatch, if you want to hide the apps password, how will that be possible?

Use adpatch flags=hidepw while applying patches in apps to hide apps or system password being displayed on screen.

66. What is the importance of IMAP Server in Java Notification Mailer?

IMAP stands for Internet Message Access Protocol and Java Notification mailer require IMAP server for Inbound Processing of Notification Mails.

67. What is difference between Socket & Servlet Mode in Apps Forms?

When forms run in SOCKET Mode these are dedicated connection between Client Machine & Form Server. When Forms run in servlet mode there will be additional JVM for Forms Request in that case and you won't start form via adfrmctl.sh.

68. What is make program in Unix?

make is utility in Unix/Linux to maintain , update & generate a file mainly executable.

69. If by mistake someone deleted FNDLIBR can this executable be restored if Yes, How & if no, what will you do?

Yes, you can restore FNDLIBR executables

Run adadmin on concurrent manager node

Select Maintain Applications Files menu

Then select Relink Applications programs

When prompts for “Enter list of products to link ('all' for all products) [all]” select FND

When prompt for “Generate specific executables for each selected product [No]?” select YES

From list of executables select FNDLIBR this will create new FNDLIBR executables

70. What is .pls files which you see with apps?

.PLS file stands for plsql files. In apps patch these files contain code to create package spec or package body or both.

71. What are .ldt & .lct files which you see in apps patch or with FNDLOAD?

.ldt & .lct stands for Loader data file & Loader configuration files, used frequently in migrating customization, profile options, configuration data etc. across Instances.

72. What are .odf file in apps patch?

odf stands for Object Description Files used to create tables & other database objects while applying a patch.

73. Where to find Form Server log files in forms?

Form Server Start up log file default location is $OAD_TOP/admin/log/$CONTEXT_NAME/f60svrm.txt

Forms Run Time Diagnostics default location is $ORACLE_HOME/forms60/log/$CONTEXT_NAME

74. How to convert pll to pld file or pld file to pll ?

pll->pld f60gen module=MSCOSCW3.pll module_type=library userid=apps/<passwd> module_access=file output_file=MSCOSCW1.pld script=yes

pld -> pll f60gen module=MSCOSCW3.pld userid=apps/<passwd> module_type=library module_access=file output_file=MSCOSCW1.pll parse=y batch=yes compile_all=special

75. Does APPS_MRC Schema exist for MRC in 11.5.10 and higher?

No, apps_mrc schema is dropped with 11.5.10 Upgrade & 11.5.10 new Install. This is replaced by more Integrated Architecture.

76. If APPS_MRC schema is not used in 11.5.10 and higher then how MRC is working?

For products like Payable, Receivables which uses MRC and if MRC is enabled then each transaction table in base schema related to currency now has an associated MRC sub tables.

77. When you apply C driver patch does it require database to be Up & Why?

Yes, database & db listener should be UP when you apply any driver patch in apps. Even if driver is not updating any database object connection is required to validate apps & other schema and to upload patch history information in database tables.

78. Can C driver in apps patch create Invalid Object in database?

No, C driver only copies files in File System. Database Object might be invalidated during D driver when these objects are created/dropped/modified.

79. What is dev60cgi & f60cgi?

cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

80. Why does a worker fail in Oracle Apps Patch and few scenarios in which it failed for you?

Apps Patch worker can fail in case it doesn't find expected data, object, files or anything which driver is trying to update/edit/modify. Possible symptoms may be underlying tables/objects are invalid, a prerequisite patch is missing, login information is incorrect, inconsistency in seed data.

81. What is difference between mod_osso & mod_ose in Oracle HTTP Server?

mod_osso is Oracle Single Sign-On Module where as mod_ose is module for Oracle Servlet Engine.

82. What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms?

Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file.

83. What is ps -ef or ps command in Unix? for work ex < 1 yr

ps in unix/linux is a utility to find status of process. Used mainly to find if service/process is running or not.

84. Find application server version?

FND_PRODUCT_GROUPS

85. How will you find the Apache, forms, reports version in an EBS suite?

http –v – Apache

f60gen – Forms

ar60run – Reports

86. Find jinitiator version?

Client side – Control panel

Server side – SID_hostname.xml (s_jinit_ver_)

87. List some ad utilities and their functions?

adadmin, adctrl, adpatch, adlicmgr, admrgpch, adsplice and adworker

88. List out the modules related to oracle Apps DBA?

FND – Application Object Library, AU – Application Utilities, AD – Application DBA

89. What are profile options, what are various types of profile options?

User, application, responsibility and site

90. What are the different types of oracle patches available?

One-off, mini, diagnostics, cu, rup, language, help, platform specific, OS, OPatch, Interoperability, family pack and maintenance pack.

91. What are the different patch drivers available?

C driver, D driver, G driver and U driver.

92. List out the important env files?

Appsora.env, appsSID_hostname.env, adovars.env, APPSSID_hostname

93. What is the utility to change the password of a dba schema in oracle apps?

FNDCPASS

94. What are mandatory users in oracle apps?

applsys, applsyspub, apps

95. What are the components in the Application Tier?

Apache(http)

Jserver(jre)

Forms Server(f60srv)

Metric Server(d2ls)

Metric Client(d2lc)

Report Server(rwm60)

Concurrent Server(FNDLIBR)

Discoverer

96. What are main file systems in Oracle Apps?

APPL_TOP, COMMON_TOP, DB_TOP,ORA_TOP 7

97. What are there in Desktop Tier?

Internet Browser, JInitiator

98. What is the location of JInitiator in the Desktop Tier?

c:\program files\oracle\Jinitiator

99. What is the location of client cache?

c:\documents and settings\user\oracle jar Cache 10.

100. What is the location of Server cache?

$COMMON_TOP/_pages

101. Which package will be used for the user validation by plsql agent?

oraclemypage

102. What are the location of JAVA Files?

JAVA_TOP and all PRODUCT_TOP/Java/Jar

103. What is the name of the xml file of Apps and its location?

Context Name.xml and $APPL_TOP/admin

104. What are adadmin utilities and their location?

$AD_TOP/bin

adadmin

adpatch

adsplice

adident

adrelink

adlicmgr

105. What is the location of Apps environment file and its name?

contextname.env and $APPL_TOP

106. In how many ways Jar files are generated?

Normal and Force

107. Once Jar files are generated what files get affected?

All Product_top/java/jar files and Two files in JAVA_TOP they are appsborg.zip appsborg2.zip

108. How do you see the files in zip file?

unzip -v

109. How do you generate jar files?

Using adadmin and option 5

110. How do you start the apps services?

$COMMON_TOP\admin\scripts\Contextname\adstrtal.sh apps/apps

111. What is the executable to generate jar files?

adjava

112. How do you relink an executable of a product?

By relinking option in adadmin or adrelink

113. How do you relink AD product executable and usage?

adrelink.sh and adrelink.sh force=y "ad adsplice"

114. When do you relinking?

When you miss an executable file

When there is a problem with any executable file

When any product executable gets corrupted

115. What is DAD?

It is a file which stores apps passwords in hard coded format i.e. wdbsvr

116. How do you relink OS files and libraries?

Using make command

117. How do you know the package version?

select text from dba_source where name='package name' and type='PACKAGE BODY' and rownum<10>/rdbms/admin)

118. How do you load java class to database?

loadjava

119. What are restart files and its location?

These files contain the previous session info about adadmin. Location is $APPL_TOP\admin\sid\restart\*.rf9

120. How do you validate apps schema?

To validate synonyms, missing synonyms and all grants use adadmin. After validating it will produce a report in the location $APPL_TOP\admin\sid\out\*.out

121. How do you enable maintenance mode?

Using adadmin or running a script called "adsetmmd.sql ENABLE/DISABLE" (AD_TOP/patch/115/sql)

122. How to see the version of a script or form or report or etc?

adident Header GLXSTEA.fmx

123. What is the location of adadmin log?

$APPL_TOP\admin\sid\log

124. What are the oracle homes in Apps?

8.0.6 ORACLE_HOME(Dev 6i products) and IAS_ORACLE_HOME (Apache)

125. How do you configure your IP address at client side and server side?

c:\windows\system32\drivers\etc\hosts and \etc\host

126. What is the location of Database server related scripts?

$ORACLE_HOME\appsutil\scripts\contextname

127. What is the utility to clean the concurrent manager?

@cmclean.sql (You have to download it from metalink)

128. How do you stage the 11.5.10 Apps software?

Using adautostg.pl

129. What is the location of the source files of forms?

AU_TOP/forms/US/

130. What is the executable to generate forms?

f60gen

131. What are the profile options, what are various types of profile options?

You set profile options at the following levels

Site level

Application level

Responsibility level

User level

132. What is APPS listener? Why is it used?

Apps Listener usually run on all Oracle Applications 11i Nodes with listener alias as APPS_$SID and is mainly used for listening requests for services like FNDFS & FNDSM.

133. How do you start/stop apps listener?

lsnrctl start APPS_$SID

134. If users are complaining Oracle Applications 11i system is running slow, following are the things to check?

First as an Overview of Oracle Applications 11i check following things

Broad Level Steps to Check

1. Is Gather stats scheduled? (At least weekly)

2. Is Purge Obsolete Workflow request & concurrent request purging scheduled?

3. Is there any runaway process on database tier or application tier? (Runaway process is process taking unexceptionally long time with high CPU or Memory usages)

4. Any Discoverer process taking long time or High CPU? (Usually on Middle tier)

5. Any Custom report taking long time?

In order to understand problem better

1. Is whole application slow or only component of application is slow (like only Concurrent manager running slow)?

2. If this is with particular component like reports server slow then is a particular report or all reports are slow?

3. Is performance issue during all time or at specific time let’s say from 11AM to 4PM?

4. Was performance issue popped up recently or performance degradation is for long time?

5. What has changed recently?

Depending on answers you can start looking into application. If issue is specific to whole applications then check

1. If database initialization parameter is set correctly?

2. If application sizing is proper, server configuration is enough to accommodate all users?

135. What is Autoconfig?

Autoconfig is method of configuring Oracle Applications. All the information required to configure Oracle Apps 11i is stored in file called Context file. So there are two context files, one for Database Tier and Second for Application Tier. Context file is repository for configuration information stored in xml format. Its xml file and file name format is <SID>_<HOSTNAME>.xml so if your machine name is MACHINE1 and SID is VISION then context file name will be VISION_MACHINE1.xml

136. What is context file?

Oracle stores all the environment specific values in an xml file stored in $APPL_TOP/admin directory, which they call an application context file. This file is created by running adbldxml.sh/adbldxml.pl (Located in $AD_TOP/bin). Adbldxml.sh in turn runs oracle.apps.ad.context.GenerateContext java class.

Context file is generated by plugging in environment specific values in the context file template (don’t confuse this template with the configuration file templates) $AD_TOP/admin/template/adxmlctx.tmp. In older versions of 11i these replacement values were gotten from config.txt file (created by rapidwiz during installation), but in later versions, these values are gotten from the information, in existing configuration files and the database. adbldxml.sh/adbldxml.pl (in newer versions of autoconfig) creates a detailed log file, detailing the source for each context variable.

137. How to create User in Oracle Applications 11i? Can you delete a User?

Choose Users from the Manage menu to create a new user. This opens the Manage Users dialog box, where you choose New to enter information for a new user.

You provide the following user information:

User -- Enter a name that identifies the user.

User Type -- Select the type of the user.

Usernames -- Choose Edit to access the Edit User dialog box where you can associate one or more user names with the user and specify whether the user is authorized to create shareable documents.

Other -- Provide information that is specific to the client type. For example, for a thin-client workstation, you enter the SHELLS directory of the component where you are creating the user and the Express Server instance to which the user's personal database will be connected.

Adding a new user creates a user task and sends it to the Task Processor. If you are not running the Task Processor in the background, you must run it in the foreground to process the user task.

138. What is Single Sign On? (If you are using portal 3.0.9 or 10G)?

Oracle Enterprise Single Sign-On Suite provides users with unified sign-on and authentication across all their enterprise resources, including desktops, client-server, custom and host-based mainframe applications. Even if users travel or share workstations, they can enjoy the flexibility of a single log-on that eliminates the need for multiple usernames and passwords and helps enforce strong password and authentication policies.

139. How to find OUI version?

OUI stands for Oracle Universal Installer.

140. What happens if the ICM goes down?

Since ICM is down so it can't reach other managers & can't shutdown them but yes if because of some reason Standard Manager dies & ICM is already dead Standard managers will not start. The ICM does the work of distributing the concurrent request to standard managers. If ICM goes down the concurrent requests in standard managers will be running fine. But no new request will be assigned to the standard managers. So wait until all your concurrent requests gets completed and then bounce the concurrent managers to start your ICM.

141. Provide a high-level overview of the cloning process and post-clone manual steps?

Rapid Cloning Steps for Windows R12
Source System:
1.Run adpreclone.pl in Database Node
2.Perl $ORACLE_HOME\appsutil\scripts\$CONTEX_NAME\adpreclone.pl dbTier
3.Run adpreclone.pl in Apps Node
4.Perl $ADMIN_SCRIPTS_HOME\adpreclone.pl appsTier
5.Shut down the Applications and database
6.Change all services type into manual (option)
7.Restart the system (option)
8.Check if any FNDLIBR programs are running or not
9.Take a cold backup of database and applications
Target System:
Prepare your target system as per the source system configuration
1.Install Windows Server 2003 service pack 2 Enterprise Edition
2.Install Visual Studio 2005
3.Install Active perl
4.Install Cygwin Change make version from 3.81 into 3.80
5.Copy the Source system backup into target system
6.Set perl locations by using existing env file(It varies between DB node and Apps Node, We will set this two times)
7.Goto the $ORACLE_HOME\appsutil\clone\bin folder and the post clone scripts
8.Run Postclone scripts in db Tier
9.Perl $ORACLE_HOME\appsutil\clone\Bin\adcfgclone.pl dbTier
10.Run Postclone in Apps Tier
11.Perl $COMMON_TOP\clone\bin\adcfgclone.pl appsTier
Finishing Tasks:
1.Update the profile options
Rapid clone will update the site level profile options only, we need to update instance level profile options manually
2.Update the printer settings
3.Update the workflow configuration settings
Cloning an Oracle Applications instance will not update the host and instance specific information used by Oracle Workflow. Review the following tables and columns to verify there is no instance specific data in the Workflow configuration on the target system.

Important Notes:
a)While running Postclone in appsTier it prompts hostname, we should give like hostname.domainname for example sys10.sample.com
b)While running post clone in appsTier it prompts visual studio directory don’t include bin folder. For example give like c:\vc\VC, don’t give like c:\vc\VC\bin

142. Provide an introduction to AutoConfig. How does AutoConfig know which value from the XML file needs to be put in which file?

AutoConfig uses a context file to maintain key configuration files. A context file is an XML file in the $APPL_TOP/admin directory and is the centralized repository. When you run AutoConfig it reads the XML files and creates all the AutoConfig managed configuration files. For each configuration file maintained by AutoConfig, there exists a template file which determines which values to pick from the XML file.

143. Can you tell me a few tests you will do to troubleshoot self-service login problems? Which profile options and files will you check?

Check guest user/password in the DBC file, profile option guest user/password, the DB.

Check whether apache jserv is up.

Run IsItWorking, FND_WEB.PING, aoljtest, etc.

144. What could be wrong if you are unable to view concurrent manager log and output files?

Most likely the FNDFS listener is down. Look at the value of OUTFILE_NODE_NAME and LOGFILE_NODE_NAME in the FND_CONCURRENT_REQUESTS table. Look at the FND_NODES table. Look at the FNDFS_ entry in tnsnames.ora.

145. How will you change the location of concurrent manager log and output files?

The location of log files is determined by parameter $APPLCSF/$APPLLOG and that of output files by $APPLCSF/$APPLOUT.

146. If the user is experiencing performance issues, how will you go about finding the cause?

Trace his session (with waits) and use tkprof to analyze the trace file.

Take a statspack report and analyze it.

o/s monitoring using top/iostat/sar/vmstat.

Check for any network bottleneck by using basic tests like ping results.

147. How will you change the apps password?

Use FNDCPASS to change APPS password.

Manually modify wdbsvr.app/cgiCMD.dat files.

Change any DB links pointing from other instances.

148. Explain the steps for applying apps patch and database patch?

a) Login as applmgr and set the environment. For the Windows environment also, you have to test that CLASSPATH contains %JAVA_TOP%, %JAVA_TOP%\loadjava.zip
b) Create a PATCH_TOP directory in the Base Directory (at the same level as APPL_TOP, COMMON_TOP, etc: this is just a recommendation) for the patches which will be downloaded. If this directory exists, this step can be skipped. An OS environment variable could be created for this directory. This will be done only one time, when the first patch will be applied.
c) Download the patch you want to apply in PATCH_TOP directory and unzip the patch.
d) Understand the README.txt file and complete the prerequisite or manual steps. Here, if there are any patched to apply as pre-requisite, in general, is created a document with all the steps involving in the patching process and the pre-requisite patches will be applied before the initial patch.
e) Ensure that the PLATFORM variable environment (under UNIX, Linux, Solaris) is set
f) Shut down APPS services. The database services and the listener must be up and running.
g) Enable Maintenance Mode.
h) Start AutoPatch in interactive mode. This task must be done from the directory where the patch driver is/was unzipped. Also, respond to the adpatch prompts. If there are more drivers to apply (there is no unified drive: there could be a database (d), copy (c) or generate (g) driver) restart the adpatch and apply the other patches.
i) Review the log files. By default, the location is $APPL_TOP/admin//log and the file is adpatch.log.
j) Review the customizations (if any). If a customization was modified by this patch, the customization must be applied again.

149. What is autoconfig?

AutoConfig is the tool, which is used to configure oracle application systems. It’s basically a perl script. At present there are more than 300 configuration files and environment files in application system. Whenever a change is made to any of the configuration parameter, the change needs to be propagated correctly to correct configuration and environment files. Managing such changes in such large number of files is really difficult.

AutoConfig uses a file called context file, which is used to configure changes. Context file is an XML file having all the parameters of application system. These parameters are part of some configuration file for some services or it may be part of some environment file. When we create an application system (using Rapid install), context file gets created automatically with all the parameters and there values. Most of the parameters take default value, where as some parameters takes the value that is supplied at the time of installation.

150. How to enable diagnostics for oracle applications?

FND: Enable Diagnostics.

151. What is .lgi file?

lgi files are created with patching along with .log files . .lgi files are informative log files containing information related to patch. You can check .lgi files to see what activities patch has done.

152. How will you skip worker during patch?

If in your adctrl there are six options shown then seventh is hidden option. (If there are seven options visible then 8th option is to Skip worker depending on ad version).

153. Which two tables are created at start of Apps Patch and drops at end of Patch?

FND_INSTALLED_PROCESS and AD_DEFFERED_JOBS are the tables that get updated while applying a patch mainly d or unified driver.

154. How to compile an Oracle Reports file?

Utility adrepgen is used to compile Reports. Syntax is given below adrepgen userid=apps\<psswd> source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

155. What is difference between AD_BUGS and AD_APPLID_PATCHES?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (i.e. patched) in the Oracle Applications installation.
AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (e.g. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

156. How you will troubleshoot if concurrent request is taking long time?

First you will try to check how far the query has gone (if in v$session_longops for example, or reading some session statistics) and if there is contention with another session (such as a lock for example) because it talks about a 'concurrent request'.

Method 1: Trace the session using dbms_system.set_sql_trace_in_session (or) if u r using 10g, using dbms_monitor and look at the trace file to identify where the issue is. You can get the explain plan and tune the query.
Method 2:
1. Is this a new query (or) an existing query
2. Look for the wait event associated to this query.
3. What is the elapsed time for this query?
4. Are there are any db deadlocks?
5. Are there any long operations?
6. Are there any long transactions?
7. Based on the sql hash value, look at the sql_text from v$sql
8. Run an explain plan (or) use oradebug and trace the query if what bind variables are using and how the
data distribution is.
9. Based on the above information, look for when statistics was collected
10. If it is an old query, check if the plan has changed and if so, why?
Once u understand where the problem is, then solutions can be defined ranging from creating sql profiles / stored outlines (or) adding hints to the query and thoroughly test your changes.
Always remember to make one change and test for performance change. If you make a series of changes, it will be very difficult to isolate which has caused the performance improvement.

157. Why I need shared APPL_TOP?

If you have two/three Middle Tier for Large User base then you have to install APPL_TOP, COMMON_TOP & ORA_TOP equal to number of Nodes in your configuration. So if you have three middle tiers, then you need these three top three times & if there are N nodes you need N time these three tops. This result in lots of Space Requirement & bigger problem is patching so if you have 5 nodes you need to apply apps patch 5 times.

So Oracle came up with solution to have single APPL_TOP which is shared across all nodes in apps. This will save space as well as patch timing. This concept of sharing APPL_TOP file system across all Middle Tier Nodes is called APPL_TOP.

You will say if I can share APPL_TOP why can't I share COMMON_TOP & ORA_TOP as well which are also part of Application Tier, Yes you can & this concept is called as Shared Application Tier.

158. What is Shared Application Tier?

Sharing Application Tier (including APPL_TOP, COMN_TOP, ORA_TOP) across all Middle Tier Nodes in configuration is called as Shared Application Tier. So I can say shared APPL_TOP is subset of shared Application Tier, initially shared APPL_TOP came out & based on its advantages & popularity later COMMON_TOP & ORA_TOP files were also included & entire concept is called as Shared Application Tier.

159. Limitation with Shared APPL_TOP and Application Tier?

If you are running Apps on Windows you'll be sad to hear that it’s not certified with Windows Operation System. More over you can share across same operation system like Linux to Linux & Solaris to Solaris.

160. What are the request incompatibilities? How conflict resolution concurrent manager solve them?

Checks if a request in queue can be run in parallel with the running request.

161. Where and how you update workflow notification mailer configuration setting?

This depends on which workflow mailer you are running C Mailer or Java Mailer

162. If you want to change Concurrent Manager log and out file location, is it possible, If Yes, How? If No, why not?

By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well.

163. How frequent you run Gather Schema Statistics program & with what option? Why you need to run it? What is cost based optimizer?

In Oracle Database there is something called Cost Based Optimizer (CBO) which other than doing many things also does query optimization. That is it determines the best or optimum method to run a query. Now when large changes to the data or database take place, the system performance may get slow. DBMS_STATS is the package which collects statistics for Cost Based Optimizer. Collecting Statistics lets the CBO know about the best methods for query optimization. The statistics include the Table statistics, Column statistics, Index statistics and System statistics. The DBMS_STATS package includes the following procedures

1.GATHER_INDEX_STATS, Index statistics

2.GATHER_TABLE_STATS, Table, column, and index statistics

3.GATHER_SCHEMA_STATS,Statistics for all objects in a schema

4.GATHER_DICTIONARY_STATS,Statistics for all dictionary objects

5.GATHER_DATABASE_STATS,Statistics for all objects in a database

Of all of the above usually the most used is GATHER_SCHEMA_STATS to gather statistics of all objects within a particular Schema. The Gather Schema Statistics process should be run on a regular basis (weekly at a minimum) and anytime large changes to the data or database take place.

164. If we run autoconfig which files will get effected?

In order to check list of files changes during autoconfig , you can run adchkcfg utility which will generate HTML report. This report will list all files and profile options going to change when you run AutoConfig.

165. What is difference between .xml file and AutoConfig?

Autoconfig is Utility to configure your Oracle Application environment. xml file is repository of all configuration from which AutoConfig picks configuration and populates related files.

166. What are few profile options which you update after cloning?

Rapid clone updates profile options specific to site level. If you have any profile option set at other levels like server, responsibility, user level then reset them.

167. How you will avoid your query from using indexes?

By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.

168. What is an OUTER JOIN?

An OUTER JOIN returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Core DBA

1. What is compile schema option in adadmin?

This option is used to compile/resolve the invalid objects

2. How do you compile an object?

alter object_ type objet _name compile e.g. alter table fnd_nodes compile

3. How do you see the errors of a table or view?

select text from dba_errors where name='emp_view'

4. How do you see the errors in the db?

show error

5. How do you compile a schema?

Using utlrp.sql (location is ?/rdbms/admin/) or going adadmin, compile schema option

6. How do you know how many invalid objects are in specific schema?

select count(*) from dba_objects where status='INVALID' group by owner;

7. When do you use WHERE clause and when do you use HAVING clause?

The WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions. Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE.

8. There is a % sign in one field of a column. What will be the query to find it?

SELECT column_name FROM table_name WHERE column_name LIKE ‘%\%%’ ESCAPE ‘\’;

9. Explain the difference between a hot backup and a cold backup and the benefits associated with each?

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

10. Difference between Truncate and Delete

Truncate does not generate undo, unlike delete operation
Delete triggers are not fired for truncate
Truncate releases used space and has implicit commit (ddl oper)

11. How many memory layers are in the shared pool?

Shared pool consist of 2 memory layers-library cache and data dictionary cache

Library cache -contains parsed sql statements, cursor information, execution plans,

Data dictionary cache -user account information, privileges information data file segment and extent information

12. How do you find out from the RMAN catalog if a particular archive log has been backed-up?

RMAN> list archivelog all;

13. How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?

In UNIX you go to the specific mount point and say df -k to find the space in the mount point.

14. Define the SGA and how you would configure SGA for a mid-sized OLTP environment and what is involved in tuning the SGA?

To configure SGA we need to setup the SGA_MAX_SIZE parameter in the init.ora file.

15. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?

If the Oracle process can find the data already in the Database buffer cache, it can read the data directly from the memory and it is called a cache hit. If there is more cache hit the burden on the database reduces.

16. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?

We check the Alert log files, have an eye on the backup logs and constant monitoring of the tablespaces and the number of sessions connected to the database

17. How do you tell what your machine name is and what is its IP address?

IPconfig or IFconfig

18. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

ipcs and SQL> oradebug SETMYPID followed by SQL> oradebug IPC

19. How would you verify the network name that the local_listener is currently using?

$LSNRCTL
LSNRCTL>status
There u would have the opportunity to have a look even on services. If u have more than 1 listener you would first have to specify the listener u are about to monitor with this command
$set listener <name_of_listener>
then re-enter the command I previously gave

20. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?

v$process and v$session
Select a.spid from v$process a, v$session b where a.addr = b.addr and b.audsid=userenv(‘sessionid’);

21. What is the recommended interval at which to run statspack snapshots, and why?

The single most common misuse of STATSPACK is the «more is better» approach. Often STATSPACK reports spans hours or even days. The times between the snapshots (the collection points) should, in general, be measured in minutes, not hours and never days.
The STATSPACK reports we like are from 1 5-minute intervals during a busy or peak time, when the performance is at its worst. That provides a focused look at what was going wrong at that exact moment in time. The problem with a very large STATSPACK snapshot window, where the time between the two snapshots is measured in hours, is that the events that caused serious performance issues for 20 minutes during peak processing don't look so bad when they're spread out over an 8-hour window. It's also true with STATSPACK that measuring things over too long of a period tends to level them out over time. Nothing will stand out and strike you as being wrong. So, when taking snapshots, schedule them about 15 to 30 minutes (maximum) apart. You might wait 3 or 4 hours between these two observations, but you should always do them in pairs and within minutes of each other.

22. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?

optimizer_mode TYPICAL

23. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at
2AM.

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate)+9/24, 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

24. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?

Use vi to edit Crontab file append a line on the file
00 2 * * * /test/test.sh

25. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?

DBMS_STANDARD package provides language facilities that help your application interact with Oracle.

26. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?

select owner, mview_name, last_refresh_type, last_refresh_date from dba_mviews;

27. How would you best determine why your MVIEW couldn’t FAST REFRESH?

DBMS_MVIEW.EXPLAIN_MVIEW

28. Which dictionary tables and/or views would you look at to diagnose a locking issue?

sys.v_$lock, sys.v_$session sys.obj$ , sys.user$ , sys.v_$process

29. How would you extract DDL of a table without using a GUI tool?

30. You’re getting high “busy buffer waits” - how can you find what’s causing it?

1. By using statspack report find buffer busy wait events.

2. select * from V$system_event where event like '%wait%';

31. What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining?

dba_data_files

dba_free_space

32. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.

33. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.

If the database allows u to identify the corrupted blocks and are happened to be less in no then u can go for block wise recovery using RMAN. Otherwise go for complete recovery.

34. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).

In Solaris you can do lots of things to get the hardwar info. My favorite is /usr/platform/sun4u/sbin/prtdiag -v. Other people would probably choose psrinfo or prtconf to get that info. I'm not sure what the equivalents would be for unix, but again there are several hardware related commands you can choose from I'm sure.

35. How do you increase the OS limitation for open files (LINUX and/or Solaris)?

There is a configuration related file in /etc/ directory in which you can specify the maximum number of open files.

36. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.

37. Explain how you would restore a database using RMAN to Point in Time?

You can recovery until a specific SCN, a specific log sequence, a restore point, or a specific time. To recover to a specific point in time, use: RECOVER DATABASE UNTIL TIME "TO_DATE('12/01/07 13:15','MM/DD/YY HH24:MI')";

RUN

    SET UNTIL SCN 1000;

  # SET UNTIL TIME 'Nov 15 2004 09:00:00';

  # SET UNTIL SEQUENCE 9923;

  RESTORE DATABASE;

  RECOVER DATABASE;

alter database open resetlogs;

38. How does Oracle guarantee data integrity of data changes?

39. Which environment variables are absolutely critical in order to run the OUI?

ORACLE_HOME, ORACLE_SID, ORACLE_BASE,PATH & LD_LIBRARY_PATH

40. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?

SELECT username, COUNT(*) count FROM v$session GROUP BY username;

41. Why does Oracle not permit the use of PCTUSED with indexes?

This is because an index is a complex data structure, not randomly organized like a heap table (a normal table u get when u type create table syntax) Data must go where it ‘belongs’. Unlike a heap where blocks are sometimes available for inserts, blocks are always available for new entries in an index. If the data belongs on a given block because of its values, it will go there regardless of how full or empty the block is. PCTFREE will reserve space on a newly created index, but not for subsequent operations on it for much the same reason as why PCTUSED is not used at all. The same thigh is true even for index organized tables too…

42. What would you use to improve performance on an insert statement that places millions of rows into that table?

Check that any indexes are there.

Drop the indexes and recreate after insert.

DML Triggers to be DISABLED and then ENABLED once the insert completed.

DISABLE the Clustered Index and then ENABLED once the insert completed.

After completion of INSERT, Defragmentation should be there.

After completion of INSERT, Statistics should be updated.

(Optional) If it is non-peek time & This is the only activity is going on then, Database recovery mode should be SIMPLE / BULK-LOGGED. (It should not be there along with other activities....)

43. What would you do with an “in-doubt” distributed transaction?

44. What are the commands you’d issue to show the explain plan for “select * from dual”?

set autotrace traceonly explain;

select * from dual;

45. In what script is “snap$” created? In what script is the “scott/tiger” schema created?

exec statspack.snap;

demobld.sql

46. If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?

grep

47. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?

In tnsnames use host=DSS.icallinc.com

48. You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?

This happens after the DB_NAME of the database was changed (as in note# 15390.1 ie re-create controfile with set database option etc ...).After This, the NAME column value is the new DB_NAME in V$DATABASE. The problem comes from the PROPS$ where GLOBAL_DB_NAME is still the old database name. So the WA is to execute the ALTER DATABASE RENAME GLOBAL_NAME (global_name=false is not a WA if global_naming is required).

49. I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?

backup_rman.sh >&1&

50. Explain the concept of the DUAL table.

Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X.

51. What are the ways tablespaces can be managed and how do they differ?

2 ways Locally Managed or Managed in the dictionary

Locally-managed tablespaces have the following advantages over dictionary-managed tablespaces:

Local management of extents avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a rollback segment or data dictionary table. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

52. From the database level, how can you tell under which time zone a database is operating?

select DBTIMEZONE from dual;

53. What’s the benefit of “dbms_stats” over “analyze”?

Starting with the introduction of the dbms_stats package, Oracle provides a simple way for the Oracle professional to collect statistics for the CBO. The old-fashioned analyze table and dbms_utility methods for generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don't always capture high-quality information about tables and indexes. The CBO uses object statistics to choose the best execution plan for all SQL statements. The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.

54. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?

\oracle\ora91\bin ( windows

$Oracle_Home\

55. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?

56. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.

Assume that the file ‘/u01/ORADATA/data/data01.dbf’ of tablespace DATA is damaged and you don't have a backup of the data file.
SVRMGRL
SVRMGRL> connect internal
connected
SVRMGRL> shutdown abort
Oracle Instance Aborted
SVRMGRL> startup mount
Oracle Instance Started
SVRMGRL> alter database create datafile ‘/u01/ORADATA/data/data01.dbf’;
Statement Processed
SVRMGRL> recover datafile ‘/u01/ORADATA/data/data01.dbf’;
Statement Processed
SVRMGRL> select * from v$datafile;
You will see a list of datafiles with their status. If the status of the concerned file has not been reset to ONLINE, issue the following command.
SVRMGRL> alter database datafile ‘/u01/ORADATA/data/data01.dbf’ online;
Statement Processed
SVRMGRL> select * from dba_tablespaces;
If the status of the tablespace CASE is not ONLINE, then issue the following command.
SVRMGRL> alter tablespace CASE online;
Statement Processed
SVRMGRL> alter database open;
Statement Processed

57. What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

1000000

58. Can you use a commit statement within a database trigger?

Yes, if you are using autonomous transactions in the Database triggers.

59. What is an UTL_FILE? What are different procedures and functions associated with it?

The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).
Subprogram -Description
FOPEN function-Opens a file for input or output with the default line size.
IS_OPEN function -Determines if a file handle refers to an open file.
FCLOSE procedure -Closes a file.
FCLOSE_ALL procedure -Closes all open file handles.
GET_LINE procedure -Reads a line of text from an open file.
PUT procedure-Writes a line to a file. This does not append a line terminator.
NEW_LINE procedure-Writes one or more OS-specific line terminators to a file.
PUT_LINE procedure -Writes a line to a file. This appends an OS-specific line terminator.

60. Difference between database triggers and form triggers?

Database triggers are fired whenever any database action like INSERT, UPATE, DELETE, LOGON LOGOFF etc occurs. Form triggers on the other hand are fired in response to any event that takes place while working with the forms, say like navigating from one field to another or one block to another and so on.

61. What is OCI. What are its uses?

OCI is Oracle Call Interface. When applications developers demand the most powerful interface to the Oracle Database Server, they call upon the Oracle Call Interface (OCI). OCI provides the most comprehensive access to all of the Oracle Database functionality. The newest performance, scalability, and security features appear first in the OCI API. If you write applications for the Oracle Database, you likely already depend on OCI.

62. What are ORACLE PRECOMPILERS?

A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc.

63. What is syntax for dropping a procedure and a function? Are these operations possible?

Drop Procedure/Function ; yes, if they are standalone procedures or functions. If they are a part of a package then one have to remove it from the package definition and body and recompile the package.

64. Can a function take OUT parameters. If not why?

yes, IN, OUT or IN OUT.

65. Can the default values be assigned to actual parameters?

Yes. In such case you don’t need to specify any value and the actual parameter will take the default value provided in the function definition.

66. What is difference between a formal and an actual parameter?

The formal parameters are the names that are declared in the parameter list of the header of a module. The actual parameters are the values or expressions placed in the parameter list of the actual call to the module.

67. What are different modes of parameters used in functions and procedures?

There are three different modes of parameters: IN, OUT, and IN OUT.

68. Difference between procedure and function.

A function always returns a value, while a procedure does not. When you call a function you must always assign its value to a variable.

69. How do you pass cursor variables in PL/SQL?

Pass a cursor variable as an argument to a procedure or function. You can, in essence, share the results of a cursor by passing the reference to that result set.

70. How do you open and close a cursor variable. Why it is required?

Using OPEN cursor_name and CLOSE cursor_name commands. The cursor must be opened before using it in order to fetch the result set of the query it is associated with. The cursor needs to be closed so as to release resources earlier than end of transaction, or to free up the cursor variable to be opened again.

71. What should be the return type for a cursor variable. Can we use a scalar data type as return type?

The return type of a cursor variable can be %ROWTYPE or record_name%TYPE or a record type or a ref cursor type. A scalar data type like number or varchar can’t be used but a record type may evaluate to a scalar value.

72. Difference between an implicit and an explicit cursor.

The implicit cursor is used by Oracle server to test and parse the SQL statements and the explicit cursors are declared by the programmers.

73. What is a cursor?

A cursor is a mechanism by which you can assign a name to a “select statement” and manipulate the information within that SQL statement.

74. What is the purpose of a cluster?

A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use clusters for tables that are frequently accessed individually.

75. How do you find the number of rows in a Table ?

select count(*) from table, or from NUM_ROWS column of user_tables if the table statistics has been collected.

76. What is a pseudo column. Give some examples?

Information such as row numbers and row descriptions are automatically stored by Oracle and is directly accessible, i.e. not through tables. This information is contained within pseudo columns. These pseudo columns can be retrieved in queries. These pseudo columns can be included in queries which select data from tables.

Available Pseudo Columns

· ROWNUM - row number. Order number in which a row value is retrieved.
· ROWID - physical row (memory or disk address) location, ie. unique row identification.
· SYSDATE - system or today’s date.
· UID - user identification number indicating the current user.
· USER - name of currently logged in user.

77. What is difference between UNIQUE and PRIMARY KEY constraints?

An UNIQUE key can have NULL whereas PRIMARY key is always not NOT NULL. Both bears unique values.

78. What is difference between Rename and Alias?

Rename is actually changing the name of an object whereas Alias is giving another name (additional name) to an existing object. Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which does not exist once the SQL statement is executed.

79. What is an Oracle Instance?

An Oracle instance is the combination of the background processes and memory structures.

80. When you start an Oracle DB which file is accessed first?

Reading the initialization file from $ORACLE_HOME/dbs in the following order:

– First spfileSID.ora. If not found then

– spfile.ora

– initSID.ora

81. What information is stored in Control File?

The information in the control file includes:

DB_NAME or the name used in the CREATE DATABASE statement.

Database identifier is recorded when the database is created.

Time stamp of database creation is also recorded at database creation.

Names and locations of associated data files and online redo log files are updated when a data file or redo log is added to, renamed in, or dropped from the database.

Tablespace information is updated as tablespaces are added or dropped.

Redo log history is recorded during log switches.

Location and status of archived logs are recorded when archiving occurs.

Location and status of backups are recorded by the Recovery Manager utility.

Current log sequence number is recorded when log switches occur.

Checkpoint information is recorded as checkpoints are made.

82. What is the Job of SMON, PMON processes?

The system monitor process (SMON) performs recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces. If any terminated transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or file is brought back online. SMON checks regularly to see whether it is needed. Other processes can call SMON if they detect a need for it. With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.

The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using. For example, it resets the status of the active transaction table, releases locks, and removes the process ID from the list of active processes.

PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener. Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.

83. What is Instance Recovery?

When an Oracle instance fails, Oracle performs an instance recovery when the associated database is re-started. Instance recovery occurs in two steps:

Cache recovery: Changes being made to a database are recorded in the database buffer cache. These changes are also recorded in online redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.

Transaction recovery: When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment. The data stored in the undo segment is used to restore the original values in case a transaction is rolled back. At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have got saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

84. What is written in Redo Log Files?

Each Oracle database has a redo log. This redo log records all changes made in datafiles.

85. How many Maximum Datafiles can there be in an Oracle Database?

The maximum number of datafiles in an Oracle Database is usually 64K files

86. What is a Tablespace?

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server.

87. What is the purpose of Redo Log files?

Redo logs are transaction journals. Each transaction is recorded in the redo logs.

88. Which default Database roles are created when you create a Database?

CONNECT, RESOURCE, DBA

EXP_FULL_DATABASE

IMP_FULL_DATABASE

DELETE_CATALOG_ROLE

EXECUTE_CATALOG_ROLE EXECUTE

SELECT_CATALOG_ROLE

89. What is a Checkpoint?

An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data, to the data files.

Checkpoints are implemented for the following reasons:

Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur.

Because all database changes up to the checkpoint have been recorded in the data files, redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery.

At a checkpoint, the following information is written:

• Checkpoint number into the data file headers

• Checkpoint number, log sequence number, archived log names, and system change numbers into the control file.

CKPT does not write data blocks to disk or redo blocks to the online redo logs.

90. Which Process reads data from Datafiles?

The server process first checks the buffer cache for the presence of data. If not found then only copy the data from datafile to buffer cache. Then send the data to the client.

91. Which Process writes data in Datafiles?

DBWn writes data in Datafile.

92. Can you make a Datafile auto extendible. If yes, how?

Yes, use EXTENT MANAGEMENT LOCAL AUTOALLOCATE while creating tablespace.

93. What is a Shared Pool?

Shared pool stores parsed version of SQL statements, PL/SQL procedures, and data dictionary information.

94. What is kept in the Database Buffer Cache?

Memory area used to store blocks read from data files. Data is read into the blocks by server processes and written out by DBWn asynchronously.

95. How many maximum Redo Logfiles one can have in a Database?

Depends on what you specified for MAXLOGFILES during database creation (manually) or what you specified for "Maximum no. of redo log files" with DBCA

96. What is difference between PFile and SPFile?

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

97. What is PGA_AGGREGRATE_TARGET parameter?

PGA_AGGREGATE_TARGET is an Oracle server parameter that specifies the target aggregate PGA memory available to all server processes attached to the instance.

98. Large Pool is used for what?

An optional area in the SGA that provides large memory allocations for backup and restore operations, I/O server processes, and session memory for the shared server.

99. What is PCT Increase setting?

The percent of increase in extent size after NEXT extent and thereafter.

100. What is PCTFREE and PCTUSED Setting?

PCTFREE for a data segment specifies the percentage of space in each data block reserved for growth resulting from updates to rows in the block. The default for PCTFREE is 10%.

PCTUSED for a data segment represents the minimum percentage of used space that the oracle server tries to maintain for each data block of the table. A block is put back on the free list when its used space falls below PCTUSED.

101. What is Row Migration and Row Chaining?

A migrated row is a row that was moved to another block due to an update making it too large to fit on its original block with the other rows there. We cannot just "move" the row -- we have lots of indexes pointing to the original block. We therefore leave behind a forwarding address on the original block and migrate the updated row to a new block. Now, when you access that row, we discover it is not really there -- it is migrated and we read the other block to get it.

A chained row is a row that is too large to fit on a single block. If you have a 2k blocksize but your row size is 4k, we will use 3 blocks to store that row in pieces. Any table with a long/long raw will have chained rows. Any table whose rowsize exceeds the blocksize will have chained rows. Any table with more then 255 columns will have chained rows (we break really wide tables up).

102. What is 01555 - Snapshot Too Old error and how do you avoid it?

Snapshot_too_old exception is thrown when ur performing very large DML operation without commiting this can be resolved by increasing undo retention period. contact ur DBA to check for Undo retention period.

There may two Cause for SNAPSHOT-TOO-OLD error:-

(1) Since space managemnt in undo segment follo LRU algorihm.So in space allocated for to undo segment will so small to data may frequently swaped out from undo segement and may cause SNAPSHOT-TOO-OLD error.

(2) It also depend on the undo retention policy set by the DBA.If the undo retention time is set to very low the the data will sweped out too early and may cause SNAPSHOT-TOO-OLD error.

103. What is a Locally Managed Tablespace?

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces. Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Advantages of Locally Managed Tablespaces:

Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)

Reduce contention on data dictionary tables (single ST enqueue)

Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)

Changes to the extent bitmaps do not generate rollback information

104. Can you audit SELECT statements?

105. What does DBMS_FGA package do?

The DBMS_FGA package provides fine-grained security functions. This package is available for only cost-based optimization. The rule-based optimizer may generate unnecessary audit records since audit monitoring can occur before row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL text and corresponding bind variables that are issued.

106. What is Cost Based Optimization?

Whenever a valid SQL statement is processed Oracle has to decide how to retrieve the necessary data. This decision can be made using one of two methods:

Rule Based Optimizer (RBO) - This method is used if the server has no internal statistics relating to the objects referenced by the statement. This method is no longer favoured by Oracle and will be desupported in future releases.

Cost Based Optimizer (CBO) - This method is used if internal statistics are present. The CBO checks several possible execution plans and selects the one with the lowest cost, where cost relates to system resources.

If new objects are created, or the amount of data in the database changes the statistics will no longer represent the real state of the database so the CBO decision process may be seriously impaired.

107. How often you should collect statistics for a table?

Whenever the data changes "significantly".

If a table goes from 1 row to 200 rows, that's a significant change. When a table goes from 100,000 rows to 150,000 rows, that's not a terribly significant change. When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X, that's a significant change.

Statistics store information about item counts and relative frequencies -- things that will let it "guess" at how many rows will match a given criteria. When it guesses wrong, the optimizer can pick a very suboptimal query plan.

108. How do you collect statistics for a table, schema and Database?

Using DBMS_STATS package

109. Can you make collection of Statistics for tables automatic?

Yes, using Oracle 10g Scheduler

110. On which columns you should create Indexes?

On the columns which are often used in queries.

You should create indexes on columns that are used frequently in WHERE clauses.

You should create indexes on columns that are used frequently to join tables.

You should create indexes on columns that are used frequently in ORDER BY clauses.

You should create indexes on columns that have few of the same values or unique values in the table.

You should not create indexes on small tables (tables that use only a few blocks) because a full table scan may be faster than an indexed query.

111. What type of Indexes are available in Oracle?

1.Normal index

Whenever we created a column(s) with Primary key or Unique constraints, Oracle implicitly creates Normal

index.This index is not Unique index .Why because those columns already having uniqeness because of Unique or

Primary key constriants.

2.Bitmap indexs

3.Function based indexs

4.Domain indexes.

if we want to create Unique indexes we have to use Unique keyword when we are creating indexes.

But if you refer Oracle 9i corporation books you can read as follows...

They gave these statement in wrong.

Indexes can be created in two types

1.Unique indexes(Oracle creates it implicitly whenever we created column(s) with PK or Unique constraints

2. Non unique indexes This indexes can be created by developers

112. What is B-Tree Index?

In computer science, a B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic amortized time. The B-tree is a generalization of a binary search tree in that more than two paths diverge from a single node. (Comer, p. 123) Unlike self-balancing binary search trees, the B-tree is optimized for systems that read and write large blocks of data. It is commonly used in databases and filesystems. b-tree indexes are used for high cardinaties, usuall when we have too many distinct columns. and bitmap indexex are used for low cardinaties, usuallywhen we have repeated columns

113. A table is having few rows, should you create indexes on this table?

114. A Column is having many repeated values which type of index you should create on this column, if you have to?

Bit map index

115. When should you rebuild indexes?

Every once and a while I get asked the question, "How do you when it time to rebuild an index?" I like to use the rule of thumb that if the blevel column in DBA_INDEXES is greater 4, it's time to rebuild the index. Make sure that you analyze the index before checking the blevel columns (analyze index owner.table_name estimate statistics sample 20 percent). There is some other great index information that can be accessed via the index_stats table, after issuing the "analyze index ... validate structure" command.

116. Can you built indexes online?

Yes

117. Can you see Execution Plan of a statement?

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query.

118. A table is created with the following setting
storage (initial 200k next 200k minextents 2 maxextents 100 pctincrease 40)
What will be size of 4th extent?

119. What is DB Buffer Cache Advisor?

DB Buffer Cache Advisor is one of the many advisors which advise the DBA about the optimum size of the DB Buffer. Now what is DB Buffer? The buffer cache is part of the SGA. It holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk. In short,The Buffer Cache Advisor provides advice on how to size the Database Buffer Cache to obtain optimal cache hit ratios.

120. What is STATSPACK tool?

It is basically used in performance tuning. It is used to get input/op statistics especially being the top five waiting events. Statspack is an performance evaluation report used for verifying load on the server,SGA utilization,wait events and resource consuming SQL. STATSPACK is a set of tools that captures and reports on database statistics.

121. Can you change SHARED_POOL_SIZE online?

You can change shared_pool_size on the fly in 9i using alter system ... scope=both (probably in 8i aswell) but you are limited by sga_max_size which you cant change dynamically (need to bounce the database).

122. Can you Redefine a table Online?

Using online redefinition, we no need outage & also user can access the table for DML operation.
For large, active databases, it is sometime necessary to redefine large “hot” tables to improve the performance of queries or data manipulation language (DML) operations performed against these tables. Additionally business applications may require underlying database structure to be changed or transformed periodically.
Oracle Online Redefinition mechanism provides a significant increase in availability compared to traditional methods of redefining tables that require tables to be taken offline. When a table is redefined online, it is accessible by all read and write operations during the redefinition process. Administrators then have control over when to switch from the original to the newly redefined table.
The switch process is very brief and is independent of the size of the table or the complexity of the redefinition. The redefinition process effectively creates a new table and improves its data block layout efficiency

123. Can you assign Priority to users?

124. You want users to change their passwords every 2 months. How do you enforce this?

125. How do you delete duplicate rows in a table?

A way to delete duplicate rows in oracle table You can also detect and delete duplicate rows using Oracle analytic functions:
delete from customer where rowid in
(select rowid from (select rowid, row_number() over (partition by custnbr order by custnbr) dup
from customer) where dup > 1);

126. What is Automatic Management of Segment Space setting?

127. What is the difference between DELETE and TRUNCATE statements?

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason: When you type DELETE. all the data get copied into the Rollback Tablespace first then delete operation get performed. That’s why when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That’s why TRUNCATE is faster. Once you Truncate you can’t get back the data.

3>You can't rollback in TRUNCATE but in DELETE you can rollback. TRUNCATE removes the record permanently.

4>In case of TRUNCATE ,Trigger doesn't get fired. But in DML commands like DELETE .Trigger get fired.

5>You can't use conditions(WHERE clause) in TRUNCATE. But in DELETE you can write conditions using WHERE clause

128. What is COMPRESS and CONSISTENT setting in EXPORT utility?

COMPRESS - Specifies how Export will manage the initial extent for the table data. This parameter is helpful during database re-organization. Export the objects (especially tables and indexes) with COMPRESS=Y. If your table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced. Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on your disk (tablespace), and you do not want your imports to bomb.

CONSISTENT - If massive updates/rollbacks are taking place when you have kicked off the exports, then the exports will not be consistent under normal conditions. If you set CONSISTENT=Y then a rollback segment is designated to backout the effect of any uncommitted transactions. I.e. a value changed to 100 when you have kicked off the exports and before the exports finishes, the changes being undone. The negative effect are exports being slower cause they have to check the consistency by cross referring the rollback segments.

129. What is the difference between Direct Path and Convention Path loading?

Direct - In direct path export the data is read directly bypassing the evaluation buffer. It also optimizes the use of SELECT * FROM TABLE statement. It is used in conjunction with the database in direct read mode, which makes use of private buffers instead of public buffers and hence less resources are consumed and performance is improved.

Conventional - In conventional path export uses SQL statement "SELECT * FROM TABLE" to extract data from database tables. Data is then read from disk into a buffer (private buffer or buffer cache) and rows are transferred into evaluation buffer, The data after passing expression evaluation (equivalent Insert statement generated and validated), is transferred to the Export Client which then writes the data into the export file.

130. Can you disable and enable Primary key?

ALTER TABLE supplier disable CONSTRAINT supplier_pk;

131. What is an Index Organized Table?

Index Organized Tables or IOT where introduced in Oracle with the arrival of Oracle 8. These tables where introduced primarily for Internet applications that involve data access based on single column primary keys.

Storage organization has been always a key factor in faster access of data. With normal tables there are no indexes created at first. First you have to create a table, then create indexes for faster performance in data access.

Indexes have some drawbacks such as it stores data in two places, one in table and in index. If a query is issued (it is assumed that it uses the index), it checks the index and retrieves the address of data in table. Then the data is fetched from the tables to produce the query output.

But in case of an Index Organized Table, data is stored in the index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table.

132. What is a Global Index and Local Index?

The global/local indexes problem is generic - global indexes CAN be faster than local indexes. In general, local indexes will be faster when partition elimination can take place, and the expected volume of data acquired id significant. If either of this conditions is not met, then local indexes probably won't help performance.

B-tree indexes on partitioned tables can be global or local. With Oracle8i and earlier releases, Oracle recommended that global indexes not be used in data warehouse environments because a partition DDL statement (for example, ALTER TABLE ... DROP PARTITION) would invalidate the entire index, and rebuilding the index is expensive. In Oracle9i, global indexes can be maintained without Oracle marking them as unusable after DDL. This enhancement makes global indexes more effective for data warehouse environments.

However, local indexes will be more common than global indexes. Global indexes should be used when there is a specific requirement which cannot be met by local indexes (for example, a unique index on a non-partitioning key, or a performance requirement).

Bitmap indexes on partitioned tables are always local.

133. What is the difference between Range Partitioning and Hash Partitioning?

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development

" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

So now you know partitioning in oracle now the only thing that yo u need to know is little bit of syntax and that’s it, and you are a partitioning guru.

Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go thru the details now.

Advantages of using Partition’s in Table

1. Smaller and more manageable pieces of data ( Partitions )
2. Reduced recovery time
3. Failure impact is less
4. import / export can be done at the " Partition Level".
5. Faster access of data
6. Partitions work independent of the other partitions.
7. Very easy to use

Types of Partitioning Methods

1. RANGE Partitioning

This type of partitioning creates partitions based on the " Range of Column" values. Each partition is defined by a " Partition Bound" (non inclusive ) that basically limits the scope of partition. Most commonly used values for " Range Partition" is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter.

So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.

This is example of the syntax needed for creating a RANGE PARTITION.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
;

the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999.

2. HASH Partitioning

Under this type of partitioning the records in a table, are partitions based of a Hash value found in the value of the column, that is used for partitioning. " Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01,
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03,
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;

The above example creates four hash partitions based on the zip codes from where the orders were placed.

3. List Partitioning ( Only with 9i)

Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’

Lets take one example. In fact, we will modify the same example.

CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04,
PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.

134. What is difference between Multithreaded/Shared Server and Dedicated Server?

In an Oracle instance using shared server, Oracle sessions do not have a dedicated server process to execute their SQL statements. Instead, each statement is passed by a dispatcher to one of the shared server processes associated with the instance. Such an architecture is useful in systems that have to support many hundreds or thousands of concurrent sessions, as it reduces the number of server processes on the Oracle host. There is slightly more CPU overhead associated with the use of shared servers, but it sh

135. Can you import objects from Oracle ver. 7.3 to 9i?

Different versions of the import utility are upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next. Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

136. How do you move tables from one tablespace to another tablespace?

Use alter table move command

Export / Import

137. How do see how much space is used and free in a tablespace?

dba_free_space

v$tablespace

Unix Related Interview Questions for an Oracle DBA

1. How do you see how many instances are running?

In unix, you can use the command line
% ps -ef|grep ora_

2. How do you automate starting and shutting down of databases in Unix?

Automatic startup can be done with /etc/oratab/ entyr of the linux machine.
Automatic shutdown we can't configure in the entry.

3. You have written a script to take backups. How do you make it run automatically every week?

corntab

4. What is OERR utility?

The oerr utility (Oracle Error) is provided only with Oracle databases on UNIX platforms. oerr is not an executable, but instead, a shell script that retrieves messages from installed message files. Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. This utility is very useful as it can extract OS-specific errors that are not in the generic Error Messages and Codes Manual.

5. How do you see Virtual Memory Statistics in Linux?

vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity.

6. How do you see how much hard disk space is free in Linux?

df -lh

7. What is SAR?

The sar command writes to standard output the contents of selected cumulative activity counters in the operating system

8. What is SHMMAX?

SHMMAX is the maximum size of a shared memory segment on a Linux system

9. Swap partition must be how much the size of RAM?

Your swap partition should be at least as big as your RAM size. However it should be double the size of RAM

10. What is DISM in Solaris?

DISM Dynamic Intimate Shared memory which is used to support oracle in Solaris Envirnoment DISM is only supported from Solaris 9 and above version. On Solaris 9 systems, dynamic/pageable ISM (DISM) is available. This enables Oracle Database to share virtual memory resources between processes sharing the segment, and at the same time, enables memory paging. The operating system does not have to lock down physical memory for the entire shared memory segment.

11. How do you see how many memory segments are acquired by Oracle Instances?

sga

pga

Db_buffer_cache

Log_buffer_cache

12. How do you see which segment belongs to which database instances?

13. What is VMSTAT?

vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity.

14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?

There are two methods to configure the Kernel parameters in RHEL

1. by using the command "sysctl -w <parameter_name>= <value>

The above command will change the kernel parameters on the fly but the changes are not persistent with system reboots. That is why people always choose the second method to make changes to kernel parameters

2. By editing the file "/etc/sysctl.conf" file

A. Edit the file "/etc/sysctl.conf" by adding the parameters along with values

B. execute "/sbin/sysctl -p" to make sure that the changes are made using the values inside the above mentioned file.

The advantage with the second method is that the changes are persistent with system reboots.

15. How do you remove Memory segments?

To remove the shared memory segment, you could copy/paste shmid and execute:

$ ipcrm shm 32768

Another approach to remove shared memory is to use Oracle's sysresv utility.

What is the difference between Soft Link and Hard Link?
Soft link:-This is a Symbolic link between files. The actual file or directory must be residing at any available partitions of the harddisk Soft Link is just a shortcut (in windows terms) or link created with a new file name at the working directory or at current working partition of hard disk. Even when you don't require it you can confidently delete this soft link as it doesn't remove the actual file or directory. The reason is the actual file or diretcory's inode is different from the softlink created file's inode in any unix system. Hard link:-It is the replica of the actual file or directory which must be residing at any available partitions. This is a duplicate file copy of it's orginal which can be created at current working partition.When we remove or delete this hardlink it removes the original file or directory too.The reson is they share the same inode in any unix file system.

16. What is stored in oratab file?

This file is used by ORACLE utilities. It is created by root.sh and updated by the Database Configuration Assistant when creating a database.
A colon, ':', is used as the field terminator. A new line terminates the entry. Lines beginning with a pound sign, '#', are comments.
Entries are of the form:
$ORACLE_SID:$ORACLE_HOME:<N|Y>:
The first and second fields are the system identifier and home directory of the database respectively. The third filed indicates to the dbstart utility that the database should , "Y", or should not, "N", be brought up at system boot time.
Multiple entries with the same $ORACLE_SID are not allowed.

17. How do you see how many processes are running in Unix?

ps -e|cut -d " " -fname|wc -l

18. How do you kill a process in Unix?

Linux and all other UNIX like oses comes with kill command. The command kill sends the specified signal (such as kill process) to the specified process or process group. If no signal is specified, the TERM signal is sent. Kill process using kill command under Linux/UNIX. kill command works under both Linux and UNIX/BSD like operating systems.

19. Can you change priority of a Process in Unix?

As system administrator you can use the renice command to change the priority of a process all processes of a user or all processes belong to a group of users. The renice command has the form

/etc/renice priority [ [ -p ] pid ... ] [ [ -g ] pgrp ... ] [ [ -u ] user

Backup and Recovery Interview Questions for an Oracle DBA

1. Which types of backups you can take in Oracle?

2. A database is running in NOARCHIVELOG mode then which type of backups you can take?

3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6. You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

7. Where should you place Archive logfiles, in the same disk where DB is or another disk?

8. Can you take online backup of a Control file if yes, how?

9. What is a Logical Backup?

10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11. Why do you take tablespaces in Backup mode?

12. What is the advantage of RMAN utility?

13. How RMAN improves backup time?

14. Can you take Offline backups using RMAN?

15. How do you see information about backups in RMAN?

16. What is a Recovery Catalog?

17. Should you place Recovery Catalog in the Same DB?

18. Can you use RMAN without Recovery catalog?

19. Can you take Image Backups using RMAN?

20. Can you use Backupsets created by RMAN with any other utility?

21. Where RMAN keeps information of backups if you are using RMAN without Catalog?

22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

24. Which is more efficient Incremental Backups using RMAN or Incremental Export?

25. Can you start and shutdown DB using RMAN?

26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

28. You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

31. You loss controlfile how do you recover from this?

32. The current logfile gets damaged. What you can do now?

33. What is a Complete Recovery?

34. What is Cancel Based, Time based and Change Based Recovery?

35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?

38. How do you recover from the loss of a controlfile if you have backup of controlfile?

39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?

41. Have you faced any emergency situation. Tell us how you resolved it?

42. At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.

43. What is Consistent Backup?

A Consistent backup is one in which the files being backed up contain all changes upto the same system change number (SCN)

44. What is fractured Block?

Because the database continues writing to the file during an online backup, there is the possibility of backing up inconsistent data within a block. For example, assume that either RMAN or an operating system utility reads the block while database writer is in the middle of updating the block. In this case, RMAN or the copy utility could read the old data in the top half of the block and the new data in the bottom top half of the block. The block is a fractured block, meaning that the data in this block is not consistent.

45. What are the steps to performing complete recovery on the whole database?

Mount the database

Ensure that all datafiles you want to recover are online

Restore a backup of the whole database or the files you want to recover

Apply online or archived redo logs, or a combination of the two

46. What are the steps to performing complete recovery on a tablespace or datafile?

Take the tablespace or datafile to be recovered offline if the database is open

Restore a backup of the datafiles you want to recover

Apply online or archived redo logs, or a combination of the two

47. What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

48. What are the components of logical database structure of Oracle database?

There are tablespaces and database's schema objects.

49. What is a tablespace?

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

50. What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

51. Explain the relationship among database, tablespace and data file.

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

52. What is schema?

A schema is collection of database objects of a user.

53. What are Schema Objects?

Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

54. Can objects of the same schema reside in different tablespaces?

Yes.

55. Can a tablespace hold objects from different schemes?

Yes.

56. What is Oracle table?

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

57. What is an Oracle view?

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

58. Do a view contain data?

Views do not contain or store data.

59. Can a view based on another view?

Yes.

60. What are the advantages of views?

- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.

- Hide data complexity.

- Simplify commands for the user.

- Present the data in a different perspective from that of the base table.

- Store complex queries.

61. What is an Oracle sequence?

A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

62. What is a synonym?

A synonym is an alias for a table, view, sequence or program unit.

63. What are the types of synonyms?

There are two types of synonyms private and public.

64. What is a private synonym?

Only its owner can access a private synonym.

65. What is a public synonym?

66. Any database user can access a public synonym.

67. What are synonyms used for?

- Mask the real name and owner of an object. - Provide public access to an object - Provide location transparency for tables, views or program units of a remote database. - Simplify the SQL statements for database users.

68. What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

69. How are the index updates?

Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

70. What are clusters?

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

71. What is cluster key?

The related columns of the tables in a cluster are called the cluster key.

72. What is index cluster?

A cluster with an index on the cluster key.

73. What is hash cluster?

A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

74. When can hash cluster used?

Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

75. What is database link?

A database link is a named object that describes a "path" from one database to another.

76. What are the types of database links?

Private database link, public database link & network database link.

77. What is private database link?

Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

78. What is public database link?

Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

79. What is network database link?

Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

80. What is data block?

Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

81. How to define data block size?

A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

82. What is row chaining?

In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

83. What is an extent?

An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

84. What is a segment?

A segment is a set of extents allocated for a certain logical structure.

85. What are the different types of segments?

Data segment, index segment, rollback segment and temporary segment.

86. What is a data segment?

Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

87. What is an index segment?

Each index has an index segment that stores all of its data.

88. What is rollback segment?

A database contains one or more rollback segments to temporarily store "undo" information.

89. What are the uses of rollback segment?

To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

90. What is a temporary segment?

Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

91. What is a datafile?

Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tablesand indexes is physically stored in the data files allocated for a database.

92. What are the characteristics of data files?

A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

93. What is a redo log?

The set of redo log files for a database is collectively known as the database redo log.

94. What is the function of redo log?

The primary function of the redo log is to record all changes made to data.

95. What is the use of redo log information?

The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

96. What does a control file contains?

- Database name - Names and locations of a database's files and redolog files. - Time stamp of database creation.

97. What is the use of control file?

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Data Base Administration

1. What is a database instance? Explain.

A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users. The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

2. What is Parallel Server?

Multiple instances accessing the same database (only in multi-CPU environments)

3. What is a schema?

The set of objects owned by user account is called the schema.

4. What is an index? How it is implemented in Oracle database?

An index is a database structure used by the server to have direct access of a row in a table. An index is automatically created when a unique of primary key constraint clause is specified in create table command

5. What are clusters?

Group of tables physically stored together because they share common columns and are often used together is called cluster.

6. What is a cluster key?

The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

7. What are the basic element of base configuration of an Oracle database?

It consists of one or more data files. one or more control files. two or more redo log files. The Database contains multiple users/schemas one or more rollback segments one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the database

consists of

SGA (Database buffer,

Dictionary Cache Buffers, Redo log

buffers, Shared SQL pool)

SMON (System MONito)

PMON (Process MONitor)

LGWR (LoG Write)

DBWR (Data Base Write)

ARCH (ARCHiver)

CKPT (Check Point)

RECO

Dispatcher

User Process with associated

PGS

8. What is a deadlock? Explain.

Two processes waiting to update the rows of a table, which are locked by other processes then deadlock arises. In a database environment this will often happen because of not issuing the proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically. These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

Memory Management

1. What is SGA?

The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

2. What is a shared pool?

The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.

3. What is mean by Program Global Area (PGA)?

It is area in memory that is used by a single Oracle user process.

4. What is a data segment?

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

5. What are the factors causing the reparsing of SQL statements in SGA?

Due to insufficient shared pool size. Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

Database Logical & Physical Architecture

1. What is Database Buffers?

Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

2. What is dictionary cache?

Dictionary cache is information about the database objects stored in a data dictionary table.

3. What is meant by recursive hints?

Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of data dictionary cache.

4. What is redo log buffer?

Changes made to the records are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

5. How will you swap objects into a different table space for an existing database?

- Export the user - Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql. - Drop necessary objects. - Run the script newfile.sql after altering the tablespaces. - Import from the backup for the necessary objects.

6. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?

SYSTEM - Data dictionary tables.

DATA - Standard operational tables.

DATA2- Static tables used for standard operations

INDEXES - Indexes for Standard operational tables.

INDEXES1 - Indexes of static tables used for standard operations.

TOOLS - Tools table.

TOOLS1 - Indexes for tools table.

RBS - Standard Operations Rollback Segments,

RBS1,RBS2 - Additional/Special Rollback segments.

TEMP - Temporary purpose tablespace

TEMP_USER - Temporary tablespace for users.

USERS - User tablespace.

7. How will you force database to use particular rollback segment?

SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.

8. What is meant by free extent?

A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.

9. Which parameter in Storage clause will reduce number of rows per block?

PCTFREE parameter Row size also reduces no of rows per block.

10. What is the significance of having storage clause?

We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updating, etc.,

11. How does Space allocation table place within a block?

Each block contains entries as follows Fixed block header Variable block header Row Header, row date (multiple rows may exists) PCTEREE (% of free space for row updating in future)

12. What is the role of PCTFREE parameter is storage clause?

This is used to reserve certain amount of space in a block for expansion of rows.

13. What is the OPTIMAL parameter?

It is used to set the optimal length of a rollback segment.

14. What is the functionality of SYSTEM table space?

To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.

15. How will you create multiple rollback segments in a database?

- Create a database, which implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.

- Create a second rollback segment name R0 in the SYSTEM tablespace. - Make new rollback segment available (after shutdown, modify init.ora file and start database) - Create other tablespaces (RBS) for rollback segments. - Deactivate rollback segment R0 and activate the newly created rollback segments.

16. How the space utilization takes place within rollback segments?

It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent (number of extents is based on the optimal size)

17. Why query fails sometimes?

Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the rollback segment tablespace. This prevents other user using rollback segments.

18. How will you monitor the space allocation?

By querying DBA_SEGMENT table/view

19. How will you monitor rollback segment status?

Querying the DBA_ROLLBACK_SEGS view

IN USE - Rollback

Segment is on-line.

AVAILABLE – Rollback Segment available but not on-line.

OFF-LINE – Rollback Segment off-line

INVALID – Rollback Segment Dropped.

NEEDS RECOVERY - Contains data but need recovery or corrupted.

PARTLY AVAILABLE - Contains data from an unresolved transaction involving a distributed database.

20. List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend.

Transaction Begins. An entry is made in the RES header for new transactions entry

Transaction acquires blocks in an extent of RBS

The entry attempts to wrap into second extent. None is available, so that the RBS must extent.

The RBS checks to see if it is part of its OPTIMAL size.

RBS chooses its oldest inactive segment.

Oldest inactive segment is eliminated.

RBS extents

The data dictionary tables for space management are updated.

Transaction Completes.

21. How can we plan storage for very large tables?

Limit the number of extents in the table

Separate table from its indexes.

Allocate sufficient temporary storage.

22. How will you estimate the space required by a non-clustered tables?

Calculate the total header size

Calculate the available data space per data block

Calculate the combined column lengths of the average row

Calculate the total average row size.

Calculate the average number rows that can fit in a block

Calculate the number of blocks and bytes required for the table.

After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.

23. It is possible to use raw devices as data files and what are the advantages over file system files?

Yes.

The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing into disk. Disk corruption will be very less.

24. What is a Control file?

Database's overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.

25. How to implement the multiple control files for an existing database?

Shutdown the database Copy one of the existing controlfile to new location Edit Config ora file by adding new control filename Restart the database.

26. What is redo log file mirroring? How can be achieved?

Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

27. What is advantage of having disk shadowing / mirroring?

Shadow set of disks save as a backup in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

28. What is use of rollback segments in Oracle database?

They allow the database to maintain read consistency between multiple transactions.

29. What is a rollback segment entry?

It is the set of before image data blocks that contain rows that are modified by a transaction. Each rollback segment entry must be completed within one rollback segment. A single rollback segment can have multiple rollback segment entries.

30. What is hit ratio?

It is a measure of well the data cache buffer is handling requests for data. Hit Ratio = (Logical Reads – Physical Reads - Hits Misses)/ Logical Reads.

31. When will be a segment released?

When Segment is dropped.

When Shrink (RBS only)

When truncated (TRUNCATE used with drop storage option)

32. What are disadvantages of having raw devices?

We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.

33. List the factors that can affect the accuracy of the estimations?

- The space used transaction entries and deleted records, does not become free immediately after completion due to delayed cleanout. - Trailing nulls and length bytes are not stored. - Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.

Database Security & Administration

1. What is user Account in Oracle database?

A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

2. How will you enforce security using stored procedures?

Don't grant user access directly to tables within the application. Instead grant the ability to access the

procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

3. What are the dictionary tables used to monitor a database space?

DBA_FREE_SPACE

DBA_SEGMENTS

DBA_DATA_FILES.

SQL*Plus Statements 100.

1. What are the types of SQL statement?

Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT. Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT. Transactional Control: COMMIT & ROLLBACK Session Control: ALTERSESSION & SET ROLE System Control: ALTER SYSTEM.

2. What is a transaction?

Transaction is logical unit between two commits and commit and rollback.

6. What is the sub-query?

Sub-query is a query whose return values are used in filtering conditions of the main query.

3. What is correlated sub-query?

Correlated sub-query is a sub-query, which has reference to the main query.

4. Explain CONNECT BY PRIOR?

Retrieves rows in hierarchical order eg. select empno, ename from emp where.

5. Difference between SUBSTR and INSTR?

INSTR (String1, String2 (n, (m)),

INSTR returns the position of the m-the occurrence of the string 2 in string1. The search begins from nth position of string1.

SUBSTR (String1 n, m)

SUBSTR returns a character string of size m in string1, starting from n-the position of string1.

6. Explain UNION, MINUS, UNION ALL and INTERSECT?

INTERSECT - returns all distinct rows selected by both queries.

MINUS - returns all distinct rows selected by the first query but not by the second.

UNION - returns all distinct rows selected by either query

UNION ALL - returns all rows selected by either query, including all duplicates.

7. What is ROWID?

ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

8. What is the fastest way of accessing a row in a table?

Using ROWID.

CONSTRAINTS

9. What is an integrity constraint?

Integrity constraint is a rule that restricts values to a column in a table.

10. What is referential integrity constraint?

Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

11. What is the usage of SAVEPOINTS?

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

12. What is ON DELETE CASCADE?

When ON DELETE CASCADE is specified Oracle maintains referential integrity by automatically removing dependent foreign key values if a referenced primary or unique key value is removed.

13. What are the data types allowed in a table?

CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.

14. What is difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?

CHAR pads blank spaces to the maximum length.

VARCHAR2 does not pad blank spaces.

For CHAR the maximum length is 255 and 2000 for VARCHAR2.

15. How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

16. What are the pre-requisites to modify datatype of a column and to add a column with NOT NULL constraint?

- To modify the datatype of a column the column must be empty. - To add a column with NOT NULL constrain, the table must be empty.

17. Where the integrity constraints are stored in data dictionary?

The integrity constraints are stored in USER_CONSTRAINTS.

18. How will you activate/deactivate integrity constraints?

The integrity constraints can be enabled or disabled by ALTER TABLE ENABLE CONSTRAINT / DISABLE CONSTRAINT.

19. If unique key constraint on DATE column is created, will it validate the rows that are inserted with SYSDATE?

It won't, Because SYSDATE format contains time attached with it.

20. What is a database link?

Database link is a named path through which a remote database can be accessed.

21. How to access the current value and next value from a sequence? Is it possible to access the current value in a session before accessing next value?

Sequence name CURRVAL, sequence name NEXTVAL. It is not possible. Only if you access next value in the session, current value can be accessed.

22. What is CYCLE/NO CYCLE in a Sequence?

CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum. NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.

23. What are the advantages of VIEW?

- To protect some of the columns of a table from other users. - To hide complexity of a query. - To hide complexity of calculations.

24. Can a view be updated/inserted/deleted? If Yes -under what conditions?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

25. If a view on a single base table is manipulated will the changes be reflected on the base table?

If changes are made to the tables and these tables are the base tables of a view, then the changes will be reference on the view.

Oracle DBA / Apps DBA - Asif Abbasi

Tuesday, May 22, 2012

Oracle Applications DBA Interview Question Answers

Applications DBA

Core DBA

Unix Related Interview Questions for an Oracle DBA

Backup and Recovery Interview Questions for an Oracle DBA

Data Base Administration

Memory Management

Database Logical & Physical Architecture

Database Security & Administration

SQL*Plus Statements 100.

1 comment: