Oracle Ultimate DBA Interview Questions
By: Equity Press
Oracle Ultimate DBA Interview Questions
ISBN: 1-933804-26-2
Smashwords Edition
Edited By: Jamie Fisher
Copyright© 2006 Equity Press and ORA COOKBOOK all rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without either the prior written permission of the publisher or a license permitting restricted copying in the United States or abroad.
The scanning, uploading and distribution of this book via the internet or via any other means with the permission of the publisher is illegal and punishable by law. Please purchase only authorized electronic editions, and do not participate in or encourage piracy of copyrighted materials.
The programs in this book have been included for
instructional value only. They have been tested with
care but are not guaranteed for any particular purpose.
The publisher does not offer any warranties or
representations nor does it accept any liabilities with
respect to the programs.
Trademarks: All trademarks are the property of their respective owners. Equity Press is not associated with any product or vendor mentioned in this book.
Please visit our website at www.oracookbook.com
Question 01: Error while adding database
Question 02: Can't login to EM
Question 03: Unable to start enterprise manager for Oracle
Question 04: Failed to start Oracle 10g EM console
Question 06: OEM in Oracle10G does not start
Question 07: Oracle 10g database and Oracle 10 client in same pc
Question 08: Unable to log in to console
Question 09: Unable to start OMS
Question 10: Unable to login to the database
Question 11: Oracle 10.2G: e-mail events every minute
Question 12: Shutting down database using Enterprise Manager
Question 13: Unable to connect to management server
Question 14: Wrong password for user
Question 15: Oracle Intelligent Agent
Question 17: Connect to 10g OEM with Windows o/s authentication
Question 18: ORA-00406: COMPATIBLE parameter
Question 20: Oracle 9i OEMC unable to login
Question 21: Add/Remove Program
Question 22: Agent fails to start
Question 23: Problem with enterprise manager
Question 24: Enterprise manager for oracle 9i
Question 26: Insufficient Privileges
Question 27: TNS: protocol adapter error
Question 28: 9i OEM or OMS to manage 10g database
Question 29: Data Dictionary View(s)
Question 32: Archived log files missing in Online Backup
Question 33: ORA-19760: error starting change tracking
Question 34: Delete in multi-master replication
Question 35: 9i server and 10g client
Question 36: View oracle database installed on HP-UX from Windows
Question 37: ORA - 07217 errors
Question 38: Configure GC to send alert message
Question 39: Verify commit time
Question 40: System Database in Oracle
Question 41: Unable to lock table trades
Question 42: Exporting questionable statistics
Question 44: Binary Compatibility
Question 45: DB links clean up after refresh
Question 46: Oracle failsafe over distances
Question 47: Accessing 2 databases (10g) on Win XP
Question 48: Partitions of table
Question 49: Closing the database
Question 51: Access environment variable in an SQL Script
Question 52: Call stored procedure over dblink
Question 53: Fragmentation on LMT
Question 54: Clone database to another machine
Question 56: Append values to an existing row in a nested table
Question 57: Index a document in a subtype
Question 58: Rollback package installation
Question 59: Add or change key on the registry on Oracle forms
Question 60: Dates converting to numbers
Question 61: Display first and last name
Question 62: Replicate table structure
Question 63: Compare contents of two tables
Question 64: Copy table contents to another table
Question 65: Insufficient select privilege
Question 67: Executing script for Enterprise Manager
Question 68: Updating partition key column
Question 69: Shared pool memory
Question 70: Materialized views
Question 72: Components of physical database structure
Question 73: Components of logical database structure
Question 79: System tablespace
Question 80: Rename a database
Question 81: New features of Oracle 9i
Question 82: Relationship among database, tablespace and data file
Question 86: Shared Pool Size threshold
Question 87: Cumulative Backup problem
Question 88: Failing scheduled Oracle - Suggested Backup
Question 89: Diverting new data to a second file
Question 90: File types supported by SQL*Loader
Question 92: Database w/out SPFile
Question 94: Advantage of using DBCA
Question 95: Physical database structure
Question 96: CASE expression and statement
Question 97: Integrity constraint
Question 98: Correlated sub-query
Question 99: Access row in a table
Question 100: Group By and Order By
Question 01: Error while adding database
When trying to add database to tree and to network, the following error read while trying to connect them:
Failed to parse tnsnames.ora file
Error: 100--NLNV-NLNV String format Error
A. A syntax error occurred somewhere in your TNSNAMES.ORA file. Delete all entries in your TNSNAMES.ORA and start adding them back one-by-one until you find the entry that is responsible for the error.
Question 02: Can't login to EM
I did a basic install of Oracle 10g (standard) and installed a starter database. After install, via the EM console, the database did not start.
After clicking the start button, it asked for both the OS and Database user and password. For OS, I use 'Administrator' and password and for Database, I tried SYS and password but it still won’t register.
How can I successfully login to EM?
A. The following procedures are suggested:
1. Install a Firefox 1.5 to upgrade your browser.
2. After completing installation and enter EM for the first time, you have to scroll to the bottom of the screen and "agree" to the eula a to continue using EM again. Re-install using UI.
3. Start lsnrctl start and emctl start dbconsole;
http:// localhost:1158/em for 10g
Question 03: Unable to start enterprise manager for Oracle
I have two oracle 10g database:
[1]orcl <--- (first instance created)
[2]sunilhcl <--- (second instance)
For the first database, I wrote this link: http://10.103.93.86:5501/em
The address bar of web page enterprise manager is in progress and also shows the name of database to which it is connected. What link should be written in the address bar of the web page if I want to start enterprise manager for oracle?
A. Reset the configuration file listener.ora by dbca.
Below is my listener.ora:
# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
(ADDRESS = (PROTOCOL = TCP)(HOST = db31)(PORT = 1521))
)
)
Question 04: Failed to start Oracle 10g EM console
When I start up my Oracle 10gR2 console, it gives an error message alert saying:
“Windows could not start the OracleDbConsoleorcl on local computer. For more info, review the System Event Log”
I check the event log and it says:
“System log
The OracleDBConsoleorcl service terminated with service-specific error 2 (0x2)”
“Application log
Agent process exited abnormally during initialization”
Everything is running fine but I can’t have my Oracle console up.
What procedure is needed to install my Oracle again?
A. You should change the IP back to what it was when you installed Oracle. If the Network cable is disconnected then unplug that cable, start DBConsole and then attach the cable again.
I've got a problem with an agent installed on a w2000 server. The host and the listener are running and databases are discovered normally and following agent, everything is up. In the grid control, database seems down and I don't understand why.
2005-11-10 11:07:01 Thread-3216 Starting Agent 10.1.0.3.0 from G:\oracle\product\10.1.0\em_1 (00701)
2005-11-10 11:07:01 Thread-3216 [Adapter Framework] InstanceProperty (OidRepSchemaName) is marked OPTIONAL but is being used (00506)
2005-11-10 11:07:02 Thread-3216 ParseError: File=G:\oracle\product\10.1.0\em_1\sysman\admin\metadata\oracle_bc4j.xml, Line=486, Msg=attribute NAME in <CategoryProp> cannot be NULL (01006)
2005-11-10 11:07:03 Thread-3216 Undefined column name EFFICIENCY__BYTES_SAVED_WITH_COMPRESSION__AVG_PER_SEC_SINCE_START in expression EFFICIENCY__BYTES_SAVED_WITH_COMPRESSION__AVG_PER_SEC_SINCE_START/1024/1024 (00104)
2005-11-10 11:07:12 Thread-3216 EMAgent started successfully (00702)
2005-11-10 11:14:01 Thread-3216 EMAgent normal shutdown (00703)
Environment: Grid under Windows2003 - 10.1.0.3
Target Machine: W2000 with oracle 8.1.7.3
How do I find my emagent.log?
A. The error is not platform specific to Windows. I am seeing the same problem on Redhat Linux 4 U2 x86_64 using 10.2.0.1 agent (no databases running on the server yet) with the grid running on 10.2.0.1.0 on Redhat Linux 4 U2 i386. Just expand on this thread.
Question 06: OEM in Oracle10G does not start
I type and enter this in my browser:
http://localhost:1158/em
At first, I only get this line:
java.lang.NumberFormatException: For input string: "7.0B"
When I go to:
http://localhost:1158/em/console/logon/logon and log on the next page, I get the following errors:
500 Internal Server Error
java.lang.NumberFormatException: For input string: "7.0B"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
at java.lang.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1207)
at java.lang.Float.valueOf(Float.java:205)
at java.lang.Float.<init>(Float.java:289)
at oracle.sysman.emSDK.util.http.UserAgentUtil.getBrowserInfo(UserAgentUtil.java:300)
at oracle.sysman.emSDK.util.http.UserAgentUtil.isUserAgentWindowsCE(UserAgentUtil.java:198)
at oracle.sysman.emSDK.svlt.EMServletUtil.isEm2goApp(EMServletUtil.java:178)
at oracle.sysman.eml.app.Console.doGet(Console.java:184)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:65)
at oracle.sysman.emSDK.svlt.EMRedirectFilter.doFilter(EMRedirectFilter.java:101)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:16)
at oracle.sysman.db.adm.inst.HandleRepDownFilter.doFilter(HandleRepDownFilter.java:138)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:20)
at oracle.sysman.eml.app.ContextInitFilter.doFilter(ContextInitFilter.java:269)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:600)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:317)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:793)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.HttpRequestHandler.run(HttpRequestHandler.java:270)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
at com.evermind[Oracle Application Server Containers for J2EE 10g (9.0.4.1.0)].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:192)
at java.lang.Thread.run(Thread.java:534)
I am using winxp home edition. All my services are running even my sqlplus. My application servers’ first page is displayed but when I click on one of the examples, I get an http 404 error.
How do I correctly start OEM in Oracle10G?
A. Use the O/S credentials and provide the 'Log on as a batch job' privilege: The following procedure should work:
1. Go to control panel/administrative tools.
a. click on "local security policy"
b. click on "local policies"
c. click on "user rights assignments"
d. double click on "log on as a batch job"
e. click on "add" and add the user that was entered in the "normal username" or "privileged username" section of the EM Console.
2. Go to the Preferences link in the EM GUI.
a. click on Preferred Credentials (link on the left menu)
b. under "Target Type: Host" click on "set credentials"
c. enter the OS user who has logon as a batch job privilege into the "normal username" and "normal password" fields
3. Test the connection.
a. while in the Set Credentials window, click on "Test"
Question 07: Oracle 10g database and Oracle 10 client in same pc
How do I install oracle 10g database and oracle 10 client in the same pc?
A. You can install both in the same pc but you don't need to have a separate client installation in the same machine (unless you are using different tools like forms/reports).
Install server and any/all components you want.
Make sure you install oracle sqlplus, etc. to create database.
However, you cannot put it in the same dir and set a classpath or oracle home. Every client has to be in a different ORACLE_HOME.
Read documentation.
http://download-east.oracle.com/docs/cd/B19306_01/install.102/b14318/toc.htm
Any oracle product that needs ORACLE_HOME cannot be installed in same directory. You need to use different directories.
Question 08: Unable to log in to console
We stopped getting emails for job starts/completes and could no longer log into the OEM console. It hangs after hitting Submit. The scheduled jobs have stop running as well. We try to stop and start the Oracle Management server with oemctl start oms but it just hangs there. We are running 9.2.0 on UNIX.
How can we log into our OEM console again?
A. Check on your access permissions on the UNIX. Try to start the OMS service with another UNIX user.
Question 09: Unable to start OMS
I recently installed oracle9.2.0.1.0 on redhat el as4 and successfully installed oracle and created a db. The oem console works well but when I try to start oms (I have created the repository), it failed. The system gave out the following information:
Please check the file /opt/ora9/product/9.2/sysman/log/oms.nohup for more details.
Terminated
Starting the Oracle Management Server:
Error starting Oracle Management Server. ORBSingleton: access deniedjava.lang.SecurityException: ORBSingleton: access denied
at com.sun.corba.se.internal.corba.ORBSingleton.connect(ORBSingleton.java:330)
at oracle.sysman.vxn.VxnNamingContext.<init>(VxnNamingContext.java:285)
at oracle.sysman.vxn.VxnNamingContext.getNewContext(VxnNamingContext.java:222)
at oracle.sysman.vxn.VxnNamingService.getInitialContext(VxnNamingService.java:1072)
at oracle.sysman.vxn.VxnNamingService.enableBootstrap(VxnNamingService.java:1080)
at oracle.sysman.vxn.VxnNamingService.init(VxnNamingService.java:238)
at oracle.sysman.vxn.VxnNamingService.getNamingService(VxnNamingService.java:103)
at oracle.sysman.vxn.VxnNamingService.getNamingService(VxnNamingService.java:112)
at oracle.sysman.vxa.VxaAppServer.initServer(VxaAppServer.java:3417)
at oracle.sysman.vxa.VxaAppServer.main(VxaAppServer.java:3048)
OMS exited on Mon Oct 24 09:25:49 CST 2005 with return value 56
Could not start management server. Initialization failure
\nManagement server startup failed. Check the file /opt/ora9/product/9.2/sysman/log/oms.nohup for details
How do I properly start OMS?
A. In oemctl file, make sure your PATH.check have correct values in ORACLE_HOME.
Question 10: Unable to login to the database
I have successfully installed Oracle 10g at a client site. When I tried to launch the enterprise manager to create a schema: xxxx:5500\em, the page did not load. Then I tried "xxxx:5500\em\console\logon\logon and got the initial logon screen to the database ORCL. I entered the username & password and click on the login button, nothing happened. I tried sys, sysman, system, but was also of no use.
How can I login to the database?
A. Do either or both of the following process:
a. Check if DB Control is running (if you can, restart it). Also, clear your browser's cache as you may be getting an old copy of the page.
b. >sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Oct 26 11:41:19 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: sys / as sysdba
Enter password: <anything works>
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
Now alter user <user> identified by <your new password>;
Question 11: Oracle 10.2G: e-mail events every minute
Is it possible to receive events every minute via EM with an Oracle 10.2G?
A. Events are mailed out every time ADDM runs (after each AWR snapshot). Try to set this:
SQL> SELECT * FROM dba_hist_wr_control;
SQL>
SQL> BEGIN
2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(