INTRODUCTION TO
Basic Database Administration
Presented By
OrienIT
www.orienit.com
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.
www.orienit.com
What is Database Administration?
A Function information technology (IT)
department
Database Administrator (DBA)
Overall health / Performance
Manages Security
Setup Test and Dev. Environments
www.orienit.com
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.
www.orienit.com
DBA Tools
www.orienit.com
DBA Tools Product Comparison
Oracle 11g
Oracle Enterprise Manager
Web-Based
SQL Server
SQL Server Management
Studio
Client-Based
www.orienit.com
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
www.orienit.com
OEM Architecture
www.orienit.com
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
www.orienit.com
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
www.orienit.com
Table spaces
One or more Data files
Stores all database structures + data
Tables, data, views, sps etc
www.orienit.com
Datafiles
.dbf extensions
Store tablespace contents
Stored in Oracle_Base\oradata\SID
Use OEM to view and modify
Grow via Extents
www.orienit.com
Segments They Partition the data
www.orienit.com
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.
www.orienit.com
Data Blocks Smallest Unit
Read/Written
www.orienit.com
Managing Oracle 11g Data Structures
Create tablespace
Manage datafile extents
Autoextensible tablespace
Configure tablespace and datafile properties
www.orienit.com
Oracle 11g Database File Architecture
www.orienit.com
Parameter File
Text file
Specifies configuration information about Oracle
10g database instance
init.ora
Stored in Oracle_Base\admin\SID\pfile folder
DBAs can edit parameter file
Modify database configuration
www.orienit.com
Control Files
Store information about database structure and
state
Stored in Oracle_Base\oradata\SID
Three separate control files by default:
CONTROL01.CTL
CONTROL02.CTL
CONTROL03.CTL
All contain same data
At least one must be present
www.orienit.com
Redo Log Files
Records information to undo action query changes
.log extension
Stored in Oracle_Base\ORADATA\SID
Pre-image
Rollback segment
www.orienit.com
User Accounts
www.orienit.com
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
www.orienit.com
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
www.orienit.com
Specifying General User Information
Use OEM
General page:
Name
Profile
Authentication
Default tablespace
Temporary tablespace
Status
www.orienit.com
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
www.orienit.com
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
www.orienit.com
Editing Existing User Accounts
Use OEM
Select user account to be modified on Users page
General page opens
Select other links to modify properties
www.orienit.com
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.
www.orienit.com
Startup / Shutdown
www.orienit.com
Starting /Shutting Down the DB
Shut down database periodically
Perform maintenance
Restart database
www.orienit.com
Creating an Administrative
Connection
Shutting down database makes database
unavailable for user connections
DBA must log onto database using administrative
connection
SYS user account
www.orienit.com
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
www.orienit.com
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
www.orienit.com
Oracle 11g Database Instance States
www.orienit.com
