Tag Archives: dba

11g, ASM, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, RAC, SQL, uncategorized, unix

Senior-Level DBA Interview Question:

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple of articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Senior Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions: NOTE: Answers here can be very broad, however they not always wrong. That is why it is important to have a proven DBA on staff or one you can call to review the answers. I have seen very inventive ways to get around the most impossible tasks and while they do not actually meet the standard way of doing the work, I would not count these people out. A person whom thinks outside of the box is very valuable in IT and believe it or not, some of the best DBA’s I have ever met thought completely outside the box, however always accomplished the task.

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

4. In what months does Oracle release CPU patches and what is the utility to apply them.

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

6. How do you logon to Oracle server without knowing any oracle passwords?

7. How would you determine that SCATTERED READ where occuring?

8. How would you see the SQL of the currently active sessions from a command prompt?

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Answers:

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

– Verify that all patches are installed and kernel parameters set.(Some not all SA say the system is ready when it is not. You can save yourself a lot of problems by double checking)
– Configuration of .profile or .bash_profile for the Oracle owner.
– Base Installation of Oracle (ie what to get to 10.2.0.4 must install Oracle 10.1 first).
– Upgrade to Oracle Version Desired.
– Upgrade the database(if upgrade is necessary)
– Run UTLRP.SQL.
– Apply lastest CPU patch.
– Run UTLRP.SQL.
– Configuration of Listener.
– Configuration of client TNSNAMES.ORA file.

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

Solution:
Relink all

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

Solution:
Differing Indian codes.

4. In what months does Oracle release CPU patches and what is the utility to apply them.

Solution:
JAN, APR, JUL, OCT

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

Solution:
Kill the smon process.

6. How do you logon to Oracle server without knowing any oracle passwords?

Solution:
Sudo to the Oracle software owners account and logon with the command sqlplus ‘/ as sysdba’

7. How would you determine that SCATTERED READ where?
Solution:

Look in the views v$session_wait or v$session_wait_history.

Could give:

Statspack or EM report.

8. How would you see the SQL of the currently active sessions from a command prompt?

Solution:
Compare tables V$SQL view by sql_address with V$SESSION view of currently active sessions.

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

Solution:
TNS_ADMIN

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Solution:
Other database would shutdown abort.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Mid-Level DBA Interview Questions

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Mid Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

6. Define the Oracle utility used to manage an Oracle listener?

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

10. Name the utility used to format perfstat report trace files?

Answers:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

– The directory where Oracle stores the maintenance scripts is $ORACLE_HOME/rdbms/admin.
– The question mark represents the ORACLE_HOME within the database, thus you would call this directory with ‘?/rdbms/admin’. Another acceptable answer is ‘@?/rdbms/admin’.

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

– USER_ – These views only contain definition of objects which you own.
– ALL_ – These views contain definitions of all objects which you have access to.
– DBA_ – These views contain definitions of all objects in the database, even if you do not have access to them.

– The USER_ views do not contain a column named OWNER, by definition of the view you are the owner.

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

– By using the data dictionary view DBA_OBJECTS with the statement “select count(*) from dba_objects where status=’INVALID’;”
– You would use the Oracle script, “UTLRP.SQL” located in directory $ORACLE_HOME/rdbms/admin.

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

– In a standard Oracle installation the network files are located under “$ORACLE_HOME/network/admin”.
– Files located in this directory are: “LISTENER.ORA”, “TNSNAMES.ORA”, or “SQLNET.ORA”.

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

– RMAN (Recovery Manager)
– The two methods for storing information about backed up database are: the controlfile or Recovery Catalog.

6. Define the Oracle utility used to manage an Oracle listener?

– LSNRCTL (LISTENER CONTROL)

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

– $ORACLE_HOME – defines the directory where the Oracle binaries are installed.
– $ORACLE_SID – defines the instance name used to access a database.

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

– “opatch” is the utility which is used to install CPU patches.
– The “opatch” utility is located in the directory “$ORACLE_HOME/OPatch”. NOTE: “opatch” is not a valid answer in a standard Oracle installation the directory has a capital O and P.

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

– DBMS_JOBS – This answer is valid for all versions of Oracle.
– DBMS_SCHEDULER – This answer is only valid for Oracle 10g or higher.

10. Name the utility used to format perfstat report trace files?

– TKPROF

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, linux, Oracle 10g, oracle 9i, SQL

Oracle DBA role

Leave a comment

Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role DBA.

The DBA role is used to give an individual user the right to administrator an oracle database. This role is normally only granted to users who have a need to view dictionary level views and administrate other user accounts. NOTE: Great care has to be used when assigning the DBA role, because users who have it can perform almost every action in the database short of actually shutting down the system. The following instructions define how to create a user account, assign the DBA role to that user and lists the privileges granted by DBA role.

1. Logon the you Oracle database as sysdba.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 4 21:56:18 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create a permanent tablespace for your new user.

SQL> create tablespace admin datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/admin01.dbf’ size 5m;

Tablespace created.

SQL>

3. Create a temporary tablespace for you new user.

SQL> create temporary tablespace admin_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/O
RADATA/ORCL10G/admin_temp01.dbf’ size 5m;

Tablespace created.

SQL>

4. Create you administrative user.

SQL> Create user admin01 identified by admin
2 Default tablespace admin
3 Quota unlimited on admin
4 Temporary tablespace admin_temp;

User created.

SQL>

5. Grant the role DBA to the user developer01.

SQL> grant dba to admin01;

Grant succeeded.

SQL>

6. Connect to the system as the admin01 user and perform a select from a system view.

SQL> connect admin01/admin
Connected.
SQL> select count(*) from v$session;

COUNT(*)
———-
18

SQL>

7. The PL/SQL block below will list all privileges contained in the role DBA, where the variable V_USER defines the role’s privileges to be displayed.
set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’DBA’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

34
35 36 37 38 39 40 41 42
grant XDBADMIN to DBA;
grant JAVA_ADMIN to DBA;
grant JAVA_DEPLOY to DBA;
grant WM_ADMIN_ROLE to DBA;
grant SCHEDULER_ADMIN to DBA with admin option;
grant EXP_FULL_DATABASE to DBA;
grant IMP_FULL_DATABASE to DBA;
grant DELETE_CATALOG_ROLE to DBA with admin option;
grant SELECT_CATALOG_ROLE to DBA with admin option;
grant EXECUTE_CATALOG_ROLE to DBA with admin option;
grant GATHER_SYSTEM_STATISTICS to DBA;
grant ADVISOR to DBA with admin option;
grant AUDIT ANY to DBA with admin option;
grant DROP USER to DBA with admin option;
grant RESUMABLE to DBA with admin option;
grant ALTER USER to DBA with admin option;
grant CREATE JOB to DBA with admin option;
grant ANALYZE ANY to DBA with admin option;
grant BECOME USER to DBA with admin option;
grant CREATE ROLE to DBA with admin option;
grant CREATE RULE to DBA with admin option;
grant CREATE TYPE to DBA with admin option;
grant CREATE USER to DBA with admin option;
grant CREATE VIEW to DBA with admin option;
grant ALTER SYSTEM to DBA with admin option;
grant AUDIT SYSTEM to DBA with admin option;
grant CREATE TABLE to DBA with admin option;
grant DROP PROFILE to DBA with admin option;
grant ALTER PROFILE to DBA with admin option;
grant ALTER SESSION to DBA with admin option;
grant DROP ANY ROLE to DBA with admin option;
grant DROP ANY RULE to DBA with admin option;
grant DROP ANY TYPE to DBA with admin option;
grant DROP ANY VIEW to DBA with admin option;
grant QUERY REWRITE to DBA with admin option;
grant ALTER ANY ROLE to DBA with admin option;
grant ALTER ANY RULE to DBA with admin option;
grant ALTER ANY TYPE to DBA with admin option;
grant ALTER DATABASE to DBA with admin option;
grant CREATE ANY JOB to DBA with admin option;
grant CREATE CLUSTER to DBA with admin option;
grant CREATE LIBRARY to DBA with admin option;
grant CREATE PROFILE to DBA with admin option;
grant CREATE SESSION to DBA with admin option;
grant CREATE SYNONYM to DBA with admin option;
grant CREATE TRIGGER to DBA with admin option;
grant DROP ANY INDEX to DBA with admin option;
grant DROP ANY TABLE to DBA with admin option;
grant GRANT ANY ROLE to DBA with admin option;
grant LOCK ANY TABLE to DBA with admin option;
grant MERGE ANY VIEW to DBA with admin option;
grant UNDER ANY TYPE to DBA with admin option;
grant UNDER ANY VIEW to DBA with admin option;
grant ALTER ANY INDEX to DBA with admin option;
grant ALTER ANY TABLE to DBA with admin option;
grant CREATE ANY RULE to DBA with admin option;
grant CREATE ANY TYPE to DBA with admin option;
grant CREATE ANY VIEW to DBA with admin option;
grant CREATE OPERATOR to DBA with admin option;
grant CREATE RULE SET to DBA with admin option;
grant CREATE SEQUENCE to DBA with admin option;
grant DROP TABLESPACE to DBA with admin option;
grant UNDER ANY TABLE to DBA with admin option;
grant ALTER TABLESPACE to DBA with admin option;
grant BACKUP ANY TABLE to DBA with admin option;
grant CREATE ANY INDEX to DBA with admin option;
grant CREATE ANY TABLE to DBA with admin option;
grant CREATE DIMENSION to DBA with admin option;
grant CREATE INDEXTYPE to DBA with admin option;
grant CREATE PROCEDURE to DBA with admin option;
grant DELETE ANY TABLE to DBA with admin option;
grant DROP ANY CLUSTER to DBA with admin option;
grant DROP ANY CONTEXT to DBA with admin option;
grant DROP ANY LIBRARY to DBA with admin option;
grant DROP ANY OUTLINE to DBA with admin option;
grant DROP ANY SYNONYM to DBA with admin option;
grant DROP ANY TRIGGER to DBA with admin option;
grant EXECUTE ANY RULE to DBA with admin option;
grant EXECUTE ANY TYPE to DBA with admin option;
grant INSERT ANY TABLE to DBA with admin option;
grant MANAGE ANY QUEUE to DBA with admin option;
grant MANAGE SCHEDULER to DBA with admin option;
grant SELECT ANY TABLE to DBA with admin option;
grant UPDATE ANY TABLE to DBA with admin option;
grant ALTER ANY CLUSTER to DBA with admin option;
grant ALTER ANY LIBRARY to DBA with admin option;
grant ALTER ANY OUTLINE to DBA with admin option;
grant ALTER ANY TRIGGER to DBA with admin option;
grant COMMENT ANY TABLE to DBA with admin option;
grant CREATE TABLESPACE to DBA with admin option;
grant DEQUEUE ANY QUEUE to DBA with admin option;
grant DROP ANY OPERATOR to DBA with admin option;
grant DROP ANY RULE SET to DBA with admin option;
grant DROP ANY SEQUENCE to DBA with admin option;
grant ENQUEUE ANY QUEUE to DBA with admin option;
grant EXECUTE ANY CLASS to DBA with admin option;
grant FORCE TRANSACTION to DBA with admin option;
grant MANAGE FILE GROUP to DBA with admin option;
grant MANAGE TABLESPACE to DBA with admin option;
grant ON COMMIT REFRESH to DBA with admin option;
grant ALTER ANY RULE SET to DBA with admin option;
grant ALTER ANY SEQUENCE to DBA with admin option;
grant CREATE ANY CLUSTER to DBA with admin option;
grant CREATE ANY CONTEXT to DBA with admin option;
grant CREATE ANY LIBRARY to DBA with admin option;
grant CREATE ANY OUTLINE to DBA with admin option;
grant CREATE ANY SYNONYM to DBA with admin option;
grant CREATE ANY TRIGGER to DBA with admin option;
grant DROP ANY DIMENSION to DBA with admin option;
grant DROP ANY DIRECTORY to DBA with admin option;
grant DROP ANY INDEXTYPE to DBA with admin option;
grant DROP ANY PROCEDURE to DBA with admin option;
grant RESTRICTED SESSION to DBA with admin option;
grant ALTER ANY DIMENSION to DBA with admin option;
grant ALTER ANY INDEXTYPE to DBA with admin option;
grant ALTER ANY PROCEDURE to DBA with admin option;
grant ALTER RESOURCE COST to DBA with admin option;
grant CHANGE NOTIFICATION to DBA with admin option;
grant CREATE ANY OPERATOR to DBA with admin option;
grant CREATE ANY RULE SET to DBA with admin option;
grant CREATE ANY SEQUENCE to DBA with admin option;
grant CREATE EXTERNAL JOB to DBA with admin option;
grant DEBUG ANY PROCEDURE to DBA with admin option;
grant DROP PUBLIC SYNONYM to DBA with admin option;
grant EXECUTE ANY LIBRARY to DBA with admin option;
grant EXECUTE ANY PROGRAM to DBA with admin option;
grant FLASHBACK ANY TABLE to DBA with admin option;
grant GRANT ANY PRIVILEGE to DBA with admin option;
grant READ ANY FILE GROUP to DBA with admin option;
grant SELECT ANY SEQUENCE to DBA with admin option;
grant CREATE ANY DIMENSION to DBA with admin option;
grant CREATE ANY DIRECTORY to DBA with admin option;
grant CREATE ANY INDEXTYPE to DBA with admin option;
grant CREATE ANY PROCEDURE to DBA with admin option;
grant CREATE DATABASE LINK to DBA with admin option;
grant DROP ANY SQL PROFILE to DBA with admin option;
grant EXECUTE ANY OPERATOR to DBA with admin option;
grant EXECUTE ANY RULE SET to DBA with admin option;
grant EXPORT FULL DATABASE to DBA with admin option;
grant GLOBAL QUERY REWRITE to DBA with admin option;
grant IMPORT FULL DATABASE to DBA with admin option;
grant ALTER ANY SQL PROFILE to DBA with admin option;
grant CREATE PUBLIC SYNONYM to DBA with admin option;
grant DEBUG CONNECT SESSION to DBA with admin option;
grant DROP ROLLBACK SEGMENT to DBA with admin option;
grant EXECUTE ANY INDEXTYPE to DBA with admin option;
grant EXECUTE ANY PROCEDURE to DBA with admin option;
grant FORCE ANY TRANSACTION to DBA with admin option;
grant MANAGE ANY FILE GROUP to DBA with admin option;
grant SELECT ANY DICTIONARY to DBA with admin option;
grant ALTER ROLLBACK SEGMENT to DBA with admin option;
grant ANALYZE ANY DICTIONARY to DBA with admin option;
grant CREATE ANY SQL PROFILE to DBA with admin option;
grant SELECT ANY TRANSACTION to DBA with admin option;
grant CREATE ROLLBACK SEGMENT to DBA with admin option;
grant CREATE MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER SQL TUNING SET to DBA with admin option;
grant CREATE EVALUATION CONTEXT to DBA with admin option;
grant DROP PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY MATERIALIZED VIEW to DBA with admin option;
grant GRANT ANY OBJECT PRIVILEGE to DBA with admin option;
grant ADMINISTER DATABASE TRIGGER to DBA with admin option;
grant ADMINISTER RESOURCE MANAGER to DBA with admin option;
grant ALTER ANY MATERIALIZED VIEW to DBA with admin option;
grant CREATE PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER ANY EVALUATION CONTEXT to DBA with admin option;
grant CREATE ANY MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER ANY SQL TUNING SET to DBA with admin option;
grant CREATE ANY EVALUATION CONTEXT to DBA with admin option;
grant EXECUTE ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER on SYS.MAP_OBJECT to DBA;
grant DELETE on SYS.MAP_OBJECT to DBA;
grant INSERT on SYS.MAP_OBJECT to DBA;
grant SELECT on SYS.MAP_OBJECT to DBA;
grant UPDATE on SYS.MAP_OBJECT to DBA;
grant ON COMMIT REFRESH on SYS.MAP_OBJECT to DBA;
grant QUERY REWRITE on SYS.MAP_OBJECT to DBA;
grant DEBUG on SYS.MAP_OBJECT to DBA;
grant FLASHBACK on SYS.MAP_OBJECT to DBA;
grant EXECUTE on SYS.DBMS_FLASHBACK to DBA;
grant EXECUTE on SYS.OUTLN_PKG to DBA;
grant EXECUTE on SYS.OUTLN_EDIT_PKG to DBA;
grant EXECUTE on SYS.DBMS_RESUMABLE to DBA;
grant EXECUTE on SYS.DBMS_DEFER_QUERY to DBA;
grant EXECUTE on SYS.DBMS_DEFER_SYS to DBA;
grant EXECUTE on SYS.DBMS_STORAGE_MAP to DBA;
grant UPDATE on WKSYS.WK$SYS_CONFIG to DBA;
grant EXECUTE on PORTAL.WWV_EXPORT to DBA;
grant EXECUTE on SYS.DBMS_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_INTERNAL_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_SERVER_ALERT to DBA;
grant EXECUTE on SYS.DBMS_TDB to DBA;
grant ALTER on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AW$ to DBA;
grant DEBUG on SYS.AW$ to DBA;
grant SELECT on SYS.PS$ to DBA;
grant DEBUG on SYS.PS$ to DBA;
grant SELECT on SYS.AW_PROP$ to DBA;
grant DEBUG on SYS.AW_PROP$ to DBA;
grant SELECT on SYS.AW_OBJ$ to DBA;
grant DEBUG on SYS.AW_OBJ$ to DBA;
grant EXECUTE on SYS.DBMS_SERVER_TRACE to DBA;
grant EXECUTE on SYS.DBMS_SERVICE to DBA;
grant EXECUTE on SYS.DBMS_MONITOR to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;
grant EXECUTE on SYS.DBMS_UADV_ARR to DBA;
grant EXECUTE on SYS.DBMS_UNDO_ADV to DBA;
grant EXECUTE on SYS.LOAD_UNDO_STAT to DBA;
grant EXECUTE on SYS.RESET_UNDO_STAT to DBA;
grant EXECUTE on SYS.DBMS_FEATURE_USAGE_REPORT to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_CAPTURE to DBA;

PL/SQL procedure successfully completed.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i

Junior DBA Interview Question

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business would. So, I decided to write a couple of articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Junior DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions:

1. Give the three modes of starting an oracle database and explain each?
2. Give the three modes of shutting down an Oracle database and explain each?
3. Who can start and stop an Oracle database
4. How do you create a user account in an Oracle database?
5. How do you delete a user account in an Oracle database?
6. Explain the difference between an Oracle database and Oracle instance?
7. Name three physical objects that can be seen from the OS layer of Database Server?
8. Name two Oracle OS layer processes?
9. What is the utility to write an Oracle database to an OS layer file?
10. What is the utility to read an Oracle database to an OS layer file?

Answers:

1. Give the three modes of starting an oracle database and explain each?

Startup nomount – The pfile or initfile is read and memory realm is started.

Startup mount –The controlfile is read and files verified.

Startup –The datafiles are opened in read/write mode and the database is open in read/write mode.

2. Give the three modes of shutting down an Oracle database and explain each?

Shutdown – All new connections are refused, current connections are maintained until the users log off, and once all user connections are completed the database shuts down.

Shutdown immediate – All new connections are refused, all current connection activities are rolled back and disconnected, the database shuts down.

Shutdown abort – All connections are disconnected without roll back and the database shuts down.

3. Who can start and stop an Oracle database?

– The SYS user or any user with sysdba privileges can shutdown an Oracle database.

4. How do you create a user account in an Oracle database?

– Create user ‘username’ identified by ‘password’;

5. How do you delete a user account and all objects owned by that user in an Oracle database?

– Drop user ‘username’ cascade;

6. Explain the difference between an Oracle database and Oracle instance?

-Oracle database includes all of the physical objects of an Oracle environment which reside at the OS layer.

-Oracle Instance is the memory realm and OS processes of the Oracle environment.

7. Name three physical objects that can be seen from the OS layer of Database Server?

-controlfiles
-datafiles
-tempfiles
-redo logs
-archive redologs

8. Name two Oracle OS layer processes?

-PMON
-SMON
-ARCN
-MMON
-MMNL
-MMAN
-LGWR
-CKPT

9. What is the utility to write an Oracle database to an OS layer file?

-EXP or export in any version of Oracle.
-EXPDP or export data pump in Oracle 10g or higher

10. What is the utility to read an Oracle database to an OS layer file?

-IMP or import in any version of Oracle.
-IMPDP or import data pump in Oracle 10g or higher

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com