Interview Questions For Dba

Interview questions for DBA By admin | August 2, 2005

1. How many memory layers are in the shared pool? 2. How do you find out from the RMAN catalog if a particular archive log has been backed-up? 3. 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? 4. Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA? 5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it? 6. 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? 7. How do you tell what your machine name is and what is its IP address? 8. How would you go about verifying the network name that the local_listener is currently using? 9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance? 10. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process? 11. What is the recommended interval at which to run statspack snapshots, and why? 12. 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? 13. 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. 14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM? 15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do? 16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed? 17. How would you best determine why your MVIEW couldn’t FAST REFRESH? 18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)? 19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment? 20. How would you begin to troubleshoot an ORA-3113 error? 21. Which dictionary tables and/or views would you look at to diagnose a locking issue? 22. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed’, how do you approach troubleshooting this issue? 23. How would you extract DDL of a table without using a GUI tool? 24. You’re getting high “busy buffer waits’ - how can you find what’s causing it? 25. What query tells you how much space a tablespace named “test’ is taking up, and how much space is remaining? 26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect. 27. 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.

28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris). 29. How do you increase the OS limitation for open files (LINUX and/or Solaris)? 30. 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. 31. Explain how you would restore a database using RMAN to Point in Time? 32. How does Oracle guarantee data integrity of data changes? 33. Which environment variables are absolutely critical in order to run the OUI? 34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account? 35. Why does Oracle not permit the use of PCTUSED with indexes? 36. What would you use to improve performance on an insert statement that places millions of rows into that table? 37. What would you do with an “in-doubt” distributed transaction? 38. What are the commands you’d issue to show the explain plan for “select * from dual’? 39. In what script is “snap$” created? In what script is the “scott/tiger” schema created? 40. 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? 41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com? 42. 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? 43. 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? 44. Explain the concept of the DUAL table. 45. What are the ways tablespaces can be managed and how do they differ? 46. From the database level, how can you tell under which time zone a database is operating? 47. What’s the benefit of “dbms_stats” over “analyze”? 48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside? 49. 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? 50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.

What are things which play major role in designing the backup strategy? What are RTO, MTBF, and MTTR? What are the various tape backup solutions available in the market? What is RAID? What is RAID0? What is RAID1? What is RAID 10? What is the difference between physical and logical backups? What is hot backup and what is cold backup? When do you recommend hot backup? What are the pre-reqs? How do you generate the begin backup script?

Give me the steps for recovering the full database from cold backup? Give me the steps for TIME based recovery from the full database from hot backup. ? Give me the steps for SCN based recovery from the full database from hot backup. ? Give me the steps for CANCEL based recovery from the full database from hot backup. ? Give me the steps for recovery with missing archived redo logs? Give me the steps for recovery with missing online redo logs? Give me the steps for recovery of missing data file? Give me the steps for physical standby database creation? What is db_recovery_file_dest ? When do you need to set this value ? Give me the steps to perform the point in time recovery with a backup which is taken before the resetlogs of the db ? Tell me about the steps required to enable the RMAN backup for a target database ? What is the difference between obsolete RMAN backups and expired RMAN backups ? What is recovery catalog ? Why do you need this ? How do you install the RMAN recovery catalog ? Can you skip specific tables when using RMAN DUPLICATE feature ? What is FRA ? When do you use this ? How do you clone the database using RMAN software ? Give me the brief steps ? when do you use crosscheck command ? How do you setup the RMAN tape backups ? How do you identify the expired,active,obsolete backups ? Which RMAN command you use ? What is channel ? How do you enable the parallel backups with RMAN ? What is auxiliary channel in RMAN? When do you need this ? How do you identify what are the all the target databases that are being backed-up with RMAN database ? Give me some of the RMAN catalog view names which contains the catalog information ? What is oracle wallet ? Tell me about the encryption options available with RMAN ? List atleast 6 advantages of RMAN backups compare to traditional hot backups ? How do you identikit the block corruption in RMAN database ? How do you fix this ? How do you verify the integrity of the image copy in RMAN environment ? How do you enable the encryption for RMAN backups ? What is backup set ? What is the difference between cumulative incremental and differential incremental backups ? How do you enable the autobackup for the controlfile using RMAN ? What is configure command ? When do you use this ? Give me the steps for setting up the physical stand by database with RMAN ? Give me the steps for changing the DBID in a cloned environment ? What is the significance of incarnation and DBID in the RMAN backups ? What are the steps which needs to be performed in $ORACLE_HOME for enabling the RMAN backups with netbackup or TSM tape library software ?

RMAN is an Oracle tool for taking the backup and recovering the databases. You can copy, restore, and recover datafiles, control files and archived redo logs. It has command line utility as well as GUI-

based Enterprise Manager Backup. Here we are discussing the command line utility. For example, we have TEST and PROD databases. Here TEST is the catalog database and PROD is the target database for which the backup has to be taken. You may loose your backup if you have your catalog and target databases on the same box and the box crashes .So it is always advisable to keep a separate database for RMAN catalog . Create two databases. One for RMAN catalog(Test) and one target(PROD). Both the Databases should be archive log mode. In catalog database (test) create a tablespace 'CATALOG' create a user rman/rman and give the connect , resource, recovery_catalog_owner Also give unlimited quota on CATALOG tablespace. Create recovery catalog c:\rman catalog rman/rman@test log = create_rmanlog.log Recovery Manager: Release 8.1.7.0.0 - Production RMAN-06008: connected to recovery catalog database RMAN-06428: recovery catalog is not installed RMAN> RMAN> create catalog tablespace 'RCVCAT'; RMAN> create catalog tablespace 'CATALOG'; RMAN-06431: recovery catalog created RMAN> Register the database. c:\>rman catalog rman/rman@test target internal/oracle@prod Recovery Manager: Release 8.1.7.0.0 - Production RMAN-06005: connected to target database: PROD (DBID=4145212838) RMAN-06008: connected to recovery catalog database RMAN> register database; RMAN-03022: RMAN-03023: RMAN-08006: RMAN-03023: RMAN-08002: RMAN-08004:

compiling command: register executing command: register database registered in recovery catalog executing command: full resync starting full resync of recovery catalog full resync complete

Backup the datafile RMAN> run{ Allocate channel c1 type disk; Backup datafile ‘c:\orawin_815\oradata\prod\system01.dbf’ format ‘c:\backup\temp01.dbf’; } You can change the backup fine format including the destination. In this example, this backup file is created on the local drive of the system.

11g Release 2 Installation on Suse and Enterprise Linux 5 Introduction This articles covers the Installation of Oracle Database 11g Release 2 on Enterprise Linux 5 and Suse 11. Oracle 11g Release 2 launched in August 2009. Now, the product is available on Linux platform. There are almost 400 aditional functionality added by Oracle 11g. After watching the Oracle webcast 11g Release 2, I feel Oracle 11g Release more focus on High Availability(easy RAC installation and implementation),Compressing Data,Security,etc. 11g Release 2 required minimum 1gb RAM for the installation. I would recommend minimum 4gb ram will be ideally better. Swap should have double of physical ram. I recommend the following Server Configuration: Intel Xeon Processor W5580 3.20ghz L3 8mb Cache 1 Quad Core 4 to 8gb Ram (an ideal configuration) . 146gb HDD (default) - This configuration can be changed, based on various factors.

The following packages is mandatory for Enterprise Linux : GNOME Desktop Environment ,Graphical Internet, X Window System ,Editors , Development Tools ,Server Configuration Tools,System Tools and Administration Tools. You can download the software, subject to Oracle Licensing. http://www.oracle.com/technology/software/products/database/index.html Standard Edition One – support upto 2 cpu Standard Edition – support upot 4 cpu Enterprise Edition(11.2.0.1.0)– unlimited. Linux x86 | Disk 1, Disk 2 (2.1 GB)

Set the Linux Kernal Parameters: Linux Kernel Parameters What is the Shared Memory ( reference with various linux/unix sites for these parameters.): The shared memory will hve huge impact on Oracle Performance . So very carefully to be handled. Shared memory allows processes to access common structures and data by placing them in shared memory segments. It's the fastest form of IPC (Interprocess Communication) available since no kernel involvement occurs when data is passed between the processes. Oracle uses shared memory segments for the SGA (Shared Global Area) which is an area of memory that is shared by all Oracle background and foreground processes. Setting SHMMAX Parameter This parameter defines the maximum size in bytes for a shared memory segment. Since the SGA is comprised of shared memory, SHMMAX can potentially limit the size of the SGA. Setting SHMMNI Parameter This parameter sets the maximum number of shared memory segments system wide. Setting SHMALL Parameter This parameter sets the total amount of shared memory in bytes that can be used at one time on the system. The SEMMSL Parameter This parameter defines the maximum number of semaphores per semaphore set. Oracle recommends to set SEMMSL to the largest PROCESSES init.ora parameter of any database on the Linux system. The SEMMNI Parameter This parameter defines the maximum number of semaphore sets in the entire Linux system. The SEMMNS Parameter This parameter defines the total number of semaphores (not semaphore set) in the entire Linux system. The SEMOPM Parameter This parameter defines the maximum number of semaphore operations that can be performed per semop(2) system call. Setting File Handles The maximum number of file handles denotes the maximum number of open files that you can have on the Linux system. Setting System Wide Limit for File Handles The value in /proc/sys/fs/file-max sets the maximum number of file handles or open files that the Linux kernel will allocate. When you get error messages about running out of file handles, then you might want to raise this limit. Change the parameters: Copy sysctl.conf

cp sysctl.conf sysctl.confold Cd /etc Vi sysctl.conf Add the following: kernel.shmmax = 2147483648 kernel.shmall = 2097152 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 # 512 * PROCESSES net.core.rmem_default=4194304 net.core.rmem_max=4194304 net.core.wmem_default=262144 net.core.wmem_max=262144 net.ipv4.ip_local_port_range = 1024 65000 Press Esc :wq

Change the Hosts File Entry Cd /etc Cp hosts hostsold Vi hosts IP-address Machinename.domainname Hostname Press Esc and :wq Go to /etc/security (cd /etc/security) Vi limits.conf Add or change /etc/security/limits.conf file: oracle soft nofile 1024 oracle hard nofile 65536 oracle soft nproc 2047 oracle hard nproc 16384 to save Esc :wq Go to /etc/pam.d (cd /etc/pam.d) Vi login Add or change line to the /etc/pam.d/login file session required pam_limits.so session required /lib/security/pam_limits.so to save Esc :wq

Very important Steps: On secure linux has to be disabled: Go to /etc/selinux(cd /etc/selinux) Vi config Change or add SELINUX=disabled to save Esc :wq The following packages(rpm) required for Enterprise Linux 5.0 on various cd’s :

Insert the cd and mount the same Go to cd /media/cdrom/Server (cd /media/cdrom/Server) (carefully use –force and –nodeps) rpm -Uvh --force --nodeps binutils-2.17.50.0.6 rpm -Uvh --force --nodeps compat-libstdc++-33-3.2.3 rpm -Uvh --force –nodeps elfutils-libelf-0.125 rpm -Uvh --force –nodeps elfutils-libelf-devel-0.125 rpm -Uvh --force –nodeps elfutils-libelf-devel-static-0.125 rpm -Uvh --force –nodeps gcc-4.1.2 rpm -Uvh --force –nodeps gcc-c++-4.1.2 rpm -Uvh --force –nodeps glibc-2.5-24 rpm -Uvh --force –nodeps glibc-common-2.5 rpm -Uvh --force –nodeps glibc-devel-2.5 rpm -Uvh --force –nodeps glibc-headers-2.5 rpm -Uvh --force –nodeps kernel-headers-2.6.18 rpm -Uvh --force –nodeps ksh-20060214 rpm -Uvh --force –nodeps libaio-0.3.106 rpm -Uvh --force –nodeps libaio-devel-0.3.106 rpm -Uvh --force –nodeps libgcc-4.1.2 rpm -Uvh --force –nodeps libgomp-4.1.2 rpm -Uvh --force –nodeps libstdc++-4.1.2 rpm -Uvh --force –nodeps libstdc++-devel-4.1.2 rpm -Uvh --force –nodeps make-3.81 rpm -Uvh --force --nodeps sysstat-7.0.2 rpm -Uvh --force --nodeps unixODBC-2.2.11 rpm -Uvh --force --nodeps unixODBC-devel-2.2.11 eject or unmount Linux Enterprise Server 11 - SUSE: rpm rpm rpm rpm rpm rpm rpm rpm rpm rpm rpm rpm rpm rpm rpm

-Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh -Uvh

--force --force --force --force --force --force --force --force --force --force --force --force --force --force --force

–nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps –nodeps

binutils-2.19 gcc-4.3 gcc-c++-4.3 glibc-2.9 glibc-devel-2.9 ksh-93t libstdc++33-3.3.3 libstdc++43-4.3.3_20081022 libstdc++43-devel-4.3.3_20081022 libaio-0.3.104 libaio-devel-0.3.104 libgcc43-4.3.3_20081022 libstdc++-devel-4.3 make-3.81 sysstat-8.1.5

Create user and groups Adding Groups groupadd oinstall groupadd dba groupadd asmadmin Adding user and assign the group: useradd -g oinstall -G dba,oper,asmadmin oracle

Add password for Oracle user passwd oracle I would like to light on the "asmadmin" group is used for ASM.

Make directories in which the Oracle Binary to be installed: mkdir -p /data1/app/oracle/product/11/db_1 chown -R oracle:oinstall /data1 chmod -R 777 /data1 Shutdown or reboot the machine Login as Oracle Change/add user enviroment Go to /home/oracle Vi .bash_profile Add the following: ORACLE_HOSTNAME=machinename.domainname ORACLE_BASE=/data1/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1; export ORACLE_HOME ORACLE_SID=orcl; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi Press Esc key :wq Log out Login as Oracle user Run Install Steps: Set the Display first export DISPLAY=hostname:0.0 Or DISPAY=HOSTNAME:0.0 export DISPLAY Go setup Disk directory Cd Disk1 ./runInstaller

Rest follow the GUI provided by Oracle