oracle database architecture faq

 ARCHITECTURE FAQ

 

1. What is an Oracle Server

   Oracle server is a database management system which consists of an   

   oracle instance and an oracle database.

 

2. What is an Oracle Instance

   Instance is a combination of SGA and background processes.

 

3. What is an Oracle database

   Oracle database consists of database files which provides the physical

   storage for database information                           .                               

 

4. What is SGA

   System Global Area used to store database information that is shared by database

   processes.

 

5. What are the mandatory background processes

   SMON, PMON, DBWR, LGWR, CKPT.

 

6. What are the components of SGA

    Shared pool, Database buffer cache, Redo log buffer, Large pool, Java pool.

 

7. What does shared pool contain

    Shared pool contains most recently executed SQL statements

    Most recently used data definitions.

 

8. What does database buffer cache contain

    It contains copies of data blocks that have been retrieved from the datafiles.

 

9. What  does redo log buffer contain

    Redo log buffer records all changes made to the data for recovery purpose.

 

10.What is the function of SMON

     SMON performs instance recovery by roll forwarding the committed as 

     Well as uncommitted transactions and rollbacks the uncommitted transactions.

     Colease free space in datafiles.

 

11. What is the function of PMON

      PMON cleans up after failed processes by rolling back the user’s current transactions,

      Releases table or row locks, resources  held by user processes, restarts the dead

      Dispatchers.

 

12. What is the function of DBWR

      DBWR writes dirty buffers (buffers which are modified but not yet written to

     Datafiles.) from buffer cache to datafiles.

 

13. What is the function of LGWR

      LGWR writes the data from redo log buffer to redo log files.

 

14. What is the purpose of checkpoint

      Purpose of checkpoint is to make sure that all the buffers in the buffer cache that were

      Modified prior to point in time have been written to the datafiles.

 

15. When does DBWR writes dirty buffers from buffer cache to datafiles

      When a check point occurs

      When dirty buffers reach thresh hold

      When time out occurs

      When a tablespace is taken offline

      When a tablespace is taken to read only

      When a tablespace is kept in begin backup mode

      When a table is dropped or truncated

      When the database is shutdown.

 

16. When does LGWR writes to redo log files

      When redo log buffer is one third full

      When a commit occurs

      Before DBWR writes to datafiles

      Every 3 seconds

      When there is 1MB of redo in redo log buffer

 

17. What is a parameter file

      Parameter file defines the characteristics of the instance

 

18. What are the types of parameter files

      Pfile, spfile

 

19. What is the difference between pfile and spfile

      Pfile is a text file , spfile is a binary file

      Changes to entries in pfile take effect on the next startup

      Changes to entries in spfile are persistent across startup and shutdown

 

20. What is the default location of pfile

      $ORACLE_HOME/dbs

 

21. What is the use of control file?what information it contain?

      Control file is used  to maintain the integrity of the database.It contains information

      Like database name, timestamp of database creation, tablespace names, location of

      Datafiles and redo log files, checkpoint information, current redo log sequence

      Number

 

22. What are the logical structures of the database

      Tablespaces, segments, extente, blocks

 

23. What is a tablespace

      Tablespace is a logical structure of the database that consists of one or more datafiles

 

24. What is a segment

      Segment is a space allocated for a specific object within a tablespace

 

25. What is an extent

      An extent is a set of contiguous oracle blocks

 

26. What is an oracle block

      Block is a smallest unit of storage for read write operations.

 

27. What is the default standard block size in 9i

      8k

 

28. What are the parameters for sizing the buffer cache,shared pool,redo log buffer

      Db_cache_size, shared_pool_size, log_buffer

 

29. What is a password file

      Password file authenticates privileged users to start up and shut down an oracle 

      Instance

 

30. What is an archived redo log file

      Archived redo log files are offline copies of the redo log files that are necessary for

      Recovery from media failures.

 

31. What is a session

      A session is a specific connection of a user to an oracle server

 

32. What is PGA

      Memory reserved for each user process connecting to an oracle database

 

33. What Is dedicated server

      Server process handles the request of a single user process in dedicated server

 

34. What is shared server

      In shared server, multiple server process handles the requests of  several user

      Processes

 

35. What is server process

      Server process is a program which interacts directly with oracle server

 

36. What is user process

      User process is a program which requests interaction with the oracle server

 

37. How can we control the size of SGA

      SGA_MAX_SIZE parameter can control the size of sga.

                                             CONTRIL FILE FAQ

1. What is a control file?

Control file is a binary file which records the physical structure of the database.

2. Upto how many times control files can be multiplexed

Control files can be multiplexed upto 8 times.

3. What information does the control file contains?

Database name, timestamp of database creation, table4space names, names and

Locations of datafiles and redo log files, current redo log file sequence number, checkpoint information, backup information, archive information, undo information.

4. Why the control file must be multiplexed?

To safeguard against a single point of failure, the control file must be multiplexed.

5. Which views would you query to display the name and location of the control file?

V$controlfile, v$parameter, show parameter control file

6. What is the way to multiplex the control file when creating a new database?

Specify atleast two control file names and locations using CONTROL_FILES initialization parameter in the parameter file before issuing the create database statement.

7. Which state is the control file read, when starting an instance

Mount state

8. How could we verify that the control file is multiplexed?

By querying v$parameter, v$controlfile, show parameter control file

9. Which dynamic performance view can be queried to obtain the total no.of

Records used for data files in the control file?

v$controlfile_record_section

10. When should we backup the control file

After adding, dropping or renaming a datafile

After adding, dropping or altering the read/write state of the tablespace, adding or dropping redo log files or groups

11. How to backup the control file

Create a backup of the control file using ALTER DATABASE BACKUP CONTROL FILE TO TRACE

12. How to multiplex the control file using pfile

Shut down the database

Create additional control file using os cp command

Add control file names to pfile

Start the database

13. How to multiplex the control file using spfile

Using alter system command alter spfile to include list of control files to be multiplexed

Shutdown the database

Create additional control file using os cp command

Startup the database

13a: What happens if the control file is unavailable?

If any of the control files become unavailable during database operation, the

instance becomes inoperable and should be aborted

13b: When to create a new control file

When all control files for the database have been permanently damaged and you

do not have a control file backup

13c: How to determine the size of the control file

The size of a control file are the values set for the MAXDATAFILES, MAXLOGFILES,

MAXLOGMEMBERS, MAXLOGHISTORY, and MAXINSTANCES parameters in the CREATE

DATABASE statement. Increasing the values of these parameters increases the size

of a control file of the associated database .

14. What information the parameter file contains?

Dbname, name and location of control files, undo information, parameters for sizing the buffer cache, shared pool, redo log buffer, java pool, large pool, name and location of user trace files, background trace files etc.

15. What is the syntax to create spfile from pfile

Create spfile from pfile

16. What is the order of precedence for parameter files when startup is issued

SpfileSID.ora

Default spfile.ora

InitSID.ora

Default init.ora

17. What is the default location for pfile

$ORACLE_HOME/dbs

18. How can we change parameter values in spfile

Using alter system set command

19. In which state pfile is read

Nomount state

20. How can we override the use of default spfile to start the instance

A specified pfile can override the use of default spfile to start the instance

21. What is the defference between pfile and spfile

Pfile is a text file, spfile is a binary file

Changes made in pfile will affect on the next startup, changes made in spfile are persistent across startup and shutdown

22. What is a parameter file

Parameter file defines the characteristics of the instance

23. What are the types of parameter files

Pfile, spfile

22. How will I know if my database is using a PFILE or SPFILE

Select DECODE (value, NULL, ’pfile’,’spfile’) from sys.v_$parameter where name=’spfile’;

23. How can we view parameter values

Using show parameter command, v$parameter view, v$spparameter view

24. Can we edit spfile

The spfile (server parameter file) is a binary file, and thus cannot be edited

manually.Editing and saving it using a text editor will surely corrupt the file

INVALID OBJECTS FAQ:

How to identify invalid objects?

                   SELECT owner,object_name, object_type, status FROM dba_objects

                   WHERE status = 'INVALID';

What is the syntax to compile invalid objects manually for small no.of  objects?

        Alter object objectname compile;

How to compile all the invalid objects in a specified schema?

  EXEC DBMS_UTILITY.compile _schema (schema => ‘SCOTT’);

  How to recompile invalid objects in the database?

  Using utlrp.sql and utlprp.sql .these scripts are run after major database changes 

  such as upgrades or  patches.these scripts are located in

  $ORACLE_HOME/rdbms/admin directory.Both  scripts must be run as 

  sys user or any user with sysdba priv.The utlrp.sql calls utlprp.sql script with a

  command line parameter 0

           0 - The level of parallelism is derived based on the CPU_COUNT parameter.

          1 - The recompilation is run serially, one object at a time.

          N - The recompilation is run in parallel with "N" number of threads.

  When the objects will become invalid?

                    In a typical running application, you would not expect to see views or stored procedures become

                    invalid because applications typically do not change table structures or change view or stored procedure

                    definitions during normal execution. Changes to tables, views, or PL/SQL units typically occur when an 

                   application is patched or upgraded using a patch script or ad-hoc DDL statements. Dependent objects might

                    be left invalid after a patch has been applied to change a set of referenced objects.

  Eg : when we rebuild a table, the indexes on that table becomes invalid

  What is UTLRP.SQL?
 This script recompiles invalid PLSQL modules.

  What this script does?

  This script recompiles all existing invalid PL/SQL modules in a database.

           This is a fairly general script that can be used at any time to recompile all existing  

         invalid PL/SQL modules in a database If run as one of the last steps during  

         migration/upgrade/downgrade this script will validate all

                 PL/SQL modules (i.e. procedures, functions, packages, triggers, types, views,

                 libraries) during the migration step itself.

packages and package bodies gong invalid when I make schema changes.  How do I recompile invalid objects?

 Here is a script to recompile invalid PL/SQL packages and package bodies

         invalid.sql
 Spool run_invalid.sql 

select

       'ALTER ' || OBJECT_TYPE || ' ' ||

        OWNER || '.' || OBJECT_NAME || ' COMPILE;'

        from

        dba_objects

        where

        status = 'INVALID'

        and

        object_type in ('PACKAGE','FUNCTION','PROCEDURE');      

        spool off;       

        @run_invalid.sql

Synonyms:

 Giving an alternate name to another user i.e., that means hiding the details of user and table details

Two types of privileges :

Public synonym and private synonym

For public synonym you need use public keyword in the synoym while creating

Create  public synonym empinfo  On u1.emp

By granting these synonym to the user  u can hide the details of the user.

Public synonym means that any one can access the data.

Create synonym empinfo On u1.emp

Only the user of the owner of schema and to whom this synonym is granted can accessed

U can also drop a synonym

Drop synonym empinfo;

U can also have os level  authentication

By setting the parameter os_authent_prefix In initialization file .

user looks in this manner

create user  u2 identified by u2

default tablespace ts1

grant quota 5m on ts1

profile p1;