{"id":2911,"date":"2025-04-02T20:11:52","date_gmt":"2025-04-02T17:11:52","guid":{"rendered":"https:\/\/dataera.com.tr\/blog\/2025\/04\/02\/oracle-veritabani-mimarisi\/"},"modified":"2025-05-06T03:39:29","modified_gmt":"2025-05-06T00:39:29","slug":"oracle-database-architecture","status":"publish","type":"post","link":"https:\/\/dataera.com.tr\/en\/oracle-database-architecture\/","title":{"rendered":"Oracle Database Architecture"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"2911\" class=\"elementor elementor-2911 elementor-1\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-f3c65d0 elementor-section-full_width elementor-section-height-default elementor-section-height-default\" data-id=\"f3c65d0\" data-element_type=\"section\" data-e-type=\"section\" data-settings=\"{&quot;background_background&quot;:&quot;classic&quot;}\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-wider\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-65ae0e0 animated-slow elementor-invisible\" data-id=\"65ae0e0\" data-element_type=\"column\" data-e-type=\"column\" data-settings=\"{&quot;background_background&quot;:&quot;gradient&quot;,&quot;animation&quot;:&quot;fadeInRight&quot;}\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b37cf84 elementor-widget elementor-widget-menu-anchor\" data-id=\"b37cf84\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"menu-anchor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div class=\"elementor-menu-anchor\" id=\"images_included\"><\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<section class=\"elementor-section elementor-inner-section elementor-element elementor-element-91ab968 elementor-section-full_width elementor-reverse-tablet elementor-reverse-mobile elementor-section-height-default elementor-section-height-default\" data-id=\"91ab968\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-wider\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-inner-column elementor-element elementor-element-199d047\" data-id=\"199d047\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-c8721cd animated-fast elementor-invisible elementor-widget elementor-widget-text-editor\" data-id=\"c8721cd\" data-element_type=\"widget\" data-e-type=\"widget\" data-settings=\"{&quot;_animation&quot;:&quot;fadeIn&quot;,&quot;_animation_mobile&quot;:&quot;none&quot;}\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<p><b>Database<\/b> and <b>instance Oracle database server<\/b> are the concepts that constitute it. (There may be more than one instance on the same server, each of which takes the memory allocated to it.) <b><\/b><\/p><p><b>Instance<\/b> = Memory (SGA) + background process. When the instance is started <b><i>(sql>startup)<\/i><\/b>, it takes the allocated memory from the system, starts the background processes, and begins serving users.<\/p><p>Oracle has two structures: <b>physical and logical<\/b>. The physical structure can be changed without affecting the logical one. (Such as changing the name or location of a datafile) <b><\/b><\/p><p><b>Database<\/b> = the location where datafiles, control files, and online redologs are stored.<\/p><p>Oracle tries to perform operations in memory as much as possible. There is a significant cost difference between operations performed on disk and those done in memory. When Oracle starts, it divides the memory it receives into parts for different purposes. Some hold execution plans, others hold data, and share this with users.<\/p><p><b>Areas that can be changed while DB is open<\/b><\/p><ul><li>Database Buffer Cache<\/li><li>shared pool<\/li><li>large pool<\/li><li>java pool<\/li><li>streams pool<\/li><\/ul><p><b>memory_target<\/b> and <b>memory_max_target<\/b> parameters can be configured to enable Oracle to manage memory automatically. Additionally, by setting the above areas manually, we can prevent their size from falling below a certain value.<br \/><b>System Global Area (SGA) \/ Shared Global Area;<\/b><\/p><p>It consists of memory groups that contain data (shared memory structure). Both read and write operations are performed on data in the SGA. This data is shared among users.<\/p><p>\u2013 Each instance has its own SGA.<\/p><p>When the instance is started, it allocates RAM from the system, and when shut down, it releases it.<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">Select * from V$SGA_DYNAMIC_COMPONENTS ;\nShow parameter memory ; (11G)\nShow parameter sga ; (9i, 10G, 11G)<\/pre><p><strong>Program Global Area (PGA);<\/strong><br \/>Unlike the SGA, it is not shared. It stores data and control information for server and background processes. It is created when a server or background process starts. Each process has its own PGA.<br \/>(The goal is to keep cache_hit_percent above 90%.)<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">Show parameter pga ;(9i, 10G, 11G)\nalter system set pga_aggregate_target=1G scope=both;<\/pre><p>Operations like Order by, group by, and rollup are attempted in the PGA first; if it\u2019s not enough, they are done in the temp tablespace. Each user gets a server process and a dedicated PGA area.<\/p><p><strong>Bind Variable<\/strong><\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select * from employees where dept_id=:b1;<\/pre><p><strong>dept_id=b1<\/strong> Different users can assign different values to b1, such as 5 or 7. These values are called bind variables and are stored in the PGA.<\/p><p>In dedicated mode, SGA and PGA are separate; in shared mode, PGA is included in the SGA. Large pool is an optional area and can be configured.<\/p><p>Queries that pass through the PGA in one go are called \u201coptimal executions.\u201d If passed in two steps, it\u2019s \u201cone pass executions.\u201d More than two is called \u201cmultipass executions.\u201d The goal is to reduce multipasses (the red ones).<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select name from v$fixed_table\nwhere name like '%PGA%' ;<\/pre><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select name from v$fixed_table\nwhere upper(name) like '%ADVICE%' ;<\/pre><p>All tables that start with v$ are listed in v$fixed_table.<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select * from V$DB_CACHE_ADVICE ;<\/pre><div>The memory amount in SIZE_FOR_ESTIMATE where <strong>ESTD_PHYSICAL_READS<\/strong> drops is noted.<\/div><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select * from v$shared_pool_advice ;<\/pre><div>ESTD_LC_TIME_SAVED shows the time gained in library cache. As more RAM is allocated to LC, time savings increase, but only up to a point.<\/div><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select * from v$pga_target_advice ;<\/pre><div><strong>ESTD_PGA_CACHE_HIT_PERCENTAGE<\/strong> should be kept at or above 90%.<\/div><p>Within the SGA, there is a shared area for users. It stores copies of data blocks read from datafiles.<br \/>When a user runs a <b><i>select<\/i><\/b> query and wants to read data, the DBC (Database Buffer Cache) is checked first. If present (<b>cache hit<\/b>), data is served from DBC.<\/p><p>If not (<b>cache miss<\/b>), data is read from the datafile and one copy goes to the DBC and the other to the user. To write data to the DBC, there must be available space. If not, Oracle frees up space using a mixed algorithm based on LRU (Least Recently Used). Simply put, frequently used data stays in memory (not FIFO).<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select * from employees where employee_id = 1000 ;<\/pre><p>In a SQL statement like the one above, not the entire \u201cemployees\u201d table is copied into DBC\u2014only the portion required by the \u201cwhere\u201d clause.<br \/><b>spfile\/pfile<\/b><\/p><p>The buffer cache appears as db_cache_size.<br \/>Note: If the buffer cache wait event is high, the buffer cache size should be increased.<\/p><p>The ratio of logical reads to physical reads should be above 90%. Physical reads should be minimized.<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select id, name, physical_reads, physical_writes\nfrom v$buffer_pool_statistics;<\/pre><p>Tables with heavy read\/write operations can be kept in the buffer cache persistently.<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">alter table hr.employees storage(buffer_pool keep);<\/pre><p>To revert:<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">alter table hr.employees storage(buffer_pool default);<\/pre><p>Changes made to the database (insert, update, delete, create, alter, drop) are logged. These logs are essential for recovery when needed. It uses a circular operation logic. The LGWR background process writes information to redolog files. (In 9i, the default size was 512K, which caused errors in the alert log file; increasing it to 5\u201310MB depending on the system is advisable.)<br \/>log_buffer = redo log buffer<\/p><div>It is a part of the SGA;<\/div><ul><li>data dictionary cache<\/li><li>library cache<\/li><\/ul><div><strong>Data Dictionary Cache:<\/strong> Area where tables and views containing structural and user-related reference information of the database are stored. It is frequently accessed during SQL parsing, so keeping it in memory is important for performance.<\/div><div><strong>Library Cache:<\/strong> (shared SQL area) Stores parsed SQL statements and their execution plans. When two users execute the same SQL, Oracle recognizes this and skips re-parsing by using the information from the library cache\u2014this is called a soft parse. New SQLs allocate memory from the shared pool depending on complexity.<\/div><div>The same goes for PL\/SQL code\/program units (procedures, functions, packages, anonymous blocks, triggers).<\/div><div>It uses the LRU (Least Recently Used) algorithm.<\/div><div>When statistics are collected (DBMS_STATS), the shared pool is flushed. (There are differences in 9i, 10g, 11g)<\/div><div>If an object is modified (alter), it must be reparsed because the SQL statement becomes invalid.<\/div><div>If the global DB name is changed<\/div><div>If a DBA manually runs \u201calter system flush shared_pool\u201d, the shared pool is flushed.<\/div><div>BC (Buffer Cache) caches data, while the shared pool caches execution plans of queries.<\/div><div>Checks are performed on query structure and user access rights to table columns.<\/div><div>Then the parsing process begins, deciding whether to use an index or do a full table scan.<\/div><div>If the same query is sent again, these steps are skipped by reusing existing parsed data.<\/div><div>You can query v$sgastat view for details.<\/div><div>This is an optional area.<\/div><div>Used in shared mode databases and applications that use Oracle XA interface.<\/div><div>Activated when additional memory is required for I\/O server processes or backup and restore operations.<\/div><div>If large pool is not configured, RMAN uses the shared pool, which negatively affects performance.<\/div><div>Used in parallel SQL operations on multi-processor systems (via hints).<\/div><div>If the server is running in shared mode, the large pool size should be increased accordingly.<\/div><div><strong>Java Pool:<\/strong><\/div><div>Area used for Java code and JVM data. The Java_pool advisor provides statistics on how library cache usage for Java affects parse rates when the Java pool is resized. (Requires statistics_level = typical or higher)<\/div><div><strong>Streams Pool:<\/strong><\/div><div>Area allocated from SGA for replication purposes. (Used in structures like Data Guard and Golden Gate)<\/div><div>It uses the LRU (Least Recently Used) algorithm.<\/div><div>When statistics (DBMS_STATS) are gathered, the shared pool is flushed. (Differences in 9i, 10G, and 11G can be explained)<\/div><div>If an object is altered, a re-parse is required because the SQL statement becomes invalid.<\/div><div>If the global DB name is changed<\/div><div>If the DBA manually runs \u201calter system flush shared_pool\u201d, it triggers a flush.<\/div><div>BC (Buffer Cache) caches data; shared pool caches execution plans of queries.<\/div><div>It performs query validation and column access privilege checks<\/div><div>Then proceeds to parsing,<\/div><div>Determines whether to use an index or perform a full table scan.<\/div><div>If the same query arrives again, it doesn\u2019t repeat the same operations.<\/div><div>For more info, you can query the v$sgastat view.<\/div><div><strong>This is an optional area.<\/strong><\/div><div>It is used in DBs operating in shared mode or when Oracle XA interface is used<\/div><div>It is activated when additional memory is needed for I\/O server processes<\/div><div>and for backup and restore operations.<\/div><div>If large pool is not configured, RMAN uses the shared pool, which negatively impacts performance.<\/div><div>In systems with multiple processors, it is used in parallel SQL operations (via hints).<\/div><div>If shared server mode is enabled, large pool should be configured with a larger size.<\/div><div><strong>Java Pool:<\/strong><\/div><div>Area used for Java code and JVM data. Java_pool advisor provides statistical information about Java use of the library cache and how changing the Java pool affects parsing efficiency (when statistics_level = typical or higher).<\/div><div><strong>Streams Pool:<\/strong><\/div><div>Area reserved from the SGA for replication. (Used in setups like Dataguard, GoldenGate)<\/div><div>Let\u2019s view current SGA values.<\/div><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select component, current_size\/1048576 , min_size\/1048576 , max_size\/1048576 ,\nlast_oper_time from v$sga_dynamic_components ;<\/pre><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select * from v$sgainfo ;<\/pre><div>Note: The value like orcl._db_cache_size in the pfileORCL.init file shows the memory allocated at that moment when SGA management is automatic. <strong>Automatic memory management in 11G<\/strong><\/div><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;\nALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;\nALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;\nALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;\nSHUTDOWN IMMEDIATE;\nSTARTUP;<\/pre><div><strong>Automatic memory management in 10G<\/strong><\/div><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">ALTER SYSTEM SET sga_max_size=5G SCOPE=SPFILE;\nALTER SYSTEM SET sga_target=5G SCOPE=SPFILE;\nALTER SYSTEM SET pga_aggregate_target=1G SCOPE=SPFILE;\nSHUTDOWN IMMEDIATE;\nSTARTUP;<\/pre><p>Oracle processes can be divided into two main groups:<\/p><p>1\u2013 Processes that run when an application is executed or when code is run through a tool (Java app \/ <a href=\"http:\/\/sysdba.org\/sqlplus\/?lang=tr\" target=\"_blank\" rel=\"noopener\">sqlplus<\/a> or Toad etc.)<\/p><p>2\u2013 Processes that Oracle Database runs on its own (server and background processes)<\/p><p>When a user runs an application like sqlplus or Toad, Oracle creates a user process and then a server process to run the code from the user process. In addition, there are background processes belonging to the instance. Background processes handle memory management, asynchronous disk read\/write, and other essential tasks.<\/p><p><b>The structure of processes<\/b> varies depending on database configuration, operating system, and Oracle database options. For example, in <b>non-archive log<\/b> mode, the archiver process does not run. In dedicated server mode, one server process is created per user process.<\/p><p>In shared server mode, a server process is not created for each user process. The dispatcher holds session requests in a pool and executes multiple user requests using fewer server processes. Sometimes, the application and the Oracle database reside on the same machine; in this case, Oracle can use the same process to minimize system load.<\/p><p>The server process corresponding to the user process performs one or more of the following:<br \/>\u2013<b>parse<\/b> and execute the <b>SQL<\/b> statement<br \/>\u2013 Read <b>data blocks<\/b> from datafiles and copy them into <b>SGA<\/b><br \/>\u2013 Return the result to the user<\/p><div><strong>Background Process:<\/strong><\/div><div>In instances that do not use RAC or ASM, the background processes include:<\/div><ul><li>Database writer process (DBWn)<\/li><li>Log writer process (LGWR)<\/li><li>Checkpoint process (CKPt)<\/li><li>System Monitor (SMON)<\/li><li>Process Monitor (PMON)<\/li><li>Recover process (RECO)<\/li><li>Job Queue process<\/li><li>Archiver Process (ARCn)<\/li><li>Queue Monitor Process (QMNn)<\/li><\/ul><div>Information about background processes can be obtained from the v$bgprocess view.<\/div><div>Some BG processes start automatically when the instance starts, while others run as needed.<\/div><div>To view background processes:<\/div><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select name, description from v$bgprocess;\nselect paddr, name, description\nfrom v$BGprocess\nwhere paddr != '00';<\/pre><p>The Database Writer (DBW) process is responsible for writing <b>dirty<\/b> data from the Database Buffer Cache to disk. (Dirty data refers to data that has been modified and committed.) For most systems, a single DBW process is sufficient, but more can be added if necessary. If your database performs a lot of write operations, adding DBW processes can improve write performance. However, on single-CPU systems, increasing the number of DBW processes is usually not helpful.<\/p><p>When a change is made to a buffer area in the DBC and a commit is issued, it is marked as dirty and added to the LRUW (LRU write) list. This list is maintained in SCN order. This order is aligned with the redo sequence to be written into redologs. When the DBC runs out of space (hits a threshold), the DBW process writes dirty buffers to datafiles based on the LRUW list order.<\/p><p>The LRU algorithm keeps frequently accessed blocks in the buffer cache. Blocks written to disk are likely to be those accessed less frequently.<\/p><p>To see the System Change Number (SCN):<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select current_scn from v$database; -- In 10G\nselect dbms_flashback.get_system_change_number from dual; -- In 9i<\/pre><p>LGWR is responsible for writing data from the redo log buffer to the redo log files. It performs write operations in the following cases:<\/p><ul><li>When a transaction is committed (sometimes before the commit if more redo buffer is needed)<\/li><li>When 1\/3 of the redo log buffer is full<\/li><li>Before DBW writes to disk<\/li><li>Every 3 seconds<\/li><\/ul><p>When DBW writes dirty buffers from the DBC to disk, the checkpoint process writes information about the modified blocks to control files and the headers of datafiles. The purpose of a checkpoint is to determine the point in the redolog files where instance recovery should begin.<\/p><p>This is called the Checkpoint Position.<br \/>It ensures that modified data blocks are regularly written from memory to disk, reducing the risk of data loss if an instance or database failure occurs.<br \/>It also minimizes the time needed for instance recovery.<br \/>During shutdown, it ensures that all committed data is written to disk.<\/p><p>In summary, checkpoint information is written into control files by the checkpoint process. Oracle reads from control files at startup. Important checkpoint contents include: SCN (System Change Number), the position in the redo log file where recovery starts, and log metadata.<br \/>During <b>instance startup<\/b>, if needed, it performs <b>recovery<\/b> by aligning the SCN in datafiles and control files, then opens the database. It rolls forward committed changes and rolls back uncommitted ones.<\/p><p>It cleans up unused <b>temporary<\/b> segments. Other processes can trigger SMON if necessary.<\/p><p>If a user process terminates unexpectedly, PMON handles recovery for that process. It releases the buffer cache and other resources assigned to the user. For example, if there are active transactions and the session is aborted, locks are released and the process ID is removed.<\/p><p>It releases locks on users\u2019 uncommitted rows (such as when the DB shuts down unexpectedly).<br \/>PMON periodically checks <b>dispatcher<\/b> and <b>server<\/b> processes and restarts them if they\u2019ve crashed (but not if they were shut down intentionally).<br \/>It registers <b>instance<\/b> and <b>dispatcher<\/b> info with the <b><a href=\"http:\/\/sysdba.org\/listener\/?lang=tr\" target=\"_blank\" rel=\"noopener\">listener<\/a><\/b>.<br \/>Like SMON, PMON can also be triggered by other processes when needed.<\/p><p>In multi-instance environments, RECO handles failed distributed transactions by connecting to the other database and resolving the issue automatically.<\/p><p>If the DB is in archive log mode, ARCn runs and archives redo logs to a specified path after each log switch. If standby servers exist, it also transfers logs to them. (More in Data Guard topic)<\/p><p><strong>Other Processes<\/strong><br \/><b>MMON (Manageability Monitor Process):<\/b> Handles alerts, statistics gathering, and management tasks.<br \/><b>MMNL (Lightweight Manageability Monitor Process):<\/b> Performs lightweight manageability tasks like session history tracking.<br \/><b>MMAN (Memory Manager Process):<\/b> Automatically handles memory distribution between components based on need.<br \/><b>RBAL (Rebalance Process):<\/b> Balances disks in ASM environments.<br \/><b>CJQ0 (Job Queue Coordinator):<\/b> Executes scheduled jobs (like PL\/SQL procedures).<br \/><b>QMNC (AQ Coordinator Process):<\/b> Monitors advanced queues and supports Oracle Streams.<\/p><p>As mentioned before, when a <b>query<\/b> is received, Oracle checks if the block is in the buffer cache. If yes, it returns it to the user; if not, it reads from the datafile, loads it into the buffer cache, and then returns it.<\/p><p>Buffer states can be:<br \/><b>pinned:<\/b> Multiple users want to write to the same block; users wait for the lock to release.<br \/><b>clean:<\/b> Not in use and subject to replacement if not accessed again.<br \/><b>free or unused:<\/b> Initial state when the instance has just started.<br \/><b>dirty:<\/b> Block has been modified and committed, waiting to be written to disk.<\/p><p>A phone line between two people means a connection exists; when one calls and the other picks up, a session begins. (A server process starts for each client process.)<\/p><p>1\u2013 <b>Instance<\/b> is started, memory is allocated from the OS, background processes are launched, and the system becomes ready for user access.<br \/>2\u2013 The user starts an <b>application<\/b> or attempts to connect via an Oracle tool.<br \/>3\u2013 The server uses the <b>listener<\/b> service to make the instance accessible over the network, receives the connection request, and creates a dedicated server process.<br \/>4\u2013 The user executes a <b>DML<\/b> or <b>SQL<\/b> command, e.g., updates an employee record.<br \/>5\u2013 The server process checks the <b>shared<\/b> pool to see if the same SQL was previously executed. If yes, it verifies user permissions and reuses the plan; if not, it parses the SQL.<br \/>6\u2013 If the data block exists in <b>buffer cache<\/b>, it is read from there; otherwise, it is retrieved from datafiles.<br \/>7\u2013 The server process modifies data in the <b>SGA<\/b>.<br \/><b>LGWR<\/b> writes the changes to redologs.<br \/>Later, DBW writes changes permanently to datafiles.<br \/>8\u2013 If the transaction succeeds, the server process returns success via the network; otherwise, it returns an error.<br \/>9\u2013 Background processes monitor and assist throughout the entire flow (e.g., ARCn archives logs after a log switch, manages user transactions, prevents conflicts, etc.)<\/p><p><b>Control Files:<\/b> Store identity information about the database. Each instance has at least one control file. It is recommended to have at least two, placed on different physical disks.<br \/>Control files contain datafile and redolog names\/paths, current log sequence numbers, backup set details, and the \u201csystem change number\u201d (SCN). These are binary files and cannot be modified manually. See \u201cControl File Management\u201d for more details.<\/p><p><b>DataFiles:<\/b> Store user objects. A datafile is the physical equivalent of a tablespace. One datafile belongs to one tablespace. One tablespace can have many datafiles (except Big File Tablespace). See <a href=\"http:\/\/sysdba.org\/tablespace-yonetimi\/?lang=tr\" target=\"_blank\" rel=\"noopener\">Tablespace Management<\/a> for more.<\/p><p><b>Online Redo Log Files:<\/b> Store transaction data. Default count is 3, minimum is 2. Can be increased as needed. It is highly recommended to place members on different disks. They operate in a circular manner.<\/p><p><b>Parameter File:<\/b> Also called startup file. It is the first file Oracle reads on startup to determine memory allocation and control file location. It is binary but can be copied to a text file using \u201ccreate spfile from pfile\u201d.<\/p><p><b>BackUp Files:<\/b> Export, DBF, and RMAN backup files. There are three backup types in Oracle:<br \/>1\u2013 Export (logical backup)<br \/>2\u2013 Cold Backup (user-managed; database is shut down and files are copied)<br \/>3\u2013 RMAN backup (primary tool)<\/p><p><b>Archive Redo Log Files:<\/b> In archive log mode, Oracle copies online redo logs to archive logs. These allow point-in-time recovery.<\/p><p><b>Password File:<\/b> Allows access to the DB even when it is shut down, using an external file for authentication. Can be recreated if needed.<\/p><p><b>Alert Log:<\/b> A file that logs everything happening in the database, including errors. DBAs should monitor this regularly.<\/p><p><b>Trace Files:<\/b> Contain more detailed error information. If SQL tracing is enabled, trace files include this data too.<\/p><p><b>Schema<\/b> = User (object catalog) (equivalent of database in MSSQL and MySQL)<\/p><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select Owner, table_name, tablespace_name from all_tables where owner = 'SCOTT'<\/pre><pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">select Owner, index_name, tablespace_name from all_indexes where owner = 'SCOTT'<\/pre><p><b>Database<\/b> = Consists of tablespaces<br \/><b>Tablespace<\/b> = A logical structure that contains segments<br \/><b>Segment<\/b> = Made of extents<br \/>Data segments store user tables\/indexes and form tablespaces. (e.g., index segment)<br \/>Undo segments are used for read consistency and form the undo tablespace.<br \/><b>Extent<\/b> = Made of blocks<br \/><b>Datablock<\/b> = The smallest unit in Oracle where data is physically stored<br \/>[bash]$dumpe2fs \/dev\/sda3 | grep -i \u2018Block size\u2019<br \/>Block size: 4096[\/bash]<\/p><p>Oracle databases are logically divided into tablespaces:<\/p><ul><li>system<\/li><li>sysaux<\/li><li>user tbs (big\/small)<\/li><li>undo tbs<\/li><li>temporary<\/li><\/ul><p><b>System Tablespace:<\/b> Automatically created with the DB. Mandatory for all DBs. Contains data dictionary and core system structures. Cannot be taken offline.<\/p><p><b>SysAux Tablespace:<\/b> Introduced in 10G to support the system tablespace. Stores AWR statistics.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Database and instance are the fundamental components that make up an Oracle database server. (There can be multiple instances on the same server, and each instance allocates its own dedicated memory.) &hellip; <a href=\"https:\/\/dataera.com.tr\/en\/oracle-database-architecture\/\">Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":2328,"comment_status":"closed","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"footnotes":""},"categories":[52],"tags":[],"class_list":["post-2911","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-genel-en"],"_links":{"self":[{"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/posts\/2911"}],"collection":[{"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/comments?post=2911"}],"version-history":[{"count":9,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/posts\/2911\/revisions"}],"predecessor-version":[{"id":2945,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/posts\/2911\/revisions\/2945"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/media\/2328"}],"wp:attachment":[{"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/media?parent=2911"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/categories?post=2911"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dataera.com.tr\/en\/wp-json\/wp\/v2\/tags?post=2911"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}