Interveiw Questions - DBA

8. What is the proper method for disabling and re-enabling a primary key consraint?
Ans: ALTER TABLE commnad. For the enable clause, sepecify the USING INDEX and TABLESPACE clasue for primary key.

DS: (1) ALTER TABLE schema.table MODIFY PRIMARY KEY DISABLE
(2) ALTER TABLE schema.table MODIFY PRIMARY KEY USING INDEX TABLESPACE tablespace_name ENABLE

9. What happens if a primary key constraint is disabled and then enabled without fully specifying the index clause
Ans: the index is created in the user's default tablespace and all sizing information is lost. Oracle doesn't store this information as a part of the contraint definition, but only as part of the index definition, when the contraint was disabled the index was dropped and the information is gone. (?? DS: what the meaning of sizing information)

10. (on UNIX) When should more than one DB writer process be used? How many should be used?
Ans: If the UNIX system being used is capable of asynchronous IO, then only one is required, if the system in not capable of asynchronous IO, then up to twice the number of disks used by Oracle. The number of DB writers shoud be specified by use of the DB_WRITERS initialziation parameter .
(?? DS: DB_WRITERS is pre-release 8.0 name, DBWR_IO_SLAVES)
---------------------------------
from Oracle9i Database ReferenceRelease 2 (9.2)Part Number A96536-01
-------------------------------

DBWR_IO_SLAVES is relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.
If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false.
Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently. However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.
I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.
-------------
DS: Asynchronous I/O interfaces have been available in Solaris for some time, providing a means by which applications could issue I/O requests and not have to "block" or cease working until the I/O was completed.


11. You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?
Ans: You can't use hot backup without being in archivelog mode. So no, you couldn't recover

12. What causes the "snapshot too old" error? How can this be prevented or mitigated?
Ans: This is caused by large or long running transactions that have either wrapped onto their own rollback space or have had another transaction write on part of their rollback space. This can be prevented or mitigated by breaking the transaction into a set of smaller transactions or increasing the size of the rollback segments and their extents.

DS: additional refs: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923

13. How can you tell if a database object is invalid?
Ans: By checking the status column of the DBA_, ALL_ or USER_OBJECTS views.

14. A user is getting an ORA-00942 error yet you know you have granted them permission on the table, what else should you check?
Ans: check the user has specified the full name of the object (e.g. select empid from scott.emp; instead of select empid form emp;) or has a synonym that points to the object (create synonym emp for scott.emp;)

15. A developer is trying to create a view and the database won't let him. He has the "DEVELOPER" role which has the "CREATE VIEW" system privilege adn SELECT grants on the tables he is using, what is the problem?
Ans: You need to verify the developer has direct grants on all talbes used in the view. You can't create a stored object with grants given through views
(DS: plan to create a test case to verify)

16. If you have an example table, what is the best way to get sizeing data for the production table imeplementation?
Ans: The best way is to analyze the table and then use the data provide in the DBA_TABLES view to get the average row length and other pertinent data for the caculation. The quick and dirty way is to look at the number of blocks the table is actually using and ratio the number of rows in the table to its number of blocks against the number of expected rows. [SN0606291540]

17. How can you find out how many users are currently logged into the database? How can you find their operating system id?
Ans: several ways:
(1) v$session or v$process
(2) check 'current_logins' parameter in the v$sysstat view
(3) If on unix, "ps -efgrep oracle wc -l?" (only works against a single instance installation)

18 skip
19. How can you determine if an index needs to be dropped and rebuilt?
Ans: run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of ...

Install Oracle 9i on FC3 - second time

(1) following the steps in:

(2) export DISPLAY=:0.0
Note when export DISPLAY=localhost:0.0 , 'xterm' does not work

(3) the following "xorg" packages are presented:
(not nessessary everyone of them is needed. Encounter "libXo.so.6 not found" error before.
With these packages, installation proceed)
% rpm -q grep "xorg"sort
fonts-xorg-100dpi-6.8.1-1
fonts-xorg-75dpi-6.8.1-1
fonts-xorg-base-6.8.1-1
xorg-x11-6.8.1-12
xorg-x11-deprecated-libs-6.8.1-12
xorg-x11-deprecated-libs-devel-6.8.1-12
xorg-x11-devel-6.8.1-12
xorg-x11-font-utils-6.8.1-12
xorg-x11-libs-6.8.1-12
xorg-x11-Mesa-libGL-6.8.1-12
xorg-x11-Mesa-libGLU-6.8.1-12
xorg-x11-tools-6.8.1-12
xorg-x11-twm-6.8.1-12
xorg-x11-xauth-6.8.1-12
xorg-x11-xfs-6.8.1-12
xorg-x11-Xnest-6.8.1-12
xorg-x11-Xvfb-6.8.1-12

SQL Commads for manage log files

Chapter 1 Management of logfile

1. forcing log switch
sql> alter system switch logfile;

2. forcing checkpoints
sql> alter system checkpoint;

3. adding online redolog groups
sql> alter database add logfile [group 4]
sql> ('/disk3/log4a.rdo', '/disk4/log4b.rdo') size 1M;

4. adding online redo log members
sql> alter database add logfile member
sql> '/disk3/log1b.rdo' to group 1,
sql> '/disk4/log2b.rdo' to group 2;

5. changes the name of the online redo logfile
sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'
sql> to 'c:/oracle/oradata/redo01.log'

6. drop online redo log groups
sql> alter database drop logfile group 3;

7. drop online redo log members
sql> alter database drop logfile member
sql> 'c:/oracle/oradata/redo01.log';

8. clearing online redo log files
sql> alter database clear [unarchived] logfile
sql> 'c:/oracle/log2a.rdo'

9. using logminer analyzing redo logfiles
(a) in the init.ora specify utl_file_dir=' '
(b) sql> execute dbms_logmnr_d.build('oradb.ora', 'c:\oracle\oradb\log');
(c) sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log, dbms_logmnr.new);
(d) sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log', dbms_logmnr.addfile)
(e) sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');
(f) sql> select * from v$logmnr_contents(v$logmnr_dictionary, v$logmnr_parameters, v$logmnr_logs);
(g) sql> execute dbms_logmnr.end_logmnr;

Install Oracle 9i on FC 3

Note: this installation failed

REF: (1) http://ivan.kartik.sk/oracle/install_ora9_fedora.html
(2) http://www.tldp.org/HOWTO/html_single/Oracle-9i-Fedora-3-Install-HOWTO/#sect_06_01

Added YS annotation:


Installation of Oracle 9i (R2) on Fedora Core Linux 2, 3 and 4

This paper (HOWTO ref 1) describes step-by-step installation of Oracle 9i database software on Fedora Core 2, 3 and 4.

This paper covers following steps:

• Pre-Instalation Tasks
• Download & Install
• Post-Instalation Tasks
• Common Installation Errors

1. Pre-Instalation Tasks 1. Create oracle User Account
Login as root and create te user oracle which belongs to dba group.

su -
# groupadd dba
# useradd -g dba oracle

# passwd oracle
(YS: ignore bad password retype the password again)

2. Setting System parameters
Edit the /etc/sysctl.conf and add following lines:

kernel.sem = 250 32000 100 128
kernel.shmmax = 2147483648
kernel.shmmni = 128
kernel.shmall = 2097152
kernel.msgmnb = 65536
kernel.msgmni = 2878
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Note: You need execute "sysctl -p" or reboot system to apply above settings.
YS: type
% sysctl -p /etc/sysctl.conf

Edit the /etc/security/limits.conf file and add following lines:

* - nproc 16384
* - nofile 16384

3. Setting Oracle Enviroment
Edit the /home/oracle/.bash_profile file and add following lines:

ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/9.2.0.4
ORACLE_SID=FC3DB1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_ASSUME_KERNEL=2.4.1
THREADS_FLAG=native
ORACLE_OEM_JAVARUNTIME=/opt/jre
PATH=$PATH:$ORACLE_HOME/bin

export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH LD_ASSUME_KERNEL THREADS_FLAG ORACLE_OEM_JAVARUNTIME PATH

Save the .bash_profile and execute following commands for load new enviroment:

cd /home/oracle
. .bash_profile

Additional step (YS)
- create directory
% mkdir /opt/app
% cd /opt/app
% mkdir oracle
% chown oracle.dba oracle
% chmod 775 oracle

Download & Install

1. Download and install required .rpm packages

Some additional packages are required for succesful instalation of Oracle software. To check wheter required packages are installed on your operating system use following command:

% rpm -q gcc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc

YS: -------------------- result in my machine: -----------------------------------------------
[root@localhost app]# rpm -q gcc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc
gcc-3.4.2-6.fc3
glibc-headers-2.3.3-74
glibc-kernheaders-2.4-9.1.87
glibc-devel-2.3.3-74
package compat-libstdc++ is not installed
cpp-3.4.2-6.fc3
package compat-gcc is not installed

-------------------------------------------------------------------------

If some package is not installed download it from Fedora project website or direct from Core 2, Core 3 or Core 4 locations.

This step is required for Fedora Core 3 and 4 only. Fedora Core 3 is shipped with compat-gcc-8.3.3.4. Fedora Core 4 is shipped with compat-gcc-32-3.2.3. These package are GCC 3.x compiler which are not useful for succesful inatallation. Check whether compat-gcc-8.3.3.4 or compat-gcc-32-3.2.3 is installed. If so then uninstall it using folowing command:

rpm -e compat-gcc

Download the compat-gcc-7.3-2.96.126.i386.rpm package from Core 2 location or from here.


Install the required packages using the rpm command:

# rpm -ivh gcc-3.4.2-6.fc3.i386.rpm glibc-headers-2.3.3-74.i386.rpm glibc-kernheaders-2.4-9.1.87.i386.rpm glibc-devel-2.3.3-74.i386.rpm compat-libstdc++-3.4.2-6.fc3.i386.rpm cpp-3.4.2-6.fc3.i386.rpm compat-gcc-7.3-2.96.126.i386.rpm

---------------------------------------------------------------------------------------
YS: In my case, dowload the following four packages and install:
Note : compat-libstdc++-3.4.2-6.fc3.i386.rpm ( can not find this package)

• compat-libstdc++-7.3-2.96.126.i386.rpm

• compat-libstdc++-devel-7.3-2.96.126.i386.rpm

• compat-gcc-7.3-2.96.126.i386.rpm

• compat-gcc-c++-7.3-2.96.126.i386.rpm

using: rpm -i --force
to install above 4 packgaes

% rpm -i --force compat-*.rpm
----------------------------------------------------------------------------

For Fedora Core 4 only:Install the required additional packages using the rpm command:

# rpm -ivh compat-libgcc-296-2.96-132.fc4 compat-libstdc++-33-3.2.3-47.fc4 compat-libstdc++-296-2.96-132.fc4

If all required packages were installed succesfuly then login as root and switch the GCC3 compiler binary with GCC2 compiler binary as following:

su -
# cd /usr/bin
# mv ./gcc ./gcc3
# mv ./gcc296 ./gcc

YS: in my case do the following:
---------------------------------------------

# mv /usr/bin/gcc /usr/bin/gcc342
# mv /usr/bin/g++ /usr/bin/g++342
# ln -s /usr/bin/gcc296 /usr/bin/gcc
# ln -s /usr/bin/g++296 /usr/bin/g++

2. Download the Java Runtime Enviroment (j2re-1_3_1_15-linux-i586.bin) from the Sun website. Keep in mind you need to download j2re1.3.1_11 or higher (Note: Install JRE 1.3.1_1x version only).

YS: downloaed j2re-1_3_1_18-linux-i586.bin ( from: http://java.sun.com/j2se/1.3/download.html)
Login as root and make the the file executable and then execute it. When the JRE is exracted move the "jre1.3.1_15" di rectory to "/opt" directory.

# chmod +x j2re-1_3_1_18-linux-i586.bin
# ./j2re-1_3_1_18-linux-i586.bin
# mv jre1.3.1_18 /opt/
% cd /opt/
% ln -s jre1.3.1_18 jre
3. Download the Oracle 9i (9.2.0.4) software from Oracle website. (YS: skip. Already have disks)
Extract the files using following command: <>
When all archives were extracted you've got three directories Disk1, Disk2 and Disk3.
Edit the Disk1/install/linux/oraparam.ini and modify JRE_LOCATION variable and set path to our JRE installation from Step 2.

JRE_LOCATION=/opt/jre

4. Start the Oracle software installation process.

Now the system is prepared for Oracle software installation. To start the installation process execute the following commands: Unable to load native library: /tmp/OraInstall2005-01-08_11-11-34AM/jre/lib/i386/libjava.so: symbol __libc_wait, version GLIBC_2.0 not defined in file libc.so.6 with link time reference
Solution: Install new JRE 1.3.1 version. Edit the Disk1/install/linux/oraparam.ini and set path to new JRE for JRE_LOCATION variable. For more information see Download & Install section.

Error in invoking target install of makefile /opt/oracle/920/ctx/lib/ins_ctx.mk
Error in invoking target install of makefile /opt/oracle/920/precomp/lib/ins_precomp.mk
Error in invoking target install of makefile /opt/oracle/920/plsql/lib/ins_plsql.mk
Error in invoking ntcontab.o of makfile /opt/oracle/920/network/lib/ins_net_client.mk
Solution: Install the compat-gcc-7.3-2.96.126.i386.rpm (gcc 2.96) package and change the GCC binaries. For more information see Download & Install section.

/tmp/OraInstall2005-06-14_03-46-25PM/jre/bin/i386/native_threads/java: error while loading shared libraries: /tmp/OraInstall2005-06-14_03-46-25PM/jre/lib/i386/native_threads/libhpi.so: cannot restore segment prot after reloc: Permission denied
Solution: Modify /etc/selinux/config and change value of SELINUX to "disabled" and reboot computer.

Install Fedora Core 3 on the notebook - dual boot with windows xp home edition

- Intallation of FC, dual boot successful
- Acess windows partition: ntfs
ref: http://www.linux-ntfs.org/content/view/127/63/
* downloand linux-ntfs package, install

* mount problem
[root@localhost mnt]# mount /dev/hda5 /mnt/window1
mount: fs type ntfs not supported by kernel

- list all partitions on all drives:
% sfdisk -l ( fdisk -l)
% cat /proc/filesystems ( should see ntfs)

- decide which release you have. Run this command
% cat /etc/redhat-release

Next find out your kernel version:

    uname -r

Install You must be root for the rest of the commands. The examples will continue as if you downloaded kernel-ntfs-2.4.18-14.i686.rpm.

Note: Newer NTFS RPMs have names like

    kernel-module-ntfs-2.6.8-1.541-2.1.17-0.fc.1.2.i586.rpm

- show swap size:
% swapon -s

Deinstalling the Oracle software from Windows

Deinstalling the Software

How to Deinstall All Oracle Software

1. If you have already installed Oracle software, use Oracle Database Configuration Assistant to delete all current instances. Make sure that the necessary services are started in Control Panel Services before doing this.
2. Stop all Oracle-related services in the Services control panel.

3. Deinstall all Oracle products, using the appropriate installers.
(using Oracle Universal installer)
4. Delete all Oracle software directories, including the inventory files in the C:\PROGRAM FILES\ORACLE directory.

(when deleting oci.dll: encounter access denial problem. After deleting the registry entry and reboot the system, it can be manuallly deleted)

How to Uninstall Additional Software
1. Deinstall the following additional software:
JDeveloper
Apache
JDK

Checking the Registry
How to Check Registry Entries
1. Select Start > Run, enter REGEDIT, and click OK.
2. At the Windows NT Registry Editor, expand the HKEY_LOCAL_MACHINE\SOFTWARE registry item.
3. Delete the ORACLE key.
4. Expand the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services registry item.
5. Delete all keys that begin with ORACLE.
6. Expand the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application registry item.
7. Delete all keys that begin with ORACLE.
8. Repeat steps 5 through 8 for any other ControlSet references in your register that contain ORACLE keys.

Checking Environment Variables
How to Check Environment Variables
1.
Select Start > Settings > Control Panel and double-click System.
2.
Delete all Oracle-related environment variables (especially any ORACLE_HOME variables).
3.
Delete all Oracle-related paths from the PATH variable.
4.
Delete any reference to the JDK directory from the PATH variable.

A DBA Job Description

Ref: Oracle Datatabase Administration, David C. Kreines, Brian Laskeytte, O'REILY,1999

"An Oracle DBA must be able to ..."

Oracle architecture and options
- Demonstrate an understanding of the memory structures and processs that make up an Oracle
instance
- Demonstrate an understanding of the logical and physical structure associated with an Oracle database
- Demonstrate an understanding of PL/SQL constructs (triggers, functions, packages, procedures) and their processing
- Demonstrate and understanding of discributed architecture and client server
- Demonstrate and understanding of locking mechanism

Security
- Create, alter, and drop database users
- Monitor and audit database access
- Develop and implement a strategy for managing security (roles, privileges, authentication)
- Demonstrate an understanding of the implications of distributed processing on the security model

Data administration
- Manage integrity constraints
- Implement the physical database from the logical design
- Evaluate the implications of using stored procedures and contraints to implement bussiness rules

Backup and recovery
- Understand backup options
- Develop backup and recovery strategies
- Manage the implementation of backup procedures
- Recover a database

Software maintenance and operation
- Install and upgrade Oracle and supporting products
- Configure the Oracle instance using the initialization parameters
- Configure and manage SQL* Net
- Distinguish among startup and shutdown options
- Create a database
- Demonstrate an understanding of the capabilities of underlying operating systems as they relate to the Oracle database

Resource Management
- Create and manage indexes
- valuate the use of clusters and hash clusters
- Allocate and manage physical storage structures (e.g., datafiles, redo logs, control files)
- Allocate and manage logical storage structures (e.g., tablespaces, schemas, extents)
- Control system resource usage by defining proper profiles
- Perform capacity planning

Tuning and troubleshooting
- Diagnose and resolve locking conflicts
- Use data dictionary tables and views
- Monitor the instance
- Collect and analyze relevant database performance information
- Identify and implement appropriate solutions for database performance problems
- Use vendor support services when necessary
- solve SQL* Net problems