RAC Load Balancing, TAF , FAN

Client Side Connect-Time Load Balance

The client load balancing feature enables clients to randomize connection requests among the listeners.
This is done by client Tnsnames Parameter: LOAD_BALANCE.
The (load_balance=yes) instructs SQLNet to progress through the list of listener addresses in the address_list section of the net service name in a random sequence. When set to OFF, instructs SQLNet to try the addresses sequentially until one succeeds.

Client Side Connect-Time failover

This is done by client Tnsnames Parameter: FAILOVER
The (failover=on) enables clients to connect to another listener if the initial connection to the first listener fails. Without connect-time failover, Oracle Net attempts a connection with only one listener.

Server Side Listener Connection Load Balancing.

With server-side load balancing, the listener directs a connection request to the best instance currently providing the service.
Init parameter remote_listener should be set. When set, each instance registers with the TNS listeners running on all nodes within the cluster.

There are two types of server-side load balancing:
Load Based — Server side load balancing redirects connections by default depending on node load. This id default.
Session Based — Session based load balancing takes into account the number of sessions connected to each node and then distributes the connections to balance the number of sessions across the different nodes.

From 10g release 2 the service can be setup to use load balancing advisory. This mean connections can be routed using SERVICE TIME and THROUGHPUT. Connection load balancing means the goal of a service can be changed, to reflect the type of connections using the service.

Transparent Application Failover (TAF)

Transparent Application Failover (TAF) is a feature of the Oracle Call Interface (OCI) driver at client side. It enables the application to automatically reconnect to a database, if the database instance to which the connection is made fails. In this case, the active transactions roll back.
Tnsnames Parameter: FAILOVER_MODE

e.g (failover_mode=(type=select)(method=basic))
Failover Mode Type can be Either SESSION or SELECT.

Session failover will have just the session to failed over to the next available node. With SELECT, the select query will be resumed.
TAF can be configured with just server side service settings by using dbms_service package.

Fast Connection Failover (FCF)

Fast Connection Failover is a feature of Oracle clients that have integrated with FAN HA Events.
Oracle JDBC Implicit Connection Cache, Oracle Call Interface (OCI), and Oracle Data Provider for .Net (ODP.Net) include fast connection failover.

With fast connection failover, when a down event is received, cached connections affected by the down event are immediately marked invalid and cleaned up

How to move OCR and Voting disk to new storage device

You must be logged in as the root user, because root owns the OCR files. Also an ocrmirror must be in place before trying to replace the OCR device.

Make sure there is a recent backup of the OCR file before making any changes:

ocrconfig –showbackup

If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file. Use the following command to generate an export of the online OCR file:

In 10.2

# ocrconfig –export -s online

In 11g

# ocrconfig -manualbackup

The new OCR disk must be owned by root, must be in the oinstall group, and must have permissions set to 640. Provide at least 100 MB disk space for the OCR.

On one node as root run:

# ocrconfig -replace ocr
# ocrconfig -replace ocrmirror

Now run ocrcheck to verify if the OCR is pointing to the new file

Moving Voting Disk
==================

Note: crsctl votedisk commands must be run as root

Shutdown the Oracle Clusterware (crsctl stop crs as root) on all nodes before making any modification to the voting disk. Determine the current voting disk location using:

crsctl query css votedisk

Take a backup of all voting disk:

dd if=voting_disk_name of=backup_file_name

To move a Voting Disk, provide the full path including file name:

crsctl delete css votedisk –force
crsctl add css votedisk –force

After modifying the voting disk, start the Oracle Clusterware stack on all nodes

# crsctl start crs

Verify the voting disk location using

crsctl query css votedisk

HowTo Restore RAC Database to Single Instance On Another Node

Take RMAN backup of the production RAC database..

RMAN> run{
allocate channel c1 type disk format '/tmp/%U';
backup database;
backup archivelog all;
}

- Create a PFILE for the single instance database using the production RAC parameter file

And modify the parameters %dest, control_files, log_archive_dest_1, %convert, cluster_database_instances, cluster_database etc.. for undo_tablespace, mention any one undo tablespace name

- Copy the backup pieces and the modified INIT.ORA file to the new host to same mount point.

- Use the pfile created above to STARTUP NOMOUNT the database on the new host

$ sqlplus "/ as sysdba"

SQL> startup nomount;

$ rman target / nocatalog

RMAN> restore controlfile from '/tmp/< backup piece name of controlfile auto backup>';

RMAN> alter database mount;

- Determine the recovery point.

RMAN> list backup of archivelog all;

Check the last archive sequence for all redo threads and select the archive sequence having LEAST "Next SCN" among them.

- Having determined the point upto which media recovery should run, start the restore/recovery using:

RMAN> run {
set until sequence < sequence# from above> thread < thread# >;
restore database;
recover database;
}

SQL> alter database open resetlogs;

If open database fail with error ORA-38856
then, Set the following parameter in the init.ora file:

_no_recovery_through_resetlogs=TRUE

Then open with resetlogs.
Once the database has opened, removed this hidden parameter.

- Once the database is opened successfully, you may remove the redolog groups for redo threads of other instances.

SQL> select THREAD#, STATUS, ENABLED
2 from v$thread;

THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PRIVATE

SQL> select group# from v$log where THREAD#=2;

GROUP#
----------
4
5
6

SQL> alter database disable thread 2;

Database altered.

SQL> alter database clear unarchived logfile group 4; ( repeat for 4 to 6)

Database altered.

SQL> alter database drop logfile group 4; ( repeat for 4 to 6)

Database altered.

- Now you can remove the undo tablespaces of other instances.

SQL> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

RAC Interview Q&A2

Clien'ts are performing some operation and suddenly one of the datafile is experiencing problem what do you do?

The cluster is a two node one.

Bring the datafile offline recover the datafile.

How can you connect to a specific node in a RAC environment?

tnsnames.ora ensure that you have INSTANCE_NAME specified in it.

What are Oracle Clusterware processes for 10g on Unix and Linux

Cluster Synchronization Services (ocssd) — Manages cluster node membership and runs as the oracle user; failure of this process results in cluster restart.

Cluster Ready Services (crsd) — The crs process manages cluster resources (which could be a database, an instance, a service, a Listener, a virtual IP (VIP) address, an application process, and so on) based on the resource's configuration information that is stored in the OCR. This includes start, stop, monitor and failover operations. This process runs as the root user

Event manager daemon (evmd) —A background process that publishes events that crs creates.

Process Monitor Daemon (OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD performs its check, stops running, and if the wake up is beyond the expected time, then OPROCD resets the processor and reboots the node. An OPROCD failure results in Oracle Clusterware restarting the node. OPROCD uses the hangcheck timer on Linux platforms.

RACG (racgmain, racgimon) —Extends clusterware to support Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur.

What are Oracle database background processes specific to RAC

•LMS—Global Cache Service Process

•LMD—Global Enqueue Service Daemon

•LMON—Global Enqueue Service Monitor

•LCK0—Instance Enqueue Process

To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.

What are Oracle Clusterware Components

Voting Disk — Oracle RAC uses the voting disk to manage cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. The voting disk must reside on shared disk.

Oracle Cluster Registry (OCR) — Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster

How do you troubleshoot node reboot

Please check metalink ...

Note 265769.1 Troubleshooting CRS Reboots
Note.559365.1 Using Diagwait as a diagnostic to get more information for diagnosing Oracle Clusterware Node evictions.

How do you backup the OCR

There is an automatic backup mechanism for OCR. The default location is : $ORA_CRS_HOME\cdata\"clustername"\

To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig -restore

With Oracle RAC 10g Release 2 or later, you can also use the export command:
#ocrconfig -export -s online, and use -import option to restore the contents back.
With Oracle RAC 11g Release 1, you can do a manaual backup of the OCR with the command:
# ocrconfig -manualbackup

How do you backup voting disk

#dd if=voting_disk_name of=backup_file_name

How do I identify the voting disk location

#crsctl query css votedisk

How do I identify the OCR file location

check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck

Is ssh required for normal Oracle RAC operation ?

"ssh" are not required for normal Oracle RAC operation. However "ssh" should be enabled for Oracle RAC and patchset installation.

What is SCAN?

Single Client Access Name (SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature that provides a single name for clients to access an Oracle Database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the cluster.

Click here for more details from Oracle

What is the purpose of Private Interconnect ?

Clusterware uses the private interconnect for cluster synchronization (network heartbeat) and daemon communication between the the clustered nodes. This communication is based on the TCP protocol.
RAC uses the interconnect for cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster.

Why do we have a Virtual IP (VIP) in Oracle RAC?

Without using VIPs or FAN, clients connected to a node that died will often wait for a TCP timeout period (which can be up to 10 min) before getting an error. As a result, you don't really have a good HA solution without using VIPs.
When a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.

What do you do if you see GC CR BLOCK LOST in top 5 Timed Events in AWR Report?

This is most likely due to a fault in interconnect network.
Check netstat -s
if you see "fragments dropped" or "packet reassemblies failed" , Work with your system administrator find the fault with network.

How many nodes are supported in a RAC Database?

10g Release 2, support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database.

Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem?

Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack.

what is the purpose of the ONS daemon?

The Oracle Notification Service (ONS) daemon is an daemon started by the CRS clusterware as part of the nodeapps. There is one ons daemon started per clustered node.
The Oracle Notification Service daemon receive a subset of published clusterware events via the local evmd and racgimon clusterware daemons and forward those events to application subscribers and to the local listeners.

This in order to facilitate:

a. the FAN or Fast Application Notification feature or allowing applications to respond to database state changes.
b. the 10gR2 Load Balancing Advisory, the feature that permit load balancing accross different rac nodes dependent of the load on the different nodes. The rdbms MMON is creating an advisory for distribution of work every 30seconds and forward it via racgimon and ONS to listeners and applications.



What is the difference between a OS cluster and a rac cluster?
What is gsd?
What is cachefusion?
What happens when a DML is issued in a RAC environment, how are requests for common buffers handled in a RAC environment.

RAC Interview Q&A

What is RAC?

RAC stands for Real Application cluster. It is a clustering solution from Oracle
Corporation that ensures high availability of databases by providing instance failover,
media failover features.

Mention the Oracle RAC software components:-

Oracle RAC is composed of two or more database instances. They are composed of
Memory structures and background processes same as the single instance database.Oracle
RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache
Service) that enable cache fusion.Oracle RAC instances are composed of following
background processes:

ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor



What is GRD?

GRD stands for Global Resource Directory. The GES and GCS maintains records of the
statuses of each datafile and each cahed block using global resource directory.This
process is referred to as cache fusion and helps in data integrity.

Give Details on Cache Fusion:-

Oracle RAC is composed of two or more instances. When a block of data is read from
datafile by an instance within the cluster and another instance is in need of the same
block,it is easy to get the block image from the insatnce which has the block in its SGA
rather than reading from the disk. To enable inter instance communication Oracle RAC
makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance
enqueue process manages the cahce fusion.

Give Details on ACMS:-

ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment
ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are
globally committed on success or globally aborted in event of a failure.

Give details on GTX0-j :-

The process provides transparent support for XA global transactions in a RAC
environment.The database autotunes the number of these processes based on the
workload of XA global transactions



LMON:This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.This is called as Global Enqueue Service Monitor.
Give details on LMD:
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
Give details on LMS:
This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.
Give details on LCK0:
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.
Give details on RMSn:
This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
Give details on RSMN:
This process is called as Remote Slave Monitor.This process manages background slave process creation andd communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.


What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.

What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE's, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.
Give few examples for solutions that support cluster storage:ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).

What is an interconnect network?
an interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.

How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unia and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.

Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware intercnects.

What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.

How do users connect to database in an Oracle RAC environment?
Users can access a RAC database using a client/server configuration or through one or more middle tiers ,with or without connection pooling.Users can use oracle services feature to connect to database.

What is the use of a service in Oracle RAC environemnt?

Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.

What are the characteriscs controlled by Oracle services feature?
The charateristics include a unique name, workload balancing and failover options,and high availability characteristics.

Which enable the load balancing of applications in RAC?
Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.

What is a virtual IP address or VIP?

A virtl IP address or VIP is an alternate IP address that the client connectins use instead of the standard public IP address. To configureVIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.

What is the use of VIP?

If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections. Give situations under which VIP address failover happens:VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.

What is the significance of VIP address failover?

When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for TCP connection timeout messages.


What are the administrative tools used for Oracle RAC environments?

Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NE TCA

How do we verify that RAC instances are running?
Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.

What is FAN?
Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAc uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action

. Where can we apply FAN UP and DOWN events?
FAN UP and FAN DOWN events can be applied to instances,services and nodes.

State the use of FAN events in case of a cluster configuration change?

During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.

Why should we have seperate homes for ASm instance?

It is a good practice to have ASM home seperate from the database hom(ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.

What is the advantage of using ASM?

Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the storage configuration across the disks.ASM does this by distributing the database file across all of the available storage within our cluster database environment.

What is rolling upgrade?
It is a new ASM feature from Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions.

Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1).

State the initialization parameters that must have same value for every instance in an Oracle RAC database:Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
ACTIVE_INSTANCE_COUNT ARCHIVE_LAG_TARGET COMPATIBLE CLUSTER_DATABASE CLUSTER_DATABASE_INSTANCE CONTROL_FILES DB_BLOCK_SIZE DB_DOMAIN DB_FILES DB_NAME DB_RECOVERY_FILE_DEST DB_RECOVERY_FILE_DEST_SIZE DB_UNIQUE_NAME INSTANCE_TYPE (RDBMS or ASM) PARALLEL_MAX_SERVERS REMOTE_LOGIN_PASSWORD_FILE UNDO_MANAGEMENT

Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be identical on all instances only if these parameter values are set to zero.

What two parameters must be set at the time of starting up an ASM instance in a RAC environment?

The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set. Mention the components of Oracle clusterware:Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).


What is a CRS resource?

Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.


What is the use of OCR?

Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

How does a Oracle Clusterware manage CRS resources?

Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

Name some Oracle clusterware tools and their uses?

OIFCFG - allocating and deallocating network interfaces OCRCONFIG - Command-line tool for managing Oracle Cluster Registry OCRDUMP - Identify the interconnect being used CVU - Cluster verification utility to get status of CRS resources

What are the modes of deleting instances from ORacle Real Application cluster Databases?

We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).

How do we remove ASM from a Oracle RAC environment?

We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows: srvctl stop asm -n node_name srvctl remove asm -n node_name We can verify if ASM has been removed by issuing the following command: srvctl config asm -n node_name

How do we verify that an instance has been removed from OCR after deleting an instance?

Issue the following srvctl command: srvctl config database -d database_name cd CRS_HOME/bin ./crs_stat

How do we verify an existing current backup of OCR?

We can verify the current backup of OCR using the following command : ocrconfig -showbackup

What are the performance views in an Oracle RAC environment?

We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type.GV$ views obtain information from individual V$ views.

What are the types of connection load-balancing?

There are two types of connection load-balancing:server-side load balancing and clientside load balancing.

What is the differnece between server-side and client-side connection load balancing?

Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service. Give the usage of srvctl:srvctl start instance -d db_name -i "inst_name_list" [-o start_options]srvctl stop instance -d name -i "inst_name_list" [-o stop_options]srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount

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;