Using LogMiner to Analyze Redo Log Files
Before the fact, you can set up auditing to record changes. With auditing, you can record actions in a database table as they happen and then query the table any time in the future. For more information on auditing, please refer to the following documentation:
Question: We are using RAC for High Availability. What is better two nodes or three nodes? It's been suggested that "the more nodes, the better". What is the optimal number of RAC nodes?
Answer: First, remember that RAC only protects against server failure, and you must still mirror the disk and have network redundancy for guaranteed 100% availability. In my experience, RAC is not as good as some other options for scalability (i.e. the "scale up" approach), and the most common use of RAC is for HA, and covering server failover.
In deciding about the number of nodes for HA, just multiply the Mean Time Between Failure's (MTBF) for each node together and get an overall probability of failure. It's all about covering the probability of server failure. When used exclusively for HA, two-node RAC is ideal, especially when the nodes are geographically distributed and connected via super-fast dark fiber networks.
Oracle ACE Andy Kerber notes that two node clusters are ideal under some circumstances:
"Well, 3 nodes can be better than two if your workload is fully scalable, and there is no resource contention among the three nodes. I would venture to say that the majority of RAC systems are 2 node clusters, and are set up that way as part of a HA system instead of a workload scaling system."
Oracle Certified Master Steve Karam notes the considerations when choosing the optimal number of RAC nodes for failover:
"Three nodes are 'better' because if one crashes, you won't be failing the entirety of your load over to a single solitary server. In a three node RAC, it allows two nodes to fail before the situation is 'critical.'
Uptime is a function of many unknown variables (power to the server room, stable memory consumption, hardware stability, etc) over time. One server = a boundary condition that limits your chances of uptime by introducing a constant: one unknown variable being negative at any time = total breakdown.More servers = wider boundaries = less emphasis on the unknown variables that can ultimately cause downtime. Now your uptime is a function of many unknown variables per server over time. More servers = less chance of total downtime.
At the same time, saying that more nodes is always better is false. More nodes are better until you get close to the limits imposed by your back-end cluster interconnect. Thus the boundary condition concept is introduced to a new boundary condition: the number of servers you can have before your performance tanks, which is a different equation entirely.
Interestingly enough, you could use the boundary condition argument to speak the case for SSD (in my hastily assembled opinion). If performance is based upon the ease with which data can travel from the database to the end user; the unknown variables are the amount of data (block gets), the location of data (RAM or disk), concurrency of access (latches, locks), and other obstacles (misc. waits).The most common tuning practice is to reduce the number of block gets, thereby limiting the first unknown. By using SSD we can moot the second unknown, and always ensure our data comes from RAM. RAC will add a new variable to the 'location' unknown (RAM, remote RAM, disk), but it limits the third unknown (concurrency) since it will be spread across multiple machines.
It can also limit other obstacles (waits) if the waits are node dependent (for instance, network waits would not be limited or removed by going RAC, but px waits could). By this logic, you could say the best system imaginable would be a RAC cluster powered by SSD with a tiny buffer cache on each node, running well tuned SQL.
• Well tuned SQL limits the ill effects of the 'block gets' unknown
• SSD with a tiny buffer cache negates the location unknown, by nulling disk and limiting remote RAM, leaving SSD as the main location of data. This also reduces the ill effects of the cluster interconnect bandwidth/latency boundary condition
• Multiple nodes limit the effects of the concurrency unknown (though the databases = 1 boundary means hot blocks are still possible)
Thus your tuning knobs become the simple concepts of the number of nodes you have (based on concurrency) and the number of block gets you request (based on access paths/data requirements).
Simply put, I would say that our bottlenecks (single points of contention or failure) are math's boundary conditions. They are the limits within which we must operate. Except that as DBAs, we get to do something mathematicians, physicists, etc. don't get to do: change the boundaries."
See my related notes on determining RAC node optimization:
2008 Market Survey of SSD vendors for Oracle:There are many vendors who offer rack-mount solid-state disk that work with Oracle databases, and the competitive market ensures that product offerings will continuously improve while prices fall. SearchStorage notes that SSD is will soon replace platter disks and that hundreds of SSD vendors may enter the market:
As of June 2008, many of the major hardware vendors (including Sun and EMC) are replacing slow disks with RAM-based disks, and Sun announced that all of their large servers will offer SSD. As of June 2008, here are the major SSD vendors for Oracle databases (vendors are listed alphabetically): 2008 rack mount SSD Performance StatisticsSearchStorage has done a comprehensive survey of rack mount SSD vendors, and lists these SSD rack mount vendors, with this showing the fastest rack-mount SSD devices (as of May 15, 2008):
Choosing the right SSD for OracleWhen evaluating SSD for Oracle databases you need to consider performance (throughput and response time), reliability (Mean Time Between failures) and TCO (total cost of ownership). Most SSD vendors will provide a test RAM disk array for benchmark testing so that you can choose the vendor who offers the best price/performance ratio |
Oradebug requires the SYSDBA level privileges account ie) SYS to run from within a new SQL*Plus session.
First we will set our oradebug to trace the current user session with the setmypid command.
SQL> oradebug setmypid
Statement processed.
SQL>
Now lets use an ALTER SESSION command to tidy up the name for the oradebug file to make things easier when we need to locate the trace file.
SQL> alter session set tracefile_identifier='mytrace';
Session altered.
Now, let’s check where our trace file is stored with the following oradebug command:
SQL> oradebug tracefile_name
c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1348_mytrace.trc
By default, oradebug writes out the trace file to the USER_DUMP_DESTINATION directory.
Now lets setup our SQL trace for our current user session and generate some database activity to capture with oradebug.
Oradebug uses events to capture various database events for Oracle. For a SQL trace, the specific event is 10046. In addition to these events for capturing low level Oracle database activity, oradebug also has many levels available for setting the trace.
Some of these trace levels with oradebug include:
Level 1 – contains the basic level of trace information. For example, this trace level will display the bind variables in PL/SQL and SQL statements.
Level 8 - provides the trace details from Level 1 plus the wait events for elapsed times that are more than current CPU timings.
Level 12- adds in all the previous trace level information in addition to all wait event information.
Since we are using oradebug in a test environment, let’s enable a full trace to capture as much detail as possible for SQL activity with our user session. We also want to set the trace file to be unlimited in size so no errors occur during the tracing activity.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
Now that we have the trace enabled for SQL statements with oradebug, lets generate some activity.
SQL> create table t1 (a number, b varchar2(100));
Table created.
SQL> insert into t1
2 values (1,'A');
1 row created.
SQL> commit;
Commit complete.
2 values (2,'B');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t1
2 values (3,'C');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t1
2 values (3,'C');
1 row created.
Now let’s end our trace session for oradebug and review the new trace file.
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1348_mytrace.trc
We can format our trace file with tkprof or review the raw data generated by oradebug in the trace file. First, we will look at the raw trace dump that we generated with oradebug and then we will conclude our brief visit to oradebug with a tkprof formatted trace output.
Our raw dump in the trace file reviews many low level details for Oracle as shown below.
*** TRACE DUMP CONTINUED FROM FILE ***
Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1348_mytrace.trc
Mon May 19 15:12:22 2008
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:856M/2037M, Ph+PgF:2240M/3934M, VA:979M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 26
Windows thread id: 1348, image: ORACLE.EXE (SHAD)
*** 2008-05-19 15:12:22.218
*** SERVICE NAME:(SYS$USERS) 2008-05-19 15:12:22.187
*** SESSION ID:(135.279) 2008-05-19 15:12:22.187
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=18507445018
*** 2008-05-19 15:13:20.187
WAIT #0: nam='SQL*Net message from client' ela= 57993544 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=18565462754
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #4 len=42 dep=0 uid=0 oct=1 lid=0 tim=18565469559 hv=4239432121 ad='40adba98'
create table t1(a number, b varchar2(100))
END OF STMT
PARSE #4:c=0,e=3292,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=18565469553
BINDS #4:
=====================
PARSING IN CURSOR #3 len=51 dep=1 uid=0 oct=3 lid=0 tim=18565479916 hv=1523794037 ad='46295e4c'
select audit$,options from procedure$ where obj#=:1
END OF STMT
PARSE #3:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565479911
BINDS #3:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0a2dbffc bln=22 avl=03 flg=05
value=10200
EXEC #3:c=15625,e=11817,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565499761
FETCH #3:c=0,e=59,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=18565501551
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY INDEX ROWID PROCEDURE$ (cr=3 pr=0 pw=0 time=64 us)'
STAT #3 id=2 cnt=1 pid=1 pos=1 obj=109 op='INDEX UNIQUE SCAN I_PROCEDURE1 (cr=2 pr=0 pw=0 time=32 us)'
=====================
PARSING IN CURSOR #1 len=179 dep=1 uid=0 oct=3 lid=0 tim=18565507100 hv=2812844157 ad='462ee788'
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
END OF STMT
PARSE #1:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565507094
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0a2dc1d8 bln=22 avl=03 flg=05
value=10200
EXEC #1:c=15625,e=13251,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565529339
FETCH #1:c=0,e=545,p=0,cr=20,cu=0,mis=0,r=1,dep=1,og=4,tim=18565531768
FETCH #1:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565533741
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565535668
FETCH #1:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565537698
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565539607
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565541509
FETCH #1:c=0,e=22,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=18565543419
FETCH #1:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18565545385
STAT #1 id=1 cnt=7 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=20 pr=0 pw=0 time=654 us)'
STAT #1 id=2 cnt=7 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=20 pr=0 pw=0 time=495 us)'
STAT #1 id=3 cnt=7 pid=2 pos=1 obj=92 op='TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=0 pw=0 time=136 us)'
STAT #1 id=4 cnt=7 pid=3 pos=1 obj=122 op='INDEX RANGE SCAN I_DEPENDENCY1 (cr=2 pr=0 pw=0 time=62 us)'
STAT #1 id=5 cnt=7 pid=2 pos=2 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=16 pr=0 pw=0 time=257 us)'
STAT #1 id=6 cnt=7 pid=5 pos=1 obj=36 op='INDEX UNIQUE SCAN I_OBJ1 (cr=9 pr=0 pw=0 time=116 us)'
In the header section of the trace file, oradebug has provided us with useful configuration information on the system and database environment. Next, we see the dump output for wait events and SQL statements for our Oracle 10g database instance.
Now we will conclude our brief visit to the world of oradebug with a brief example of using tkprof with the trace file for database tuning analysis.
To format our trace file generated from our recent oradebug session we need to execute the tkprof command from the trace file default directory as follows.
C:\oradebug>cd c:\oracle\product\10.2.0\admin\orcl\udump
C:\oracle\product\10.2.0\admin\orcl\udump>
tkprof orcl_ora_1348_mytrace.trc trace_sql.txt explain=system/oracle
sys=no waits=yes
TKPROF: Release 10.2.0.1.0 - Production on Mon May 19 15:24:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Now, let’s review the contents of our newly formatted trace file:
TKPROF: Release 10.2.0.1.0 - Production on Mon May 19 15:24:23 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: orcl_ora_1348_mytrace.trc
Sort options: default
***********************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
***********************************************************************
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (XDB) (recursive depth: 1)
**********************************************************************
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38 (XDB) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 NESTED LOOPS
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'USER$'
(CLUSTER)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'I_USER1' (INDEX
(UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (CLUSTER) OF 'TS$' (CLUSTER)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'I_TS#' (INDEX
SELECT /*+ ALL_ROWS */ COUNT(*)
FROM
ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND
(POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION =
:B1
call count cpu elapsed disk query current rows
Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=3 pr=0 pw=0 time=148 us)
1 TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=43 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 FILTER
1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'DUAL' (TABLE)
***********************************************************************
Misses in library cache during parse: 6
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- --------SQL*Net message to client 28 0.00 0.00
SQL*Net message from client 28 178.60 331.48
SQL*Net break/reset to client 2 0.00 0.00
db file sequential read 2 0.00 0.00
reliable message 1 0.00 0.00
enq: RO - fast object reuse 1 0.00 0.00
rdbms ipc reply 1 0.00 0.00
log file sync 6 0.00 0.00
As you can see the oradebug utility provides a wealthy of information and serves as a goldmine of resources for the Oracle expert practitioner who must resolve the toughest Oracle problems. It will allow you to develop insight into the Oracle database engine on how the nuts and bolts of the Oracle database really works.
Oracle’s 11g database has received some less-than-stellar press recently — The Pythian Group accused Oracle of releasing misleading adoption rates, and some Eye on Oracle readers don’t see many compelling reasons to upgrade.
The new database does, however, have plenty of new tools. And while they may be costly and time-consuming at first, many users are finding these features very valuable.
So, you ask…what are they?
In no particular order, here is my list of the top five features in 11g that make your upgrade worthwhile, along with some resources to help get you started.
1. Database Replay Tool: This tool provides users with a new way to test database changes and “works like a DVR inside the database,” according to Oracle Corp. in its step-by-step instructions. Before making a change (such as debugging, applying a patch or switching platforms), a user can use Database Replay to capture a database workload and replay it in a test system. Oracle ACE Lutz Hartmann calls it an 11g highlight, and Tim from ORACLE-BASE also provides a thorough explanation on how to use it.
2. SQL Performance Analyzer: This new feature shows the user how a database change, such as an upgrade, would affect SQL performance and allows one to compare different results. Check out SearchOracle.com’s chapter download from the book “Oracle Database 11g SQL” to learn about the optimizer in more detail. Also, you can find some more examples in Tyler Muth’s blog and Oracle’s Database Performance Tuning Guide.
3. Real Application Testing: Real Application testing is the product that combines the Database Replay Tool and SQL Performance Analyzer. The best part of this feature is the ability to detect potential problems with database changes, and see how these changes will affect performance, before actually putting them into production. Read Oracle’s description of Real Application Testing and SearchOracle.com’s conversation with Oracle executives about the product.
4. Data Recovery Advisor: This is one of the new backup and recovery features of 11g. The Data Recovery Advisor automatically diagnoses a problem (such as a physical corruption or datafile inconsistency) and gives repair advice, which the DBA can then choose whether or not to execute. Both Oracle and DBA Ben Prusinski give thorough descriptions of this and other 11g backup and recovery tools.
5. Oracle Data Guard: This tool protects Oracle data by creating and maintaining standby databases that are synchronized with the original production database. If the production database goes down, the standby database can prevent data loss by taking over its role. Data Guard is not a new feature but is certainly enhanced in 11g. This includes failover enhancements, an Active Data Guard option, enhanced security and more — all of which can be read about here.
What are your favorite tools? If you’ve upgraded, what have your experience been like with these tools? If you haven’t upgraded, are these features enough of a reason to do so?
 | ||
Before attempting such recovery, I always backup my database files and online redo logs files. That way, if I screw something up, I can go back to the point right before I attempted recovery and try again. I also take a backup after recovery is complete since I wont want to go through this process again should another failure occur.
“The new default value of METHOD_OPT with 10g is ‘FOR ALL COLUMNS SIZE AUTO’. This basically means that Oracle will automatically decide for us which columns need histograms and which columns don’t based on what it considers to be the distribution of data within a column and based on the “workload” associated with the table (basically are there any SQL statements running in the database referencing columns which might need histograms for those statements to be costed correctly). . .
However in environments with many tables and columns (potentially many thousands) with many users executing many different SQL statements, the ramifications of potentially suddenly having thousands of additional histograms can be disastrous.”
In sum, 10g SQL optimization involves a perpetual trade-off between sub-optimal plans and the un-needed overhead of superfluous or un-referenced histograms.
An exploration of "intelligent histogram creation", is needed, a method that uses AWR to correlate the SQL to the objects, avoiding histograms that are never used, and develop a method to only create histograms that "make a difference".
That's the basics anyway! There are variations depending on the exact command being given.
TABLE OF CONTENTS
Resources for working with errors ORA-00000 - ORA-01999 ORA-02000 - ORA-03999 ORA-04000 - ORA-05999 ORA-06000 - ORA-07999 ORA-08000 - ORA-11999 ORA-12000 - ORA-13999 ORA-14000 - ORA-23999 ORA-24000 - ORA-25999 ORA-26000 - ORA-27999 ORA-28000 - ORA-30100 Other error messages For more information
Oracle9i Database Error Messages
Release 2 (9.2)
Part Number A96525-01
http://download-west.oracle.com/docs/cd/B10501_01/
server.920/a96525/toc.htm
