Oracle Short Notes
Database – It’s a collection of files.
Physical file locations:
Listener.ora, TNSname.ora – $ORACLE_HOME/network/admin/
Password & Parameter file – $ORACLE_HOME/dbs/
Control file – flash_recovery_area & /opt/oracle/oradata/
Instance – It’s a collection of SGA+BG process
SGA – Shared pool, Database buffer cache, redo log cache, Java pool, large pool & stream pool
Shared Pool – Most recently executed statements and definitions.
Data Dictionary – most recently used definitions, including table, index & privilege.
Library cache – most recently used SQL & PL/SQL statements
Shared SQL area – Shared PL/SQL area
Database buffer pool – It stores a copy of data block.
Default, keep & Recycle – Pinned, dirty & free buffer
Redo log buffer pool – it stores all the changes made to the data blocks.
Large pool – it only used in the shared server environment and for some of RMAN features.
Java pool – it used for Java programs.
BG – SMON, PMON, DBWR, LGWR, CPKT & Arc etc..
DBWR – It writes a dirty buffers to data file.
(Dirty buffers reach threshold, Checkpoint occurs, no free buffers, Alter tablespace)
LGWR – It writes a redo buffer entries to redo log file.
(At commit,Every 3 seconds,contains 1MB worth of redo,When 1/3rd full & Before DBWn writes.)
PMON – It cleanup failed process, rollback transactions, releasing lock & restart death dispatchers.
SMON – It’s for instance recovery, Coalesce free space & Deallocate temporary segments.
CKPT – It will call the DBWR to write the dirty buffers to data file.
(Log switch, Shutdown immediate, Transactional, manual & fast_start_mttr_target=<No of Seconds>,)
PGA – User process & sort, merge area. (User connection àUser process àServer process àPGA
Oracle Installation – OS Semaphores settings, User limit settings, RPM & Oracle group, User, .bash_profile settings.
Block – PCTused -40%, PCTfree -10 & high water mark.
Extent– huge continuous block. System managed – AUTOALLOCATE or uniform
Segment– segment is a set of extents
Tablespace– (System,Sysaux,Undo & Temporary) By default LMT.
Password file – connect the oracle database, either by password file or OS group by SYSDBA.
Parameter file – Pfile – text & SPfile – Binary. Store – Compatibility, DB name, control file,undo management and name. Location – ORACLE_HOME/dbs.
Control file – Default 2 files, maximum 8. DB name, file location,SCN & Rman backup info.
Data file – The Max size of each data file is 32G.
Redo log file – Changes written, minimum 2 files (Current, active, inactive, unused)
Archive log file – offline backup of redo log. Location – $ORACLE_BASE/flash_recovry_area
Startup – Read sp or pfile (nomunt), Read Control file (mount), open, force, restrict & upgrade
Shutdown – Shutdown(Wait for transaction),Immediate,Transactional & abort (improper shutdown)
Users/ Schemas
Profile – is for resource limit, we can enforce password & session parameters to the user. DEFAULT is default profile.
Password: FAILED_LOGIN_ATTEMPTS,PASSWORD_LIFE_TIME, REUSE_TIME,GRACE TIME etc
Session: SESSIONS_PER_USER, CPU_per_user, CONNECT_TIME,IDLE_TIME etc
Role – is for system and object level permission for user.
Object (DML), System – more than 256
User – Create user with default tablespace + one more TBS + temporary TBS & profile.
Indexes – A table dependent and optional object.
Properties – Usability (No DML) – Bulk load & Visibility (DML) – removing unused index.
B tree index – Normal B tree (Default),Index-organized tables – IOT, Cluster index table, Composite index, unique index, Non unique index, Descending indexes, Reverse key indexes.
Normal B tree – unique index, Non unique, composite & descending index
IOT– same as CI in SQL server. Physical & logical order differ. It stores all column of the table (PK must, Cannot modify only by alter table)
Cluster index table – A cluster is simply a method for storing more than 1 tables same column on the same block. Cluster key can point. Instead of pointing to a row, the key points to the block that contains rows.
Composite index – Creating an index with more than one column.
Unique index – No duplicate allowed
Non unique index – Duplicates are allowed
Descending indexes – The index order will be in descending
Reverse key indexes – The bytes of the index key are reversed. Ex: emp id: EE100. Most useful for Oracle RAC.
Bit map index – compact and designed for data warehousing.
Function based index — contain the precomputed value of a function ()/expression. Either a B-tree or a bitmap index. Ex: UPPER funcation.
Application domain indexes – Only created by application team. It mostly an externally file.
Constraints – Same as SQL server.
Backups – Physical (Cold, Hot & RMAN) Logical (Import/Export)
Cold – Offline and consistent backup.
Hot – Online and inconsistent partial backup. Archive mode and Tablespace, control file.
RMAN – consistent. Utility. Backup only used block, Auto backup of control & parameter file, incremental backup with block change tracking and optimization on archivelog & Level 0,1
Export / Import – Normal old one and Data pump – new one with improved parameters
Recovery –
Connectivity – Net8 – Server: S/W, TNSname.ora & listener Client: S/W+ TNSnames.ora
TNSname – Database address
Listener – Database information for the incoming connection
Patches – Opatch- one patch, CPU – quarterly, PSU –
$ /opt/oracle/product/11.2.0.3.0/dbhome/OPatch/opatch lsinventory – equal/higher version OPatch.
Upgrade – patchset – change the database minor release. From 11.2.0.1.0 onwards only a DB upgrade, no S/W upgrade needed.
Cloning – same database as in different name. Methods (Cold, Hot, RMAN- duplicate database & active database). 11G onwards Active Database cloning using Rman.
Migration – major release change.
ASM – 10g feature, Stores a DB file on ASM filesystem, which has a RAID. Disk group, DISK, ASM Instance.
Data Guard – standby database. Types: Physical standby, Logical Standby & failover, switchover
Performance Tuning –
COB – optimizer_mode, dbms_stats.gather
Query tuning: Explain plan, TK prof, Auto trace, AWR
Memory tuning
Row changing, migration, Fragmentation
AWR – Sql> @?/rdbms/admin/awrrpt.sql;
ADDM – Sql> @?/rdbms/admin/addmrpt.sql;
ASH – Sql> @?/rdbms/admin/ashrpt.sql;
Explain Plan – Sql> @?/rdbms/admin/utlxplan.sql;
