Oracle Data Guard Interview Questions & Answers

What are the types of Oracle Data Guard?

Oracle Data Guard classified in to two types based on way of creation and method used for Redo Apply. They are as follows.

1. Physical standby (Redo Apply technology)
2. Logical standby (SQL Apply Technology)

What are the advantages in using Oracle Data Guard?

Following are the different benefits in using Oracle Data Guard feature in your environment.

1. High Availability.
2. Data Protection.
3. Off loading Backup operation to standby database.
4. Automatic Gap detection and Resolution in standby database.
5. Automatic Role Transition using Data Guard Broker.

What are the different services available in Oracle Data Guard?

Following are the different Services available in Oracle Data Guard of Oracle database.

1. Redo Transport Services.
2. Log Apply Services.
3. Role Transitions.

What are the different Protection modes available in Oracle Data Guard?

Following are the different protection modes available in Data Guard of Oracle database you can use any one based on your application requirement.

1. Maximum Protection
2. Maximum Availability
3. Maximum Performance

How to check what protection mode of primary database in your Oracle Data Guard?

By using following query you can check protection mode of primary database in your Oracle Data Guard setup.

SELECT PROTECTION_MODE FROM V$DATABASE;

For Example:

SQL> select protection_mode from v$database;

PROTECTION_MODE
——————————–

MAXIMUM PERFORMANCE

How to change protection mode in Oracle Data Guard setup?

By using following query your can change the protection mode in your primary database after setting up required value in corresponding LOG_ARCHIVE_DEST_n parameter in primary database for corresponding standby database.

ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY];

Example:

alter database set standby database to MAXIMUM PROTECTION;

What are the advantages of using Physical standby database in Oracle Data Guard?

Advantages of using Physical standby database in Oracle Data Guard are as follows.

• High Availability.
• Load balancing (Backup and Reporting).
• Data Protection.
• Disaster Recovery.

What is physical standby database in Oracle Data Guard?

Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Physical standby database are created as exact copy i.e block by block copy of primary database. In physical standby database transactions happen in primary database are synchronized in standby database by using Redo Apply method by continuously applying redo data on standby database received from primary database. Physical standby database can offload the backup activity and reporting activity from Primary database. Physical standby database can be opened for read-only transactions but redo apply won’t happen during that time. But from 11g onwards using Active Data Guard option (extra purchase) you can simultaneously open the physical standby database for read-only access and apply redo logs received from primary database.

What is Logical standby database in Oracle Data Guard?

Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Logical standby database can be created similar to Physical standby database and later you can alter the structure of logical standby database. Logical standby database uses SQL Apply method to synchronize logical standby database with primary database. This SQL apply technology converts the received redo logs to SQL statements and continuously apply those SQL statements on logical standby database to make standby database consistent with primary database. Main advantage of Logical standby database compare to physical standby database is you can use Logical standby database for reporting purpose during SQL apply i.e Logical standby database must be open during SQL apply. Even though Logical standby database are opened for read/write mode, tables which are in synchronize with primary database are available for read-only operations like reporting, select queries and adding index on those tables and creating materialized views on those tables. Though Logical standby database has advantage on Physical standby database it has some restriction on data-types, types of DDL, types of DML and types of tables.

What are the advantages of Logical standby database in Oracle Data Guard?

• Better usage of resource
• Data Protection
• High Availability
• Disaster Recovery

What is the usage of DB_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?

DB_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. DB_FILE_NAME_CONVERT parameter are used to update the location of data files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database data files location.

What is the usage of LOG_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?

LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to update the location of redo log files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database redo log file location.

 Step for Physical  Standby

These are the steps to follow:

1. Enable forced logging
2. Create a password file
3. Configure a standby redo log
4. Enable archiving
5. Set up the primary database initialization parameters
6. Configure the listener and tnsnames to support the database on both nodes

col name format a20

col thread# format 999

col sequence# format 999

col first_change# format 999999

col next_change# format 999999

SELECT thread#, sequence# AS “SEQ#”, name, first_change# AS “FIRSTSCN”,

       next_change# AS “NEXTSCN”,archived, deleted,completion_time AS “TIME”

FROM   v$archived_log

V$ log_history

Tell me about parameter which is used for standby database?

Log_Archive_Dest_n

Log_Archive_Dest_State_n

Log_Archive_Config

Log_File_Name_Convert

Standby_File_Managment

DB_File_Name_Convert

DB_Unique_Name

Control_Files

Fat_Client

Fat_Server

The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg2. The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.

Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.

Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.

Oracle RAC Interview Questions & Answers

1. Where are the Clusterware files stored on a RAC environment?
The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)

2. Where are the database software files stored on a RAC environment?

The base software is installed on each node of the cluster and the
database storage on the shared disks.

3. What kind of storage we can use for the shared Clusterware files?
- OCFS (Release 1 or 2)
- raw devices
- third party cluster file system such as GPFS or Veritas

4. What kind of storage we can use for the RAC database storage?
- OCFS (Release 1 or 2)
- ASM
- raw devices
- third party cluster file system such as GPFS or Veritas

5. What is a CFS?
A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.

6. What is an OCFS2?
The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster.

7. Which files can be placed on an Oracle Cluster File System?
- Oracle Software installation (Windows only)
- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
- Shared configuration files (spfile)
- OCR and voting disk
- Files created by Oracle during runtime

Note: There are some platform specific limitations.

8. Do you know another Cluster Vendor?
HP Tru64 Unix, Veritas, Microsoft

9. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.

10. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

11. What is a raw partition?
A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.

12. When to use CFS over raw?
A CFS offers:
- Simpler management
- Use of Oracle Managed Files with RAC
- Single Oracle Software installation
- Autoextend enabled on Oracle datafiles
- Uniform accessibility to archive logs in case of physical node failure
- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster.

Note: This option is very dependent on the availability of a CFS on your platform.

13. When to use raw over CFS?
- Always when CFS is not available or not supported by Oracle.
- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk.
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed.

14. What CRS is?
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.

15. What is VIP IP used for?
It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.

16. Why we need to have configured SSH or RSH on the RAC nodes?
SSH (Secure Shell,10g+) or RSH (Remote Shell, 9i+) allows “oracle” UNIX account connecting to another RAC node and copy/ run commands as the local “oracle” UNIX account.

17. Is the SSH, RSH needed for normal RAC operations?
No. SSH or RSH are needed only for RAC, patch set installation and clustered database creation.

18. Do we have to have Oracle RDBMS on all nodes?
Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk).

19. What are the restrictions on the SID with a RAC database? Is it limited to 5 characters?
The SID prefix in 10g Release 1 and prior versions was restricted to five characters by install/ config tools so that an ORACLE_SID of up to max of 5+3=8 characters can be supported in a RAC environment. The SID prefix is relaxed up to 8 characters in 10g Release 2, see bug 4024251 for more information.

20. Does Real Application Clusters support heterogeneous platforms?
The Real Application Clusters do not support heterogeneous platforms in the same cluster.

21. Are there any issues for the interconnect when sharing the same switch as the public network by using VLAN to separate the network?
RAC and Clusterware deployment best practices suggests that the interconnect (private connection) be deployed on a stand-alone, physically separate, dedicated switch. On big network the connections could be instable.

22. What is the Load Balancing Advisory?
To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag.

23. How many nodes are supported in a RAC Database?
With 10g Release 2, we support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. Currently DBCA has a bug where it will not go beyond 63 instances. There is also a documentation bug for the max-instances parameter. With 10g Release 1 the Maximum is 63.

24. What is the Cluster Verification Utiltiy (cluvfy)?
The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.

25. What versions of the database can I use the cluster verification utility (cluvfy) with?
The cluster verification utility is release with Oracle Database 10g Release 2 but can also be used with Oracle Database 10g Release 1.

26. If I am using Vendor Clusterware such as Veritas, IBM, Sun or HP, do I still need Oracle Clusterware to run Oracle RAC 10g?
Yes. When certified, you can use Vendor Clusterware however you must still install and use Oracle Clusterware for RAC. Best Practice is to leave Oracle Clusterware to manage RAC. For details see Metalink Note 332257.1 and for Veritas SFRAC see 397460.1.

27. Is RAC on VMWare supported?
Yes.

28. What is hangcheck timer used for ? 
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.

29. Is the hangcheck timer still needed with Oracle RAC 10g?
Yes.

30. What files can I put on Linux OCFS2?
For optimal performance, you should only put the following files on Linux OCFS2:
- Datafiles
- Control Files
- Redo Logs
- Archive Logs
- Shared Configuration File (OCR)
- Voting File
- SPFILE

31. Is it possible to use ASM for the OCR and voting disk?
No, the OCR and voting disk must be on raw or CFS (cluster file system).

32. Can I change the name of my cluster after I have created it when I am using Oracle Clusterware?
No, you must properly uninstall Oracle Clusterware and then re-install.

33. What the O2CB is?
The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems).

34. What the OCR file is used for? 
OCR is a file that manages the cluster and RAC configuration.

35. What the Voting Disk file is used for? 
The voting disk is nothing but a file that contains and manages information of all the node memberships.

36. What is the recommended method to make backups of a RAC environment?                          RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.

37.  What command would you use to check the availability of the RAC system?
crs_stat -t -v (-t -v are optional)
38. What is the minimum number of instances you need to have in order to create a RAC?
You can create a RAC with just one server.
39.  Name two specific RAC background processes
RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.

40.  Can you have many database versions in the same RAC?
Yes, but Clusterware version must be greater than the greater database version.

41.  What was RAC previous name before it was called RAC?OPS: Oracle Parallel Server

42.  What RAC component is used for communication between instances?Private Interconnect.

43.  What is the difference between normal views and RAC views?A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION

44.  Which command will we use to manage (stop, start) RAC services in command-line mode?
srvctl

45.  How many alert logs exist in a RAC environment?
A- One for each instance.

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

47. How do you backup voting disk

#dd if=voting_disk_name of=backup_file_name

48. How do I identify the voting disk location

#crsctl query css votedisk

49. How do I identify the OCR file location

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

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

Basic RAC Management Commands

The commands we will use are listed below. Remember that this document is a quick reference, and not an exhaustive list of all commands for managing your RAC environment.

Cluster Related Commands
crs_stat -t	Shows HA resource status (hard to read)
crsstat	Ouptut of crs_stat -t formatted nicely
ps -ef|grep d.bin	crsd.bin evmd.bin ocssd.bin
crsctl check crs	CSS,CRS,EVM appears healthy
crsctl stop crs	Stop crs and all other services
crsctl disable crs*	Prevents CRS from starting on reboot
crsctl enable crs*	Enables CRS start on reboot
crs_stop -all	Stops all registered resources
crs_start -all	Starts all registered resources

* These commands update the file /etc/oracle/scls_scr/<node>/root/crsstart which contains the string “enable” or “disable” as appropriate.

Database Related Commands
srvctl start instance -d <db_name> -i <inst_name>	Starts an instance
srvctl start database -d <db_name>	Starts all instances
srvctl stop database -d <db_name>	Stops all instances, closes database
srvctl stop instance -d <db_name> -i <inst_name>	Stops an instance
srvctl start service -d <db_name> -s <service_name>	Starts a service
srvctl stop service -d <db_name> -s <service_name>	Stops a service
srvctl status service -d <db_name>	Checks status of a service
srvctl status instance -d <db_name> -i <inst_name>	Checks an individual instance
srvctl status database -d <db_name>	Checks status of all instances
srvctl start nodeapps -n <node_name>	Starts gsd, vip, listener, and ons
srvctl stop nodeapps -n <node_name>	Stops gsd, vip and listener

Keep in mind that some resources will not start unless other resources are already online. We will now look at the general dependency list in greater detail.

There are three main background processes you can see when doing a ps –ef|grep d.bin.  They are normally started by init during the operating system boot process.  They can be started and stopped manually by issuing the command /etc/init.d/init.crs {start|stop|enable|disable}

1. /etc/rc.d/init.d/init.evmd
2. /etc/rc.d/init.d/init.cssd
3. /etc/rc.d/init.d/init.crsd

Once the above processes are running, they will automatically start the following services in the following order if they are enabled.  This list assumes you are using ASM and have a service set up for TAF/load balancing.

1. The nodeapps (gsd, VIP, ons, listener) are brought online.
2. The ASM instances are brought online.
3. The database instances are brought online.
4. Any defined services are brought online.

How To Change Look And Feel and Colors Of Oracle Applications 11i and R12

There are two system profile options that can be used to change look and feel. 

1. Java Look and Feel.
   Java Look and Feel can have two values: GENERIC or ORACLE.
   If set to GENERIC, then set Java Color Scheme to blank.
   Which will leave look and feel to generic.
   The Generic look and feel adheres to the native interface and color scheme of the current operating system.
   If set to ORACLE (or if the value is left blank), then the profile option 'Java Color Scheme' can be used to personalize the colors of your screen.
2. Java Color Scheme.
   If 'Java Look and Feel' is set to ORACLE (or if the value is left blank), then 'Java Color Scheme' can be set to the following values:
   
   • Blaf        (in R12: Swan)
   • Blue
   • Khaki
   • Olive
   • Purple
   • Red
   • Teal
   • Titanium

Do the following: 

1. Login to Oracle Applications as the System Administator responsibility.
2. Navigate to Profile > System.
3. Ensure that the Site display is checked.
4. Query up 'Java Color Scheme' for the profile.
5. Select the appropriate color under the Site column.
6. Save the selection.
7. Sign off and back on (or change responsibility) for your new color scheme to take effect.

Be aware that the 'Java Color Scheme' profile has no effect if 'Java Look and Feel' is set to GENERIC.

Oracle Core DBA Interview questions - Backup and Recovery

1.       Which types of backups you can take in Oracle?

2.       A database is running in NOARCHIVELOG mode then which type of backups you can take?

3.       Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4.       Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

5.       How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6.       You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

7.       Where should you place Archive logfiles, in the same disk where DB is or another disk?

8.       Can you take online backup of a Control file if yes, how?

9.       What is a Logical Backup?

10.   Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11.   Why do you take tablespaces in Backup mode?

12.   What is the advantage of RMAN utility?

13.   How RMAN improves backup time?

14.   Can you take Offline backups using RMAN?

15.   How do you see information about backups in RMAN?

16.   What is a Recovery Catalog?

17.   Should you place Recovery Catalog in the Same DB?

18.   Can you use RMAN without Recovery catalog?

19.   Can you take Image Backups using RMAN?

20.   Can you use Backupsets created by RMAN with any other utility?

21.   Where RMAN keeps information of backups if you are using RMAN without Catalog?

22.   You have taken a manual backup of a datafile using o/s. How RMAN will know about it?

23.   You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

24.   Which is more efficient Incremental Backups using RMAN or Incremental Export?

25.   Can you start and shutdown DB using RMAN?

26.   How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?

27.   You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?

28.   You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?

29.   How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?

30.   You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?

31.   You loss controlfile how do you recover from this?

32.    The current logfile gets damaged. What you can do now?

33.   What is a Complete Recovery?

34.   What is Cancel Based, Time based and Change Based Recovery?

35.   Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?

36.   Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?

37.   A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?

38.   How do you recover from the loss of a controlfile if you have backup of controlfile?

39.   Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?

40.   Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?

41.   Have you faced any emergency situation. Tell us how you resolved it?

42.   At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.

43.   What is Consistent Backup?

A Consistent backup is one in which the files being backed up contain all changes upto the same system change number (SCN)

44.   What is fractured Block?

Because the database continues writing to the file during an online backup, there is the possibility of backing up inconsistent data within a block. For example, assume that either RMAN or an operating system utility reads the block while database writer is in the middle of updating the block. In this case, RMAN or the copy utility could read the old data in the top half of the block and the new data in the bottom top half of the block. The block is a fractured block, meaning that the data in this block is not consistent.

45.   What are the steps to performing complete recovery on the whole database?

Mount the database

Ensure that all datafiles you want to recover are online

Restore a backup of the whole database or the files you want to recover

Apply online or archived redo logs, or a combination of the two

46.   What are the steps to performing complete recovery on a tablespace or datafile?

Take the tablespace or datafile to be recovered offline if the database is open

Restore a backup of the datafiles you want to recover

Apply online or archived redo logs, or a combination of the two

47.   What are the components of physical database structure of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

48.   What are the components of logical database structure of Oracle database?

There are tablespaces and database's schema objects.

49.   What is a tablespace?

A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

50.   What is SYSTEM tablespace and when is it created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

51.   Explain the relationship among database, tablespace and data file.

Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

52.   What is schema?

A schema is collection of database objects of a user.

53.   What are Schema Objects?

Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

54.   Can objects of the same schema reside in different tablespaces?

Yes.

55.   Can a tablespace hold objects from different schemes?

Yes.

56.   What is Oracle table?

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

57.   What is an Oracle view?

A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

58.   Do a view contain data?

Views do not contain or store data.

59.   Can a view based on another view?

Yes.

60.   What are the advantages of views?

- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.

- Hide data complexity.

- Simplify commands for the user.

- Present the data in a different perspective from that of the base table.

- Store complex queries.

61.   What is an Oracle sequence?

A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

62.   What is a synonym?

A synonym is an alias for a table, view, sequence or program unit.

63.   What are the types of synonyms?

There are two types of synonyms private and public.

64.   What is a private synonym?

Only its owner can access a private synonym.

65.   What is a public synonym?

66.   Any database user can access a public synonym.

67.   What are synonyms used for?

- Mask the real name and owner of an object. - Provide public access to an object - Provide location transparency for tables, views or program units of a remote database. - Simplify the SQL statements for database users.

68.   What is an Oracle index?

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

69.   How are the index updates?

Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

70.   What are clusters?

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

71.   What is cluster key?

The related columns of the tables in a cluster are called the cluster key.

72.   What is index cluster?

A cluster with an index on the cluster key.

73.   What is hash cluster?

A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

74.   When can hash cluster used?

Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

75.   What is database link?

A database link is a named object that describes a "path" from one database to another.

76.   What are the types of database links?

Private database link, public database link & network database link.

77.   What is private database link?

Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

78.   What is public database link?

Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

79.   What is network database link?

Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

80.   What is data block?

Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

81.   How to define data block size?

A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

82.   What is row chaining?

In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

83.   What is an extent?

An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

84.   What is a segment?

A segment is a set of extents allocated for a certain logical structure.

85.   What are the different types of segments?

Data segment, index segment, rollback segment and temporary segment.

86.   What is a data segment?

Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

87.   What is an index segment?

Each index has an index segment that stores all of its data.

88.   What is rollback segment?

A database contains one or more rollback segments to temporarily store "undo" information.

89.   What are the uses of rollback segment?

To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

90.   What is a temporary segment?

Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

91.   What is a datafile?

Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tablesand indexes is physically stored in the data files allocated for a database.

92.   What are the characteristics of data files?

A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

93.   What is a redo log?

The set of redo log files for a database is collectively known as the database redo log.

94.   What is the function of redo log?

The primary function of the redo log is to record all changes made to data.

95.   What is the use of redo log information?

The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

96.   What does a control file contains?

- Database name - Names and locations of a database's files and redolog files. - Time stamp of database creation.

97.   What is the use of control file?

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.