Refreshing VS Cloning

Refreshing VS Cloning an e-Business Suite Environment

What is Refreshing?

A refresh is where the data in the target environment has been synchronized with a copy of production. This is done by taking a copy of the production database and restoring it to the target environment.

What is Cloning?

Cloning means that an identical copy of production has been taken and restore to the target environment. This is done by taking both a copy of the production database as well as all of the application files.

When should you Clone or Refresh?

There are a couple of scenarios when cloning should be performed:

1. Building a new environment.

2. Patches or other configuration changes have been made to the target environment so that they are now out of sync.

3. Beginning of development cycles. Before major development efforts take place, its wise to re-clone dev, test environments so that your 100% positive that the environments are in sync.

There is only one scenario in which you should refresh an environment:

1. Your 100% confident that the environments are in sync and need an updated copy of the production data in order to reproduce issues.

Technically, if proper change control processes are being followed, test and production environments should be identical. So in the case of test, you should be able to get away with performing refreshes. However, to ease concerns and for comfort levels, test environments are usually re-cloned at the beginning of new development cycles as well.

Oracle Data Guard Interview Questions & Answers

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

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.

Linux interview questions for DBA

Linux interview questions for DBA

1.       How do you see how many instances are running?

In unix, you can use the command line
% ps -ef|grep ora_

2.       How do you automate starting and shutting down of databases in Unix?

Automatic startup can be done with /etc/oratab/ entyr of the linux machine.
Automatic shutdown we can't configure in the entry.

3.       You have written a script to take backups. How do you make it run automatically every week?

corntab

4.       What is OERR utility?

The oerr utility (Oracle Error) is provided only with Oracle databases on  UNIX  platforms.  oerr is not an executable, but instead, a shell script that retrieves messages from installed message files. Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. This utility is very useful as it can extract OS-specific errors that are not in the generic Error Messages and Codes Manual.

5.       How do you see Virtual Memory Statistics in Linux?

vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity.

6.       How do you see how much hard disk space is free in Linux?

df -lh

7.       What is SAR?

The sar command writes to standard output the contents of selected cumulative activity counters in the operating system

8.       What is SHMMAX?

SHMMAX is the maximum size of a shared memory segment on a Linux system

9.       Swap partition must be how much the size of RAM?

Your swap partition should be at least as big as your RAM size. However it should be double the size of RAM

10.   What is DISM in Solaris?

DISM Dynamic Intimate Shared memory which is used to support oracle in Solaris Envirnoment DISM is only supported from Solaris 9 and above version. On Solaris 9 systems, dynamic/pageable ISM (DISM) is available. This enables Oracle Database to share virtual memory resources between processes sharing the segment, and at the same time, enables memory paging. The operating system does not have to lock down physical memory for the entire shared memory segment.

11.   How do you see how many memory segments are acquired by Oracle Instances?

sga

pga

Db_buffer_cache

Log_buffer_cache

12.   How do you see which segment belongs to which database instances?

13.   What is VMSTAT?

vmstat reports information about processes, memory, paging, block IO, traps, and cpu activity.

14.   How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?

There are two methods to configure the Kernel parameters in RHEL

1. by using the command "sysctl -w =

The above command will change the kernel parameters on the fly but the changes are not persistent with system reboots. That is why people always choose the second method to make changes to kernel parameters

2. By editing the file "/etc/sysctl.conf" file

 A. Edit the file "/etc/sysctl.conf" by adding the parameters along with values

B. execute "/sbin/sysctl -p" to make sure that the changes are made using the values inside the above mentioned file.

The advantage with the second method is that the changes are persistent with system reboots.

15.   How do you remove Memory segments?

To remove the shared memory segment, you could copy/paste shmid and execute:

$ ipcrm shm 32768

Another approach to remove shared memory is to use Oracle's sysresv utility.

What is the difference between Soft Link and Hard Link?Soft link:-This is a Symbolic link between files. The actual file or directory must be residing at any available partitions of the harddisk Soft Link is just a shortcut (in windows terms) or link created with a new file name at the working directory or at current working partition of hard disk. Even when you don't require it you can confidently delete this soft link as it doesn't remove the actual file or directory. The reason is the actual file or diretcory's inode is different from the softlink created file's inode in any unix system. Hard link:-It is the replica of the actual file or directory which must be residing at any available partitions. This is a duplicate file copy of it's orginal which can be created at current working partition.When we remove or delete this hardlink it removes the original file or directory too.The reson is they share the same inode in any unix file system.

16.   What is stored in oratab file?

This file is used by ORACLE utilities.  It is created by root.sh and updated by the Database Configuration Assistant when creating a database.
A colon, ':', is used as the field terminator.  A new line terminates the entry.  Lines beginning with a pound sign, '#', are comments.
Entries are of the form:
                $ORACLE_SID:$ORACLE_HOME::
The first and second fields are the system identifier and home directory of the database respectively.  The third filed indicates to the dbstart utility that the database should , "Y", or should not, "N", be brought up at system boot time.
Multiple entries with the same $ORACLE_SID are not allowed.

17.   How do you see how many processes are running in Unix?

ps -e|cut -d " " -fname|wc -l

18.   How do you kill a process in Unix?

Linux and all other UNIX like oses comes with kill command. The command kill sends the specified signal (such as kill process) to the specified process or process group. If no signal is specified, the TERM signal is sent.  Kill process using kill command under Linux/UNIX. kill command works under both Linux and UNIX/BSD like operating systems.

19.   Can you change priority of a Process in Unix?

As system administrator you can use the renice command to change the priority of a process all processes of a user or all processes belong to a group of users. The renice command has the form

/etc/renice priority [ [ -p ] pid ... ] [ [ -g ] pgrp ... ] [ [ -u ] user

Oracle Core DBA Interview questions - Backup and Recovery

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.