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