Transcript Slide 1

DBA Survival Guide
Colin Abbott
Supervisor Database Operations
McGill University
[email protected]
To be courteous to others, please turn off all cell phones and pagers.
Introduction
Our Job 5 years ago
•
•
•
•
•
•
•
Monitor 1 or 2 production databases
Clone test database on demand
Compile new programs/packages/procedures
Verify daily cold backups
Perform upgrades and apply patches
Verify that gurjobs and sleep-wake are running
Code some Unix scripts
7/17/2015 2:52
PM
DBA Survival Guide
3
Our Job Today
• Monitor 25 Production databases (and
growing)
• Monitor 87 test databases (and growing)
• Hot Backups / RMAN
• OEM (Oracle Enterprise Manager)
• Apache and Oracle Application Server
• Upgrades, Patches
• Clones
7/17/2015 2:52
PM
DBA Survival Guide
4
Our Job Today (cont.)
•
•
•
•
•
•
•
•
•
Dataguard
RAC (Real Application Clusters)
On-line operations
XML
Unix Scripting, Windows Scripting
Web programming, Web tailoring
Javascript, HTML
Internet Native Banner (forms services)
Capacity planning / Hardware planning
7/17/2015 2:52
PM
DBA Survival Guide
5
Our Job Today (cont)
•
•
•
•
•
•
•
•
•
Storage area Networks (SAN)
Demand for high availability
Web services
Migration to cost based optimizer
Migration to Oracle 10g
Migration from MMB to LMB
Metadirectories
SSO, LDAP, OID, Active Directory integration
Change management
7/17/2015 2:52
PM
DBA Survival Guide
6
Our Job Today (cont.)
• Tuning SQL (and training Developers)
• Data warehouse, Operational Data Store,
OLAP
• Replication
• Materialized views
• Partitioning
• LDIS
• Portals
7/17/2015 2:52
PM
DBA Survival Guide
7
Our Job Today (cont.)
•
•
•
•
•
•
•
•
•
Workflow
Java
Security threats / Virus (ex. Alert #68)
Message Queues
Onames (Oracle Names)
VPD / FGAC
Protection of privacy legislation
Load balancing solutions
Etc etc etc.
7/17/2015 2:52
PM
DBA Survival Guide
8
Quotes from E-Week
“A reason for additional responsibilities
placed on DBAs' shoulders can be traced
to needed mastery of operating-systemrelated functionality steadily migrating to
the database. Todd Langille, associate
director of Administrative Computing for
Dartmouth College, in Hanover, N.H., said
more DBAs are being assigned "tweaking
and tuning" tasks typically associated with
programmers”
7/17/2015 2:52
PM
DBA Survival Guide
9
Quotes from E-Week
"[DBAs] have more exposure and
responsibility for middle-tier applications
like Web servers and application servers;
there's a whole middle layer of software
that has come along for the ride with our
move toward Web-based applications,"
said Langille. "It's definitely adding up to
another big chunk of work to an alreadyburdened staff."
7/17/2015 2:52
PM
DBA Survival Guide
10
Summary
• The lines are becoming blurred between
the developers and the DBAs and
between the DBAs and the sysadmins
• The ongoing trend as more functionality is
moved into the database is that the DBA
needs to become the expert of everything
7/17/2015 2:52
PM
DBA Survival Guide
11
Goal of Session
• Tips and tricks to help the DBA survive
2004/2005
• Explain some new Oracle features in the
context of how we are using them
• Note - due to time constraints of the
session, topics will be high level. More
information is available in the Oracle
administrator’s guides.
7/17/2015 2:52
PM
DBA Survival Guide
12
Topics
• Oracle 9i: new features to implement in
preparation for Oracle 10g
• Tuning with the new advisors in Oracle 9i
• OEM: Monitor the enterprise with “grid
control”
• Our SAN Migration (database reorg and 9i
new segment management features)
7/17/2015 2:52
PM
DBA Survival Guide
13
Topics (cont.)
• Neat things worth investigating
–
–
–
–
–
–
–
–
–
–
Workspace Manager
History of dataguard
New methods to gather statistics
Flashback queries
Resumable operations
External Tables
LogMiner
RMAN much improved in 9i
Block Compression
dynamic_sampling
7/17/2015 2:52
PM
DBA Survival Guide
14
Oracle 9i: new features to
implement in preparation for
Oracle 10g
Oracle 9i: new features to implement in preparation for
Oracle 10g
•
•
•
•
•
•
Intro
Dynamic SGA
SPFILE
UNDO Tablespace
PGA_AGGREGATE_TARGET
FAST_START_MTTR_TARGET
7/17/2015 2:52
PM
DBA Survival Guide
16
Intro
• Oracle is advertising 10g as a big change in
Oracle’s architecture, this part of the session will
cover some things that can be done now in
preparation for 10g
• Oracle 9i’s goal: reduce the number of
parameters used to configure a database, move
towards a “self-tuning” architecture
• Many new features in 9i are infrastructure for
10g (Only partially implemented or with little or
no value in 9i)
7/17/2015 2:52
PM
DBA Survival Guide
17
Dynamic SGA
• Prior to 9i:
– SGA components were a fixed size
– Needed to reinstance the database to make
changes.
• Since 9i:
– Can dynamically change the values of the
buffer cache, shared pool and large pool
– Maximum size is controlled by
SGA_MAX_SIZE (not dynamically alterable)
7/17/2015 2:52
PM
DBA Survival Guide
18
Dynamic SGA (cont.)
• In ORACLE 10g:
– ORACLE will “self-tune” these parameters as
the load on the database changes
– feature is enabled by setting up Dynamic SGA
7/17/2015 2:52
PM
DBA Survival Guide
19
SPFILE
• Prior to 9i:
– The DBA needed to edit the PFILE (init.ora) anytime they wanted to
persist an alter system command.
• Since 9i:
– Oracle introduced the SPFILE which is a binary representation of the
PFILE.
– The DBA can tell Oracle to update the SPFILE as part of the alter
system command.
Alter system set undo_retention=10800 scope MEMORY
Alter system set undo_retention=10800 scope SPFILE
Alter system set undo_retention=10800 scope BOTH
• In Oracle 10g:
– Oracle will want to write to the SPFILE as it self-tunes in order to
maintain a baseline.
7/17/2015 2:52
PM
DBA Survival Guide
20
UNDO Tablespace
• Prior to 9i:
– DBAs configured rollback segments
• Since 9i:
– Oracle introduced UNDO Tablespace which
optionally replaces Rollback segemnts
– You set the undo_retention parameter to tell
Oracle how long it should try and keep a read
consistent image
– you can use UNDO tablespace to flashback
and run a query at a specific point in time.
7/17/2015 2:52
PM
DBA Survival Guide
21
UNDO Tablespace
• In 10g:
– Many features were added to flashback in 10g
(including a new flashback area). To prepare
for 10g should implement auto undo
management and get familiar with flashback
queries.
7/17/2015 2:52
PM
DBA Survival Guide
22
PGA_AGGREGATE_TARGET
• Prior to 9i:
– Difficult to tune memory usage for SQL
•
•
•
•
SORT_AREA_SIZE
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE
• Since 9i:
– Oracle has introduced automatic SQL execution
memory management
– The DBA sets PGA_AGGREGATE_TARGET to the
total amount of memory you want Oracle to allocate to
the PGA, Oracle will then allocate this to sessions as
they need it.
7/17/2015 2:52
PM
DBA Survival Guide
23
PGA_AGGREGATE_TARGET
• Setting PGA_AGGREGATE_TARGET will
reduce disk operations and make tuning
the PGA easier.
7/17/2015 2:52
PM
DBA Survival Guide
24
FAST_START_MTTR_TARGET
• Prior to 9i:
– Many parameters were used to control checkpoints.
– Difficult to predict time to perform crash recovery
– Difficult to commit to a Service Level Agreement (SLA)
• Since 9i:
– Now only need to set FAST_START_MTTR_TARGET
– This parameter is the number of seconds it should take Oracle to
perform a crash recovery and be ready to open.
– Oracle will set defaults for other parameters based on the value
of FAST_START_MTTR_TARGET
– Setting it low will impact performance due to excessive load
placed on db writer.
7/17/2015 2:52
PM
DBA Survival Guide
25
Tuning with the new advisors in
Oracle 9i
Tuning with the new advisors in Oracle 9i
• Oracle 9i has introduced new advisors to
help the DBA tune their database.
– MTTR Advisor (mean time to recover)
– Undo Tablespace Advisor
– PGA Target Advisor
– DB Cache Advisor
– Shared Pool Advisor
7/17/2015 2:52
PM
DBA Survival Guide
27
Tuning with the new advisors in Oracle 9i
• Advisors are based on v$ Views
• Can see a graphical representation using
OEM
• 10g will use the advisors to “self-tune” the
database
7/17/2015 2:52
PM
DBA Survival Guide
28
STATISTICS_LEVEL
• Prior to 9i:
– you had to set parameters for managing
every type of advice view.
• Since 9i:
– Oracle has consolidated this into 1 parameter
with 3 values
7/17/2015 2:52
PM
DBA Survival Guide
29
STATISTICS_LEVEL
• STATISTICS_LEVEL={BASIC|TYPICAL|ALL}
•
BASIC: No advisories or statistics are collected.
•
•
•
•
•
•
•
TYPICAL: The following advisories or statistics are collected:
Buffer cache advisory
MTTR advisory
Shared Pool sizing advisory
Segment level statistics
PGA target advisory
Timed statistics
•
•
•
ALL: All of TYPICAL, plus the following:
Timed operating system statistics
Row source execution statistics
7/17/2015 2:52
PM
DBA Survival Guide
30
MTTR Advisor
• Will show +/- in disk writes based on the
number of seconds specified for Oracle to
perform crash recovery.
• v$mttr_target_advice
• Goal: Maintain your service level
agreements without making checkpointing
a bottleneck
7/17/2015 2:52
PM
DBA Survival Guide
31
UNDO Advisor
• Will show undo retention vs. size of undo
tablespace
• Based on formula (undo space =
undo_retention * undo blocks per second
+ overhead)
• Undo blocks per second derived from
v$undostat
• Goal: Minimize snapshot too old error,
provide enough undo to flashback
7/17/2015 2:52
PM
DBA Survival Guide
32
PGA TARGET Advisor
• Will show optimal executions, single pass
executions and multipass executions for
different values of
PGA_AGGREGATE_TARGET
• Goal: to reduce disk sorts and have
optimal executions in memory
7/17/2015 2:52
PM
DBA Survival Guide
33
DB CACHE Advisor
• Buffer cache advice shows change in
physical reads (cache miss)
• Goal: Obtain optimal cache hit ratio by
ensuring that blocks are found in cache
and do not have to be read from disk.
7/17/2015 2:52
PM
DBA Survival Guide
34
Shared Pool Advisor
• Advice shows change in parse time.
• Goal: Avoid reloading and parsing
frequently used statements
7/17/2015 2:52
PM
DBA Survival Guide
35
OEM: Monitor the enterprise with
“grid control”
OEM: Monitor the enterprise with “grid
control”
• Now called Oracle Enterprise Manager
10g grid control (OEM10gc)
• No Longer Java Client, OC4J Container
running in the Oracle Application Server
• FAQ Metalink note # 273579.1
7/17/2015 2:52
PM
DBA Survival Guide
37
OEM: Monitor the enterprise with “grid
control”
• Supports Host monitoring for:
–
–
–
–
–
–
–
–
–
Sun Solaris 32 bit (8, 9)
Sun Solaris 64 bit (8,9)
HP-UX 64 bit (11i)
HP Tru64 (5.1a)
IBM AIX 64 bit (5.2)
RH Linux ES and AS (2.1)
RH Linux (3)
United Linux 1.0 (SP3)
Windows 2000, XP
7/17/2015 2:52
PM
DBA Survival Guide
38
OEM: Monitor the enterprise with “grid
control”
• Supported Database targets:
– Oracle Database (8.1.7.4, 9.x, 10g)
– Oracle Listener (8.1.7.4, 9.x, 10g)
– Application Server (9.0.2, 9.0.3, 9.0.4)
– Real Application Clusters (9.x, 10g)
– Web Applications (custom targets)
– Management Repository and OMS (10g)
– Management Agent (10g)
7/17/2015 2:52
PM
DBA Survival Guide
39
OEM: Monitor the enterprise with “grid
control”
• What can you do with OEM:
– Home page overview of the enterprise
– Monitor a host
– Monitor a database
– Maintenance and Administration
– View performance snapshots and look at
historical performance, analyse patterns and
trends
7/17/2015 2:52
PM
DBA Survival Guide
40
OEM: Monitor the enterprise with “grid
control”
• Use OEM to be proactive
– Warning Alerts
– Critical Alerts
– Ability to set your own thresholds
7/17/2015 2:52
PM
DBA Survival Guide
41
OEM: Monitor the enterprise with “grid
control”
7/17/2015 2:52
PM
DBA Survival Guide
42
Our SAN Migration (database
reorganization and new segment
features in 9i)
Our SAN Migration
• Challenges:
– ERP systems are growing beyond the
capacity of local storage.
– Make the database more logically organized
– Reduce errors due to space issues (reduce
DBA work to maintain segments)
7/17/2015 2:52
PM
DBA Survival Guide
44
Migration to a SAN
• Took this opportunity to reorganize the database
and implement new segment features in 9i
• After 5 years on Banner was time for a clean up
–
–
–
–
–
–
–
Moved to locally managed tablespaces
Implemented segment monitoring
Moved to auto segment management
Set maxextents unlimited on all non-system segments
Organized segments based on size and usage
Created separate tablespace for LOB data
Created WORK_DATA tablespace for “temporary
segments”
7/17/2015 2:52
PM
DBA Survival Guide
45
Locally Managed Tablespaces
• Extent sizes are managed in a bitmap on
the tablespace they are no longer
managed in the dictionary.
• Uniform extent sizes eliminate
fragmentation
• With Autoallocate Oracle will determine
the extent size (always multiples)
7/17/2015 2:52
PM
DBA Survival Guide
46
Locally Managed Tablespaces
•
•
•
•
•
•
•
•
CREATE TABLESPACE WORK_DATA DATAFILE
'/bdata17/oradata/UP6F/work_data_UP6F_01.dbf' SIZE 2000M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
•
•
•
•
•
•
•
•
CREATE TABLESPACE DATA1_64M DATAFILE
'/bdata9/oradata/UP6F/data1_64m_UP6F_01.dbf' SIZE 2000M AUTOEXTEND OFF,
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;
7/17/2015 2:52
PM
DBA Survival Guide
47
Auto Segment Space Management
• Free Space in a block is managed in a
bitmap
• Replaces freelists, freelist groups and
pctused
• Eliminates freelist contention on heavily
inserted tables.
7/17/2015 2:52
PM
DBA Survival Guide
48
Segment Monitoring
• 2 months in advance turned on segment
monitoring to track selects, inserts, updates,
deletes for all segments
• alter table fimsmgr.ftvfund MONITORING
• select * from dba_tab_modifications where
table_name = 'FTVFUND‘
• Shows inserts = 1440713, updates =1096550,
Deletes = 657158
• exec
dbms_stats.ALTER_DATABASE_TAB_MONITO
RING(TRUE);
7/17/2015 2:52
PM
DBA Survival Guide
49
Logically Organized Structure
• Created tablespaces for Banner data. Decided
to use the same extent sizes Oracle uses for
AUTOALLOCATE
–
–
–
–
DATA1_64K, DATA1_1M, DATA1_8M, DATA1_64M
DATA2_64K, DATA2_1M, DATA2_8M, DATA2_64M
INDX1_64K, INDX1_1M, INDX1_8M, INDX1_64M
INDX2_64K, INDX2_1M, INDX2_8M, INDX2_64M
7/17/2015 2:52
PM
DBA Survival Guide
50
Logically Organized Structure
• Very large Banner tables get their own
tablespace (once we go to CBO they will
be partitioned for better manageability)
• FGBTRND_DATA,FGBTRND_INDX
• FGBTRNH_DATA,FGBTRNH_INDX
• NHRDIST_DATA,NHRDIST_INDX
• SARPERR_DATA,SARPERR_INDX
• SFRFAUD_DATA,SFRFAUD_INDX
7/17/2015 2:52
PM
DBA Survival Guide
51
Logically Organized Structure
• Special Tablespaces:
– WORK_DATA
– LOB_64k
– DEVELOPMENT
– PERF_DATA
– MV_LOGS
– MERCURY
7/17/2015 2:52
PM
DBA Survival Guide
52
Migration
•
•
•
•
•
•
•
•
Create new tablespaces on the SAN
Use Alter table move tablespace command
Rebuild indexes on new tablespaces
Use export/import and scripts for LOBs
Verify if any objects left in old tablespaces
Drop old tablespaces.
Backup
Monitor performance
7/17/2015 2:52
PM
DBA Survival Guide
53
Maintenance
• Having a logically organized structure
allows us to automate monthly segment
maintenance.
• If a segment would take at least one full
extent in a larger tablespace it will be
promoted
– Example: if a segment in DATA1_1M has 65
1M extents it is a candidate to be moved to
DATA1_64M
7/17/2015 2:52
PM
DBA Survival Guide
54
Other neat things!
Workspace Manager
• Not for a production database
• Allows you to perform simulations without
requiring another test instance
• Versions a table so that it can be used for
many purposes
7/17/2015 2:52
PM
DBA Survival Guide
56
History of Dataguard
•
•
•
•
•
What is a standby database
Manual Log Shipping in 7.X
Physical Standby
Read-Only Physical Standby
Logical Standby (Why it doesn’t work with
Banner) How 10g will help
7/17/2015 2:52
PM
DBA Survival Guide
57
New methods to gather statistics
•
•
•
•
The demise of RBO
Gather Auto with Segment monitoring
Gathering system statistics
Much smarter in 10g
7/17/2015 2:52
PM
DBA Survival Guide
58
Flashback Queries
• Allows you to flashback and see data as of
a point-in-time
• Requires Automatic Undo Management
• Retention based on UNDO_RETENTION
parameter
• Convoluted to use in 9i Release1,
improved in 9i Release 2 with the select
…as of clause
• Flashback database in 10g
7/17/2015 2:52
PM
DBA Survival Guide
59
Resumable operations
• Operations that fail because of
maxextents, lack of space in a tablespace,
or quota can now be resumed without
rolling back and restarting
7/17/2015 2:52
PM
DBA Survival Guide
60
External Tables
•
•
•
•
Is really a flat file outside of Oracle
Can select from it just like any Oracle table
Uses SQLLOADER to read the file
Caution – Will cause gostage to fail during
Banner upgrades.
7/17/2015 2:52
PM
DBA Survival Guide
61
LogMiner
• Tool to mine the data in archive logs
• Many enhancements in 9i
– Can use on-line dictionary
– Can dump dictionary to redo logs
– GUI interface in OEM
– Tracks DDL
– Skips past corrupt logs
– Can now dedicate a tablespace to logminr
(rather then being in system)
7/17/2015 2:52
PM
DBA Survival Guide
62
RMAN
• Oracle is pushing people towards RMAN
• Much Improved in 9i
– Persistent configuration
•
•
•
•
Block Recovery
Restartable backups
Integration with OEM
Clone a database now supported
7/17/2015 2:52
PM
DBA Survival Guide
63
Block Compression
• Compress attribute of a segment
• Only compresses rows during direct load
operations
• Best compression ratio when loaded in
order of a column with low cardinality
(column with most like data)
7/17/2015 2:52
PM
DBA Survival Guide
64
Block Compression
• Example
Create table TBRACCD_2004 COMPRESS
as
select * from taismgr.tbraccd order by
tbraccd_term_code
7/17/2015 2:52
PM
DBA Survival Guide
65
Block Compression
• Determine low cardinality from
DBA_TAB_COL_STATISTICS or by
executing a query like select count(
distinct(column) from table
• Not currently supported with Logical
standby database
• Better suited for DSS then OLTP
7/17/2015 2:52
PM
DBA Survival Guide
66
Dynamic sampling
• Database parameter or Hint
• Will sample the data in the tables prior to
parsing a statement
• Will use CBO
7/17/2015 2:52
PM
DBA Survival Guide
67
Surviving
• Make management aware of our job role
and its importance to the organization
• Align DBA goals with business objectives
• Make management aware of the
importance of training (50% of everything
DBAs know will be obsolete in one year)
• Keep up to date on new features
• Try and learn something new every day
• Have fun
7/17/2015 2:52
PM
DBA Survival Guide
68
Questions
Please fill out the evaluation form for this session and hand it to the
moderator on your way out.
7/17/2015 2:52
PM
DBA Survival Guide
69