Oracle DBA Best Practices [PDF]
UTOUG Training Days 2004 Advanced DBA Best Practices Michael S. Abbey The Pythian Group [email protected] Michael S.
47 0 5MB
![Oracle DBA Best Practices [PDF]](https://vdoc.tips/img/200x200/oracle-dba-best-practices.jpg)
- Author / Uploaded
- Alain Thailande
Datei wird geladen, bitte warten...
Zitiervorschau
UTOUG Training Days 2004 Advanced DBA Best Practices
Michael S. Abbey The Pythian Group [email protected]
Michael S. Abbey – Advanced DBA Best Practices
Agenda • • • • •
Preamble / raison d'être Monitoring INIT.ora Do not wait for waits Integrity of your backups
2
Michael S. Abbey – Advanced DBA Best Practices
Agenda • • • • •
Infrastructure setup Schema environments Cost-based optimizer Distributed computing Application tuning
3
Michael S. Abbey – Advanced DBA Best Practices
Agenda • Working with Oracle Support Services • Hodge podge
4
Michael S. Abbey – Advanced DBA Best Practices
5
Preamble / raison d'être Cryptic
Too many areas
• It's so hard to tune – Cryptic? – Too many areas? – Too complex?
Memory
Apps
• It’s so easy to tune – Separate components – Version compatibility – Concept carry-over
I/O
Michael S. Abbey – Advanced DBA Best Practices
Preamble / raison d'être Facts about the Oracle Server
• Terminology is • Instance parameters portable across version – v$parameter – v$instance • Same background processes • SQL statement – v$sqlarea • Method rather than – v$sqltext guesswork – Ripple affect • Memory structures – Fix this / break that
– v$librarycache
6
Michael S. Abbey – Advanced DBA Best Practices
7
Preamble / raison d'être Too complex??
• Tune Apps • Tune Memory
• Tune I/O
Turn this
Michael S. Abbey – Advanced DBA Best Practices
Preamble / raison d'être Facts about the Oracle Server
Into this!!!!!
8
Michael S. Abbey – Advanced DBA Best Practices
Monitoring
Best practices NOW will payoff down the road
9
Michael S. Abbey – Advanced DBA Best Practices
Caveat You would not believe what the guy at Training Days told me. He said that his approach to monitoring was to ignore situations that he did not deem to be necessary! One person's approach to monitoring may not be the other person's style. Suggestions? Be my guest.
10
Michael S. Abbey – Advanced DBA Best Practices
Ensure your space monitoring traps ALL space deficiency situations
11
Michael S. Abbey – Advanced DBA Best Practices
12
Monitoring The dba_free_space poltergeist select distinct a.tablespace_name from dba_tablespaces a, dba_free_space b where a.tablespace_name = b.tablespace_name;
select distinct a.tablespace_name from dba_tablespaces a, dba_free_space b where a.tablespace_name = b.tablespace_name (+);
TABLESPACE_NAME -----------------------------AD_DATA AD_INDEX RBS SYSTEM TEMP
TABLESPACE_NAME -----------------------------AD_DATA AD_INDEX BLINKY RBS SYSTEM TEMP
Michael S. Abbey – Advanced DBA Best Practices
Monitoring I'm too full set pages 100 col ts_name form a20 head 'Tablespace'
col pieces form 9990 head 'Pcs'
Environment
col ts_size form 999,990 head 'SizeMb' col largestpc form 999,990 head 'LrgMB' col totalfree form 999,990 head 'FreeMb' col pct_free form 990 head '%Free' col whatsused form 999,990 head 'Used' col pct_used form 990 head '%Used' col problem head 'Prob??' spool umcdbp1 . . . spool off
Da code goes here
13
Michael S. Abbey – Advanced DBA Best Practices
14
Monitoring I'm too full select q2.other_tname ts_name, pieces, ts_size ts_size, nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree, nvl(round((total_free/ts_size)*100,2),0) pct_free, ts_size-total_free whatsused, nvl(100-round((total_free/ts_size)*100,2),100) pct_used, decode(nvl(100-round((total_free/ts_size)*100,0),100), 85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++', 92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++', 98,'+++++',99,'+++++',100,'+++++','') problem from (select dfs.tablespace_name,count(*) pieces, round(max(dfs.bytes)/1024/1024,2) largest_chunk, round(sum(dfs.bytes)/1024/1024,2) total_free from dba_free_space dfs group by tablespace_name) q1, (select tablespace_name other_tname,
round(sum(ddf2.bytes)/1024/1024,2) ts_size from dba_data_files ddf2 group by tablespace_name) q2 where q2.other_tname = q1.tablespace_name(+) order by nvl(100-round((total_free/ts_size)*100,0),100) desc;
Code
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Thresholds
• Percentage based on – Growth patterns – Past experiences – Archival habits
• Ignore or not ignore – Rollback segments – Temp – Non-app related
• LM tablespaces with extent management local …uniform size … • The dba_free_space poltergeist Infrastructure issues
More of a than anything else
15
Michael S. Abbey – Advanced DBA Best Practices
Ensure you trap potential object extension problems before your applications
16
Michael S. Abbey – Advanced DBA Best Practices
17
Monitoring Unable to extend
• Less free space available than potential object extension • Objects within 5 extents of their maximum col col col col col col
owner form a5 head Owner segment_type form a5 head Type segment_name form a24 head Name next_extent form 999,999,990 head NextEXT max_extents form 9,999 head MaxEXT extents form 9,999 head CurrEXT
break on owner on segment_type
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Unable to extend – available extents prompt prompt Objects that cannot extend ... prompt select from where and
owner,segment_type,segment_name,next_extent sys.dba_segments ds segment_type in ('TABLE','INDEX') next_extent > (select max(bytes) from sys.dba_free_space dfs where dfs.tablespace_name = ds.tablespace_name) order by 1,2,3;
18
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Unable to extend – within 5 prompt prompt Objects within 5 of max extents ... prompt select from where and
owner,segment_type,segment_name,max_extents,extents sys.dba_segments ds segment_type in ('TABLE','INDEX') max_extents - extents sysdate-inter/1440 and broken = 'Y'; Smarts to ensure you begin do not un-break what is for jobrec in jobstofix supposed to be broken loop dbms_job.run(jobrec.job); end loop; end; /
31
Michael S. Abbey – Advanced DBA Best Practices
Ensure unusable index partitions are caught by you, not your apps
32
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Unusable index partitions
Automagically, index partitions are marked unusable • Aborted direct path Loader sessions • Some partition maintenance operations local • Partition maintenance operations global select 'alter index '||owner||'.'||index_name|| ' rebuild partition '||partition_name||';' from dba_ind_partitions where status= 'UNUSABLE';
33
Michael S. Abbey – Advanced DBA Best Practices
3 "L" words – locks & latches turn into lousy performance if not detected early
34
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Locks
• Two sessions vying for the same resource • catblock.sql – rdbms/admin • Assortment of cryptic lock views • Narrow down sessions and convert to OS pid using v$session and v$process
v$session paddr
v$process addr spid
35
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Latches
• The DBA's nightmare • Low level serializable mechanisms designed to protect global data structures in the SGA • Life expectancy sub-second • Latches are to memory as locks are to disk
36
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Latches col Requests form 999,999,999,990 head 'Requests' col PctMiss form 99.90 head 'PCTMiss'
select name,gets+misses Requests, round(misses/decode(gets+misses,0,-1, gets+misses)*100,2) PctMiss
from v$latch where misses/decode(gets+misses,0,-1,gets+misses)*100 > 10 order by gets desc;
37
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Latches
select count(*) from x$kglpn; Absolute over a certain amount (static)
X:Y
Relative compared to the norm
38
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Latches
• A healthy environment returns counts well under 200 • Spinning for latches to gain access to precious resources • Tempting to increase latch counts • Low level supervisory mechanism would not allow
39
Michael S. Abbey – Advanced DBA Best Practices
Detect spikes in system load that affect performance
40
Michael S. Abbey – Advanced DBA Best Practices
41
Monitoring System load #!/bin/ksh typeset -i THRESHOLD typeset -i LOAD HOUR=$(date +%H) if (( $HOUR > 7 && $HOUR < 17 )); then THRESHOLD=$1 else THRESHOLD=$2 fi tmpuptime=`uptime` tmploads=${tmpuptime##*average:} LOAD=${tmploads%%.*} if (( $LOAD > $THRESHOLD )); then echo "Load of $LOAD exceeded threshold of $THRESHOLD" exit 1 fi exit 0
Michael S. Abbey – Advanced DBA Best Practices
Detect devices with low free space
42
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Disk usage
Some locations are crucial to backups exports archived redo logs #!/bin/ksh # $1 - the filesystem to check # $2 - the byte count threshold typeset -i bytesused typeset -i pctused pctused=$(df -k |grep $1 | awk '{print $5}' | sed 's/%//') bytesused=$(df -k |grep $1 | awk '{print $4}') if (( $bytesused < $2 )) then print $1 is $pctused% full, PROBLEM. fi
43
Michael S. Abbey – Advanced DBA Best Practices
SQL statement contention is a sibling of latch contention
44
Michael S. Abbey – Advanced DBA Best Practices
Monitoring SQL statement contention
• Concurrent executions • Location of data • Clustering • Default RS locking mode
45
Michael S. Abbey – Advanced DBA Best Practices
Monitoring SQL statement contention select 'Too many sessions ( '||to_char(count(*))||' running ' substr(sql_text,1,80) query from v$session s, v$sqlarea sa where s.sql_address = sa.address
and s.sql_hash_value = sa.hash_value and s.status = 'ACTIVE' and s.audsid != 0 and sql_text not like 'select sql_text%'
group by substr(sql_text,1,80) having count(*) > 100;
46
Michael S. Abbey – Advanced DBA Best Practices
Why do you think Oracle writes that alert log anyways?
47
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Alert log
• Per instance • Significant errors • Lots of meaningless gobbledygook • Establish a marker – Read on – Set new marker
• Ignore list
00600 03113 01142 01598
00604 06512 07445 01659 01146 01545
Err on the side of the client—if the pages are not necessary, add to the exclude list.
48
Michael S. Abbey – Advanced DBA Best Practices
Who discards the state of those packages anyways?
49
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Invalid objects
• SYS and SYSTEM – Expect DBMS_ invalid packages – Run dbmsxxxx,sql & prvtxxxx.plb – Interdependencies cause one to invalidate other • DBA_DEPENDENCIES
oracle> grep –il dbms_job *sql a0800150.sql catjobq.sql dbmsjob.sql e0800150.sql statsauto.sql statscusr.sql statspack.sql oracle> sqlplus /nolog SQL> connect / as sysdba . . . SQL> @dbmsjob . . . . . . SQL> @prvtjob.plb
50
Michael S. Abbey – Advanced DBA Best Practices
Ensure you standby your standby
51
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Standby database – is it up? But I'm still on 8, what should I do??
typeset -i LINES Replace line export log_history=\$log_history 1 with echo "connect / as sysdba internal, and desc v$log_history get rid of the exit"| sqlplus -s /nolog > recid.log /nolog! LINES=`grep RECID recid.log|wc -l` if (( $LINES = 0 )) then echo Standby down exit 1 else echo Standby OK exit 0 fi
52
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Standby database – is it up-to-date? typeset -i CHECKER export log_history=\$log_history CHECKER=`echo "connect / as sysdba set echo off feed off pages 0 select (sysdate-(max(first_time)))*24 from v$log_history;" | sqlplus –s /nolog` if (( $CHECKER > 1 )) then echo Recovery older than 1 hour exit 1 else echo OK exit 0 fi
53
Michael S. Abbey – Advanced DBA Best Practices
54
Monitoring Is standby useable?
• Do an a regular basis • Weekly, bi-weekly, every 48 hours? • Script and inspect output • Significant output (dual??)
*
echo "/ as sysdba alter database open read only; describe dual; shutdown immediate startup nomount; alter database mount standby database;" | sqlplus -s > roc.log LINES=`grep DUMMY roc.log|wc -l` if [ $LINES = 1 ] then echo OK exit 0 else echo Problem @standby ... exit 1 fi
Only required for 8i
*
Michael S. Abbey – Advanced DBA Best Practices
55
Monitoring Direct inserts on master This is a problem on the standby, so ya'd better watch out!
On master, there is no undo or redo for these inserts!
alter session enable parallel dml;
All transactions propagated from master to standby via archived redo logs. Direct inserts not logged!
alter table mailer nologging; insert into mailer select /*+ parallel (mailer,2) */ * from mailer@hasek; commit;
Beware or else
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Direct inserts on master
• mailer table on master row count = 12,345,781 on July 12 • 77,999,201 rows created in mailer on master on July 12 • Standby database activated due to disaster on July 13
select num_rows from user_tables where table_name = 'MAILER'; NUM_ROWS -----------13889778
Over-simplified, but the gist of the problem.
56
Michael S. Abbey – Advanced DBA Best Practices
57
Monitoring Detection of nologging activities col col col col
tablespace_name form a20 head 'Tablespace' file_name form a30 head 'File' tablespace_name form a20 head 'Tablespace' unrecoverable_time form a20 head 'Change Time'
select ddf.file_name,ddf.tablespace_name, vd.unrecoverable_time from sys.dba_data_files ddf,v$datafile vd where ddf.file_name = vd.name and nvl(vd.unrecoverable_time,trunc(sysdate+10)) > to_date('&1','DD-MON-YYYY');
Resolution Babette
Michael S. Abbey – Advanced DBA Best Practices
Ensure statistics exist for CBO and they are current
58
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Table statistics -- Not select from where and or
analyzed owner,table_name sys.dba_tables owner not in ('SYS','SYSTEM') nvl(num_rows,0) = 0 last_analyzed is null;
-- Not select from where and and
analyzed for pre-determined number of days owner,table_name sys.dba_tables owner not in ('SYS','SYSTEM') last_analyzed is not null trunc(last_analyzed) < trunc(sysdate)-5;
59
Michael S. Abbey – Advanced DBA Best Practices
Make sure YOU are not the bottleneck
60
Michael S. Abbey – Advanced DBA Best Practices
Monitoring The real story
• dba_ and user_ views are expensive • Learn your way around the x$ tables • With credit to Steve Adams (ixora) – Build x_$ views to match their corresponding x$ views – Grant select to public – Reference these views using sys.{view_name}
61
Michael S. Abbey – Advanced DBA Best Practices
Monitoring The real story set pages 0 lines 999 trimsp on echo off ver off feed off spool xdollars select 'create view x_$'||substr(table_name,3)|| ' as select * from '||table_name||';' from user_tables where table_name like 'X$%'; spool off set echo on feed on spool xdollars.log
@xdollars.lst spool off
62
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Dollar signs
• Familiarize yourself with the v$ views • Available when database mounted and not open • Column names inconsistent with dba_ counterparts select table_name from dict where table_name like 'V$%' order by 1;
63
Michael S. Abbey – Advanced DBA Best Practices
64
Monitoring Roadmap to $# select ds.owner, ds.segment_name, ds.partition_name, ds.segment_type, . . . from sys.uet$ e, sys.sys_dba_segs ds, sys.file$ f . . . select /*+ ordered use_nl(e) use_nl(f) */ . . . from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f DONE
Michael S. Abbey – Advanced DBA Best Practices
Monitoring sys_dba_segs select u.name, o.name, o.subname, . . . from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s, sys.file$ f . . . select u.name, un.name, NULL, . . . from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f . . . select u.name, to_char(f.file#)||'.'||to_char(s.block#), . . . from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
65
Michael S. Abbey – Advanced DBA Best Practices
Monitoring This is the end
select select select select select select select select select
. . . . . . . . .
. . . . . . . . .
. . . . . . . . .
from from from from from from from from from
sys.tab$ t sys.tabpart$ tp sys.clu$ c sys.ind$ i sys.indpart$ ip sys.lob$ l sys.tabsubpart$ tsp sys.indsubpart$ isp sys.lobfrag$ lf
66
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Caveat at upgrade time
67
Michael S. Abbey – Advanced DBA Best Practices
Monitoring So many alerts!!
68
Michael S. Abbey – Advanced DBA Best Practices
The autoextensible black hole revealed
69
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Autoextension
• Default for many installations • 2 serious issues when requested extension – over 2Gb limit – cannot be physically accommodated one device
• Oracle is known to not do a very good job of recovering from aborted autoextension • Cleanup after abend not handled well
70
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Autoextension create table aemon ( file_name sdate bytes insert select from where
varchar2(30), date number);
into aemon file_name,sysdate,bytes dba_data_files autoextensible = 'YES';
71
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Autoextension select fst.file_name, fst.bytes, fsy.bytes, round((fst.bytes-fsy.bytes)/fsy.bytes *100,2) from aem fsy, aem fst where fst.file_name = fsy.file_name and trunc(fst.sdate) = trunc(sysdate) and trunc(fsy.sdate) = trunc(sysdate-1) and fst.bytes > fsy.bytes;
72
Michael S. Abbey – Advanced DBA Best Practices
Monitoring Best practices
• Connection testing • Anticipate common problematic areas • Be thorough and proactive • Email gateway • Share the load
• Page and/or email • Document via some form of tracking system • MetaLink MetaLink MetaLink MetaLink MetaLink MetaLink
73
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora
Best practices NOW will payoff down the road
74
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora • • • • •
Organization Compatibility Shared pool Ramifications Undocumented parameters
75
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora must be clean and readable
76
Michael S. Abbey – Advanced DBA Best Practices
77
INIT.ora Organization
• Not a fan of ifile • Alphabetical order sort < initumc.ora > sio cp sio initumc.ora rm sio
• Clean up dbs directory – What is soxx??
Significant defaults – Hard-code – Movements between versions
• Toggles stay in place • Comment changes • Show parameters
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora Compatibility
• Hard-code – Surprises during upgrades – Are you going far enough
• New features – Can I use them? – Can I go back?
• Access to new features • Changed behaviour of old features
78
Michael S. Abbey – Advanced DBA Best Practices
Wading through the shared pool
79
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora Shared pool
• Increasing demands as version increases • CURSOR_SHARING in 8.1.6 is a big deal – – – –
Default is EXACT Preferred is FORCE Linux 8.1.7 Alpha 8.1.6
• Bigger is not always better • 4031 errors centre around 4096 – _shared_pool_reserved_min_alloc
at 4000 • Flushing the pool – Manual or transparently – A double-edged sword
• Get yourself in a bind
80
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora
81
Michael S. Abbey – Advanced DBA Best Practices
Aware of ramifications when changing values
82
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora Ramifications of higher values • Semaphores are advisory locking mechanisms that ensure a server completes certain tasks before beginning another. • Some parameter values drain semaphores and others look for larger portions of shared memory DB_CACHE_SIZE LARGE_POOL_SIZE SHARED_POOL_SIZE
OPEN_CURSORS PROCESSES
set set set set set set set set set
shmsys:shminfo_shmmax=4294967295 shmsys:shminfo_shmmin=1 shmsys:shminfo_shmmni=100 shmsys:shminfo_shmseg=10 semsys:seminfo_semmni=100 semsys:seminfo_semmsl=500 semsys:seminfo_semmns=500 semsys:seminfo_semopm=1000 semsys:seminfo_semvmx=32767
83
Michael S. Abbey – Advanced DBA Best Practices
More than meets the eye
84
Michael S. Abbey – Advanced DBA Best Practices
INIT.ora Undocumented parameters x$ksppi Name --------ADDR INDX INST_ID KSPPINM KSPPITY KSPPDESC KSPPIFLG
x$ksppcv Type ---------------RAW(4) NUMBER NUMBER VARCHAR2(64) NUMBER VARCHAR2(64) NUMBER
Name ----------ADDR INDX INST_ID KSPPSTVL KSPPSTDF KSPPSTVF KSPPSTCMNT
Type -------------RAW(4) NUMBER NUMBER VARCHAR2(512) VARCHAR2(9) NUMBER VARCHAR2(255)
85
Michael S. Abbey – Advanced DBA Best Practices
86
INIT.ora Undocumented parameters select ksppinm,ksppstvl,ksppdesc from x$ksppi x,x$ksppcv y where x.indx = y.indx and translate(ksppinm,'_','#') like '#%' order by 1 KSPPINSM
KSPPSTVL
KSPPDESC
-------------------------------- --------- --------------------------------_allow_resetlogs_corruption
FALSE
allow resetlogs even if it will cause corruption
_corrupted_rollback_segments
corrupted undo segment list
_db_handles_cached
5
Buffer handles cached each process
_shared_pool_reserved_min_alloc
4400
minimum allocation size in bytes for reserved area of shared pool
Michael S. Abbey – Advanced DBA Best Practices
87
INIT.ora Best practices
• Readable is crucial • Comment changes • Cleanup the dbs directory • Be familiar with memory consumers • Semaphore limitations
• Familiarize yourself with dynamic memory structures in 9i • Block size caches 9i • Watch out for shared_pool_size > 90000000 Issues Rich
Michael S. Abbey – Advanced DBA Best Practices
Do not wait for WAITs
Best practices NOW will payoff down the road
88
Michael S. Abbey – Advanced DBA Best Practices
Are wait situations detracting from overall performance?
89
Michael S. Abbey – Advanced DBA Best Practices
Do not wait for WAITs • Wait situations are the biggest performance detractor • Latches—protect the integrity of shared memory structures. • Locks—protect the integrity of your data.
• V$ dynamic performance views: – v$waitstat – v$session_wait
• DBA_ views – dba_blockers (catblock.sql)
90
Michael S. Abbey – Advanced DBA Best Practices
Do not wait for WAITs v$session_wait select event,count(*),sum(seconds_in_wait) siw from v$session_wait group by event; EVENT COUNT(*) SIW --------------------------- -------- --------SQL*Net message from client SQL*Net message to client db file sequential read log file parallel write pmon timer rdbms ipc message smon timer
1192 2 5 1 1 24 1
747,039 0 0 0 9,062 128 908
91
Michael S. Abbey – Advanced DBA Best Practices
Where has all the CPU gone?
92
Michael S. Abbey – Advanced DBA Best Practices
Do not wait for WAITs Who's doing what
• Map running SQL to user sessions • Zero in on CPU consumers select from where and and and and
sql_text, sid, serial# v$session s, v$sqlarea sa s.sql_address = sa.address s.sql_hash_value = sa.hash_value s.status = 'ACTIVE' s.audsid != 0 sql_text not like 'select sql_text%';
93
Michael S. Abbey – Advanced DBA Best Practices
Do not wait for WAITs Relationship to system load
• Get a handle on "normal" system load using uptime command • Spool to log file in the crontab, running every 5 minutes */5 /oracle/bin/uptime.sh >> /oracle/logs/uptime.sh.log 2>&1
• High load averages almost always map to SQL*Net message from client wait situations db_file sequential read db file scattered read
94
Michael S. Abbey – Advanced DBA Best Practices
95
Do not wait for WAITs utllockt.sql set charwidth 17 select lpad(' ',3*(level-1))||waiting_session, waiting_session,lock_type,mode_requested, mode_held,lock_id1,lock_id2 from lock_holders connect by prior waiting_session = holding_session start with holding_session is null; WAITING_SESSION
TYPE MODE REQUESTED
MODE HELD
LOCK ID1 LOCK ID2
----------------- ---- ----------------- ----------------- -------- -------553
NONE None
None
0
0
TX
Share (S)
Exclusive (X)
34888
39
378
RW
Exclusive (X)
S/Row-X (SSX)
33255666
2
3928
RW
Exclusive (X)
S/Row-X (SSX)
3255666
2
213
Michael S. Abbey – Advanced DBA Best Practices
96
Do not wait for waits Best practices
• Waiting drains system resources • Wait events cause destructive spinning • Minimizing potential waits foremost in your minds • Commit often
• SQL statement contention • Concurrent user requests for the same data in the cache • DO not exploit multitasking Issues Rich
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups
Best practices NOW will payoff down the road
97
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Media recovery enabled (archivelog mode) 1. Image backups 2. Export
5. INIT.ora 6. listener.ora 7. tnsnames.ora
– No indexes / No constraints – No rows
Assume these are run nightly
3. Control file
98
Michael S. Abbey – Advanced DBA Best Practices
Backup is nada without integrity and recovery testing
99
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Image backups
Best case scenario • Separate server to restore image • Deliberate complete media recovery – recover database . . . – recover datafile . . . – recover tablespace . . .
• Deliberate incomplete recovery at the database level
100
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Image backups
• Mock disaster recovery • Ideally on the same machine – With no downtime, involves an ORACLE_SID change • using backup controlfile is not the Real McCoy
– Plan during a window of opportunity
• Make sure you have performed the recovery portion of your image is NOT tested
101
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Image backups
• NFS device is no substitute for offline storage but … – Mount points all over the corporate server environment – Target of all images and exports
• Run the NFS out to secondary storage • root user buy-in
102
Michael S. Abbey – Advanced DBA Best Practices
Tested rebuild from full database exports
103
Michael S. Abbey – Advanced DBA Best Practices
104
Integrity of your backups Test rebuild from full database export
• Create the database (see in Infrastructure section) • Run 3 or 4 full database imports Just definitions NOT data
1
4
inctype=system
rows=n ignore=y
online rollback segments just built
2
3
inctype=restore
rows=n ignore=y
Michael S. Abbey – Advanced DBA Best Practices
Standby assisting checkup of recovery process
105
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Testing using standby technology
• Shutdown your standby • Re-instantiate (hot /u02/hot) set pages 0 trimsp on lines 999 echo off feed off select 'scp /u02/hot'||file_name|| ' [email protected]:'||file_name from dba_data_files; set pages 0 trimsp on lines 999 echo off feed off select 'gzip –d '||file_name||'.gz' from dba_data_files;
106
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Testing using standby technology
• Re-get standby control file • Copy to appropriate control_files = locations alter database create standby controlfile as 'standby.ctl';
set pages 0 trimsp on lines 999 echo off feed off select 'cp standby.ctl '||name from v$controlfile;
107
Michael S. Abbey – Advanced DBA Best Practices
Data integrity of your exports
108
Michael S. Abbey – Advanced DBA Best Practices
109
Integrity of your backups How's export written
• Mismatch between UNIX environment and Server character set • Determined by NLS_LANG
Wake-up
Export: Release 9.2.0.5 - Production on Sat Sep 31 16:47:52 2004 (c) Copyright 2001 Oracle Corporation.
All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5 - Production
With the Partitioning option JServer Release 9.2.0.5 - Production Export done in WE8DEC character set and AL16UTF16 NCHAR character set server uses US7ASCII character set (possible charset conversion)
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Character set conversion
Collège Brébeuf Frhre Norman: Mrs. Jelinski and I were very pleased with our visit to Collhge Bribeuf, and are pleased with what we saw. We are especially happy that we see eye to eye on the preservation of the native Francophone culture with something as simple as accent retention in our Oracle9i database!
110
Michael S. Abbey – Advanced DBA Best Practices
Compression checking your export/import engine
111
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Gee, zip? rm export.pipe > /dev/null 2>&1 mkfifo export.pipe gzip < export.pipe > fulldb.dmp.gz & sleep 2 exp parfile= fulldb.parfile
Usable in its compressed state? 8i
userid=/ full=y log=fulldb indexes=n constraints=n
triggers=n
buffer=5000000 triggers=n file=export.pipe
9i
112
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Gee, unzip? rm import.pipe > /dev/null 2>&1 mkfifo import.pipe gunzip < fulldb.dmp.gz > import.pipe & sleep 2 imp userid=/ file=import.pipe full=y log=fulldb_in buffer=50000000
Better find out sooner than during a disaster recovery!! IMP-00037: Character set marker unknown IMP-00000: Import terminated unsuccessfully
IMP-00009: abnormal end of export file IMP-00000: Import terminated unsuccessfully
113
Michael S. Abbey – Advanced DBA Best Practices
Bottom-line … can I read that export file?
114
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Read this, import! imp userid=/ full=y indexfile=fulldb.sql log=fulldb_if Import: Release 9.2.0.5 - Production on Sat Jun 30 22:13:49 2004 (c) Copyright 2001 Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.5 - Production With the Partitioning option JServer Release 9.2.0.5 - Production
Export file created by EXPORT:V09.20.00.05 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set Import terminated successfully without warnings.
115
Michael S. Abbey – Advanced DBA Best Practices
Integrity of your backups Best practices
• Only 10% of the way there without testing recovery • Full and partial components of database • Missing archived redo logs
• Missing control file(s) • Test rebuild from export • Test clone on another server • Role played by your standby
116
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup The
Oracle
9i
Best practices NOW will payoff down the road
117
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup A word on creating a database I've been an Oracle DBA for 2,000 years (US) and have never created a database; why start now?? I always upgrade
No thanks, sonny, I went to a user group meeting. As soon as I got back to the office I started creating databases. I learned SO MUCH!! You ever tried it?? You need the support N more than me!!
Please sit
o
118
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Creating the database
• 9i Oracle Managed files by specifying – – – – –
db_create_file_dest db_create_online_log_destn undo_management = auto undo_tablespace = rollback undo_retention = 2000
create database umc undo tablespace rollback default temporary tablespace temp;
• Do it yourself – Create database • • • • • •
controlfile character set maxlogfiles maxloghistory maxlogmembers maxdatafiles
– Datafile – Log file(s)
119
Michael S. Abbey – Advanced DBA Best Practices
Step-by-step database creation; the players
120
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Creating the database yourself
• The SYSTEM tablespace – systemnn.dbf ~ 250m – Set pctincrease 1 (later)
• Redo log groups – At least dual twomembered groups – logmn_gn.ora (member number / group number)
• Construct first nonSYSTEM rollback segment – Acquire 20 extents of ~ 100k each
• Run admin scripts • Test creation
121
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Create database – phase 1 startup nomount create database umc controlfile reuse maxlogfiles 16 maxlogmembers 2 maxdatafiles 2048 maxloghistory 1000 character set we8iso8859p1 datafile '/data01/oracle/umc/dbs1_umc.dbf' size 250m logfile group 1 ('/redo01/umc/log1_g1.dbf', '/redo02/umc/log2_g1.dbf') size 200m, group 2 ('/redo02/umc/log1_g2.dbf', '/redo01/umc/log2_g2.dbf') size 200m;
122
Michael S. Abbey – Advanced DBA Best Practices
123
Infrastructure setup Create database (1) – important points
1. For this or create controlfile 2. Will not error out if files already exist 3. Make an artificially large # 4. Do not allow to default – WE good place to start 5. No smaller 6. 2 dual membered groups
1
startup nomount create database umc controlfile reuse
2
maxlogfiles 16 maxlogmembers 2 maxdatafiles 30
3
maxloghistory 1000 character set
4
datafile '/data01/…' size 250m logfile group 1 ('…1_g1.dbf',
5 6
'…2_g1.dbf') size 200m, group 2 ('…1_g2.dbf', '…2_g2.dbf') size 200m;
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup A word on sizing online redo logs
Two choices:
1. Small online redo logs to minimize time to push to standby 2. Customary large redo logs with forced log switches
124
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Create database – phase 2 shutdown startup alter tablespace system default
storage (pctincrease 1); create rollback segment rb_temp tablespace system storage (initial 50k next 50k minextents 20 maxextents 20); alter rollback segment rb_temp online;
125
Michael S. Abbey – Advanced DBA Best Practices
126
Infrastructure setup Create database (2) – important points 1 1. No more to be done nomount – rest of work requires 2 instance to be open. 2. Permit pmon to automatically coalesce free space in SYSTEM 3. First non-SYSTEM rollback segment (~1Mb) 4. Rollback segments created OFFLINE by default; edit INIT.ora at the same time so you do not forget shutdown startup
alter tablespace system default storage (pctincrease 1);
create rollback segment rb_temp
3
tablespace system storage (initial 50k next 50k minextents 20 maxextents 20);
alter rollback segment rb_temp online;
4
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Create database – phase 3 @?/rdbms/admin/catalog
Build the data dictionary
@?/rdbms/admin/catproc
Install PL/SQL
@?/rdbms/admin/catrep
Replication
@?/rdbms/admin/catsnap
Snapshot specifics
@?/rdbms/admin/dbmsutil
Popular utility packages
@?/rdbms/admin/prvtutil.plb
@?/rdbms/admin/dbmssql @?/rdbms/admin/prvtsql.plb
""
SQL utilities ""
127
Michael S. Abbey – Advanced DBA Best Practices
128
Infrastructure setup Create database – phase 4 Right option(s)
Version numbering EE/Standard Expected feedback
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup
Database is created Data dictionary has been populated First non-system rollback segment exists and is online
129
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup pupbld.sql
130
Michael S. Abbey – Advanced DBA Best Practices
Fluency in rollback segment setup
131
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Rollback segments
• 4 to 6 equally-sized • Large segment for large segments transactions • minextents and maxextents – 2 to 4Gb the same at creation time – Larger extent size • 500k to 2Mb extents – Cap at creation time • Not in locally managed • No guarantee unless the tablespaces only one online • Mentioned in INIT.ora
132
Michael S. Abbey – Advanced DBA Best Practices
133
Infrastructure setup Rollback segment tablespace(s)
• Dedicated tablespace • Appropriately named
File names contain tablespace name.
create tablespace rollback datafile '/u3/oradata/umc/rollback01.dbf' size 1024m, '/u5/oradata/umc/rollback02.dbf' size 1024m, '/u5/oradata/umc/rollback03.dbf' size 1024m, '/u5/oradata/umc/rollback04.dbf' size 1024m;
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Rollback segment creation create rollback segment rbs01 tablespace storage (initial 2m next 2m minextents 499 maxextents 499); create rollback segment rbs02 tablespace storage (initial 2m next 2m minextents 499 maxextents 499); create rollback segment rbs03 tablespace storage (initial 2m next 2m minextents 499 maxextents 499); create rollback segment rbs04 tablespace storage (initial 2m next 2m minextents 499 maxextents 499);
rollback
rollback
rollback
rollback
ROLLBACK_SEGMENTS = (rbs01,rbs02,rbs03,rbs04)
134
Michael S. Abbey – Advanced DBA Best Practices
Empowering the techies with do-it-yourself
135
Michael S. Abbey – Advanced DBA Best Practices
136
Infrastructure setup v$dba
• v$ dynamic performance views • Over 200 with 9i – most accessible when database mounted • DBA_ dictionary views similar to their USER_ counterparts with ownership column • There's nothing to hide • Grant access but no public synonyms
Mother may I ??
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup v$dba connect / as sysdba set echo off pages 0 lines 999 trimsp on feed off spool veedba select 'grant select on '||view_name||' to public;' from user_views where view_name like 'DBA%' or view_name like 'V_$%';
• Often used here and there by applications • V$ public synonyms probably exist • Granting must be done on view, not public synonym for V$ to avoid ORA-02030: can only select from fixed tables/views
137
Michael S. Abbey – Advanced DBA Best Practices
Fluency in locally managed
138
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Locally managed tablespaces
• Different syntax than DM counterparts • Temporary files end up in DBA_TEMP_FILES • Free space bitmap in datafile headers • Reduced recursive I/O
• Extent management local – autoallocate turns Oracle loose with extent sizing • Objects of differing sizes • Lots of different sized extents
– uniform size {} defaults to 1Mb
139
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Locally managed tablespaces create tablespace loc_geo_index datafile '/u01/oradata/loc_geo_index01.dbf' size 2000m, '/u04/oradata/loc_geo_index01.dbf' size 2000m extent management local autoallocate ;
create tablespace loc_geo_index datafile '/u01/oradata/loc_geo_index01.dbf' size 2000m, '/u04/oradata/loc_geo_index01.dbf' size 2000m extent management local uniform size 20m;
140
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup LM temp tablespace create temporary tablespace loc_temp datafile '/u01/oradata/loc_temp01.dbf' size 2000m, '/u04/oradata/loc_temp02.dbf' size 2000m extent management local uniform size 20m;
• Keep extents in TEMP below 1000 • Space allocated then controls uniform size • Great deal of the work done at startup involves reconciliation and transfer of block ids from free to unallocated (fet$ to uet$)
141
Michael S. Abbey – Advanced DBA Best Practices
Infrastructure setup Best practices
• Make a point of starting from the beginning (create database) • Pay attention to each piece in the puzzle • Completeness checking at the end
• Practice practice practice • Do temp and rollback right • Know your friendly neighbourhood admin scripts
142
Michael S. Abbey – Advanced DBA Best Practices
Schema Environments
Best practices NOW will payoff down the road
143
Michael S. Abbey – Advanced DBA Best Practices
Establish and follow guidelines
144
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Guidelines
• Thou shalt not give user an overrich environment • Thou shalt not sort or default to SYSTEM • Thou shalt occupy space in appropriately-named tablespaces • Thou shalt not have DBA, CONNECT, or RESOURCE
145
Michael S. Abbey – Advanced DBA Best Practices
Smart when setting up your user environments
146
Michael S. Abbey – Advanced DBA Best Practices
147
Schema environments User creation • Creating a user • Temporary tablespace • Use O/S authorization for all host-based users • Connection and privileges depend on function
create user sample identified by sx_p0 temporary tablespace loc_temp; alter user sample identified externally;
-- Jack and Jill users grant create session to jj_user;
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Application data users
• Give necessary privileges and no more grant create table, create view, create procedure, create trigger, create synonym, create snapshot, create database link to sample;
• Who has more than they need?? • Who has been given DBA • Are the any privileges given out correctly?
148
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Who has more than they need?? select grantee, privilege -- SAMPLE is a schema from sys.dba_sys_privs -- deliberately set up to where privilege not in -- set the standard (select privilege from sys.dba_sys_privs where grantee = 'SAMPLE') UNION select grantee, granted_role from sys.dba_role_privs where granted_role in ('DBA','CONNECT', 'RESOURCE','EXP_FULL_DATABASE', 'IMP_FULL_DATABASE') order by 1;
149
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Who has more than they need?? GRANTEE -----------------ABRAMSON ABRAMSOM JACKSON JACKSON JONES REDMOND THOMSON THOMSON THOMSON UQUART UQUART
PRIVILEGE ----------------------DBA DROP PUBLIC SYNONYM CONNECT RESOURCE DBA SELECT ANY TABLE DBA INSERT ANY TABLE UPDATE ANY TABLE DBA UNLIMITED TABLESPACES
150
Michael S. Abbey – Advanced DBA Best Practices
Adopt conventions that make sense
151
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Naming conventions
• Plan for the future • Picture yourself at 4:15 am weeding through cryptic names • Bind tablespace names to owner USERNAME • Marry data and index tablespace names
152
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Naming tablespaces
• Based on USERNAME create tablespace DELIVERY datafile -- Data segments '/u01/oracle/oradata/delivery01.dbf' size 1024m, '/u05/oracle/oradata/delivery02.dbf' size 1024m default storage (initial 100m next 100, pctincrease 0);
• Based on function (data or index) create tablespace DELIVERYX datafile -- Index segments '/u02/oracle/oradata/deliveryx01.dbf' size 1024m, '/u03/oracle/oradata/deliveryx02.dbf' size 1024m default storage (initial 100m next 100, pctincrease 0);
153
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Naming tablespaces - violators
Define rules first, implement, and track – Data segment names = USERNAME – Index segment names = USERNAME with X – Partitioned objects = USERNAME followed by underscore ~ X for index partitions ~ PTS for partitioned tablespace select from where or
distinct tablespace_name sys.dba_segments tablespace_name not in (owner,owner||'X') (tablespace_name not in ('%'||owner||'%X%'||'%PTS%') or tablespace_name not in ('%'||owner||'%PTS%'));
154
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Naming partitions
• Object name • Index non-unique or unique indicator + sequential number • Underscore separators • Pnnnn forcing fixedlength names for sorts • Abbreviate carefully
• Imbed hp for hashpartitioned objects – Affects syntax for maintenance – Affects how operations are carried out
• Fix names after merges and splits
155
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Data partitions create table mail (originator varchar2(40), created date, . . . . . . read varchar2(1)) storage (initial 200m next 200m pctincrease 0) partition by range (created) (partition post_mail_p0001 values less than (to_date('01-JAN-2001','DD-MON-YYYY')) tablespace post_mail_pts_0001, partition post_mail_p0002 values less than (to_date('01-JUL-2001','DD-MON-YYYY')) tablespace post_mail_pts_0002, partition post_mail_p0003 values less than (to_date('01-JAN-2002','DD-MON-YYYY')) tablespace post_mail_pts_0003, . . . . . . partition post_mailing_pmax values less than (maxvalue) tablespace post_mail_pts_max);
156
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Index partitions create index mail_n1 on mail (originator) (partition post_mail_n1_p0001 values less than tablespace post_mailx_pts_0001, partition post_mail_n1_p0002 values less than tablespace post_mailx_pts_0002, partition post_mail_n1_p0003 values less than tablespace post_mailx_pts_0003, . . . . . . partition post_mail_n1_pmax values less than (maxvalue) tablespace post_mailx_pts_max);
157
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Naming indexes Table name Abbreviation(s) for column(s) in index
Table name
N for non-unique indexes U for unique indexes Separate series for each type 2 schools of thought
158
Michael S. Abbey – Advanced DBA Best Practices
Key index decisions
159
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Unique index vs. constraint
• Primary key constraints – Always name with _PK suffix – With using index are the same as unique indexes – Allow foreign key references alter table mailer add constraint mailer_pk primary key (id) using index storage (initial 300m next 300m pctincrease 0) tablespace loc_mail_indx;
160
Michael S. Abbey – Advanced DBA Best Practices
Schema environments Best practices
• Users allowed to do what they need and nothing more • Segregate users by function • Adopt and follow naming conventions
• Trap and report on violators • 30 character limit – Abbreviate smartly – Abbreviate consistently – Ensure all players are aware
161
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing
Best practices NOW will payoff down the road
162
Michael S. Abbey – Advanced DBA Best Practices
Manage (not mis-manage) your replication environment
163
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing Snapshots are a snap
• • • • •
Use same name as master table Give them storage parameters just like tables Negotiate refresh interval Ensure there are no premature refreshes Existing refresh intervals . . .
select job,what,interval from user_jobs where lower(what) like 'dbms_refresh%';
164
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing Indexes on snapshots
• • • •
Primary key snapshots preferred ROWID (à la Oracle7) still supported in 9i PK constraint created with create snapshot Check for secondary indexes on snapshots by referring back to master • Do not check and forget—do so on a regular basis
165
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing Indexes on snapshots On master create snapshot log on vendor_prod storage (initial 2m next 2m pctincrease 0) tablespace sn_logs;
On remote
snapshot + secondary indexes
create snapshot vendor_prod refresh fast start with sysdate next sysdate+10/1440 as select * from vendor_prod@product tablespace loc_snaps;
166
Michael S. Abbey – Advanced DBA Best Practices
Thoroughness of indexing your replicated objects
167
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing Indexes on snapshots—completeness check col inm form a10 head Index col column_name form a7 head Column col column_position form 999 head Pos
break on inm select from where minus select from where order
index_name inm,column_position,column_name user_ind_columns table_name = upper('&1') index_name inm,column_position,column_name user_ind_columns@product table_name = upper('&1') by 1,2;
168
Michael S. Abbey – Advanced DBA Best Practices
Many many listeners
169
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing Some port with that?
• Use different ports for different connection requests • Separate by source – By web server – By connection type (jvm, cgi)
• Speak with the SA before choosing ports • Must be reflected on client(s)
170
Michael S. Abbey – Advanced DBA Best Practices
171
Distributed computing Multiple ports / multiple listeners LISTENER1521 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mofo1)(PORT = 1521))))) LISTENER1522 = (DESCRIPTION_LIST = (DESCRIPTION = tnsnames.ora aliases must reflect right port (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mofo1)(PORT = 1522)))))
#s
Michael S. Abbey – Advanced DBA Best Practices
Distributed computing Best practices
• Know how to replicate • Fundamental to the Internet environment • Offloading of processing crucial to life expectancy of sites with very large user community
• Completeness in your replication environment • Multiple listeners • Listener per snapshot (if enough to go around) • Fluency with DBMS_JOB package
172
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer
Best practices NOW will payoff down the road
173
Michael S. Abbey – Advanced DBA Best Practices
174
Cost-based optimizer DBA's best-friend
• Collecting of statistics like – – – –
C Number of rows Average row length Distinct column values High value / low value
• Run unattended periodically from crontab
• Frequency decisions
B O
– Consult IT personnel – Stale statistics more harm than good
• Using SQL*Plus analyze command OR PL/SQL packages • Inspect last_analyzed
Michael S. Abbey – Advanced DBA Best Practices
Decisions / approaches
175
Michael S. Abbey – Advanced DBA Best Practices
176
Cost-based optimizer Setup
• analyze is not going any where (yet) with C 9i and 10g • Perform from a central user • Preferably O/S authenticated [externally]
create user analyzer identified by pw; alter user analyzer identified externally;
B
grant create session to analyzer; grant analyze any to analyzer; O
We always identify externally
Michael S. Abbey – Advanced DBA Best Practices
177
Cost-based optimizer How often
• Size based – Pick a size – Treat those above threshold differently
• Usage based – Track most frequently changed objects – Exclude hot objects / treat differently
Michael S. Abbey – Advanced DBA Best Practices
Ensure you begin at the beginning
178
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer In the beginning
• • • • • •
Delete statistics EVERYWHERE Flip or set entry in INIT.ora Liaise with user community Pick 2 or 3 days a week Assess run time Assess impact on system
179
Michael S. Abbey – Advanced DBA Best Practices
180
Cost-based optimizer Deleting statistics set echo off pages 0 trimsp on lines 999 spool stat_del.sql select 'exec dbms_stats.delete_schema_stats (ownname=>'|| ''''||username||''''||')'
from sys.dba_users where username not in ('SYS','SYSTEM'); spool off set echo on feed on
spool stat_del @stat_del spool off
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Method
• Tables – Estimate sample {2-10} percent – Populates USER_TABLES
• Indexes – Compute – Populates USER_IND_COLUMNS
• Nothing short of a windfall
181
Michael S. Abbey – Advanced DBA Best Practices
Always be aware of your options
182
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Using PL/SQL procedure PROCEDURE GATHER_SCHEMA_STATS Argument Name Type ----------------- ---------OWNNAME VARCHAR2 ESTIMATE_PERCENT NUMBER BLOCK_SAMPLE BOOLEAN METHOD_OPT VARCHAR2 DEGREE NUMBER GRANULARITY VARCHAR2 CASCADE BOOLEAN STATTAB VARCHAR2 STATID VARCHAR2 OPTIONS VARCHAR2 OBJLIST DBMS_STATS STATOWN VARCHAR2
In/Out -----IN IN IN IN IN IN IN IN IN IN OUT IN
Default? -------DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT DEFAULT
183
Michael S. Abbey – Advanced DBA Best Practices
Four-part approach 1. Initialization parameters 2. Place objects in monitoring mode 3. Place a stake in the ground for each schema 4. Schedule regular statistic collection I. Do not use for Oracle Applications II. Do not use for third party apps – liaise with vendors
184
Michael S. Abbey – Advanced DBA Best Practices
1 – INIT.ora
185
Michael S. Abbey – Advanced DBA Best Practices
2 - Turn on monitoring set pages 0 lines 999 trimsp on feed off spool monon select 'alter table '||owner||'.'||table_name|| ' monitoring;' from sys.dba_tables where owner not in ('SYS','SYSTEM') and nvl(duration,'X') 'SYS$TRANSACTION'
and nvl(iot_type,'X') 'IOT_OVERFLOW' and nvl(temporary,'X') 'Y' and monitoring 'YES'; spool off
186
Michael S. Abbey – Advanced DBA Best Practices
187
3 – Stake in the ground set lines 999 trimsp on pages 0 spool sitg select unique 'exec dbms_stats.gather_schema_stats (ownname=>'|| ''''||owner||''''||',estimate_percent=>2,'||
'block_sample=>false,method_opt=>'|| ''''||'for all indexed columns size 1'|| ''''||',degree=>8,granularity=>'||''''||'ALL'||''''|| ',cascade=>true);'
from sys.dba_tables where owner not in ('SYS','SYSTEM'); spool off
Michael S. Abbey – Advanced DBA Best Practices
4 – Schedule regular collection • turn on monitoring – for tables not already in monitoring mode
• gather empty – new tables created since last run – tables inadvertently taken out of monitoring mode
• gather stale – those that have changed >= 10%
188
Michael S. Abbey – Advanced DBA Best Practices
4.1 – Turn on monitoring set pages 0 lines 999 trimsp on feed off spool monon select 'alter table '||owner||'.'||table_name|| ' monitoring;' from sys.dba_tables where owner not in ('SYS','SYSTEM') and nvl(duration,'X') 'SYS$TRANSACTION'
and nvl(iot_type,'X') 'IOT_OVERFLOW' and nvl(temporary,'X') 'Y' and monitoring 'YES'; spool off
189
Michael S. Abbey – Advanced DBA Best Practices
190
4.2 – gather empty select unique 'exec dbms_stats.gather_schema_stats '|| '(ownname=>'||''''||owner||''''|| ',estimate_percent=>1,block_sample=>false,'|| 'method_opt=>'||''''|| 'for all indexed'|| ' columns'||' size 1'||''''||',options=>'|| ''''||'gather empty'||''''||',degree=>8,'|| 'granularity=>'||''''||'ALL'||''''|| ',cascade=>true);' from sys.dba_tables where owner not in ('SYS','SYSTEM');
Michael S. Abbey – Advanced DBA Best Practices
191
4.3 – gather stale select unique 'exec dbms_stats.gather_schema_stats '|| '(ownname=>'||''''||owner||''''|| ',estimate_percent=>1,block_sample=>false,'|| 'method_opt=>'||''''|| 'for all indexed'|| ' columns'||' size 1'||''''||',options=>'|| ''''||'gather stale'||''''||',degree=>8,'|| 'granularity=>'||''''||'ALL'||''''|| ',cascade=>true);' from sys.dba_tables where owner not in ('SYS','SYSTEM');
Michael S. Abbey – Advanced DBA Best Practices
192
Rationalize the approach break on owner on table_name set pages 70 col table_name form a24 col partition_name form a24 col owner form a16 col pct form 999,999.9 select a.owner,a.table_name,b.partition_name, a.num_rows, (inserts+updates+deletes)/num_rows*100 pct, last_analyzed from sys.dba_tables a,sys.dba_tab_modifications b where a.table_name = b.table_name and a.owner = b.table_owner and (inserts+updates+deletes)/num_rows > .1 and nvl(num_rows,0) 0 order by 1,2
Michael S. Abbey – Advanced DBA Best Practices
Tried, tested, and true analyze (but not recommended unless you have no choice)
193
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Using analyze set echo off pages 0 feed off lines 999 trimsp on spool anaall.sql {analyze tables and indexes code} spool off
set echo on timi on feed on spool anaall @anaall spool off
Put the pieces together over the next 3 slides
194
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Using analyze on non-partitioned tables
select 'analyze table '||owner||'.'||table_name|| ' estimate statistics for table sample '||
'20 percent;' from sys.dba_tables where owner not in ('SYS','SYSTEM') and partitioned = 'NO' order by owner,table_name;
195
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Using analyze on non-partitioned indexes select 'analyze index '||owner||'.'||
index_name|| ' compute statistics;' from sys.dba_indexes where owner not in ('SYS','SYSTEM') and partitioned = 'NO' order by owner,index_name;
196
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Using analyze on partitioned tables select 'analyze table '||table_owner||'.'|| '.'||table_name||
' partition ('||partition_name||')'|| ' estimate statistics sample 2 percent;' from sys.dba_tab_partitions where table_owner not in ('SYS','SYSTEM') order by table_owner,table_name,partition_name;
197
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Using analyze on partitioned indexes select 'analyze index '||index_owner|| '.'||index_name|| ' partition ('||partition_name||')'|| ' compute statistics;'
from sys.dba_ind_partitions where index_owner not in ('SYS','SYSTEM') order by index_owner,index_name,partition_name;
198
Michael S. Abbey – Advanced DBA Best Practices
Not interfering with replication
199
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Exclusions with snapshots and (owner,table_name) not in (select distinct log_owner,master from sys.dba_snapshot_logs union select log_owner,log_table from sys.dba_snapshot_logs) and (owner,index_name) not in (select a.owner,a.index_name from sys.dba_indexes a, sys.dba_snapshot_logs b where a.table_name = b.master)
200
Michael S. Abbey – Advanced DBA Best Practices
201
Cost-based optimizer Exclusions with snapshots
• • • •
Analyzing collides with snapshot refreshes Restrictive lock inhibits job execution System load can skyrocket Horrific latch contention
6:57pm up 6 day(s), 7:12, 9 users, load average: 1.06, 1.08, 1.10
normal during analyze 4:13am up 8 day(s), 7:12, 9 users, load average: 341.06, 309.08, 301.10
Michael S. Abbey – Advanced DBA Best Practices
Stuff just too big to analyze
202
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Exclusions – large objects
• DBA nightmare • exclude based on row count • gather stale takes care of most very big tables • if still required – store large table names somwhere – join with dba_tables when building calls to DBMS_STATS
203
Michael S. Abbey – Advanced DBA Best Practices
204
Cost-based optimizer Implementation without study
XDBA
Hastily implemented collection plan
Michael S. Abbey – Advanced DBA Best Practices
Cost-based optimizer Best practices
• Know your options • Adopt an approach • Liaise with consumer • No cowboy implementation • Staleness of statistics
• The whole schema • Ensure new objects are analyzed – Whose responsibility – Step in promotion of code and dependent objects
• ONLY option
205
Michael S. Abbey – Advanced DBA Best Practices
Application tuning
Best practices NOW will payoff down the road
206
Michael S. Abbey – Advanced DBA Best Practices
Allow everyone to EXPLAIN themselves
207
Michael S. Abbey – Advanced DBA Best Practices
Application tuning plustrce role
• Create at once when setting up a new instance or inheriting an old one • plustrce.sql as SYS from ?/sqlplus/admin • PLAN_TABLE
beforehand using ?/rdbms/admin/utlxplan
• There's nothing to hide • Access to dynamic performance tables used for – v_$sessstat – v_$statname – v_$session
• Give access to all
208
Michael S. Abbey – Advanced DBA Best Practices
AUTOTRACE
and beyond
209
Michael S. Abbey – Advanced DBA Best Practices
210
Application tuning Empowering the developers
• Want to write good code • Arm them with the tools • Imbed testing into coding – education fosters growth
set autot trace exp SP2-0613: Unable to verify PLAN_TABLE format or existence SP2-0611: Error enabling EXPLAIN report
• Centrally managed PLAN_TABLE with public or private synonyms
Michael S. Abbey – Advanced DBA Best Practices
211
Application tuning Foster smart coding habits SQL> set autot trace exp SQL> select * from mailing partition (mailing_p04); Execution Plan ---------------------------------------------------------0 1
SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=164 Bytes=337676) 0
TABLE ACCESS (FULL) OF 'MAILING' (Cost=1 Card=164 Bytes=337676)
SQL> select * from mailing; Execution Plan
---------------------------------------------------------0
SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=984 Bytes=2026056)
1
0
2
1
PARTITION RANGE (ALL) TABLE ACCESS (FULL) OF 'MAILING' (Cost=2 Card=984 Bytes=2026056)
Michael S. Abbey – Advanced DBA Best Practices
Understanding the cost
212
Michael S. Abbey – Advanced DBA Best Practices
Application tuning Cost analysis set echo off term off feed off ver off select decode(id,0,'', lpad(' ',2*(level-1))||level||'.'||position)||' '||
operation||' '||options||' '||object_name||' '|| object_type||' '|| decode(id,0,'Cost = '||position) Query_plan from plan_table
connect by prior id = parent_id and statement_id = upper('&1') start with id = 0 and statement_id = upper('&1');
213
Michael S. Abbey – Advanced DBA Best Practices
Application tuning Listen and listen closely
You can only compare costs between different wordings of the same SQL statement NOT across statements.
You won't get away with it!
214
Michael S. Abbey – Advanced DBA Best Practices
Find potentially problematic SQL statements before it's too late
215
Michael S. Abbey – Advanced DBA Best Practices
Application tuning Buffer gets measurement select buffer_gets,sql_text,executions, buffer_gets/executions from v$sqlarea where buffer_gets > 200000
Good place to start
and v$sqlarea.executions != 0 order by buffer_gets/executions desc
216
Michael S. Abbey – Advanced DBA Best Practices
Assess I/O balance while applications interact with instance
217
Michael S. Abbey – Advanced DBA Best Practices
218
Application tuning I/O balance - primer col file_name form a30 head File col tablespace_name form a20 head Tbsp col a new_value preads col c new_value pwrites select sum(phyrds) a,sum(phywrts) c from v$filestat;
/u02 /u04
select file_name,tablespace_name, phyrds/&b*100 pctrd,phywrts/&d*100 pctwrt from sys.dba_data_files ddf,v$filestat vf where ddf.file_id = vf.file# and (phyrds/&preads*100 > 5 or phywrts/&pwrites*100 > 5) order by 3;
Michael S. Abbey – Advanced DBA Best Practices
Application tuning Best practices
• Share the responsibility • Make inroads with management
• Balanced I/O patterns • Arm the developers so they can help themselves
• Compare relative costs within different wordings of the same statement • Imbed in program development • Pick reasonable measurement indicators
219
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS
Best practices NOW will payoff down the road
220
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS MetaLink
• The first place to start • Be smart with your search criteria • Save yourself and OSS time • Someone else's problem yesterday is yours today • Learn from others' experience
221
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS Be a smart DBA
• Use profiles – 10 per account – keep them up-to-date – organize in 1 central account
• Familiarize yourself with updates – current O/S? uname –a – current Oracle version tool herald
222
Michael S. Abbey – Advanced DBA Best Practices
Arm yourself with the necessary backup to your request
223
Michael S. Abbey – Advanced DBA Best Practices
224
Working with OSS Profiles
Organize by client and O/S
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS Be specific Problem with rman
Oracle error when doing a list backup command
Unique constraint violation when trying to list backup
225
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS TAR intake
• Keyword scan of free form entry • Routed to subject experts • Response time – 30-60 minutes – Severity 1 next to immediate
• Preferred contact method is email – ensure OSS has correct address – check incoming mail regularily
226
Michael S. Abbey – Advanced DBA Best Practices
227
Working with OSS Tools of the trade
lert log
init.ora
ode
racle error(s)
xplain plan
race file(s)
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS Best practices
• Have realistic expectations • Do your homework first (MetaLink) • Correct contact information
• Be prepared
• Use email—best communications performance • Respond to requests • No games
228
Michael S. Abbey – Advanced DBA Best Practices
Working with OSS
229
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge
Best practices NOW will payoff down the road
230
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Admin scripts
• ?/rdbms/admin • Familiarize yourself with the contents of this directory in your spare time • Two part naming convention – dbmsabcd.sql – prvtabcd.plb
231
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Hard-core education Just what do you think you're doing Dave?
232
Michael S. Abbey – Advanced DBA Best Practices
233
Hodge-podge Smart professionals
$$
Michael S. Abbey – Advanced DBA Best Practices
234
Hodge-podge In a bind
• Work with your developers to bind everything – perl – SQL*Plus – AOWBI
• Re-usable SQL • Computation of hash value very restrictive
select from where group
sql_text,count(*) v$sqlarea instr(sql_text,'":SY"') > 0 by sql_text;
select count(*) from v$sqlarea; select count(*) from v$sqlarea where sql_text like '%'||''''||'%';
Michael S. Abbey – Advanced DBA Best Practices
235
Hodge-podge Index foreign keys
• Development requires primary/foreign key relationships to protect data integrity • Primary key constraints using index • Never think of or take the time to oops! index foreign key columns • Locks on deleting from parent
primary key index foreign key INDEX
HUGE locking problems
col table_name form a20 head Table col column_name form a30 heading 'Missing index for FK'
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Finding non-indexed foreign keys select dc.table_name,dcc.column_name from sys.dba_constraints dc, sys.dba_cons_columns dcc where dc.constraint_type = 'R' and dc.constraint_name = dcc.constraint_name and dc.owner not in ('SYS','SYSTEM') and not exists (select ' ' from sys.dba_indexes di,sys.dba_ind_columns dic where di.index_name = dic.index_name and di.table_name = dc.table_name and dic.column_name = dcc.column_name);
236
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Manage RI before it manages you • Frustrating venture • Endless Oracle errors • constraint_type – C for check • Protection as well as integrity – R for reference – P for primary
• r_constraint_name • r_owner
237
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Manage RI before it manages you
SYS_ SYS_
alter table … add constraint …_pk primary key (…) using index storage (initial N next N pctincrease 0) tablespace …; alter table … add constraint …_fk foreign key (…) references … (…);
Thou shalt name your constraints Thou shalt alter table … add constraint
238
Michael S. Abbey – Advanced DBA Best Practices
239
Hodge-podge
Saving foreign key constraints – part 1 select 'alter table '||uc1.owner||'.'||uc1.table_name|| chr(10)||' drop constraint '|| uc1.constraint_name||';' from user_constraints uc1, -- foreign key constraint def user_constraints uc2, -- primary key constraint def user_cons_columns ucc1, -- columns in foreign key user_cons_columns ucc2 -- columns in primary key where uc1.r_constraint_name = upper(uc2.constraint_name) and ucc2.constraint_name = upper(uc2.constraint_name) and ucc1.constraint_name = uc1.constraint_name and uc2.table_name in ({list_of_tables});
DBAs worth their weight in gold
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge
Saving foreign key constraints – part 2 select 'alter table '||uc1.owner||'.'||uc1.table_name|| ' add constraint '||chr(10)||' '|| uc1.constraint_name||chr(10)||' foreign key ('|| ucc1.column_name||')'|| ' references '||uc2.owner||'.'||uc2.table_name|| ' ('||ucc2.column_name||');' from user_constraints uc1, -- foreign key constraint def user_constraints uc2, -- primary key constraint def user_cons_columns ucc1, -- columns in foreign key user_cons_columns ucc2 -- columns in primary key where uc1.r_constraint_name = upper(uc2.constraint_name) and ucc2.constraint_name = upper(uc2.constraint_name) and ucc1.constraint_name = uc1.constraint_name and uc1.table_name in ({list_of_tables});
240
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge
Extract source code – procedures / functions
• Be prepared / be smart • Quicker and more useful than alter procedure GEO_MAINT compile; sho errors package geo_maint set lines 9999 trimsp on pages 0 echo off feed off ver off sqlbl on spool pf select decode(line,1,'create or replace '||text,text) from user_source where name = upper('&1') order by line; prompt / spool off
241
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Extract source code - packages set lines 9999 trimsp on pages 0 echo off feed off ver off sqlbl on spool pkg select decode(line,1,'create or replace '||text,text) from user_source where name = upper('&1') and type = 'PACKAGE' order by line; prompt / prompt select decode(line,1,'create or replace '||text,text) from user_source where name = upper('&1') and type = 'PACKAGE BODY' order by line; prompt / spool off
242
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Global partitioned indexes
• Do not have the same partition implementation as their data • Boundaries specified on creation • Must be rebuilt from scratch when performing partition maintenance – adding or dropping – splitting or merging
243
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Avoid global index rebuilds
• List or range partitioning • Painstakingly adopted naming convention • Date or surrogate key based approach easiest to work with • Ensure there are no rows in last 2 partitions
244
Michael S. Abbey – Advanced DBA Best Practices
245
Hodge-podge Avoid global index rebuilds
• Partition naming - table – table name + highest possible date – e.g., SUBS_20020831 • format of date mask important • allows for pseudo-sequential queries
it's 3AM – your planning will pay off
• Partition naming – index – table name + index name + U/N/F identifier – unique / non-unique / function-based
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Avoid global index rebuilds set pages 0 lines 999 trimsp on ver off echo off col a new_value mxname select from where and
max(partition_name) a dba_tab_partitions table_owner = upper('&1') table_name = upper('&2');
select 'Partitioned table '||upper('&1')||'.'|| upper('&2')||' has '||count(*)|| ' rows in last partition ('||'&mxname'||')' from &1..&2 partition (&mxname) having count(*) = 0; set lines 75
246
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Avoid global index rebuilds set echo off feed off pages 0 select distinct '@lp_look '||table_owner|| ' '||table_name from sys.dba_tab_partitions order by table_owner,table_name; . . . SQL> @lp_look TERA WORK SQL> @lp_look TERA YIELD . . . Partitioned table TERA.WORK has 3 rows in last partition (TERA_WORK_P20030630) Partitioned table TERA.YIELD has 8 rows in last partition (TERA_YIELD_P20030831)
247
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Shutdown abort When running with or without media recovery enabled SQL> shutdown abort ORACLE instance shutdown. SQL> startup restrict . . . SQL> shutdown . . . ORACLE instance shutdown.
248
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge A word on OEM
249
Michael S. Abbey – Advanced DBA Best Practices
250
Hodge-podge Weaning yourself of Server Manager
• No more svrmgrl with release 9i • No more internal user • Done exclusively in SQL*Plus, unlike 8i with either/or • Close the one remaining inconsistency between the way these 2 tools behave • Ripple affect on backup scripts
9i
Michael S. Abbey – Advanced DBA Best Practices
Hodge-podge Ensuring tools behave exactly the same way SVRMGR> select count(*), cus_id 2> from reader 3> 4> group by cus_id 5> having count(*) > 1;
SQL> select count(*), cus_id 2> from reader 3> SQL>
• Server Manager tolerates blank lines, whereas SQL*Plus didn't until
251
Michael S. Abbey – Advanced DBA Best Practices
252
Hodge-podge Change in logon procedures
connect internal
8i
9i /nolog connect / as sysdba 10g
Michael S. Abbey – Advanced DBA Best Practices
253
Hodge-podge Online redo log maintenance
• To protect you from yourself, rename the members … Before … select group#,member from v$logfile order by group#,member GROUP# MEMBER ------ ----------------------1 /redo1/beg9/redo01a.log 1 /redo2/beg9/redo01b.log 2 /redo2/beg9/redo02a.log 2 /redo1/beg9/redo02b.log
After … select group#,member from v$logfile order by group#,member GROUP# MEMBER ------ ----------------------1 /redo1/beg9/log1_g1.log 1 /redo2/beg9/log2_g1.log 2 /redo2/beg9/log1_g2.log 2 /redo1/beg9/log2_g2.log
Michael S. Abbey – Advanced DBA Best Practices
254
The chicken or the egg?? practices allow your systems to
Better systems
=
users contribute to a smarter DBA
better.
users.
Michael S. Abbey – Advanced DBA Best Practices
DBA Best Practices
256