Oracle Database Administrator (DBA)

Basic Unix/Linux commands in RAC Administration SRVCTL Utility is used to start and stop the database and instances and configuration management etc. To Start a RAC database srvctl start nodeapps -n  <nodename> srvctl start asm -n  <nodename> srvctl start database -d  <dbname> options: srvctl start database -d  <dbname>  -o open | -o mount | -o nomount Stop a RAC database srvctl stop database -d  <dbname>  -o immediate options: srvctl stop database -d  <dbname>  -o normal | -o transactional | -o immediate | -o abort srvctl stop asm -n  <nodename> options: srvctl stop asm -n  <nodename>  -o immediate srvctl stop nodeapps -n  <nodename> To check status and configurations Nodeapps: srvctl status nodeapps -n  <nodename> srvctl config nodeapps -n  <nodename> ASM: srvctl status asm -n  <nodename> srvc...

There are some important question and answer for Oracle DBA. 1. What are the components of SGA? SGA is used to store shared information across all database users. It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool. 2. Difference between SGA and PGA. SGA (System Global Area) is a memory area allocated during an instance start up. SGA is allocated as 40% of RAM size by default. SGA size is controlled by DB_CACHE_SIZE parameter defined in initialization parameter file (init.ora file or SPFILE).  PGA (Program or Process Global Area) is a memory area that stores a user session specific information. PGA is allocated as 10% of RAM size by default. 3. What is System Change Number (SCN)?  SCN is a unique ID that Oracle generates for every committed transaction.  It is recorded for every change in the redo entry. 4. What is Database Writer (DBWR) and when does DBWR write to the data file? -D...

Table partition is used to improve the performance, manageability and availability of applications in large size databases. Types of table partitioning: a. Range partitioning:   The data is distributed based on a range of values of the partitioning key. For example, if we choose a date column as the partitioning key, the partition “JAN-2017” will contain all the rows that have the partition key values between 01-JAN-2017 and 31-JAN-2017 (assuming the range of the partition is from first of the month to the last date in the month). SQL>Create table emp_range(empid number(4),empname varchar2(10), empsal(10,2)) partition by range(empid) (partition p1 values less than (10) tablespace t1, partition p2 values less than (20) tablespace t2, partition p3 values less than (30) tablespace t3, partition p4 values less than (40) tablespace t4); b. Hash Partitioning:   A hash algorithm is applied to the partitioning key to determine the partition for a given row. Th...

Basic Steps Gathering Statistics : a)Table Stats(statistics) Sql> exec dbms_stats.gather_table_stats(‘Schema’,’Tname’); Sql> select table_name,to_char(last_analyzed,’hh24:mi:ss dd-mon-yy’) from dba_tables where table_name=’Tname’; b)Schema Stats Sql> exec dbms_stats.gather_schema_stats(‘Schema’); c)Database Stats Sql> exec dbms_stats.gather_database_stats(); d) Dictionary Stats Sql>exec dbms_stats.gather_dictionary_stats(); e)Index Stats Sql>exec dbms_stats.gather_index_stats(‘Schema’,’IND_Name’); Deleting Stats :  a) Table Stats  Sql>exec dbms_stats.delete_table_stats(‘Schema’,’Tname’);  b) Schema Stats  Sql>exec dbms_stats.delete_schema_stats(‘Schema’);  c) Database Stats  Sql>exec dbms_stats.delete_database_stats(); Defragmentation : Table : Sql> alter table table_name shrink space compact; Index: Making index invisible/visible SQL...

SLOB is the best SQL workload to drive maximum physical random I/O from a database platform. SLOB is an I/O intensive SQL generator, instead of a synthetic I/O generator, with the following characteristics: Ø   Supports testing physical, random single-block reads (database file sequential read/DB file parallel read) Ø   Supports testing random single-block writes (database file parallel write) Ø   Supports testing extreme REDO logging I/O Ø   Consists of simple PL/SQL Installation: Installation steps are below: 1.        Install the latest SLOB RPM yum install http://yum.outrun.nl/outrun-extras.rpm 2.        Go to the url "https://kevinclosson.net/slob/" and then click on SLOB 2.3: Click Here link. This link will redirect on " https://my.syncplicity.com/share/ize90aqgqy9yj1d/2015.07.16.slob_2.3.0.3-1.tar " page, then click on download button to download the SLOB 2.3 in .tar file ...