Feed aggregator

Merge - unfold records based on conditional join

Tom Kyte - Thu, 2017-11-02 14:06
Hi Team, Need your help or suggestion on altering a merge statement. I have a below staging table A_TRANSACTION_STAGING which gets merged to main table A_TRANSACTION : A_TRANSACTION_STAGING : <code> TRANSACTION_ID NUMBER REGION_CD ...
Categories: DBA Blogs

SQL Query related to String

Tom Kyte - Thu, 2017-11-02 14:06
Hi Tom, There is a string 'ascjhsdndfdaja' i want to print only 'a' alphabet from this string there are 3-occurrence of 'a' so i want to print 'aaa' can you please help me this. Your help will be much appriciated. Thanks
Categories: DBA Blogs

trim in sql*plus

Tom Kyte - Thu, 2017-11-02 14:06
Hi Tom, I have a varchar2(30) field which when displayed on sqlplus, doesn't seem to be trimming the trailing spaces when I use rtim or trim in select stmt: set head off set colsep "," set trim on set wrap off set linesize 800 select part_id...
Categories: DBA Blogs

Two Talks Accepted for RMOUG Training Days

Bobby Durrett's DBA Blog - Thu, 2017-11-02 14:01

I got two talks accepted for RMOUG Training Days in February. I mentioned these two titles in a earlier post:

  • Python for the Oracle DBA
  • Toastmasters for the Oracle DBA

These two talks are about topics that interest me so I am glad that RMOUG thinks that they are valuable to the conference attendees.

I plan to do the two talks for my DBA coworkers and shorter versions at Toastmasters so I should get some constructive feedback and practice before the conference.

Should be fun. Hope to see you in Denver next February.

My Python posts: url

My Toastmasters posts: url

Bobby

Categories: DBA Blogs

Quick history on database growth

Yann Neuhaus - Thu, 2017-11-02 12:13

AWR collects segment statistics, and this can be used to quickly understand an abnormal database growth. Here is a script I use to get, from the AWR history, the segments that have grown by more than 1% of the database size, in one hour.

First I must mention that this uses only the part of AWR which is not subject to additional option. This even works in Standard Edition:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE

So here is the query, easy to modify with different threshold:
set echo on pagesize 1000
set sqlformat ansiconsole
select * from (
select
round(sum(SPACE_ALLOCATED_DELTA)/1024/1024/1024) GBYTE_ALLOCATED
,trunc(max(end_interval_time),'hh24') snap_time
,round(sum(SPACE_ALLOCATED_DELTA)/1024/1024/1024*24*(cast(max(end_interval_time) as date)-cast(min(begin_interval_time) as date))) "GB/hour"
,owner,object_name,subobject_name,object_type
from DBA_HIST_SEG_STAT join DBA_HIST_SEG_STAT_OBJ using (dbid,ts#,obj#,dataobj#) join dba_hist_snapshot using(dbid,snap_id)
group by trunc(end_interval_time,'hh24'),owner,object_name,subobject_name,object_type
) where "GB/hour" > (select sum(bytes)/1024/1024/1024/1e2 "one percent of database size" from dba_data_files)
order by snap_time
;

and the sample output, showing only the snapshots and segments where more than 1% of the database size has been allocated within one hour:

GBYTE_ALLOCATED SNAP_TIME GB/hour OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
--------------- --------- ------- ----- ----------- -------------- -----------
4 25-OCT-2017 19:00:00 4 BIGDATA SYS_LOB0000047762C00006$$ LOB
9 25-OCT-2017 20:00:00 9 BIGDATA SYS_LOB0000047762C00006$$ LOB
9 25-OCT-2017 21:00:00 9 BIGDATA SYS_LOB0000047762C00006$$ LOB
3 25-OCT-2017 22:00:00 3 BIGDATA SYS_LOB0000047762C00006$$ LOB
5 26-OCT-2017 00:00:00 5 BIGDATA SYS_LOB0000047762C00006$$ LOB
6 26-OCT-2017 01:00:00 6 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 02:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 03:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
7 26-OCT-2017 04:00:00 7 BIGDATA SYS_LOB0000047762C00006$$ LOB
5 26-OCT-2017 05:00:00 5 BIGDATA SYS_LOB0000047762C00006$$ LOB
2 26-OCT-2017 06:00:00 2 BIGDATA SYS_LOB0000047719C00008$$ LOB
2 26-OCT-2017 06:00:00 2 BIGDATA SYS_LOB0000047710C00006$$ LOB

With this, it is easier to ask to the application owners if this growth is normal or not.

 

Cet article Quick history on database growth est apparu en premier sur Blog dbi services.

National Restaurant Association Educational Foundation and Oracle Hospitality Empower Students to Get Creative about Future of Hospitality

Oracle Press Releases - Thu, 2017-11-02 08:00
Press Release
National Restaurant Association Educational Foundation and Oracle Hospitality Empower Students to Get Creative about Future of Hospitality New report highlights innovative culinary and restaurant management concepts developed by students participating in the national ProStart Invitational

Redwood Shores, Calif.—Nov 2, 2017

The National Restaurant Association Educational Foundation (NRAEF) and Oracle Hospitality present “A Look Inside the National ProStart Invitational.” The report showcases the innovative restaurant and culinary concepts developed by high school students who are enrolled in ProStart®, a two-year career and technical education program in secondary schools nationwide that teaches culinary arts and restaurant management skills and fundamentals.

“Oracle Hospitality is extremely proud to be supporting the 2017 National ProStart Invitational—it gives the students a life-changing opportunity to find a career in this exciting industry of ours, while simultaneously helping our customers, restaurant operators, in finding new talent for them to recruit,” said Brett R. Smith, Senior Director, Food and Beverage Solutions Management, Oracle Hospitality. “This report offers us a glimpse into the creative innovative minds of the next generation foodservice industry.”

Nearly 400 students who participated in this year’s National ProStart Invitational presented concepts to a panel of industry judges. Based on qualitative data collected from the students’ submitted materials, the NRAEF tracked methods, ingredients used, restaurant concepts and students inclusion of technology and innovation to determine the core themes of the competition and ultimately the report findings.

This year’s report unveils themes of sustainability, philanthropy and efficiency. It also showcases innovation, not only with food ingredients and various culinary concepts, but students’ desire to use technology to enhance the food service experience overall.

“Once again, we’re proud to partner with Oracle to highlight insights from students at our National ProStart Invitational,” said Rob Gifford, Executive Vice President, NRAEF. “The report showcases how much students learn from ProStart, and the students’ dedication to coming up with new and creative concepts and possible trends in the restaurant and foodservice industry.”

Report highlights include:

Students incorporated innovative technology into their restaurant concepts:

  • Students used restaurant floor planning software to design a layout of their concepts, designed websites as a marketing technique, created social media campaigns, ideated smartphone applications, and allowed ordering through tablets
  • Teams employed different point-of-sale systems, allowing customers to place orders through tablets, kiosks, or mobile applications
  • The use of technology was not simply limited to the customer and employee experiences—teams leveraged solar panels, biogas generator, and geothermal energy to sustain restaurant operations

Students showcased their culinary skills through new techniques:

  • Seafood was the overwhelming favorite ingredient used for appetizers
  • Standout ingredients included wakame seaweed, quail eggs and game meats
  • Teams exercised pickling techniques across starters, entrees and desserts—from chicken wings to lemon curd
  • Required to showcase certain knife skills, students chose chiffonade, julienne, small dice, brunoise and rondelle

Students promoted sustainability, philanthropy and efficiency across concepts:

  • Concepts centered on locally-sourced, health-conscious and fusion cuisine; more than half of the menus featured gluten free, vegan and vegetarian options
  • Concepts presented were classified as either casual dining, quick casual/fast casual or quick service, which illustrated a critical trend in the industry—a shift away from traditional, sit-down, in-restaurant dining
  • Nearly 50 percent of management teams’ concepts promoted community engagement and contained a philanthropic component, such as a culinary internship for at-risk youth, a book donation drop, a pay-it-forward giving model, and a weekly fundraising night for local non-profits
  • Concepts also incorporated unique food delivery services at popular on-the-go venues, such as the airport or gym, to effectively reach target audiences

“A Look Inside the National ProStart Invitational” and an accompanying infographic can be found here: https://go.oracle.com/LP=60748?elqcampaignid=104070.

As a sponsor of the ProStart program Oracle Hospitality is actively taking a role in empowering the next generation of hospitality. Oracle Hospitality provides comprehensive solutions that elevate guest experiences while streamlining operations with point of sale, kitchen and back office management and mobile technologies. Oracle Hospitality tools ensure that food and beverage operations with thousands of workstations or single property locations have the same ability to deliver brand loyalty and customer loyalty.

Reaching nearly 140,000 students annually, ProStart is offered in more than 1,800 high schools and career and technical education centers in all states, Guam and Department of Defense Education Activity schools in Europe and the Pacific. For more information on the ProStart program, visit ChooseRestaurants.org/ProStart or find us on Twitter or Facebook.

Contact Info
Matt Torres
Oracle PR
+1.415.595.1584
matt.torres@oracle.com
Patty Oien
Blanc & Otus
+1.510.303.7289
patty.oien@blancandotus.com
Jasmine Jones
NRAEF
202-315-4101
jajones@nraef.org
About the National Restaurant Association Educational Foundation

As the philanthropic foundation of the National Restaurant Association, the National Restaurant Association Educational Foundation’s mission of service to the public is dedicated to enhancing the industry’s training and education, career development and community engagement efforts. The NRAEF and its programs work to Attract, Empower and Advance today’s and tomorrow’s restaurant and foodservice workforce. NRAEF programs include: ProStart®—a high-school career and technical education program; Restaurant Ready—partnering with community based organizations to provide “opportunity youth” with skills training and job opportunities; Military—helping military servicemen and women transition their skills to restaurant and foodservice careers; Scholarships—financial assistance for students pursuing restaurant, foodservice and hospitality degrees; and, the Hospitality Sector Registered Apprenticeship Project—a partnership with the American Hotel & Lodging Association providing a hospitality apprenticeship program for the industry. For more information on the NRAEF, visit ChooseRestaurants.org.

About Oracle Hospitality

Oracle Hospitality brings 35 years of experience in providing technology solutions to food and beverage operators. We provide hardware, software, and services that allow our customers to deliver exceptional guest experiences while maximizing profitability. Our solutions include integrated point-of-sale, loyalty, reporting and analytics, inventory and labor management, all delivered from the cloud to lower IT cost and maximize business agility. For more information about Oracle Hospitality, please visit www.Oracle.com/Hospitality.

About Oracle

The Oracle Cloud delivers hundreds of SaaS applications and enterprise-class PaaS and IaaS services to customers in more than 195 countries and territories while processing 55 billion transactions a day. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Matt Torres

  • +1.415.595.1584

Patty Oien

  • +1.510.303.7289

Jasmine Jones

  • 202-315-4101

nVision Performance Tuning: 5. Additional Instrumentation of nVision

David Kurtz - Thu, 2017-11-02 07:28
This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

One of the challenges of tuning and monitoring nVision is to be able to identify each report being run. Calls to Oracle instrumentation package dbms_application_info  were added to the component processor in PeopleTools 8.50, and to Application Engine in PeopleTools 8.52.  However, COBOL, nVision, and SQR were never instrumented.
Therefore, there is still a place for the psftapi package and trigger.  When a PeopleSoft batch process starts, it sets the status on its request record on the Process Scheduler request record, psprcsrqst to 7, thus indicating that it is processing.  A trigger on that table fires on that update and calls the psftapi package.  The package sets module and action to the process name and process instance, and also stored the process instance number in a package global variable that can be read with another procedure in the package.  Every scheduled process will have module and action set to something meaningful.  Any PeopleSoft instrumentation will simply overwrite these values.  A sessions module and action are picked up Oracle monitoring tools, in particular, they are also stored in the Active Session History (ASH).
However, nVision reports always run as the same process name, either NVSRUN for a single report, RPTBOOK for a report book of many reports, or DRILLDWN for a nVision drill-down query.  Knowing the process instance is useful because then we can look up the operator and run control ID
However, we also need to know the report ID being run.  When each individual nVision report starts it queries the runtime parameters from the PS_NVS_REPORT PeopleTools table.  There is no update, so it is not possible to capture this with a DML trigger.  Instead, you can create a fine-grained audit policy on the query with a PL/SQL handler.  The handler package is then invoked by the audit policy.
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'SYSADM',
object_name => 'PS_NVS_REPORT',
policy_name => 'PS_NVS_REPORT_SEL',
handler_module => 'AEG_FGA_NVISION_HANDLER',
enable => TRUE,
statement_types => 'SELECT',
audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
END;
/
The handler package runs in the session that triggered the audit.  It can access the audit record and extract the string of colon-separated bind variables thus obtaining the report ID and business unit.  It updates the session action attribute in the same way as psftapi.sql.
CREATE OR REPLACE PROCEDURE sysadm.aeg_fga_nvision_handler
(object_schema VARCHAR2
,object_name VARCHAR2
,policy_name VARCHAR2)
AS
l_sqlbind VARCHAR2(4000);
l_parm1 VARCHAR2(30);
l_parm2 VARCHAR2(30);
l_parm3 VARCHAR2(30);
l_parm4 VARCHAR2(30);
BEGIN
BEGIN
SELECT x.lsqlbind
, SUBSTR(x.lsqlbind,x.start1,LEAST(30,NVL(x.end1,x.lensqlbind+1)-x.start1)) parm1
, SUBSTR(x.lsqlbind,x.start2,LEAST(30,NVL(x.end2,x.lensqlbind+1)-x.start2)) parm2
, SUBSTR(x.lsqlbind,x.start3,LEAST(30,NVL(x.end3,x.lensqlbind+1)-x.start3)) parm3
, SUBSTR(x.lsqlbind,x.start4,LEAST(30,NVL(x.end4,x.lensqlbind+1)-x.start4)) parm4
INTO l_sqlbind, l_parm1, l_parm2, l_parm3, l_parm4
FROM (
SELECT l.*
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,1,1,'i'),0) start1
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,2,0,'i'),0) end1
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,2,1,'i'),0) start2
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,3,0,'i'),0) end2
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,3,1,'i'),0) start3
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,4,0,'i'),0) end3
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,4,1,'i'),0) start4
, NULLIF(REGEXP_INSTR(lsqlbind,' #[0-9]+\([0-9]+\)\:',1,5,1,'i'),0) end4
, LENGTH(lsqlbind) lensqlbind
FROM sys.fga_log$ l
) x
WHERE x.sessionid = USERENV('SESSIONID')
AND x.entryid = USERENV('ENTRYID')
AND x.obj$name = 'PS_NVS_REPORT';
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20000,'AEG_FGA_NVISION_HANDER: No Audit Row');
END;

IF l_parm4 IS NULL THEN
l_parm4 := l_parm3;
l_parm3 := l_parm2;
l_parm2 := l_parm1;
END IF;

IF l_parm4 IS NULL THEN
l_parm4 := l_parm3;
l_parm3 := l_parm2;
END IF;

IF l_parm4 IS NULL THEN
l_parm4 := l_parm3;
END IF;

dbms_output.put_line(l_sqlbind);
dbms_output.put_line(l_parm1);
dbms_output.put_line(l_parm2);
dbms_output.put_line(l_parm3);
dbms_output.put_line(l_parm4);

dbms_application_info.set_action(SUBSTR('PI='||psftapi.get_prcsinstance()||':'||l_parm4||':'||l_parm3,1,64));
--EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''PI='||psftapi.get_prcsinstance()||':'||l_parm4||':'||l_parm3||'''';
END;
/
The action attribute is then picked up by the ASH data.  It is easy to extract the report ID and business unit from the action string with regular expressions, as in this example query.
set lines 160 trimspool on
column module format a12
column action format a32
column client_id format a12
column prcsinstance format a9 heading 'Process|Instance'
column business_unit format a8 heading 'Business|Unit'
column report_id format a10
select DISTINCT module, action, client_id
, REGEXP_SUBSTR(h.action,'[[:digit:]]+') prcsinstance
, substr(regexp_substr(h.action,':([[:alnum:]])+',1,2),2) business_unit
, substr(regexp_substr(h.action,':([A-Za-z0-9_-])+',1,1),2) report_id
from v$active_session_History h
where program like 'PSNVS%'
/
Here you can see how process instance, report ID and business unit are held in action and how they can be extracted.  Now, it is possible to profile ASH data for nVision processes, find the long-running SQL and determine which layout it came from.
                                                           Process   Business
MODULE ACTION CLIENT_ID Instance Unit REPORT_ID
------------ -------------------------------- ------------ --------- -------- ----------
RPTBOOK PI=1780508:GBGL224S:UK001 GBNVISION 1780508 UK001 GBGL113S
RPTBOOK PI=1780509:GBGL010E:UK002 GBNVISION 1780509 UK002 GBGL010E
RPTBOOK PI=1780502:GBGL91PF:UK001 GBNVISION 1780502 UK001 GBGL91PF
RPTBOOK PI=1780502:GBGL91FR:UK001 GBNVISION 1780502 UK001 GBGL91FR
RPTBOOK PI=1780502:GBGL91GB:UK001 GBNVISION 1780502 UK001 GBGL91GB
RPTBOOK PI=1780502:GBGL91DM:UK002 GBNVISION 1780502 UK002 GBGL91DM
RPTBOOK PI=1780506:GBEXP2AM:UK001 GBNVISION 1780506 UK001 GBEXP2AM
RPTBOOK PI=1780509:Processing GBNVISION 1780509 Processing
RPTBOOK PI=1780500:GBGL113S:UK003 GBNVISION 1780500 UK003 GBGL113S
RPTBOOK PI=1780509:GBGL010E:UK000 GBNVISION 1780508 UK000 GBGL010E 
This code in this blog is available on github.
Other recommendations
  • Create an index on SYS.FGA_LOG$ to support the query in the FGA handler package.
CREATE INDEX sys.fga_log$_obj$name
ON sys.fga_log$ (obj$name, sessionid, entryid)
TABLESPACE sysaux PCTFREE 1 COMPRESS 1
/
  • Put a regular purge of the FGA_LOG$ table in place, to purge rows after, say, 31 days.  Otherwise, it will grow indefinitely, one row will be added for every nVision report run.
DELETE FROM fga_log$ 
WHERE obj$name = 'PS_NVS_REPORT'
AND ntimestamp#
  • Move SYS.AUD$ and SYS.FGA_LOG$ from the SYSTEM tablespace to another ASSM tablespace using the instructions in Oracle support note 1328239.1.

Live Demo: Create Database on Cloud & FREE Trainings This Week

Online Apps DBA - Thu, 2017-11-02 04:59

[K21Academy Weekly Newsletter] 171102 Subject: Live Demo: Create Database on Cloud & FREE Trainings This Week Interesting This Week: 1. Create Oracle Database on Cloud 2. FREE Information/Guides This Week 2.1 Role of DBA in Cloud 2.2 7 Docs every Apps DBA must read before Integrating OAM 2.3 EBS (R12)-OAM Integration: Troubleshooting 3. Useful FREE […]

The post Live Demo: Create Database on Cloud & FREE Trainings This Week appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Are large objects supported in PostgreSQL 10 logical replication

Yann Neuhaus - Thu, 2017-11-02 01:32

Another interesting topic that popped up last week during pgconfeu: Are large objects supported with logical replication in PostgreSQL 10? The only truth is a test, isn’t it? Lets go…

Obviously we need a table containing same large objects to start with:

postgres=# create table t1 ( a int, b oid);
CREATE TABLE

Before inserting some data lets create a publication for that table right now:

postgres=# create publication my_pub for table t1;
CREATE PUBLICATION

Ok, that works. Now we need a subscription for that, so on a second instance:

postgres=# create table t1 ( a int, b oid);
CREATE TABLE
postgres=# create subscription my_sub connection 'host=localhost port=6000 dbname=postgres user=postgres' publication my_pub;
CREATE SUBSCRIPTION

So far, so good. Lets insert some data on the publishing instance and see what happens:

postgres=# \! which cp
/usr/bin/cp
postgres=# insert into t1 (a,b) values (1, lo_import('/usr/bin/cp'));
INSERT 0 1

That worked. What do we see on the subscription side?

postgres=# select * from t1;
 a |   b   
---+-------
 1 | 16418
(1 row)

postgres=# select * from pg_size_pretty ( pg_relation_size ( 't1' ) );
 pg_size_pretty 
----------------
 8192 bytes
(1 row)

So, at least “something” is there. Lets prove it:

postgres=# select lo_export(b,'/tmp/cp') from t1;
ERROR:  large object 16418 does not exist
postgres=# 

Hm, this is not what was expected, right? Doing the same on the publishing side works:

postgres=# select lo_export(b,'/tmp/cp') from t1;
 lo_export 
-----------
         1
(1 row)

postgres=# \! chmod +x /tmp/cp
postgres=# \! /tmp/cp --help | head -1
Usage: /tmp/cp [OPTION]... [-T] SOURCE DEST

This means the OID is replicated but not the large object itself. So the answer is: No, large objects can not be used with PostgreSQL 10 logical replication.

 

Cet article Are large objects supported in PostgreSQL 10 logical replication est apparu en premier sur Blog dbi services.

Pass Summit 2017

Yann Neuhaus - Wed, 2017-11-01 23:52

Today starts the Pass Summit 2017 taking place in Seattle.
After a small fly over the Ocean, more than 10 hours… yesterday, and a nice jet lag which avoid me to sleep later than 4AM this morning, I arrived to the Convention Center in Seattle where the Pass takes place.

IMG_9474[1]

I start this first day by the session of Itzik Ben-Gan: T-SQL Tips and Tricks.
As part of the session, Itzik spoke about batch processing (start with 2012) which boost the execution of T_SQL script compare to Row execution mode.
The problem is that Batch mode is just available with columnstore indexes. So if you don’t have a columnstore index in your table you cannot benefit of this feature.
To cheat this drawback Itzik showed us the possibility to create a filter columnstore index (filter CI start with 2016) which will return no row but will enable the possibility to use batch processing.
Well done!

After a quick lunch, I continue this first day by the Session of Drew Furgiuele:

PowerShell

After having explained why to use PowerShell (automation, bridge between tools…) and how to install the SQLSERVER module (Install-Module SQLSERVER or Save-Module SQLServer), Drew shown how to use this module.
The first interesting point is how to browse SQL Server once the module has been installed.
For that just execute the PS script:

cd SQLSERVER:\

And after connection to your SQL Server instance with cd sql\<servername>\default for a SQL Server default instance or \<instancename> for a named instance it’s possible to browse your complete instance as you can do via SQL Server Management Studio with commands like:

$dbs = Get-Item
$dbs = Get-Item ¦ where-object {$_.name -eq AdventureWorks2104}

Easy for a fist step with PowerShell.
Of course Drew showed us really more with PowerShell scripts copying tables from an instance to an other one, managing backups identically in your whole environment or executing a Point in time restore.
Well done Drew.

The last session of the day as 2 parts and is driven by Glenn Berry about Migration to SQL Server 2017.
Glenn explained that there is plenty Reasons to upgrade to SQL Server 2017: great new features, features available with Standard Edition (start with 2016 SP1)…
But he also pointed that there is also big performance differences between Standard and Enterprise Edition with examples using columnstore indexes or when running a dbcc checkdb.
So it’s not just new features that are available with Enterprise Edition, it could also provide great performance gain which is often forgotten.
There is also limitation for memories, sockets and physical cores usage with Standard Edition, don’t build a Virtual Machine for a Standard Edition with too many memories or sockets/cores because it will not be able to use them ;-) You can learn more on Glenn Berry’s blog.

This first day was very great with lot’s of interesting sessions.
It’s time now to visit a little bit Seattle and waiting tomorrow for the second day with some other great sessions and speakers!

 

 

Cet article Pass Summit 2017 est apparu en premier sur Blog dbi services.

Monitoring Standby – SQLPlus or DGMGRL

Michael Dinh - Wed, 2017-11-01 22:00

Here is an example using dgmgrl


DGMGRL> show database roverdb SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
           roverstby     ARCHIVED        936921167                1                13019  10/31/2017 10:47:04  10/31/2017 10:48:31     746413367424     746413483999          1819004 
           roverstby     ARCHIVED        936921167                1                13023  10/31/2017 10:51:40  10/31/2017 10:52:19     746413767648     746413883688          1809094 
           roverstby     ARCHIVED        936921167                1                13031  10/31/2017 10:57:02  10/31/2017 10:57:44     746414728981     746414851377          1924909 
           roverstby     ARCHIVED        936921167                1                13032  10/31/2017 10:57:44  10/31/2017 10:58:23     746414851377     746414967877          1815042 
           roverstby     ARCHIVED        936921167                1                13033  10/31/2017 10:58:23  10/31/2017 10:59:02     746414967877     746415089206          1798857 
           roverstby     ARCHIVED        936921167                1                13034  10/31/2017 10:59:02  10/31/2017 10:59:41     746415089206     746415217514          1818919 
                          CURRENT        936921167                1                13036  10/31/2017 11:29:41                          746415239037                               628 
                          CURRENT        936921167                2                12359  10/31/2017 11:29:05                          746415238854                               864 

DGMGRL> show database roverstby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
         NOT_APPLIED        936921167                1                13020  10/31/2017 10:48:31  10/31/2017 10:48:49     746413483999     746413509640           385949 
         NOT_APPLIED        936921167                1                13021  10/31/2017 10:48:49  10/31/2017 10:50:19     746413509640     746413636246          1885417 
         NOT_APPLIED        936921167                1                13022  10/31/2017 10:50:19  10/31/2017 10:51:40     746413636246     746413767648          1944637 
         NOT_APPLIED        936921167                1                13024  10/31/2017 10:52:19  10/31/2017 10:52:58     746413883688     746413999759          1819116 
         NOT_APPLIED        936921167                1                13025  10/31/2017 10:52:58  10/31/2017 10:53:40     746413999759     746414124264          1868420 
         NOT_APPLIED        936921167                1                13026  10/31/2017 10:53:40  10/31/2017 10:54:22     746414124264     746414244619          1890478 
         NOT_APPLIED        936921167                1                13027  10/31/2017 10:54:22  10/31/2017 10:55:02     746414244619     746414363387          1843514 
         NOT_APPLIED        936921167                1                13028  10/31/2017 10:55:02  10/31/2017 10:55:41     746414363387     746414484244          1818826 
         NOT_APPLIED        936921167                1                13029  10/31/2017 10:55:41  10/31/2017 10:56:20     746414484244     746414605367          1813344 
         NOT_APPLIED        936921167                1                13030  10/31/2017 10:56:20  10/31/2017 10:57:02     746414605367     746414728981          1904385 
         NOT_APPLIED        936921167                1                13035  10/31/2017 10:59:41  10/31/2017 11:29:41     746415217514     746415239037            79395 
   PARTIALLY_APPLIED        936921167                2                12352  10/31/2017 10:40:04  10/31/2017 10:47:07     746413130730     746413371576             1980 
         NOT_APPLIED        936921167                2                12353  10/31/2017 10:47:07  10/31/2017 10:50:22     746413371576     746413640990             1658 
         NOT_APPLIED        936921167                2                12354  10/31/2017 10:50:22  10/31/2017 10:53:01     746413640990     746414010894             1774 
         NOT_APPLIED        936921167                2                12355  10/31/2017 10:53:01  10/31/2017 10:55:04     746414010894     746414371654             1541 
         NOT_APPLIED        936921167                2                12356  10/31/2017 10:55:04  10/31/2017 10:57:04     746414371654     746414736501             1532 
         NOT_APPLIED        936921167                2                12357  10/31/2017 10:57:04  10/31/2017 10:59:04     746414736501     746415097318             1485 
         NOT_APPLIED        936921167                2                12358  10/31/2017 10:59:04  10/31/2017 11:29:05     746415097318     746415238854             6101 

Limitations of REGEXP functions

Tom Kyte - Wed, 2017-11-01 19:46
Tom: I am trying to use this function to validate email addresses. I am reading that the regexp functions have a limitation of 512 bytes. Is there a way around this or a better way? Thanks! Mitchell <code>CREATE OR REPLACE FUNCTION is_valid_e...
Categories: DBA Blogs

Index organized table, secondary indexes, and stale guesses

Tom Kyte - Wed, 2017-11-01 19:46
Hi, Tom Secondary indexes of IOT have physical guesses. However guesses can become stale after many inserts. It is two ways to obtain fresh guesses: 1. Alter index ... rebiuld and 2. ALTER INDEX ... UPDATE BLOCK REFERENCES. 3. What is be...
Categories: DBA Blogs

How to calculate free space in the Database

Tom Kyte - Wed, 2017-11-01 19:46
How to calculate the actual database size and the free space in the database. I tried below queries <code>select round(sum(bytes)/1024/1024/1024) size_in_gb from dba_data_files; = 1073 GB select round(sum(bytes)/1024/1024/1024) size_in_gb from dba...
Categories: DBA Blogs

Unilab Turns to NetSuite OneWorld for a Modern, Cloud-based System to Transform B2B Operations

Oracle Press Releases - Wed, 2017-11-01 19:00
Press Release
Unilab Turns to NetSuite OneWorld for a Modern, Cloud-based System to Transform B2B Operations Philippines' largest pharmaceutical company improves efficiency, transparency in B2B healthcare distribution channel

San Mateo, Calif., and Makati Citry, Philippines—Nov 2, 2017

Oracle NetSuite, one of the world’s leading providers of cloud-based financials / ERPHRProfessional Services Automation (PSA) and omnichannel commerce software suites, announced today that Unilab, the largest pharmaceutical company in the Philippines, has implemented NetSuite OneWorld to help power its 21 distributors by setting them up as individual business partners. Unilab upgraded from a 15-year-old locally developed application to a unified cloud ERP system, enabling its distributors to easily manage inventory and billing processes of around 10,000 trade accounts such as drug stores, clinics, and groceries. Unilab is also using OneWorld for sales and data consolidation and multi-subsidiary management. Since completing the NetSuite OneWorld implementation in January 2017, Unilab has streamlined operations for its distributors and has gained greater visibility into the channel, which accounts for a significant percentage of its US $1 billion annual revenue. Unilab is the first in the industry to leverage cloud ERP to standardize and stabilize its distributor management program through its project called iSERV 2.0.

Founded in 1945, Unilab manufactures over 350 brands of over-the-counter and prescription medications and personal health care products. The 4,000-person company, based in Mandaluyong in greater Manila, has maintained more than 20-percent market share in the Philippines for more than three decades. To help support continued growth and keep up with the changing times, Unilab needed to modernize from an on-premise system used by distributors to a flexible and scalable cloud-based system. Previously, Unilab’s business leaders had to manually consolidate and track data from distributors. Unilab realized it would need a new modern system that did not need to rely on servers scattered across the corporate landscape.

As part of its business continuity plan, Unilab also wanted to transition to the cloud as a disaster-protection measure that would stabilize the entire system during unexpected events like typhoons.

After evaluating several software options, Unilab selected NetSuite OneWorld as an agile, scalable cloud platform ideal to improve efficiency, visibility and standardization in the distribution channel.  NetSuite Solution Provider CloudTech played a key role, successfully and seamlessly implementing NetSuite at Unilab’s distributors.

With NetSuite OneWorld, Unilab has been able to realize its goals of real-time data visibility, streamlined distribution process, simplified data consolidation, and strengthened compliance while providing disaster protection through its cloud-based architecture.

NetSuite OneWorld supports 190 currencies, 20 languages, automated tax calculation and reporting in more than 100 countries, and transactions in more than 200 countries.

With NetSuite OneWorld, Unilab has also realized the following benefits:

Channel efficiency and visibility. Today, distributors use NetSuite for transactions with its trade accounts such as managing the inventory and billing process. Distributors are also able to monitor accounts receivable, inventory status, order status, and credit limits in NetSuite, while Unilab can better track vital data in real time.

Improved compliance. NetSuite gives Unilab better inventory management with lot-tracking capabilities to support compliance of distributors with the FEFO (first expiration, first out) distribution.

Multi-subsidiary management. With OneWorld, Unilab is able to centrally manage each of its 21 distributors.  

Contact Info
Mary Joy Katrina R. Contreras
NetSuite
+63 920 926 3024
PR@netsuite.com
About Oracle NetSuite

Oracle NetSuite pioneered the Cloud Computing revolution in 1998, establishing the world's first company dedicated to delivering business applications over the internet. Today, it provides a suite of cloud-based financials / Enterprise Resource Planning (ERP), HR and omnichannel commerce software that runs the business of companies in more than 100 countries. For more information, please visit http://www.netsuite.com.

Follow NetSuite's Cloud blog, Facebook page and @NetSuite Twitter handle for real-time updates.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.

Trademarks

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. 

Talk to a Press Contact

Mary Joy Katrina R. Contreras

  • +63 920 926 3024

PASS SUMMIT 2017 – SQL Server Security

Yann Neuhaus - Wed, 2017-11-01 18:05

Today is the first day of the PASS SUMMIT 2017 in Seattle (WA). The weather is cloudy and we have only 11°C… but where is the problem? Everything happens inside! (at the Convention Center).

IMG_9474[1]

In this blog, I will make a summary of main attack vectors against MSSQL environments, based on Argenis FERANDEZ’s session called “Modern Security Attack Vectors Against SQL Server Environments”.

METASPLOIT

Metasploit is a penetration testing framework to exploit known security vulnerabilities. This tool is able to scan a server by providing an IP address, and to list all security vulnerabilities you can find on TechNet.

After your environment has been scanned, you can exploit these vulnerabilities on every non-patched server. This kind of tool remind us how it is important to keep environments up-to-date with security updates!

Metasploit can also be used to hack SQL Server login password with a Brute-Force method. Time to remain Windows Logins are recommended over SQL Logins.

 

PowerSploit

PowerSploit is a collection of PowerShell modules (CodeExecution, ScriptModification, Exfiltration…) which can be used to exploit information / data from a compromised machine. This module includes the famous Mimikatz cmdlet which can be used to extract plaintext passwords, hash, PIN code and Kerberos tickets from memory.

 

SQLMAP

sqlmap is a penetration testing tool. It can detect and exploit different SQL injection types, like Boolean-based blind or Time-based blind.

 

RDP Hijacking

Imagine a Domain Admin (or a SQL Admin) connects to a remote Windows server. When his work is done, he disconnects from his session (so the session is still available). It happens to many administrators, doesn’t it? And now imagine this Windows server has been compromised and the hacker has local administrator privileges. He is able to hijack the domain admin session, and so retrieve all his privileges…

But how is it possible? You can either use PSEXEC Sysinternals tool (but it needs to be there), or either create a service which will hijack user’s session. You can find the demonstration made by Alexander Korznikov.

As a consequence, it highly recommended to completely logoff from your rdp sessions!

 

CONCLUSION

This session was pretty interesting because it provided various approaches which can be used to attack your MSSQL environment. It also provided different best practices to take care about, and I know I will always log off from my sessions ;-)

 

Cet article PASS SUMMIT 2017 – SQL Server Security est apparu en premier sur Blog dbi services.

November/December Online Class Offerings Posted

Jim Marion - Wed, 2017-11-01 16:40

We posted our online class offerings for November and December, 2017. You can find details on our website. Our Fluid classes have been extremely popular, so we will continue offering those at least once per month. Our Fluid 1 class is designed to get a developer up and running with Fluid as fast as possible. Through hands-on activities, you will use familiar tools and existsing skills to build Fluid components. No web development experience necessary. If you are a PeopleSoft developer, then Fluid 1 will teach you what you need to know to build Fluid pages. But there is more to Fluid than just dragging and dropping fields on a page. Fluid 2 takes your Fluid game to the next level by teaching you Fluid design patterns and Fluid techniques to build Fluid pages the way Oracle builds Fluid pages.

November, 2017 Course Title Date Start Time End Time Duration   AWE Nov. 7, 2017 9:00 AM PDT 4:00 PM PDT 2 days Register Fluid 1 Nov. 27, 2017 9:00 AM PDT 4:00 PM PDT 2 days Register Fluid 2 Nov. 29, 2017 9:00 AM PDT 4:00 PM PDT 2 days Register


December, 2017 Course Title Date Start Time End Time Duration   Fluid 1 December 11, 2017 9:00 AM PDT 4:00 PM PDT 2 days Register Fluid 2 December 13, 2017 9:00 AM PDT 4:00 PM PDT 2 days Register PeopleTools
8.55/8.56 Delta December 19, 2017 9:00 AM PDT 4:00 PM PDT 2 days Register

AVM Fritz: WLAN Mesh with Powerline Repeater does not work - Part 2

Dietrich Schroff - Wed, 2017-11-01 10:55
A month ago i tried to get the new WLAN mesh running with my Fritz!Box 7490 and Fritz!Powerline 546E.

Some days ago AVM release a new firmware for the 546E:

But even with the new firmware the powerline adapter does show up with the "mesh symbol" inside the home network overview:


I am wondering, if the WLAN mesh only works by using the powerline connectivity. AVM support pages show up the following advisory:
As you can see: Only the secondary powerline adapter shows the "mesh symbol".

Inside the networking overview my 546E is registered in this way:
Which is definitly wrong, because it is connected as WLAN bridge to the Fritz!Box without any powerline technololgy...

Hopefully the next update may fix this.

Contao: How to create a virtual system from your live website

Dietrich Schroff - Wed, 2017-11-01 09:07
Since a couple of year my sport club runs its website with contao.
Because our webhoster changed the support for php, i have to find a way to do an upgrade without damaging the running website.

The plan:
  1. create a linux host as a vm
  2. install mysql, apache2, php
  3. import the backups
The first step was easy. Inside virtualbox i created an ubuntu 17.10 system.
The second step was not that simple:
add-apt-repository ppa:ondrej/php
apt install mysql-server apache2 php5.6 php5.6-mysqlImporting the data into the database was easy (Before i did a backup via the contao frontend: system -> backup database).
mysql -u root -pYOURPASS -e "create database mydb"
mysql -u root -pYOURPASS mydb < database.backup.sqlFrom the live system i copied everything into /var/www/html and changed the user to www-data:
cd /var/www/html
chown -R www-data *Last step is the customizing of /var/www/html/system/config/localconfig.php
$GLOBALS['TL_CONFIG']['dbHost'] = 'localhost';
$GLOBALS['TL_CONFIG']['dbUser'] = 'root';
$GLOBALS['TL_CONFIG']['dbPass'] = 'YOURPASS';
$GLOBALS['TL_CONFIG']['dbDatabase'] = 'mydb';And after a last command
systemctl restart apache2i was able to open the website via a local firefox with http://localhost/index.php.
Now a snapshot a damaged installation after an upgrade can be rolled back without any problem ;-)

Enable Trusted Content Services (TCS) license in Documentum Content Server

Yann Neuhaus - Wed, 2017-11-01 08:12

The Trusted Content Services is a pack of features that you can enable/purchase to gain more security for your Content Server. The main key features are listed in this documentation:

https://www.emc.com/collateral/software/data-sheet/h3138-security-ds.pdf

In our case, we wanted to benefit from the Repository Encryption feature:

Repository Encryption: Documentum TCS prevents intruders from accessing information even if they obtain unauthorized access to repository files at the file-system or storage level. This capability protects content against an operating system level security breach and enables you to securely store back-up media containing information assets in encrypted form.

To enable the TCS first you must get a license key from OpenText then they will send you a key String. Put that key only in a file located here: $DOCUMENTUM/dba/tcs_license

Then login to the docbase with IAPI and enable TCS for this repo with:

retrieve,c,dm_server_config
set,c,l,r_trusted_mode
1
save,c,l
reinit,c

No need to restart, the TCS license is only checked dynamically when a TCS feature is called.
To see if the license has been properly integrated, login to Documentum Administrator and on the Admin page you should see:
Trusted Mode: Enabled

 

Cet article Enable Trusted Content Services (TCS) license in Documentum Content Server est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator