Skip to main content

Posts

Showing posts from 2017

Background Processes - Oracle Architecture

SMON - System Monitor  Performs recovery after instance failure, monitor temporary segments and extents clean those temporary extents and segments.  PMON - Process Monitor Failed process resources in shared server architecture monitor and restart any failed dispatcher or server process.  DBWn - Database Writer Database Writer process writes new or changed data blocks into the data-files which are in the buffer cache.  LGWR - Log Writer Log writer is in change of REDO Log buffer management log writer is one of most active processes until log writer success data commit will not be completed. ARCn - Archive Process Copies REDO Logs to one or more destinations. CKPT - Checkpoint Process This helps to reduce the amount of time required for instance recovery. During checkpoint updates the header of the control file and data-files to reflect the last successful  System Changed Number (SCN)  This automatically starts every time the ...

Oracle Memory Structure - Oracle Architecture

SGA - System Global Area SGA is a  group of shared memory area which is dedicated to oracle instance. Shared Pool Shared pool is dividing into two major areas which are : Library Cache :  Area which stores the SQL information such as select statements, cursors and execution plans. Data Dictionary Cache :  Which contains cache, user information and privileges, segments and extent information as well. Buffer Cache Buffer cache holds blocks of data from disk that have been recently read to satisfy a select statement or that contains modified data-blocks.  Redo Log Buffer Redo Log buffer holds the most recent changes to the data blocks in the data-files.    Large Pool This is an operational area of the SGA it is used for transactions that interact with more than one database. Java Pool Java Pool is used by the Oracle JVM ( Java Virtual Machine ) for all Java code & data within user session. Stream Pool Stream Pool is...

Oracle Data Dump Import and Export

Login to server : sqlplus / as sysdba exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y or exp SYSTEM/password PARFILE=param.dat where param.dat contains the following information  FILE=dba.dmp   GRANTS=y   FULL=y   ROWS=y   LOG=dba.log To dump a single schema to disk. exp / FILE=dump.dmp OWNER=production Export Specific tables to disk exp SYSTEM/password FILE=export.dmp TABLES=(user1.table,user2.table) Export Data in one user  exp / FILE=export.dmp TABLES=(table1,table2) Using imp:   To import the full database exported in the example above. imp SYSTEM/password FULL=y FIlE=dba.dmp To import just the dept and emp tables from the scott schema imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp) To import tables and change the owner  imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager) To import just the scott schema exported in t...