Basic Database
Administration
Lecture: 5
Guide to Oracle 10g 1
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.
2
What is Database Administration?
• Database Administrator (DBA) is a person that
• Overall health / Performance
• Manages Security
• Setup Test and Development Environments
3
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.
Guide to Oracle 10g 4
OEM Architecture
5
Managing Oracle 11g Data Storage
• Like most DBMS’s the logical structures
• Tables
• Constraints
• Views / Procedures
• Can be stored in physical data structures
• Files on disk
• Dedicated drive partitions
• RAM
Guide to Oracle 10g 6
Oracle 11g Data Structures
• Tablespace
• One or more Data Files
• Segment
• Partitioned Data
• Extent
• Sequence of data blocks
• Data block
• Database storage data block
• Operating system blocks
7
Tablespaces
• One or more Data files
• Stores all database structures + data
• Tables, data, views, sp’s etc…
Guide to Oracle 10g 8
Datafiles
• .dbf extensions
• Store tablespace contents
• Stored in Oracle_Base\oradata\SID
• Use OEM to view and modify
• Grow via Extents
Guide to Oracle 10g 9
Segments – They Partition the data
10
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.
Guide to Oracle 10g 11
Data Blocks – Smallest Unit Read/Written
12
Managing Oracle 11g Data Structures
• Create tablespace
• Manage datafile extents
• Autoextensible tablespace
• Configure tablespace and datafile properties
•Demo!
Guide to Oracle 10g 13
Oracle 11g Database File Architecture
14
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
Guide to Oracle 10g 15
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
16
Redo Log Files
• Records information to undo action query changes
• .log extension
• Stored in Oracle_Base\ORADATA\SID
• Pre-image
• Rollback segment
Guide to Oracle 10g 17
User Accounts
18
Creating and Managing User Accounts
• Create new user account
• General information about user account
• System privileges user has in database
• User’s tablespace quota on database server
•Demo!
Guide to Oracle 10g 19
Specifying General User Information
• Use OEM
• General page:
• Name
• Profile
• Authentication
• Default tablespace
• Temporary tablespace
• Status
Guide to Oracle 10g 20
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
Guide to Oracle 10g 21
Tablespace Quotas
• Specifies amount of disk space that user’s database objects can
occupy in default tablespace
• Must be assigned
• Quota Size value:
• None, default
• Unlimited
• Value
Guide to Oracle 10g 22
Editing Existing User Accounts
• Use OEM
• Select user account to be modified on Users page
• General page opens
• Select other links to modify properties
Guide to Oracle 10g 23
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.
24
Startup / Shutdown
25
Starting /Shutting Down the Db
• Shut down database periodically
• Perform maintenance
• Restart database
Guide to Oracle 10g 26
Creating an Administrative Connection
• Shutting down database makes database unavailable for user
connections
• DBA must log onto database using administrative connection
• SYS user account
Guide to Oracle 10g 27
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 server’s main memory
28
Instance Options
Startup Shutdown
• Start in one of two modes: • Specify one of four ways to
• Unrestricted handle existing user
• Restricted connections:
• Normal
• Transactional
• Immediate
• Abort
29
Oracle 11g Database Instance States
Relerenoes:
https://docs.oracle.com/oloud latest/db112/CNCPT/startup.htm#CNCPT1290
https://docs.oracle.com/cdl 11 2 01lmde .h1m 30
OCA Oracle Database 11g Admlnlstarrion I Exam Gulde -John Watson
