Oracle Architecture FAQ

Please download to get full document.

View again

of 8
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.


Document Related
Document Description
Describe Oracle architecture in brief. Answer The Oracle database has: - Logical layer: The components of the logical layer map the data to these physical components - Physical layer: The physical layer consists of the files that reside on the disk Tablespaces and Datafiles The database is divided into one or more logical pieces known as tablespaces. A tablespace is used to logically group data together The Database Schema: A collection of logical-structure objects, known as schema objects. Thes
Document Share
Document Tags
Document Transcript
  Describe Oracle architecture in brief. Answer  The Oracle database has:- Logical layer: The components of the logical layer map the data to these physical components- Physical layer: The physical layer consists of the files that reside on the diskTablespaces and DatafilesThe database is divided into one or more logical pieces known as tablespaces. A tablespace is used to logicallygroup data together The Database Schema: A collection of logical-structure objects, known as schema objects.These schema objects consist of structures such as:- tables,- clusters,- indexes,- views,- stored procedures,- database triggers, and- sequences.Segments, Extents, and Data BlocksWithin Oracle, the space used to store data is controlled by the use of logical structures. These structures consist of the following:  Data blocks--A block is the smallest unit of storage in an Oracle database. (contains header informationconcerning the block itself as well as the data)  Extents--Extents consist of data blocks.  Segments--A segment is a set of extents used to store a particular type of data.The Oracle Instance has:  User processes  Oracle background processes  The shared memoryThe Oracle Memory Structure contains:  The System Global Area (SGA)  The Data-Dictionary Cache  The Program Global Area (PGA) What is the function of SMON? Answer  The SMON background process performs all system monitoring functions on the oracle database.Each time oracle is re-started, SMON performs a warm start and makes sure that the transactions that were leftincomplete at the last shut down are recovered.SMON performs periodic cleanup of temporary segments that are no longer needed. Oracle architecture - August 28, 2008 at 17:00 PM by Amit Satpute   Explain different types of segment. Data segment, Index segment, Rollback segment andtemporary segment. Answer   There are four types of segments used in Oracle databases:- data segments- index segments- rollback segments- temporary segments Data Segments: There is a single data segment to hold all the data of every non clustered table in an oracle database. This datasegment is created when you create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command.Also, a data segment is created for a cluster when a CREATE CLUSTER command is issued.The storage parameters control the way that its data segment's extents are allocated. These affect the efficiency of data retrieval and storage for the data segment associated with the object. Index Segments: Every index in an Oracle database has a single index segment to hold all of its data. Oracle creates the indexsegment for the index when you issue the CREATE INDEX command. Setting the storage parameters directly affectsthe efficiency of data retrieval and storage. Rollback Segments Rollbacks are required when the transactions that affect the database need to be undone. Rollbacks are also neededduring the time of system failures. The way the roll-backed data is saved in rollback segment, the data can also beredone which is held in redo segment.A rollback segment is a portion of the database that records the actions of transactions if the transaction should berolled back. Each database contains one or more rollback segments. Rollback segments are used to provide readconsistency, to rollback transactions, and to recover the database. Types of rollbacks: - statement level rollback- rollback to a savepoint- rollback of a transaction due to user request- rollback of a transaction due to abnormal process termination- rollback of all outstanding transactions when an instance terminates abnormally- rollback of incomplete transactions during recovery. Temporary Segments: The SELECT statements need a temporary storage. When queries are fired, oracle needs area to do sorting andother operation due to which temporary storages are useful.The commands that may use temporary storage when used with SELECT are:GROUP BY, UNION, DISTINCT, etc. Explain SGA memory structures: Shared Pool, Database buffer Cache, Redo log Cache,Large Pool Java Pool. Answer  SGA (System Global Area) is a dynamic memory area of an Oracle Server. In SGA,the allocation is done in granuels.The size of the SGA is dependent on SGA_MAX_SIZE parameter.The memory structures contained by SGA are:- Shared Pool - this memory structure is divided into two sub-structures which are Library Cache and Data Dictionary Cache for storing recently used PL/SQL statements and the recent data definitions. The maximum size of the Shared Pooldepends on the SHARED_POOL_SIZE parameter. Database Buffer Cache – This memory structure improves the performance while fetching or updating the recently used data as it stores therecently used datafiles. The size of this block is decided by DB_BLOCK_SIZE. Redo Log Buffer – This memory structure is used to store all the changes made to the database and it's primarily used for the data  recovery purposes. The size of this block is decided by LOG_BUFFER. Java Pool – This memory structure is used when Java is installed on the Oracle server. Size that can be used is stored inparameter named JAVA_POOL_SIZE. Large Pool – This memory structure is used to reduce the burden of the Shared Pool, as the Session memory for the SharedServer, as the temporary storage for the I/O and for the backup and restore operations or RMAN. Parameter thatstores the maximum size is LARGE_POOL_SIZE. What is SQL Loader? Explain the files used by SQL Loader to load file. i.e Loader control file,Input datafile, Log File, Bad File, Discard file. Answer  SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads.When a control file is fed to an SQL*Loader, it writes messages to the log file, bad rows to the bad file and discardedrows to the discard file. Control file The SQL*Loader control file contains information that describes how the data will be loaded. It contains the tablename, column datatypes, field delimiters, etc.controlfile.sql should be used to generate an accurate control file for a given table. Log File The log file contains information about the SQL*loader execution. It should be viewed after each SQL*Loader job iscomplete. Explain the methods provided by SQL Loader. Answer  Conventional Path LoadDirect Path Load What is the physical and logical structure of oracle? Answer Logical Database structures Logical structures include tablespaces, schema objects, data blocks, extents and segments. Tablespaces   Database is logically divided into one or more tablespaces. Each tablespace creates one or more datafiles tophysically store data. Schema objects Schema objects are the structure that represents database's data. Schema objects include structures such as tables,views, sequences, stored procedures, indexes, synonyms, clusters and database links. Data Blocks Data block represents specific number of bytes of physical database space on disk. Extents An extent represents continuous data blocks that are used to store specific data information. Segments A segment is a set of extents allocated for a certain logical structure. Physical database structure The physical database structure comprises of datafiles, redo log files and control files  Datafiles Datafiles contain database's data. The data of logical data structures such as tables and indexes is stored in datafilesof the database. One or more datafiles form a logical unit of database storage called a tablespace. Redo log files The purpose of these files is to record all changes made to data. These files protect database against failures. Control files Control files contain entries such as database name, name and location of datafiles and redo log files and time stampof database creation.   Explain the categories of oracle processes i.e. user, data writing processes, loggingprocesses and monitoring processes.  User process – User process is used in invocation of application software.  Data writing process - A database writer process is used to write buffer content into a datafile. They arespecifically used to write “dirty block” to data files from the buffer.  Logging processes - Log writer is used to write the redo log buffer from system global area to online redolog file. Only those redo entries are written hat have been copied into the buffer since the last time it wrote.  Monitoring process - this can be either a system monitor process or a process monitor process. Systemmonitor process is mainly used for crash recovery and cleaning up of temporary segments. Process monitor is used to clean all resources acquired by a failed process. What are the Back ground processes in Oracle? There are a number of background processes running. Each process is used for a specific job.  SMON – system monitor process is used to clean temporary segments and crash recovery of a crashedinstance.  PMON – process monitor is used to clean all resources acquired by a failed process.  DBWR - Database writer is used to write “dirty block” to data files from the buffer.  LGWR - Log writer is used to write the redo log buffer from system global area to online redo log file.  MMAN – Memory manager process is used to manage the memory.  ARCn - Archiver 0-9 is used for writing the online redo log files to the mentioned archive log destination after a log switch has occurred.  CKPT – checkpoint process using the checkpoints information signals the synchronization of all databasefiles. Used for database consistency and faster database recovery.  LCK0 -9 – LOCK process is used to lock shared resources for manages the global enqueue requests andthe cross-instance broadcast Explain the areas of memory used by oracle, i.e. Software code area, system global area(SGA), program global area(PGA), sort area. Software area code: - It is a protected location that is used to store oracle code that is supposed to be run. Thelocation is different from users' programs. The software area code is read only and can be shared or nonshared.System global area: - SGA is used to store data and control information of an Oracles database instance whenstarted. Each instance has its own SGA. SGA is read write. It comprises data structures like Redo buffer, Java pool,Streams pool etc.Program global area:- PGA contains information about server process. It is a non shared memory when a server process is started. Each process has an exclusive PGA.Sort area: - SQL work area is typically used for complex queries by memory-intensive operators such as Sort area. Asort operator is used to perform the in-memory sort of a set of rows Explain the types of data files used by the oracle RDBMS.
Search Related
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks