RAC FAQ

Oracle Real Application Clusters (RAC) FAQ - previously called Oracle Parallel Server (OPS):

What is RAC and how is it different from non RAC databases?

RAC stands for Real Application Clusters. It allows multiple nodes in a clustered system to mount and open a single database that resides on shared disk storage. Should a single system fail (node), the database service will still be available on the remaining nodes.

A non-RAC database is only available on a single system. If that system fails, the database service will be down (single point of failure).

Creating Real Application Clusters Data Dictionary Views with CATCLUST.SQL

Because you manually created your Real Application Clusters database, you must run the CATCLUST.SQL script to create Real Application Clusters-related views and tables. You must have SYSDBA privileges to run this script.

Can any application be deployed on RAC?

Most applications can be deployed on RAC without any modifications and still scale linearly (well, almost).

However, applications with 'hot' rows (the same row being accessed by processes on different nodes) will not work well. This is because data blocks will constantly be moved from one Oracle Instance to another. In such cases the application needs to be partitioned based on function or data to eliminate contention.

Do you need special hardware to run RAC?

RAC requires the following hardware components:

• A dedicated network interconnect - might be as simple as a fast network connection between nodes; and
• A shared disk subsystem.

Example systems that can be used with RAC:

• Windows Clusters
• Linux Clusters
• Unix Clusters like SUN PDB (Parallel DB).
• IBM z/OS in SYSPLEX
• HP Serviceguard extension for RAC

How many OCR and voting disks should one have?

For redundancy, one should have at lease two OCR disks and three voting disks (raw disk partitions). These disk partitions should be spread across different physical disks.

How does one convert a single instance database to RAC?

Oracle 10gR2 introduces a utility called rconfig (located in $ORACLE_HOME/bin) that will convert a single instance database to a RAC database.

$ cp $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC.xml racconv.xml

$ vi racconv.xml

$ rconfig racconv.xml

One can also use dbca and enterprise manager to convert the database to RAC mode.

For prior releases, follow these steps:

• Shut Down your Database:

SQL> CONNECT SYS AS SYSDBA

SQL> SHUTDOWN NORMAL

• Enable RAC - On Unix this is done by relinking the Oracle software.

• Make the software available on all computer systems that will run RAC. This can be done by copying the software to all systems or to a shared clustered file system.

• Each instance requires its own set of Redo Log Files (called a thread). Create additional log files:

SQL> CONNECT SYS AS SYSBDA

SQL> STARTUP EXCLUSIVE

SQL> ALTER DATABASE ADD LOGFILE THREAD 2

SQL>     GROUP G4 ('RAW_FILE1') SIZE 500k,

SQL>     GROUP G5 ('RAW_FILE2') SIZE 500k,

SQL>     GROUP G6 ('RAW_FILE3') SIZE 500k;

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

• Each instance requires its own set of Undo segments (rollback segments). To add undo segments for New Nodes:

UNDO_MANAGEMENT = auto

UNDO_TABLESPACE = undots2

• Edit the SPFILE/INIT.ORA files and number the instances 1, 2,...:

CLUSTER_DATABASE = TRUE           (PARALLEL_SERVER = TRUE prior to Oracle9i).  

INSTANCE_NUMBER = 1

THREAD = 1

UNDO_TABLESPACE = undots1          (or ROLLBACK_SEGMENTS if you use UNDO_MANAGEMENT=manual)

# Include %T for the thread in the LOG_ARCHIVE_FORMAT string.

# Set LM_PROCS to the number of nodes * PROCESSES

# etc....

• Create the dictionary views needed for RAC by running catclust.sql (previously called catparr.sql):

SQL> START ?/rdbms/admin/catclust.sql

• On all the computer systems, startup the instances:

SQL> CONNECT / as SYSDBA

SQL> STARTUP;

How does one stop and start RAC instances?

There are no difference between the way you start a normal database and RAC database, except that a RAC database needs to be started from multiple nodes. The CLUSTER_DATABASE=TRUE (PARALLEL_SERVER=TRUE) parameter needs to be set before a database can be started in cluster mode.

In Oracle 10g one can use the srvctl utility to start instances and listener across the cluster from a single node. Here are some examples:

$ srvctl status database -d RACDB

$ srvctl start database -d RACDB

$ srvctl start instance -d RACDB -i RACDB1

$ srvctl start instance -d RACDB -i RACDB2

$ srvctl stop database -d RACDB

$ srvctl start asm -n node2

Before Oracle 8.0, use the following command sequence from each node (using the old server manager):

SVRMGR> connect INTERNAL

SVRMGR> set retries 5

SVRMGR> startup parallel retry      .. or SVRMGR> startup shared

You can also use the SET INSTANCE instanceN command to switch between instances (if defined in TNSNAMES.ORA).

Can I test if a database is running in RAC mode?

Use the DBMS_UTILITY package to determine if a database is running in RAC mode or not. Example:

BEGIN

  IF dbms_utility.is_cluster_database THEN

      dbms_output.put_line('Running in SHARED/RAC mode.');

  ELSE

      dbms_output.put_line('Running in EXCLUSIVE mode.');

  END IF;

END;

/

For Oracle 8i and prior releases:

BEGIN

  IF dbms_utility.is_parallel_server THEN

      dbms_output.put_line('Running in SHARED/PARALLEL mode.');

  ELSE

      dbms_output.put_line('Running in EXCLUSIVE mode.');

  END IF;

END;

/

Another method is to look at the database parameters. For example, from SQL*Plus:

SQL> show parameter CLUSTER_DATABASE

If the value of CLUSTER_DATABASE is FALSE then database is not running in RAC Mode.

How can I keep track of active instances?

You can keep track of active RAC instances by executing one of the following queries:

SELECT * FROM SYS.V_$ACTIVE_INSTANCES;

SELECT * FROM SYS.V_$THREAD;

To list the active instances from PL/SQL, use DBMS_UTILITY.ACTIVE_INSTANCES().

How does one setup SQL*Net for RAC?

See Transparent Application Failover.

Can one see how connections are distributed across the nodes?

Select from gv$session. Some examples:

SELECT inst_id, count(*) "DB Sessions" FROM gv$session

 WHERE type = 'USER' GROUP BY inst_id;

With login time (hour):

SELECT inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24') "Hour when connected", count(*) "DB Sessions"

  FROM gv$session

 WHERE type = 'USER'

 GROUP BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24')

 ORDER BY inst_id, TO_CHAR(logon_time, 'DD-MON-YYYY HH24');

[edit] What is pinging and why is it so bad?

Starting with Oracle 9i, RAC can transfer blocks from one instance to another across the interconnect (cache fusion). This method is much faster than the old "pinging" method, where one instance had to write the block to disk before another instance could read it.

Oracle 8i and below:

Pinging is the process whereby one instance requests another to write a set of blocks from its SGA to disk so it can obtain it in exclusive mode. This method of moving data blocks from one instance's SGA to another is extremely slow. The challenge of tuning RAC/OPS is to minimize pinging activity.

Real Application Clusters

From Oracle FAQ

Jump to: navigation, search

Oracle Real Application Clusters (RAC) is a database clustering solution that allows more than one instance to mount and open an Oracle database. RAC can only be used on special clustered systems with shared disk storage and a private network interconnect.

A normal Oracle installation consists of a single Oracle instance that accesses a database on the same computer system. In contrast, RAC allows multiple instances on different computer systems (nodes in a cluster) to access the same database files simultaneously. Communication between instances is managed by the Distributed Lock Manager (DLM). To address the possibility of two or more instances attempting to modify the same information simultaneously, Oracle uses up to ten additional background processes, named LCK0 through LCK9, to lock the resources in use by these instances.

RAC is available with Oracle Enterprise Edition, and under certain conditions, with Standard Edition as well. These restrictions (for Standard Edition) include:

• Must use Oracle Clusterware (no third-party clusterware allowed);
• Must use ASM to store database files; and
• Can only use a max of 4 CPU sockets in the cluster (either 2 nodes with 2 CPUs each, or 4 nodes with 1 CPU each).

Contents [hide] 1 History 2 Benefits 3 RAC Storage Options 4 Oracle Clusterware 4.1 Virtual IP 4.2 Voting Disk 4.3 Cluster Registry 4.4 Clusterware processes 4.5 Stop and start 4.6 Starting and stopping resources 5 RAC parameters 6 GV$ views 7 Configure archiving 8 Configure Services 9 Also see 10 External links

[edit] History

RAC was previously called Oracle Parallel Server (OPS). Oracle re-branded OPS as RAC when they released Oracle 9i.

[edit] Benefits

Running Oracle in RAC mode can benefit you in the following ways:

• High availability - If some nodes fail, the remainder of the nodes will still be available for processing requests. Failover support is available from Oracle 8 with Oracle's Transparent Application Failover (TAF) technology and from Oracle 10g, server side load balancing.

• Speedup (increased transaction response time) - RAC normally adds some overhead. However, for some DSS applications one can achieve increased performance by running operations in parallel (mainly for DSS applications).

• Scale-up (increased transaction volume) - RAC can be used to provide increased application scalability (mainly used for OLTP applications).

[edit] RAC Storage Options

The database's data and control files are shared between the instances. However, each instance must have its own UNDO and REDO:

• UNDO: Each instance must have its own UNDO_TABLESPACE
• REDO: Each instance must have its own REDO LOG files (called a thread)

Shared files can be stored on:

• RAW volumes
• ASM
• Clustered file systems (like OCFS)

[edit] Oracle Clusterware

Oracle Clusterware (previously called Cluster Ready Services or CRS) provides Cluster Management Services and High Availability Services to Oracle and 3rd party software that wants to hook into it. For example, if you kill your LGWR process, CRS will detect the failure and automatically restart the database.

Oracle clusterware eliminates the need for 3rd party clusterware software like SUN Clusters, IBM HACMP and HP Omniguard. Oracle clusterware is provided at no additional cost with the 10g database.

Oracle clusterware needs to be installed on all nodes of the cluster before installing the database software. During installation, you will be prompted to configure a virtual IP, voting disk and cluster registry.

[edit] Virtual IP

IP address that will fail-over to another node in the cluster when a failure is detected. This will allow connected session to (almost) immediately fail-over to another node when a problem is experienced.

RAC requires one or more private interconnects, and two or more public network interfaces.

[edit] Voting Disk

A Voting Disk is a shared disk device or file used to determine node availability (establishes quorum) and resolve split-brain scenarios. All instances write to the voting disk (check in) to indicate that they are still active. This is required as instances may not always be able to communicate across the network with each other.

The voting disk must be stored in a raw partition or regular file in a clustered filesystem (like OCFS, but not on ASM!).

The voting disk, like the OCR, should be multiplexed and backed-up to protect them against media failures.

[edit] Cluster Registry

The Cluster Registry (OCR) is used to store cluster wide settings and status information. For example: node names, IP and VIP addresses, voting disk locations, node applications, database names, instance names, listener names, etc.

The Oracle Cluster Registry (OCR) is a binary file that is maintained by the CRS daemon. The OCR must be stored in a raw partition or regular file in a clustered filesystem (not on ASM!).

To see what is in the OCR, use the "ocrdump" command to dump it contents to a flat file.

[edit] Clusterware processes

When clusterware is started, the following process will be running:

crsd – Cluster Resource Services Daemon cssd – Cluster Synchronization Services Daemon evmd – Event Manager Daemon

Corresponded executables are located in $ORA_CRS_HOME/bin/.

[edit] Stop and start

Oracle Clusterware is started at boot with the /etc/rc.d/init.d/init.crs script.

Commands to manually start or stop:

/etc/init.d/init.crs start 

/etc/init.d/init.crs stop 

Commands to manually disable or enable start at boot time:

/etc/init.d/init.crs disable 

/etc/init.d/init.crs enable 

Check status of components registered in the OCR:

crs_stat

Status in tabular format:

crs_stat -t

[edit] Starting and stopping resources

Stops all RAC instances:

$ srvctl stop database –d myracdb 

Stops Listener, VIP, GSD, ONS:

$ srvctl stop nodeapps –n racnode1 

Starts ASM on racnode1 and all required dependencies:

$ srvctl start asm –n racnode1

Starts one instance and all required dependencies:

$ srvctl start instance –d myracdb –i mydb1

For more info, read article srvctl.

RAC parameters

Some of the INIT.ORA/SPFILE parameters required for RAC:

• CLUSTER DATABASE=TRUE -- start the database in RAC mode
• INSTANCE_NUMBER=n -- a unique instance number
• THREAD=n -- each instance must have its own thread of redo logs
• UNDO_TABLESPACE=... -- each instance must have its own undo tablespace
• Etc.

[edit] GV$ views

The V$ views only shows details from the currently connected instance. Use the GV$ parameters to query all values across all instances (note the INST_ID column).

For example: instead of using V$THREAD, query GV$THREAD to see all threads across all nodes.

[edit] Configure archiving

Since each instance has its own thread of redo logs, all instances needs to archive its own redo logs. For convenience, it is recommended that each instance archives to a shared cluster file system.

[edit] Configure Services

One can use DBCA or srvctl to configure a service with "primary" instances and "available" instances. Client connections will be routed to the "primary" instances. When one of the "primary" instances becomes unavailable, one of the "available" instance will be promoted to "primary".

NOTE: while fail-over to a "available" instance happens automatically, you need to manually switch them back to "primary" when they become available again.

RAC for Beginners

Real Application Clusters (RAC):


Oracle RAC, introduced with Oracle9i, is the successor to Oracle Parallel Server (OPS). Oracle RAC allows multiple
instances to access the same database (storage) simultaneously. RAC provides fault tolerance, load balancing,
and performance benefits by allowing the system to scale out, and at the same time since all nodes access the
same database, the failure of one instance will not cause the loss of access to the database.

Oracle RAC 10g is a shared disk subsystem. All nodes in the cluster must be able to access all of the data,
redo log files, control files and parameter files for all nodes in the cluster. The data disks must be globally
available in order to allow all nodes to access the database. Each node has its own redo log file(s) and UNDO tablespace,
but the other nodes must be able to access them (and the shared control file) in order to recover that node
in the event of a system failure.

The difference between Oracle RAC and OPS is the addition of Cache Fusion. With OPS a request for data from
one node to another required the data to be written to disk first, then the requesting node can read that data.
With cache fusion, data is passed along a high-speed interconnect using a sophisticated locking algorithm.

With Oracle RAC 10g, the data files, redo log files, control files, and archived log files reside on shared
storage on raw-disk devices, a NAS, ASM, or on a clustered file system

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


LMON

The background Global Enqueue Service Monitor (LMON) monitors the entire cluster to manage global resources.
LMON manages instance and process failures and the associated recovery for the Global Cache Service (GCS) and
Global Enqueue Service (GES). In particular, LMON handles the part of recovery associated with global resources.
LMON-provided services are also known as cluster group services (CGS)

This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.
This is called as Global Enqueue Service Monitor.


LCKx

The LCK process manages instance global enqueue requests and cross-instance call operations. Workload is automatically
shared and balanced when there are multiple Global Cache Service Processes (LMSx).

This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as
libry and row cache requests.

LMSx

The Global Cache Service Processes (LMSx) are the processes that handle remote Global Cache Service (GCS) messages.
Current Real Application Clusters software provides for up to 10 Global Cache Service Processes. The number of LMSx
varies depending on the amount of messaging traffic among nodes in the cluster. The LMSx handles the acquisition
interrupt and blocking interrupt requests from the remote instances for Global Cache Service resources.
For cross-instance consistent read requests, the LMSx will create a consistent read version of the block and send
it to the requesting instance. The LMSx also controls the flow of messages to remote instances.

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.


LMDx

The Global Enqueue Service Daemon (LMD) is the resource agent process that manages Global Enqueue Service (GES)
resource requests. The LMD process also handles deadlock detection Global Enqueue Service (GES) requests.
Remote resource requests are requests originating from another instance.

This process is called as global enqueue service daemon. This process manages incoming remote resource requests
within each instance.

DIAG

The diagnose daemon is a Real Application Clusters background process that captures diagnostic data on instance
process failures. No user control is required for this demo.

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.

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.

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.

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.

CRS

CRS (Cluster Ready Services) is a new feature for 10g Real Application Clusters that provides a standard cluster
interface on all platforms and performs new high availability operations not available in previous versions.
CRS manages cluster database functions including node membership, group services, global resource management,
and high availability. CRS serves as the clusterware software for all platforms. It can be the only clusterware or
run on top of vendor clusterware such as Sun Cluster, HP Serviceguard, etc.


CRS automatically starts the following resources:
· Nodeapps
o Virtual Internet Protocol(VIP) address for each node
o Global Services Daemon
o Oracle Net Listeners
o Oracle Network Services (ONS)
· Database Instance
· Services


Oracle Clusterware (Cluster Ready Services in 10g/ Cluster Manager in 9i) -
provides infrastructure that binds multiple nodes that then operate as single server. Clusterware monitors all
components like instances and listeners. There are two important components in Oracle clusterware, Voting Disk and
OCR (Oracle Cluster Registry)

OCR & Voting Disk

Oracle, 10g RAC, provided its own cluster-ware stack called CRS. The main file components of CRS are the
Oracle Cluster Repository (OCR) and the Voting Disk.
The OCR contains cluster and database configuration information for RAC and Cluster Ready Services (CRS).
Some of this information includes the cluster node list, cluster database instance-to-node mapping information,
and the CRS application resource profiles. The OCR contains configuration details for the cluster database and for
high availability resources such as services, Virtual Inerconnect Protocoal (VIP) addresses.

The Voting Disk is used by the Oracle cluster manager in various layers. The Node Monitor (NM) uses the Voting Disk
for the Disk Hearbeat, which is essential in the detection and resolution of cluster "split brain".


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

Cache Fusion and Global Cache Service (GCS)
Memory-to-memory copies between buffer caches over high-speed interconnects

· fast remote access times
· memory transfers for write or read access
· transfers for all types (e.g data, index, undo, headers )
· Cache coherency across the cluster
· globally managed access permissions to cached data
· GCS always knows whether and where a data block is cached
· a local cache miss may result in remote cache hit or disk read

Unix Commands for DBA

Monitoring database is one of the role Oracle database administrator has to fulfil. I have listed some of the basic and useful commands which are generally used in database administration. If you feel the list is incomplete, please suggest more Unix commands used by database adminstrators in day to day monitoring and administration.

1. To create a file.
cat > [filname]
2. To view contents of a file.
cat [filname] OR more [filname] OR less [filname]
3. To append a file.
cat >> [filname]
4. Creating an empty file.
touch [filname1][filname2][filname3]…. so on. OR touch filname{1,2,3….}
5. Removing a file.
rm [filname] OR rm -i [filname] OR rm -f [filname]
6. To check file type.
file [filname]
7. To create directory.
mkdir [directoryname]
8. To see present working directory.
pwd
9. To list file and directory in current directory.
ls OR ls -l (for long list)
OR ls -p (puts slash to directories)
OR ls -a (shows all directories including hidden files,hidden files or directories starts with dot.)
10. To create nested directories.
mkdir -p dir/dir1/dir2/dir3
11. To see directories in mapped way.
ls -R [directoryname]
12. To enter in another directory
cd [directoryname]
13. To go to home directory.
cd
14. To back to last directory
cd -
15. To enter in to parent directory
cd..
16. Remove directories
rmdir [directoryname] OR rm -r [directoryname](asks permission before deletion)
17. Removing nested directories
rm -r dir1/dir2/dir3 (dir3 is deleted)

18. Commands to compress and uncompress.
A) compress [filname] (compressed file is renamed with .z extension)
To read this file command is zcat [filname].
To uncompress this filecommand is uncompress [filname].
B) gzip[filname] (compressed file is renamed with .gz extension)
To read this file command is gzcat [filname].
To uncompress this filecommand is gunzip [filname]
C) bzip2[filname] (compressed file is renamed with .bz2 extension)
To read this file command is bzcat [filname].
To uncompress this filecommand is bunzip2 [filname]

19. Disk based utilities.
A) du command(disk usage command)
du [filname](displays file size in blocks)
OR du -k [filname](displays file size in KB)
OR du -h [filname](displays file size in human readable form)
OR du -sh[filename]

B) df command(disk free command)
df (displays diskfilesystems in blocks)
OR df -k (displays filesystem in KB)
OR df -h (displays file system in human readable form)
These commands lists only mounted file systems.

20. Help commands
A) man [command] (displays manual page for specified command)
OR man -a [command] (displays all manual pages for specified command)
B) info[command]
C) [command] –help
21. cp COMMAND
This comman is used to copy files and directories from one location to other location
cp
OR cp -r
22. mv COMMAND
This command is used to move directory from one location to other location as
well as to rename them.
mv (moving)
mv sundir/suntest suntest/moontest (renaming suntest to moontest)
23. Change group of the file
chgrp[options] group file
24. Changing file or directory access permissions
chmod [options] file

25 Reporting current date and time
date [options]
26. Comparing the two files and display the differences(text files only)
diff[options] file1 file2
27. Remotely copy files from this machine to another machine
rcp[options] hostname
28. Displaying the last few lines (or parts)of a file
tail [options] file
tail -100 file(displays last 100 lines of file)
29. Removing repeated lines in file
uniq [options] file.
30. Communicating with another host using telnet protocol
telnet[host[port]]
31. Reporting who is logged in and what process are running
who OR w.

32. Killing the process
kill
33. Set or change your password
passwd[options]

34. Showing status of active process
ps [options]

35. Shows the currently running process and some important information about there CPU Usage and Memory Space Usuage.

TOP

Rebuild Index

Fragmentation or Rebuilding of Index:

Scenario:

Let us consider one Table Emp, which is having 1000 rows and created index on the columns. The User was deleted 500 rows from the Emp Table.

But the deleted rows will be still maintained due to High Water Mark. This may occur in issue of Database performance.

While Re-Building the index, the index Re-creates the index only for existing rows in the table. This Re-Building the index is called the Index Fragmenation.

Steps to Fragmention:

Step 1: Let us consider, TEST is the index name for the Emp table.

Analyze Index TEST validate structure;
--------> Index is Analyzed.

Step 2: Select * from Index_status;

Step 3: Select Del_lf_rows, lf_rows from index_stats;

Del_lf_rows--> No.of deleted rows
lf_rows--> Total no.of rows

Step 4: Select (del_lf_rows/lf_rows*100)from index_stats where name='TEST';

Step 5: The result is 30%, so we must rebuild the index is called fragmentation.

Note: The result is >30% means, we must rebuild/fragment the particular index
The result is <30% means, we must coalesce/defragment the particular index

Step 6: How to Fragment/Rebuild the index

Alter Index TEST Rebuild;

Step 7: How to Coalesce/De fragment the index

Alter Index TEST Coalesce;

Step 8: After the rebuilding/or Coalescing the index we must check out the status of the index;

Analyze Index TEST validate structure;
--------> Index is Analyzed.

Step 9: Select (del_lf_rows/lf_rows*100) from index_stats where name='IBM';

Step 10: The above calculation result should be zero(0).

Block Recover using RMAN

BLOCK CORRUPTION ERROR:

Cause: When the datafile is corrupted, some of the blocks are corrupted in the particular datafile it shows the error message like block corruption.

The error code is ORA-01578.

By default, RMAN will check for physical corruption. We can include CHECK LOGICAL in RMAN
to find the corrupted blocks.

How to check the particular block is corrupted:

we can query,

v$database_block_corruption;

How to recover the corrupted blocks:

RMAN> BLOCKRECOVER DATAFILE 12 BLOCK 5; [recover the particular block number which is corrupted.]

Here Datafile 12 is the particular datafile number, and block 5 is the corrupted block number.

or

RMAN> blockrecover corruption list; (all blocks from v$database_block_corruption it will recover)

Row Chaining & Row Migration

IDENTIFY AND SOLVE THE CHAINED ROWS:

ROW CHAINING:

It occurs due to huge inserts where a particular row is too large to fit in the empty block. In this case, the oracle stores the data of the row in a chain
of two or more data blocks. So more I/O is needed to fetch a particular block.

It usually occurs with large rows that contain a large object[LOB] datatypes. LOB supports upto 2GB data.

ROW MIGRATION:

It occurs due to updations. An update increases the amount of data in a row so that the row no longer fits in its datablock, then
the oracle tries to find another block with enough free space to hold the entire row.

Solutions:

1. Move the table from one tablespace to another tablespace which is bigger blocksize.

Alter table test move tablespace users;

2. Take the export of the table and drop the existing table and import the table.

Steps:

SQL> conn sys/sys@info as sysdba

SQL> alter system set db_4k_cache_size=1m scope=both;

System altered.

SQL> create tablespace info datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\INFO\INFO01.DBF'
2 size 20m blocksize 4k;

Tablespace created.

SQL> create user robo identified by robo
2 default tablespace info
3 quota unlimited on info;

User created.

SQL> grant connect,resource to robo;

Grant succeeded.

SQL> conn robo/robo
Connected.

SQL> create table test as select * from all_objects;

Table created.

SQL> select count(*) from test;

COUNT(*)
----------
40686

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
TEST

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
98 0 TEST

SQL> alter table test add address varchar2(1000);

Table altered.

SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
ADDRESS VARCHAR2(1000)

SQL> update test set address='asasasaasasasasasasasasasasasaasasasasasasas';

40686 rows updated.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
145 10310 TEST

SQL> ho
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>exp 'sys/sys as sysdba' file=abc.dmp tables=robo.test direct=y

Export: Release 10.2.0.1.0 - Production on Mon Aug 3 06:56:51 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
Current user changed to ROBO
. . exporting table TEST 40686 rows exported
Export terminated successfully without warnings.

C:\>exit

SQL> show user
USER is "ROBO"
SQL> drop table test purge;

Table dropped.

SQL> ho
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>imp 'sys/sys as sysdba' file=abc.dmp tables=test fromuser=robo touser=robo

Import: Release 10.2.0.1.0 - Production on Mon Aug 3 06:59:19 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing ROBO's objects into ROBO
. . importing table "TEST" 40686 rows imported
Import terminated successfully without warnings.

C:\>exit

SQL> select count(*) from test;

COUNT(*)
----------
40686

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select avg_row_len, chain_cnt, table_name from user_tables
2 where table_name='TEST';

AVG_ROW_LEN CHAIN_CNT TABLE_NAME
----------- ---------- ------------------------------
143 0 TEST

Note:

The above output displays the Chained Rows is 0...Now it reduces the I/O while fetching the rows....

Oracle 10g Background Process

10G BACKGROUND PROCESS:

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.

RVWR

Process responsible for writing flashback logs which stores pre-image of data-blocks.

These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

M000

MMON background slave (m000) processes.


ASM BACKGROUND PROCESS:

RBAL

RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx

These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB

The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

ASM instance uses at least two additional background processes to manage data storage operations. The Rebalancer process, RBAL, coordinates the rebalance activity for ASM disk groups, and the Actual ReBalance processes, ARBn, handle the actual rebalance of data extent movements.

RAC BACKGROUND PROCESS:

The additional RAC centric processes are DIAG, LCK, LMON, LMDn, and LMSn processes. We will give a brief description of each and discuss how they interact in a RAC environment next.

DIAG: This is a diagnostic daemon. It constantly monitors the health of the instances across the RAC and possible failures on the RAC. There is one per instance.

LCK: This lock process manages requests that are not cache-fusion requests. Requests like row cache requests and library cache requests. Only a single LCK process is allowed for each instance.

LMD: The Lock Manager Daemon. This is also sometimes referred to as the GES (Global Enqueue Service) daemon since its job is to manage the global enqueue and global resource access. It also detects deadlocks and monitors lock conversion timeouts.

LMON: The Lock Monitor Process. It is the GES monitor. It reconfigures the lock resources adding or removing nodes. LMON will generate a trace file every time a node reconfiguration takes place. It also monitors the RAC cluster wide and detects a node’s demise and trigger a quick reconfiguration.

LMS: This is the Lock Manager Server Process or the LMS process, sometimes also called the GCS (Global Cache Services) process. Its primary job is to transport blocks across the nodes for cache-fusion requests. If there is a consistent-read request, the LMS process rolls back the block, makes a Consistent-Read image of the block and then ship this block across the HSI (High Speed Interconnect) to the process requesting from a remote node. LMS must also check constantly with the LMD background process (or our GES process) to get the lock requests placed by the LMD process. Up to 10 such processes can be generated dynamically.