Skip navigation.

Feed aggregator

Watch: Hadoop vs. Oracle Exadata

Pythian Group - Mon, 2014-11-24 09:05

Every data platform has its value, and deciding which one will work best for your big data objectives can be tricky—Alex Gorbachev, Oracle ACE Director, Cloudera Champion of Big Data, and Chief Technology Officer at Pythian, has recorded a series of videos comparing the various big data platforms and presents use cases to help you identify which ones will best suit your needs.

“Obviously there’s a big difference in cost,” Alex explains. “However, you would require significantly more engineering effort invested in Hadoop—so it’s a matter of scale when a solution like Hadoop becomes cost efficient.” Learn more key differentiators by watching Alex’s video Hadoop vs. Oracle Exadata.

Note: You may recognize this series, which was originally filmed back in 2013. After receiving feedback from our viewers that the content was great, but the video and sound quality were poor, we listened and re-shot the series.

Categories: DBA Blogs

Innovation in Managing the Chaos of Everyday Project Management

WebCenter Team - Mon, 2014-11-24 08:50
Fishbowl

Invitation

Innovation in Managing the Chaos
of Everyday Project Management

Oracle Media Network

Stay Connected

OPN on PartnerCast

Oracle and Fishbowl Solutions Present:
A Breakthrough in Enterprise-Wide Project Management

Controlled chaos - this phrase sums up most enterprise-wide projects as workers, documents, and materials move from task to task. To effectively manage this chaos, project-centric organizations need to consider a new set of tools to allow for speedy access to all project assets and to ensure accurate and up-to-date information is provided to the entire project team.

Live Webcast

4th December, 2014

02:00 PM EST

Duration: 60 minutes


Register Now

Fishbowl Solutions and Oracle would like to invite you to a webinar on an exciting new solution for enterprise project management.

This solution transforms how project-based tools like Oracle Primavera, and project assets, such as documents and diagrams, are accessed and shared.


With this solution:

  • Project teams will have access to the most accurate and up to date project assets based on their role within a specific project;
  • Through a single dashboard, project managers will gain new real-time insight to the overall status of even the most complex projects;
  • The new mobile workforce will now have direct access to the same insight and project assets through an intuitive mobile application;

With this real-time insight and enhanced information sharing and access, this solution can help project teams increase the ability to deliver on time and on budget.

Fishbowl's Cole Orndorff, who has 10+ years in the engineering and construction industry, will keynote and share how a mobile-ready portal can integrate project information from Oracle Primavera and other sources to serve information up to users in a personalized, intuitive user experience.

Please join us on Thursday, December 4th at 2:00 pm EST to better understand this new and exciting solution.

 Contact Us

 Get Resources

opncomms_ww@oracle.com

OPN Website

Oracle Partner Store

OPN Information Center

OPN News Blog

Market Your Offerings

facebook twitter linkedin

Copyright © 2014, Oracle and/or its affiliates. All rights reserved.

Oracle Corporation - Worldwide Headquarters
500 Oracle Parkway, OPL - E-mail Services
Redwood Shores, CA 94065, United States

Contact Us | Legal Notices and Terms of Use | Privacy Statement

Create or update your profile to receive customized e-mail about Oracle products and services.

You are receiving this communication because of your current Oracle PartnerNetwork (OPN) membership agreement. General Marketing e-mail opt-out preferences may have been overridden to ensure that you receive this program information. If you are a designated OPN Administrator you may not opt out of receiving communications from Oracle PartnerNetwork; please refer to your OPN agreement for additional information.

You can opt-out of general Oracle Marketing e-mails at any time. Please note that opting-out of Marketing communications does not affect your receipt of important business communications related to your current relationship with Oracle such as Security Updates, Event Registration notices, Account Management, and Support/Service communications.


What Is Oracle 12 Unified Auditing? The View UNIFIED_AUDIT_TRAIL with 94 Columns

What is Oracle 12c Unified Auditing? The short answer is the view UNIFED_AUDIT_TRAIL. This view consolidates all logging and auditing information into a single source. Regardless of using either Mixed Mode or Pure Unified Auditing, the SYS.UNIFIED_AUDIT_TRAIL can be used. 

The key column in SYS.UNIFIED_AUDIT_TRAIL is AUDIT_TYPE.  This column shows from which Oracle component the log data originated -

SYS.UNIFIED_AUDIT_TRAIL Component Sources

Column AUDIT_TYPE Value

Description

Number of Columns in Table

Standard

Standard auditing including SYS audit records

44

XS

Real Application Security (RAS)and RAS auditing

17

Label Security

Oracle Label Security

14

Datapump

Oracle Data Pump

2

FineGrainedAudit

Fine grained audit(FGA)

1

Database Vault

Data Vault(DV)

10

RMAN_AUDIT

Oracle RMAN

5

Direct path API

SQL*Loader Direct Load

1

 

Total

94

If you have questions, please contact us at mailto:info@integrigy.com

Reference Tags: AuditingOracle Database
Categories: APPS Blogs, Security Blogs

Reminder to myself: turn off felix service urlhandlers in combined BPM & OSB12c installation

Darwin IT - Mon, 2014-11-24 05:12
Last week I started with creating a few OSB services for my current project, which is in fact a BPM12c project that needs to be interated with database services on an AS400, thus DB2. Firstly I found that when I tried to deploy on a standalone wls domain (created with the qs_config script), it lacks an OSB installation. Whereas the integrated weblogic default domain has one.

But when I try to deploy to a pretty simple project I ran into the fault 'The WSDL is not semantically valid: Failed to read wsdl file from url due to -- java.net.MalformedURLException: Unknown protocol: servicebus.'

I even tried to do an import of a configuration.jar into the sbconsole, but same error here.

Frustration all over the place: how hard can it be, beïng quite an experienced osb developer on 11g?

Luckily I wasn't the only frustrated chap in the field: Lucas Jellema already ran into it and found a solution, where he credited Daniel Dias from Middleware by Link Consulting.

Generating a Calendar View of Employee Absence

Duncan Davies - Mon, 2014-11-24 05:00

This blog post has been kindly contributed by Richard Yip. Richard is a PeopleSoft HCM & Payroll Technical Consultant at Santander Bank here in the UK. He is a long-term PeopleSoft techie and a regular at the UKOUG conferences.

We have posted two guest posts from Richard already, they can be found here:

Generating a custom Org Chart in PeopleSoft

Interacting client-side JavaScript with server-side PeopleCode

Introduction

In PeopleSoft HCM 9.0, an employee’s absence history is displayed in the usual tabular format (a grid with rows and columns). However, we felt the presentation was a bit uninspiring, and needed a better look to it. So we custom built a transaction whereby absences are displayed on a calendar used in both employee and manager self-service transactions.

[Duncan: this is actually a customisation that has been repeated in one shape or form by a number of customers, however I’m not aware of anyone else openly sharing the code behind their calendar. I’m very grateful to Richard’s generosity in sharing this and hope that others find it useful.

Richard’s code was written on the Oracle Database, however it could be translated to work in a similar manner on SQL Server.

I particularly like Richard’s clever trick of storing SQL Objects in HTML (as storing SQL in SQL Objects messes up the formatting).]

This is the end result:

calendar

Absence and other data comes from the following PeopleSoft tables:

  • GP_ABS_EVENT,
  • GP_ABS_EVT_JR,
  • GPGB_ABS_CODE,
  • EMPLOYEES

I will take you through the steps involved. If you want to skip this, the full listing is enclosed at the end.

3.    SQL 3.1.      Generating the calendar

This is part of the WITH clause. The %Bind(2) variable comes from user input on the page. This uses the “connect by level” as rows generator, depending on the number of days in a year.


WITH calendar AS
select ROWNUM ,
to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM – 1 daily ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'DAY') day ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'D') dow ,
to_number(to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'DD')) dom ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'WW') week ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'MM') mm ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'Month') month ,
to_char(to_date('01-jan-'|| '%Bind(:2)' ,'dd-mon-yyyy') + ROWNUM - 1 ,'YYYY') year
FROM   dual
CONNECT BY LEVEL <=(
SELECT
/* test for leap year */
decode(to_number(to_char(last_day(to_date('01-feb-'|| '%Bind(:2)' ,'DD-mon-yyyy')) ,'DD')) ,
29 ,366 ,365)
FROM   dual ))

3.2.      Extracting absences from the PeopleSoft absence tables

This is also part of the WITH clause. %Bind(:1) comes from the search page. The oracle hints stop parallel execution as these tables are partitioned at our site, and lead to better performance (as observed from  v$active_session_history)


abs_hist AS
SELECT
/*+ no_parellel(a) no_parallel(b) no_parallel(c) */
a.emplid,
a.bgn_dt,
a.end_dt,
a.gpgb_absence_type,
a.gpgb_absence_code,
c.descr
FROM   ps_gpgb_abs_evt_jr a ,
ps_gp_abs_event b,
ps_gpgb_abs_code c
WHERE  a.emplid = '%Bind(:1)'
AND    a.emplid = b.emplid
AND    a.empl_rcd = b.empl_rcd
AND    a.pin_take_num = b.pin_take_num
AND    a.bgn_dt = b.bgn_dt
AND    a.end_dt = b.end_dt
AND    a.gpgb_absence_type = c.gpgb_absence_type
AND    a.gpgb_absence_code = c.gpgb_absence_code
AND    b.voided_ind = 'N'
AND    b.bgn_dt <= to_date('3112%Bind(:2)', 'ddmmyyyy')
AND    b.end_dt >= to_date('0101%Bind(:2)','ddmmyyyy')

3.3.      Joining steps 1 and 2

The lines from 16 to 28 play an important part later.

SELECT mm,
Lead (mm) over(ORDER BY mm, dom) NEXT_MM,
dom,
dow,
Ltrim (Rtrim (day, ' '), ' ') DAY,
>Coalesce ((SELECT gpgb_absence_type
FROM   abs_hist
WHERE  daily BETWEEN bgn_dt AND end_dt), ' ') TYPE
/*abs_hist from 2*/
,
Coalesce((SELECT descr
FROM   abs_hist
WHERE  daily BETWEEN bgn_dt AND end_dt), ' ') descr
/*abs_hist from 2*/
-- the rest of columns are for html purpose
, '<table style="width: 100%" class="table_heading"> <tr>' first_row,
'</tr> </table> </td> </tr> <tr>' break_on_month_group,
'</tr> </table> </td>' break_on_month,
'(''04'',''07'',''10'')' month_group_value,
'<td valign="top"> <table style="width: 100%" class="sub_tab_heading" border="1"> <tr> <td colspan="7" class="month_heading"> ' || month || '</td> </tr> <tr> <td class="day_heading">Mon</td> <td class="day_heading">Tue</td> <td class="day_heading">Wed</td> <td class="day_heading">Thu</td> <td class="day_heading">Fri</td> <td class="day_heading">Sat</td> <td class="day_heading">Sun</td> </tr> <tr>' month_cell,
'<td class="default_cell">' normal_cell,
'<td></td>' blank_cell1,
'<td></td> <td></td>' blank_cell2,
'<td></td> <td></td> <td></td>' blank_cell3,
'<td></td> <td></td> <td></td> <td></td>' blank_cell4,
'<td></td> <td></td> <td></td> <td></td> <td></td>' blank_cell5,
'<td></td> <td></td> <td></td> <td></td> <td></td> <td></td>' blank_cell6,
'<td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td>' blank_cell7
FROM   calendar /*from step 1*/

3.4.      Adding in CSS and applying logic

Now we add Styling and some logic to the results from step 3.3 to produce the desired html for the page. The 1st row is when rownum =1 and last row is when NEXT_MM is null. DOW stands for day of week and DOM stands for day of month.

</pre>
SELECT
CASE
WHEN ROWNUM = 1 THEN '<html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Mon</title> <style type="text/css">
.month_heading { color: #FFFFFF; background-color: red; font-weight:bold; }
.day_heading { color: black; background-color: #C0C0C0; font-weight:bold; font-family:Arial,sans-serif;font-size:12pt; }
.table_heading { border: 0px solid #000000; }
.sub_tab_heading { border-style: solid; border-width: 1px; border-collapse:collapse; }
.style_mat { font-family:Arial,sans-serif;font-size:10pt; background-color: purple; color: white; font-style: italic; font-weight:bold; }
.style_sck { font-family:Arial,sans-serif;font-size:10pt; background-color: yellow; color: black; font-style: italic; font-weight: bold; width: 40px; }
.style_flu  { font-family:Arial,sans-serif;font-size:10pt; background-color: red; color: black; font-style: italic; font-weight: bold; width: 40px; }
.style_oth_sck { background-color: orange;  color: #FFFFFF; font-weight:bold; }
.default_cell { width: 40px;  font-family:Arial,sans-serif;font-size:10pt; } </style> </head> <body>' || '<table width="500px"> <tr><td class="style_sck" style="width:20px"></td> <td>Sickness</td> <td class="style_mat" style="width:20px"></td> <td>Maternity</td> <td class="style_flu" style="width:20px"></td> <td>Flu Pandemic</td> <td class="style_oth_sck" style="width:20px"></td> <td>Other</td> </tr> <tr><td colspan="8" style="font-family: Arial,sans-serif; font-size: 10pt; font-style: italic;">**Place mouse over highlighted day to see details</tr> </table>'
|| first_row
|| month_cell
||
CASE
WHEN dow = '1' THEN normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '2' THEN blank_cell1
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '3' THEN blank_cell2
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '4' THEN blank_cell3
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '5' THEN blank_cell4
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '6' THEN blank_cell5
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE blank_cell6
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
ELSE
CASE
WHEN dom = '1' THEN
CASE
WHEN dow = '1' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '2' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell1
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell1
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '3' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell2
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell2
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '4' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell3
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell3
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '5' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell4
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell4
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '6' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell5
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell5
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
WHEN dow = '7' THEN
CASE
WHEN mm IN ('05',
'09') THEN break_on_month_group
|| month_cell
|| blank_cell6
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE break_on_month
|| month_cell
|| blank_cell6
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td></tr>'
END
ELSE normal_cell
END
WHEN dow = '7'
AND    mm = next_mm THEN normal_cell
|| <strong>To_char</strong>(dom)
|| '</td></tr>'
WHEN dow = '7'
AND    mm <> next_mm THEN normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
WHEN dow = '1' THEN '<tr>'
|| normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
ELSE normal_cell
|| <strong>To_char</strong>(dom)
|| '</td>'
END
||
CASE
WHEN next_mm IS NULL THEN '</tr></table></td>'
|| <strong>Chr</strong>(10)
|| '</tr></table></body></html>'
END
END html_data ,
TYPE ,
descr
FROM ...

3.5.      Adding in more logic to produce the desired colour for the absences.
</pre>
SELECT
CASE
WHEN TYPE = 'MAT' THEN <strong>Replace</strong>(html_data, '<td class="default_cell">', '<td class="style_mat">')
WHEN TYPE = 'SCK' THEN <strong>Replace</strong>(html_data, '<td class="default_cell">', '<td class="style_sck" title="'
|| descr
|| '">')
WHEN TYPE = 'FLU' THEN <strong>Replace</strong>(html_data, '<td class="default_cell">', '<td class="style_flu">')
ELSE html_data
END
FROM ...

3.6.      Integrating the SQL with PeopleSoft.

This is the peoplecode from YEAR_CODE.RowInit. The complete SQL is stored in the HTML object as RY_ABS_CAL_HTM. I dislike storing SQL in the SQL object as it distorts the formatting and render is unreadable.


Local SQL

If %Page = Page.AN_EMPL_ABS_CAL Or

%Page = Page.AN_ABS_CAL_ESS Then

&year_code = Year(%Date);

DERIVED_ABS_AN.YEAR_CODE = &year_code;

&emplid = PERSON.EMPLID;

DERIVED_ABS_AN.HTMLAREA1 = "";

&html = GetHTMLText(HTML.RY_ABS_CAL_HTM, &emplid, &year_code);

&sql = CreateSQL(&html);

While &sql.Fetch(&html_data)

DERIVED_ABS_AN.HTMLAREA1 = DERIVED_ABS_AN.HTMLAREA1 | &html_data;

End-While;

End-If;

4.      Conclusion

I have not outlined the page with all its attributes nor have I enclosed all the peoplecode as I feel these are easily be accomplished by a developer.

In “HTMLising” the SQL, I am always mindful of “ORA-01489: result of string concatenation is too long”. I could have keep the CSS out of the SQL and inject the CSS part on the page using a static html object. However, this demonstrates the combine power of SQL with embedded HTML.

5.      Full Listing
select
case
when type = 'MAT' then replace(html_data, '<td class="default_cell">', '<td class="style_mat">')
when type = 'SCK' then replace(html_data, '<td class="default_cell">', '<td class="style_sck" title="' || descr || '">')
when type = 'FLU' then replace(html_data, '<td class="default_cell">', '<td class="style_flu">')
else html_data
end
from
(
--
-- generate HTML and CSS for calendars
--
select
case
when rownum = 1 then
'<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Mon</title>
<style type="text/css">
.month_heading {
color: #FFFFFF;
background-color: red;
font-weight:bold;
}
.day_heading {
color: black;
background-color: #C0C0C0;
font-weight:bold;
font-family:Arial,sans-serif;font-size:12pt;
}
.table_heading {
border: 0px solid #000000;
}
.sub_tab_heading {
border-style: solid;
border-width: 1px;
border-collapse:collapse;
}
.style_mat {
font-family:Arial,sans-serif;font-size:10pt;
background-color: purple;
color: white;
font-style: italic;
font-weight:bold;
}
.style_sck {
font-family:Arial,sans-serif;font-size:10pt;
background-color: yellow;
color: black;
font-style: italic;
font-weight: bold;
width: 40px;
}
.style_flu
{
font-family:Arial,sans-serif;font-size:10pt;
background-color: red;
color: black;
font-style: italic;
font-weight: bold;
width: 40px;
}
.style_oth_sck
{
background-color: orange;
color: #FFFFFF;
font-weight:bold;
}
.default_cell
{
width: 40px;
font-family:Arial,sans-serif;font-size:10pt;
}
</style>
</head>
<body>' ||
'<table width="500px"><tr><td class="style_sck" style="width:20px"></td><td>Sickness</td>
<td class="style_mat" style="width:20px"></td><td>Maternity</td>
<td class="style_flu" style="width:20px"></td><td>Flu Pandemic</td>
<td class="style_oth_sck" style="width:20px"></td><td>Other</td>
</tr>
<tr><td colspan="8" style="font-family:Arial,sans-serif;font-size:10pt;font-style: italic;">**Place mouse over highlighted day to see details</tr>
</table>' ||
first_row || month_cell ||
case
when DOW = '1' then normal_cell || to_char(DOM) || '</td>'
when DOW = '2' then blank_cell1 || normal_cell || to_char(DOM) || '</td>'
when DOW = '3' then blank_cell2 || normal_cell || to_char(DOM) || '</td>'
when DOW = '4' then blank_cell3 || normal_cell || to_char(DOM) || '</td>'
when DOW = '5' then blank_cell4 || normal_cell || to_char(DOM) || '</td>'
when DOW = '6' then blank_cell5 || normal_cell || to_char(DOM) || '</td>'
else                blank_cell6 || normal_cell || to_char(DOM) || '</td>'
end
else
case
when DOM = '1' then
case
when DOW = '1' then
case when MM in ('05','09') then break_on_month_group || month_cell || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '2' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell1 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell1 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '3' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell2 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell2 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '4' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell3 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell3 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '5' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell4 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell4 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '6' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell5 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell5 || normal_cell || to_char(DOM) || '</td>'
end
when DOW = '7' then
case when MM in ('05','09') then break_on_month_group || month_cell || blank_cell6 || normal_cell || to_char(DOM) || '</td>'
else                                break_on_month  || month_cell || blank_cell6 || normal_cell || to_char(DOM) || '</td></tr>'
end
else normal_cell
end
when DOW = '7' and MM = NEXT_MM  then normal_cell || to_char(DOM) || '</td></tr>'
when DOW = '7' and MM <> NEXT_MM then normal_cell || to_char(DOM) || '</td>'
when DOW = '1' then '<tr>' || normal_cell || to_char(DOM) || '</td>'
else                            normal_cell || to_char(DOM) || '</td>'
end
|| case when NEXT_MM is null then '</tr></table></td>' || chr(10) || '</tr></table></body></html>' end
end html_data
, type
, descr
from
(
with
/* Stage 1  Creating calendar using Dual with connect by level testing for leap year. */
calendar as ( SELECT rownum
,  to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1                           DAILY
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'DAY')           DAY
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'D')             DOW
,  to_number(to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'DD')) DOM
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'WW')            WEEK
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'MM')            MM
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'Month')         MONTH
,  to_char(to_date('01-jan-' || '%Bind(:2)'  ,'dd-mon-yyyy') + rownum - 1 ,'YYYY')          YEAR
FROM dual
connect by level  <= (  SELECT /* test for leap year */ decode(to_number(to_char(last_day(to_date('01-feb-' || '%Bind(:2)'  ,'DD-mon-yyyy'))  ,'DD'))  , 29  ,366  ,365)   FROM dual ))
/*Extract absences for the appropriate time span*/
, abs_hist as
( SELECT /*+ no_parellel(a) no_parallel(b) no_parallel(c) */
a.emplid, a.bgn_dt, a.end_dt, a.gpgb_absence_type, a.gpgb_absence_code, c.descr
from ps_gpgb_abs_evt_jr a   , ps_gp_abs_event b, ps_gpgb_abs_code c
where a.emplid            = '%Bind(:1)'
and a.emplid            = b.emplid
and a.empl_rcd          = b.empl_rcd
and a.pin_take_num      = b.pin_take_num
and a.bgn_dt            = b.bgn_dt
and a.end_dt            = b.end_dt
and a.gpgb_absence_type = c.gpgb_absence_type
and a.gpgb_absence_code = c.gpgb_absence_code
and b.voided_ind        = 'N'
and b.bgn_dt            <= to_date('3112%Bind(:2)', 'ddmmyyyy')
and b.end_dt            >= to_date('0101%Bind(:2)','ddmmyyyy')
)
/*Stage2. Joining data from Stage1
Start with generated yearly calendar(above) and merge in absence details (criteria DAILY between bgn_dt and end_dt)
*/
select
MM
, lead(mm) over(order by mm, dom) NEXT_MM
, DOM
, DOW
, LTRIM(RTRIM(DAY,' '),' ') DAY
, coalesce((select gpgb_absence_type from abs_hist where DAILY between bgn_dt and end_dt),' ') type
, coalesce((select descr from abs_hist where DAILY between bgn_dt and end_dt),' ') descr
-- rest of columns are for html purpose
, '<table style="width: 100%" class="table_heading"><tr>'           first_row
,'</tr></table></td></tr><tr>'                               break_on_month_group
,'</tr></table></td>'                                        break_on_month
, '(''04'',''07'',''10'')'                            month_group_value
,'<td valign="top">
<table style="width: 100%" class="sub_tab_heading" border="1">
<tr>
<td colspan="7" class="month_heading">' || MONTH || '</td>
</tr>
<tr>
<td class="day_heading">Mon</td>
<td class="day_heading">Tue</td>
<td class="day_heading">Wed</td>
<td class="day_heading">Thu</td>
<td class="day_heading">Fri</td>
<td class="day_heading">Sat</td>
<td class="day_heading">Sun</td>
</tr>
<tr>'                                                    month_cell
, '<td class="default_cell">'                                       normal_cell
, '<td></td>'                                                       blank_cell1
, '<td></td><td></td>'                                              blank_cell2
, '<td></td><td></td><td></td>'                              blank_cell3
, '<td></td><td></td><td></td><td></td>'                     blank_cell4
, '<td></td><td></td><td></td><td></td><td></td>'                   blank_cell5
, '<td></td><td></td><td></td><td></td><td></td><td></td>'          blank_cell6
, '<td></td><td></td><td></td><td></td><td></td><td></td><td></td>' blank_cell7
from calendar
)
)

Journées SQL Server 2014: il n'est pas trop tard pour vous inscrire

Yann Neuhaus - Mon, 2014-11-24 01:35

Le plus grand événement français va bientôt avoir lieu (1 et 2 décembre) à Paris et il vous reste  encore une semaine pour vous y inscrire! Pensez également à commander votre lunch box, ce qui peut être pratique si vous voulez manger :-) 

 

blog_24_-_5_-_AlwaysOn

 

Pour ma part j’aurai l’occasion d’ouvrir le bal et de vous retrouver au cours d’une session sur SQL Server et les groupes de disponibilité (cela devient presque une habitude) lundi matin à 10h30. Cela fait quelques temps maintenant que je mets en place des infrastructures de groupes de disponibilité chez des clients et cette fois-ci je voulais partager avec vous les problématiques auxquelles j’ai été confronté pendant mes différentes prestations que ce soit pendant les phases d’implémentation mais aussi sur des problèmes concrets de production. Une session avec beaucoup de cas concrets et de démo! Plus d'informations ici.

Cette année est également particulière car la société dbi services, pour laquelle je travaille, fera parti des sponsors (Gold) de l’événement. Une autre façon de montrer que nous attachons beaucoup d’importance à ce genre d’événement autour des technologies de bases de données.

Un petit clin d’oeil également à mon collègue Stéphane Haby (MVP SQL Server) qui présentera une session sur la gestion des règles avec SQL Server … session à ne pas manquer si la gestion de la sécurité est est une problématique importante.

Il y a également un nombre conséquent de sessions qui peuvent vous intéressés et dans plusieurs domaines (Microsot Azure, BI, moteur SQL etc.) et personnellement j'ai déjà réaliser mon planning de sessions à voir mais vous? Si ce n'est pas le cas rendez-vous à l'agenda de session ... faites votre marché :-)

 

blog_24_-_3_-_Agenda

 

En espérant vous y retrouver nombreux, que ce soit aux sessions ou au stand dbi services!

12.1.0.2 Introduction to Zone Maps Part III (Little By Little)

Richard Foote - Mon, 2014-11-24 00:16
I’ve previously discussed the new Zone Map database feature and how they work in a similar manner to Exadata Storage indexes. Just like Storage Indexes (and conventional indexes for that manner), they work best when the data is well clustered in relation to the Zone Map or index. By having the data in the table […]
Categories: DBA Blogs

Baselines

Jonathan Lewis - Sun, 2014-11-23 14:58

I’m not very keen on bending the rules on production systems, I’d prefer to do things that look as if they could have happened in a completely legal fashion, but sometimes it’s necessary to abuse the system and here’s an example to demonstrate the point. I’ve got a simple SQL statement consisting of nothing more than an eight table join where the optimizer (on the various versions I’ve tested, including 12c) examines 5,040 join orders (even though _optimizer_max_permutations is set to the default of 2,000 – and that might come as a little surprise if you thought you knew what that parameter was supposed to do):

select
	/*+ star */
	d1.p1, d2.p2, d3.p3,
	d4.p4, d5.p5, d6.p6,
	d7.p7,
	f.small_vc
from
	dim1		d1,
	dim2		d2,
	dim3		d3,
	dim4		d4,
	dim5		d5,
	dim6		d6,
	dim7		d7,
	fact_tab	f
where
	d1.v1 = 'abc'
and	d2.v2 = 'def'
and	d3.v3 = 'ghi'
and	d4.v4 = 'ghi'
and	d5.v5 = 'ghi'
and	d6.v6 = 'ghi'
and	d7.v7 = 'ghi'
and	f.id1 = d1.id
and	f.id2 = d2.id
and	f.id3 = d3.id
and	f.id4 = d4.id
and	f.id5 = d5.id
and	f.id6 = d6.id
and	f.id7 = d7.id
;

It’s useful to have such extreme examples because they make it easy to notice certain features of Oracle’s behaviour – in this case the difference between SQL Plan Baselines and SQL Profiles. After I had created a baseline for this statement the optimizer still examined 5,040 join orders because that’s the way that baselines work – the optimizer first tries to optimize the statement without the baseline in case it can produce a better plan (for future evaluation and evolution) than the plan dictated by the baseline.

In my example this wasn’t really a problem (apart from the memory requirement in the SGA) but one of my clients has a production query that takes 7 seconds to optimize then runs in a few seconds more, so I’d like to get rid of that 7 seconds … without touching the code. Adding a baseline won’t reduce the time to optimize. (Note: 7 seconds may not seem like a lot, but when several copies of this query are executed concurrently using a different literal value as an input, that 7 seconds can go up to anything between 40 and 500 seconds of CPU parse time per query).

If I take a different tack and write some code to acquire the content of the SQL Plan Baseline (viz. the outline section from the in-memory execution plan) but store it as an SQL Profile the optimizer simply follows the embedded hints and examines just one join order (because the set of hints includes a leading() hint specifying exactly the join order required). This is why I will, occasionally, take advantage of the code that Kerry Osborne and Randolf Geist produced some time ago to raid the library cache for a child cursor and store its plan outline as an SQL profile.

Footnote:

This dirty trick doesn’t always solve the problem - the first example I tried to use for my demonstration did a complex concatenation that took a couple of minutes to optimise, and storing the baseline as a profile didn’t help.

 

 


Great Lakes Oracle Conference 2015 call for speakers now open

Grumpy old DBA - Sun, 2014-11-23 12:17
Calling all geeks speakers big data nerds and application whiz kids the Great Lakes Oracle Conference ( GLOC ) is open for abstracts/presentation proposals ( May 18-20 2015 ).


This is a growing high quality regional conference in Cleveland where we are hoping to pass 350 ( 400? ) in attendance this year.  We have Kerry Osborne and Steven Feuerstein doing keynotes and on Monday 1/2 day workshops before the conference kicks into high gear for the next two days.


Call for presentations information here: GLOC speakers


Please consider joining us in Cleveland and hope to see you there!


John Hurley @grumpyolddba and NEOOUG President

Categories: DBA Blogs

PCFHawq*Web Browser based web application for PHD service within Cloud Foundry

Pas Apicella - Sun, 2014-11-23 05:12
Pivotal HD for Pivotal CF deliver's Pivotal HD - Pivotal's leading Hadoop distribution as a Pivotal CF Service. Pivotal HD is a commercially supported distribution of Apache Hadoop. The Pivotal HD Data Service includes HDFS, YARN and MapReduce. It also includes HAWQ, Pivotal's high performance SQL database on HDFS and Pivotal's In-memory OLTP SQL processing engine GemFire XD.

https://network.pivotal.io/products/pivotal-hd-service

Pivotal PCFHawq*Web is a browser based schema administration tool for HAWQ within Pivotal Cloud Foundry 1.3. It supports auto binding to a PHD service but can run stand alone outside of PCF. If you don't bind the application to a PHD instance it presents a login page to allow you to manually connect to HAWQ within PCF. When bound to a PHD service it will connect using the VCAP_SERVICES credentials automatically for you. It supports the following features

  • Browse tables/views/external tables
  • Save Query Results in CSV or JSON format
  • SQL Worksheet to load/execute SQL DML/DDL statements



Below is the GITHUB project for this tool.

More Info

https://github.com/papicella/PCFHawqWebhttp://feeds.feedburner.com/TheBlasFromPas
Categories: Fusion Middleware

From Coffee Table to Cloud at a Glance: Free Oracle Applications Cloud UX eBook Available

Usable Apps - Sat, 2014-11-22 08:56

Your free eBook, Oracle Applications Cloud User Experiences: Trends and Strategy, is now available. Go to tinyurl.com/UXstrategy to register and download the PDF.   

This is a colorful, beautifully illustrated, and simply written document that shows and tells you everything you need to know about the Oracle Applications Cloud user experience. From strategy and design philosophy to current innovation and emergent trends, the Oracle Applications User Experience (OAUX) team has it covered. 

 Trends and Strategy

Jeremy Ashley (@jrwashley), Oracle Applications User Experience Vice President, tell you just what you need to use that Oracle UX message to increase your cloud business. 

So, what's in it for partners and developers? As you scan through the eBook, you'll discover as innovation and ideation comes to life using Oracle technology toolkit and the guidance shared on the Usable Apps website, the OUAX outreach enables you to build similar awesome cloud user experiences. You can use the eBook to confidently explain key cloud UX concepts to your customers and to jointly inspire new business ideas and solutions.

And hey, it's a great resource to share with anyone interested in design, technology, and building things with a user experience too!

Extending the User Interface

We enable business users and developers to build and tailor simplified user experiences for the Oracle Applications Cloud productively, in ways that make sense for their customers.

The Cloud is Our Platform

The cloud is our platform. You can also design optimized, contextual user experiences easily, using familiar, core elements across experiences. 

Watch out for more eBooks from OAUX. Stay tuned to the usual channels.

For now, enjoy! 

PeopleSoft Update Image as a Service - Proof of Concept

Javier Delgado - Fri, 2014-11-21 17:40
Together with PeopleSoft 9.2, Oracle announced the new Continuous Delivery model for delivering patches, based on PeopleSoft Update Manager. The new model allows customers to choose which fixes or enhancements they are interested in, and then just extract the objects related to them and their prerequisites.

The following diagram outlines the process of applying an update using this new delivery model:



In order to apply a change in your Development environment, you would to follow these steps:


  1. Download the latest Update Image from My Oracle Support. The latest images have a size of around 35 Gb for PeopleSoft HCM and for FSCM.
  2. Run a script to unzip the downloaded files and combine them into a single virtual appliance file (.ova).
  3. Import the .ova file into Virtual Box in order to create the virtual machine.
  4. Start the virtual machine and follow the installation steps so it can be used.
  5. Using PeopleSoft Change Assistant, upload your environment information into PeopleSoft Update Manager.
  6. Connect to the virtual machine environment in order to choose which patches or enhancements are going to be applied. The selected patches conform an Change Package.
  7. Run the source steps in the Change Package against the Update Image.
  8. Run the target steps included in the Change Package against the target environment.


Many customers find that the first 4 steps in the process take too much time, particularly when the intention is to apply a single regulatory update or enhancement. In the past, the customer would just download the patch and its prerequisites (which in many cases, for regulatory patches, were already applied) and starts the updating process. The new process requires to invest at least a day in downloading, uncompressing and installing the virtual machine.

On top of the time issues, the virtual machine can only run in a physical box with certain prerequisites. In these times when many organizations have moved to virtualization, it is not always easy to allocate the needed hardware to host the virtual machines.

BNB has conducted a successful Proof of Concept for one of our customers to install the latest Update Image on an Amazon EC2 server and use it to apply the latest patches.

Initially, we had some concerns about if this idea could work with a good performance. After our first real run, we can conclude that the performance is more than acceptable. The new Change Assistant in PeopleTools 8.54 is helping a lot, as it allows to run source and target steps separately. In this way, the source steps can be run in a Windows Amazon EC2 server sitting close to the Update Image, with the obvious performance gains. Still there will be some connectivity needed between your site and the Amazon EC2 servers, mainly to:

  • Upload target environment information from your server into the Update Image.
  • Download the Oracle Database and PeopleTools clients.
  • Download the Change Package once the source steps are finished.

We also faced some challenges in converting the Virtual Box delivered virtual machine into an Amazon EC2 hosted machine. We could eventually solve them and now we have created an AMI to quickly deploy new instances for other PeopleSoft customers. We have also tested the conversion steps multiple times, so we should now be able to have the AMI available just one day after the Update Image is made available by Oracle.

If you are interested in trying this solution, please feel free to contact us at peoplesoft@bnetbuilders.com. We are offering it as a complimentary service until the end of this year.

WCET14 Student Panel: What do students think of online education?

Michael Feldstein - Fri, 2014-11-21 15:37

Yesterday at the WCET14 conference in Portland I had the opportunity along with Pat James to moderate a student panel.[1] I have been trying to encourage conference organizers to include more opportunities to let students speak for themselves – becoming real people with real stories rather than nameless aggregations of assumptions. WCET stepped up with this session. And my new favorite tweet[2]:

@kuriousmind @lukedowden @wcet_info @PhilOnEdTech Best. Panel. Ever. Massively insightful experience.

— Matthew L Prineas (@mprineas) November 20, 2014

As I called out in my introduction, we talk about students, we characterize students, we listen to others talk about students, but we don’t do a good job in edtech talking with students.  There is no way that a student panel can be representative of all students, even for a single program or campus[3]. We’re not looking for statistical answers, but we can hear stories and gain understanding.

These four students were either working adults (and I’m including a stay-at-home mom in this category) taking undergraduate online programs. They were quite well-spoken and self-aware, which made for a great conversation that included comments that might surprise some on faculty-student interaction potential:

A very surprising (to me) comment on class size:

And specific feedback on what doesn’t work well in online courses:

To help with viewing of the panel, here are the primary questions / topics of discussion:

The whole student panel is available on the Mediasite platform:

Thanks to the help of the Mediasite folks, I have also uploaded a Youtube video of the full panel:

Click here to view the embedded video.

  1. Pat is the executive director of the California Community College Online Education Initiative (OEI) – see her blog here for program updates.
  2. I’m not above #shameless.
  3. As can be seen from this monochromatic panel, which might make sense for Portland demographics but not from a nationwide perspective.

The post WCET14 Student Panel: What do students think of online education? appeared first on e-Literate.

New leafmisscount Option in Flex Cluster 12c

Pythian Group - Fri, 2014-11-21 09:40

In the Oracle RAC previous version we have worked with the option “misscount” of crsctl, which applies for all the servers because there was only one kind of servers, but in the new version of Oracle (12c) we have a new kind of RAC configuration, the “Flex Cluster”. With Flex Cluster we have two kinds of servers “Hub nodes” and “Leaf Nodes”, the Hub Nodes have the same concept of the normal nodes in the previous versions, however the Leaf Nodes are different. I will let Oracle Documentation define these servers:

Hub Nodes are similar to Oracle Grid Infrastructure nodes in an Oracle Clusterware standard Cluster configuration: they are tightly connected, and have direct access to shared storage. In an Oracle Flex Cluster configuration, shared storage can be provisioned to Leaf Nodes independent of the Oracle Grid Infrastructure.

Leaf Nodes are different from standard Oracle Grid Infrastructure nodes, in that they do not require direct access to shared storage, but instead request data through Hub Nodes. Hub Nodes can run in an Oracle Flex Cluster configuration without having any Leaf Nodes as cluster member nodes, but Leaf Nodes must be members of a cluster that includes at least one Hub Node.

Reference: https://docs.oracle.com/database/121/CWADD/bigcluster.htm#CWADD92560

With new kind of servers, we also have a new option to configure the maximum time that a Leaf node can be tolerated with heartbeat issues. This crsctl option is “leafmisscount” and it’s exactly the same than the “misscount” for Hub Nodes, but this is for Leaf Nodes.

Let’s see how does it work:

 My Flex Cluster

Configuration

[root@dg1 ~]# crsctl check cluster -all

**************************************************************

dg1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

dg2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

dg3:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

[root@dg1 ~]#

 

[root@dg1 ~]# crsctl get node role status -all

Node ‘dg1′ active role is ‘hub’

Node ‘dg2′ active role is ‘hub’

Node ‘dg3′ active role is ‘leaf’

[root@dg1 ~]#

Checking the current value:

[grid@dg1 ~]$ crsctl get css leafmisscount

CRS-4678: Successful get leafmisscount 30 for Cluster Synchronization Services.

[grid@dg1 ~]$

How does it work with 30 secs:

[grid@dg2 ~]$ date

Fri Nov 14 16:03:54 EST 2014

–Stop connectivity between Leaf Node
and Hub Nodes

[root@dg3 ~]# date

Fri Nov 14 16:04:01 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:04:29 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

[root@dg3 ~]#

Now let’s increase the value to 600 sec:

[grid@dg1 ~]$ crsctl set css leafmisscount 600

CRS-4684: Successful set of parameter leafmisscount to 600 for Cluster Synchronization Services.

[grid@dg1 ~]$

Let’s confirm the value:

[grid@dg2 ~]$ crsctl get css leafmisscount

CRS-4678: Successful get leafmisscount 600 for Cluster Synchronization Services.

[grid@dg2 ~]$

The maximum value we can specify is 600:

[grid@dg1 ~]$ crsctl set css leafmisscount 3600

CRS-1671: The value for parameter leafmisscount is outside the allowed range of 1 to 600

[grid@dg1 ~]$

Let’s see how does it work with 600:

[root@dg3 ~]# date

Fri Nov 14 16:19:54 EST 2014

–Stop connectivity between leaf
node and hub nodes

[root@dg3 ~]# date

Fri Nov 14 16:24:01 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:25:18 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:27:30 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:28:08 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:29:04 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:29:50 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:30:10 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

[root@dg3 ~]#

Categories: DBA Blogs

Challenge Me! Capturing SQL Server Data with PowerShell

Pythian Group - Fri, 2014-11-21 09:05

LaerteHello PowerShell lovers! If you are reading this blog post, then you are likely interested in attending my session at SQLBits (well, so far it is an abstract. Hopefully I will be selected!)

Any data professional who is responsible for performance tuning, has their way of gathering data to troubleshoot and solve issues. But what if I told you there was a different way, a better way, to collect the data you need without consuming additional SQL Server resources?

Attend my session and learn how to leverage PowerShell to collect data from Perfmon, DMVs, and more. This session will feature demos where attendees will learn about some .Net classes to control timer events and the PowerShell Register-Objectevent cmdlet.

Attendees are encouraged to send their challenges to me by email at laertesqldba@outlook.com. I will review their scripts and select some of them to demonstrate in the session by offering an alternative using PowerShell and the event  subsystem.

I will split your challenges into three categories:

  1. Perfmon Counters
  2. DMVs, DMFs, etc.
  3. Other

Using one challenge from each category, here are examples of the kinds challenges I will demonstrate:

 

Example 1: Perfmon Counters

Hello Laerte,

My name is James Tiberius Kirk, I am Captain of the starship USS Enterprise and we are testing a new warp drive and our SQL Server´s servers are very, very busy with that. Our DBAs need a way to collect some performance counters, in an interval of time of 15 minutes and the data stored in a repository for further analysis. This process need to be done remotely and we cannot use any kind of SQL Server resources for that. We don’t want to use any kind of GUI for that as well. The performance counters are:SQLServer: Buffer Manager: Buffer cache hit ratio

SQLServer: Buffer Manager: Page life expectancy
SQLServer: SQL Statistics: Batch Requests/Sec
SQLServer: SQL Statistics: SQL Compilations/Sec
SQLServer: SQL Statistics: SQL Re-Compilations/SecCan you help us?  Thanks!


Example 2: DMVs, DMFs, etc.

Hello Laerte,

My name is Anakyn Skywalker, (a.k.a. Darth Vader, the most powerful Sith Master ever.) We are building a new Death Star and our SQL Server´s servers are extremely busy with inserts and updates. We need to capture and store the fragmentation data from one table called BlowUpThePlanet in a server called DeathStarProd3 Database VaderIsTheBest. As I told you before and I will not say again, it is a very busy server and because of that, the interval of gathering needs to be hourly. I am awaiting your help today. *FORCE CHOKING*


Example 3: Other

Hello Laerte,

My Name is Neo. Everything you know, or you think know, actually does not exist—but we can talk about that later.  We are trying to inject a new code in the Matrix (don’t worry about that right now either) and I need to capture some data. I will send to you the TSQL and all information that I need. Can you help me?  Thanks.

TSQL
Insert into XXXX Select XXXX
Interval – 10 seconds
Server Name – XXX
Database Name – YYY

 

Please send your challenges to laertesqldba@outlook.com. I will select three of them, one from each category and I will demonstrate how to do them using PowerShell without using any kind of SQL Server resources, after  I explain the Timer class and the register-objectevent cmdlet.

That is it! And remember: Always… if it is PowerCool, it is PowerShell.

Categories: DBA Blogs

#DOAG2014 bits of info for DBAs

The Oracle Instructor - Fri, 2014-11-21 07:36

Just wanted to share some pieces of information from the recent DOAG annual conference that you may find interesting.

From Mike Dietrich’s presentation about Database Upgrade:

Mike Dietrich

Database Replay is extremely useful to predict after-upgrade performance on a test system, especially we can record the production load on 10g even.

From Carsten Czarski’s talk about XML DB:

Carsten Czarski

With 12c, XML DB is mandatory and it provides an easy way to upload BLOBs via ftp into the database.

From Ulrike Schwinn’s talk about the Resource Manager I took away that

The resource manager becomes more and more popular and important, especially for Multitenant

- something Hans Forbrich reinforced later on.

Particularly I liked way she presented later on about ADO: Very many live demonstrations – that’s how I try to do my own presentations also :-)

Ulrike Schwinn

Frank Schneede did a great job debunking Exadata myths. For example,

You don’t need to have all cores enabled with Exadata X4 in order to save license cost. That’s called Capacity on Demand.

Frank Schneede

If I should name one presentation that was most useful for me, it’ll be probably Frank’s.

Markus Michalewicz delivered an excellent talk as expected about RAC cache fusion:

Markus Michalewicz

Two important messages:

RAC scales well (far) beyond three nodes because there are never more than three nodes involved for cache fusion intercommunication. And Multitenant and RAC are a perfect fit.

One Data Guard snippet out of Larry Carpenter’s talk about Global Data Services (GDS):

GDS makes it possible to automate the failover of the Real-Time Query service to the primary in case the physical standby has an outage.

Larry Carpenter

Hans Forbrich talked about Multitenant. He showed great presentation skills and although I knew the technical details before, the way he highlighted certain aspects was still very helpful for me.

Hans Forbrich

One key message was that

Multitenant is here to stay. DBAs should learn about it and become familiar with it as soon as possible, because sooner than later it will have to be administered in production!
Tagged: #DOAG2014
Categories: DBA Blogs

KeepAlive socket in 12c listener

Laurent Schneider - Fri, 2014-11-21 06:50

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on communication channel, ORA-03135: connection lost contact, TNS-12547 Lost contact.

The good news is that Oracle 12c now implements Socket Options (see man setsockopt), as documented in Net admin new features and more nicely in Note 1591874.1
dcd visualized

I made until now a positive experience with this keepalive behavior, especially with SSL listener. The default value for SQLNET.EXPIRE_TIME is 0, so you must set it to a non-zero value first, the recommended value is 10 (minutes).

Oracle 12c Unified Auditing - Mixed Mode

Next in our blog series on Oracle 12 Unified Auditing is a discussion of Mixed Mode. Mixed Mode is the default auditing mode for Oracle 12c.  Oracle describes Mixed Mode auditing as a means of becoming familiar with Unified Auditing prior to migrating to Pure Unified Auditing.  Mixed Mode allows for all traditional, pre-12c log and audit functionality to co-exist with Unified Auditing.  More importantly, Mixed Mode will support any current Syslog-based logging solution.

Mixed mode auditing provides the following key capabilities –

  • All existing (pre-12c) auditing initialization configurations and parameters are used such as AUDIT_TRAILAUDIT_FILE_DESTAUDIT_SYS_OPERATIONS, and AUDIT_SYSLOG_LEVEL
  • The format of the audit records remains the same as in Oracle Database 11g Release 2
  • Writes mandatory audit records to the traditional audit trails
  • If the AUDIT_SYS_OPERATIONS initialization parameter is set to TRUE, writes audit records only to the traditional audit trails

With Mixed Mode, audit data can be found both in the traditional locations as well as in SYS.UNIFIED_AUDIT_TRAIL.  This is because the Unified Auditing Policy ORA_SECURECONFIG is enabled by default.  ORA_SECURECONFIG audits the same default audit settings from Oracle Database Release 11g.  Integrigy recommends to either periodically purge Unified Auditing data or disable the policy.  To disable ORA_SECURECONFIG policy follow the instructions in Oracle Support Note Doc ID 1624051.1.

The following table shows the definition of the default policy ORA_SECURECONFIG.  Note the column ‘Common’ that shows that the policy is defined for all PDBs (tenant) databases.

Mixed Mode Default Unified Policy ORA_SECURECONFIG

Audit Option

Option Type

Common

Integrigy Framework

ADMINISTER KEY MANAGEMENT

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

ALTER ANY PROCEDURE

SYSTEM PRIVILEGE

YES

E13 – Objects

ALTER ANY SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

ALTER ANY TABLE

SYSTEM PRIVILEGE

YES

E13 – Objects

ALTER DATABASE

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

ALTER DATABASE LINK

STANDARD ACTION

YES

E13 – Objects

ALTER PLUGGABLE DATABASE

STANDARD ACTION

YES

E11 - Privileged commands

ALTER PROFILE

STANDARD ACTION

YES

E14 - Modify configuration settings

ALTER ROLE

STANDARD ACTION

YES

E8 - Modify role

ALTER SYSTEM

SYSTEM PRIVILEGE

YES

E14 - Modify configuration settings

ALTER USER

STANDARD ACTION

YES

E6 - Modify user account

AUDIT SYSTEM

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

CREATE ANY JOB

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ANY LIBRARY

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ANY PROCEDURE

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ANY SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

CREATE ANY TABLE

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE DATABASE LINK

STANDARD ACTION

YES

E13 – Objects

CREATE DIRECTORY

STANDARD ACTION

YES

E13 – Objects

CREATE EXTERNAL JOB

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE PLUGGABLE DATABASE

STANDARD ACTION

YES

E11 - Privileged commands

CREATE PROFILE

STANDARD ACTION

YES

E11 - Privileged commands

CREATE PUBLIC SYNONYM

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE ROLE

STANDARD ACTION

YES

E7 - Create role

CREATE SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E13 – Objects

CREATE USER

SYSTEM PRIVILEGE

YES

E5 – Create user account

DROP ANY PROCEDURE

SYSTEM PRIVILEGE

YES

E13 – Objects

DROP ANY SQL TRANSLATION PROFILE

SYSTEM PRIVILEGE

YES

E13 - Objects

DROP ANY TABLE

SYSTEM PRIVILEGE

YES

E13 – Objects

DROP DATABASE LINK

STANDARD ACTION

YES

E13 – Objects

DROP DIRECTORY

STANDARD ACTION

YES

E13 – Objects

DROP PLUGGABLE DATABASE

STANDARD ACTION

YES

E11 - Privileged commands

DROP PROFILE

STANDARD ACTION

YES

E14 - Modify configuration settings

DROP PUBLIC SYNONYM

SYSTEM PRIVILEGE

YES

E13 – Objects

DROP ROLE

STANDARD ACTION

YES

E8 - Modify role

DROP USER

SYSTEM PRIVILEGE

YES

E6 - Modify user account

EXEMPT ACCESS POLICY

SYSTEM PRIVILEGE

YES

E14 - Modify configuration settings

EXEMPT REDACTION POLICY

SYSTEM PRIVILEGE

YES

E14 - Modify configuration settings

GRANT ANY OBJECT PRIVILEGE

SYSTEM PRIVILEGE

YES

E9 - Grant/revoke user privileges

GRANT ANY PRIVILEGE

SYSTEM PRIVILEGE

YES

E9 - Grant/revoke user privileges

GRANT ANY ROLE

SYSTEM PRIVILEGE

YES

E9 - Grant/revoke user privileges

LOGMINING

SYSTEM PRIVILEGE

YES

E12 - Modify audit and logging

LOGOFF

STANDARD ACTION

YES

E2 - Logoff

LOGON

STANDARD ACTION

YES

E1 - Login

PURGE DBA_RECYCLEBIN

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

SET ROLE

STANDARD ACTION

YES

E11 - Privileged commands

TRANSLATE ANY SQL

SYSTEM PRIVILEGE

YES

E11 - Privileged commands

If you have questions, please contact us at mailto:info@integrigy.com

Reference Tags: AuditingOracle Database
Categories: APPS Blogs, Security Blogs

Parallel Costs

Jonathan Lewis - Fri, 2014-11-21 05:36

While creating a POC of a SQL rewrite recently I received a little surprise as I switched my query from serial execution to parallel execution and saw the optimizer’s estimated cost increase dramatically. I’ll explain why in a moment, but it made me think it might be worth setting up a very simple demonstration of the anomaly. I created a table t1 by copying view all_source – which happened to give me a table with about 100,000 rows and 1117 blocks – and then ran the query ‘select max(line) from t1;’ repeating the query with a /*+ parallel(t1 2) */ hint. From 11.2.0.4 here are the two execution plans I got:


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |   153   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 99173 |   387K|   153   (4)| 00:00:01 |
---------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |     4 |  1010   (1)| 00:00:05 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |     4 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |     4 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |     4 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 99173 |   387K|  1010   (1)| 00:00:05 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       | 99173 |   387K|  1010   (1)| 00:00:05 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------

It seems that the optimizer thinks that running the query parallel 2 will take five times as long as running it serially ! Is it, perhaps, some cunning fix to the optimizer that is trying to allow for process startup time for the parallel execution slaves ? Is it a bug ? No – it’s just that I hadn’t got around to setting my system stats and they were complete garbage thanks to various other tests I had been running over the previous couple of weeks. Critically, I had not adjusted the “parallel-oriented” statistics to be consistent with the “serial-oriented” stats.

Here, from the 10053 trace file for the parallel run, is the section on the Single Table Access costing, together with the system stats and base statistical information:


SYSTEM STATISTICS INFORMATION
-----------------------------
  Using WORKLOAD Stats
  CPUSPEED: 1110 millions instructions/sec
  SREADTIM: 4.540000 milliseconds
  MREADTIM: 12.440000 millisecons
  MBRC:     21 blocks
  MAXTHR:   3000000 bytes/sec
  SLAVETHR: 1000000 bytes/sec

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 99173  #Blks:  1117  AvgRowLen:  76.00  ChainCnt:  0.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 99173.000000  Rounded: 99173  Computed: 99173.00  Non Adjusted: 99173.00
  Access Path: TableScan
    Cost:  151.13  Resp: 1010.06  Degree: 0
      Cost_io: 147.00  Cost_cpu: 20826330
      Resp_io: 1007.76  Resp_cpu: 11570183

I’m going to walk through the optimizer’s calculations that got the serial I/O cost (cost_io: 147.00) and the parallel I/O cost (Resp_io: 1007.76), but before I do that I’ll point out how inconsistent some of the system stat are. The multiblock read time (mreadtim) is 12.44 milliseconds, to read an average of 21 blocks (mbrc) which, converting to bytes per second means (21 * 8192) * 1000/12.44 = 13,828,938 bytes per second; but the I/O rate for a single parallel execution slave (slavethr) is only 1,000,000 bytes per second – which means a single serial session can (apparently) tablescan nearly 14 times as fast as an individual parallel execution slave. It’s not surprising that somehow the optimizer thinks a serial tablescan will be faster than parallel 2) – but let’s check exactly how the arithmetic falls out.

Serial:

  • #Blks: 1117, MBRC = 21 => read requests = 1117/21 = 53.19
  • sreadtim = 4.54 milliseconds, mreadtim = 12.44 milliseconds = 2.74 * sreadtim
  • Cost = 53.19 * 2.74 + 1 (_tablescan_cost_plus_one = true) = 146.74 — which looks close enough.

Parallel:

  • #Blks: 1117, block size = 8KB => total I/O requirement = 9,150,464 bytes
  • slavethr: 1,000,000 bytes/sec, degree 2 => total throughput 2,000,000 bytes/sec => elapsed I/O time = 4.575232 seconds
  • sreadtim = 4.54 milliseconds => cost = 4.575232 / 0.00454 = 1007.76 QED.
Two final thoughts:

First, if I increase the degree of parallelism to 3 the cost drops to 673 (671.84 plus a bit of CPU); if I increase the degree any further the cost doesn’t drop any further – that’s because the maxthr (maximum throughput) is 3,000,000. The optimizer uses the smaller of maxthr and (degree * slavethr) in the parallel arithmetic.

Secondly, and most importantly, the way the optimizer behaves will be affected by the various parameters relating to parallelism, so here’s a list of the relevant settings on the instance I was using when creating this example:


SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     80
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     32
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

Note, particularly, that I have not enabled parallel_automatic_tuning.

For further details on parallel costs and the cost/time equivalence that the optimizer uses in recent versions, here’s a link to an article by Randolf Geist.


Evènement : Atelier Enterprise Manager 12c / DBaaS

Jean-Philippe Pinte - Fri, 2014-11-21 02:55

Rejoignez-nous le 2 ou 3 Décembre 2014 pour expérimenter pendant une journée les aspects liés à la planification, le déploiement, le suivi et la gestion d’une solution de Cloud Privé d’Entreprise.
Pendant cette journée, vous aurez l’occasion de découvrir les points suivants :
  • Considération architecturale et concepts d’une solution de cloud privé
  • Consolidation base de données & Planification des ressources
  • Approche Base de données en mode service
    (Database as a service)
  • Clone de bases de données
  • Métriques & facturation
  • Provisioning, patching, configuration & conformité