Showing posts with label ASM. Show all posts
Oracle RAC interview questions and answers
Q 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.
Q what is dynamic remastering ? When will the dynamic remastering happens?
dynamic remastering is ability to move the ownership of resource from one instance to another instance in RAC. dynamic resource remastering is used to implement for resource affinity for increased performance. resource affinity optimized the system in situation where update transactions are being executed in one instance. when activity shift to another instance the resource affinity correspondingly move to another instance. If activity is not localized then resource ownership is hashed to the instance.
In 10g dynamic remastering happens in file+object level.the process of remastering is very stringent. For one instance should touch more than 50 times than the other instance in particular period(say 10 mints). this touch ratio and time can be tuned by gc_affinity_limit and _gc_affinity_time parameter.
Q why we required to maintain odd number of voting disks?
Odd number of disk are to avoid split brain, When Nodes in cluster can't talk to each other they run to lock the Voting disk and whoever lock the more disk will survive, if disk number are even there are chances that node might lock 50% of disk (2 out of 4) then how to decide which node to evict.
whereas when number is odd, one will be higher than other and each for cluster to evict the node with less number
Q How you check the health of Your RAC Database?
'crsctl' command from root or oracle user can be used to check the clusterware health But for starting or stopping we have to use root user or any privilege user.
[oracle@TEST_NODE1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
Q How you check the services in RAC Node?
We can check the service or start the services with 'srvctl' command.load balanced/TAF service named RAC online.
[oracle@TEST_NODE1 ~]$ srvctl start service -d orcl -s RAC
[oracle@TEST_NODE1 ~]$ crsstat
Q If there is some issue with virtual IP how will you troubleshoot it?How will you change virtual ip?
To change the VIP (virtual IP) on a RAC node, use the command
[oracle@testnode oracle]$ srvctl modify nodeapps -A new_address
Q How you will backup your RAC Database?
Backup strategy of RAC Database:
An RAC Database consists of
1)OCR
2)Voting disk &
3)Database files, controlfiles, redolog files & Archive log files
Q Do you have any idea of load balancing in application?How load balancing is done?
http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/
Q 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.
Q What is RAC and how is it different from non RAC databases?
RAC stands for Real Application Cluster, you have n number of instances running in their own separate nodes and based on the shared storage. Cluster is the key component and is a collection of servers operations as one unit. RAC is the best solution for high performance and high availably. Non RAC databases has single point of failure in case of hardware failure or server crash.
Q 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 immediate
srvctl start database -d name [-o start_options]
srvctl stop database -d name [-o stop_options]
srvctl start database -d orcl -o mount
Q 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
Q 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.
Q What are the different network components are in 10g RAC?
public, private, and vip components
Private interfaces is for intra node communication. VIP is all about availability of application. When a node fails then the VIP component fail over to some other node, this is the reason that all applications should based on vip components means tns entries should have vip entry in the host list
Q 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.
Q What are the major RAC wait events?
In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs.The most common wait events related to this are gc cr request and gc buffer busy
GC CR request :the time it takes to retrieve the data from the remote cache
Reason: RAC Traffic Using Slow Connection or Inefficient queries (poorly tuned queries will increase the amount of data blocks requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.)
GC BUFFER BUSY: It is the time the remote instance locally spends accessing the requested data block.
Q 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.
Q Give details on 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.
Q Give details on LMD
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
Q 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.
Q 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.
Q 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.
Q How to export and import crs resources while migrating Oracle RAC to new server.
Below script generate svrctl add script for database, instance, service and 11G listeners from OCR from current RAC.
Save the result of the script and run it at new RAC.
for DBNAME in $(srvctl config database)
do
# Generate DB resource
srvctl config database -d $DBNAME -a | awk -v dbname="$DBNAME" \
'BEGIN { FS=":" }
$1~/Oracle home/ || $1~/ORACLE_HOME/ {dbhome = "-o" $2}
$1~/Spfile/ || $1~/SPFILE/ {spfile = "-p" $2}
$1~/Disk Groups/ {dg = "-a" $2}
END { if (avail == "-a ") {avail = ""}; printf "%s %s %s %s %s\n", "srvctl add database -d ", dbname, dbhome, spfile, dg }'
# Generate Instance resource
srvctl status database -d $DBNAME | awk -v dbname="$DBNAME" \
'$4~/running/ { printf "%s %s %s %s %s %s\n", "srvctl add instance -d ",dbname, " -i ", $2 ," -n ", $7 }
$5~/running/ { printf "%s %s %s %s %s %s \n", "srvctl add instance -d ",dbname, " -i ", $2 ," -n ", $8 }'
# Modify instance for 10G - ASM dependency
if [ $(echo $ORACLE_HOME | grep "1020" | wc -l ) -eq 1 ]
then
srvctl status database -d $DBNAME | awk -v dbname="$DBNAME" \
'$2~/1$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM1" }
$2~/2$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM2" }
$2~/3$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM3" }
$2~/4$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM4" }'
fi
echo "srvctl start database -d $DBNAME"
# Generate Service resource
snamelist=$(srvctl status service -d $DBNAME | awk '{print $2}')
for sname in $snamelist
do
srvctl config service -d $DBNAME -s $sname| awk -v dbname="$DBNAME" -v sname=$sname \
'BEGIN { FS=":"}
$1~/Preferred instances/ {pref = "-r" $2}
$1~/PREF/ {pref = "-r" $2; sub(/AVAIL/, "", pref) }
$1~/Available instances/ {avail = "-a" $2}
$2~/AVAIL/ {avail = "-a" $3}
$1~/Failover type/ {ft = "-e" $2}
$1~/Failover method/ {fm = "-m" $2}
$1~/Runtime Load Balancing Goal/ {g = "-B" $2}
END { if (avail == "-a ") {avail = ""}; printf "%s %s %s %s %s %s %s %s %s %s\n", "srvctl add service -d ",dbname, "-s ", sname, pref, avail ,ft, fm,g, "-P BASIC"}'
echo "srvctl start service -d $DBNAME -s $sname"
done
done
# Listener at 11G Home. 10G listener can't ba added with srvctl.
srvctl config listener | awk \
'BEGIN { FS=":"; state = 0; }
$1~/Name/ {lname = "-l" $2; state=1};
$1~/Home/ && state == 1 {ohome = "-o" $2; state=2;}
$1~/End points/ && state == 2 {lport = "-p " $3; state=3;}
state == 3 {if (ohome != "-o ") {printf "%s %s %s %s\n", "srvctl add listener ", lname, ohome, lport;} state=0;}'
Q 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.
Q What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
Q 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.
Q 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).
Q 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.
Q How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.
Q Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware intercnects.
Q What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
Q 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.
Q What is the use of a service in Oracle RAC environment?
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.
Q What are the characteristics controlled by Oracle services feature?
The charateristics include a unique name, workload balancing and failover options,and high availability characteristics.
Q What enables 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q 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,NETCA
Q 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q 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).
Q 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
Q What is ORA-00603: ORACLE server session terminated by fatal error or ORA-29702: error occurred in Cluster Group Service operation?
RAC node name was listed in the loopback address...
Q 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.
Q Mention the components of Oracle clusterware
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
Q 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.
Q 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).
Q 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).
Q 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
Q 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).
Q 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
Q 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
Q 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 client-side load balancing.
Q What is the difference 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.
Q What are the three greatest benefits that RAC provides??
The three main benefits are availability, scalability, and the ability to use low cost commodity hardware. RAC allows an application to scale vertically, by adding CPU, disk and memory resources to an individual server. But RAC also provides horizontal scalability, which is achieved by adding new nodes into the cluster. RAC also allows an organization to bring these resources online as they are needed. This can save a small or midsize organization a lot of money in the early stages of a project.
In a RAC environment, if a node in the cluster fails, the application continues to run on the surviving nodes contained in the cluster. If your application is configured correctly, most users won't even know that the node they were running on became unavailable.
Q What are the major RAC wait events?
In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs.The most common wait events related to this are gc cr request and gc buffer busy
GC CR request: the time it takes to retrieve the data from the remote cache
Reason: RAC Traffic Using Slow Connection or Inefficient queries (poorly tuned queries will increase the amount of data blocks
requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.)
GC BUFFER BUSY: It is the time the remote instance locally spends accessing the requested data block.
Q What are the different network components in Oracle 10g RAC?
We have public, private, and VIP components. Private interfaces is for intra node communication. VIP is all about availability of application. When a node fails then the VIP component will fail over to some other node, this is the reason that all applications should be based on VIP components. This means that tns entries should have VIP entry in the host list.
Q Tune the following RAC DATABASE (DBNAME=PROD) which is 3 node RAC.
PROD1 PROD2 PROD3
CPU 8 CPU 15 CPU 8
32 GB RAM 12 GB RAM 16 GB RAM
What are you looking for here? What tuning information do you expect?
It is a 3 node cluster with different hardware configuration running RAC.
I would put 20% of the memory for Oracle in each node. So that would mean that the SGA is different in each of the nodes.
Also since the CPU's are different PROD2 can have more number of max number of processes as compared to the rest of them.
But as I said this is just configuration, this is not tuning. Question is not clear.
Q Write a sample script for RMAN for the recovery if all the instance are down.(First explain the procedure how you will restore)
Bring all nodes down.
Start one Node
Restore all datafiles and archive logs.
Recover 1 Node.
Open the database.
bring other nodes up.
Confirm that all nodes are operational.
Clients 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.
Q How to move OCR and Voting disk to new storage device?
Moving OCR
==========
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
Q What is runfixup.sh script in Oracle Clusterware 11g release 2 installation
With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts, called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.
The Fixup script does the following:
¦ If necessary sets kernel parameters to values required for successful installation,
including:
– Shared memory parameters.
– Open file descriptor and UDP send/receive parameters.
¦ Sets permissions on the Oracle Inventory (central inventory) directory.
¦ Reconfigures primary and secondary group memberships for the installation
owner, if necessary, for the Oracle Inventory directory and the operating system
privileges groups.
¦ Sets shell limits if necessary to required values.
Q When exactly during the installation process are clusterware components created?
After fulfilling the pre-installation requirements, the basic installation steps to follow are:
1. Invoke the Oracle Universal Installer (OUI)
2. Enter the different information for some components like:
- name of the cluster
- public and private node names
- location for OCR and Voting Disks
- network interfaces used for RAC instances
-etc.
3. After the Summary screen, OUI will start copying under the $CRS_HOME (this is the $ORACLE_HOME for Oracle Clusterware) in the local node the libraries and executables.
- here we will have the daemons and scripts init.* created and configured properly.
Oracle Clusterware is formed of several daemons, each one of which have a special function inside the stack. Daemons are executed via the init.* scripts (init.cssd, init.crsd and init.evmd).
- note that for CRS only some client libraries are recreated, but not all the executables (as for the RDBMS).
4. Later the software is propagated to the rest of the nodes in the cluster and the oraInventory is updated.
5. The installer will ask to execute root.sh on each node. Until this step the software for Oracle Clusterware is inside the $CRS_HOME.
Running root.sh will create several components outside the $CRS_HOME:
- OCR and VD will be formated.
- control files (or SCLS_SRC files ) will be created with the correct contents to start Oracle Clusterware.
These files are used to control some aspects of Oracle Clusterware like:
- enable/disable processes from the CSSD family (Eg. oprocd, oslsvmon)
- stop the daemons (ocssd.bin, crsd.bin, etc).
- prevent Oracle Clusterware from being started when the machine boots.
- etc.
- /etc/inittab will be updated and the init process is notified.
In order to start the Oracle Clusterware daemons, the init.* scripts first need to be run. These scripts are executed by the daemon init. To accomplish this some entries must be created in the file /etc/inittab.
- the different processes init.* (init.cssd, init.crsd, etc) will start the daemons (ocssd.bin, crsd.bin, etc). When all the daemons are running then we can say that the installation was successful
- On 10.2 and later, running root.sh on the last node in the cluster also will create the nodeapps (VIP, GSD and ONS). On 10.1, VIPCA is executed as part of the RAC installation.
6. After running root.sh on each node, we need to continue with the OUI session. After pressing the 'OK' button OUI will include the information for the public and cluster_interconnect interfaces. Also CVU (Cluster Verification Utility) will be executed.
Q 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.
Q 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.
Q 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
Q 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.
Q 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
Q How do you backup voting disk
#dd if=voting_disk_name of=backup_file_name
Q How do I identify the voting disk location
#crsctl query css votedisk
Q How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
Q 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.
Q 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.
Q 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.
Q 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
Q 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q What is the use of a service in Oracle RAC environment?
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.
Q What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing and failover options, and high availability characteristics.
Q What is a voting disk?
A voting disk is a file that manages information about node membership.
Q What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks
Q How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name
Q What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with a minimum block size of 4KB.
Q How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
where,
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk
Q How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path
Q How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.
Q How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force
Q How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force
Q How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force
Q What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk
Q When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration
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.
difference between RAC public IP, private IP and Virtual VIP
Answer: In Oracle RAC clusters, we see three types of IP addresses:
Public IP: The public IP address is for the server. This is the same as any server IP address, a unique address with exists in /etc/hosts.
Private IP: Oracle RCA requires "private IP" addresses to manage the CRS, the clusterware heartbeat process and the cache fusion layer.
Virtual IP: Oracle uses a Virtual IP (VIP) for database access. The VIP must be on the same subnet as the public IP address. The VIP is used for RAC failover (TAF).
Global Cache Service
Global Cache Service (GCS) is the heart of Cache Fusion concept. It is through GCS that data integrity in RAC is maintained when more than one instance need a particular data block. Instances look up to the GCS for fulfilling their data block needs.
GCS is responsible for:
Tracking the data block
Accepting the data block requests from instances
Informing the holding instance to release the lock on the data block or ship a CR image
Coordinating the shipping of data blocks as needed between the instance through the interconnect
Informing the instances to keep or discard PIs
dynamic remastering is ability to move the ownership of resource from one instance to another instance in RAC. dynamic resource remastering is used to implement for resource affinity for increased performance. resource affinity optimized the system in situation where update transactions are being executed in one instance. when activity shift to another instance the resource affinity correspondingly move to another instance. If activity is not localized then resource ownership is hashed to the instance.
In 10g dynamic remastering happens in file+object level.the process of remastering is very stringent. For one instance should touch more than 50 times than the other instance in particular period(say 10 mints). this touch ratio and time can be tuned by gc_affinity_limit and _gc_affinity_time parameter.
Q why we required to maintain odd number of voting disks?
Odd number of disk are to avoid split brain, When Nodes in cluster can't talk to each other they run to lock the Voting disk and whoever lock the more disk will survive, if disk number are even there are chances that node might lock 50% of disk (2 out of 4) then how to decide which node to evict.
whereas when number is odd, one will be higher than other and each for cluster to evict the node with less number
Q How you check the health of Your RAC Database?
'crsctl' command from root or oracle user can be used to check the clusterware health But for starting or stopping we have to use root user or any privilege user.
[oracle@TEST_NODE1 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
Q How you check the services in RAC Node?
We can check the service or start the services with 'srvctl' command.load balanced/TAF service named RAC online.
[oracle@TEST_NODE1 ~]$ srvctl start service -d orcl -s RAC
[oracle@TEST_NODE1 ~]$ crsstat
Q If there is some issue with virtual IP how will you troubleshoot it?How will you change virtual ip?
To change the VIP (virtual IP) on a RAC node, use the command
[oracle@testnode oracle]$ srvctl modify nodeapps -A new_address
Q How you will backup your RAC Database?
Backup strategy of RAC Database:
An RAC Database consists of
1)OCR
2)Voting disk &
3)Database files, controlfiles, redolog files & Archive log files
Q Do you have any idea of load balancing in application?How load balancing is done?
http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/
Q 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.
Q What is RAC and how is it different from non RAC databases?
RAC stands for Real Application Cluster, you have n number of instances running in their own separate nodes and based on the shared storage. Cluster is the key component and is a collection of servers operations as one unit. RAC is the best solution for high performance and high availably. Non RAC databases has single point of failure in case of hardware failure or server crash.
Q 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 immediate
srvctl start database -d name [-o start_options]
srvctl stop database -d name [-o stop_options]
srvctl start database -d orcl -o mount
Q 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
Q 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.
Q What are the different network components are in 10g RAC?
public, private, and vip components
Private interfaces is for intra node communication. VIP is all about availability of application. When a node fails then the VIP component fail over to some other node, this is the reason that all applications should based on vip components means tns entries should have vip entry in the host list
Q 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.
Q What are the major RAC wait events?
In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs.The most common wait events related to this are gc cr request and gc buffer busy
GC CR request :the time it takes to retrieve the data from the remote cache
Reason: RAC Traffic Using Slow Connection or Inefficient queries (poorly tuned queries will increase the amount of data blocks requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.)
GC BUFFER BUSY: It is the time the remote instance locally spends accessing the requested data block.
Q 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.
Q Give details on 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.
Q Give details on LMD
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.
Q 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.
Q 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.
Q 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.
Q How to export and import crs resources while migrating Oracle RAC to new server.
Below script generate svrctl add script for database, instance, service and 11G listeners from OCR from current RAC.
Save the result of the script and run it at new RAC.
for DBNAME in $(srvctl config database)
do
# Generate DB resource
srvctl config database -d $DBNAME -a | awk -v dbname="$DBNAME" \
'BEGIN { FS=":" }
$1~/Oracle home/ || $1~/ORACLE_HOME/ {dbhome = "-o" $2}
$1~/Spfile/ || $1~/SPFILE/ {spfile = "-p" $2}
$1~/Disk Groups/ {dg = "-a" $2}
END { if (avail == "-a ") {avail = ""}; printf "%s %s %s %s %s\n", "srvctl add database -d ", dbname, dbhome, spfile, dg }'
# Generate Instance resource
srvctl status database -d $DBNAME | awk -v dbname="$DBNAME" \
'$4~/running/ { printf "%s %s %s %s %s %s\n", "srvctl add instance -d ",dbname, " -i ", $2 ," -n ", $7 }
$5~/running/ { printf "%s %s %s %s %s %s \n", "srvctl add instance -d ",dbname, " -i ", $2 ," -n ", $8 }'
# Modify instance for 10G - ASM dependency
if [ $(echo $ORACLE_HOME | grep "1020" | wc -l ) -eq 1 ]
then
srvctl status database -d $DBNAME | awk -v dbname="$DBNAME" \
'$2~/1$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM1" }
$2~/2$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM2" }
$2~/3$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM3" }
$2~/4$/ { printf "%s %s %s %s %s \n", "srvctl modify instance -d ",dbname, " -i ", $2 ," -s +ASM4" }'
fi
echo "srvctl start database -d $DBNAME"
# Generate Service resource
snamelist=$(srvctl status service -d $DBNAME | awk '{print $2}')
for sname in $snamelist
do
srvctl config service -d $DBNAME -s $sname| awk -v dbname="$DBNAME" -v sname=$sname \
'BEGIN { FS=":"}
$1~/Preferred instances/ {pref = "-r" $2}
$1~/PREF/ {pref = "-r" $2; sub(/AVAIL/, "", pref) }
$1~/Available instances/ {avail = "-a" $2}
$2~/AVAIL/ {avail = "-a" $3}
$1~/Failover type/ {ft = "-e" $2}
$1~/Failover method/ {fm = "-m" $2}
$1~/Runtime Load Balancing Goal/ {g = "-B" $2}
END { if (avail == "-a ") {avail = ""}; printf "%s %s %s %s %s %s %s %s %s %s\n", "srvctl add service -d ",dbname, "-s ", sname, pref, avail ,ft, fm,g, "-P BASIC"}'
echo "srvctl start service -d $DBNAME -s $sname"
done
done
# Listener at 11G Home. 10G listener can't ba added with srvctl.
srvctl config listener | awk \
'BEGIN { FS=":"; state = 0; }
$1~/Name/ {lname = "-l" $2; state=1};
$1~/Home/ && state == 1 {ohome = "-o" $2; state=2;}
$1~/End points/ && state == 2 {lport = "-p " $3; state=3;}
state == 3 {if (ohome != "-o ") {printf "%s %s %s %s\n", "srvctl add listener ", lname, ohome, lport;} state=0;}'
Q 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.
Q What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
Q 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.
Q 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).
Q 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.
Q How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.
Q Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware intercnects.
Q What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
Q 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.
Q What is the use of a service in Oracle RAC environment?
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.
Q What are the characteristics controlled by Oracle services feature?
The charateristics include a unique name, workload balancing and failover options,and high availability characteristics.
Q What enables 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q 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,NETCA
Q 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q 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).
Q 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
Q What is ORA-00603: ORACLE server session terminated by fatal error or ORA-29702: error occurred in Cluster Group Service operation?
RAC node name was listed in the loopback address...
Q 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.
Q Mention the components of Oracle clusterware
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
Q 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.
Q 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).
Q 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).
Q 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
Q 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).
Q 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
Q 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
Q 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 client-side load balancing.
Q What is the difference 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.
Q What are the three greatest benefits that RAC provides??
The three main benefits are availability, scalability, and the ability to use low cost commodity hardware. RAC allows an application to scale vertically, by adding CPU, disk and memory resources to an individual server. But RAC also provides horizontal scalability, which is achieved by adding new nodes into the cluster. RAC also allows an organization to bring these resources online as they are needed. This can save a small or midsize organization a lot of money in the early stages of a project.
In a RAC environment, if a node in the cluster fails, the application continues to run on the surviving nodes contained in the cluster. If your application is configured correctly, most users won't even know that the node they were running on became unavailable.
Q What are the major RAC wait events?
In a RAC environment the buffer cache is global across all instances in the cluster and hence the processing differs.The most common wait events related to this are gc cr request and gc buffer busy
GC CR request: the time it takes to retrieve the data from the remote cache
Reason: RAC Traffic Using Slow Connection or Inefficient queries (poorly tuned queries will increase the amount of data blocks
requested by an Oracle session. The more blocks requested typically means the more often a block will need to be read from a remote instance via the interconnect.)
GC BUFFER BUSY: It is the time the remote instance locally spends accessing the requested data block.
Q What are the different network components in Oracle 10g RAC?
We have public, private, and VIP components. Private interfaces is for intra node communication. VIP is all about availability of application. When a node fails then the VIP component will fail over to some other node, this is the reason that all applications should be based on VIP components. This means that tns entries should have VIP entry in the host list.
Q Tune the following RAC DATABASE (DBNAME=PROD) which is 3 node RAC.
PROD1 PROD2 PROD3
CPU 8 CPU 15 CPU 8
32 GB RAM 12 GB RAM 16 GB RAM
What are you looking for here? What tuning information do you expect?
It is a 3 node cluster with different hardware configuration running RAC.
I would put 20% of the memory for Oracle in each node. So that would mean that the SGA is different in each of the nodes.
Also since the CPU's are different PROD2 can have more number of max number of processes as compared to the rest of them.
But as I said this is just configuration, this is not tuning. Question is not clear.
Q Write a sample script for RMAN for the recovery if all the instance are down.(First explain the procedure how you will restore)
Bring all nodes down.
Start one Node
Restore all datafiles and archive logs.
Recover 1 Node.
Open the database.
bring other nodes up.
Confirm that all nodes are operational.
Clients 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.
Q How to move OCR and Voting disk to new storage device?
Moving OCR
==========
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
Q What is runfixup.sh script in Oracle Clusterware 11g release 2 installation
With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts, called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.
The Fixup script does the following:
¦ If necessary sets kernel parameters to values required for successful installation,
including:
– Shared memory parameters.
– Open file descriptor and UDP send/receive parameters.
¦ Sets permissions on the Oracle Inventory (central inventory) directory.
¦ Reconfigures primary and secondary group memberships for the installation
owner, if necessary, for the Oracle Inventory directory and the operating system
privileges groups.
¦ Sets shell limits if necessary to required values.
Q When exactly during the installation process are clusterware components created?
After fulfilling the pre-installation requirements, the basic installation steps to follow are:
1. Invoke the Oracle Universal Installer (OUI)
2. Enter the different information for some components like:
- name of the cluster
- public and private node names
- location for OCR and Voting Disks
- network interfaces used for RAC instances
-etc.
3. After the Summary screen, OUI will start copying under the $CRS_HOME (this is the $ORACLE_HOME for Oracle Clusterware) in the local node the libraries and executables.
- here we will have the daemons and scripts init.* created and configured properly.
Oracle Clusterware is formed of several daemons, each one of which have a special function inside the stack. Daemons are executed via the init.* scripts (init.cssd, init.crsd and init.evmd).
- note that for CRS only some client libraries are recreated, but not all the executables (as for the RDBMS).
4. Later the software is propagated to the rest of the nodes in the cluster and the oraInventory is updated.
5. The installer will ask to execute root.sh on each node. Until this step the software for Oracle Clusterware is inside the $CRS_HOME.
Running root.sh will create several components outside the $CRS_HOME:
- OCR and VD will be formated.
- control files (or SCLS_SRC files ) will be created with the correct contents to start Oracle Clusterware.
These files are used to control some aspects of Oracle Clusterware like:
- enable/disable processes from the CSSD family (Eg. oprocd, oslsvmon)
- stop the daemons (ocssd.bin, crsd.bin, etc).
- prevent Oracle Clusterware from being started when the machine boots.
- etc.
- /etc/inittab will be updated and the init process is notified.
In order to start the Oracle Clusterware daemons, the init.* scripts first need to be run. These scripts are executed by the daemon init. To accomplish this some entries must be created in the file /etc/inittab.
- the different processes init.* (init.cssd, init.crsd, etc) will start the daemons (ocssd.bin, crsd.bin, etc). When all the daemons are running then we can say that the installation was successful
- On 10.2 and later, running root.sh on the last node in the cluster also will create the nodeapps (VIP, GSD and ONS). On 10.1, VIPCA is executed as part of the RAC installation.
6. After running root.sh on each node, we need to continue with the OUI session. After pressing the 'OK' button OUI will include the information for the public and cluster_interconnect interfaces. Also CVU (Cluster Verification Utility) will be executed.
Q 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.
Q 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.
Q 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
Q 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.
Q 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
Q How do you backup voting disk
#dd if=voting_disk_name of=backup_file_name
Q How do I identify the voting disk location
#crsctl query css votedisk
Q How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
Q 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.
Q 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.
Q 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.
Q 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
Q 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.
Q 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.
Q 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.
Q 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.
Q 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.
Q What is the use of a service in Oracle RAC environment?
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.
Q What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing and failover options, and high availability characteristics.
Q What is a voting disk?
A voting disk is a file that manages information about node membership.
Q What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks
Q How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
where,
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name
Q What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with a minimum block size of 4KB.
Q How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
where,
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk
Q How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path
Q How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.
Q How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force
Q How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force
Q How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force
Q What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk
Q When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration
R12.2 adop phase=prepare Failed While Synchronizing Snapshots
Error: remote_execution_result_20150619_122924.xml file shows following error.
Synchronizing Snapshots...
[ERROR] Failed to execute SQL statement :
begin
ad_zd_adop.sync_snapshots(342);
exception
when others then
update ad_adop_sessions set status='F'
where adop_session_id=8;
commit;
raise_application_error(-20001,'Error while calling ad_zd_adop.sync_snapshots.' || sqlerrm);
end;
Sol:
1. Execute the following script from SQL command prompt:
begin
ad_zd_adop.sync_snapshots(342);
exception when others then
raise_application_error(-20001,'Error while calling ad_zd_adop.sync_snapshots.' || sqlerrm);
end;
end;
/
2. rerun adop phase=prepare.
Synchronizing Snapshots...
[ERROR] Failed to execute SQL statement :
begin
ad_zd_adop.sync_snapshots(342);
exception
when others then
update ad_adop_sessions set status='F'
where adop_session_id=8;
commit;
raise_application_error(-20001,'Error while calling ad_zd_adop.sync_snapshots.' || sqlerrm);
end;
Sol:
1. Execute the following script from SQL command prompt:
begin
ad_zd_adop.sync_snapshots(342);
exception when others then
raise_application_error(-20001,'Error while calling ad_zd_adop.sync_snapshots.' || sqlerrm);
end;
end;
/
2. rerun adop phase=prepare.
Automatic Storage Management ( ASM )
Automatic
Storage Management (ASM)
Oracle Database
10g Release 1, introduced Automatic Storage Management (ASM),
a new framework for managing Oracle database files,
to bypass the OS overhead,
to simplify Oracle data management,
to enforce the SAME (Stripe And Mirror Everywhere,
RAID10), and
to provide a platform for file sharing in RAC and Grid computing.
Automatic Storage Management (ASM) is a new type of file
system. ASM provided a foundation for highly efficient storage management with
kernelized asynchronous I/O, direct I/O, redundancy, striping, and an
easy way to manage storage. ASM is recommended file system for RAC and single instance ASM for
storing database files. This provides direct I/O to the file and performance is
comparable with that provided by raw devices. Oracle creates a separate instance
for this purpose.
ASM includes volume management functionality similar to that of a
generic logical volume manager. Automatic StorageManagement (ASM) will
take physical disk partitions and manages their contents in a way that
efficiently supports the files needed to create an Oracle database.
Automatic Storage Management (ASM) simplifies administration
of Oracle related files by allowing the administrator to reference diskgroups
rather than hundreds of individual disks and files, which are managed
by ASM. The ASM functionality is an extension of the Oracle Managed Files (OMF)
functionality that also includes striping and mirroring to provide balanced and
secure storage. The ASM functionality can be used in combination with existing
raw and cooked file systems, along with OMF and manually managed files.
Before ASM, there were only two choices: file system storage and
raw disk storage. File system storage is flexible, allowing the DBA to see the individual files
and to move them, copy them, and back them up easily, but it also incurs
overhead. Raw disk storage has no file directories on it, and Oracle manages
its blocks directly, which makes it more efficient. Raw disk storage is such a
manageability nightmare that few DBAs use it.
ASM is the middle ground. It's raw disk storage managed by Oracle,
and it is very efficient. Oracle uses a scaled down Oracle instance to simulate
a file structure on it where none exists, by recording all the metadata. The
metadata enables the Recovery Manager
(RMAN) to backup and restore Oracle files easily within it.
Setting up storage takes a significant amount of time during most
database installations. Zeroing on a specific disk configuration from among the
multiple possibilities requires careful planning and analysis, and most
important, intimate knowledge of storage technology, volume managers, and file
systems. The design tasks at this stage can be loosely described as follows:
1.
Confirm that storage is recognized at the OS level and determine
the level of redundancy protection that might already be provided (hardware
RAID, called external redundancy in ASM).
2.
Assemble and build logical volume groups and determine if
striping or mirroring is also necessary.
3.
Build a file system on the logical volumes created by the logical
volume manager.
4.
Set the ownership and privileges so that the Oracle process can
open, read, and write to the devices.
5.
Create a database on that file system while taking care to create
special files such as redo logs, temporary
tablespaces, and undo tablespaces in
non-RAID locations, if possible.
All above tasks, striping, mirroring, logical file system building, are done to serve Oracle database. Oracle database offers some techniques of its own to simplify or enhance the process. Lets DBAs execute many of the above tasks completely within the Oracle framework. Using ASM you can transform a bunch of disks to a highly scalable and performance file system/volume manager using nothing more than what comes with Oracle database software at no extra cost and you don't need to be an expert in disk, volume managers, or file system management.
You can store the following file types in ASM diskgroups:
o
Datafiles
o
Control files
o
Online redo logs
o
Archive logs
o
Flashback logs
o
SPFILEs
o
RMAN backups
o
Temporary datafiles
o
Datafile copies
o
Disaster recovery configurations
o
Change tracking bitmaps
o
Datapump dumpsets
·
Download Pdf
·
Manages
·
How to download pdf
·
Las vegas hotel offers
·
Dictionarys
In summary, ASM provides the following functionality/features:
·
Manages groups of disks, called diskgroups. Must be careful while
choosing disks for a diskgroup.
·
Manages disk redundancy within a diskgroup.
·
Provides near-optimal I/O balancing without any manual tuning.
·
Enables management of database objects without specifying mount
points and filenames.
·
Supports large files.
·
Replacement for CFS (Cluster File System).
·
Also useful for Non-RAC databases.
·
A new instance type - ASM is introduced in 10g.
·
ASM instance has no data dictionary.
·
A Disk can be a partial, full or a LUN from the RG.
·
I/O is spread evenly across all disks of a diskgroup.
·
Disks can be dynamically added to any diskgroup.
·
When combined with OMF increases manageability.
·
ASM cannot maintain empty directories “delete input” has issues,
create a dummy directory.
·
Use of ASM diskgroup is very simple create tablespace.
·
Enterprise Manager can also be used for administering diskgroups
·
Only RMAN can be used with ASM.
·
Introduces three additional Oracle
background processes – RBAL, ARBx and ASMB.
o
ASMB - This ASMB process is used to provide information to and
from cluster synchronization services used by ASM to manage the disk resources.
It's also used to update statistics and
provide a heart beat mechanism.
o
Re-Balance, RBAL - RBAL is the ASM related process that performs
rebalancing of disk resources controlled by ASM.
o
Actual Rebalance, ARBx - ARBx is configured by ASM_POWER_LIMIT.
·
ASM instance has it own set of v$views and init.ora parameters.
The advantages of ASM are
§ Disk
Addition - Adding a disk is very easy. No downtime is required and file extents
are redistributed automatically.
§ I/O
Distribution - I/O is spread over all the available disks automatically,
without manual intervention, reducing chances of a hot spot.
§ Stripe
Width - Striping can be fine grained as in redolog files (128K for faster
transfer rate) and coarse for datafiles (1MB for transfer of a large number of
blocks at one time).
§ Mirroring
- Software mirroring can be set up easily, if hardware mirroring is not
available.
§ Buffering
- The ASM file system is not buffered, making it direct I/O capable by design.
§ Kernelized Asynchronous I/O
- There is no special setup necessary to enable kernelized asynchronous I/O,
without using raw or third-party file systems such as Veritas Quick I/O.
ASM
Instance
The ASM functionality is controlled by an ASM instance. This is a
special instance, not a database where users can create objects, just the
memory structures and as such is very small and lightweight.
With ASM, you don't have to create anything on the OS side; the
feature will group a set of physical disks to a logical entity known as a
diskgroup. A diskgroup is analogous to a striped and optionally mirrored, file
system, with important differences: it's not a general-purpose file system for
storing user files and it's not buffered. Diskgroup offers the advantage of
direct access to this space as a raw device, yet provides the convenience and
flexibility of a file system. All the metadata about the disks are stored in
the diskgroups themselves, making them as self-describing as possible.
This special ASM instance is similar to other file systems in that
it must be running for ASM to work and can't be modified by the user. One ASM
instance can serve number of Oracle databases. ASM instance and database
instances have to be present on same server. Otherwise it will not work.
Logical volume managers typically use a function, such as hashing,
to map the logical address of the blocks to the physical blocks. This
computation uses CPU cycles. When a new disk is added, this typical striping
function requires each bit of the entire data set to be relocated. In contrast,
ASM uses this special instance to address the mapping of the file extents to
the physical disk blocks. This design, in addition to being fast in locating
the file extents, helps while adding or removing disks because the locations of
file extents need not be coordinated.
You should start the instance up when the server is booted i.e. it
should be started before the database instances, and it should be one of the
last things stopped when the server is shutdown. From 11.2.0,
we can use ASMCMD to
start and stop the ASM instances.
The initialization parameters that are specific to an ASM instance
are:
·
INSTANCE_TYPE - Set to ASM. The default is RDBMS.
·
ASM_DISKGROUPS - The list of diskgroups that should be mounted by
an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT
statement. ASM configuration changes are automatically reflected in this
parameter.
·
ASM_DISKSTRING - Specifies a value that can be used to limit the
disks considered for discovery. The default value is NULL allowing all suitable
disks to be considered. Altering the default value may improve the speed of
diskgroup mount time and the speed of adding a disk to a diskgroup. Changing
the parameter to a value which prevents the discovery of already mounted disks
results in an error.
·
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on
an ASM instance. The valid values range from 1 (default) to 11. The higher the
limit the more resources are allocated resulting in faster rebalancing
operations. This value is also used as the default when the POWER clause is
omitted from a rebalance operation. A value of 0 disables rebalancing.
·
ASM_PREFERRED_READ_FAILURE_GROUPS - This initialization parameter
value (default is NULL) is a comma-delimited list of strings that specifies the
failure groups that should be preferentially read by the given instance. This
parameter is generally used only for clustered ASM instances and its value can
be different on different nodes. This is from Oracle 11g.
·
DB_UNIQUE_NAME - Specifies a globally unique name for the
database. This defaults to +ASM but must be altered if you intend to run
multiple ASM instances.
To create an ASM instance first create pfile, init+ASM.ora, in the
/tmp directory, containing the following parameter.
INSTANCE_TYPE = ASM
Next, connect to the ideal instance.
$ export ORACLE_SID=+ASM
SQL> sqlplus "/as sysdba"
Create a spfile using the contents of the init+ASM.ora file.
SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';
SQL> startup nomount
ASM instance started
Total System Global Area 130023424 bytes
Fixed Size 2028368 bytes
Variable Size 102829232 bytes
ASM Cache 25165824 bytes
The ASM instance is now ready to use for creating and mounting
diskgroups.
Once an ASM instance is present, diskgroups can be used for the
following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM
file creation:
·
CONTROL_FILES
·
DB_CREATE_FILE_DEST
·
DB_CREATE_ONLINE_LOG_DEST_n
·
DB_RECOVERY_FILE_DEST
·
LOG_ARCHIVE_DEST_n
·
LOG_ARCHIVE_DEST
·
STANDBY_ARCHIVE_DEST
Startup of ASM Instances
ASM instances are started and
stopped in a similar way to normal database instances.
·
Dictionary english to spanish
·
English dictionary online
·
Transfer
·
Followers
·
First mover advantage
The options for the STARTUP command are:
·
NOMOUNT - Starts the ASM instance without mounting any diskgroups.
·
MOUNT - Starts the ASM instance and mounts the diskgroups
specified by the ASM_DISKGROUPS parameter.
·
OPEN - ASM instance does not have open stage.
·
FORCE - Performs a SHUTDOWN ABORT before restarting the ASM
instance.
ASMCMD equivalent
for this command is startup (11g R2 command).
Shutdown of ASM Instances
The options for the SHUTDOWN command are:
Shutdown of ASM Instances
The options for the SHUTDOWN command are:
·
NORMAL - The ASM instance waits for all connected ASM instances
and SQL sessions to exit then shuts down.
·
IMMEDIATE - The ASM instance waits for any SQL transactions to
complete then shuts down. It doesn't wait for sessions to exit.
·
TRANSACTIONAL - Same as IMMEDIATE.
·
ABORT - The ASM instance shuts down instantly.
ASMCMD equivalent
for this command is shutdown (11g
R2 command).
ASM Diskgroups
ASM Diskgroups
The main components of ASM are diskgroups, each of which comprise
of several physical disks that are controlled as a single unit. The physical
disks are known as ASM disks, while the files that reside on the disks are
known as ASM files. The locations and names for the files are controlled by
ASM, but user-friendly aliases and directory structures can be defined for ease
of reference.
Diskgroup is a terminology used for logical structure which holds
the database files. Each diskgroup consists of disks/raw devices where the
files are actually stored. Any ASM file (and it's redundant copy) is completely
contained within a single diskgroup. A diskgroup might contain files belonging
to several databases and a single database can use files from multiple
diskgroups.
In the initial release of Oracle 10g,
ASM diskgroups were a black box. We had to manipulate ASM diskgroups with SQL
statements while logged in to the special ASM instance that manages the
diskgroups.
In Oracle 10g
Release 2, Oracle introduced a new command line tool called ASMCMD that
lets you look inside ASM volumes (which are called diskgroups). Now you can do
many tasks from the command line.
While creating a diskgroup, we have to specify an ASM diskgroup
type based on one of the following three redundancy levels:
·
Normal redundancy - for 2-way mirroring, requiring two failure
groups, when ASM allocates an extent for a normal redundancy file, ASM
allocates a primary copy and a secondary copy. ASM chooses the disk on which to
store the secondary copy in a different failure group other than the primary
copy.
·
High redundancy - for 3-way mirroring, requiring three failure
groups, in this case the extent is mirrored across 3 disks.
·
External redundancy - to not use ASM mirroring. This is used if
you are using hardware mirroring or third party redundancy mechanism like RAID,
Storage arrays.
ASM is supposed to stripe the data and also mirror the data (if
using Normal, High redundancy). So this can be used as an alternative for RAID
(Redundant Array of Inexpensive Disks) 0+1 solutions.
No, we cannot modify the redundancy for diskgroup once it has been
created. To alter it we will be required to create a new diskgroup and move the
files to it. This can also be done by restoring full backup on the new
diskgroup.
Failure groups are defined within a diskgroup to support the
required level of redundancy, using normal/high redundancy. They contain the
mirrored ASM extents and must be containing different disks and preferably on
separate disk controller.
In addition failure groups and preferred names for disks can be
defined in CREATE DISKGROUP statement. If the NAME clause is omitted the disks
are given a system generated name like "disk_group_1_0001". The FORCE
option can be used to move a disk from another diskgroup into this one.
Creating
diskgroups
SQL> CREATE DISKGROUP dg_asm_data NORMAL REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1, '/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1, '/devices/diskb2' NAME diskb2;
For two-way mirroring we would expect a diskgroup to contain two
failure groups, so individual files are written to two locations.
SQL> CREATE DISKGROUP dg_asm_fra HIGH REDUNDANCY
FAILGROUP failure_group_1 DISK
'/devices/diska1' NAME diska1, '/devices/diska2' NAME diska2,
FAILGROUP failure_group_2 DISK
'/devices/diskb1' NAME diskb1, '/devices/diskb2' NAME diskb2,
FAILGROUP failure_group_3 DISK
'/devices/diskc1' NAME diskc1, '/devices/diskc2' NAME diskc2;
For three-way mirroring we would expect a diskgroup to contain
three failure groups, so individual files are written to three locations.
SQL> CREATE DISKGROUP dg_grp1 EXTERNAL REDUNDANCY
DISK '/dev/d1','/dev/d2','/dev/d3','/dev/d4' ... ...;
In the above command, database will create a diskgroup named
dg_grp1 with the physical disks named /dev/d1, /dev/d2, and so on. Instead of
giving disks separately, we can also specify disk names in wildcards in the
DISK clause as DISK '/dev/d*'.
We have also specified a clause EXTERNAL REDUNDANCY, which
indicates that the failure of a disk will bring down the diskgroup. This is
usually the case when the redundancy is provided by the hardware, such as mirroring.
If there is no hardware based redundancy, the ASM can be set up to create a
special set of disks called failgroup in the diskgroup to provide that
redundancy.
SQL> CREATE DISKGROUP dskgrp1 NORMAL REDUNDANCY
FAILGROUP failgrp1 DISK '/dev/d1','/dev/d2',
FAILGROUP failgrp2 DISK '/dev/d3','/dev/d4';
Although it may appear as such, d3 and d4 are not mirrors of d1
and d2. Rather, ASM uses all the disks to create a fault-tolerant system. For
example, a file on the diskgroup might be created in d1 with a copy maintained
on d4. A second file may be created on d3 with copy on d2, and so on. That is,
primary copy will be on one failure group and secondary copy will be another
(third copy will be another, for high redundancy).
Failure of a specific disk allows a copy on another disk so that
the operation can continue. For example, you could lose the controller for both
disks d1 and d2 and ASM would mirror copies of the extents across the failure
group to maintain data integrity.
SQL> CREATE DISKGROUP dg1 DISK '/dev/raw/*'
ATTRIBUTE 'compatible.rdbms' = '11.1', 'compatible.asm' = '11.1';
(11g R1 command)
SQL> CREATE DISKGROUP dg2 EXTERNAL REDUNDANCY
DISK '/dev/sde1' ATRRIBUTE 'au_size' = '32M'; (11g R1 command)
SQL> CREATE DISKGROUP archdg NORMAL REDUNDANCY
FAILGROUP fg1 DISK
'/devices/diska1','/devices/diska2','/devices/diska3','/devices/diska4'
FAILGROUP fg2 DISK
'/devices/diskb1','/devices/diskb2','/devices/diskb3','/devices/diskb4'
ATTRIBUTE 'au_size'='4M','compatible.asm'='11.2','compatible.rdbms'='11.2','compatible.advm'='11.2'; (11g R2command)
SQL> CREATE DISKGROUP archdg NORMAL REDUNDANCY
FAILGROUP fg1 DISK
'/devices/diska1','/devices/diska2','/devices/diska3','/devices/diska4'
FAILGROUP fg2 DISK
'/devices/diskb1','/devices/diskb2','/devices/diskb3','/devices/diskb4'
ATTRIBUTE 'au_size'='4M','compatible.asm'='11.2','compatible.rdbms'='11.2','compatible.advm'='11.2'; (11g R2command)
ASMCMD equivalent
for this command is mkdg (11g R2 command).
Listing diskgroups
Listing diskgroups
To find out all the diskgroups:
SQL> SELECT * FROM V$ASM_DISKGROUP;
ASMCMD equivalent
for this command is lsdg.
Dropping
diskgroups
Diskgroups can be deleted using the DROP DISKGROUP statement.
SQL> DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;
SQL> DROP DISKGROUP disk_group_1 FORCE; (11g R1 command)
SQL> DROP DISKGROUP disk_group_1 FORCE INCLUDING CONTENTS; (11gR1 command)
ASMCMD equivalent
for this command is dropdg (11g R2 command).
Altering
diskgroups
Disks can be added or removed from diskgroups using the ALTER
DISKGROUP statement. Remember that the wildcard "*" can be used to
reference disks so long as the resulting string does not match a disk already
used by an existing diskgroup.
Adding disks
We may have to add additional disks into the diskgroup to
accommodate growing demand.
SQL> ALTER DISKGROUP dskgrp1 ADD DISK '/dev/d5';
SQL> ALTER DISKGROUP dg1 ADD DISK '/devices/disk*3',
'/devices/disk*4';
ASMCMD equivalent
for this command is chdg (11g R2 command).
Listing disks
Listing disks
The following command shows all the disks managed by the ASM
instance for all the client databases.
SQL> SELECT * FROM V$ASM_DISK;
ASMCMD equivalent
for this command is lsdsk (11g R1 command).
Listing
client databases
The following command shows all the database instances connected
to the ASM instance.
SQL> SELECT * FROM V$ASM_CLIENT;
ASMCMD equivalent
for this command is lsct.
Dropping disks
We can remove a disk from diskgroup.
SQL> ALTER DISKGROUP dg4 DROP DISK diska4;
ASMCMD equivalent for this command is chdg (11g R2 command).
ASMCMD equivalent for this command is chdg (11g R2 command).
Resizing disks
Disks can be resized using the RESIZE clause of the ALTER
DISKGROUP statement. The statement can be used to resize individual disks, all
disks in a failure group or all disks in the diskgroup. If the SIZE clause is
omitted the disks are resized to the size of the disk returned by the OS.
SQL> ALTER DISKGROUP dg_data_1 RESIZE DISK diska1 SIZE 150G;
Resizing all disks in a failure group
SQL> ALTER DISKGROUP dg_data_1 RESIZE DISKS IN FAILGROUP fg_1
SIZE 50G;
Resizing all disks in a diskgroup
SQL> ALTER DISKGROUP dg_data_1 RESIZE ALL SIZE 100G;
Undropping disks
The UNDROP DISKS clause of the ALTER DISKGROUP statement allows
pending disk drops to be undone. It will not revert drops that have completed,
or disk drops associated with the dropping of a diskgroup.
SQL> ALTER DISKGROUP disk_group_1 UNDROP DISKS;
Online disks
SQL> ALTER DISKGROUP data ONLINE DISK 'disk_0000', 'disk_0001';
SQL> ALTER DISKGROUP data ONLINE DISKS IN FAILGROUP 'fg_99';
SQL> ALTER DISKGROUP data ONLINE ALL;
ASMCMD equivalent for this command is online (11gR2 command).
Offline disks
ASMCMD equivalent for this command is online (11gR2 command).
Offline disks
SQL> ALTER DISKGROUP data OFFLINE DISK 'disk_0000',
'disk_0001';
SQL> ALTER DISKGROUP data OFFLINE DISKS IN FAILGROUP 'fg_99';
SQL> ALTER DISKGROUP data OFFLINE DISK d1_0001 DROP AFTER 30m;
ASMCMD equivalent
for this command is offline (11gR2 command).
Mounting diskgroups
Diskgroups are mounted at ASM instance startup and unmounted at
ASM instance shutdown. Manual mounting and dismounting can be accomplished
using the ALTER DISKGROUP statement as below.
SQL> ALTER DISKGROUP ALL MOUNT;
SQL> ALTER DISKGROUP dg_data2 MOUNT;
SQL> ALTER DISKGROUP dg_data2 MOUNT RESTRICTED; (11gR1 command)
ASMCMD equivalent
for this command is mount (11gR2 command).
Dismounting diskgroups
Dismounting diskgroups
SQL> ALTER DISKGROUP ALL DISMOUNT;
SQL> ALTER DISKGROUP dg_fra DISMOUNT;
ASMCMD equivalent
for this command is umount (11gR2 command).
Changing
attributes
SQL> ALTER DISKGROUP data3 SET ATTRIBUTE 'compatible.rdbms' =
'11.1'; (11gR1 command)
SQL> ALTER DISKGROUP data3 SET ATTRIBUTE 'compatible.asm' =
'11.2';
(11gR1 command)
SQL> ALTER DISKGROUP data3 SET ATTRIBUTE 'disk_repair_time' =
'4.5h'; (11gR1 command)
ASMCMD equivalent for this command is setattr (11gR2 command).
Listing attributes
SQL> SELECT * FROM V$ASM_ATTRIBUTE;
ASMCMD equivalent for this command is lsattr (11gR2 command).
ASMCMD equivalent for this command is setattr (11gR2 command).
Listing attributes
SQL> SELECT * FROM V$ASM_ATTRIBUTE;
ASMCMD equivalent for this command is lsattr (11gR2 command).
Rebalancing
Diskgroups can be rebalanced manually using the REBALANCE clause
of the ALTER DISKGROUP statement. If the POWER clause is omitted the
ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the
speed of the automatic rebalancing is not appropriate.
SQL> ALTER DISKGROUP disk_group_1 REBALANCE POWER 6;
ASMCMD equivalent for this command is rebal (11gR2 command).
IO statistics of a diskgroup
SQL> SELECT * FROM V$ASM_DISK_IOSTAT;
ASMCMD equivalent for this command is rebal (11gR2 command).
IO statistics of a diskgroup
SQL> SELECT * FROM V$ASM_DISK_IOSTAT;
ASMCMD equivalent
for this command is iostat (11gR2 command).
Until 11.1.0, all the above commands can not be performed with ASMCMD.
From 11.2.0,
we can.
Directories
As in other file systems, an ASM directory is a container for
files, and an ASM directory can be part of a tree structure of other
directories. The fully qualified filename represents a hierarchy of directories
in which the plus sign (+) represent the root directory. In each diskgroup, ASM
automatically creates a directory hierarchy that corresponds to the structure
of the fully qualified filenames in the diskgroup. The directories in this
hierarchy are known as system-generated directories.
An absolute path refers to the full path of a file or directory.
An absolute path begins with a plus sign (+) followed by a diskgroup name,
followed by subsequent directories in the directory tree. The absolute path
includes directories until the file or directory is reached. A fully qualified
filename is an example of an absolute path to a file. A relative path includes
only the part of the filename or directory name that is not part of the current
directory. That is, the path to the file or directory is relative to the
current directory.
A directory hierarchy can be defined using the ALTER DISKGROUP
statement to support ASM file aliasing.
Creating
a directory
SQL> ALTER DISKGROUP dg_1 ADD DIRECTORY '+dg_1/my_dir';
ASMCMD equivalent
for this command is mkdir.
Renaming
a directory
SQL> ALTER DISKGROUP dg_1 RENAME DIRECTORY '+dg_1/my_dir' TO
'+dg_1/my_dir_2';
Deleting
a directory
SQL> ALTER DISKGROUP dg_1 DROP DIRECTORY '+dg_1/my_dir_2'
FORCE;
ASMCMD equivalent
for this command is rm.
Files
There are several ways to reference ASM files. Some forms are used
during creation and some for referencing ASM files. Every file created in ASM
gets a system-generated filename, known as fully qualified filename, this is
same as complete path name in a local file system.
The forms of the ASM filenames are:
|
Filename Type
|
Format
|
|
Fully
Qualified ASM Filename
|
+dgroup/dbname/file_type/
file_type_tag.file.incarnation
|
|
Numeric
ASM Filename
|
+dgroup.file.incarnation
|
|
Alias
ASM Filenames
|
+dgroup/directory/filename
|
|
Alias
ASM Filename with Template
|
+dgroup(template)/alias
|
|
Incomplete
ASM Filename
|
+dgroup
|
|
Incomplete
ASM Filename with Template
|
+dgroup(template)
|
ASM generates filenames according to the following scheme:
+diskGroupName/databaseName/fileType/fileTypeTag.fileNumber.incarnation
e.g: +dgroup2/crm/CONTROLFILE/Current.256.541956473
+dg_fra/hrms/DATAFILE/users.309.621906475
ASM does not place system-generated files into user-created
directories; it places them only in system-generated directories. We can add
aliases or other directories to a user-created directory.
Dropping Files
Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.
Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.
Dropping file using an alias
SQL> ALTER DISKGROUP dg_2 DROP FILE '+dg_2/my_dir/my_file.dbf';
Dropping file using a numeric form filename
SQL> ALTER DISKGROUP dg_2 DROP FILE '+dg_2.321.123456789';
Dropping file using a fully qualified filename
SQL> ALTER DISKGROUP dg_2 DROP FILE
'+dg_2/mydb/datafile/my_ts.292.265390671';
ASMCMD equivalent
for this command is rm.
Aliases
Aliases allow you to reference ASM files using user-friendly
names, rather than the fully qualified ASM filenames.
Creating an alias
Creating an alias, using the fully qualified filename
SQL> ALTER DISKGROUP dg_3 ADD ALIAS '+dg_3/my_dir/users.dbf'
FOR '+dg_3/mydb/datafile/users.392.333222555';
Creating an alias, using the numeric form filename
SQL> ALTER DISKGROUP dg_3 ADD ALIAS '+dg_3/my_dir/my_file.dbf'
FOR '+dg_3.317.111222333';
ASMCMD equivalent
for this command is mkalias.
Renaming an alias
SQL> ALTER DISKGROUP dg_3 RENAME ALIAS
'+dg_3/my_dir/my_file.dbf' TO '+dg_3/my_dir/my_file2.dbf';
Deleting an alias
SQL> ALTER DISKGROUP dg_3 DELETE ALIAS
'+dg_3/my_dir/my_file.dbf';
ASMCMD equivalent
for this command is rmalias.
Attempting to drop a system alias results in an error.
Templates
Templates are named groups of attributes that can be applied to
the files within a diskgroup. The level of redundancy and the granularity of
the striping can be controlled using templates. Default templates are provided
for each file type stored by ASM, but additional templates can be defined as
needed.
Available attributes are:
·
UNPROTECTED - No mirroring or striping regardless of the
redundancy setting.
·
MIRROR - Two-way mirroring for normal redundancy and three-way
mirroring for high redundancy.
·
COARSE - Specifies lower granularity for striping.
·
FINE - Specifies higher granularity for striping.
MIRROR, COARSE, FINE attributes are cannot be set for external
redundancy.
Creating a template
SQL> ALTER DISKGROUP dg_4 ADD TEMPLATE mf_template ATTRIBUTES
(MIRROR FINE);
ASMCMD equivalent
for this command is mktmpl (11gR2 command).
Modifying
a template
SQL> ALTER DISKGROUP dg_4 ALTER TEMPLATE c_template ATTRIBUTES
(COARSE);
ASMCMD equivalent
for this command is chtmpl (11gR2 command).
Listing templates
SQL> SELECT * FROM V$ASM_TEMPLATE;
ASMCMD equivalent for this command is lstmpl (11gR2 command).
Listing templates
SQL> SELECT * FROM V$ASM_TEMPLATE;
ASMCMD equivalent for this command is lstmpl (11gR2 command).
Dropping
a template
SQL> ALTER DISKGROUP dg_4 DROP TEMPLATE u_template;
ASMCMD equivalent
for this command is rmtmpl (11gR2 command).
Checking Metadata
The internal consistency of diskgroup metadata can be checked in a
number of ways using the CHECK clause of the ALTER DISKGROUP statement.
Checking metadata for a specific file
SQL> ALTER DISKGROUP dg_5 CHECK FILE '+dg_5/my_dir/my_file.dbf'
Checking metadata for a specific disk in the diskgroup
SQL> ALTER DISKGROUP dg_5 CHECK DISK diska1;
Checking metadata for a specific failure group in the diskgroup
SQL> ALTER DISKGROUP dg_5 CHECK FAILGROUP failure_group_1;
Checking metadata for all disks in the diskgroup
SQL> ALTER DISKGROUP dg_5 CHECK ALL;
SQL> ALTER DISKGROUP dg_5 CHECK;
SQL> ALTER DISKGROUP dg_5 CHECK NOREPAIR;
SQL> ALTER DISKGROUP dg_5 CHECK REPAIR;
ASMCMD equivalent
for this command is chkdg (11gR2 command).
User Management
From Oracle 11g release 2, we can create ASM users and usergroups and manipulate the permissions and ownership of files.
Creating an ASM usergroup
SQL> ALTER DISKGROUP data_dg ADD USERGROUP 'grp1';
SQL> ALTER DISKGROUP data_dg ADD USERGROUP 'grp2' WITH MEMBER 'oracle1','oracle2';
ASMCMD equivalent for this command is mkgrp (11gR2 command).
Listing ASM usergroups
To find out the list of ASM usergroups.
SQL> SELECT * FROM V$ASM_USERGROUP;
ASMCMD equivalent for this command is lsgrp (11gR2 command).
Dropping an ASM usergroup
SQL> ALTER DISKGROUP data_dg DROP USERGROUP 'grp1';
ASMCMD equivalent for this command is rmgrp (11gR2 command).
Modifying(adding/deleting ASM users to/from) an ASM usergroup
SQL> ALTER DISKGROUP data_dg MODIFY USERGROUP 'grp2' ADD MEMBER 'oracle3';
SQL> ALTER DISKGROUP data_dg MODIFY USERGROUP 'grp2' DROP MEMBER 'oracle3';
ASMCMD equivalent for this command is grpmod (11gR2 command).
Creating an ASM user
SQL> ALTER DISKGROUP data_dg ADD USER 'oracle1';
ASMCMD equivalent for this command is mkusr (11gR2 command).
Listing ASM users
To find out the list of ASM users.
SQL> SELECT * FROM V$ASM_USER;
ASMCMD equivalent for this command is lsusr (11gR2 command).
Listing ASM usergroups to which user belongs
SQL> SELECT * FROM V$ASM_USERGROUP_MEMBER;
ASMCMD equivalent for this command is groups (11gR2 command).
Dropping an ASM user
SQL> ALTER DISKGROUP data_dg DROP USER 'oracle1';
ASMCMD equivalent for this command is rmusr (11gR2 command).
Modifying permissions for a file
SQL> ALTER DISKGROUP data_dg SET PERMISSION OWNER=read write, GROUP=read only, OTHER=none FOR FILE '+data_dg/controlfile.f';
ASMCMD equivalent for this command is chmod (11gR2 command).
Modifying ownership of a file
SQL> ALTER DISKGROUP data_dg SET OWNERSHIP OWNER='oracle1', GROUP='grp1' FOR FILE '+data_dg/controlfile.f';
ASMCMD equivalent for this command is chown (11gR2 command).
Volume Management
From 11g release 2, we can create Oracle ASM Dynamic Volume Manager (Oracle ADVM) volumes in a diskgroups. The volume device associated with the dynamic volume can then be used to host an (Oracle ACFS) file system.
Creating a volume
SQL> ALTER DISKGROUP data_dg ADD VOLUME volume1 SIZE 20G;
ASMCMD equivalent for this command is volcreate (11gR2 command).
Listing volume information
To find out the volumes information.
SQL> SELECT * FROM V$ASM_VOLUME;
ASMCMD equivalent for this command is volinfo (11gR2 command).
Listing volume statistics
To find out the volumes statistics information.
SQL> SELECT * FROM V$ASM_VOLUME_STAT;
ASMCMD equivalent for this command is volstat (11gR2 command).
Dropping a volume
SQL> ALTER DISKGROUP data_dg DROP VOLUME volume1;
ASMCMD equivalent for this command is voldelete (11gR2 command).
Resizing a volume
SQL> ALTER DISKGROUP fra_dg RESIZE VOLUME volume1 SIZE 25G;
ASMCMD equivalent for this command is volresize (11gR2 command).
Disabling a volume
SQL> ALTER DISKGROUP redo_dg DISABLE VOLUME volume1;
SQL> ALTER DISKGROUP ALL DISABLE VOLUME ALL;
ASMCMD equivalent for this command is voldisable (11gR2 command).
Enabling a volume
SQL> ALTER DISKGROUP arch_dg ENABLE VOLUME volume1;
ASMCMD equivalent for this command is volenable (11gR2 command).
Setting a volume
SQL> ALTER DISKGROUP asm_dg_data MODIFY VOLUME volume1 USAGE 'acfs';
ASMCMD equivalent for this command is volset (11gR2 command).
Misc
Listing the current operations
SQL> SELECT * FROM V$ASM_OPERATION;
ASMCMD equivalent for this command is lsop (11gR2 command).
User Management
From Oracle 11g release 2, we can create ASM users and usergroups and manipulate the permissions and ownership of files.
Creating an ASM usergroup
SQL> ALTER DISKGROUP data_dg ADD USERGROUP 'grp1';
SQL> ALTER DISKGROUP data_dg ADD USERGROUP 'grp2' WITH MEMBER 'oracle1','oracle2';
ASMCMD equivalent for this command is mkgrp (11gR2 command).
Listing ASM usergroups
To find out the list of ASM usergroups.
SQL> SELECT * FROM V$ASM_USERGROUP;
ASMCMD equivalent for this command is lsgrp (11gR2 command).
Dropping an ASM usergroup
SQL> ALTER DISKGROUP data_dg DROP USERGROUP 'grp1';
ASMCMD equivalent for this command is rmgrp (11gR2 command).
Modifying(adding/deleting ASM users to/from) an ASM usergroup
SQL> ALTER DISKGROUP data_dg MODIFY USERGROUP 'grp2' ADD MEMBER 'oracle3';
SQL> ALTER DISKGROUP data_dg MODIFY USERGROUP 'grp2' DROP MEMBER 'oracle3';
ASMCMD equivalent for this command is grpmod (11gR2 command).
Creating an ASM user
SQL> ALTER DISKGROUP data_dg ADD USER 'oracle1';
ASMCMD equivalent for this command is mkusr (11gR2 command).
Listing ASM users
To find out the list of ASM users.
SQL> SELECT * FROM V$ASM_USER;
ASMCMD equivalent for this command is lsusr (11gR2 command).
Listing ASM usergroups to which user belongs
SQL> SELECT * FROM V$ASM_USERGROUP_MEMBER;
ASMCMD equivalent for this command is groups (11gR2 command).
Dropping an ASM user
SQL> ALTER DISKGROUP data_dg DROP USER 'oracle1';
ASMCMD equivalent for this command is rmusr (11gR2 command).
Modifying permissions for a file
SQL> ALTER DISKGROUP data_dg SET PERMISSION OWNER=read write, GROUP=read only, OTHER=none FOR FILE '+data_dg/controlfile.f';
ASMCMD equivalent for this command is chmod (11gR2 command).
Modifying ownership of a file
SQL> ALTER DISKGROUP data_dg SET OWNERSHIP OWNER='oracle1', GROUP='grp1' FOR FILE '+data_dg/controlfile.f';
ASMCMD equivalent for this command is chown (11gR2 command).
Volume Management
From 11g release 2, we can create Oracle ASM Dynamic Volume Manager (Oracle ADVM) volumes in a diskgroups. The volume device associated with the dynamic volume can then be used to host an (Oracle ACFS) file system.
Creating a volume
SQL> ALTER DISKGROUP data_dg ADD VOLUME volume1 SIZE 20G;
ASMCMD equivalent for this command is volcreate (11gR2 command).
Listing volume information
To find out the volumes information.
SQL> SELECT * FROM V$ASM_VOLUME;
ASMCMD equivalent for this command is volinfo (11gR2 command).
Listing volume statistics
To find out the volumes statistics information.
SQL> SELECT * FROM V$ASM_VOLUME_STAT;
ASMCMD equivalent for this command is volstat (11gR2 command).
Dropping a volume
SQL> ALTER DISKGROUP data_dg DROP VOLUME volume1;
ASMCMD equivalent for this command is voldelete (11gR2 command).
Resizing a volume
SQL> ALTER DISKGROUP fra_dg RESIZE VOLUME volume1 SIZE 25G;
ASMCMD equivalent for this command is volresize (11gR2 command).
Disabling a volume
SQL> ALTER DISKGROUP redo_dg DISABLE VOLUME volume1;
SQL> ALTER DISKGROUP ALL DISABLE VOLUME ALL;
ASMCMD equivalent for this command is voldisable (11gR2 command).
Enabling a volume
SQL> ALTER DISKGROUP arch_dg ENABLE VOLUME volume1;
ASMCMD equivalent for this command is volenable (11gR2 command).
Setting a volume
SQL> ALTER DISKGROUP asm_dg_data MODIFY VOLUME volume1 USAGE 'acfs';
ASMCMD equivalent for this command is volset (11gR2 command).
Misc
Listing the current operations
SQL> SELECT * FROM V$ASM_OPERATION;
ASMCMD equivalent for this command is lsop (11gR2 command).
Creating Tablespaces
Now create a tablespace in the main database using a datafile in
the ASM-enabled storage.
SQL> CREATE TABLESPACE user_data DATAFILE
'+dskgrp1/user_data_01'
SIZE 1024M;
ASM filenames can be used in place of conventional filenames for
most Oracle file types, including controlfiles, datafiles, logfiles etc. For
example, the following command creates a new tablespace with a datafile in the
disk_group_1 diskgroup.
SQL> CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M
AUTOEXTEND ON;
Note how the diskgroup is used as a virtual file system. This
approach is useful not only in datafiles, but in other types of Oracle files as
well. For instance, we can create online redo log files as
...
LOGFILE GROUP 1 (
'+dskgrp1/redo/group_1.258.659723485',
'+dskgrp2/redo/group_1.258.659723485'
) SIZE 50M,
...
Archived log destinations can also be set to a diskgroup.
Everything related to Oracle database can be created in an ASM diskgroup.
Backup is another great use of ASM. You can set up a bunch of inexpensive disks
to create the recovery area of
a database, which can be used by RMAN to
create backup datafiles and archived log files.
ASM supports files created by and read by the Oracle database
only; it is not a replacement for a general-purpose file system.
Until Oracle 11g
release1, we cannot store binaries or flat files. We cannot use ASM
for storing the voting disk and OCR. It is due to the fact that Clusterware
starts before ASM instance and it should be able to access these files which
are not possible if you are storing it on ASM. You will have to use raw devices
or OCFS or any other shared storage. But from 11g release 2,
we can store ALL files on ASM.
Can we see the files stored in the ASM instance using standard
Unix commands?
No, you cannot see the files using standard Unix commands like ls.
You need to use utility called asmcmd to do this.Oracle 10g
release2 introduces asmcmd which
makes administration very easy.
$ asmcmd
ASMCMD>
ASMLIB is the support library for the ASM. ASMLIB allows an Oracle
database using ASM more efficient and capable access to diskgroups. The purpose
of ASMLIB, is to provide an alternative interface to identify and access block
devices. ASMLIB API enables storage and OS vendors to supply extended
storage-related features.
Migrating to ASM using RMAN
The following method shows how a database can be migrated to ASM
from a disk based backup:
1) Shutdown the database.
SQL> SHUTDOWN IMMEDIATE
2) Modify the parameter file of the database as follows:
Set DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters
to the relevant ASM diskgroups.
3) Remove CONTROL_FILES parameter from the spfile so the control
files will be moved to the DB_CREATE_* destination and the spfile gets updated
automatically. If you are using a pfile the CONTROL_FILES parameter must be set
to the appropriate ASM files or aliases.
4) Start the database in nomount mode.
RMAN> STARTUP NOMOUNT
5) Restore the controlfile into the new location from the old
location.
RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
6) Mount the database.
RMAN> ALTER DATABASE MOUNT;
7) Copy the database into the ASM diskgroup.
RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
8) Switch all datafile to the new ASM location.
RMAN> SWITCH DATABASE TO COPY;
9) Open the database.
RMAN> ALTER DATABASE OPEN;
10) Create new redo logs in ASM and delete the old ones.
ASM New features in Oracle 11g
release1
·
Support for rolling upgrades.
·
We can maintain version compatibilites at diskgroup level.
SQL> alter diskgroup dg-name set attribute 'compatible.rdbms'='11.1';
SQL> alter diskgroup dg-name set attribute 'compatible.asm'='11.1';
SQL> alter diskgroup dg-name set attribute 'compatible.rdbms'='11.1';
SQL> alter diskgroup dg-name set attribute 'compatible.asm'='11.1';
·
ASM drops disks and if they remain offline for more than 3.6
hours. The diskgroups default time limit is altered by changing the
DISK_REPAIR_TIME parameter with a unit of minutes(M/m) or hours(H/h).
SQL> alter diskgroup dg-name set attribute 'disk_repair_time'='4.5h';
SQL> alter diskgroup dg-name set attribute 'disk_repair_time'='4.5h';
·
Automatic bad block detection and repair.
·
Supports variable extent(allocation unit) sizes. The total number
of extents in shared pool will be significantly reduced and improved
performance.
SQL> create diskgroup ... attribute 'au_size' = 'number-of-bytes';
SQL> create diskgroup ... attribute 'au_size' = 'number-of-bytes';
·
New SYSASM role (like SYSDBA, SYSOPER) & OSASM OS group (like
OSDBA, OSOPER) to manage ASM instance only. This will separate storage
administration from database administration.
$ sqlplus "/as sysasm" or $ asmcmd -a sysasm
$ sqlplus "/as sysasm" or $ asmcmd -a sysasm
·
ASM Preferred Mirror Read or Preferred Read Failure Groups -
ASM_PREFERRED_READ_FAILURE_GROUPS parameter is set to the preferred failure
groups for each node.
·
Faster Mirror Resync - Fast mirror resync after temporary
connectivity lost.
·
We can drop a diskgroup forcefully.
SQL> drop diskgroup dg-name force including contents;
SQL> drop diskgroup dg-name force including contents;
·
Can mount the disk in restricted mode, to rebalance faster.
SQL> alter diskgroup dg-name mount restricted;
SQL> alter diskgroup dg-name mount restricted;
·
New commands in ASMCMD.
o
cp -
to copy between ASM and local or remote destination.
o
md_backup -
to backup metadata.
o
md_restore -
to restore metadata.
o
lsdsk -
to list(check) disks.
o
remap -
to repair a range of physical blocks on disk.
ASM New features in Oracle 11g
release2
·
ASM Configuration Assistant (ASMCA) is a new tool to install and
configure ASM.
·
ASM Cluster File System (ACFS) provides support for files such as
Oracle binaries, Clusterware binaries, report files, trace files, alert logs,
external files, and other application datafiles. ACFS can be managed by
ACFSUTIL, ASMCMD,
OEM, ASMCA, SQL command interface.
·
ASM Dynamic Volume Manager (ADVM) provides volume management
services and a standard device driver interface to its clients (ACFS, ext3,
OCFS2 and third party files systems).
·
ACFS Snapshots are read-only on-line, space efficient, point in
time copy of an ACFS file system. ACFS snapshots can be used to recover from
inadvertent modification or deletion of files from a file system.
·
ASM can hold and manage OCR (Oracle Cluster Registry) file and
voting file.
·
ASM diskgroups can be renamed, by using renamedg command.
·
ASMCMD utility can do
o
startup and
shutdown of ASM instances.
o
Managing diskgroups (create, mount, alter, drop).
o
File access control (like OS, ugo and rwx ...).
o
User management.
o
Template management.
o
Volume management.
o
We can execute OS commands at asmcmd by using !, in the same
we do at SQL prompt.
ASM New features in Oracle Clusterware 12c
1. Oracle Flex ASM - This feature of Oracle Clusterware 12c claims to reduce per-node overhead of using ASM instance. Now the instances can use remote node ASM for any planned/unplanned downtime. ASM metadata requests can be converted by non-local instance of ASM.
2. ASM Disk Scrubbing - From RAC 12c, ASM comes with disk scrubbing feature so that logical corruptions can be discovered. Also Oracle 12c ASM can automatically correct this in normal or high redundancy diskgroups.
3. Oracle ASM Disk Resync & Rebalance enhancements.
ASM Views
1. Oracle Flex ASM - This feature of Oracle Clusterware 12c claims to reduce per-node overhead of using ASM instance. Now the instances can use remote node ASM for any planned/unplanned downtime. ASM metadata requests can be converted by non-local instance of ASM.
2. ASM Disk Scrubbing - From RAC 12c, ASM comes with disk scrubbing feature so that logical corruptions can be discovered. Also Oracle 12c ASM can automatically correct this in normal or high redundancy diskgroups.
3. Oracle ASM Disk Resync & Rebalance enhancements.
ASM Views
The ASM configuration can be viewed using the V$ASM_% views, which
contain information depending on whether they are queried from the ASM
instance, or a dependant database instance.
|
View
|
In ASM instance
|
In DB instance
|
|
V$ASM_ALIAS
|
Displays
a row for each alias present in every diskgroup mounted by the ASM instance.
|
Returns
no rows.
|
|
V$ASM_ATTRIBUTE
(11gR2)
|
Displays
attributes of diskgroups.
|
Displays
attributes of diskgroups.
|
|
V$ASM_CLIENT
|
Displays
a row for each database instance using a diskgroup managed by the ASM
instance.
|
Displays
a row for the ASM instance if the database has open ASM files.
|
|
V$ASM_DISK
or V$ASM_DISK_STAT
|
Displays
a row for each disk discovered by the ASM instance, including disks which are
not part of any diskgroup.
|
Displays
a row for each disk in diskgroups in use by the database instance.
|
|
V$ASM_DISK_IOSTAT(11gR2)
|
Displays
IO statistics of
disks.
|
Displays
IO statistics of disks.
|
|
V$ASM_DISKGROUP
or V$ASM_DISKGROUP_STAT
|
Displays
a row for each diskgroup discovered by the ASM instance.
|
Displays
a row for each diskgroup mounted by the local ASM instance.
|
|
V$ASM_FILE
|
Displays
a row for each file for each diskgroup mounted by the ASM instance.
|
Displays
no rows.
|
|
V$ASM_FILESYSTEM (11gR2)
|
Displays
a row for each filesystem for each diskgroup mounted by the ASM instance.
|
Displays
no rows.
|
|
V$ASM_OPERATION
|
Displays
a row for each file for each long running operation executing in the ASM
instance.
|
Displays
no rows.
|
|
V$ASM_TEMPLATE
|
Displays
a row for each template present in each diskgroup mounted by the ASM
instance.
|
Displays
a row for each template present in each diskgroup mounted by the ASM
instance.
|
|
V$ASM_USER (11gR2)
|
Displays
a row for each ASM user.
|
-
|
|
V$ASM_USERGROUP (11gR2)
|
Displays
a row for each ASM usergroup.
|
-
|
|
V$ASM_USERGROUP_MEMBER(11gR2)
|
Displays
ASM usergroups and it's members.
|
-
|
|
V$ASM_VOLUME
orV$ASM_VOLUME_STAT(11gR2)
|
Displays
a row for each volume.
|
-
|
ASM backup can be taken by spooling the output of the ASM views to text file.
SPOOL asm_views.log
SET ECHO ON
SELECT * FROM V$ASM_ALIAS;
SELECT * FROM V$ASM_ATTRIBUTE;
SELECT * FROM V$ASM_ATTRIBUTE;
SELECT * FROM V$ASM_CLIENT;
SELECT * FROM V$ASM_DISK;
SELECT * FROM V$ASM_DISK_IOSTAT;SELECT * FROM
V$ASM_DISK_STAT;
SELECT * FROM V$ASM_DISKGROUP;
SELECT * FROM V$ASM_DISKGROUP_STAT;
SELECT * FROM V$ASM_FILE;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_FILESYSTEM;
SELECT * FROM V$ASM_OPERATION;
SELECT * FROM V$ASM_TEMPLATE;
SELECT * FROM V$ASM_USER;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;
SELECT * FROM V$ASM_USERGROUP;
SELECT * FROM V$ASM_USERGROUP_MEMBER;
SELECT * FROM V$ASM_VOLUME;
SELECT * FROM V$ASM_VOLUME_STAT;
SPOOL OFF
Subscribe to:
Posts
(
Atom
)