INTRODUCTION TO
Basic Database Administration
Presented By
OrienIT
[Link]
Objectives
Define database administration.
Understand database administration tasks.
Perform database administration tasks using
Oracle 11g Enterprise Manager.
Understand Oracle 11g and SQL Server data
storage structures.
[Link]
What is Database Administration?
A Function information technology (IT)
department
Database Administrator (DBA)
Overall health / Performance
Manages Security
Setup Test and Dev. Environments
[Link]
Duties of the DBA
Manage Database Objects Tables / Views /
Procedures
Database performance
Security Logons /Users / Roles
Clone data from Production to Development or
Test
Manage backups and carry out DR plans.
[Link]
DBA Tools
[Link]
DBA Tools Product Comparison
Oracle 11g
Oracle Enterprise Manager
Web-Based
SQL Server
SQL Server Management
Studio
Client-Based
[Link]
Oracle Enterprise Manager
User account must have DBA role
Oracle Enterprise Manager (OEM)
Three-tier architecture
Console
Oracle Management Server (OMS)
Interacts with repository
Makes it easier for DBAs to administer multiple
databases in organizations network
[Link]
OEM Architecture
[Link]
Managing Oracle 11g Data Storage
Like most DBMSs the logical structures
Tables
Constraints
Views / Procedures
Can be stored in physical data structures
Files on disk
Dedicated drive partitions
RAM
[Link]
Oracle 11g Data Structures
Tablespace
One or more Data Files
Segment
Partitioned Data
Extent
Growth rule for segment
Data block
Database storage data block
Operating system blocks
[Link]
Table spaces
One or more Data files
Stores all database structures + data
Tables, data, views, sps etc
[Link]
Datafiles
.dbf extensions
Store tablespace contents
Stored in Oracle_Base\oradata\SID
Use OEM to view and modify
Grow via Extents
[Link]
Segments They Partition the data
[Link]
Extents Smallest unit added to data file
Sequence of Data Blocks
When an insert grows beyond the data file size
allocation, a new extent is added.
More efficient to add groups of data blocks vs.
individual blocks.
[Link]
Data Blocks Smallest Unit
Read/Written
[Link]
Managing Oracle 11g Data Structures
Create tablespace
Manage datafile extents
Autoextensible tablespace
Configure tablespace and datafile properties
[Link]
Oracle 11g Database File Architecture
[Link]
Parameter File
Text file
Specifies configuration information about Oracle
10g database instance
[Link]
Stored in Oracle_Base\admin\SID\pfile folder
DBAs can edit parameter file
Modify database configuration
[Link]
Control Files
Store information about database structure and
state
Stored in Oracle_Base\oradata\SID
Three separate control files by default:
[Link]
[Link]
[Link]
All contain same data
At least one must be present
[Link]
Redo Log Files
Records information to undo action query changes
.log extension
Stored in Oracle_Base\ORADATA\SID
Pre-image
Rollback segment
[Link]
User Accounts
[Link]
Creating and Managing User Accounts
Create new user account
General information about user account
System privileges user has in database
Users tablespace quota on database server
[Link]
Creating and Managing User Accounts
Create new user account
General information about user account
System privileges user has in database
Users tablespace quota on database server
[Link]
Specifying General User Information
Use OEM
General page:
Name
Profile
Authentication
Default tablespace
Temporary tablespace
Status
[Link]
Specifying System Privileges
System privilege
Object privilege
Enable new user to interact with Oracle 10g
database
DBA grants system privileges
Use System Privileges page in Create User page
Admin Option
[Link]
Tablespace Quotas
Specifies amount of disk space that users
database objects can occupy in default tablespace
Must be assigned
Quota Size value:
None, default
Unlimited
Value
[Link]
Editing Existing User Accounts
Use OEM
Select user account to be modified on Users page
General page opens
Select other links to modify properties
[Link]
Roles
Database object
Represents collection of system privileges
Assign to multiple users
Create role
Can inherit privileges from other roles
Grant Role to User Account
Easier than manually assigning everything
manually.
[Link]
Startup / Shutdown
[Link]
Starting /Shutting Down the DB
Shut down database periodically
Perform maintenance
Restart database
[Link]
Creating an Administrative
Connection
Shutting down database makes database
unavailable for user connections
DBA must log onto database using administrative
connection
SYS user account
[Link]
Using OEM to Shut Down and Start a
Database Instance
DBA shuts down database instance using Normal,
Transactional, or Immediate shutdown option
Shutdown process performs five following tasks:
Writes contents of data buffer cache to datafiles
Writes contents of redo log buffer to redo log files
Closes all files
Stops all background processes
Deallocates SGA in servers main memory
[Link]
Instance Options
Startup
Start in one of two modes:
Unrestricted
Restricted
Shutdown
Specify one of four ways to
handle existing user
connections:
Normal
Transactional
Immediate
Abort
[Link]
Oracle 11g Database Instance States
[Link]