Tales From A Lazy Fat DBA

Fan of Oracle DB & Performance, PostgreSQL & Cassandra … \,,/

  • Likes

    • 227,096
  • Archives

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘Architecture’

Redo Logs

Posted by FatDBA on December 7, 2012

Redo Log Files are used in a circular fashion.

  • One log file is written in sequential fashion until it is filled, and then the second redo log begins to fill.  This is known as a Log Switch.
  • When the last redo log is written, the database begins overwriting the first redo log again.

image008

 

  • The Redo Log file to which LGWR is actively writing is called the current log file.
  • Log files required for instance recovery are categorized as active log files.
  • Log files no longer needed for instance recovery are categorized as inactive log files.

·        Active log files cannot be overwritten by LGWR until ARCn has archived the data when archiving is enabled.

 

Log Writer Failure

What if LGWR cannot write to a Redo Log File or Group?  Possible failures and the results are:

  1. At least one Redo Log File in a Group can be written – Unavailable Redo Log Group members are marked as Invalid, a LGWR trace file is generated, and an entry is written to the alert file – processing of the database proceeds normally while ignoring the invalid Redo Log Group members.
  2. LGWR cannot write to a Redo Log Group because it is pending archiving – Database operation halts until the Redo Log Group becomes available (could be through turning off archiving) or is archived.
  3. A Redo Log Group is unavailable due to media failure – Oracle generates an error message and the database instance shuts down.  During media recovery, if the database did not archive the bad Redo Log, use this command to disable archiving so the bad Redo Log can be dropped:

ALTER DATABASE CLEAR UNARCHIVED LOG

  1. A Redo Log Group fails while LGWR is writing to the members – Oracle generates an error message and the database instance shuts down.  Check to see if the disk drive needs to be turned back on or if media recovery is required.  In this situation, just turn on the disk drive and Oracle will perform automatic instance recovery.

Sometimes a Redo Log File in a Group becomes corrupted while a database instance is in operation.

  • Database activity halts because archiving cannot continue.
  • Clear the Redo Log Files in a Group (here Group #2) with the statement:

ALTER DATABASE CLEAR LOGFILE GROUP 2;

 

Minimum size for an On-line Redo Log File is 4MB.

 

Posted in Basics | Tagged: | Leave a Comment »

Something Default: Control Files Backup

Posted by FatDBA on December 7, 2012

If trying to create a backup of your control file and you have not mentioned TO clause which allows you to choose any directory of your choice to move the backup of CF. If using 10g default location is always User Dump Destination and for 11g it’s DIAG (Automatic Diagnostic Repository).

Snippets from one of my Alert Log soon after i fired command.

alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12795.trc
Completed: alter database backup controlfile to trace

Posted in Advanced | Tagged: , | Leave a Comment »

Limit of ARCn and DBWRn processes.

Posted by FatDBA on December 2, 2012

Maximum number of Archiver (ARCn) processes allowed in Oracle 11g DB id 1-30.  Let’s try and check changing the parameter  log_archive_max_processes

SQL> alter system set log_archive_max_processes=35;
alter system set log_archive_max_processes=35
*
ERROR at line 1:
ORA-00068: invalid value 35 for parameter log_archive_max_processes, must be
between 1 and 30

Maximum number of Database Writer (DBWRn)

DB_WRITER_PROCESSES = 2 * number of CPU
Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.

SQL> alter system set db_writer_processes=20 scope=spfile sid=’orcl’;
System altered.

Posted in Basics | Tagged: | Leave a Comment »

UGA (User Global Area) & PGA (Program Global Area)

Posted by FatDBA on November 18, 2012

Before i start explain UGA & PGA areas of Oracle Server one should always has better understanding about Shared Server (Multi Threaded Server – MTS) and Dedicated Server architecture.Let’s start with the basic knowledge about MTS and Dedicated Server Architecture.

Each user process is associated to a single server process. The server process can be Dedicated or Shared. A dedicated process has one to one relationship with user process. A dedicated process occupies certain amount of memory. In oracle database 10g, a dedicated server process consumes about 5 MB of memory. In large scale application, memory requirements for dedicated server can be a serious issue. On the other hand, a shared server process is shared by multiple user processes. We do not need a dedicated server process for every user process, so less memory is required and performance increases. At a given time, Shared server process can serve only one user process. Multiple shared server processes are configured to handle multiple user processes.

Shared server architecture consists of Listener Process, Dispatcher Process, Request Queue, Shared server process and Response Queue.

After deciding MTS is right for your environment, it is now time to dig into the implementation details. The DBA will need to consider the following two issues: User Session-Specific Data (UGA) and Routing Client Requests to Shared Server Processes.

User Session-Specific Data (UGA)
Every connection to the Oracle database has a “session-specific” memory associated with it. This memory is referred to as the User Global Area (UGA) and is used to hold the values of PL/SQL variables, the values of bind variables and other items specific to a session.

With a dedicated server connection, the UGA is stored within the dedicated server process’s Program Global Area (PGA). When an MTS connection is made though, the UGA is stored in either the “LARGE POOL” or the “SHARED POOL”.

In an MTS environment, the UGA needs to be moved into a common memory structure like the large pool or shared pool since it contains “session-specific” data.

Posted in Advanced | Tagged: | 3 Comments »

Background Processes – Revisited

Posted by FatDBA on November 18, 2012

SMON
The System Monitor carries out a crash recovery when a crashed instance is started up again. It also cleans temporary segments. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don’t match, the database is in an inconsistent state. SCN Number is the System Change Number provided by Oracle for each committed transaction. When the database in inconsistent state while the database is opened, crash recovery is done. Crash recovery is the recovery of a database in a single-instance configuration or an Oracle Real Application Clusters configuration in which all instances have crashed. In contrast, instance recovery is the recovery of one failed instance by a live instance in an Oracle Real Application Clusters configuration. SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

SMON_SCN_TIME is the metadata table populated for every five minutes by SMON, If column_tracking_level is set to 1 SMON also updates sys.col_usage$ another metadata table.

PMON
The Process Monitor checks if a user process fails and if so, do all cleaning up of resources that the user process has acquired. In the case of shared server configuration, PMON checks dispatchers and server processes and restarts them if they have failed. It also does another important thing. During service registration, PMON provides the listener with Names of the database services provided by the database. However, if there is no listener at startup, PMON can obviously not register those information. Therefore, PMON tries then periodically to register with the listener, which might take up to 60 seconds. During service registration it provides listener with the following information:

– Name of the associated instance
– Current load and maximum load on instance
– Names of DB services provided by database.
– Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode)

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and your PMON process fails then it’s not possible to register information to listener periodically. In this case you can do ‘Manual service registration’ using command:

ALTER SYSTEM REGISTER;

This command forces the registration of database information to the listener.

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
This process was introduced in Oracle 10g.

DBWR

The Database Writer writes dirty blocks from the database buffer to the datafiles. is determined by the Initialization parameter Initialization parameter DB_WRITER_PROCESSES determines on how many DBWn Processes are started . DBWR also writes the actual SCN with the Block and flushes datafile blocks.

LGWR
The Log Writer writes the redo log buffer from the SGA to the online redo log file. It wakes up for every three seconds to flush from the redo log buffer to the online redo logs or Whenever the redo log buffer is filled up to _log_io_size defaults to 1/3 of the log buffer size) and LGWR is not currently writing. This parameter is defined in units measured in physical operating system blocks, not DB blocks. The operating system block size is (obviously) OS dependent. It can be retrieved through x$kccle. This is called X$ table owned by SYS. The entire X$ tables are memory structures generated by Oracle when instance is spawned. They die with Instance. This is a separate discussion which we do later at another point of time.

RECO
The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.

CKPT
The Checkpoint Process regularly initiates a checkpoint, which uses DBWR to write all dirty blocks back to the datafiles, thus ensuring a consistent state to the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied. The ckpt process also updates the datafile headers. The ckeckpoint process was optional in Oracle 7 (set CHECKPOINT_PROCESS=TRUE) and could be enabled to speed-up checkpointing on databases with a large number of files. Starting with Oracle 8i, the checkpoint process is automatically started with the other Oracle processes at instance startup.

ARCH
The Archiver Process archives redo log files if ARCHIVELOG is enabled. This means you have the capability of Media Recovery in case of failure.
Dnnn
The Dispatcher Process is used in a shared server environment. Dispatcher Processes, suppor multi-threaded configuration by allowing user processes to share limited number of server processes.

Snnn
The Shared Server Process is used in a shared server environment. shared Server Processes serve multiple client requests in the multi-threaded server configuration

LMON
The lock manager.

LMD0
AQ Time Manager

TRWR
Trace writer. The System Tracking process.

WMON
The wakeup monitor process.

LCKnnn
Inter-instance locking process.

SNPnnn
The snapshot process.

Qmnn (Queue Monitor)
Optional for Oracle Advanced Queuing which monitors the message queues. Used by Oracle Streams Advanced Queuing.
MMON
It makes snapshots of the database health (statistics) and stores this information in the automatic workload repository. MMON is also responsible for issuing alerts for metrics that exceed their thresholds.  This process was introduced in Oracle 10g.

Automated Storage Management ASM Instance Background Processes(10g)
Below provided are some new background processes added for an ASM instance

RBAL
This processes coordinates re-balance activity for disk groups. In an ASM instance coordinated rebalancing operations. In a DB instance, opens and mount diskgroups from the local ASM instance.

ARBx – Oracle backgroud processes. In an ASM instance, a slave for rebalancing operations

GMON – Oracle backgroud processes. In an ASM instance, diskgroup monitor.

ASMB – Oracle backgroud process. In an DB instance, keeps a (bequeath) persistent DB connection to the local ASM instance. Provides hearthbeat and ASM statistics. During a diskgroup rebalancing operation ASM communicates to the DB AU changes via this connection.

list of the new background processes in Oracle 11g:

VKTM
The virtual keeper of time provides wall-clock time and reference time for other sessions/processes.

FBAR  
The flashback data archiver writes old row-versions of tables with ‘flashback archive’ enabled into flashback data archives on commit.

DIAG
The diagnosibility process (DIAG) runs oradebug commands and triggers diagnostic dumps as part of the new ADR (automatic diagnostic repository) feature.

DIA0
The “other” diagnosibility process detects hangs then triggers DIAG do take diagnostics.

SMCO/Wnnn
The space management coordinator and slaves perform space allocation and reclamation.

There are many more. Please check below link to check various other back-end processes.

http://docs.oracle.com/cd/E14072_01/server.112/e10820/bgprocesses.htm

 

Posted in Basics | Tagged: | 1 Comment »

What is Heartbeat in Oracle ?

Posted by FatDBA on August 27, 2012

The heartbeat functionality performs heartbeat checks towards HTTP-based network nodes on behalf of a plug-in. When a heartbeat fails, the plug-in is set to state INACTIVE. The heartbeat functionality will continue trying to connect to the node and, when a positive answer is received, the plug-in re-enters the ACTIVE state.

Is a part of Oracle Net 8/Net networking suite to provide connectivity.

@This is what i found while exploring alert.log file of one of my database.

Sun May 27 11:53:23 2012
ARC0: Becoming the heartbeat ARCH

Posted in Advanced | Tagged: | Leave a Comment »

Oracle Background Processes Explained – (10-07-2012)

Posted by FatDBA on July 10, 2012

Background Process Explained-
==========================================

[oracle@prashant1 2012_09_04]$ ps -ef | grep qrcl
oracle    3219  3176  0 20:31 pts/2    00:00:00 tail -f /u01/app/oracle/admin/qrcl/bdump/alert_qrcl.log
oracle    3545     1  0 20:43 ?        00:00:00 ora_pmon_qrcl
oracle    3547     1  0 20:43 ?        00:00:00 ora_psp0_qrcl
oracle    3549     1  0 20:43 ?        00:00:00 ora_mman_qrcl
oracle    3551     1  0 20:43 ?        00:00:00 ora_dbw0_qrcl
oracle    3553     1  0 20:43 ?        00:00:00 ora_lgwr_qrcl
oracle    3555     1  0 20:43 ?        00:00:00 ora_ckpt_qrcl
oracle    3557     1  0 20:43 ?        00:00:00 ora_smon_qrcl
oracle    3559     1  0 20:43 ?        00:00:00 ora_reco_qrcl
oracle    3561     1  0 20:43 ?        00:00:00 ora_cjq0_qrcl
oracle    3563     1  0 20:43 ?        00:00:00 ora_mmon_qrcl
oracle    3565     1  0 20:43 ?        00:00:00 ora_mmnl_qrcl
oracle    3567     1  0 20:43 ?        00:00:00 ora_d000_qrcl
oracle    3569     1  0 20:43 ?        00:00:00 ora_s000_qrcl
oracle    3573     1  0 20:43 ?        00:00:00 ora_arc0_qrcl
oracle    3575     1  0 20:43 ?        00:00:00 ora_arc1_qrcl
oracle    3579     1  0 20:43 ?        00:00:00 ora_qmnc_qrcl
oracle    3585     1  0 20:44 ?        00:00:00 ora_j000_qrcl
oracle    3589     1  0 20:44 ?        00:00:00 ora_q000_qrcl
oracle    3591     1  0 20:44 ?        00:00:00 ora_q001_qrcl
oracle    4137     1  0 21:05 ?        00:00:00 ora_q002_qrcl
oracle    4162  2694  0 21:06 pts/1    00:00:00 grep qrcl
[oracle@prashant1 2012_09_04]$

SMON :– SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.  SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

$ ps -ef | grep smon
oracle   31144     1  0 11:10 ?        00:00:00 ora_smon_orcl

PMON (Process MONitor) is an Oracle background process created when you start a database instance. The PMON process will free up resources if a user process fails (eg. release database locks).
PMON is responsible to help listeners to determine if database service and it’s service handlers (Dispatchers) are available through registration.
During registration process PMON provides information like:
– Name of the database service provided by the database
– Service handlers (Dispatchers) available for the instance, including their type, protocol   address and current load details.

PMON normally wakes up every 3 seconds to perform its housekeeping activities. PMON must always be running for an instance. If not, the instance will terminate.
To speed-up housekeeping, one may also wake-up PMON (process 2 below) manually

SQL> oradebug setmypid
SQL> oradebug wakeup 2

MMON (Memory Monitor) is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.  This process was introduced in Oracle 10g.

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
This process was introduced in Oracle 10g.

ARCH (Oracle’s ARCHiver Process) is an Oracle background process created when you start an instance in ARCHIVE LOG MODE. The ARCH process will archive on-line redo log files to an archive destination, typically a directory on the database sever.

DBWR (DataBase WRiter) is an Oracle background process created when you start a database instance. The DBWR writes data from the SGA to the Oracle database files. When the SGA buffer cache fills the DBWR process selects buffers using an LRU algorithm and writes them to disk. From Dirty buffers to Data Files.

LGWR (LoG WRiter) is an Oracle background process created when you start a database instance. The LGWR writes the redo log buffers to the on-line redo log files. If the on-line redo log files are mirrored, all the members of the group will be written out simultaneously.

CKPT (Oracle Checkpoint Process) is an Oracle background process that timestamps all datafiles and control files to indicate that a checkpoint has occurred.
The ckeckpoint process was optional in Oracle 7 (set CHECKPOINT_PROCESS=TRUE) and could be enabled to speed-up checkpointing on databases with a large number of files. Starting with Oracle 8i, the checkpoint process is automatically started with the other Oracle processes at instance startup.

d000 (Dispatchers): Dispatchers are optional background processes, present only when the shared server configuration is used.

QMNC: A queue monitor process which monitors the message queues. Used by Oracle Streams Advanced Queuing.

MMNL Performs frequent and light-weight manageability-related tasks, such as session history capture and metrics computation.
Server Process (Not part of standard Oracle Architecutre) – Writes the data blocks from data file to DB Buffer Cache.

Oracle 11g Architecture (View Online)

Posted in Basics | Tagged: | Leave a Comment »

What is a Control File ?

Posted by FatDBA on July 10, 2012

What is a Control File ?
Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

  • The database name
  • Names and locations of associated datafiles and redo log files
  • The timestamp of the database creation
  • The current log sequence number
  • Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.

Backing it up in a binary format:
alter database backup controlfile to ‘/some/arbitrary/path’;
alter database backup controlfile to ‘/some/arbitrary/path’ reuse;

Backing it up in a human readable format:
alter database backup controlfile to trace;
alter database backup controlfile to trace as ‘/some/arbitrary/path’;
alter database backup controlfile to trace as ‘/some/arbitrary/path’ reuse;

Inside of a Control File (Contents):


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” NORESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M
— STANDBY LOGFILEDATAFILE
‘/u01/app/oracle/oradata/orcl/system01.dbf’,
‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’,
‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’,
‘/u01/app/oracle/oradata/orcl/users01.dbf’,
‘/u01/app/oracle/oradata/orcl/example01.dbf’
CHARACTER SET WE8ISO8859P1
;– Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE– All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

— Database can now be opened normally.
ALTER DATABASE OPEN;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/orcl/temp01.dbf’
SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
— End of tempfile additions.

—     Set #2. RESETLOGS case

— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
—  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “ORCL” RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/u01/app/oracle/oradata/orcl/redo01.log’  SIZE 50M,
GROUP 2 ‘/u01/app/oracle/oradata/orcl/redo02.log’  SIZE 50M,
GROUP 3 ‘/u01/app/oracle/oradata/orcl/redo03.log’  SIZE 50M
— STANDBY LOGFILE

DATAFILE
‘/u01/app/oracle/oradata/orcl/system01.dbf’,
‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’,
‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’,
‘/u01/app/oracle/oradata/orcl/users01.dbf’,
‘/u01/app/oracle/oradata/orcl/example01.dbf’
CHARACTER SET WE8ISO8859P1
;

— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— ALTER DATABASE REGISTER LOGFILE ‘/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2012_06_29/o1_mf_1_1_%u_.arc’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/app/oracle/oradata/orcl/temp01.dbf’
SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
— End of tempfile additions.

Posted in Advanced, Basics | Tagged: | Leave a Comment »

High-water Mark

Posted by FatDBA on June 30, 2012

High-water Mark —–

The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

Example.
for example, if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,  At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.

Posted in Advanced, Basics | Tagged: | Leave a Comment »

Oracle Storage Hierarchy

Posted by FatDBA on June 27, 2012

Image

Posted in Basics | Tagged: | Leave a Comment »

 
%d bloggers like this: