Oracle

Learn Oracle in short notes SQL DBA

Advertisements

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;

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: