Course
Objective:
• Installing, creating, and administering an Oracle
Database 10g Enterprise Edition database
• Configuring the database for an application
• Implementing a backup and recovery strategy
• Employing basic monitoring procedures
Tasks
of an Oracle Database Administrator :
Following is the
approach for designing, implementing and maintaining an Oracle database:
1. Evaluate the
database server hardware.
2. Install the Oracle
software.
3. Plan the database.
4. Create and open the
database.
5. Back up the
database.
6. Enroll system users.
7. Implement the
database design.
8. Recover from
database failure.
9. Monitor database
performance
10. Administering users
11. Managing Schema
12. Managing data
13. Oracle Database
Security
14. Proactive
maintenance
15. Monitoring and
Resolving Lock Conflicts
16. Database Recovery
Database
Architecture
The files that constitute an
Oracle database are organized into the following:
• Control files: Contain data about the database itself, called the
metadata. These files are critical to the database. Without them you cannot open the data files to
access the data within the database.
• Data files: Contain the data of the database.
• Online redo log files: Allow for instance recovery of the database.
If the database crashes and does not lose any data files, the instance can
recover the database with the information in these files.
There are other files which are
not officially part of the database but are important to the successful running
of the database. These are:
• Parameter file: Used to define how the instance will be configured
when it starts up.
• Password file: Allows users to connect remotely to the database and
perform administrative tasks.
• Archive log files: Contain an ongoing history of the redo generated
by the instance. These files allow for database recovery; using these files and
a backup of the database, you can recover a lost data file.
Oracle Memory
Structures
The basic memory structures
associated with an Oracle instance include:
• System Global Area (SGA): Shared by all server and background
processes
• Program Global Area (PGA): Private to each server and background
process; there is one PGA for each process.
The System Global Area (SGA) is a
shared memory area that contains data and control information for the instance.
The SGA includes the following
data structures:
• Database buffer cache: Caches blocks of data retrieved from the
database.
• Redo log buffer: Caches redo information (used for instance
recovery) until it can be written to the physical redo log files stored on disk.
• Shared pool: Caches various constructs that can be shared among
users.
• Large pool: Optional area used for buffering large I/O requests.
• Java pool: Used for all session-specific Java code and data within
the Java Virtual Machine (JVM).
• Streams pool: Used by Oracle Streams
Data Dictionary:
The data dictionary is the
central set of tables and views that are used as a read-only reference about a
particular database. A data dictionary stores information such as:
• The logical and physical
structure of the database
• Valid users of the database
• Information about integrity
constraints
• How much space is allocated for
a schema object and how much of it is in use.
A data dictionary is created when
a database is created and is automatically updated when the structure of the
database is updated.
The data dictionary is where
Enterprise Manager retrieves information about objects in the database. You can
also select information from the data dictionary tables. Enterprise Manager does
this for you and presents the information in a very easy-to-use format. The
DICTIONARY view contains descriptions of data dictionary tables and views.
These tables and views generally have one of three prefixes:
• USER: Information pertaining to the objects owned by the current
user.
• ALL: Information pertaining to the objects accessible to the
current user.
• DBA:
Information pertaining to every object in the database.
Database Control
Oracle Database 10g ships
with Oracle Enterprise Manager’s Database Control. Database Control is a
web-enabled control console that the database administrator can use for:
• Performance monitoring
• Managing proactive alerts
• Controlling maintenance wizards
and advisors
• User and database object
administration
• Database backup and recovery
• Storage management and much
more.
Grid Control
Database Control’s capabilities
can be extended and integrated with the rest of your systems using Oracle
Enterprise Manager’s Grid Control. The architecture of the Grid Control
framework provides a high level of flexibility and functionality. We can easily
customize Enterprise Manager to suit the monitoring and administrative needs of
our environment.
The typical Enterprise Manager Framework
configuration consists of the following functional areas:
• Managed targets
• Management services
• Web-based grid control
• Database control
• Application server control
Creating a Database
A) Manually Creation Of Database:
Step1: create folder as
“F:\NAVEEN”
Step2: create following
folders under “F:\NAVEEN” named as following.
1) pfile
2) udump
3) cdump
4) bdump
5) adump
6) create
7) flash_recovery_area
Step3: under pfile create a text file “F:\naveen\pfile\initnaveen.ora”
Step4: write following information in text
file
db_name=naveen
instance_name=naveen
db_block_size=8192
background_dump_dest=F:\naveen\bdump
core_dump_dest=F:\naveen\cdump
user_dump_dest=F:\naveen\udump
audit_file_dest=F:\naveen\adump
control_files=("F:\naveen\create\control01.ctl",
"F:\naveen\create\control02.ctl",
"F:\naveen\create\control03.ctl")
db_recovery_file_dest=F:\naveen\flash_recovery_area
db_recovery_file_dest_size=2147483648
compatible=10.2.0.1.0
sga_target=290455552
remote_login_passwordfile=EXCLUSIVE
pga_aggregate_target=96468992
undo_management=auto
undo_tablespace=UNDOTBS1
Step5: create a text
file as following:”F:\naveen\create.sql”
create database naveen
LOGFILE
group
1('F:\naveen\create\redo1a.log','F:\Aptech\create\redo1b.log') size 100M,
group
2('F:\naveen\create\redo2a.log','F:\Aptech\create\redo2b.log') size 100M
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXLOGHISTORY 1
MAXDATAFILES 50
MAXINSTANCES 1
datafile
'F:\naveen\create\system.dbf' size 500M
sysauxdatafile
'F:\naveen\create\sysaux.dbf' size 300M
default temporary
tablespace temp1
tempfile
'F:\naveen\create\temp1.dbf' size 100M
undo tablespace
UNDOTBS1
datafile
'F:\naveen\create\undotbs1.dbf' size 300M
Step6: set parameter to
the database using cmd and startup the database using SQLPlus instance
>cd\
>oradim –new
–sid naveen –intpwd sys
> set
oracle_sid=naveen
>sqlplusw
Now DBA is connected to
an idle instance and now start the oracle instance with no mount
Step 7: Now create database by the
following commands
>
@F:\Aptech\create.sql
> /
>@F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql;
>@F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql;
Now the database has been created. Now we
can create tables, views etc. in database.
B) Use DBCA (Database configuration Assistant) to
1) Create
Database
2) Configure
a database
3) Drop
a database
4) Manage
templates
Database User
Accounts
To access the database a user
must specify a valid database user account and successfully authenticate as
required by that user account.
In some systems, each database
user has his or her own database account. In others, many users share a common
database account. Regardless of which model your database follows, each user
account will have:
• A unique username. Usernames cannot exceed 30 characters, cannot
contain special characters, and must start with a letter.
•
An authentication method. The most common authentication method is a
password, but Oracle Database 10g supports several other authentication
methods including biometric, certificate, and token authentication.
• A default tablespace. A place where the user will create objects if
the user does not specify some other tablespace. Note that having a default
tablespace does not imply the user has the privilege of creating objects
in that tablespace nor a quota of space within that tablespace in which
to create objects. Both these are granted separately.
• A temporary tablespace. A place where the user can create temporary
objects such as sorts
and temporary tables.
• A user profile. A set of resource and password restrictions
assigned to the user.
When starting the database, we
select the state in which it starts. The following scenarios describe different
stages of starting up an instance.
Starting the
Instance (NOMOUNT)
An instance is started in the
NOMOUNT stage only during database creation or the re-creation of control
files.
Starting an instance includes the
following tasks:
• Reading the initialization file
from $ORACLE_HOME/dbs in the following order:
- First spfileSID.ora
- If not found then, spfile.ora
- If not found then, initSID.ora
Specifying the PFILE parameter
with STARTUP overrides the default behavior.
• Allocating the SGA
• Starting the background
processes
• Opening the alertSID.log file and the trace files
Mounting the
Database (MOUNT)
To perform specific maintenance
operations, you start an instance and mount a database but do not open the
database.
For example, the database must be
mounted but not open during the following tasks:
• Renaming data files (data files
for an offline tablespace can be renamed when the database is open)
• Enabling and disabling online
redo log file archiving options
• Performing full database
recovery Mounting a database includes the following tasks:
• Associating a database with a
previously started instance
• Locating and opening the
control files specified in the parameter file
• Reading the control files to
obtain the names and status of the data files and online redo log files.
However, no checks are performed to verify the existence of the data files and
online redo log files at this time.
Opening the
Database (OPEN)
Normal database operation means
that an instance is started and the database is mounted and open. With normal
database operation, any valid user can connect to the database and perform
typical data access operations.
Opening the database includes the
following tasks:
• Opening the online data files
• Opening the online redo log
files
If any of the data files or
online redo log files is not present when you attempt to open the database, the
Oracle server returns an error.
During this final stage, the
Oracle server verifies that all the data files and online redo log files can be
opened and checks the consistency of the database. If necessary, the SMON
background process initiates instance recovery.
Oracle stores data logically in tablespaces and physically in data files.
• Tablespaces:
– Can belong to only one database at a time
– Consist of one or more data files
– Are further divided into logical units of storage
• Data files:
– Can belong to only one tablespace and one database
– Are a repository for schema object data
Tablespaces and
Data Files
Databases, tablespaces, and data
files are closely related, but they have important differences:
• An Oracle database consists of
one or more logical storage units called tablespaces, which collectively store
all of the database’s data.
• Each tablespace in an Oracle
database consists of one or more files called data files, which are physical
structures that conform with the operating system on which Oracle is running.
• A database’s data is
collectively stored in the data files that constitute each tablespace of the database.
For example, the simplest Oracle database would have one tablespace and one
data file. Another database can have three tablespaces, each consisting of two
data files (for a total of six data files). A single database could potentially
have as many as 65,535 data files.
Space Management
in Tablespaces
Tablespaces allocate space in
extents. Tablespaces can be created to use one of the following two
different methods of keeping
track of free and used space:
Locally managed
tablespaces: The
extents are managed within the tablespace via bitmaps. Each
bit in the bitmap corresponds to
a block or a group of blocks. When an extent is allocated or freed
for reuse, the Oracle server
changes the bitmap values to show the new status of the blocks.
Dictionary-managed
tablespaces: The
extents are managed by the data dictionary. The Oracle
server updates the appropriate
tables in the data dictionary whenever an extent is allocated or
deallocated. This is for backward compatibly; you
should use locally managed for all tablespaces.
Creating a New
Tablespace
To create a tablespace, perform
the following steps:
1. Navigate to the Tablespaces
page. Go to the Administration tab, then click Tablespaces under the Storage
heading.
2. Click the Create button.
Note: If you want to
create a tablespace that is like an existing tablespace, select an existing
tablespace and select Create Like from the Actions menu. Click Go. The Create
Tablespace General page appears.
3. Enter a name for the
tablespace.
4. Under the Extent Management
heading, select LocallyManaged. The extents of a locally managed tablespace are
managed efficiently within the tablespace by the Oracle database server. For a
dictionary managed tablespace you must more actively manage extents and data
dictionary access is required for tracking them. Dictionary managed tablespaces
are being deprecated. Oracle does not suggest their use.
5. Under the Type heading, select
Permanent. Permanent tablespaces store permanent database objects created by
the system or users.
6. Under the Status heading,
select Read Write. Read/write status means users can read and write to the
tablespace after it is created. This is the default.
7. In the Datafiles region of the page click Add to
add datafiles to the tablespace, a tablespace must have at least one file.
Bigfile tablespaces are used with ultra large databases where Oracle’s
Automatic Storage Management or other logical volume managers support striping
or RAID, and dynamically extensible logical volumes.
8. In the Add Datafiles page,
enter a file name. Accept the defaults for the File Directory and File Size.
9. Under the Storage region,
select “Automatically extend datafile when full (AUTOEXTEND)” and specify an
amount in the Increment field by which you want to extend the data file each
time it fills. Leave the Maximum File Size set to Unlimited. Click OK. You are
returned to the Create Tablespace General page.
10. Click the Storage tab. The
Create Tablespace Storage page appears.
11. Accept all of the defaults on
the Storage page.
12. Click the Thresholds tab to
open the Thresholds page. This page enables you to set monitored thresholds for
space usage. You receive advice and an option for action when the threshold is
reached.
13. After specifying thresholds
click OK to add the tablespace. You are returned to the Tablespaces page where
you receive a confirmation of the creation of the tablespace. You can view your
new tablespace in the Results section.
Database
Security
A secure system ensures the confidentiality of the data it contains.
There are several aspects of security:
• Restricting access to data and services
• Authenticating users
• Monitoring for suspicious activity
Oracle Database 10g provides
the industry’s best framework for a secure system, but for that framework to be
effective the database administrator should follow best practices and
continually monitor database activity.
Restricting
Access to Data and Services
All users should not have access
to all data. Depending on what is stored in your database, restricted access
can be mandated by business requirements, customer expectations, and
increasingly by legal restrictions. Credit card information, health care data,
identity information, and more must be protected from unauthorized access.
Oracle provides extremely fined-grained authorization controls to limit
database access. Restricting access should include applying the principal of
least privilege.
Authenticating
User
To enforce access controls on
sensitive data, the system must first know who is trying to access the data.
Compromised authentication can render all other security precautions useless.
The most basic form of user authentication is by challenging the user to
provide something they know such as a password. Ensuring that passwords follow
simple rules can greatly increase the security of your system. Stronger
authentication methods include requiring the user to provide something they
have, such as a token or Public Key Infrastructure (PKI) certificate. An even
stronger form of authentication is to identify the user through a unique
biometric characteristic such as a fingerprint, iris scan, bone structure
patterns, and so on. Oracle supports advanced authentication techniques such as
token-, biometric-, and certificate-based identification through the Advanced
Security Option. User accounts that are not in use should be locked to prevent
attempts to compromise authentication.
Monitoring for
Suspicious Activity
Even authorized, authenticated
users can sometimes compromise your system. Identifying unusual database
activity such as an employee who suddenly begins querying large amounts of
credit card information, research results, or other sensitive information, can
be the first step to detecting information theft. Oracle provides a rich set of
auditing tools to track user activity and identify suspicious trends.
Protect the Data
Dictionary
Non administrators do not need
access to the data dictionary, but may gain access if you grant one of the *
ANY TABLE system privileges such as SELECT ANY TABLE or UPDATE ANY TABLE. The
data dictionary contains information that a malicious user can leverage to
penetrate or damage your system. To exempt data dictionary tables from the *
ANY TABLE privileges, set the O7_DICTIONARY_ACCESSIBILITY initialization
parameter to FALSE.
If there are non administrator
users who do need access to the data dictionary, you can grant that
access by:
• Using standard GRANT commands
to allow the user to see the specific data dictionary objects required
• Granting the SELECT ANY
DICTIONARY system privilege to give access to the entire data dictionary
In Oracle Database 10g and
Oracle9i Database, the default value for O7_DICTIONARY_ACCESSIBILITY is
FALSE; however, in Oracle8i and earlier, it defaults to TRUE; therefore with
older version you should manually set it to FALSE to enable data dictionary
protection.
Monitoring or auditing should be an integral part of your security
procedures.
Oracle’s built-in audit tools include:
• Database auditing
• Value-based auditing
• Fine-grained auditing (FGA)
Standard
Database Auditing
Enabled through the AUDIT_TRAIL parameter
• NONE: Disables collection of audit records
• DB: Enables auditing with records stored in the
database
• OS: Enables auditing with records stored in the
operating system audit trail
Can audit:
• Login events
• Exercise of system privileges
• Exercise of object privileges
• Use of SQL statements
Auditing SYSDBA and SYSOPER Users User’s with SYSDBA or SYSOPER
privileges can be
connecting with the database closed.
• Audit trail must be stored outside of the database.
• Connect as SYSDBA or SYSOPER is always audited.
• Enable additional auditing of SYSDBA or SYSOPER
actions with audit_sys_operations.
• Control audit trail with audit_file_dest. Default
is:
– $ORACLE_HOME/rdbms/audit (UNIX/Linux)
– Windows Event Log (Windows)
Performance
Monitoring
To administer Oracle Database 10g
and keep it running smoothly, the database administrator must regularly
monitor performance to locate bottlenecks and correct problem areas.
There are hundreds of performance
measurements the DBA can look at, covering everything from network performance
to disk input/output speed to the time spent working on individual application
operations. These performance measurements are commonly referred to as database
metrics.
Monitoring
Methodologies
• Reactive
• Proactive
– Server-generated alerts
– Automated Database Diagnostic Monitor (ADDM)
Optimizer
Statistics
Optimizer statistics for tables
and indexes are stored in the data dictionary. These statistics are not
intended to provide real-time data. They provide the optimizer a statistically
correct snapshot of data storage and distribution which the optimizer uses
to make decisions on how to access data.
Metrics collected include:
• Size of the table or index in
database blocks
• Number of rows
• Average row size and chain
count (tables only)
• Height and number of deleted
leaf rows (indexes only)
As data is inserted, deleted, and
modified these values change. The performance impact of maintaining real-time
data distribution statistics would be prohibitive, so these statistics are
updated by periodically gathering statistics on tables and indexes.
If your database was created with
the Database Creation Assistant (DBCA), optimizer statistics are automatically
collected once a day between 10:00 PM and 6:00 AM. If your database was not
created with the DBCA, or if your database contains tables and indexes that are
very volatile, you must manually collect optimizer statistics.
A large table that experiences 10
percent growth (or reduction) within a 24-hour period is usually considered too
volatile for statistics collection once per day to be sufficient. For tables that
experience this level of change, Oracle recommends collecting statistics more
frequently, preferably often enough that the table never changes by more than
about 10 percent between collection periods.
Statistics can be manually
collected using Enterprise Manager, or through the use of the DBMS_STATS
package as shown below:
SQL> EXEC
dbms_stats.gather_table_stats(‘HR’,’EMP’);
SQL> SELECT num_rows FROM dba_tables
2 WHERE owner='HR' AND table_name = 'EMP';
NUM_ROWS
----------
Use Enterprise Manager to:
• Find key performance issues
• Drill down to the root cause
Enterprise Manager’s Performance
page is the best place to start if you notice degradation in system performance.
The Performance page is divided
into three main regions:
• Host: Server metrics showing the number of waiting processes and
the amount of memory paging
• Active Sessions: Waiting and Working: An overview of instance
performance showing grouped metrics from multiple categories. If one category
is consuming a significant portion of the wait time for the instance, that
category is where troubleshooting should start.
• Instance Throughput: Information about current sessions, redo
generation, and data file read activity.
Each of these sections can be
drilled into by clicking the metric of interest. In the example shown in the
slide, users are spending a significant portion of their time waiting on a
configuration issue. Click the Configuration measurement to drill down and see
where the problem is.
Disk I/O contention is often a
performance bottleneck. Oracle suggests:
• Putting redo log files on
separate disks
• Separating index and data
tablespace files
Undo Data
Undo data is:
• A copy of original, premodification, data
• Captured for every transaction that changes
data
• Retained at least until the transaction is ended
• Used to support:
– Rollback operations
– Read-consistent and flashback queries
– Recovery from failed transactions
Undo information is retained for
all transactions at least until the transaction ends by:
• Users changing their mind
(rolls back)
• Users ending a transaction
(commits)
• User-session abnormally
terminating (rolls back)
• User-session normally
terminating with an exit (commits)
Undo information may be retained
longer depending on the amount of database activity
and the database
configuration.
Storing Undo Information
Undo information is stored in undo segments, which are in turn stored in
an undo tablespace. Undo
tablespaces:
• Are only used for undo segments
• Have special recovery considerations
• May only be associated with a single instance, and
an instance can only have one active undo
tablespace at a time
Monitoring Undo
Undo usually requires little management.
Areas to monitor include:
• Undo
tablespace free space
• “Snapshot
too old” errors
Administering Undo
Administration of undo should include preventing:
• Undo tablespace space errors
– Size the undo tablespace properly
– Ensure large transactions commit periodically
• “Snapshot too old” errors
– Configure an appropriate undo retention interval
– Size the undo tablespace properly
– Consider guaranteeing undo retention
DBA
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS1
Configuring Undo Retention
DBA
UNDO_RETENTION=0
Undo retention specifies (in seconds) the amount of already committed
undo information to retain.
• Default value is 0 (automatic).
• Maximum value is 232 seconds (more than 187
years).
• A setting of 0 indicates automatic undo retention
mode.
Committed
undo information will be overwritten rather than cause transactions to fail for
lack of undo space unless undo retention is “guaranteed.”
SQL> ALTER
TABLESPACE undotbs1 RETENTION GUARANTEE;
Tablespace
altered.
SQL> SELECT
contents, retention
2 FROM
dba_tablespaces
3 WHERE TABLESPACE_NAME='UNDOTBS1';
CONTENTS
RETENTION
---------
-----------
UNDO GUARANTEE
Using
the Undo Advisor
The Undo Advisor is accessed
through the Undo Management properties page. It provides an estimate of the
undo tablespace size required to satisfy a given undo retention.
Enter the desired retention
period and click the Update Analysis and Graph button. The analysis section of
the advisor displays the tablespace size required to support the retention
period.
We can also click a point on the
graph to see the tablespace size required to support the
selected period.
After we have selected an undo
retention period, click OK to implement the new retention period.
Locks
• Prevent multiple sessions from changing the same
data at the same time
• Automatically obtained at the lowest possible level
for a given statement
Locking Mechanism
• High level of data concurrency
– Row-level locks for inserts, updates, and deletes
– No locks required for queries
• Automatic queue management
• Locks held until transaction ends (with commit or
rollback operation)
Each DML transaction must acquire two locks:
• Row-exclusive lock for the row or rows being updated
• Shared table-level lock for the table containing the
rows
The enqueue mechanism keeps track of:
• Sessions waiting for locks
• The requested lock mode
• The order in which sessions requested the lock
Possible Causes of Lock Conflicts
• Uncommitted changes
• Long-running transactions
• Unnecessarily high locking levels
To
resolve a lock conflict:
• Have the session holding the lock commit or roll
back.
• Terminate the session holding the lock as a last
resort.
Backup
and Recovery Issues
The administrator’s duty is to:
• Protect the database from failure wherever possible.
• Increase the Mean-Time-Between-Failures (MTBF).
• Decrease the Mean-Time-To-Recover (MTTR).
• Minimize the loss of data.
Failures can generally be divided into the following categories:
• Statement failure
• User process failure
• Network failure
• User error
• Instance failure
• Media failure
Instance or crash recovery:
• Is caused by attempts to open a database whose files
were not synchronized on shutdown
• Is automatic
• Uses information stored in redo log groups to
synchronize files
• Involves two distinct operations
– Rolling forward: Data files are restored to their
state before the instance failed.
– Rolling back: Changes made but not committed are
returned to their original state.
Phases
of Instance Recovery:
1. Data
files out-of-sync
2. Roll
forward (redo)
3.
Committed and non committed data in files
4. Roll
back (undo)
5.
Committed data in files
Tuning Instance Recovery
• During instance recovery the transactions between
the checkpoint position and end of redo log must be applied to the data files.
• Tune instance recovery by controlling the difference
between the checkpoint position and end of redo log.
To configure your database for maximum recoverability:
• Schedule regular backups
• Multiplex control files
• Multiplex redo log groups
• Retain archived copies of redo logs
Protect against database failure by multiplexing control files.
• At least two copies (Oracle suggests three)
• Each copy on a separate disk
• At least one copy on a separate disk controller
Multiplexing redo log groups to protect against media failure and
loss of data.
• At least two members (files) per group
• Each member on a separate disk drive
• Each member on a separate disk controller
• Redo logs heavily influence performance
To preserve redo information, create archived copies of redo log
files.
• Specify archived log file naming convention.
• Specify one or more locations to archive logs to.
• Switch the database to ARCHIVELOG mode.
Place the database in ARCHIVELOG mode.
• Click the ARCHIVELOG Mode checkbox
• Click Apply. The database can only be set to ARCHIVELOG
mode from the MOUNT state. Click Yes
when asked if you want to restart the database.
Database
Backups
Backup strategy may include:
– The
entire database (whole)
– A
portion of the database (partial)
• Backup type may be:
– All
information from all data files (full)
– Only
information that has changed since some previous backup (incremental)
• Backups mode may be:
– Offline
(consistent, cold)
– Online
(inconsistent, hot)
Recovery Manager (RMAN)
Enterprise Manager uses Recovery Manager (RMAN) to perform backup
and recovery operations.
• Command-line client for advanced functions
• Powerful control and scripting language
• Published API that allows interface with most
popular backup software
• Backs up data, control, archived log, and server
parameter files
• Backs up files to disk or tape
Monitor the Flash Recovery Area
• Configure flashback logging
• Size the recovery area
• Monitor current space consumption
Post a Comment