Oracle Dba Interview Questions 4m721r

FORCE INCLUDING CONTENTS;

ASM Rolling Upgrades ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition An Active Data Guard standby database is an exact copy of the primary that is open readonly while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database. With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently s all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups. Active Dataguard Features: 1. Physical Standby with Real-time Query 2. Fast Incremental Backup on Physical Standby. 3. Automatic Block Repair.

http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf http://pocketoracle.blogspot.com/search/label/10g%20New%20Features http://pocketoracle.blogspot.com/search/label/11g%20New%20Features

Posted by Dharmendra Chalasani at Wednesday, September 11, 2013 Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: Oracle DBA Interview Questions and Answers

July 24, 2013

Oracle DBA Interview Questions and Answers - ASM Oracle ASM Interview Questions and Answers What is ASM in Oracle? Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2. ASM offers for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or filesystems. ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run. ASM simplifies istration of Oracle related files by allowing the to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

Advantages of ASM in Oracle? Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage Uses redundancy features available in intelligent storage arrays The storage system can store all types of database files Using disk group makes configuration easier, as files are placed into disk groups ASM provides stripping and mirroring (fine and coarse gain - see below)

ASM and non-ASM oracle files can coexist Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning. For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance. Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group. ASM s 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies. Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group. Managed file creation and deletion—ASM further reduces istration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed. What is ASM instance in Oracle? The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight. Characteristics of Oracle ASM instance -------------------------------------1. do not have controlfile and datafiles, do not have online redo logs 2. do have init.ora and a file 3. for connecting remotely, create file and set following in init.ora remote__file=exclusive create a file: $ORACLE_HOME/bin/orapwd file=orapw+ASM1 =yourpw entries=10 4. ASM instance can not be in open status as there are not datafiles. Can be in mount (although there is no controlfile) and nomount status. When in mount status, database can use the diskgroup. The mount status actually means mount disk groups.

What are ASM Background Processes in Oracle?Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances. There are at least two new background processes added for an ASM instance: ASM Instance Background Processes: --------------------------------ARBx (ASM) Rebalance working processARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter. RBAL (Re-balancer) RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities for disk resources controlled by ASM. Database Instance ASM Background Processes: ------------------------------------------In the database instances, there are three background process to ASM, namely:

ASMB, this process CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance. RBAL, which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time. O00x, a group slave processes, with a numeric sequence starting at 000. What are the components of components of ASM are disk groups? The main components of ASM are disk groups, 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 know as ASM files. The locations and names for the files are controlled by ASM, but -friendly aliases and directory structures can be defined for ease of reference. Failure groups are defined within a disk group to the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations. What are ASM instance initialization parameters? INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS. 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. ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. 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. ASM_DISKGROUPS - The list of disk groups 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. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered. Advantages of ASM in Oracle? Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage Uses redundancy features available in intelligent storage arrays The storage system can store all types of database files Using disk group makes configuration easier, as files are placed into disk groups ASM provides stripping and mirroring (fine and coarse gain - see below) ASM and non-ASM oracle files can coexist Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning. For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance. Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group. ASM s 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies. Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove

disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group. Managed file creation and deletion—ASM further reduces istration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed. Why should we use separate ASM home? ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently. How many ASM instances should one have? Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system. For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc). How many diskgroups should one have? Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA). Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks). To get started, create 2 disk groups - one for data and one for recovery files. Here is an example: CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....; CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....; Here is an example how you can enable automatic file management with such a setup: ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE; ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE; You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets. What is ASM Rebalancing? The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing. ALTER DISKGROUP data REBALANCE POWER 11;

What happens when an Oracle ASM diskgroup is created? When an ASM diskgroup is created, a hierarchialfilesystem structure is created. How does this filesystem structure appear? Oracle ASM diskgroup'sfilesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy. Where are the Oracle ASM files stored? Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures. How are the Oracle ASM files stored within the Oracle ASM filesystem structure? Oralce ASM files are stored within the Oracle ASM filesystem structures as objects that RDBMS instances/Oracle database instance access. RDBMS/Oracle instance treats the Oracle ASM files as standard filesystem files. What are the Oracle ASM files that are stored within the Oracle ASM file hierarchy? Files stored in Oracle ASM diskgroup/Oracl ASM filestructures include: 1) Datafile 2) Controlfiles 3) Server Parameter Files(SPFILE) 4) Redo Log files What happens when you create a file/database file in ASM?What commands do you use to create database files? Some common commands used for creating database files are :

1) Create tabespace 2) Add Datafile 3) Add Logfile For example, SQL> CREATE TABLESPACE TS1 DATAFILE '+DATA1' SIZE 10GB; Above command creates a datafile in DATA1 diskgroup How can you access a databasefile in ASM diskgroup under RDBMS? Once the ASM file is created in ASM diskgroup, a filename is generated. This file is now visible to the via the standard RDBMS view V$DATAFILE. What will be the syntax of ASM filenames? ASM filename syntax is as follows: +diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation where, +diskgroup_name - Name of the diskgroup that contains this file database_name - Name of the database that contains this file datafile - Can be one among 20 different ASM file types tag_name - corresponds to tablespace name for datafiles, groupnumber for redo log files file_number - file_number in ASM instance is used to correlate filenames in database instance incarnation_number - It is derived from the timestamp. IT is used to provide uniqueness What is an incarnation number? An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change. What is the use of an incarnation number in Oracle ASM filename? Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts Machine: x86_64 Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991 System parameters with non-default values: large_pool_size = 12M instance_type = "asm" remote__file= "EXCLUSIVE" asm_diskgroups = "FLASH" asm_diskgroups = "DATA" asm_power_limit = 1 diagnostic_dest = "/opt/app/oracle" Or using the asmcmd's spget command which shows the spfile location ed with GnP profile ASMCMD> spget +DATA/asm/asmparameterfile/registry.253.766260991

Oracle DBA Interview Questions and Answers - Patching,Cloning and Upgrade Oracle Patching,Cloning and Upgrade Interview Questions and Answers When you moved oracle binary files from one ORACLE_HOME server to another server then which oracle utility will be used to make this new ORACLE_HOME usable? Relink all. In which months oracle release U patches? JAN, APR, JUL, OCT When we applying single Patch, can you use opatch utility? Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset. Is it possible to apply OPATCH without downtime? As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible without or zero downtime in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME). You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it? With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture. For Example: opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.

If both U and PSU are available for given version which one, you will prefer to apply? From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply U on the top of PSU as PSU contain U (If you apply U over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1 PSU is superset of U then why someone choose to apply a U rather than a PSU? Us are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus anyone who is concerned only with security fixes and not functionality fixes, U may be good approach. How to Patches, Patchset or Opatch from metalink? If you are using latest .oracle.com then after to metalink Dashboard - Click on "Patches & Updates" tab - On the left sidebar click on "Latest Patchsets" under "Oracle Server/Tools". - A new window will appear. - Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page. - Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that. - You will go the page. From the page you can also change your platform and patchset version. REFERENCES: http://docs.oracle.com/cd/E11857_01/em.111/e12255/e_oui_appendix.htm Oracle® Universal Installer and OPatch 's Guide 11g Release 2 (11.2) for Windows and UNIX Part Number E12255-11

What is the recent Patch applied? What is OPatch? How to Apply Opatch in Oracle? 1. You MUST read the Ree.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch. 2.Make sure you have a good backup of database. 3. Make a note of all Invalid objects in the database prior to the patch. 4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc. 5. You MUST Backup your oracle Home and Inventory tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz 6. Unzip the patch in $ORACLE_HOME/patches 7. cd to the patch direcory and do opatch -apply to apply the patch. 8. Read the output/log file to make sure there were no errors. Patching Oracle Software with OPatch ? opatch napply <patch_location> -skip_subset -skip_duplicate OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home). What is Opactch in Oracle? OPATCH Utility (Oracle RDBMS Patching) 1. the required Patch from Metalink based on OS Bit Version and DB Version. 2. Need to down the database before applying patch. 3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command . 4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command . 5. Patch file format will be like, “p<patch no.>_ _ .zip” 6. We can check the opatch version using “opatch -version” command. 7. Generally, takes 2 minutes to apply a patch. 8. To get latest Opatch version “patch 6880880 - latest opatch tool”, it contains OPatch directory. 9. Contents of ed patches will be like “etc,files directories and a REE file” 10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch 11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the /cfgtoollogs/opatch directory. 12. Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations

of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2). 13. Direct Oracle 10g is only ed if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import). 14.Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are ed only via intermediate upgrades to a ed upgrade version. http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/ Oracle version 10.2.0.4.0 what does each number refers to? Oracle version number refers: 10 – Major database release number 2 – Database Maintenance release number 0 – Application server release number 4 – Component Specific release number 0 – Platform specific release number Types of Patches? How to rollback a patch? What is PSU? What is Rolling Patch? How to check installed Patches? How much time will it take for Patching? Common issues faced in Patching? REFERENCES: OPATCH Utility (Oracle RDBMS Patching) http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/ How to apply Database Patches http://rafioracledba.blogspot.in/search/label/Database%20Patches Critical Patch Updates, Security Alerts and Third Party Bulletin http://www.oracle.com/technetwork/topics/security/alerts-086861.html Oracle: Quick Guide to Opatch - (Oracle Database Patching utility) http://www.dbalifeline.com/content/oracle-quick-guide-opatch-oracle-database-patchingutility How to Design an Effective Patch Management Process http://www.computing.net/howtos/show/how-to-design-an-effective-patch-managementprocess/744.html Oracle Database 11.2.0.2 Patch Set (English) http://www.dbacomp.com.br/blog/?p=69 Apply Oracle UApr2010 – 9352191 for Oracle10.2.0.4 in Aix5L http://hendrydasan.com/2010/05/21/apply-oracle-uapr2010-9352191-for-oracle10-2-0-4-inaix5l/ Cloning ======= What is Cloning? How to do take RMAN Cloning? Explain Steps? Upgrade ======= 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.Rolling upgrade can be used only for Oracle database 11g releases(from 11.1). Steps to Upgrade in Oracle ? Manual upgrade which involves the following steps: 1.Backup the database. 2.In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home. 3.Analyze the existing instance using the "$ORACLE_HOME/rdbms//utlu111i.sql" script. 4.Start the original database using the STARTUP UPGRADE command and proceed with the

upgrade by running the "$ORACLE_HOME/rdbms//catupgrd.sql" script. 5.Recompile invalid objects. 6.Restart the database. 7.Run the "$ORACLE_HOME/rdbms//utlu111s.sql" script and check the result of the upgrade. 8.Troubleshoot any issues or abort the upgrade. What happens when you give "STARTUP UPGRADE"? $sqlplus "/as sysdba" SQL> STARTUP UPGRADE Note: ---The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBAsessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade. You might be required to use the PFILE option to specify the location of your initialization parameter file. Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error. What is the difference between startup Upgrade and Migrate ? startup migrate: --------------Used to upgrade a database till 9i. Startup Upgrade --------------From 10G we are using startup upgrade database. What happens internally when you use startup upgrade/migrate? It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothely. in other way..it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues. REFERENCE: --------Oracle® Database Upgrade Guide 11g Release 2 (11.2) http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm Common issues faced in Upgrade? Error is related to timezone file Started database in upgrade mode and fired catupgrd.sql : SQL> startup upgrade ORACLE instance started. Total System Global Area 6413680640 bytes Fixed Size 2160112 bytes Variable Size 1946159632 bytes Database Buffers 4429185024 bytes Redo Buffers 36175872 bytes Database mounted. Database opened. SQL> @catupgrd.sql DOC>####################################################################### DOC>####################################################################### DOC> DOC> The first time this script is run, there should be no error messages DOC> generated; all normal upgrade error messages are suppressed. DOC> DOC> If this script is being re-run after correcting some problem, then DOC> expect the following error which is not automatically suppressed: DOC> DOC> ORA-00001: unique constraint () violated DOC># FROM registry$database * ERROR at line 2: ORA-00942: table or view does not exist This error is related to timezone file which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied. Query to check timezone file is: SQL> select * from v$timezone_file; FILENAME VERSION ———— ———-

timezlrg.dat 4 SQL> select * from v$timezone_file; FILENAME VERSION ———— ———timezlrg.dat 4 So I had correct version.I applying patch before upgrade.I got lucky because patch existed for version 10.2.0.3. If there is no patch for your Oracle versions than patch can be for similar version and applied manually. Instructions are below: 1. the identified patch. 2. Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the “files/oracore/zoneinfo” directory of the uncompressed patch (or from the relevant .jar file of a patchset). If there is also a ree.txt in this location then make a note of this as well. 3. Backup your existing files in $ORACLE_HOME/oracore/zoneinfo – THIS CAN BE VITAL, DO NOT SKIP. Note: Before going on with step 4, make sure the current files are not in use. On Windows the files will simply refuse to be removed when the are in use. On Unix replacing the files whilst they are in use can cause the files to become corrupt. Use the f command before replacing the files to make sure they are not in use. 4. Copy the 2 .dat files and possibly the ree.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory. 5. Restart the database (in case of installation on a database), or restart the client applications (in case of client install). Note that the database did not need to be down before the time zone files were applied, but it does need to be restarted afterwards. http://indiandba.blogspot.in/2012/01/error-is-related-to-timezone-file.html

Oracle DBA Interview Questions and Answers - RAC Oracle RAC Interview Questions and Answers How does OCSSD starts first if voting disk & OCR resides in ASM Diskgroups? You might wonder how CSSD, which is required to start the clustered ASM instance, can be started if voting disks are stored in ASM? This sounds like a chicken-and-egg problem: without access to the voting disks there is no CSS, hence the node cannot the cluster. But without being part of the cluster, CSSD cannot start the ASM instance. To solve this problem the ASM disk headers have new metadata in 11.2: you can use kfed to read the header of an ASM disk containing a voting disk. The kfdhdb.vfstart and kfdhdb.vfend fields tell CSS where to find the voting file. This does not require the ASM instance to be up. Once the voting disks are located, CSS can access them and s the cluster. What is gsdctl in RAC? list gsdctl commands in Oracle RAC? GSDCTL stands for Global Service Daemon Control, we can use gsdctl commands to start, stop, and obtain the status of the GSD service on any platform. The options for gsdctl are:$ gsdctl start -- To start the GSD service $ gsdctl stop -- To stop the GSD service $ gsdctl stat -- To obtain the status of the GSD service Log file location for gsdctl: $ ORACLE_HOME/srvm/log/gsdaemon_node_name.log 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. Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all the business applications. Oracle RAC provides the foundation for enterprise grid computing. What is Oracle RAC One Node? Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition). What is RAC and how is it different from non RAC databases? Oracle Real Application clusters allows multiple instances to access a single database, the instances will be running on multiple nodes. In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide

consistency and integrity. What are the advantages of RAC (Real Application Clusters)? Reliability - if one node fails, the database won't fail Availability - nodes can be added or replaced without having to shutdown the database Scalability - more nodes can be added to the cluster as the workload increases What is Oracle RAC One Node? Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition). What is Cache Fusion? Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block, it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service (GES) monitors and Instance enqueue process manages the cache fusion. What command would you use to check the availability of the RAC system? crs_stat -t -v (-t -v are optional) How do we that RAC instances are running? SQL>select * from V$ACTIVE_INSTANCES; The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column. How can you connect to a specific node in a RAC environment? tnsnames.ora ensure that you have INSTANCE_NAME specified in it. Which is the "MASTER NODE" in RAC? The node with the lowest node number will become master node and dynamic remastering of the resources will take place. To find out the master node for particular resource, you can query v$ges_resource for MASTER_NODE column. To find out which is the master node, you can see ocssd.log file and search for "master node number". when the first master node fails in the cluster the lowest node number will become master node. What components in RAC must reside in shared storage? All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage. Give few examples for solutions that cluster storage? ·ASM (automatic storage management), ·Raw disk devices, ·Network file system (NFS), ·OCFS2 and ·OCFS (Oracle Cluster Fie systems). What are Oracle Cluster Components? 1.Cluster Interconnect (HAIP) 2.Shared Storage (OCR/Voting Disk) 3.Clusterware software 4.Oracle Kernel Components What are Oracle RAC Components? VIP, Node apps etc. What are Oracle Kernel Components? Basically Oracle kernel need to switched on with RAC On option when you convert to RAC, that is the difference as it facilitates few RAC bg process like LMON,LCK,LMD,LMS etc. How to turn on RAC? # link the oracle libraries $ cd $ORACLE_HOME/rdbms/lib $ make -f ins_rdbms.mk rac_on # rebuild oracle $ cd $ORACLE_HOME/bin $ relink oracle Disk architechture in RAC? SAN (Storage Area Networks) - generally using fibre to connect to the SAN NAS (Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI

What is Oracle Clusterware? The Clusterware software allows nodes to communicate with each other and forms the cluster that makes the nodes work as a single logical server. The software is run by the Cluster Ready Services (CRS) using the Oracle Cluster Registry (OCR) that records and maintains the cluster and node hip information and the voting disk which acts as a tiebreaker during communication failures. Consistent heartbeat information travels across the interconnect to the voting disk when the cluster is running.

Real Application Clusters Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all your business applications. Oracle RAC provides the foundation for enterprise grid computing. Oracle’s Real Application Clusters (RAC) option s the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages. Oracle RAC running on clusters provides Oracle’s highest level of capability in of availability, scalability, and low-cost computing. One DB opened by multipe instances so the the db ll be Highly Available if an crashes. Cluster Software. Oracles Clusterware or products like Veritas Volume Manager to provide the cluster and allow each node to know which nodes belong cluster and are available and with Oracle Cluterware to know which nodes have eject then from the cluster, so that errors on that node can be cleared.

instance are required to the failed and to

Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk. The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar. The voting disk is used to determine if a node has failed, i.e. become separated from the majority. If a node is deemed to no longer belong to the majority then it is forcibly rebooted and will after the reboot add itself again the the surviving cluster nodes. What are the Oracle Clusterware key components? Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk. What is Voting Disk and OCR? Voting Disk Oracle RAC uses the voting disk to manage cluster hip 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. A node must be able to access more than half of the voting disks at any time. For example, if you have 3 voting disks configured, then a node must be able to access at least two of the voting disks at any time. If a node cannot access the minimum required number of voting disks it is evicted, or removed, from the cluster. Oracle Cluster Registry (OCR) The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster. What are the istrative tasks involved with voting disk? Following istrative 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 Can you add voting disk online? Do you need voting disk backup? Yes, as per documentation, if you have multiple voting disk you can add online, but if you have only one voting disk , by that cluster will be down as its lost you just need to start crs in exclusive mode and add the votedisk using crsctl add votedisk <path> 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. 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 , 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: copy voting_disk_name backup_file_name How do we an existing current backup of OCR? We can the current backup of OCR using the following command : ocrconfig -showbackup You have lost OCR disk, what is your next step? The cluster stack will be down due to the fact that cssd is unable to maintain the integrity, this is true in 10g, From 11gR2 onwards, the crsd stack will be down, the hasd still up and running. You can add the ocr back by restoring the automatic backup or import the manual backup, 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. 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 find the fault with network. 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. Srvctl cannot start instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can start it on both nodes? How do you identify the problem? Set the environmental variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will get detailed error stack. What are Oracle Clusterware processes for 10g on Unix and Linux? Cluster Synchronization Services (ocssd) — Manages cluster node hip and runs as the oracle ; 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 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 Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur. What are Oracle database background processes specific to RAC? 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 To ensure that each Oracle RAC database instance obtains the block that it needs to satisfy

a query or transaction, Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances. What 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. What is 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. What is SCAN listener? A scan listener is something that additional to node listener which listens the incoming db connection requests from the client which got through the scan IP, it got end points configured to node listener where it routes the db connection requests to particular node listener. SCAN IP can be disabled if not required. However SCAN IP is mandatory during the RAC installation. Enabling/disabling SCAN IP is mostly used in oracle apps environment by the concurrent manager (kind of job scheduler in oracle apps). Steps to disable the SCAN IP, i. Do not use SCAN IP at the client end. ii. Stop scan listener srvctl stop scan_listener iii.Stop scan srvctl stop scan (this will stop the scan vip's) iv. Disable scan and disable scan listener srvctl disable scan 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 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. What is the use of cluster interconnect? Cluster interconnect is used by the Cache fusion for inter instance communication. How can we configure the cluster interconnect? · Configure Datagram Protocol (UDP) on Gigabit Ethernet for cluster interconnects. · On UNIX and Linux systems we use UDP and RDS (Reliable data socket) protocols to be used by Oracle Clusterware. · Windows clusters use the T protocol. 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 T protocol. RAC uses the interconnect for cache fusion (UDP) and inter-process communication (T). Cache Fusion is the remote memory mapping of Oracle buffers, shared between the caches of participating nodes in the cluster. What is a virtual IP address or VIP? A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network. What is the use of VIP? If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept T connections but it cannot accept Oracle connections. 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 T 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. Give situations under which VIP address failover happens?

VIP addresses failover happens when the node on which the VIP address runs fails; all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network. What is the significance of VIP address failover? When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don't have to wait for T connection timeout messages. 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 s and applications connect to database instances. What are the characteristics controlled by Oracle services feature? The characteristics include a unique name, workload balancing, failover options, and high availability. 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. What are the types of connection load-balancing? Connection Workload management is one of the key aspects when you have RAC instances as you want to distribute the connections to specific nodes/instance or those have less load. There are two types of connection load-balancing: 1.Client Side load balancing (also called as connect time load balancing) 2.Server side load balancing (also called as Listener connection load balancing) 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. Client Side load balancing:- Oracle client side load balancing feature enables clients to randomize the connection requests among all the available listeners based on their load. An tns entry that contains all nodes entries and use load_balance=on (default its on) will use the connect time load balancing or client side load balancing. Sample Client Side TNS Entry:finance = (DESCRIPTION = (ADDRESS = (PROTOCOL = T)(HOST = myrac2-vip)(PORT = 2042)) (ADDRESS = (PROTOCOL = T)(HOST = myrac1-vip)(PORT = 2042)) (ADDRESS = (PROTOCOL = T)(HOST = myrac3-vip)(PORT = 2042)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FINANCE) (FAILOVER=ON) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)) ) ) Server side load balancing:- This improves the connection performance by balancing the number of active connections among multiple instances and dispatchers. In a single instance environment (shared servers), the listener selects the least dispatcher to handle the incoming client requests. In a rac environments, PMON is aware of all instances load and dispatchers , and depending on the load information PMON redirects the connection to the least loaded node. In a RAC environment, *.remote_listener parameter which is a tns entry containing all nodes addresses need to set to enable the load balance advisory updates to PMON. Sample Tns entry should be in an instances of RAC cluster, local_listener=LISTENER_MYRAC1 remote_listener = LISTENERS_MYRACDB What are the istrative tools used for Oracle RAC environments? Oracle RAC cluster can be istered as a single image using the below · OEM (Enterprise Manager), · SQL*PLUS, · Server control (SRVCTL), · Cluster Verification Utility (CLUVFY), · DBCA, · NETCA Name some Oracle Clusterware tools and their uses? ·OIFCFG - allocating and deallocating network interfaces. ·OCRCONFIG - Command-line tool for managing Oracle Cluster Registry.

·OCRDUMP - Identify the interconnect being used. ·CVU - Cluster verification utility to get status of CRS resources. What is the difference between CRSCTL and SRVCTL? crsctl manages clusterware-related operations: Starting and stopping Oracle Clusterware Enabling and disabling Oracle Clusterware daemons ing cluster resources srvctl manages Oracle resource–related operations: Starting and stopping database instances and services Also from 11gR2 manages the cluster resources like network,vip,disks etc 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 if ASM has been removed by issuing the following command: srvctl config asm -n node_name How do we 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 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). What are the background process that exists in 11gr2 and functionality? Process Name Functionality crsd •The CRS daemon (crsd) manages cluster resources based on configuration information that is stored in Oracle Cluster Registry (OCR) for each resource. This includes start, stop, monitor, and failover operations. The crsd process generates events when the status of a resource changes. cssd •Cluster Synchronization Service (CSS): Manages the cluster configuration by controlling which nodes are of the cluster and by notifying when a node s or leaves the cluster. If you are using certified third-party clusterware, then CSS processes interfaces with your clusterware to manage node hip information. CSS has three separate processes: the CSS daemon (ocssd), the CSS Agent (cssdagent), and the CSS Monitor (cssdmonitor). The cssdagent process monitors the cluster and provides input/output fencing. This service formerly was provided by Oracle Process Monitor daemon (oprocd), also known as OraFenceService on Windows. A cssdagent failure results in Oracle Clusterware restarting the node. diskmon •Disk Monitor daemon (diskmon): Monitors and performs input/output fencing for Oracle Exadata Storage Server. As Exadata storage can be added to any Oracle RAC node at any point in time, the diskmon daemon is always started when ocssd is started. evmd •Event Manager (EVM): Is a background process that publishes Oracle Clusterware events mdnsd •Multicast domain name service (mDNS): Allows DNS requests. The mDNS process is a background process on Linux and UNIX, and a service on Windows. gnsd •Oracle Grid Naming Service (GNS): Is a gateway between the cluster mDNS and external DNS servers. The GNS process performs name resolution within the cluster. ons •Oracle Notification Service (ONS): Is a publish-and-subscribe service for communicating Fast Application Notification (FAN) events oraagent •oraagent: Extends clusterware to Oracle-specific requirements and complex resources. It runs server callout scripts when FAN events occur. This process was known as RACG in Oracle Clusterware 11g Release 1 (11.1). orarootagent •Oracle root agent (orarootagent): Is a specialized oraagent process that helps CRSD manage resources owned by root, such as the network, and the Grid virtual IP address oclskd •Cluster kill daemon (oclskd): Handles instance/node evictions requests that have been escalated to CSS gipcd •Grid IPC daemon (gipcd): Is a helper daemon for the communications infrastructure ctssd •Cluster time synchronisation daemon(ctssd) to manage the time syncrhonization between nodes, rather depending on NTP Under which or owner the process will start? Component Name of the Process Owner Oracle High Availability Service ohasd init, root Cluster Ready Service (CRS) Cluster Ready Services root Cluster Synchronization Service (CSS) ocssd,cssd monitor, cssdagent Event Manager (EVM) evmd, evmlogger grid owner Cluster Time Synchronization Service (CTSS) octssd root Oracle Notification Service (ONS) ons, eons grid owner Oracle Agent oragent grid owner Oracle Root Agent orarootagent root Grid Naming Service (GNS) gnsd root Grid Plug and Play (GPnP) gpnpd grid owner

grid owner

Multicast domain name service (mDNS)

mdnsd

grid owner

What is the major difference between 10g and 11g RAC? There is not much difference between 10g and 11gR (1) RAC. But there is a significant difference in 11gR2. Prior to 11gR1(10g) RAC, the following were managed by Oracle CRS Databases Instances Applications Node Monitoring Event Services High Availability From 11gR2(onwards) its completed HA stack managing and providing the following resources as like the other cluster software like VCS etc. Databases Instances Applications Cluster Management Node Management Event Services High Availability Network Management (provides DNS/GNS/MDNSD services on behalf of other traditional services) and SCAN – Single Access Client Naming method, HAIP Storage Management (with help of ASM and other new ACFS filesystem) Time synchronization (rather depending upon traditional NTP) Removed OS dependent hang checker etc, manages with own additional monitor process What is hangcheck timer? The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically. There are 2 key parameters for this module: -> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds. -> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node. 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___FILE UNDO_MANAGEMENT -------------------------------------------------------------------------------------------------------------What is RAC? What is the benefit of RAC over single instance database? In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity. Benefits: Improve response time Improve throughput High availability Transparency

Advantages of RAC (Real Application Clusters) Reliability - if one node fails, the database won't fail Availability - nodes can be added or replaced without having to shutdown the database Scalability - more nodes can be added to the cluster as the workload increases

What is a virtual IP address or VIP? A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network. What is the use of VIP? If a node fails, then the node's VIP address fails over to another node on which the VIP address can accept T connections but it cannot accept Oracle connections. Give situations under which VIP address failover happens:VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network. Using virtual IP we can save our T/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners. 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 T connection timeout messages. What is voting disk? Voting Disk is a file that sits in the shared storage area and must be accessible by all nodes in the cluster. All nodes in the cluster s their heart-beat information in the voting disk, so as to confirm that they are all operational. If heart-beat information of any node in the voting disk is not available that node will be evicted from the cluster. The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat of all nodes to the voting disk. When any node is not able to send heartbeat to voting disk, then it will reboot itself, thus help avoiding the split-brain syndrome. For high availability, Oracle recommends that you have a minimum of three or odd number (3 or greater) of votingdisks. Voting Disk - is file that resides on shared storage and Manages cluster . disk reassigns cluster ownership between the nodes in case of failure.

Voting

The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity. Oracle Database 11g Release 2 provides the ability to store the voting disks in ASM along with the OCR. Oracle Clusterware can access the OCR and the voting disks present in ASM even if the ASM instance is down. As a result CSS can continue to maintain the Oracle cluster even if the ASM instance has failed. How many voting disks are you maintaining ? http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/RACR2ARC6/Defa ult.aspx By default Oracle will create 3 voting disk files in ASM. Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail. You should plan on allocating 280MB for each voting disk file. For example, if you are using ASM and external redundancy then you will need to allocate 280MB of disk for the voting disk. If you are using ASM and normal redundancy you will need 560MB. Why we need to keep odd number of voting disks ? Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.

What are Oracle RAC software components? Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes: ACMS—Atomic Controlfile to Memory Service (ACMS) GTX0-j—Global Transaction Process LMON—Global Enqueue Service Monitor LMD—Global Enqueue Service Daemon LMS—Global Cache Service Process LCK0—Instance Enqueue Process RMSn—Oracle RAC Management Processes (RMSn) RSMN—Remote Slave Monitor What are Oracle Clusterware processes for 10g ?

Cluster Synchronization Services (ocssd) — Manages cluster node hip and runs as the oracle ; 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 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 Oracle-specific requirements and complex resources. Runs server callout scripts when FAN events occur. What are Oracle database background processes specific to RAC? LMS—Global Cache Service Process LMD—Global Enqueue Service Daemon LMON—Global Enqueue Service Monitor LCK0—Instance Enqueue Process Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES). The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances. What is Cache Fusion? Transfor of data across instances through private interconnect is called cachefusion.Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion What is SCAN? (11gR2 feature) 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. SCAN provides a single domain name via (DNS), allowing and-s to address a RAC cluster as-if it were a single IP address. SCAN works by replacing a hostname or IP list with virtual IP addresses (VIP). Single client access name (SCAN) is meant to facilitate single name for all Oracle clients to connect to the cluster database, irrespective of number of nodes and node location. Until now, we have to keep adding multiple address records in all clients tnsnames.ora, when a new node gets added to or deleted from the cluster. Single Client Access Name (SCAN) eliminates the need to change TNSNAMES entry when nodes are added to or removed from the Cluster. RAC instances to SCAN listeners as remote listeners. Oracle recommends asg 3 addresses to SCAN, which will create 3 SCAN listeners, though the cluster has got dozens of nodes.. SCAN is a domain name ed to at least one and up to three IP addresses, either in DNS (Domain Name Service) or GNS (Grid Naming Service). The SCAN must resolve to at least one address on the public network. For high availability and scalability, Oracle recommends configuring the SCAN to resolve to three addresses. http://www.freeoraclehelp.com/2011/12/scan-setup-for-oracle-11g-release211gr2.html What are SCAN components in a cluster? 1.SCAN Name 2.SCAN IPs (3) 3.SCAN Listeners (3) 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. What is TAF? TAF (Transparent Application Failover) is a configuration that allows session fail-over between different nodes of a RAC database cluster. Transparent Application Failover (TAF). If a communication link failure occurs after a connection is established, the connection fails over to another active node. Any disrupted transactions are rolled back, and session properties and server-side program variables are lost. In some cases, if the statement executing at the time of the failover is a Select statement, that statement may be automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover. After an Oracle RAC node crashes—usually from a hardware failure—all new application transactions are automatically rerouted to a specified backup node. The challenge in rerouting is to not lose transactions that were "in flight" at the exact moment of the

crash. One of the requirements of continuous availability is the ability to restart inflight application transactions, allowing a failed node to resume processing on another server without interruption. Oracle's answer to application failover is a new Oracle Net mechanism dubbed Transparent Application Failover. TAF allows the DBA to configure the type and method of failover for each Oracle Net client. TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level. What are the requirements for Oracle Clusterware? 1. External Shared Disk to store Oracle Cluster ware file (Voting Disk and Oracle Cluster Registry - OCR) 2. Two netwrok cards on each cluster ware node (and three set of IP address) Network Card 1 (with IP address set 1) for public network Network Card 2 (with IP address set 2) for private network (for inter node communication between rac nodes used by clusterware and rac database) IP address set 3 for Virtual IP (VIP) (used as Virtual IP address for client connection and for connection failover) 3. Storage Option for OCR and Voting Disk - RAW, OCFS2 (Oracle Cluster File System), NFS, ….. Which enable the load balancing of applications in RAC? Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database. How to find location of OCR file when CRS is down? If you need to find the location of OCR (Oracle Cluster Registry) but your CRS is down. When the CRS is down: Look into “ocr.loc” file, location of this file changes depending on the OS: On Linux: /etc/oracle/ocr.loc On Solaris: /var/opt/oracle/ocr.loc When CRS is UP: Set ASM environment or CRS environment then run the below command: ocrcheck In 2 node RAC, how many NIC’s are r using ? 2 network cards on each clusterware node Network Card 1 (with IP address set 1) for public network Network Card 2 (with IP address set 2) for private network (for inter node communication between rac nodes used by clusterware and rac database) In 2 node RAC, how many IP’s are r using ? 6 - 3 set of IP address ## eth1-Public: 2 ## eth0-Private: 2 ## VIP: 2 How to find IP’s information in RAC ? Edit the /etc/hosts file as shown below: # Do not remove the following line, or various programs # that requires network functionality will fail. 127.0.0.1 localhost.localdomain localhost ## Public Node names 192.168.10.11 node1-pub.hingu.net node1-pub 192.168.10.22 node2-pub.hingu.net node2-pub ## Private Network (Interconnect) 192.168.0.11 node1-prv node1-prv 192.168.0.22 node2-prv node2-prv ## Private Network (Network Area storage) 192.168.1.11 node1-nas node1-nas 192.168.1.22 node2-nas node2-nas 192.168.1.33 nas-server nas-server ## Virtual IPs 192.168.10.111 node1-vip.hingu.net node1-vip 192.168.10.222 node2-vip.hingu.net node2-vip What is difference between RAC ip addresses ? Public IP adress is the normal IP address typically used by DBA and SA to manage storage, system and database. Public IP addresses are reserved for the Internet. Private IP address is used only for internal clustering processing (Cache Fusion) (aka as interconnect). Private IP addresses are reserved for private networks. VIP is used by database applications to enable fail over when one cluster node fails. The purpose for having VIP is so client connection can be failover to surviving nodes in case there is failure

Can application developer access the private ip ? No. private IP address is used only for internal clustering processing (Cache Fusion) (aka as interconnect) http://rajeevjhaoracle.wordpress.com/2012/01/02/oracle-rac-interview-question-answer/

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

What is RAC? RAC Architechture? What is a SCAN Listener? What is VIP? What is TAF? What is FAN? What is LMON? What is CACHE FUSION? What is SPLIT BRAIN in RAC? How Load balancing works in ORACLE RAC? What is Voting Disk? How many backups are there for Voting Disk? WHY ODD NUMBER OF VOTING DISK IS USED? How to take Voting Disk backup? Do you take Voting Disk backup? If you lost Voting Disk, how do you Restore? What is OCR? How many backups are there for OCR? How to take OCR backup? Do you take OCR backup? If you lost OCR, how do you Restore? What is a Master Node? How do you know which is the Master Node without connecting to Database? Can we have Node 1 in SOLARIS OS and Node 2 in LINUX OS? Commomnly used commands in RAC? How to convert standalone database to RAC? ---------------------------------------------------------------------------------------------------------------------------------------------

Posted by Unknown

No comments:

Labels: Oracle DBA Interview Questions

Home

Older Posts

Subscribe to: Posts (Atom)

Subscribe To Posts All Comments

Awesome Inc. theme. Powered by Blogger.

Oracle Dba Interview Questions 4m721r

Overview 6h3y3j

More details h6z72

Related Documents 543cg

Oracle Dba Interview Questions 4m721r

Oracle Dba Interview Questions 4m721r

Oracle Dba Interview Questions 1 5l6s53

All Oracle Dba Interview Questions 3h526r

Senior Oracle Dba Interview Questions 4w64

Oracle Dba Upgradation Interview 4696z

More Documents from "madhu sudhana reddy" 585tg

Oracle Dba Interview Questions 4m721r

How To Improve Your Iq - Wikihow 3b43z

Glomerular Disease 3e6b43

Readon Tv Movie Radio Player Manual 6g2wl

Sunstone- Octavio Paz 4l2m

Hameeda-anbin+adai+mazhaikalam 2u4p1j