Database Architecture An Oracle database server consists of an Oracle database and an Oracle instance. Oracle Database architecture is based on physical and logical structures. Oracle Database Physical Structure Oracle database consists of physical files to store information. Basically there are three types of physical files required for database: Core Files: Data Files [...]
Archive for the ‘Oracle Architecture’ Category
5 Dec
Oracle Parameter Files
Parameter Files Oracle must read either an initialization parameter file or a server parameter file(SPFILE) to start an instance. These files contain list of initialization parameters and a value for each parameter. Server Parameter Files Initialization Parameter Files In the platform-specific default location, Oracle Database locates your initialization parameter file by examining filenames in the [...]
20 Aug
Recommended init parameters for Websphere Portal
Recommended init parameters for IBM Websphere Portal wspprd.__db_cache_size=587202560 wspprd.__java_pool_size=16777216 wspprd.__large_pool_size=16777216 wspprd.__shared_pool_size=939524096 wspprd.__streams_pool_size=33554432 *.compatible=’10.2.0.3.0′ *.db_block_size=8192 *.db_cache_size=524288000 *.db_domain=” *.db_file_multiblock_read_count=16 *.db_files=1024 *.db_recovery_file_dest=’/wspprd/oracle/product/10.2.0/flash_recovery_area’ *.db_recovery_file_dest_size=2147483648 *.job_queue_processes=10 *.open_cursors=1600 *.pga_aggregate_target=314572800 *.pre_page_sga=TRUE *.processes=600 *.sessions=665 *.sga_max_size=1610612736 *.sga_target=1610612736 *.shared_pool_size=536870912 *.db_writer_processes=20 *.sort_area_size=65536 *.cursor_space_for_time=TRUE *.session_cached_cursors=500 *.log_checkpoints_to_alert=TRUE *.log_checkpoint_interval=0 *.log_checkpoint_timeout=0 *.fast_start_mttr_target=3600 *.log_buffer=419430400 Related articles by Zemanta Creating Oracle Database for Websphere Portal (mohibalvi.wordpress.com)
24 Apr
Locks and Queries
Query to check blocking and waiting sessions SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a WHERE c.waiting_session=a.sid and c.holding_session=b.sid To Find which session is blocking other sessions with some system info select s1.username || ‘@’ || s1.machine || ‘ [...]
14 Apr
OPEN CURSORS
Open cursors take up space in the shared pool, in the library cache. OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. Monitoring open cursors v$open_cursor shows cached cursors, [...]
7 Apr
UNDO Tablespace
Undo Segment An undo segment is used to save the old value (undo data) when a process changes data in a database. It stores the location of the data and the data as it existed before being modified. The header of an undo segment contains a transaction table where information about the current transactions using [...]
3 Apr
Automatic segment space management
Automatic Segment-Space Management • Bitmap segments contain a bitmap that describes the status of each block in the segment with respect to its available space. • The map is contained in a separate set of blocks referred to as bitmapped blocks (BMBs). • When inserting a new row, the server searches the map for a [...]
3 Apr
Tablespaces
Tablespaces The data in an Oracle database are stored in tablespaces. • An Oracle database can be logically grouped into smaller logical areas of space known as tablespaces. • A tablespace can belong to only one database at a time. • Each tablespace consists of one or more operating system files, which are called data [...]
3 Apr
Redo Logs
Redo log files provide the means to redo transactions in the event of a database failure. Every transaction is written synchronously to the redo log files in order to provide a recovery mechanism in case of media failure. (With exceptions such as: direct loads and direct reads done with the NOLOGGING option.) This includes transactions [...]
2 Apr
CONTROL FILE
The control file is a small binary file necessary for the database to start and operatesuccessfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by [...]
