technical interview questions for Oracle E-Business Suite DBA

1)What happens if the ICM goes down?


All the other managers will keep working. ICM only takes care of the queue control requests, which means starting up and shutting down other concurrent managers.

2)How will you speed up the patching process?


You can merge multiple patches.
You can create a response file for non-interactive patching.
You can apply patches with options (nocompiledb, nomaintainmrc, nocompilejsp) and run these once after applying all the patches.


3)How will you handle an error during patching?


Look at the log of the failed worker, identify and rectify the error and restart the worker using adctrl utility.

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


Run pre-clone on the source (all tiers), duplicate the DB using RMAN (or restore the DB from a hot or cold backup), copy the file systems and then run post-clone on the target (all tiers).
Manual steps (there can be many more):
Change all non-site profile option values (RapidClone only changes site-level profile options).
Modify workflow and concurrent manager tables.
Change printers.

5)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.


6)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.

7)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.

8)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.

9)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.


10)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.


11)Provide the location of the DBC file and explain its significance and how applications know the name of the DBC file.



Location: $FND_TOP/secure directory.
Significance: Points to the DB server amongst other things.
The application knows the name of the DBC file by using profile option "Applications Database Id."

10g Release 2 (10.2) Installation

=======================================================================
http://www.oracle.com/pls/db102/homepage

http://download.oracle.com/docs/cd/B19306_01/install.102/b15667/preface.htm

Oracle® Database Installation Guide
10g Release 2 (10.2) for Linux x86-64
Part Number B15667-03

Hardware Requirements
=====================
At least 1 GB of physical RAM

Swap Space
==========
RAM Swap Space
Up to 512 MB 2 times the size of RAM
Between 1024 MB and 2048 MB 1.5 times the size of RAM
Between 2049 MB and 8192 MB Equal to the size of RAM
More than 8192 MB 0.75 times the size of RAM

400 MB of disk space in the /tmp directory

Between 1.5 GB and 3.5 GB of disk space for the Oracle software, depending on the installation type

1.2 GB of disk space for a preconfigured database that uses file system storage (optional)

To ensure that the system meets these requirements:
===================================================
To determine the physical RAM size, enter the following command:
# grep MemTotal /proc/meminfo
If the size of the physical RAM is less than the required size, then you must install
more memory before continuing.

To determine the size of the configured swap space, enter the following command:
# grep SwapTotal /proc/meminfo
If necessary, refer to the operating system documentation for information about
how to configure additional swap space.

To determine the available RAM and swap space, enter the following command:
# free

To determine the amount of disk space available in the /tmp directory, enter the following command:
# df -k /tmp

If there is less than 400 MB of free disk space available in the /tmp directory, then
complete one of the following steps:
Delete unnecessary files from the /tmp directory to meet the disk space
requirement.
Set the TEMP and TMPDIR environment variables when setting the oracle
user's environment
Extend the file system that contains the /tmp directory. If necessary, contact your system administrator for information about extending file systems.

To determine the amount of free disk space on the system, enter the following command:
# df -k

The following table shows the approximate disk space requirements for software
files for each installation type:

Installation Type Requirement for Software Files (GB)
Enterprise Edition 1.9
Standard Edition 1.9
Custom (maximum) 2.0

To determine whether the system architecture can run the software, enter the following command:
# grep "model name" /proc/cpuinfo

Checking the Software Requirements

To determine which distribution and version of Linux is installed, enter the following command:
# cat /proc/version

To determine whether the required kernel is installed, enter the following command:
# uname -r


The following is sample output displayed by running this command on a Red Hat
Enterprise Linux 3.0 system:
2.4.21-27.EL
In this example, the output shows the kernel version (2.4.27) and errata level
(27.EL) on the system.

Kernel version The system must be running the following kernel version (or a later version):
Red Hat Enterprise Linux 3.0:
2.4.21-27.EL
Note: This is the default kernel version.
Red Hat Enterprise Linux 4.0:
2.6.9-11.EL
SUSE Linux Enterprise Server 9.0:
2.6.5-7.201

To determine whether the required packages are installed, enter commands similar to the following:
# rpm -q package_name
install rpm
rpm -Uvh gcc-3.4.4-2.x86_64.rpm

#Checking/Updating the redhat-release File

Verify that the redhat-release RPM is installed on your Red Hat system:

#rpm -q redhat-release

On RHEL AS 4 (for 10g R1), RH9, and FC 1 you have to change the /etc/redhat-release file to make Oracle 10g believe it's running on a supported release.
Regarding RHEL AS 4, the installer for 10g 10.1.0.3 does not recognize RHEL AS 4 as a supported release but 10g R2 OUI does.

To change the /etc/redhat-release file, you can simply copy/paste the following commands:

#su - root
cp /etc/redhat-release /etc/redhat-release.orig
cat > /etc/redhat-release << EOF
Red Hat Enterprise Linux AS release 3 (Taroon)
EOF


Checking the Network Setup
==========================

Configuring Name Resolution

When you run Oracle Universal Installer, an error may occur if name resolution is not
set up. To avoid this error, before you begin installation, you must ensure that host
names are resolved only through the /etc/hosts file.
To ensure that host names are resolved only through the /etc/hosts file:

Verify that the /etc/hosts file is used for name resolution. You can do this by checking the hosts file entry in the nsswitch.conf file as follows:
# cat /etc/nsswitch.conf | grep hosts

The output of this command should contain an entry for files.

Verify that the host name has been set by using the hostname command as follows:
# hostname

The output of this command should be similar to the following:
myhost.mycomputer.com

Verify that the domain name has not been set dynamically by using the domainname command as follows:
# domainname

This command should not return any results.

Verify that the hosts file contains the fully qualified host name by using the following command:
# cat /etc/hosts | grep `eval hostname`

The output of this command should contain an entry for the fully qualified host
name and for localhost.
For example:
192.168.100.16 myhost.us.mycompany.com myhost
127.0.0.1 localhost localhost.localdomain
If the hosts file does not contain the fully qualified host name, then open the file
and make the required changes in it.

Setting the ORACLE_HOSTNAME Environment Variable
=================================================
Use the following procedure to set the ORACLE_HOSTNAME environment variable.
For example, if the fully qualified host name is somehost.us.acme.com, then enter
one of the following commands:
Bourne, Bash, or Korn shell:
$ ORACLE_HOSTNAME=somehost.us.acme.com
$ export ORACLE_HOSTNAME
C shell:
% setenv ORACLE_HOSTNAME somehost.us.acme.com


Creating Required Operating System Groups and Users
===================================================
The following operating system groups and user are required if you are installing
Oracle Database:

The OSDBA group (dba)
You must create this group the first time you install Oracle Database software on
the system. It identifies operating system user accounts that have database
administrative privileges (the SYSDBA privilege). The default name for this group
is dba.

The OSOPER group (oper)
This is an optional group. Create this group if you want a separate group of
operating system users to have a limited set of database administrative privileges
(the SYSOPER privilege). By default, members of the OSDBA group also have the
SYSOPER privilege.

The following operating system group and user are required for all installations:

The Oracle Inventory group (oinstall)
You must create this group the first time you install Oracle software on the system.
The usual name chosen for this group is oinstall. This group owns the Oracle
inventory, which is a catalog of all Oracle software installed on the system.

The Oracle software owner user (typically, oracle)

You must create this user the first time you install Oracle software on the system.
This user owns all of the software installed during the installation. This user must
have the Oracle Inventory group as its primary group. It must also have the
OSDBA and OSOPER groups as secondary groups.

Creating the Oracle Inventory Group
===================================
Determining Whether the Oracle Inventory Group Exists

When you install Oracle software on the system for the first time, Oracle Universal
Installer creates the oraInst.loc file. This file identifies the name of the Oracle
Inventory group and the path of the Oracle Inventory directory.
To determine whether the Oracle Inventory group exists, enter the following command:

# more /etc/oraInst.loc

If the output of this command shows the oinstall group name, then the group already exists.
If the oraInst.loc file exists, then the output from this command is similar to the following:

inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall
The inst_group parameter shows the name of the Oracle Inventory group,oinstall.

Creating the Oracle Inventory Group

If the oraInst.loc file does not exist, then create the Oracle Inventory group by entering the following command:

# /usr/sbin/groupadd oinstall

Creating the OSDBA Group
========================
To determine whether the OSDBA group exists, enter the following command:
# grep dba /etc/group

If the OSDBA group does not exist or if you require a new OSDBA group, then create it as follows.In the following command, use the group name dba unless a group with
that name already exists.

# /usr/sbin/groupadd dba

Creating the Oracle Software Owner User
=======================================
Determining Whether an Oracle Software Owner User Exists
To determine whether an Oracle software owner user named oracle exists, enter the following command:
# id oracle

If the oracle user exists, then the output from this command is similar to the following:
uid=440(oracle) gid=200(oinstall) groups=201(dba),202(oper)


Creating an Oracle Software Owner User
======================================
If the Oracle software owner user does not exist or if you require a new Oracle
software owner user, then create it as follows. In the following procedure, use the user
name oracle unless a user with that name already exists.

To create the oracle user, enter a command similar to the following:

# /usr/sbin/useradd -g oinstall -G dba[,oper] oracle
In this command:
The -g option specifies the primary group, which must be the Oracle

Inventory group, for example oinstall
The -G option specifies the secondary groups, which must include the OSDBA

group and if required, the OSOPER group. For example, dba or dba,oper

Set the password of the oracle user:

# passwd oracle

#To create the oracle account and groups, execute the following commands:

su - root
groupadd dba # group of users to be granted SYSDBA system privilege
groupadd oinstall # group owner of Oracle files
useradd -c "Oracle software owner" -g oinstall -G dba oracle
passwd oracle

Configuring Kernel Parameters
====================================
Using any text editor, create or edit the /etc/sysctl.conf file, and add or edit lines similar to the following:

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

By specifying the values in the /etc/sysctl.conf file, they persist when you
restart the system.
On SUSE systems only, enter the following command to ensure that the system
reads the /etc/sysctl.conf file when it restarts:
# /sbin/chkconfig boot.sysctl on

Setting Shell Limits for the oracle User
========================================
To improve the performance of the software on Linux systems, you must increase the
following shell limits for the oracle user:

Shell Limit Item in limits.conf Hard Limit
=========== ==============================
Maximum number of open file descriptors nofile 65536

Maximum number of processes available to a single user nproc 16384


To increase the shell limits:
Add the following lines to the /etc/security/limits.conf file:

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

Add or edit the following line in the /etc/pam.d/login file, if it does not already exist:
session required pam_limits.so

Depending on the oracle user's default shell, make the following changes to the default shell start-up file:
For the Bourne, Bash, or Korn shell, add the following lines to the
/etc/profile file (or the /etc/profile.local file on SUSE systems):
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
For the C shell (csh or tcsh), add the following lines to the
/etc/csh.login file (or the file on SUSE
systems)/etc/csh.login.local:
if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

Identifying or Creating an Oracle Base Directory
================================================
Identifying an existing Oracle Inventory directory

Enter the following command to view the contents of the oraInst.loc file:

# more /etc/oraInst.loc
If the oraInst.loc file exists, then the output from this command is similar to
the following:
inventory_loc=/u01/app/oracle/oraInventory
inst_group=oinstall

identifying existing Oracle home directories

Enter the following command to view the contents of the oratab file:
# more /etc/oratab
If the oratab file exists, then it contains lines similar to the following:
*:/u03/app/oracle/product/10.2.0/db_1:N
*:/opt/orauser/infra_904:N
*:/oracle/9.2.0:N

Before deciding to use an existing Oracle base directory for this installation, ensure
that it satisfies the following conditions:
It should not be on the same file system as the operating system.

It must have sufficient free disk space, as follows:

Requirement Free Disk Space
The Oracle base directory will contain only Up to 3 GB
software files.
Up to 4 GB
The Oracle base directory will contain both
software and database files (not recommended for
production databases).

To determine the free disk space on the file system where the Oracle base directory
is located, enter the following command:
# df -h oracle_base_path


To create the Oracle base directory and specify the correct owner, group, and permissions for it:
Enter commands similar to the following to create the recommended subdirectories in the mount point directory that you identified and set the
appropriate owner, group, and permissions on them:

# mkdir -p /mount_point/app/oracle_sw_owner
# chown -R oracle:oinstall /mount_point/app/oracle_sw_owner
# chmod -R 775 /mount_point/app/oracle_sw_owner
For example, if the mount point you identify is /u01 and oracle is the user name of the Oracle software owner, then the recommended Oracle base directory path is
as follows:
/u01/app/oracle


Configuring the oracle User's Environment
=========================================
Set the default file mode creation mask (umask) to 022 in the shell startup file.

Set the DISPLAY environment variable.

To set the oracle user's environment:
Start a new terminal session, for example, an X terminal (xterm).

Enter the following command to ensure that X Window applications can display on this system:
$ xhost fully_qualified_remote_host_name
For example:
$ xhost somehost.us.acme.com
If you are not already logged in to the system where you want to install the software, then log in to that system as the oracle user.
If you are not logged in as the oracle user, then switch user to oracle:

$ su - oracle

To determine the default shell for the oracle user, enter the following command:

$ echo $SHELL

Open the oracle user's shell startup file in any text editor:
Bourne shell (sh), Bash shell (bash), or Korn shell (ksh):

$ vi .bash_profile
C shell (csh or tcsh):

% vi .login

Enter or edit the following line, specifying a value of 022 for the default file mode creation mask:

umask 022

If the ORACLE_SID, ORACLE_HOME, or ORACLE_BASE environment variable is set in the file, then remove the appropriate lines from the file.
Save the file, and exit from the editor.

To run the shell startup script, enter one of the following commands:

Bash shell:

$ . ./.bash_profile
Bourne or Korn shell:

$ . ./.profile
C shell:

% source ./.login

If you are not installing the software on the local system, then enter a command similar to the following to direct X applications to display on the local system:

Bourne, Bash, or Korn shell:

$ DISPLAY=local_host:0.0 ; export DISPLAY
C shell:

% setenv DISPLAY local_host:0.0

In this example, local_host is the host name or IP address of the system that
you want to use to display Oracle Universal Installer (your workstation or PC).


Installation Tasks
=====================

Downloading the Installation Archive Files

To download the installation archive files from Oracle Technology Network:
Use any browser to access the software download page from Oracle Technology
1.
Network:
http://www.oracle.com/technology/software/

Navigate to the download page for the product that you want to install.
2.
On the download page, identify the required disk space by adding the file sizes for
3.
each required file.
The file sizes are listed next to the file names.
Select a file system with enough free space to store and expand the archive files.
4.
In most cases, the available disk space must be at least twice the size of all of the
archive files.
On the file system that you selected in step 4, create a parent directory for each
5.
product, for example OraDB10g, to hold the installation directories.
Download all of the installation archive files to the directory that you created in
6.
step 5.
Verify that the files you downloaded are the same size as the corresponding files
7.
on Oracle Technology Network.


Extracting the Installation Files
=================================
To extract the installation archive files, perform the following steps:
If necessary, change directory to the directory that contains the downloaded
1.
installation archive files.
To uncompress each file, enter a command similar to the following:
2.
$ gunzip filename.cpio.gz
This command creates files with names similar to the following:
filename.cpio
To extract the installation files, enter a command similar to the following:
3.
$ cpio -idmv < filename.cpio

Installing the Oracle Database Software

If the installation files are on disc, enter commands similar to the following,where directory_path is the path of the database directory on the DVD:
$ cd /tmp
$ /directory_path/runInstaller

ORACLE DATA GUARD

Steps to Perform Switchover from Primary to Standby database

Primary Database

===============

1. See the switchover_status from v$database. If it shows TO STANDBY then we can switchover to physical standby database

SQL> SELECT SWITCHOVER_STATUS FROM v$DATABASE;

Your browser may not support display of this image.


2.Give this command to switchover to physical standby database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Your browser may not support display of this image.

3. Shutdown and startup in mount stage

Your browser may not support display of this image..

4. Alter database open read only

Your browser may not support display of this image.

NOTE: Do the below steps after completing the all steps on Standby Database

5. Apply redo log by using the command



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Your browser may not support display of this image.

6.

Your browser may not support display of this image.

7. To cancel the redo log apply

Your browser may not support display of this image.




Standby Database:

==============

1. See the switchover_status from v$database. If it shows TO PRIMARY then we can switchover to primary database

SQL> SELECT SWITCHOVER_STATUS FROM v$DATABASE;

Your browser may not support display of this image.




2.

Your browser may not support display of this image.




3. Give this command to switchover to primary database

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

After switchover to primary database,

shutdown and startup the database

Your browser may not support display of this image.

4. Verify that log are applying to standby database

Steps to Perform Manual Fail over :

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. Enable Flashback on both primary and standby databases

In order to utilize Flashback you'll need to put your database in ARCHIVELOG mode. Then you can set the

DB_FLASHBACK_RETENTION_TARGET parameter that defines the period of time which we want to retain flashback logs, and finally

turn Flashback on with an ALTER DATABASE statement. Lets look at the setup.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database flashback on;

SQL> alter database open;


2. Performing a Manual Failover Operation

Manual Failover Using DGMGRL:

On the target standby database, issue the FAILOVER command to invoke a complete failover, specifying the name of the standby

database that you want to change into the primary role:

DGMGRL> FAILOVER TO database-name [IMMEDIATE];

http://download.oracle.com/docs/cd/B28359_01/server.111/b28295/sofo.htm#BCGBFFIH

command run on standby database orclsby1

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

DGMGRL> FAILOVER TO orclsby1;

Performing failover NOW, please wait...

Failover succeeded, new primary is "orclsby1"

DGMGRL> show configuration

Configuration

Name: DGConfig

Enabled: YES

Protection Mode: MaxPerformance

Databases:

orclsby1 - Primary database

orcl - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Current status for "DGConfig":

SUCCESS

3. To reinstate a database:

1. Restart the database to the mounted state

2. Connect to the new primary database

3. Use Enterprise Manager or DGMGRL to reinstate the database

Reinstatement Using DGMGRL

Issue the following command while connected to any database in the broker configuration, except the database that is to be

reinstated:

DGMGRL> REINSTATE DATABASE db_unique_name;

The newly reinstated standby database will begin serving as standby database to the new primary database. If the database is

not reinstated successfully, then you must reenable it from a copy of the new primary database, as described in Section

5.4.3.2.

command run on primary database orcl

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

SQL> shutdown immediate

SQL> startup mount

DGMGRL> REINSTATE DATABASE orcl

Error: ORA-16795: the standby database needs to be re-created

Configuration details cannot be determined by DGMGRL

http://download.oracle.com/docs/cd/B28359_01/server.111/b28295/cli.htm#BGBBDEBC

4. How to Re-create and Re enable a Disabled Database

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

a. Copied all the datafile and altered P-file from the New primary(orclsby1) to New standby(orcl)

b. Standby control file created in Section 3.2.2

c. STARTUP MOUNT;

d. ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOG FILE DISCONNECT;

e. On New primary we have given "ALTER SYSTEM SWITCH LOGFILE;"

f. SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#i67520

Note.241374.1 - Script to Collect Data Guard Primary Site Diagnostic Information.

Note.241438.1 - Script to Collect Data Guard Standby Diagnostic Information.

Make sure that you have password files set on both servers

Make sure that you can connect from:

A. primary database:

sqlplus 'sys/password@ as sysdba' - connection to Standby database

sqlplus 'sys/password@ as sysdba' - connection to the itself

B. standby database:

sqlplus 'sys/password@ as sysdba' - connection to Primary database

sqlplus 'sys/password@ as sysdba' - connection to the itself

5. Recreate the DG Broker configuration because the meta data of the data guard configuration corrupted. Prefer you to remove and then recreate it :

1) Stop the broker on both the primary and standby using following command

alter system set dg_broker_start = false scope=both;

2) Remove the old broker files from $ORACLE_HOME/dbs/*.dat from Primary and Standby.

3) Start the broker on both the primary and standby using following command

alter system set dg_broker_start = true scope=both;

4) Invoke DGMGRL and connect to your primary database.

$ dgmgrl

DGMGRL> connect sys/dba123



5) Create the broker configuration including a profile for the primary database.

6) Display information about the configuration.

DGMGRL> show configuration

7) Use the following commands to add your physical standby database to the broker configuration.

8) Use the SHOW CONFIGURATION command to verify that the orclsby1 database was added to the configuration.

9) Enabling the Broker Configuration

With the Data Guard environment set successfully for your primary and standby databases. you can now enable the broker

configuration.

Enable the entire configuration. This may take some time to complete.

DGMGRL> ENABLE CONFIGURATION;

DGMGRL> SHOW CONFIGURATION;

10)Enable the database.

This step is unnecessary except if the standby database was previously disabled with the DISABLE DATABASE command. Normally,

enabling the configuration also enables the standby database.

DGMGRL> ENABLE DATABASE orcl;

Enabled.

DGMGRL> SHOW CONFIGURATION;


"7.2 Scenario 1: Creating a Configuration"

http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#CIAGDCHH


er.102/b14230/cli.htm>

"7.4 Scenario 3: Enabling the Configuration and Databases"

http://download.oracle.com/docs/cd/B19306_01/server.102/b14230/cli.htm#i1010364




6. Now we can do switchover ( follow switchover steps)

7. After switchover

DGMGRL> show configuration

8. Recreate the DG Broker configuratio

ORACLE INTERVIEW questions

Question 21.: What is Log Switch?

Answer: The point at which ORACLE ends writing to one online redo log file and begins writing to another
is called a log switch.


Question 22. What are the steps involved in Instance Recovery?

Answer: Rolling forward to recover data that has not been recorded in data files, yet has been recorded in the on-line redo log, including the contents of rollback segments.

Rolling back transactions that have been explicitly rolled back or have not been committed as
indicated by the rollback segments regenerated in step a.
Releasing any resources (locks) held by transactions in process at the time of the failure.

Resolving any pending distributed transactions undergoing a two-phase commit at the time of
the instance failure.


Question 23. Do you need a commit after DDL statements?

Answer: DDL IS A AUTO COMMIT, YOU DON??T NEED TO COMMIT AGAIN


Question 24. How would you pass hints to the SQL processor?

Answer: USING COMMENT LINES WITH (+) SIGN YOU CAN PASS THE HINTS TO SQL ENGINE
For example à /* +PARALLEL( ) */


Question 25. what is the difference between group by and order by?

Answer: group by is used when we use aggregate functions on the columns in a query the other columns should be in group by query
example:

select empno,ename,sum(sal) from emp
group by empno,ename

Order by is used to sort values either in ascending or descending order


Question 26. A DBA had to remove some Archivelogs to free up space in filesystem. Now when the RMAN job starts to backup Archivelogs, it complains about missing Archivelogs that were deleted by DBA. To resolve the issue and continue backing up remainder of Archivelogs, which RMAN command can be used so it won't complain about missing Archivelogs.

Answer: Crosscheck command


Question 27. Which RMAN command is used to create an exact replica of a database in new host?

Answer: DUPLICATE DATABASE


Question 28. How do you install Statspack?

Answer: By running $ORACLE_HOME/rdbms/admin/spcreate.sql script

Question 29. Process you follow to start looking into Performance issue at database level (If the application is running very slow, at what points do you need to go about the database in order to improve the performance?)

Answer:
· Run a TOP command in Unix to see CPU usage (identify CPU killer processes)
· Run VMSTAT, SAR, and PRSTAT command to get more information on CPU and memory usage and possible blocking
· Run STATSPACK report to identify:

1. TOP 5 WAIT EVENTS
2. RESOURCE intensive SQL statements
· See if STATISTICS on affected tables needs to be re-generated
· IF poorly written statements are culprit, run a EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.


Question 30. Explain below wait events in STATSPACK report DB SCATTERED READ, DB SEQUENTIAL REAL, ENQUEUE

Answer: DB SCATTERED READ - FULL TABLE SCAN
DB SEQUENTIAL REAL - IO
ENQUEUE - LOCKING


Question 31. List five most important parameter in 9i affecting performance

Answer: CURSOR_SHARING
DB_CACHE_SIZE
PGA_AGGREGATE_TARGET
DB_16K_CACHE_SIZE, DB_8K_CACHE_SIZE, DB_2K_CACHE_SIZE


Question 32. What is PGA_AGGREGATE_TARGET?

Answer: This parameter controls the maximum amount of memory PGA which can be used by the queries
when WORKAREA_SIZE_POLICY is set to Auto.
The value you can be set in Bytes, kilobytes (K), megabytes (M) or gigabytes (G). The default
value is 0
This parameter also has an effect on the execution plans of the cost based optimizer. The
optimizer uses the value of the parameter PGA_AGGREGATE_TARGET to derive an estimate
for the minimum and maximum amount of memory which should be available at run-time for each
sort, hash-join and bitmap operator in the query. Based on this minimum and maximum value, the
optimizer selects the best plan.


Question 33. How do you analyze table partition using Oracle provided package?

Answer: DBMS_STATS.GATHER_TABLE_STATS with GRANULARITY => 'PARTITION' OPTION


Question 34. You see a wait on LMS process in statspack, what does that mean?

Answer: A. Wait is due to Data Guard Broker.


Question 35. Name three advisory statistics you can collect.

Answer: SHARED_POOL_ADVICE
PGA_TARGET_ADVICE
DB_CACHE_ADVICE


Question 36. Explain procedure to Change CHARACTERSET of a database.

Answer: Can't change CHARACTERSET of a database, you will need to re-create the database with
appropriate CHARACTERSET.


Question 37. If you had a tablespace, TEST_TABLESPACE, which consists of three files:
TEST01.dbf, TEST02.dbf, and TEST03.dbf, and someone accidentally used the Unix command
"rm" to delete the file TEST02.dbf, what else would you need in order to recover all the data that
was present in TEST_TABLESPACE at the time that TEST02.dbf was deleted?

Answer: All Archivelogs


Question 38. How do you put database is ARCHIVELOG mode, explain procedure

Answer:
1. Modify init.ora parameter START_ARCHIVE=TRUE
2. SQL> SHUTDOWN IMMEDIATE;
3. STARTUP MOUNT;
4. ALTER DATAVASE ARCHIVELOG;
5. ALTER DATABASE OPEN;


Question 39. How can you tell if an index on particular table is USED or NOT USED in 9i?

Answer: By turning MONITORING ON that index and querying into INDEX_USAGE table


Question 40. Explain FORCE LOGGING feature in 9i.

Answer: By setting FORCE LOGGING to TRUE, all transactions will generate REDO. This is used for Data Guard, so no data will be missed (i.e. transactions that were run with NOLOGGING option) in Standby database

ORACLE OS Interview questions

1. How do you list the files in an UNIX directory while also showing hidden files?

ls -ltra

2. How do you execute a UNIX command in the background?

Use the "&"

3. What UNIX command will control the default file permissions when files are created?

Umask

4. Explain the read, write, and execute permissions on a UNIX directory.

Read allows you to see and list the directory contents.

Write allows you to create, edit and delete files and subdirectories in the directory.

Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.

5. the difference between a soft link and a hard link?

A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.

6. Give the command to display space usage on the UNIX file system.

df -lk

7. Explain iostat, vmstat and netstat.

Iostat reports on terminal, disk and tape I/O activity.

Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat reports on the contents of network data structures.

8. How would you change all occurrences of a value using VI?

Use :%s///g

9. Give two UNIX kernel parameters that effect an Oracle install

SHMMAX & SHMMNI

10. Briefly, how do you install Oracle software on UNIX.

Basically, set up disks, kernel parameters, and run orainst.

9i Interview questions

1. 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 shut 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.


2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?


I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.


3. How do you switch from an init.ora file to a spfile?


Issue the create spfile from pfile command.


4. Explain the difference between a data block, an extent and a segment.


A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.


5. Give two examples of how you might determine the structure of the table DEPT.


Use the describe command or use the dbms_metadata.get_ddl package.


6. Where would you look for errors from the database engine?


In the alert log.


7. Compare and contrast TRUNCATE and DELETE for a table.


Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.


8. Give the reasoning behind using an index.


Faster access to data blocks in a table.


9. Give the two types of tables involved in producing a star schema and the type of data they hold.


Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

10. . What type of index should you use on a fact table?


A Bitmap index.


11. Give two examples of referential integrity constraints.


A primary key and a foreign key.


12. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?


Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.


13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.


ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.


14. What command would you use to create a backup control file?


Alter database backup control file to trace.


15. Give the stages of instance startup to a usable state where normal users may access it.


STARTUP NOMOUNT - Instance startup

STARTUP MOUNT - The database is mounted

STARTUP OPEN - The database is opened


16. What column differentiates the V$ views to the GV$ views and how?


The INST_ID column which indicates the instance in a RAC environment the information came from.

17. How would you go about generating an EXPLAIN plan?


Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement

look at the explain plan with utlxplp.sql or utlxpls.sql

18. How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19. Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside

21. How would you determine the time zone under which a database was operating?

select DBTIMEZONE from dual;

22. Explain the use of setting GLOBAL_NAMES equal to TRUE.

Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

23. What command would you use to encrypt a PL/SQL application?

WRAP

24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

25. Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

26. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

27. Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

28. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

29. When a user process fails, what background process cleans up after it?

PMON

30. What background process refreshes materialized views?

The Job Queue Processes.

31. How would you determine what sessions are connected and what resources they are waiting for?

Use of V$SESSION and V$SESSION_WAIT

32. Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

33. How would you force a log switch?

ALTER SYSTEM SWITCH LOGFILE;

34. Give two methods you could use to determine what DDL changes have been made.

You could use Logminer or Streams

35. What does coalescing a tablespace do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

37. Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

38. When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.

39. How do you add a data file to a tablespace?

ALTER TABLESPACE ADD DATAFILE SIZE

40. How do you resize a data file?

ALTER DATABASE DATAFILE RESIZE ;

41. What view would you use to look at the size of a data file?

DBA_DATA_FILES

42. What view would you use to determine free space in a tablespace?

DBA_FREE_SPACE

43. How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

44. How can you rebuild an index?

ALTER INDEX REBUILD;

45. Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

46. You have just compiled a PL/SQL package but got errors, how would you view the errors?

SHOW ERRORS

47. How can you gather statistics on a table?

The ANALYZE command.

48. How can you enable a trace for a session?

Use the DBMS_SESSION.SET_SQL_TRACE or

Use ALTER SESSION SET SQL_TRACE = TRUE;

49. What is the difference between the SQL*Loader and IMPORT utilities?

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

50. Name two files used for network connection to a database.

TNSNAMES.ORA and SQLNET.ORA

inactive, No Manager in one of the request set that we submit on frequest basis.

This problem seems to be inactive ICM.

When ICM is not up u will get error inactive-no manager
Just check with manager and make it up.
ps -ef|grep LIBR
if it shows the manager is down then start the CM using script adcmctl.sh.


And if the manager is up but it is showing the inactive-nomanager then check for the log file and do the needful.


-------try with cmclean.sql.(not supported by oracle)
1.Bring down CM.
2.run cmclean.sql
3.Bring up CM

Hot Backup and Cloning

Procedure for Hot Backup and Hot Cloning:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/test/oracle/product/10.2.0/oradata/orcl/system01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/undotbs01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/sysaux01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/users01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/example01.dbf
/u01/test/oracle/product/10.2.0/oradata/orcl/oneapps01
/u01/test/oracle/product/10.2.0/oradata/orcl/sant_reorg0.dbf
***********************************************************************

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/test/oracle/product/10.2.0/oradata/orcl/control01.ctl
/u01/test/oracle/product/10.2.0/oradata/orcl/control02.ctl
/u01/test/oracle/product/10.2.0/oradata/orcl/control03.ctl
**************************************************************************

NAMESQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/test/oracle/product/10.2.0/oradata/orcl/redo03.log
/u01/test/oracle/product/10.2.0/oradata/orcl/redo02.log
/u01/test/oracle/product/10.2.0/oradata/orcl/redo01.log
************************************************************************

SQL> show parameter _dump;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/test/oracle/product/10.2.
0/db_1/admin/test/bdump
core_dump_dest string /u01/test/oracle/product/10.2.
0/db_1/admin/orcl/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u01/test/oracle/product/10.2.
0/db_1/admin/test/udump
**************************************************************************

Taking Hot Backup

1, alter database begin backup;

2, Copy Archive log files created during hot backup to /backup directory
cp -r 2009_03_30/ /u01/bkup30/.

3 Copy the All Data files to /backup directory.
cp -r oradata/ /u01/bkup30/

4 Backup the control file to trace.
SQL> alter database backup controlfile to trace;

5 Copy this trace file to /backup directory
/u01/test/oracle/product/10.2.0/db_1/admin/test/udump
cp test_ora_9407.trc /u01/bkup30/


6 Copy the current init.ora file to /backup directory
/u01/test/oracle/product/10.2.0/db_1/dbs
cp inittest.ora /u01/bkup30/


7 End the Begin Backup Mode
Sql> alter database end backup.
*****************************************************
Hot Backup Cloning

1 Create the target database control file manually

2 Open the backed up control file

a. remove all lines before the startup nomount statement
b. Modify the REUSE to SET
c. Modify Source DB SID to Target SID (Here PROD to TEST)
d. Modify NORESETLOGS TO RESETLOGS
e. delete all lines after the CHARACTER SET statement
**********************************************************

Make the trace file as a sql format create.sql

STARTUP NOMOUNT pfile='/u01/test/oracle/product/10.2.0/db_1/dbs/initkish.ora'
CREATE CONTROLFILE SET DATABASE "KISH" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/bkup30/oradata/kish/redo01.log' SIZE 50M,
GROUP 2 '/u01/bkup30/oradata/kish/redo02.log' SIZE 50M,
GROUP 3 '/u01/bkup30/oradata/kish/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/bkup30/oradata/kish/system01.dbf',
'/u01/bkup30/oradata/kish/undotbs01.dbf',
'/u01/bkup30/oradata/kish/sysaux01.dbf',
'/u01/bkup30/oradata/kish/users01.dbf',
'/u01/bkup30/oradata/kish/example01.dbf',
'/u01/bkup30/oradata/kish/oneapps01',
'/u01/bkup30/oradata/kish/sant_reorg0.dbf'
CHARACTER SET WE8ISO8859P1

************************************************************

SQL> @create.sql
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size 2085776 bytes
Variable Size 163581040 bytes
Database Buffers 427819008 bytes
Redo Buffers 6299648 bytes

Control file created.

**************************************************************
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00279: change 2830167 generated at 03/30/2009 11:51:47 needed for thread 1
ORA-00289: suggestion :
/u01/test/oracle/product/10.2.0/db_1/flash_recovery_area/KISH/archivelog/2009_03
_30/o1_mf_1_5_%u_.arc
ORA-00280: change 2830167 for thread 1 is in sequence #5


Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/bkup30/oradata/kish/redo02.log
Log applied.
Media recovery complete.

****************************************************************
SQL> alter database open resetlogs;

Database altered.

*****************************************************************

Oracle cheat sheets commands part-x

1)drop view

DROP VIEW my_view CASCADE CONSTRAINTS;

2)explain plan

EXPLAIN PLAN SET STATEMENT_ID='TEST' FOR
SELECT * FROM emp WHERE EMPID=100;

3)flashback database

FLASHBACK DATABASE TO SCN 10000;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE – 1/24;
FLASHBACK DATABASE TO BEFORE TIMESTAMP SYSDATE – 1/24;

4)grants: Object Grants

GRANT SELECT ON scott.my_tab TO my_user;
GRANT INSERT, UPDATE, SELECT ON scott.my_tab TO my_user;
GRANT SELECT ON scott.my_tab TO my_user WITH GRANT OPTION;
GRANT SELECT ON scott.my_tab TO PUBLIC WITH GRANT OPTION;

5)grants: System Grants

GRANT CREATE TABLE to my_user;
GRANT CREATE ANY TABLE to my_user WITH ADMIN OPTION;
GRANT ALL PRIVILEGES to my_user WITH ADMIN OPTION;

6)lock table

LOCK TABLE my_table IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE my_table IN ROW EXCLUSIVE MODE;

7)rename

RENAME my_table to my_tab;

8)revoke: System Grants

REVOKE CREATE TABLE FROM my_user;
REVOKE CREATE ANY TABLE FROM my_user;
REVOKE ALL PRIVILEGES FROM my_user;

9)rollback

ROLLBACK;

10)savepoint

SAVEPOINT alpha;

Oracle cheat sheets commands part-ix

1)drop procedure

DROP PROCEDURE my_proc;

2)drop profile

DROP PROFILE my_profile CASCADE;

3)drop role

DROP ROLE my_role;

4)drop rollback segment

DROP ROLLBACK SEGMENT rbs01;

5)drop sequence

DROP SEQUENCE my_seq;

6)drop synonym

DROP SYNONYM my_synonym;
DROP PUBLIC SYNONYM my_synonym;

7)drop table

DROP TABLE my_tab;
DROP TABLE my_tab CASCADE CONSTRAINTS;
DROP TABLE my_tab CASCADE CONSTRAINTS PURGE;

8)drop tablespace

DROP TABLESPACE my_tbs;
DROP TABLESPACE my_tbs INCLUDING CONTENTS;
DROP TABLESPACE my_tbs INCLUDING CONTENTS
AND DATAFILES CASCADE CONSTRAINTS;

9)drop trigger

DROP TRIGGER my_trigger;

10)drop user

DROP USER my_user CASCADE;

Oracle cheat sheets commands part-viii

1)delete

DELETE FROM emp WHERE empid=100;
DELETE FROM emp e WHERE e.rowid >
(SELECT MIN (esub.ROWID) FROM emp esub
WHERE e.empid=esub.empid);

2)drop cluster

DROP CLUSTER scott.emp_cluster
INCLUDING TABLES CASCADE CONSTRAINTS;

3)drop database

DROP DATABASE;

4)drop database link

DROP DATABASE LINK my_db_link;
DROP PUBLIC DATABASE LINK my_db_link;

5)drop directory

DROP DIRECTORY mydir;

6)drop function

DROP FUNCTION find_value_in_table;

7)drop index

DROP INDEX ix_my_tab;

8)drop materialized view

DROP MATERIALIZED VIEW my_mview;
DROP MATERIALIZED VIEW my_mview PRESERVE TABLE;

9)drop materialized view log

DROP MATERIALIZED VIEW LOG ON mytab;

10)drop package/drop package body

DROP PACKAGE scott.my_package
DROP PACKAGE BODY scott.my_package;

Oracle cheat sheets commands part-vii

)analyze: Analyze Index

ANALYZE INDEX ix_tab_01 COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE INDEX ix_tab_01
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;

2)create role

CREATE ROLE developer_role IDENTIFIED USING develop;

3)create rollback segment

CREATE ROLLBACK SEGMENT r01 TABLESPACE RBS
STORAGE (INITIAL 100m NEXT 100M MINEXTENTS 5 OPTIMAL 500M);

4)create sequence

CREATE SEQUENCE my_seq
START WITH 1 INCREMENT BY 1 MAXVALUE 1000000 CYCLE CACHE;

5)create spfile

CREATE SPFILE FROM PFILE;
CREATE SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora'
FROM PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora';

6)create synonym

CREATE SYNONYM scott_user.emp FOR scott.EMP;
CREATE PUBLIC SYNONYM emp FOR scott.EMP;

7)create tablespace: Temporary Tablespace

CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;

8)create tablespace: Undo Tablespace

CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;

9)create user

CREATE USER Robert IDENTIFIED BY Freeman
DEFAULT TABLESPACE users_tbs
TEMPORARY TABLESPACE temp
QUOTA 100M ON users_tbs
QUOTA UNLIMITED ON data_tbs;

10)create view

CREATE OR REPLACE VIEW vw_emp_dept_10 AS
SELECT * FROM EMP WHERE dept=10;
CREATE OR REPLACE VIEW vw_public_email AS
SELECT ename_first, ename_last, email_address
FROM EMP WHERE public='Y'

Oracle cheat sheets commands part-vi

1)alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;

2)alter tablespace: Rename

ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

3)alter user: Change Password

ALTER USER olduser IDENTIFIED BY newpassword;
ALTER USER olduser IDENTIFIED EXTERNALLY;

4)alter user: Password and Account Management

ALTER USER olduser PASSWORD EXPIRE;
ALTER USER olduser ACCOUNT LOCK;
ALTER USER olduser ACCOUNT UNLOCK;

5)alter user: Profile

ALTER USER olduser PROFILE admin_profile;

6)alter user: Quotas

ALTER USER olduser QUOTA UNLIMITED ON users;
ALTER USER olduser QUOTA 10000M ON USERS;

7)alter user: Roles

ALTER USER olduser DEFAULT ROLE admin_role;
ALTER USER olduser DEFAULT ROLE NONE;
ALTER USER olduser DEFAULT ROLE ALL EXCEPT admin_role;

8)alter user: Tablespace Assignments

ALTER USER olduser DEFAULT TABLESPACE users;
ALTER USER olduser TEMPORARY TABLESPACE temp;

9)alter view: Recompile

ALTER VIEW my_view RECOMPILE;

10)analyze: Analyze Cluster

ANALYZE CLUSTER my_cluster_tab COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE CLUSTER my_cluster_tab
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;

Oracle cheat sheets commands part-v

)alter table: Table Constraints – Add Check Constraint

ALTER TABLE parts ADD (CONSTRAINT ck_parts_01 CHECK (id

2)alter table: Table Constraints – Add Default Value

ALTER TABLE PARTS MODIFY (name DEFAULT 'Not Available');
ALTER TABLE PARTS ADD (vendor_code NUMBER DEFAULT 0);
ALTER TABLE PARTS MODIFY (part_description DEFAULT NULL);

3)alter table: Table Constraints – Add Foreign Key

ALTER TABLE parts ADD CONSTRAINT fk_part_bin
FOREIGN KEY (bin_code) REFERENCES part_bin;

4)alter table: Table Partition – Merge

ALTER TABLE store_sales
MERGE PARTITIONS Oklahoma, texas
INTO PARTITION oktx;

5)alter table: Table Partition – Move

ALTER TABLE store_sales MOVE PARTITION sales_overflow TABLESPACE
new_sales_overflow STORAGE (INITIAL 100m NEXT 100m PCTINCREASE 0)
UPDATE GLOBAL INDEXES;

6)alter table: Table Partition – Remove

ALTER TABLE store_sales DROP PARTITION sales_q1_04 UPDATE GLOBAL INDEXES;

7)alter table: Table Partition – Rename

ALTER TABLE store_sales RENAME PARTITION sales_q1 TO sales_first_quarter;

8)alter table: Table Partition – Truncate

ALTER TABLE store_sales TRUNCATE PARTITION sales_overflow
UPDATE GLOBAL INDEXES;

9)alter table: Table Properties

ALTER TABLE parts PCTFREE 10 PCTUSED 60;
ALTER TABLE parts STORAGE (NEXT 1M);
ALTER TABLE parts PARALLEL 4;

10)alter table: Triggers – Modify Status

ALTER TABLE parts DISABLE ALL TRIGGERS;
ALTER TABLE parts ENABLE ALL TRIGGERS;

Oracle cheat sheets commands part-iv

1)alter profile: Miscellaneous

ALTER ROLE my_role IDENTIFIED BY password;
ALTER ROLE my_role NOT IDENTIFIED;

2)alter rollback segment: Online/Offline

ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
ALTER ROLLBACK SEGMENT rbs01 ONLINE;

3)alter rollback segment: Shrink

ALTER ROLLBACK SEGMENT rbs01 SHRINK;
ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 100M;

4)alter rollback segment: storage Clause

ALTER ROLLBACK SEGMENT rbs01 STORAGE(NEXT 50M OPTIMAL 100M);

5)alter session: Enable and Disable Parallel Operations

ALTER SESSION ENABLE PARALLEL DML PARALLEL 3;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;

6)alter session: Resumable Space Management

ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
ALTER SESSION DISABLE RESUMABLE;

7)alter session: Set Session Parameters

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG START;
ALTER SYSTEM ARCHIVE LOG STOP;
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG THREAD 1 ALL;
ALTER SYSTEM ARCHIVE LOG ALL TO 'C:\oracle\allarch';

8)alter table: Move Table

ALTER TABLE parts move TABLESPACE parts_new_tbs PCTFREE 10 PCTUSED 60;

9)alter table: Table Column – Remove

ALTER TABLE parts DROP (part_location);
ALTER TABLE parts DROP (part_location, part_bin);

10)alter table: Table Column – Rename

ALTER TABLE parts RENAME COLUMN part_location TO part_loc;

Oracle cheat sheets commands part-iii

1)alter materialized view: Allocate and Deallocate Extents

ALTER MATERIALIZED VIEW mv_my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW mv_my_tab DEALLOCATE UNUSED;

2)alter materialized view: Physical Attributes and Storage

ALTER MATERIALIZED VIEW mv_my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);

3)alter materialized view: Refresh

ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON DEMAND;

4)alter materialized view: Shrink Space

ALTER MATERIALIZED VIEW mv_my_tab SHRINK SPACE;
ALTER MATERIALIZED VIEW mv_my_tab
SHRINK SPACE COMPACT CASCADE;

5)alter materialized view log: Add Components

ALTER MATERIALIZED VIEW LOG ON my_tab ADD PRIMARY KEY;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2),
ROWID, SEQUENCE INCLUDING NEW VALUES;

6)alter materialized view log: Allocate and Deallocate Extents

ALTER MATERIALIZED VIEW LOG ON my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW LOG ON my_tab DEALLOCATE UNUSED;

7)alter materialized view log: Miscellaneous

ALTER MATERIALIZED VIEW LOG ON my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW LOG ON my_tab NOLOGGING;
ALTER MATERIALIZED VIEW LOG ON my_tab SHRINK SPACE;

8)alter materialized view log: Physical Attributes and Storage

ALTER MATERIALIZED VIEW LOG ON my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);


9)alter package: Compile

ALTER PACKAGE pk_my_package COMPILE;
ALTER PACKAGE pk_my_package COMPILE SPECIFICATION;
ALTER PACKAGE pk_my_package COMPILE BODY;

10)alter procedure: Compile

ALTER PROCEDURE pk_my_package COMPILE;

Oracle cheat sheets commands part-ii

1)alter index: Allocate and Deallocate Extents

ALTER INDEX ix_my_tab ALLOCATE EXTENT;
ALTER INDEX ix_my_tab ALLOCATE EXTENT
DATAFILE '/ora/datafile/newidx.dbf';
ALTER INDEX ix_my_tab DEALLOCATE UNUSED;
ALTER INDEX ix_my_tab DEALLOCATE UNUSED KEEP 100M;

2)alter index: Modify Logging Attributes

ALTER INDEX ix_my_tab LOGGING;
ALTER INDEX ix_my_tab NOLOGGING;

3)alter index: Modify Storage and Physical Attributes

ALTER INDEX ix_my_tab PCTFREE 10 PCTUSED 40 INITRANS 5
STORAGE (NEXT 100k MAXEXTENTS UNLIMITED FREELISTS 10
BUFFER_POOL KEEP);

4)alter index: Partition – Add Hash Index Partition

ALTER INDEX ix_my_tab ADD PARTITION
TABLESPACE NEWIDXTBS;

5)alter index: Partition – Coalesce Partition

ALTER INDEX ix_my_tab COALESCE PARTITION;

6)alter index: Partition – Drop Partition

ALTER INDEX ix_my_tab DROP PARTITION ix_my_tab_jan_04;

7)alter index: Partition – Modify Default Attributes

ALTER INDEX ix_my_tab MODIFY DEFAULT ATTRIBUTES
FOR PARTITION ix_my_tab_jan_04
PCTFREE 10 PCTUSED 40 TABLESPACE newidxtbs
NOLOGGING COMPRESS;

8)alter index: Partition – Modify Partition

ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;

9)alter index: Partition – Modify Subpartition

ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;

10)alter index: Partition – Rename

ALTER INDEX ix_my_tab RENAME
PARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
ALTER INDEX ix_my_tab RENAME
SUBPARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;

Oracle cheat sheets commands part-1

1)alter cluster

ALTER CLUSTER pub_cluster SIZE 4K;
ALTER CLUSTER pub_cluster DEALLOCATE UNUSED KEEP 1M;


2)alter database: Alter a Data File


ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
RESIZE 100m;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;


3)alter database: Alter a Tempfile


ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;


4)alter database: ARCHIVELOG Mode Commands


ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE
'/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


5)alter database: Control File Operations


ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
AS '/opt/oracle/logfile_backup/backup_logfile.trc'
REUSE RESETLOGS;
ALTER DATABASE BACKUP CONTROLFILE TO
'/opt/oracle/logfile_backup/backup_logfile.ctl';


6)alter database: Create a Data File


ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4
AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS NEW;


7)alter database: Datafile Offline/Online


See alter database: Alter a Data File


8)alter database: Logfile Commands


ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;
ALTER DATABASE ADD LOGFILE MEMBER
'/opt/oracle/logfiles/redo02c.rdo'
to GROUP 2;
ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';

9)alter database: Mount and Open the Database


ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;


10)alter database: Open the Database Read-Only

ALTER DATABASE OPEN READ ONLY;

Oracle database tuning basic Q&A

1)Version information:

SELECT * FROM product_component_version ;

2)List free and used space in database :


SELECT sum(bytes)/1024 "free space in KB"
FROM dba_free_space;

SELECT sum(bytes)/1024 "used space in KB"
FROM dba_segments;

3)List tablespace fragmentation information :



SELECT tablespace_name,COUNT(*) AS fragments,
SUM(bytes) AS total,
MAX(bytes) AS largest
FROM dba_free_space
GROUP BY tablespace_name;


4)Check the current number of extents and blocks allocated to a seg


SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS


5)Extent information


SELECT segment_name, extent_id, blocks, bytes
FROM dba_extents
WHERE segment_name = TNAME ;



6)List table blocks, empty blocks, extent count, and chain block count

SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;

SELECT chain_cnt AS CHAINED_BLOCKS
FROM dba_tables
WHERE table_name=TNAME;

SELECT COUNT(*) AS EXTENT_COUNT
FROM dba_extents
WHERE segment_name=TNAME;

7)Information about all rollback segments in the database


SELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS
FROM DBA_ROLLBACK_SEGS;


8)Index & constraint information


SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
(SELECT constraint_name
FROM dba_constraints
WHERE table_name = TNAME
AND constraint_type in ('P','U')) ;

9)List tables and synonyms

set pagesize 0;

select 'TABLE:',table_name,'current' from user_tables
union
select 'SYNONYM:',synonym_name,table_owner from user_synonyms
order by 1,2 ;

10)Tuning: library cache


Glossary:

pins = # of time an item in the library cache was executed

reloads = # of library cache misses on execution

Goal:

get hitratio to be less than 1

Tuning parm:

adjust SHARED_POOL_SIZE in the initxx.ora file, increasing by small increments



SELECT SUM(PINS) EXECS,
SUM(RELOADS)MISSES,
SUM(RELOADS)/SUM(PINS) HITRATIO
FROM V$LIBRARYCACHE ;







11)Tuning: buffer cache



Calculation:

buffer cache hit ratio = 1 - (phy reads/(db_block_gets + consistent_gets))

Goal:

get hit ratio in the range 85 - 90%

Tuning parm:

adjust DB_BLOCK_BUFFERS in the initxx.ora file, increasing by small increments




SELECT NAME, VALUE
FROM V$SYSSTAT WHERE NAME IN
('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS');


12)Archive Log Mode Status



/* Status of Archive Log Subsystem */

ARCHIVE LOG LIST


/* log mode of databases */

SELECT name, log_mode FROM v$database;


/* log mode of instance */

SELECT archiver FROM v$instance;

Oracle Architecture basic questions & ans part-1

1)What is the command to find the database that already present before connecting ?

In Unix: ps -ef | grep pmon
In Windows: You can check the database service is runnig or not.


2)What are the components of Physical database structure of Oracle Database?


the phycial structures of the database are
1.datafile
2.redologfile
3.control file
datafile:- datafile contains actial data of the database.
redologfile:- it contains all changes done to the data base.it works in the circular fashion.
controlfile:- it contains status and structure of the database.

3)What are the components of Logical database structure of ORACLE database?

Tablespaces--->Segments---->Extents----->Data Blocks.


4)What is a Tablespace?

It is a Logical area of storage in a Database that directly corresponds to one or more physical files.


5)What is SYSTEM tablespace and when is it Created?

Every Oracle Database contains a tablespace called system tablespace.This tablespace gets created at the time of database creation and it holds data dictionary information for the entire database.

6)Explain the relationship among Database, Tablespace and Data file?


i)A DATA IN A DATABSE IS STORED PHYSICALLY IN A DATAFILE AND LOGICALLY IN A TABLESPACE.
ii)A TABLESPACE CAN BELONG TO ONLY ONE DATABASE AT A TIME.
iii)A DATABASE CAN CONTAIN MORE THAN ON TABLESPACE.
iv)A TABLESPACE CAN CONTAIN ONE OR MORE DATAFLES.
v)A DATAFILE CAN BELONG TO ONLY ONE TABLESPACE AND ONE DATABASE .

7)Can a Tablespace hold objects from different Schemes ?

Yes



8)What is schema?

Schema is a Collection of Database objects of a user.
Example: Tables Views Synonyms Sequences Clusters Triggers Procedures Packages.

9)Can objects of the same Schema reside in different tablespaces.?

Yes

10)What is a Data Dictionary ?
The data dictionary of an ORACLE database is a set of tables and views that are used as a read-only reference about the database.
It stores information about both the logical and physical structure of the database, the valid users of an ORACLE database, integrity constraints defined for tables in the database and space allocated for a schema object and how much of it is being used.