Architecture - 3

3. Architecture - 3

1. Which background process write dirty blocks from database buffer cache to   data files?

  Database writer (DBWR)

2. Which background process writes data from log buffer to redo log files?  

     

     Log writer (LGWR)

3. Which background process performs Crash recovery?

   SMON (system monitor)

4. What is Log Switch?

   The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

5. What is On-line Redo Log?

   The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the   corresponding redo entries temporarily stores in redo log buffers of the SGA are   written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

6. What are the steps involved in Instance Recovery?

Rolling forward to recover data that has not been recorded in data files yet has      been recorded in the on-line redo log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or have  not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources (locks) held      by transactions in process at the time of the failure. Resolving any Pending distributed transactions undergoing a two-phase commit at the time of the instance failure.

7. What does COMMIT do?

 COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made by  the SQL statements of a transaction become visible to other user sessions transactions that start only after transaction  is committed.

8. How do you know whether the process is Server Side Process?

 By seeing the process in ps-ef as ORACLE_SID.

9. When ckpt occurs?

     1. For every 3 seconds
     2. When 1/3rd of DB buffer fills
     3. When log swtich occurs
     4. When database shuts down

10. What are the two steps involved in instance recovery?

  1. Roll forward (redofiles data to datafiles), 2.Roll backward (undo files to data files).

11. What is the use of SMON?

  SMON is an Oracle mandatory background process. It is used for Instance recovery.

12. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the tablespaces in Oracle database to have maximum performance?

1.                    SYSTEM - Data dictionary tables.

2.                    DATA - Standard operational tables.

3.                    DATA2- Static tables used for standard operations

4.                    INDEXES - Indexes for Standard operational tables.

5.                    INDEXES1 - Indexes of static tables used for standard operations.

6.                    TOOLS - Tools table.

7.                    TOOLS1 - Indexes for tools table.

8.                    RBS - Standard Operations Rollback Segments,

9.                    RBS1, RBS2 - Additional/Special Rollback segments.

10.                  TEMP - Temporary purpose tablespace

11.                  TEMP_USER - Temporary tablespace for users.

12.                  USERS - User tablespace.

13. How do you know when the process is started?

  Using ps -ef grep process name

14. What is meant by redo log buffer?

Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.

Architecture - 2

2. Architecture - 2

1. What is the basic element of Base configuration of an oracle Database?

It consists of one or more data files. One or more control files. Two or more redo log  groups. The Database contains multiple users/schemas one or more rollback segments
one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the database consists of SGA (Database buffer cache,Shared pool ,Redo log buffer)

SMON (System monitor)
PMON (Process MONitor)
LGWR (LoG Write)
DBWR (Data Base Write)
ARCH (ARCHiver)
CKPT (Check Point)
RECO
Dispatcher
User Process with associated PGA.

2. What is the function of Optimizer?

The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

3. What is Execution Plan?

The combination of the steps the optimizer chooses to execute a statement is called an execution plan.

4. What are the different approaches used by Optimizer in choosing an execution plan?

Rule-based and Cost-based.

5. What are the different Components of SGA?

1. The fixed SGA (Fixed SGA)
2. Default block buffer (Db cache)
3. Different Standard Block buffers
4. Redo log buffer (Redo log buffer)
5. Java pool (Java pool)
6. Large pool (Large pool)
7. Shared pool (Shared pool)
8. Stream pool (Stream pool)
9. Result cache

6. What is dictionary cache?

The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like the buffer cache, except it’s for Oracle data dictionary information instead of user information.

7. What is Database Buffers?

 Database buffers are cache in the SGA used to hold the data blocks that are read from the data    segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size

8. What is the functionality of SYSTEM table space?

System tablespace is a main part of oracle database. All the database information is in it.it is created when database is created.It is managed by oracle server . dba cannot change its contents. it contains the data dictionary objects .

9. What is the function of checkpoint (CKPT)?

Checkpoint is a background process which ensures dbwn process has written data to datafiles and upadates control file and datafile header to establish data consistency.The CKPT is also useful to get the point in time from where to begin the recovery in case of failure.

10. When does LGWR write to the database?

Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File.LGWR   writes  redo  log  entries  into  an  on-line  redo  log  file  when transactions commit and the log buffer files are full.

11. What is Shared SQL Area ?

 A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by using one shared SQL area for SQL statements run multiple times, which often happens when many users run the same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently used) algorithm until there is enough free space for the new statement's shared SQL area.

12. What Does DBWR do?

 Database writer writes modified blocks from the database buffer cache to the data files.

13. What is server processes?

 A server process is one that handles user requests. When you type in a SQL statement, the server process handles the parsing and running of that SQL statement,

14. Name the process which carries the request to the memory components,And also fetches from disk to buffer?
  Server Process

Questions & Answers

1. What is the function of SMON in instance recovery?

A.It writes data to the archive log files.

B.It writes data to the online redo log files.

C.It frees resources held by user processes.

D.It synchronizes data file header and control files.

E.It roles forward by applying changes in the redo log.

F.It writes dirty buffers from the buffer cache to the data files.

correct answer: e

2. Which action occur during a checkpoint?

A.Oracle updates a control file.

B.Oracle performs a delayed block clean-out.

C.Oracle copies the online redo-log to the archive destinations. `

D.Oracle flushes the dirty blocks in the database buffer cache to disk.

correct answer: d

3. SMON process is used to write into LOG files

a.TRUE

b.FALSE

correct answer: b

4. Oracle does not consider a transaction committed until

A. The Data is written back to the disk by DBWR

B. The LGWR successfully writes the changes to redo

C. PMON Process commits the process changes

D. SMON Process Writes the data

correct answer: b

5. How many maximum DBWn(Db writers) we can invoke?

A. 3

B. 1

C. 10

D. 20

correct answer: d

6. Which activity would generate less undo data?

  

A.INSERT

B.UPDATE

C.SELECT

D.DELETE

correct answer: a

7. What happens when a user issues a COMMIT?

A. The CKPT process signals a checkpoint.

B. The DBWn process wrtes the transactions changed buffers to the datafiles.

C. The LGWR flushes the log buffer to the online redo log.

D. The ARCn process writes the change vectors to the archive redo log

correct answer: c

8.What happens when a user process fails?

    A.PMON performs process recovery.

    B.SMON performs ICR

    C.SMON frees resources held by user process.

    D.Oracle Updates a controlfile.

Answer:a

9.What are the free buffers in the database buffer cache.

    A.Buffer that have changed should be pushed to the disk.

    B.Buffers that are currently in use.

    C.Buffer that are being written to disk.

    D.Buffer that can be overwritten.

Answer:d

10.When thw SMON Procees perform ICR.

      A.For every startup

      B.Only at the time of startup after graceful shutdown

      C.Only at the time of startup after abort shutdown

Answer:c

Architecture 1

1. Architecture - 1

1. Why do we need to maintain our data in database?

 As we all know Database has a availability of a storage system, the most obvious of these being persistence. To sum it up, we can identify the following reasons for wanting a Database Management Systems short for DBMS:

 We need to share data of all kinds between users (except for very small systems), between tools working on them, and usually between different computers. All should be able to work with the same set of data.

We need persistent storage and storage must be reliable.

Several users and/or several tools must be able to safely access the same data concurrently.

A DBMS may provide more efficient access to large amounts of data, through indexing and other optimizations.

A DBMS and its data model provides an abstraction; we do not need to care about the physical storage format.

A DBMS may also be used for communication between different users or applications.

2. What are the responsibilities of a Database Administrator?

Installing and upgrading the Oracle Server and application tools.

Allocating system storage and planning future storage requirements for the database system.

Managing primary database structures (tablespaces) Managing primary objects (table, views, indexes)

Enrolling users and maintaining system security.

Ensuring compliance with Oralce license agreement Controlling and monitoring user access to the database.

Monitoring and optimizing the performance of the database.

Planning for backup and recovery of database information.

Maintain archived data on tape Backing up and restoring the database.

Contacting Oracle Corporation for technical support.

3. What are the Daily Activities of a Oracle DBA

1. Check the Database availability

2. Check the Listener availability

3. Check the alert log file for errors

4. Monitoring space availability in table spaces

5. Monitoring mount point (see capacity planning document)

6. Validate Database backup or Archive backup

7. Find objects which is going to reach max extents

8. Database Health check

9. CPU, Processor, Memory usage

4. What is the most challenging aspect of your job?

This question will give you clues about the mindset of the DBA. When asked what is challenging about the DBA job, the DBA’s answer will reveal a great deal about his or her personality and ability to work well with other people. The DBA who talks about the technical challenges of the job and difficulties administering and maintaining the database may not be ideally suited for a position where contact for the development staff is critical. On the other hand, the DBA who speaks openly of the challenges associated with getting the developers fast answers and accurate information might be a better consideration.

5. How do you perceive the relationship between the DBA and the development staff?

DBAs by virtue of their high pay and product-specific knowledge, tend to think of developers as underlings, in some cases, DBAs view developers with outright contempt, believing their queries to be naive. On the other hand, DBAs with the proper attitude will respond to this question by talking about the developers as clients to whom they provide data services essential to the application. In some shops, the DBAs may be responsible for code, reviewing SQL queries, or DML statements written by developers; so, a good relationship is vital.

6. What is a Database instance? 

Explain A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are used to store most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

7. What are mandatory background processes in Oracle Database?

Smon, pmon, ckpt, dbwr, lgwr

8. What is Oltp database?
OLTP means Online Transaction Processing. OLAP means Online Analytical Processing. OLTP deals with processing of data from transactional systems. For example, an application that loads the reservation data of a hotel is an OLTP system. An OLTP system is designed mainly keeping in the mind the performance of the end application. It comprises of the application, database & the reporting system that directly works on this database. The database in an OLTP system would be designed in a manner as to facilitate the improvement in the application efficiency thereby reducing the processing time of the application.

9. What is Olap database?

OLAP systems were mainly developed using data in a warehouse. Having said that a need was felt to isolate older data, it was necessary to store them in a format that would be useful in easing out the reporting bottlenecks. A need was felt to isolate the data & redesign the application data to such a format & structure that this data repository would be the prime source of business decisions. Coming back to OLAP systems, these systems were mainly developed on the isolated data.

Questions and answers

1. Which of the following does not affect the size of the SGA

a) Database buffer

b) Redo log buffer

c) Stored procedure

d) Shared pool

correct answer: c

2. A set of Dictionary tables are created

a) Once for the Entire Database

b) Every time a user is created

c) Every time a Tablespace is created

d) None of the above

correct answer: a

3. The order in which Oracle processes a single SQL statement is ?

A. execute, parse, fetch 

B. execute, fetch, parse

C. parse, execute, fetch

D. parse, fetch, execute

correct answer: c

4. What are the mandatory datafiles to create a database in Oracle 11g?

A. SYSTEM, SYSAUX

B. SYSTEM, USERDATA, TEMP

C. SYSTEM, SYSAUX, UNDO

correct answer: c

5. In one server can we have different oracle versions?

A. No

B. Yes

correct answer: b

6. How do sessions communicate with database?

A. Server processes use oracle net to connect to the instance.

B. Background processes use oracle net to connect to the database

C. User processes read from the database and write to the instance;

D. Server processes execute SQL received from user processes.

correct answer: d

7. Which SGA memory structure cannot be resized dynamically after instance startup?

A. Database Buffer Cace

B. Java Pool

C. Large pool

D. Log buffer

E. Shared Pool

correct answer: d

8. When a session changes data, where does the change get written?

A. To the data block in the cache, and the redo log buffer

B. To the data block on disk, and the current online redo log file

C. The session writes to the database buffer cache, and the log writer writes to the current online redo logfile

D. Nothing is written until the change is committed

correct answer: a

9. How many maximum no of control files we can have within a database

    A 3

    B.5

    C.1

    D.8

Answer d

10.System Data File Consists of

    A.Metadata

    B.Business Data

    C.Temporary Data

    D.Undo Data

Answer a

index of Oracle_Dba

Oracle 11G DBA  Topics

• Oracle 11g Architecture-1, 
• Architecture-2 
• Oracle 11g Architecture-3,
• Oracle Installations Startup & Shutdown Modes/DB-Creation,
• TS-Mgmt. 
• User / Role / Profile Mgmt./schema mgmt. 
• SP File & OMF, 
• Storage (DM & LM)
• Undo & FB-Qry & FB-Table & FB-Version Qry Intro to Networking with Oracle 11g 
• DB Links & Materialized Views (with MV-Logs) Logical Bkups - Reg & Incr Exp 
• Data Pump (expdp & impdp), TTS for a single partition
• Redo-Log & Control File Mgmt, Bkup/Rec-1 - Cold-1
• Bkup/Rec-2 - Cold-2, Bkup/Rec-3 - Hot 
• Bkup/Rec-4 - RMAN-1 - Theory Bkup/Rec-5 - RMAN-2 - Scenarios 
• PT-1 DB-Tuning, PT-2 Appl Tuning PT-3 Memory Tuning, PT-4 Network Tuning
• PT-5 AWR / ASH / ASMM / ADDM
• PT-6 Flashback DB/Flashback Data Archive 
• PT-7 Partitioning/Spl. Indexes PT-8 Resumable, 
• Row-Chaining / Row-Migration 
• PT-9 ASM (Auto Storage Management) 
• PT-10 ASM Demo Availability-1
• Data-Guard/DB-Cloning Availability - 2 Active/ Snapshot Standby Availability-3
• Log-miner,
• RMAN-TS-PTR 
• SQL*Loader, 
• DB Audit *