Friday Mar 14, 2014

Oracle Database Consolidation

ConsolidationIn the industry today engineers are trying to implement consolidation that minimizes idle resources and to lower costs.  This paper discusses some important items to consider  when implementing a consolidated or Exadata engineered Oracle database platform.  Some of the items may seem obvious or common sense yet on frequent assignment at customer sites are not considered and have become major issues to solve.  This paper is not intended to provide all consolidation issues but give highlight to some and more importantly to provoke a deeper consideration and conversation when designing a consolidated or engineered platform.

Download The Full PDF here: Database_Consolidation.pdf

Wednesday Nov 20, 2013

OEM12c Password Change for SYSMAN and Weblogic Administration Accounts

password_reset.jpgHaving recently created an OEM12c virtual box install for a proof of concept (POC) at a customer site, another opportunity arose to reuse the same virtual machine. Alas the SYSMAN and WEBLOGIC administration passwords were unknown and required to be reset before reuse. Initially it was considered that this would be an easy task and it referenced some Oracle metalink published notes to complete the operation. However these notes and other published material were found to be missing some of the more critical steps, they were found to be lacking also in clarity and detail leading to a complex and error prone process that had to be retried many times. The purpose of this document is to give a clear step by step process to an administrator in a similar position that covers all the steps and not just some of the steps in detail with examples to follow.

Download The Full PDF here: OEM12c_Reset_Admin_Passwords.pdf

Wednesday Apr 17, 2013

11gR2 Flashback Guaranteed Restore Point without enabling Flashback logging

Flashback PicturePlenty has been documented and published regarding flashback database since the first implementation in Oracle 10g. However little is documented regarding using flashback restore points in Oracle when the flashback logging (flashback off) is not enabled.

At a client site flashback logging was turned off as a feature due to performance implications, in 11gR2 it is a dynamic online feature that you can now turn flashback on and off without a database bounce. A situation arose and questions were asked regarding the use of guaranteed restore points and flashback logging for  testing purposes.

For reference this paper gives a short summary and example of the Oracle documentation:

Download The Full PDF here: 11gR2_Flashback_Guaranteed_Restore_Point_without_enabling_Flashback_logging.pdf

Friday Mar 15, 2013

Applying a Parallel Hint to SQL Code that cannot change

PerformanceLast night during investigation of a performance issue for a client it was found that due to significant growth in the database a select query was suddenly taking hours to run instead of minutes. Obvious things like statistics and the database were checked and it was clear to see the SQL code needed a review. However this code is from a vendor and cannot be changed so the client was interested in anything that could be done to assist them in the short term. This blog update covers the analysis and final answer which was to introduce the parallel hint into the SQL code with NO actual code change using the ‘ dbms_sqldiag’ package and ‘i_create_patch’ procedure.

The first answer and quickest to implement was to execute the SQL tuning advisor against the code and that prompted a couple of SQL profiles for answers. The most positive of these was a PX_PROFILE which would introduce parallelism of a DOP (48) into the query and although warning about resource increases it looked good enough to investigate further. To take a stage forward the query was tested using the PARALLEL hint to see how much benefit parallelism would introduce. It should be noted that care is required before jumping into parallelism as a solution. For example in Oracle RAC what is the value of ‘parallel_force_local’ and what is the available CPU, memory, ‘large_pool’ etc.

Developers executed the code from SQL developer using the PARALLEL hint as below to see the real effect of the parallelism suggestion.

Note this is not the actual code but just an example of using the PARALLEL hint.

Select /*+PARALLEL */ count(*) from test_table;

The test was very positive with execution now taking around four minutes to complete which was a large difference from over 2 hours previously. However examining the explain plan with the PARALLEL hint showed it was very different to that of the explain plan for the SQL profile produced by the SQL tuning advisor. This meant the SQL profile was not the answer as the requested approach was now to get the PARALLEL hint implemented as tested and proven and not the execution as the SQL profile suggested with the PX_PROFILE.

Easy, create a baseline was the next answer to adopt. Baselines are very easy to generate from the SQL cache linking the execution plan for the SQL with the hint to that of the SQL without the hint. References on how to do this are contained in the acknowledgements.

After the baseline was introduced the code was re-tested to ensure correct results. Although the baseline was enabled and accepted the original code refused to implement the new baseline. The execution was not going to parallel execution as desired. What was wrong?

Researching this further, the reason why the baseline was not being selected is that the PARALLEL hint does not make it into the plan hints of the baseline but is obviously essential to force the parallel plan and get the appropriate hash plan value. Again this is not going to be discussed further here as it is available in other blog entries elsewhere, see acknowledgements.

So a profile and a baseline were now ruled out as possible solutions. It was then that an alternative arose called SQL PATCH. There are some pre-requisites in what SQL PATCH will and won’t do as also referenced in the acknowledgements section. However for a straight /*+PARALLEL */ hint it worked a treat!

To add the parallel hint to the query with SQL PATCH:

DECLARE

Sqltext clob;

BEGIN

SELECT sql_text into sqltext FROM DBA_HIST_SQLTEXT WHERE sql_id =’9h8a1m1a4fwzc’;

DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH(Sql_text => sqltext, Hint_text=> ‘PARALLEL’,Name=>’PARALLEL Hint Patch’);

END;

/

NOTE: The SQL text was obtained from the view DBA_HIST_SQLTEXT with the sql_id as it was a large query.

After submitting the above and testing with explain plan and execution finally we have the PARALLEL hint being used as was desired without any code change. In the explain plan there is even a note showing the name as above which aids in identification later.

If required at a later stage to drop the above it can be implemented with the code as below:

BEGIN

     DBMS_SQLDIAG.DROP_SQL_PATCH(name=>’PARALLEL Hint Patch’);

END;

/

Acknowledgements:

The following blogs were referenced in this research piece:

1. http://metekarar.blogspot.com.au/2012/07/adding-hints-with-sql-patch.html

2. http://orastory.wordpress.com/2013/03/09/sql-patch-iii-plus-parallel-baselines/

3. http://orastory.wordpress.com/2011/10/13/no-need-to-change-source-code-hint-it-using-a-baseline/

4. http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.html

Friday Feb 01, 2013

How to setup a simple Oracle streams example: 1 table to another table in the same schema and database

Oracle Streams ImageWhilst recently on site with a client, it was requested to provide a simple Oracle streams demonstration with documentation that showed how to replicate a single table to another table within the same database and schema. 

The attached document gives the scripts and step by step workings for the example.  It is aimed for those new to Oracle streams and wanting a simple working example to follow.  It should be noted that if looking at replication technology it is recommended to look first at the product Oracle Golden Gate. 

For support of this and further implementation help please contact the author of the paper as required.  The aims of the supplied scripts are purely for demonstration and training purposes.

The views expressed on this blog are those of the author and do not necessarily reflect the views of Oracle.  Please download the full paper for a detailed discussion as well as some recovery tricks you may not have come across.

Download The Full PDF here: Oracle_Streams_Example.pdf

Wednesday Jan 30, 2013

Taking the Black Magic Out Of Oracle Database Recovery

Backup_Recovery_Image

Introduction:

One of the biggest issues seen today with an Oracle database recovery is human error. More damage is done by administrators executing commands without a true understanding or an appreciation of the real condition of the existing Oracle database. Since the release of 11g it is interesting to question, how many administrators are actually using the RMAN recovery advisor which is now a tool built into the database?

The aim of this attached paper is to show that database recovery is not black magic. If a deep breath is taken at the point of failure and time is taken to properly diagnose the real condition of the database, whilst forming a firm action plan from what is available, and then the recovery becomes a simple straight forward process. That is providing any required backups are actually available of course!

Experience has shown that the main issue at the time of any fault is an administrator that jumps straight to the recovery phase with little or no analysis of the actual situation, potentially causing a bad situation to suddenly become worse.

The views expressed on this blog are those of the author and do not necessarily reflect the views of Oracle.  Please download the full paper for a detailed discussion as well as some recovery tricks you may not have come across.

Download The Full PDF hereMagic_Backup_Recovery.pdf

What Is Baker's Byte?

Hi, I’m Andy Baker and this blog is my attempt to give the Oracle production database administrators view of life. I hope to provide some interesting updates covering general items of interest, the issues I face as well as some I have previously solved. This is all based on my experiences at real client sites and will cover the Oracle RDBMS, RAC, Exadata and general administration Oracle DBA tasks as well as tips and tricks.

Although I am currently based in Oracle Consulting Services (OCS) my previous experience within Oracle has also been with Oracle Support Services (OSS) as a senior backup and recovery engineer. I have been working with Oracle databases for over 18 years and have experience in a wide range of industries and roles. This covers Banking, Oil and Gas, Insurance and Telecoms for many major global organizations. I have worked in software development but mainly focus in production support dealing with management and staff at varying levels, whilst having to meet rigorous performance and demanding time schedules.

About

About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today