Oracle Database Administration and Concept for the Beginners

 Oracle Database Administration

Agenda

• Oracle Database Architecture

       ( Memory Structure and Background Process)

•  Physical and Logical Structure of Database
• Database startup and shutdown stage
• Manual and DBCA Database creation in 12c 
• Indexes and Types of indexes and Its importan
• User and Role Management
• Checking Logs and Trace Files
• Backup and Recovery
• Health Check of Database  
• 
  

Role of a Database Administrator

• Install and upgrade Oracle Database products
• Creates and maintains all databases required for development, testing, education and production.
• Works in conjunction with System Administrators (SAs) to plan and allocate disk storage for each system.  The process can often depend on the type of disk storage system and how it is integrated into the over system architecture.  
• Work with software developers, network administrators and system administrators to monitor and perform tuning of the database.  The DBA works closely with the software developers because the highest yielding adjustments are often in the sql code used within the application.  The DBA works closely with the system administrators because they often have applications or tools to use in monitoring system usage and can help pinpoint system bottle necks.
• Works in concurrence with Security System Administrators (SSA) to implement and maintain database security including monitoring of auditing logs and database usage by users.
• Performs use maintenance
• Plan and implements backup and recovery strategy of Oracle databases.
• Controls migration of programs, database changes, and reference data changes through the development life cycle.
• Modify database structures as required by system/problem change requests (PCR).
• Acts as the principal Oracle support contact.  This includes using oracle metalink to research questions and open trouble tickets at http://metalink.oracle.com
• Works with Change Management Team (CM) and Software Developers (SDs) to develop and maintain standards in application design and code.  They also work with CM and SD to ensure that system / site standards are being adhered to.
• Evaluates and works with SA, SD, and CM to plan and integrate new releases of Oracle and its tools as well as any other third party products that have been integrated into the database administration or usage.
• Creates, enforces and maintains oracle database constraints to ensure integrity of the database.
• Works closely with software developers to provide technical support for database related issues as well as feedback on possible impact changes to the database objects may cause.  It is the database administrators that perform all administration tasks related to database objects, including tables, clusters, indexes, views, sequences, packages and procedures.
• The DBA should work closely with system engineers and software developers to create and implement the physical database design.

Oracle Database Instance

A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.

Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.

Database Instance Structure

When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:

• Maintaining internal data structures that are accessed by many processes and threads concurrently
• Caching data blocks read from disk
• Buffering redo data before writing it to the online redo log files
• Storing SQL execution plans

The SGA is shared by the Oracle processes, which include server processes and background processes, running on a single computer. The way in which Oracle processes are associated with the SGA varies according to operating system.

Oracle Database Architectural

Redo Log Files

Whenever data is added, removed, or changed in a table, index, or other Oracle object, 

an entry is written to the current redo log file. Every Oracle database must have at least two redo log files, because Oracle reuses redo log files in a circular fashion.

 

When one redo log file is filled with redo log entries, the current log file is marked as ACTIVE,if it is still needed for instance recovery, or INACTIVE, if it is not needed for instance recovery; the next log file in the sequence is reused from the beginning of the file and is marked as CURRENT.Ideally, the information in a redo log file is never used. However, when a power failure occurs,or some other server failure causes the Oracle instance to fail, 

the new or updated data blocks in the database buffer cache may not yet have been written to the datafiles.

 When the Oracle instance is restarted, the entries in the redo log file are applied to the database datafiles in a roll forward operation, to restore the state of the database up to the point where the failure occurred.

To be able to recover from the loss of one redo log file within a redo log group, 

multiple copies of a redo log file can exist on different physical disks. Later in this chapter,you will see how redo log files, archived log files, and control files can be multiplexed to ensure the availability 

and data integrity of the Oracle database.

Control Files

Every Oracle database has at least one control file that maintains the metadata of the database (in other words, data about the physical structure of the database itself). 

Among other things, the control file contains the name of the database, 

when the database was created, and the names and locations of all datafiles and redo log files. 

In addition, the control file maintains information used by RMAN, such as the persistent RMAN settings and the types of backups that have been performed on the database. 

RMAN is covered in depth in Chapter 12. 

Whenever any changes are made to the structure of the database,the information about the changes is immediately reflected in the control file.

Because the control file is so critical to the operation of the database,

 it can also be multiplexed. However, no matter how many copies of the control file are associated with an instance, only one of the control files is designated as primary for purposes of retrieving database metadata.

The ALTER DATABASE BACKUP CONTROLFILE TO TRACE command is another way to back up the control file. 

It produces a SQL script that you can use to re-create the database control file in case all multiplexed binary versions of the control file are lost due to a catastrophic failure.

This trace file can also be used, for example, to re-create a control file if the database needs to be renamed or to change various database limits that could not otherwise be changed without re-creating the entire database. 

As of Oracle 10g, you can use the nid utility to rename the database without having to re-create the control file.

Archived Log Files

An Oracle database can operate in one of two modes: 

ARCHIVELOG or NOARCHIVELOG mode. 

When the database is in NOARCHIVELOG mode, the circular reuse of the redo log files (also known as the online redo log files) means that redo entries

 (the contents of previous transactions) are no longer available 

in case of a failure to a disk drive or another media-related failure. 

Operating in NOARCHIVELOG mode does protect the integrity of the database in the event of an instance failure or system crash, because all transactions that are committed but not yet written to the datafiles are available in the online redo log files.

In contrast, ARCHIVELOG mode sends a filled redo log file to one or more specified destinations and can be available to reconstruct the database 

at any given point in time in the event that a database media failure occurs.

 

For example, 

if the disk drive containing the datafiles crashes, the contents of the database can be recovered to a point in time before the crash,given a recent backup of the datafiles and the redo log files that were generated since the backup occurred.The use of multiple archived log destinations for 

filled redo log files is critical for one of Oracle’s high-availability features known as Oracle Data Guard, formerly known as Oracle Standby Database. Oracle Data Guard is covered in detail in 

Initialization Parameter Files

When a database instance starts, the memory for the Oracle instance is allocated, and one of two types of initialization parameter files is opened: 

either a text-based file called init<SID>.ora (known generically as init.ora or a PFILE) ora server parameter file (otherwise known as an SPFILE). The instance first looks for an SPFILE in the default location for the operating system ($ORACLE_HOME/dbs on Unix, for example) 

as either spfile<SID>.ora or spfile.ora. If neither of these files exists, 

the instance looks for a PFILE with the name init<SID>.ora. 

Alternatively, the STARTUP command can explicitly specify a PFILE to use for startup.

Initialization parameter files, regardless of the format, 

specify file locations for trace files, control files, filled redo log files, 

and so forth. They also set limits on the sizes of the various structures in the SGA as well as how many users can connect to the database simultaneously.

Until Oracle9i, using the init.ora file was the only way to specify initialization parameters for the instance.

Although init.ora is easy to edit with a text editor, it has some drawbacks. 

If a dynamic system parameter is changed at the command line with the ALTER SYSTEM command, 

the DBA must remember to change the init.ora file so that the new parameter value  will be in effect the next time the instance is restarted.

An SPFILE makes parameter management easier and more effective for the DBA.

 If an SPFILE is in use for the running instance, any ALTER SYSTEM command 

that changes an initialization parameter can change the initialization parameter automatically in the SPFILE, 

change it only for the running instance, or both. No editing of the SPFILE is necessary, or even possible without corrupting the SPFILE itself.

Although you cannot mirror a parameter file or SPFILE per se,

 you can back up an SPFILE to an init.ora file, and both the init.ora and

 the SPFILE for the Oracle instance should be backed up using conventional operating system commands or using RMAN in the case of an SPFILE.

When the DBCA is used to create a database, an SPFILE is created by default.

Alert and Trace Log Files

When things go wrong, Oracle can and often does write messages to the alert log and,in the case of background processes or user sessions, trace log files.

The alert log file, located in the directory specified by the initialization 

parameter BACKGROUND_DUMP_DEST, contains both routine status messages and error conditions.

 When the database is started up or shut down, a message is recorded in the alert log, along with a list of initialization parameters that are different from their default values. 

In addition, any ALTER DATABASE or ALTER SYSTEM commands issued by the DBA are recorded. Operations involving tablespaces and their datafiles are recorded here, too, such as adding a tablespace, dropping a tablespace, and adding a datafile to a tablespace.

 Error conditions, such as tablespaces running out of space, corrupted redo logs, and so forth, are also recorded here.

Password Files

An Oracle password file is a file within the Oracle administrative or software directory structure on disk used to authenticate Oracle system administrators for tasks such as creating a database or starting up and shutting down the database. 

The privileges granted through this file are 

the SYSDBA and SYSOPER privileges. Authenticating any other type of user is done within the database itself;because the database may be shut down or not mounted, another form of administrator authentication is necessary 

in these cases.

The Oracle command-line utility orapwd creates a password file if one does not exist or is damaged.Because of the extremely high privileges granted via this file, it should be stored in a secure directory location 

that is not available to anyone except for DBAs and operating system administrators.

 Once this file is created, the initialization parameter 

REMOTE_LOGIN_PASSWORDFILE should be set to EXCLUSIVE 

to allow users other than SYS to use the password file. Also, the password file must be in the $ORACLE_HOME/dbs directory.

Connecting to an Instance

• 

• The user starts a tool such as SQL*Plus,sql developer,toad or runs an application developed using a tool such as Oracle Forms. This application or tool is executed as a user process.
• In the most basic configuration, when a user logs on to the Oracle server, a process is created on the computer running the Oracle server. This process is called a server process. The server process communicates with the Oracle instance on behalf of the user process that runs on the client. The server process executes SQL statements on behalf of the user.
• User Process is started at the time a database user requests a connection to the oracle server. Server Process connect to the oracle instance and started when a user establishes a session.
• user process & server process are used to manage the execution of sql/plsql statements.
• one-to-one correspondence between the User and Server Processes. This is called a Dedicated Server connection. An alternative configuration is to use a Shared Server where more than one User Process shares a Server Process.
• Server Process is the go-between for a Client Process and the Oracle Instance.
• Dedicated Server environment – there is a single Server Process to serve each Client Process.
• Shared Server environment – a Server Process can serve several User Processes, although with some performance reduction

Processing a DML Statement

During processing of a data manipulation statement (DML, i.e. SQL insert, update or delete), the basic processing is like for queries. I.e. handling of SQL and the library cache is the same. 

A new element is now identified in the SGA: the log buffer. The log buffer logs all changes made to the buffer cache, so that these changes can be redone in case of a recovery. The processing steps are: 

The block, that needs to be modified is read from the disk (unless it is already found in the cache) 

 The server process (on behalf of the SQL statement executed by the client), makes an entry into the log buffer, specifying the operation to be done on the data block; in case of a later recovery, the redo information is needed. The picture shows that the redo log entry indicates which block is being updated (it is actually more than simply a block number), and it shows the actual change. No information is logged about the previous value in the block, as this information is not needed to redo the operation

3) The server process makes the actual change in the block in the buffer cache. In the picture, the value “ABC” is replaced by the value “DEF”. At this point in time, the block in the buffer cache is different from the block in the datafile; this is called a dirty block. 

4. The user issues the commit operation, and the server process indicates this to the log writer background process. This process is part of the Oracle instance, and is started when the instance starts. 

5. The log writer process writes the log buffer to a redo log file. At this time, in case of recovery, the old block, found in the database file, and the redo log record, found in the redo log file, can be used to redo the change made by the user

Rollback Segment

A rollback segment is an Oracle database structure that stores undo information for transactions. Undo information is the original information that was changed during a transaction. It restores the changed database information back to what it was before a transaction changed it.

 The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

Logical Structure 

• An Oracle database is made up of several logical storage structures, including data blocks, extents and segments, tablespaces, and schema objects.
• The actual physical storage space in the data files is logically allocated and deallocated in the form of Oracle data blocks. The Database Block Size (DB_BLOCK_SIZE) is the smallest unit of I/O that can be used for accessing data or index files in Oracle database. Oracle reserves a portion of each block for maintaining information, such as the address offset of all the rows contained in the block and the type of information stored in the block.
• An extent is a set of logically contiguous data blocks allocated for storing a specific type of information. Physically, they could be in different file system extents and/or different physical LUNs or drives. A table is comprised of one or more extents. The very first extent of a table is known as the initial extent. When the data blocks of the initial extent become full, Oracle allocates an incremental extent. The incremental extent does not have to be the same size (in bytes) as the initial extent.
• A segment is the collection of extents that contain all of the data for a particular logical storage structure in a tablespace,such as a table or index. There are four different types of segments, each corresponding to a specific logical storage structure type:

1. Data segments
2. Index segments
3. Rollback (Undo) segments
4. Temporary segments

    Data segments store all the data contained in a table, partition, or cluster. Likewise,

     Index segments store all the data contained in an index. For backward compatibility, 

    Rollback (undo) segments are used to hold the previous contents of an Oracle data block prior to any change made by a particular transaction. If any part of the transaction should not complete successfully, the information contained in the rollback (undo) segments is used to restore the data to its previous state.

 In Oracle 9i, 10g and 11g this is achieved using automatic undo and undo tablespaces, which allows better control and use of the server resources.

Rollback (undo) segments are also used to provide read-consistency. There are two different types of read-consistency: statement-level and transaction-level.

Statement-level read consistency ensures that all of the data returned by an individual query comes from a specific point in time: the point at which the query started. This guarantees that the query does not see changes to the data made by other transactions that have committed since the query began. 

This is the default level of read consistency provided by Oracle.

In addition, Oracle offers the option of enforcing transaction-level read consistency. Transaction-level read consistency ensures that all queries made within the same transaction do not see changes made by queries outside of that transaction but can see changes made within the transaction itself. These are known as serializable transactions.

Temporary segments are used as temporary workspaces during intermediate stages of a query’s execution.

They are typically used for sort operations that cannot be performed in memory.

The following types of queries may require a temporary segment:

SELECT.....ORDER BY

SELECT.....GROUP BY

SELECT.....UNION

SELECT.....INTERSECT

SELECT.....MINUS

SELECT DISTINCT.....

CREATE INDEX.…

Tablespaces are used to group related logical entities or objects together in order to simplify physical management of the database. Tablespaces are the primary means of allocating and distributing database data at the file system or physical disk level. 

Tablespaces are used to:

• Control the physical disk space allocation for the database
• Control the availability of the data by taking the tablespaces online or off-line
• Distribute database objects across different physical storage devices to improve performance
• Regulate space for individual database users
• Every Oracle database contains tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace contains the data dictionary tables for the database used to describe its structure. The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade.
• The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases. 
• When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database.A locally managed SYSTEM tablespace cannot be used for default temporary storage. When SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage.
• Schema objects are the logical structures used to refer to the database’s data. A few examples of schema objects would be tables, indexes, views, and stored procedures. Schema objects, and the relationships between them, constitute the relational design of a database.

Oracle Physical Storage Structures

The Oracle database uses a number of physical storage structures on disk to hold and manage the data from user transactions. 

Some of these storage structures, such as the datafiles, redo log files, and archived redo log files, hold actual user data; other structures, such as control files, maintain the state of the database objects, and 

text-based alert and trace files contain logging information for both routine events and error conditions in the database.

 Figure 1-3 shows the relationship between these physical structures and the logical storage structures 

we reviewed in the earlier section “Oracle Logical Database Structures.” 

Figure 1-3: Oracle physical storage structures 

Datafiles

Every Oracle database must contain at least one datafile. One Oracle datafile corresponds to 

one physical operating system file on disk. Each datafile in an Oracle database is a member of one and

 only one tablespace; a tablespace, however, can consist of many datafiles. 

(A bigfile tablespace consists of exactly one datafile.)

An Oracle datafile may automatically expand when it runs out of space,

 if the DBA created the datafile with the AUTOEXTEND parameter. 

The DBA can also limit the amount of expansion for a given datafile by using the MAXSIZE parameter. In any case, the size of the datafile is ultimately limited by the disk volume on which it resides.

Tip -The DBA often has to decide whether to allocate one datafile that can AUTOEXTEND indefinitely

 or to allocate many smaller datafiles with a limit to how much each can extend. 

In earlier releases of Oracle, you had no choice but to have multiple datafiles and

 manage the tablespace at the datafile level. Now that you can have bigfile tablespaces, 

you can manage most aspects at the tablespace level. 

Now that RMAN can back up a bigfile tablespace in parallel as well (since Oracle Database 11g), 

it makes sense to create one datafile and let it AUTOEXTEND when necessary.

The datafile is the ultimate resting place for all data in the database.

 Frequently accessed blocks in a datafile are cached in memory; similarly, 

new data blocks are not immediately written out to the datafile but 

rather are written to the datafile depending on when the database writer process is active. 

Before a user’s transaction is considered complete, 

however, the transaction’s changes are written to the redo log files.

Tip -Create at least one user other than SYS or SYSTEM who has DBA privileges 

for daily administrative tasks. If there is more than one DBA administering a database, 

each DBA should have their own account with DBA privileges.

Alternatively, authentication for the SYSDBA and SYSOPER privileges 

can be done with OS authentication; in this case, a password file does not have to be created, 

and the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to NONE.

1.1.1Access to the database is granted to a database account known as a user. 

A user may exist in the database without own ALL_%, DBA_%,USER_%Tables

There are many system tables which can be used to determine user/system information. 

Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views.

 To list the data dictionary views available to you, query the view DICTIONARY.

SQL>select * from DICTIONARY

Many data dictionary tables have three corresponding views:

■ An ALL_ view displays all the information accessible to the current user, 

including information from the current user's schema as well as information from objects in other schemas, 

if the current user has access to those objects by way of grants of privileges or roles.

■ A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators.

 They can be accessed only by users with the SELECT ANY TABLE privilege. 

This privilege is assigned to the DBA role when the system is initially installed.

■ A USER_ view displays all the information from the schema of the current user. 

No special privileges are required to query these views.

Examples:

If you have the proper privileges you can see all the tables within a database by querying the DBA_TABLES view.

SQL>SELECT OWNER,TABLE_NAME FROM DBA_TABLES;

If you want to see all the tables the current user has access to view use the following SQL statement.

SQL>SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

If you only want to see the tables the current user owner use the next SQL statement.

SQL>SELECT TABLE_NAME FROM USER_TABLES;

I will list a few of the essential ones below and you can do a DESC on them to see the available columns.  

Table Name                 Use

*_ALL_TABLES Determine Table Information

*_CONS_COLUMNS Determine the type of constraints on columns.

*_CONSTRAINTS Determine all constraints 

*_ERRORS *_ERRORS describes current errors on stored objects

 (views, procedures, functions, packages, and package bodies) in the database.

DBA_LOCK, DBA_LOCKS DBA_LOCK lists all locks or latches held in the database, 

and all outstanding requests for a lock or latch.

Database Startup Step 

Database Shutdown

You can not go back down the stair step depicted in Figure 11, you have to SHUTDOWN the database completely and then you can open it to the state you need.  The SHUTDOWN command closes the database, dismounts the database and then shuts down the instance.  There are several SHUTDOWN options that can be employed.

SQL>SHUTDOWN NORMAL

The database will only SHUTDOWN when all users have logged off and all work is committed (or rolled back).  

No new connections are allowed.

This option is difficult to use in man environments since the users are applications and all the applications have to be shutdown before the database would shutdown.

SQL>SHUTDOWN IMMEDIATE

Disconnects all users and performs rollback on all uncommitted data by using PMON

Terminates all current SQL statements

SQL>SHUTDOWN ABORT

Shuts down without tidying up; akin to system failure and requires system recovery on start-up

No rollback of uncommitted transactions

The Basics - Shutting Down Database

Position to the correct database (setting the Oracle SID):

For example, in UNIX: Or, in NT:

     $ . oraenv          c:\> set ORACLE_SID=PROD

          PROD                              c:\> setORACLE_HOME=d:\oracle\v9204

Connect as sysdba (from a dba group user login, such as oracle):

Oracle 8.1.5 and above: Prior to Oracle 9i, use Server Manager:

     $ sqlplus “/ as sysdba”     $ svrmgrl

     (SQL> connect / as sysdba)       SVRMGR>  connect internal

Shut down the database (connected as sysdba):

     SQL> shutdown immediate   (kills sessions, rolls back pending changes)

If “shutdown immediate” fails or hangs (do this in another session):

     SQL> shutdown abort

     SQL> startup

     SQL> shutdown immediate

Real-Time Example

----------------

In this example ,We have creating easyuser and providing select privilete

1) check if user exist

select username,account_status,profile from dba_users where username like 'easyuser';

2) easyuser don't exist , so we will take metadata for similiar type of user

set long 9999999

set pagesize 0

set linesize 120 

select DBMS_METADATA.GET_DDL('USER','EASYUSER2') from dual;

create user easyuser identified by <PASSWORD> default tablespace users temporary tablespace temp;

ALTER USER BZ2805 quota unlimited on users;

GRANT CREATE SESSION to  easyuser;

Grant succeeded.

SQL> GRANT CONNECT to  easyuser;

select username,account_status,profile from dba_users where username like 'easyuser';

grant READ_ROLE to easyuser

Dynamic query to generate privilege

select 'GRANT SELECT ON '||OWNER||'.'||object_name ||' TO R_ODS_DAT;' 

from dba_objects where owner in ('INTERNSCHEMA','EASYSCHEMA') and object_type='TABLE';

To reset password

select

   username,

   last_login,

   password

from

   dba_users where username in ('EASYUSER','EASYUSER2')

col name format a20

col password format a60

select name,password  from sys.user$ where name in ('EASYUSER','EASYUSER2')

alter user EASYUSER identified by Infra123

select username,account_status,profile from dba_users where username like 'easyuser';

To check privilege

SQL> select * from dba_role_privs where grantee='easyuser';

no rows selected

SQL> select * from dba_sys_privs where grantee='easyuser';

no rows selected

SQL>

select * from dba_tab_privs where grantee='easyuser'; 

grant CONNECT,EASY_ADMIN_ROLE to 'easyuser

grant create session to 'easyuser

grant CREATE SYNONYM to 'easyuser