Home Page Icon
Home Page
Table of Contents for
Trusted Oracle
Close
Trusted Oracle
by Brian Laskey, David C. Kreines
Oracle Database Administration: The Essential Refe
Dedication
Preface
Why We Wrote This Book
Versions of Oracle
How This Book Is Organized
Part I
Part II
Conventions Used in This Book
Comments and Questions
Acknowledgments
I. DBA Tasks
1. Introduction
Oracle Database Administration
A DBA Job Description
The DBA and This Book
The tasks DBAs do
The information DBAs need
Oracle Architecture
Database Versus Instance
Background Processes
System Global Area (SGA)
Files
Logical Database Structures
Software Options
Base Product
Options
Data Cartridges
Development Tools
Applications
Licensing by the User
Version Numbers
Configuration Planning
Availability
Replication
Hot standby database
Oracle Parallel Server
Backup and Recovery
Export/import
Cold backup
Recovery Manager (RMAN)
Performance
Memory
Disk layout
2. Installation
Media Selection
The Oracle Installer
Installable Components
Dependencies
Pre-Installation Checklist
Critical Decisions
The Starter Database
Oracle Directory Structure
Post-Installation Tasks
Creating a Database
Choosing a blocksize
Choosing the name
Creating the parameter file
Building Database Creation Scripts
A sample script
3. Maximizing Oracle Performance
Configuration and Tuning—What’s the Difference?
What Can Be Configured?
What Can Be Tuned?
Achieving Maximum Performance
Configuring the Operating System
Configuring Oracle
Configuration Guidelines
Check the documentation
Check resource requirements
Check system privileges
Determine control file locations
SQL*Net Configuration
Multi-Threaded Server Configuration
Parallel Query Configuration
Parallel Server Configuration
Sizing and Configuring Database Objects
Tables
Indexes
Rollback Segments
Sort Areas
Temporary Tablespaces
Redo Logs
Archive Log Destination
Tuning Oracle
A Structured Tuning Approach
Oracle Instance Tuning
Memory utilization
Disk I/O
A note about RAID
Operating system striping
Oracle striping
SQL Tuning
Query processing
Other Useful Tuning Features
Partitioned tables
Index-only tables
Bitmap indexes
Temporary tablespaces
Unrecoverable operations
4. Preventing Data Loss
Types of Backups
Physical Backups
Benefits
Limitations
Logical Backups
Benefits
Limitations
Archivelog Mode
Benefits
Limitations
Incremental Backups
Benefits
Limitations
Database Backup
Cold Backup
Benefits
Limitations
Hot Backup
Benefits
EXPort/IMPort
Benefits
Limitations
Using incremental exports
Direct path versus conventional path
Using the EXP tables
Other uses for Export
Database Recovery
Recovery Scenarios
System failure
Disk failure
Database corruption
Data corruption
Tablespace Point-in-Time Recovery
Overview of the process
Benefits
Limitations
Data dictionary views
Automated Utilities
Oracle Recovery Manager
Oracle Enterprise Backup Utility
Third-Party Utilities
Practice Scenarios
Export/Import
Hot or Cold Backups
Other Techniques
5. Oracle Networking
Oracle Network Architecture
SQL*Net and Net8
SQL*Net functionality
How SQL*Net works
Oracle services
Network security
Single sign-on
DCE integration
DCE naming adapter
Oracle Names
MultiProtocol Interchange
Connecting to Non-Oracle Databases
Open DataBase Connectivity
Gateways
Network Manager Data Storage
Using Network Manager
Files Created by Network Manager
Oracle Net8 Assistant
Manual Network Configuration
Sample SQL*Net Files
listener.ora
tnsnames.ora
sqlnet.ora
SQL*Net Troubleshooting
Some Common ORA Errors, Causes, and Corrective Actions
SQL*Net Troubleshooting Procedures
Determine basic server operation
Determine basic client connectivity
6. Security and Monitoring
Security
Security Policy
User access to the database
Read sensitivity of the data
Write sensitivity of the data
Audit policy
Creating Users
Profiles
Resource control
Password control
Default profile
Implementing profiles
System Privileges
Object Privileges
The WITH GRANT OPTION clause
Roles
Default roles
Maximum number of enabled roles
Password-enabled roles
The WITH ADMIN OPTION clause
Common Security Holes
Not changing privileged user passwords
Using the same password in all databases
The SCOTT/TIGER account
Shared UTL_FILE access to directories
Auditing
Monitoring
What to Monitor
Memory utilization
Storage utilization
How to Monitor
Getting a baseline
SQL queries to monitor SGA utilization
SQL queries to monitor storage utilization
7. Auditing
About Auditing
Types of Auditing Supported
Scope of Auditing
Limitations of Auditing
Implementing Auditing
Disabling Auditing
Forms of the AUDIT Statement
Schema Object Auditing
Examples of schema object auditing
Statement Auditing
Examples of statement auditing
System Privilege Auditing
Audit Trail Views and Lookup Tables
Data Dictionary Views
Views containing audit trail information
Views containing auditing specifications
Lookup Tables
Establishing an Audit Policy
Maintaining the Audit Trail
Example Script to Copy and Summarize Session Information
Example Script to Clean Out the Audit Trail
Row-Level Auditing
8. Query Optimization
Types of Queries
Simple Queries
Joins
Subqueries
Correlated Subqueries
Cartesian Product
Types of Join Access Paths
Nested Loops
Merge Joins
Hash Joins
Determining the Access Plan
The PLAN_TABLE Table
The EXPLAIN PLAN Statement
SQL*Plus AUTOTRACE
SQL Trace and TKPROF
Initialization parameters
Enabling SQL Trace for a specific session
Postprocessing with TKPROF
Oracle Enterprise Manager Top Sessions
Cost-Based Optimizer
Initialization Parameters
Data Dictionary Requirements
ANALYZE TABLE
ANALYZE INDEX
ANALYZE TABLE . . . STATISTICS FOR COLUMNS
ESTIMATE versus COMPUTE
How often to ANALYZE
Script to automate the ANALYZE process
Specifying Hints
How to specify a hint
Scope of a hint
Goal hints
Access method hints
Join order hints
Join operation hints
Parallel operation hints
Additional hints
Rule-Based Optimizer
Rules of Precedence
Outside In
Most Recently Created Index
Examples
9. Oracle Tools
About the Tools
SQL*Plus
SQL from SQL
Producing Reports
Oracle Server Manager
Graphical Version
Line-Mode Version
The Future of Server Manager
SQLDBA
Oracle Network Manager
SQL*Loader
Conventional and Direct Path Loading
SQL*Loader Files
Oracle Enterprise Manager
Third-Party Tools
10. The Oracle Instance
Elements of the Instance
Processes
Memory Structures
About Processes
Standard Background Processes
DBWR—the Database Writer
LGWR—the Log Writer
SMON—the System Monitor
PMON—the Process Monitor
Other Background Processes
CKPT—the Checkpoint process
ARCH—the Archiver process
Snnn—the Shared Server process
Dnnn—the Dispatcher process
RECO—the Recoverer process
LCKn—the Lock process
Pnnn—the Parallel Query process
QMNn—the Queue Monitor process
SNPn—the Job Queue process
Server Processes
User Processes
Single task (non-SQL*Net)
Two task (with dedicated servers)
Multi-Threaded Server
About Memory Structures
Software Code Areas
System Global Area (SGA)
Database buffer cache
Redo log buffer
Shared pool
Library cache.
Dictionary cache.
Control structures.
Program Global Area (PGA)
Sort Areas
11. The Oracle Database
Types of Database Files
Parameter Storage Files
Initialization file
Configuration file
Database Coordination Files
Control files
Redo log files
Data Storage Files
Oracle Tablespaces
Oracle Blocks
Fixed block header
Variable block header
Data storage area
Storage Allocation Parameters
How Oracle Allocates Storage
INITIAL
NEXT
MINEXTENTS
PCTINCREASE
A Space Allocation Example
Object Storage Sizing
Free lists
The highwater mark
Disk Allocation
Disk Layout
About RAID
12. Initialization Parameters
Dynamically Modifiable Parameters
Platform-Specific Parameters
Summary of Initialization Parameters
Parameters Used Only in Oracle7
Parameters New in Oracle8
13. SQL Statements for the DBA
SQL Commands by Task
SQL Command Syntax
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
column_constraint_clause
Note
Examples
auto_extend_clause
Notes
Examples
for_clause
Notes
Example
filespec
autoextend_clause
Notes
Example
global_index_clause
local_index_clause
Notes
Example
table_constraint_clause
table_ref_clause
column_ref_clause
column_constraint_clause
index_organization_clause
segment_attrib_clause
partition_clause
Notes
Examples
autoextend_clause
Notes
Example
14. The Oracle Data Dictionary
Static Data Dictionary Views
Families of Views
Commonly Used Data Dictionary Views
Advanced Queuing
Audit trail
Constraints
Dictionary
Indexes
Jobs
Large objects (LOBs)
Locks
Net8
Objects Option
Partitioning
PL/SQL
Security
Sequences
Server management
Storage
Synonyms
Tables, clusters, and views
Others
Other Static Data Dictionary Views
Advanced replication
Export
Gateways
Oracle Parallel Server
Remote procedure calls
Snapshots
SQL*Loader
Tablespace point-in-time recovery
Trusted Oracle
Dynamic Performance Data Dictionary Views
Availability of the Dynamic Performance Data Dictionary Views
How the Dynamic Performance Data Dictionary Views Are Built
The Global Dynamic Performance Data Dictionary Views (GV$ Views)
New Views with Oracle8
Dynamic Views
Advanced Queuing
Configuration
Data dictionary cache
Database
Instance
Locks and latches
Multi-Threaded Server
Oracle Parallel Server
Parallel Query
Recovery
Security
Session
SQL
SQL*Loader direct path
System environment
15. System Privileges and Initial Roles
Actions, Privileges, and Roles
System Privileges
Privileges That Affect the Entire Database
Privileges That Allow You to Create Objects in Your Own Schema
Privileges That Allow You to Manipulate Objects in Any Schema
Summary of System Privileges
New System Privileges in Oracle8
Initial Roles
Automatic Roles
Optional Roles
Cross-Reference: Roles and System Privileges
Initial Users
Summary of Initial Users
Cross-Reference: Roles Assigned to Initial Users
Cross-Reference: System Privileges Assigned to Initial Users
16. Tools and Utilities
SQL*Plus
Command-Line Syntax
SQL*Plus Editing Commands
Formatting SQL*Plus Output
Other SQL*Plus Commands
SQL Variables and Related Commands
SQL*Plus System Variables
Export
Command-Line Syntax
Interactive Mode Syntax
Export Parameters
Import
Command-Line Syntax
Parameter File Syntax
Interactive Mode Syntax
Import Parameters
SQL*Loader
Command-Line Syntax
Control File Syntax
delimiter_spec
datatype_spec
A. Appendix:Resources for the DBA
Books
Other Publications
Organizations
Web Sites
Discussion Groups
List Servers
Index
Colophon
Copyright
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Tablespace point-in-time recovery
Next
Next Chapter
Dynamic Performance Data Dictionary Views
Trusted Oracle
This view provides information required by the
Trusted Oracle product:
ALL_LABELS
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset