Feed aggregator

Using function in conjunction with WITH query clause

Tom Kyte - Thu, 2018-10-04 22:06
Bit of a newbie, and hoping I can get pointed in the right direction. I've simplified things to demonstrate the issue I'm experiencing (and I'm really struggling to get a clear answer on other posts). When running the following: <code>with f...
Categories: DBA Blogs

SQL Query to Convert Ten Rows with One Column to Five Rows With One Column

Tom Kyte - Thu, 2018-10-04 22:06
i have a table with column name as value. There are 10 rows in the table. The desired output of this to be displayed as two columns first 5 rows as one column A and rows 6 to 10 as column B , next to each other as 5 rows of data like this <code>A B...
Categories: DBA Blogs

Calculate a variable date value and use it in a where clause to return all rows after that date

Tom Kyte - Thu, 2018-10-04 22:06
Long time SQL user of many flavors but brand new to PL/SQL and struggling to learn the "Oracle way". I've seen MANY examples of using variables in queries online and in documentation, but I've been unsuccessful finding a sample of what I want to do ...
Categories: DBA Blogs

SP execution plan should depend on input parameter

Tom Kyte - Thu, 2018-10-04 22:06
Hi guys, I have a SP having input parameters and the execution plan should depend on the parameters provided to the procedure. Ex : PROCEDURE GetData( DataType int, DataValue int ) I want this procedure to search DataValue in column1 if DataType =...
Categories: DBA Blogs

Understanding SQL Profiles

Tom Kyte - Thu, 2018-10-04 22:06
Hi Tom, My understanding of using SQL Profiles has always been that they would prevent (frequent) changes in access paths of SQL statement. This morning I noticed that, despite the fact that an SQL profile was connected to a statement and statias...
Categories: DBA Blogs

Oracle Utilities Technical Best Practices whitepaper updated

Anthony Shorten - Thu, 2018-10-04 18:21

With the release of Oracle Utilities Application Framework V4.3.0.6.0 the Technical Best Practices whitepaper has been updated with the latest advice and latest information.

The following changes have been made:

  • Overview of the Health Check capability
  • Preparing your Implementation for the Oracle Cloud - An overview of the objects that need to be changed to prepare for the migration from on-premise to the Oracle Cloud
  • Optimization techniques for minimizing costs.

The latest version is located in Technical Best Practices (Doc Id: 560367.1) available from My Oracle Support.

Oracle’s AI-driven Risk Management Makes Corporate Finances More Secure

Oracle Press Releases - Thu, 2018-10-04 11:00
Press Release
Oracle’s AI-driven Risk Management Makes Corporate Finances More Secure Advanced Access Controls parlay AI to help finance teams bolster security and risk analysis

Redwood Shores, Calif.—Oct 4, 2018

To help protect customers from ever-increasing fraud and security threats, Oracle today unveiled the enterprise software industry’s first AI—driven security and risk management solution. Designed specifically for Oracle Enterprise Resource Planning (ERP) Cloud, Oracle Risk Management Cloud’s new Advanced Access Controls enable organizations to continuously monitor for segregation of duties (SOD), financial compliance (SOX), privacy risks, proprietary information and payment risks.

The new controls embed self-learning, artificial intelligence (AI) techniques to constantly examine all users, roles and privileges against a library of active security rules. The offering includes more than 100 best practices (configurable rules) across general ledger, payables, receivables and fixed assets.

“As the pace of business accelerates, organizations can no longer rely on time-consuming manual processes, which leave them vulnerable to fraud and human error,” said Laeeq Ahmed, managing director at KPMG. “With adaptive capabilities and AI, products such as Oracle Risk Management Cloud can help organizations manage access controls and monitor activity at scale to protect valuable data and reduce exposure to risk.”

Key benefits of AI-driven Security & Risk Management include:

  • Continuous protection: Constant monitoring of user and application activity

  • Instant best practices: More than 100 proven ERP security rules

  • Self-learning: Embedded AI and self-learning for precise results

  • Augmented incident response: Ensures that issues are directed to analysts for tracking, investigation and closure

“On-going disruption in the marketplace and regulatory landscape presents continually evolving operational and financial risks,” said Bill Behen, principal at Grant Thornton. “Oracle’s unique approach to risk management and expertise applying AI technology enables organizations to securely move to the cloud and continuously protect their business from a host of external and internal threats.”

The pre-packaged audit-approved security rules automate access analysis during the role design phase to significantly accelerate ERP implementations. In addition, the intuitive workbench, visualization and simulation features within Advanced Access Controls make it easy to add new rules and further optimize user access. Once live, the solution continuously monitors and automatically routes incidents to security analysts.

To help customers analyze complex, recursive and dynamic security data across all users, roles and privileges, Advanced Access Controls uses graph-based analysis and self-learning algorithms. This enables organizations to accurately and reliably review and visualize the entire path by which any user is able to access and execute sensitive functions.

“Advanced Access Controls automate the time-consuming analysis needed to protect business data from insider threats, fraud, misuse and human error,” said Sid Sinha, vice president of Risk Management Cloud Product Strategy, at Oracle. “This service is part of an integrated, practical solution to effectively protect information in business applications using the latest data analysis and exception management techniques.”

For more information on Oracle Advanced Access Controls and Oracle Risk Management Cloud, go to cloud.oracle.com/risk-management-cloud.

To learn more about Risk Management Cloud at Oracle OpenWorld, please visit the sessions catalog.

Contact Info
Bill Rundle
Oracle PR
+1 650 506 1891
bill.rundle@oracle.com
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 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

Bill Rundle

  • +1 650 506 1891

New OA Framework 12.2.6 Update 14 Now Available

Steven Chan - Thu, 2018-10-04 10:31

Web-based content in Oracle E-Business Suite Release 12 runs on the Oracle Application Framework (also known as OA Framework, OAF, or FWK) user interface libraries and infrastructure.

We periodically release updates to Oracle Application Framework to fix performance, security, and stability issues.

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Packs. In this context, cumulative means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for Oracle E-Business Suite Release 12.2.6 is now available:

Oracle Application Framework (FWK) Release 12.2.6 Bundle 14 (Patch 28183913:R12.FWK.C)

Where is this update documented?

Instructions for installing this OAF Release Update Pack are in the following My Oracle Support knowledge document:

Who should apply this patch?

All Oracle E-Business Suite Release 12.2.6 users should apply this patch. Future OAF patches for EBS Release 12.2.6 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS Release 12.2.6 bundle patches.

In addition, this latest bundle patch includes fixes for the following issues:

  • The details of an expanded row are not visible in the viewport area when a table has more than 30 rows.
  • On the iSupport Service Request creation page, the length of the Problem Summary data input field is inadequate.
  • Messages filtered for viruses are not added to the confirmation message in a specific product flow.
  • Validation of required fields is not triggered when a new row is added to a table.
  • With the application session language set to Arabic, in the IE 11 browser a popup does not appear when the Delete button is clicked.

Related Articles

Categories: APPS Blogs

Local Governments to Modernize Community Development with Oracle Cloud

Oracle Press Releases - Thu, 2018-10-04 07:00
Press Release
Local Governments to Modernize Community Development with Oracle Cloud New Public Sector Community Development solution reduces permitting and licensing complexity to drive economic development

Redwood Shores, Calif.—Oct 4, 2018

Oracle has launched Oracle Public Sector Community Development, enabling local governments to improve quality-of-life for their constituents with faster, more efficient land management. A modern cloud-native application for state and local government agencies, the offering transforms cumbersome permitting and licensing into an end-to-end solution for reliable execution of building regulatory processes that can foster economic growth, while helping deliver public safety and accountability.

The new SaaS (Software-as-a-Service) application leverages Oracle’s scalable and secure cloud computing capabilities, advanced analytics and platform development services. The combination of market-leading, emerging technologies such as chatbots and artificial intelligence (AI), and pre-built, easy-to-configure applications, designed specifically for government agencies, modernizes how local government deliver services and results for their citizens and jurisdictions.

“Today’s digitally-savvy citizens and business owners expect to interact with their local government in the same way they do so with commercial companies offering multichannel, always available, 24/7 access to goods and services,” said Mark Johnson, senior vice president, Oracle Public Sector. “The Oracle Public Sector Community Development solution helps make this possible for state and local governments.”

Drive Economic Development While Helping Provide Compliance

Oracle Public Sector Community Development is a fully-configurable, built-for-purpose cloud solution for state and local government that:

  • Modernizes the compliance and regulatory process for land-use and building infrastructure, focusing first on building permits and inspections.

  • Accommodates diverse permit types and associated workflows that are easily configurable and extensible to each agency’s unique needs through built-in process automation tools from Oracle Integration Cloud, designed for use by departmental staff—not constrained IT personnel.

  • Allows frictionless interactions between the public and local government with an intuitive, role-based user experience on any device, enhanced with guided interactions and natural voice interactions.

  • Increases strategic insight and enables better decision making through contextually relevant data visualization, leveraging Oracle’s business intelligence and analytics.

  • Provides deeper permit understanding and greater processing efficiency through integration with Esri’s ArcGIS developer API. Esri is an established provider of location intelligence.

  • Empowers inspectors to ensure public safety and accountability with a mobile application, enforcing code compliance quickly and easily while accelerating occupancy rates.

Lower Costs and Innovate Faster

Oracle Public Sector Community Development is offered as a subscription cloud service. This eliminates the need for capital expenditures, enabling government to innovate faster, and lowers the total cost of ownership since cloud services are rapid to deploy and easy to maintain. “As-a-service” solutions lower the demand on IT staff, scale with the growth of a city or county, and allow government agencies to continuously receive new functionality.

“Governments face technology demands from two directions,” wrote Gartner in a recent report1. “The first is from employees, with consumer technology and employee expectations having surpassed enterprise technology and government workplace experiences. The second is from the pace of technology generally, where expectations of citizens and commercial services have outstripped governments’ ability to keep pace.”

Leveraging its unique scale and extensive investments in research and development, Oracle will continue to rapidly deliver new capabilities to market. Oracle’s roadmap includes expanding its Community Development offerings to include planning and zoning and code enforcement functionality and delivering new applications that address additional regulatory challenges at the local and state level, including business and professional licenses.

By employing open standards and building on the common technology foundation of Oracle’s robust cloud applications for finance, citizen services, procurement, and human capital management, governments can streamline operations and deploy integrated, extensible, end to end cross-agency processes that facilitate a holistic digital platform effort.

Today, thousands of public sector customers use Oracle’s digital solutions to transform how their constituents engage with government. Across the nation, cities and states have harnessed the power of cloud, IoT and analytics and big data to predict and prepare for a better future for all citizens. Read their stories here.  

For more information, go to www.oracle.com/communitydevelopment.

1 Source, Gartner, Inc., A Master CIO in Government, Rick Holgate, Alia Mendonsa, and Alvaro Mello, February 23, 2018.

Contact Info
Katie Barron
Oracle
+1.202.904.1138
katie.barron@oracle.com
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

Katie Barron

  • +1.202.904.1138

[BLOG] 1z0-161 Oracle JAVA Cloud Service (JCS) Certification Roadmap

Online Apps DBA - Thu, 2018-10-04 06:38

Are you looking forward to clear Oracle JAVA Cloud Service Certificate Associate Exam (1Z0-161)? Get clarity about the exam by visiting https://k21academy.com/jcs15 where we cover: ✔Why Java Cloud Service Certification(1Z0-161)? ✔Certificaion Details ✔Oracle 1Z0-161 Certification Topics & much more… Are you looking forward to clear Oracle JAVA Cloud Service Certificate Associate Exam (1Z0-161)? Get clarity […]

The post [BLOG] 1z0-161 Oracle JAVA Cloud Service (JCS) Certification Roadmap appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Understanding Distribution in #Exasol

The Oracle Instructor - Thu, 2018-10-04 04:12
Exasol doesn’t need much administration but getting distribution right matters

Exasol uses a clustered shared-nothing architecture with many sophisticated internal mechanisms to deliver outstanding performance without requiring much administration. Getting the distribution of rows between cluster nodes right is one of the few critical tasks left, though. To explain this, let’s say we have two tables t1 and t2:

The two tables are joined on the column JoinCol, while WHERE conditions for filtering are done with the column WhereCol. Other columns are not shown to keep the sketches small and simple. Now say these two tables are stored on a three-node cluster. Again, for simplicity only active nodes are on the sketch – no reserve nodes or license nodes. We also ignore the fact that small tables will be replicated across all active nodes.

Distribution will be random if no distribution key is specified

Without specifying a distribution key, the rows of the tables are distributed randomly across the nodes like this:

Absence of proper distribution keys: global joins

The two tables are then joined:

SELECT <something> FROM t1 JOIN t2 ON t1.JoinCol = t2.JoinCol;

Internally, this is processed as a global join which means network communication between the nodes on behalf of the join is required. This is the case because some rows do not find local join partners on the same node:

Distribution on join columns: local joins

If the two tables were distributed on their join columns with statements like these

ALTER TABLE t1 DISTRIBUTE BY JoinCol;

ALTER TABLE t2 DISTRIBUTE BY JoinCol;

then the same query can be processed internally as a local join:

Here every row finds a local join partner on the same node so no network communication between the nodes on behalf of the join is required. The performance with this local join is much better than with the global join although it’s the same statement as before.

Why you shouldn’t distribute on WHERE-columns

While it’s generally a good idea to distribute on JOIN-columns, it’s by contrast a bad idea to distribute on columns that are used for filtering with WHERE conditions. If both tables would have been distributed on the WhereCol columns, it would look like this:

This distribution is actually worse than the initial random distribution! Not only does this cause global joins between the two tables as already explained, statements like e.g.

<Any DQL or DML> WHERE t2.WhereCol='A';

will utilize only one node (the first with this WHERE condition) and that effectively disables one of Exasol’s best strengths, the Massive Parallel Processing (MPP) functionality. This distribution leads to poor performance because all other nodes in the cluster have to stand by being idle while one node has to do all the work alone.

Examine existing distribution with iproc()

The function iproc() helps investigating the existing distribution of rows across cluster nodes. This statement shows the distribution of the table t1:

SELECT iproc(),COUNT(*) FROM t1 GROUP BY 1 ORDER BY 1;
Evaluate the effect of distribution keys with value2proc()

The function value2proc() can be used to display the effect that a (new) distribution key would have:

SELECT home_node,COUNT(*) FROM (SELECT value2proc(JoinCol) AS home_node FROM t1) GROUP BY 1 ORDER BY 1;
Conclusion

Distribution on JOIN-columns leads to local joins which perform better than global joins: Do that!

Distribution on WHERE-columns leads to global joins and disables the MPP functionality, both causing poor performance: Don’t do that!

Categories: DBA Blogs

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part I (The Jean Genie)

Richard Foote - Thu, 2018-10-04 03:00
When it comes to indexing a partitioned table, many automatically opt for Local Indexes, as it’s often assumed they’re simply easier to manage and more efficient than a corresponding Global Index. Having smaller index structures that are aligned to how the table is partitioned certainly has various advantages. The focus in this little series is on […]
Categories: DBA Blogs

First steps into SQL Server 2019 availability groups on K8s

Yann Neuhaus - Thu, 2018-10-04 01:27

A couple of weeks ago, Microsoft announced the first public CTP version of next SQL Server version (CTP2). It is not a surprise, the SQL Server vNext becomes SQL Server 2019 and there are a plenty of enhancements as well as new features to discover. But for now, let’s start with likely one of my favorites: availability groups on Kurbernetes (aka K8s). As far I may see from customers and hear from my colleagues as well, we assist to a strong adoption of K8s with OpenShift as a main driver. I would not be surprised to see some SQL Server pods at customer shops in a near future, especially with the support of availability groups on K8s. From my opinion, that is definitely something that was missing in the previous for microservices architectures or not, for either quality or production environments.

blog 143 - 0 - AG K8s

Well, I decided to learn more about this new feature but let’s say this write-up concerns the CTP 2.0 version and chances are things will likely change in the future. So, don’t focus strictly on my words or commands I’m using in this blog post.

It is some time since I used the Service Azure Kubernetes (AKS) and I already wrote about it in a previous blog post. I used the same environment to deploy my first availability group on K8s. It was definitely an interesting experience because it involved getting technical skills about K8s infrastructure.

So, let’s set briefly the context with my K8s cluster on Azure that is composed of 3 agent nodes as shown below:

$ kubectl get nodes -o wide
NAME                       STATUS    ROLES     AGE       VERSION   EXTERNAL-IP   OS-IMAGE             KERNEL-VERSION      CONTAINER-RUNTIME
aks-nodepool1-78763348-0   Ready     agent     126d      v1.9.6    <none>        Ubuntu 16.04.4 LTS   4.13.0-1016-azure   docker://1.13.1
aks-nodepool1-78763348-1   Ready     agent     126d      v1.9.6    <none>        Ubuntu 16.04.4 LTS   4.13.0-1016-azure   docker://1.13.1
aks-nodepool1-78763348-2   Ready     agent     35d       v1.9.6    <none>        Ubuntu 16.04.5 LTS   4.15.0-1023-azure   docker://1.13.1

 

I also used a custom namespace – agdev – to scope my availability group resources names.

$ kubectl get ns
NAME           STATUS        AGE
ag1            Terminating   23h
agdev          Active        10h
azure-system   Active        124d
default        Active        124d
kube-public    Active        124d
kube-system    Active        124d

 

Referring to the Microsoft documentation, the SQL secrets (including master key and SA password secrets) are ready for use:

$ kubectl get secret sql-secrets -n agdev
NAME                   TYPE                                  DATA      AGE
sql-secrets            Opaque                                2         1d

$ kubectl describe secret sql-secrets -n agdev
Name:         sql-secrets
Namespace:    default
Labels:       <none>
Annotations:  <none>

Type:  Opaque

Data
====
masterkeypassword:  14 bytes
sapassword:         14 bytes

 

  • The operator

The first component to deploy is the operator which is a very important component in this infrastructure and that builds upon the basic Kubernetes resource and controller concepts. Kubernetes has a very pluggable way to add your own logic in the form of a controller in addition of existing built-in controllers as the old fashion replication controller, the replica sets and deployments. All of them are suitable for stateless applications but the story is not the same when we have to deal with stateful systems like databases because those system require specific application domain knowledge to correctly scale, upgrade and reconfigure while protecting against data loss or unavailability. For example, how to deal correctly with availability groups during a crash of pod? If we think about it, the work doesn’t consist only in restarting the crashing pod but the system will also have to execute custom tasks in a background including electing of a new primary (aka leader election), ensuring a safe transition during the failover period to avoid split brain scenarios etc.

Deploying the mssql-operator includes the creation of a new pod:

$ kubectl get pods -n agdev -l app=mssql-operator
NAME                              READY     STATUS    RESTARTS   AGE
mssql-operator-67447c4bd8-s6tbv   1/1       Running   0          11h

 

Let’s go further by getting more details about this pod:

$ kubectl describe pod -n agdev mssql-operator-67447c4bd8-s6tbv
Name:           mssql-operator-67447c4bd8-s6tbv
Namespace:      agdev
Node:           aks-nodepool1-78763348-0/10.240.0.4
Start Time:     Mon, 01 Oct 2018 08:12:47 +0200
Labels:         app=mssql-operator
                pod-template-hash=2300370684
Annotations:    <none>
Status:         Running
IP:             10.244.1.56
Controlled By:  ReplicaSet/mssql-operator-67447c4bd8
Containers:
  mssql-operator:
    Container ID:  docker://148ba4b8ccd91159fecc3087dd4c0b7eb7feb36be4b3b5124314121531cd3a3c
    Image:         mcr.microsoft.com/mssql/ha:vNext-CTP2.0-ubuntu
    Image ID:      docker-pullable://mcr.microsoft.com/mssql/ha@sha256:c5d20c8b34ea096a845de0222441304a14ad31a447d79904bafaf29f898704d0
    Port:          <none>
    Host Port:     <none>
    Command:
      /mssql-server-k8s-operator
    State:          Running
      Started:      Mon, 01 Oct 2018 08:13:32 +0200
    Ready:          True
    Restart Count:  0
    Environment:
      MSSQL_K8S_NAMESPACE:  agdev (v1:metadata.namespace)
    Mounts:
      /var/run/secrets/kubernetes.io/serviceaccount from mssql-operator-token-bd5gc (ro)
…
Volumes:
  mssql-operator-token-bd5gc:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  mssql-operator-token-bd5gc
    Optional:    false

 

Some interesting items to note here:

  • The SQL Server CTP image – mcr.microsoft.com/mssql/ha – comes from the new Microsoft Container Registry (MCR). The current tag is vNext-CTP2.0-ubuntu at the moment of this write-up
  • Volume secret is mounted to pass sensitive data that concerns a K8s service account used by the pod. In fact, the deployment of availability groups implies the creation of multiple service accounts
$ kubectl describe secret -n agdev mssql-operator-token-bd5gc
Name:         mssql-operator-token-bd5gc
Namespace:    agdev
Labels:       <none>
Annotations:  kubernetes.io/service-account.name=mssql-operator
              kubernetes.io/service-account.uid=03cb111e-c541-11e8-a34a-0a09b8f01b34

Type:  kubernetes.io/service-account-token

Data
====
namespace:  5 bytes
token:      xxxx
ca.crt:     1720 bytes

 

The command is /mssql-server-k8s-operator that is a binary file like other mssql-server* files packaged in the new SQL Server image and which are designed to respond to different events by appropriated actions like updating K8s resources:

$ kubectl exec -ti -n agdev mssql-operator-67447c4bd8-s6tbv -- /bin/bash
root@mssql-operator-67447c4bd8-s6tbv:/# ll mssql*
-rwxrwxr-x 1 root root 32277998 Sep 19 16:00 mssql-server-k8s-ag-agent*
-rwxrwxr-x 1 root root 31848041 Sep 19 16:00 mssql-server-k8s-ag-agent-supervisor*
-rwxrwxr-x 1 root root 31336739 Sep 19 16:00 mssql-server-k8s-failover*
-rwxrwxr-x 1 root root 32203064 Sep 19 16:00 mssql-server-k8s-health-agent*
-rwxrwxr-x 1 root root 31683946 Sep 19 16:00 mssql-server-k8s-init-sql*
-rwxrwxr-x 1 root root 31422517 Sep 19 16:00 mssql-server-k8s-operator*
-rwxrwxr-x 1 root root 31645032 Sep 19 16:00 mssql-server-k8s-rotate-creds*

root@mssql-operator-67447c4bd8-s6tbv:/# file mssql-server-k8s-operator
mssql-server-k8s-operator: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, not stripped

 

  • The SQL Server instances and AGs

The next step consisted in running the SQL Server AG deployment. Looking at the manifest file, we may notice we deploy custom SQL Server objects (kind: SqlServer) from new mssql.microsoft.com API installed previously as well as their corresponding services to expose SQL Server pods to the external traffic.

The deployment includes 3 StatefulSets that manage pods with 2 containers, respectively the SQL Server engine and its agent (HA supervisor). I was surprised to not see a deployment with kind: StatefulSet but I got the confirmation that the “logic” is encapsulated in the SqlServer object definition. Why StatfulSets here? Well, because they are more valuable for applications like databases by providing, inter alia, stable and unique network identifiers as well as stable and persistent storage. Stateless pods do not provide such capabilities. To meet StafulSet prerequisites, we need first to define persistent volumes for each SQL Server pod. Recent version of K8s allows to use dynamic provisioning and this is exactly what is used in the initial Microsoft deployment file with the instanceRootVolumeClaimTemplate:

instanceRootVolumeClaimTemplate:
   accessModes: [ReadWriteOnce]
   resources:
     requests: {storage: 5Gi}
   storageClass: default

 

However, in my context I already created persistent volumes for previous tests as shown below:

$ kubectl get pv -n agdev
NAME                                       CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS    CLAIM                STORAGECLASS   REASON    AGE
pvc-cb299d79-c5b4-11e8-a34a-0a09b8f01b34   10Gi       RWO            Delete           Bound     agdev/mssql-data-1   azure-disk               9h
pvc-cb4915b4-c5b4-11e8-a34a-0a09b8f01b34   10Gi       RWO            Delete           Bound     agdev/mssql-data-2   azure-disk               9h
pvc-cb67cd06-c5b4-11e8-a34a-0a09b8f01b34   10Gi       RWO            Delete           Bound     agdev/mssql-data-3   azure-disk               9h

$ kubectl get pvc -n agdev
NAME           STATUS    VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
mssql-data-1   Bound     pvc-cb299d79-c5b4-11e8-a34a-0a09b8f01b34   10Gi       RWO            azure-disk     9h
mssql-data-2   Bound     pvc-cb4915b4-c5b4-11e8-a34a-0a09b8f01b34   10Gi       RWO            azure-disk     9h
mssql-data-3   Bound     pvc-cb67cd06-c5b4-11e8-a34a-0a09b8f01b34   10Gi       RWO            azure-disk     9h

 

So, I changed a little bit the initial manifest file for each SqlServer object with my existing persistent claims:

instanceRootVolume:
    persistentVolumeClaim:
      claimName: mssql-data-1

instanceRootVolume:
    persistentVolumeClaim:
      claimName: mssql-data-2

instanceRootVolume:
    persistentVolumeClaim:
      claimName: mssql-data-3

 

Furthermore, next prerequisite for StatefulSet consists in using a headless service and this exactly we may find with the creation of ag1 service during the deployment:

$ kubectl get svc -n agdev
NAME          TYPE           CLUSTER-IP     EXTERNAL-IP     PORT(S)             AGE
ag1           ClusterIP      None           <none>          1433/TCP,5022/TCP   1d

 

I also noticed some other interesting items like extra pods in completed state:

$ kubectl get pods -n agdev -l app!=mssql-operator
NAME                            READY     STATUS      RESTARTS   AGE
mssql-initialize-mssql1-plh8l   0/1       Completed   0          9h
mssql-initialize-mssql2-l6z8m   0/1       Completed   0          9h
mssql-initialize-mssql3-wrbkl   0/1       Completed   0          9h
mssql1-0                        2/2       Running     0          9h
mssql2-0                        2/2       Running     0          9h
mssql3-0                        2/2       Running     0          9h

$ kubectl get sts -n agdev
NAME      DESIRED   CURRENT   AGE
mssql1    1         1         9h
mssql2    1         1         9h
mssql3    1         1         9h

 

In fact, those pods are related to jobs created and executed in a background during the deployment of the SQL Server AG:

$ kubectl get jobs -n agdev
NAME                      DESIRED   SUCCESSFUL   AGE
mssql-initialize-mssql1   1         1            22h
mssql-initialize-mssql2   1         1            22h
mssql-initialize-mssql3   1         1            22h

 

Let’s take a look at the mssql-initialize-mssql1 job:

$ kubectl describe job -n agdev mssql-initialize-mssql1
Name:           mssql-initialize-mssql1
Namespace:      agdev
Selector:       controller-uid=cd481f3c-c5b5-11e8-a34a-0a09b8f01b34
Labels:         controller-uid=cd481f3c-c5b5-11e8-a34a-0a09b8f01b34
                job-name=mssql-initialize-mssql1
Annotations:    <none>
Parallelism:    1
Completions:    1
Start Time:     Mon, 01 Oct 2018 22:08:45 +0200
Pods Statuses:  0 Running / 1 Succeeded / 0 Failed
Pod Template:
  Labels:           controller-uid=cd481f3c-c5b5-11e8-a34a-0a09b8f01b34
                    job-name=mssql-initialize-mssql1
  Service Account:  mssql-initialize-mssql1
  Containers:
   mssql-initialize:
    Image:      mcr.microsoft.com/mssql/ha:vNext-CTP2.0-ubuntu
    Port:       <none>
    Host Port:  <none>
    Command:
      /mssql-server-k8s-init-sql
    Environment:
      MSSQL_K8S_NAMESPACE:              (v1:metadata.namespace)
      MSSQL_K8S_SA_PASSWORD:           <set to the key 'sapassword' in secret 'sql-secrets'>  Optional: false
      MSSQL_K8S_NUM_SQL_SERVERS:       1
      MSSQL_K8S_SQL_POD_OWNER_UID:     cd13319a-c5b5-11e8-a34a-0a09b8f01b34
      MSSQL_K8S_SQL_SERVER_NAME:       mssql1
      MSSQL_K8S_SQL_POST_INIT_SCRIPT:
      MSSQL_K8S_MASTER_KEY_PASSWORD:   <set to the key 'masterkeypassword' in secret 'sql-secrets'>  Optional: false
    Mounts:                            <none>
  Volumes:                             <none>
Events:                                <none>

 

These jobs are one-time initialization code that is executed when SQL Server and the AG is bootstrapped (thank you to @MihaelaBlendea to give more details on this topic) through the mssql-server-k8s-init-sql command. This is likely something you may remove according to your context (if you daily deal with a lot of K8s jobs for example).

Then, the deployment led to create 3 StatefulSets with their respective pods mssql1-0, mssql2-0 and mssql3-0. Each pod contains 2 containers as shown below for the mssql1-0 pod:

$ kubectl describe pod -n agdev mssql1-0
Name:           mssql1-0
Namespace:      agdev
Node:           aks-nodepool1-78763348-1/10.240.0.5
…
Status:         Running
IP:             10.244.0.38
Controlled By:  StatefulSet/mssql1
Containers:
  mssql-server:
    Container ID:   docker://8e23cec873ea3d1ebd98f8f4f0ab0b11b840c54c17557d23817b9c21a863bb42
    Image:          mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu
    Image ID:       docker-pullable://mcr.microsoft.com/mssql/server@sha256:87e691e2e5f738fd64a427ebe935e4e5ccd631be1b4f66be1953c7450418c8c8
    Ports:          1433/TCP, 5022/TCP
    Host Ports:     0/TCP, 0/TCP
    State:          Running
      Started:      Mon, 01 Oct 2018 22:11:44 +0200
    Ready:          True
    Restart Count:  0
    Liveness:       http-get http://:8080/healthz delay=60s timeout=1s period=2s #success=1 #failure=3
    Environment:
      ACCEPT_EULA:        y
      MSSQL_PID:          Developer
      MSSQL_SA_PASSWORD:  <set to the key 'initsapassword' in secret 'mssql1-statefulset-secret'>  Optional: false
      MSSQL_ENABLE_HADR:  1
    Mounts:
      /var/opt/mssql from instance-root (rw)
      /var/run/secrets/kubernetes.io/serviceaccount from no-api-access (ro)
  mssql-ha-supervisor:
    Container ID:  docker://f5a0d4d51a459752a2c509eb3ec7874d94586a7499201f559c9ad8281751e514
    Image:         mcr.microsoft.com/mssql/ha:vNext-CTP2.0-ubuntu
    Image ID:      docker-pullable://mcr.microsoft.com/mssql/ha@sha256:c5d20c8b34ea096a845de0222441304a14ad31a447d79904bafaf29f898704d0
    Port:          8080/TCP
    Host Port:     0/TCP
    Command:
      /mssql-server-k8s-ag-agent-supervisor
    State:          Running
      Started:      Mon, 01 Oct 2018 22:11:45 +0200
    Ready:          True
    Restart Count:  0
    Environment:
      MSSQL_K8S_NAMESPACE:                         agdev (v1:metadata.namespace)
      MSSQL_K8S_POD_NAME:                          mssql1-0 (v1:metadata.name)
      MSSQL_K8S_SQL_SERVER_NAME:                   mssql1
      MSSQL_K8S_POD_IP:                             (v1:status.podIP)
      MSSQL_K8S_NODE_NAME:                          (v1:spec.nodeName)
      MSSQL_K8S_MONITOR_POLICY:                    3
      MSSQL_K8S_HEALTH_CONNECTION_REBOOT_TIMEOUT:
      MSSQL_K8S_SKIP_AG_ANTI_AFFINITY:
      MSSQL_K8S_MONITOR_PERIOD_SECONDS:
      MSSQL_K8S_LEASE_DURATION_SECONDS:
      MSSQL_K8S_RENEW_DEADLINE_SECONDS:
      MSSQL_K8S_RETRY_PERIOD_SECONDS:
      MSSQL_K8S_ACQUIRE_PERIOD_SECONDS:
      MSSQL_K8S_SQL_WRITE_LEASE_PERIOD_SECONDS:
    Mounts:
      /var/run/secrets/kubernetes.io/serviceaccount from mssql1-token-5zlkq (ro)
….
Volumes:
  no-api-access:
    Type:    EmptyDir (a temporary directory that shares a pod's lifetime)
    Medium:
  instance-root:
    Type:       PersistentVolumeClaim (a reference to a PersistentVolumeClaim in the same namespace)
    ClaimName:  mssql-data-1
    ReadOnly:   false
  mssql1-token-5zlkq:
    Type:        Secret (a volume populated by a Secret)
    SecretName:  mssql1-token-5zlkq
    Optional:    false
…

 

We recognize the mssql-server and mssql-ha-supervisor container as stated to the Microsoft documentation. The mssql-server container is listening on the port 1433 (SQL engine) and 5022 (hadr point). Note the container includes a HTTP liveness probes (http-get http://:8080/healthz delay=60s timeout=1s period=2s #success=1 #failure=3) to determine its health. Morever, the mssql-ha-supervisor container is self-explaining and aims to monitor the SQL Server instance if we refer to the environment variable names. I believe another blog post will be necessary to talk about it. Each SQL Server pod (meaning a SQL Server instance here that listen on the port 1433) is exposed to the external traffic by a dedicated service as shown below. External IPs are assigned to the K8s cluster load balancer services through the Azure Load Balancer (basic SKU).

$ kubectl get svc -n agdev
NAME                   TYPE           CLUSTER-IP     EXTERNAL-IP      PORT(S)             AGE
ag1                    ClusterIP      None           <none>           1433/TCP,5022/TCP   23h
mssql1                 LoadBalancer   10.0.43.216    xx.xx.xx.xxx    1433:31674/TCP      23h
mssql2                 LoadBalancer   10.0.28.27     xx.xx.xx.xxx    1433:32681/TCP      23h
mssql3                 LoadBalancer   10.0.137.244   xx.xx.xxx.xxx    1433:31152/TCP      23h

 

  • The AG Services

Finally, I only deployed the service corresponding to ag1-primary that connects to the primary replica. It is up to you to deploy other ones according to your context. In fact, the ag1-primary service acts as the AG listener in this new infrastructure.

$ kubectl get svc -n agdev
NAME          TYPE           CLUSTER-IP     EXTERNAL-IP     PORT(S)             AGE
ag1           ClusterIP      None           <none>          1433/TCP,5022/TCP   23h
ag1-primary   LoadBalancer   10.0.32.104    xxx.xx.xx.xxx       1433:31960/TCP      1m
mssql1        LoadBalancer   10.0.43.216    xx.xx.xx.xxx   1433:31674/TCP      23h
mssql2        LoadBalancer   10.0.28.27     xx.xx.xx.xxx   1433:32681/TCP      23h
mssql3        LoadBalancer   10.0.137.244   xx.xx.xxx.xxx   1433:31152/TCP      23h

 

So, it’s time to connect to my availability group from the external IP of the ag1-primary service. I already add a test database to the availability group and here a picture of the situation:

-- groups info
SELECT 
	g.name as ag_name,
	rgs.primary_replica, 
	rgs.primary_recovery_health_desc as recovery_health, 
	rgs.synchronization_health_desc as sync_health
FROM sys.dm_hadr_availability_group_states as rgs
JOIN sys.availability_groups AS g
				 ON rgs.group_id = g.group_id

-- replicas info
SELECT 
	g.name as ag_name,
	r.replica_server_name,
	r.availability_mode_desc as [availability_mode],
	r.failover_mode_desc as [failover_mode],
	rs.is_local,
	rs.role_desc as role,
	rs.operational_state_desc as op_state,
	rs.connected_state_desc as connect_state,
	rs.synchronization_health_desc as sync_state,
	rs.last_connect_error_number,
	rs.last_connect_error_description
FROM sys.dm_hadr_availability_replica_states AS rs
JOIN sys.availability_replicas AS r
	ON rs.replica_id = r.replica_id
JOIN sys.availability_groups AS g
	ON g.group_id = r.group_id
ORDER BY r.replica_server_name, rs.is_local;

-- DB level          
SELECT 
	g.name as ag_name,
	r.replica_server_name,
	DB_NAME(drs.database_id) as [database_name],
	drs.is_local,
	drs.is_primary_replica,
	synchronization_state_desc as sync_state,
	synchronization_health_desc as sync_health,
	database_state_desc as db_state
FROM sys.dm_hadr_database_replica_states AS drs
		 JOIN sys.availability_replicas AS r
		  ON r.replica_id = drs.replica_id
		 JOIN sys.availability_groups AS g
		  ON g.group_id = drs.group_id
ORDER BY g.name, drs.is_primary_replica DESC;
GO

 

blog 143 - 1 - AG config

This is a common picture we may get with traditional availability group. Another way to identify the primary replica is going through the kubectl command pod and to filter by label as follows:

$ kubectl get pods -n agdev -l="role.ag.mssql.microsoft.com/ag1"="primary"
NAME       READY     STATUS    RESTARTS   AGE
mssql1-0   2/2       Running   0          1d

 

To finish, let’s simulate the crash of the pod mssql1-0 and let’s see what happens:

$ kubectl delete pod -n agdev mssql1-0
pod "mssql1-0" deleted
kubectl get pods -n agdev
NAME                              READY     STATUS        RESTARTS   AGE
mssql-initialize-mssql1-plh8l     0/1       Completed     0          1d
mssql-initialize-mssql2-l6z8m     0/1       Completed     0          1d
mssql-initialize-mssql3-wrbkl     0/1       Completed     0          1d
mssql-operator-67447c4bd8-s6tbv   1/1       Running       0          1d
mssql1-0                          0/2       Terminating   0          1d
mssql2-0                          2/2       Running       0          1d
mssql3-0                          2/2       Running       0          1d

...

$ kubectl get pods -n agdev
NAME                              READY     STATUS              RESTARTS   AGE
mssql-initialize-mssql1-plh8l     0/1       Completed           0          1d
mssql-initialize-mssql2-l6z8m     0/1       Completed           0          1d
mssql-initialize-mssql3-wrbkl     0/1       Completed           0          1d
mssql-operator-67447c4bd8-s6tbv   1/1       Running             0          1d
mssql1-0                          0/2       ContainerCreating   0          9s
mssql2-0                          2/2       Running             0          1d
mssql3-0                          2/2       Running             0          1d

...

$ kubectl get pods -n agdev
NAME                              READY     STATUS      RESTARTS   AGE
mssql-initialize-mssql1-plh8l     0/1       Completed   0          1d
mssql-initialize-mssql2-l6z8m     0/1       Completed   0          1d
mssql-initialize-mssql3-wrbkl     0/1       Completed   0          1d
mssql-operator-67447c4bd8-s6tbv   1/1       Running     0          1d
mssql1-0                          2/2       Running     0          2m
mssql2-0                          2/2       Running     0          1d
mssql3-0                          2/2       Running     0          1d

 

As expected, the controller detects the event and recreates accordingly an another mssql1-0 pod but that’s not all. Firstly, let’s say because we are concerned by StatefulSet the pod keeps the same identity. Then the controller performs also other tasks including failover the availability group to another pod and change the primary with the mssql3-0 pod as shown below. The label of this pod is updated to identify the new primary.

$ kubectl get pods -n agdev -l="role.ag.mssql.microsoft.com/ag1"="primary"
NAME       READY     STATUS    RESTARTS   AGE
mssql3-0   2/2       Running   0          1d

 

This blog post was just an overview of what could be a SQL Server availability group on K8s. Obviously, there are a plenty of other interesting items to cover and to deep dive … probably in a near future. Stay tuned!

 

Cet article First steps into SQL Server 2019 availability groups on K8s est apparu en premier sur Blog dbi services.

Running OUAF Database Installation in Non-Interactive Mode

Anthony Shorten - Wed, 2018-10-03 23:47

Over the past few releases, the Oracle Utilities Application Framework introduced Java versions of our installers which were originally shipped as part of the Oracle Application Management Pack for Oracle Utilities (for Oracle Enterprise Manager). To use these utilities you need to set the CLASSPATH as outlined in the DBA Guides shipped with the product. Each product ships a Install-Upgrade sub-directory which contains the install files. Change to that directory to perform the install. If you want some custom storage parameters update Storage,xml and StorageOps.xml files.

Use the following command line to install the database components:

java -Xmx1500M com.oracle.ouaf.oem.install.OraDBI -d jdbc:oracle:thin:@<DB_SERVER>:<PORT>/<SID>,<DBUSER>,<DBPASS>,<RW_USER>,<R_USER>,<RW_USER_ROLE>,<R_USER_ROLE>,<DBUSER> -l 1,2 -j $JAVA_HOME

Where:

Parameter Comments <DB_SERVER> Host Name for Database Server <PORT> Listener Port for Database Server <SID> Database Service Name (PDB or non-PDB) <DBUSER> Administration Account for product (owns the schema) (created in earlier step) <DBPASS> Password for Administration Account (created in earlier step) <RW_USER> Database Read-Write User for Product (created in earlier step) <R_USER> Database Read Only User for Product (created in earlier step) <RW_USER_ROLE> Database Role for Read Write (created in earlier step) <R_USER_ROLE> Database Role for Read (created in earlier step)

That will run the install directly.

If you added additional users to your installation and want to generate the security definitions for those users then you need to run the new oragensec utility:

java -Xmx1500M com.oracle.ouaf.oem.install.OraGenSec -d <DBUSER>,<DBPASS>,jdbc:oracle:thin:@<DB_SERVER>:<PORT>/<SID> -a A -r <R_USER_ROLE>,<RW_USER_ROLE> -u <RW_USER>,<R_USER>

Where <RW_USER> is the additional user that you want to generate security for. You will need to provide <R_USER> as well.

BPEL 12.2.1.3 Certified for Prebuilt EBS 12.1 SOA Integrations

Steven Chan - Wed, 2018-10-03 12:01

Service Oriented Architecture (SOA) integrations with Oracle E-Business Suite can either be custom integrations that you build yourself or prebuilt integrations from Oracle.  For more information about the differences between the two options for SOA integrations, see this previously-published certification announcement.

The prebuilt BPEL business processes in Oracle E-Business Suite Release 12.1 are:

  • Oracle Price Protection (DPP)
  • Advanced Supply Chain Planning (MSC)
  • Oracle Transportation Management: Oracle Warehouse Management (WMS)
  • Oracle Transportation Management: Oracle Shipping Execution (WSH)
  • Oracle Transportation Management: Oracle Purchasing (PO)
  • Complex Maintenance, Repair & Overhaul (CMRO/AHL)

BPEL integration architecture diagram example

 

These prebuilt BPEL processes have now been certified with Oracle BPEL Process Manager 12c version 12.2.1.3 (in Oracle Fusion Middleware SOA Suite 12c) for Oracle E-Business Suite Release 12.1.3.

References

Certified Platforms

Oracle SOA Suite Release 12g 12.2.1.2 is certified to run on any operating system upon which Oracle WebLogic Server 12c is certified. Check the following for more details:

For information on operating systems supported by Oracle SOA Suite, refer to this document:

Integration with Oracle SOA Suite involves components spanning several different suites of Oracle products. There are no restrictions on which platform any particular component may be installed so long as the platform is supported for that component.

Getting Support If you need support for the prebuilt EBS BPEL business processes, you can log Service Requests against the Applications Technology Group product family.

Related Articles

Categories: APPS Blogs

taking many days to merge clob column

Tom Kyte - Wed, 2018-10-03 09:46
Hi Tom, I have two tables which have clob data, Trying to merge the clob data from work table to Live table. its taking more and more days.. total data size 72GB. What is the best way to run the merge in this case to complete the task quickly. ...
Categories: DBA Blogs

What is the Oracle recommendation for Oracle RAC database and use of Sophos anti-virus software on LINUX?

Tom Kyte - Wed, 2018-10-03 09:46
What is the Oracle recommendation for Oracle RAC database and use of Sophos anti-virus software on LINUX? I have read nearly every Oracle document but can find nothing but Windows recommendation. Should i use that as a base for exclusions (? Oracle...
Categories: DBA Blogs

Calling a stored procedure to send a mail from after insert trigger

Tom Kyte - Wed, 2018-10-03 09:46
I have a requirement to send mail from an after insert trigger and I am passing the :NEW.MYID to the procedure. The procedure contains a query with aggregate functions and storing to an 'INTO mynvarchar2var' as a generated string to be send as email...
Categories: DBA Blogs

How to retrieve data from a quarter?

Tom Kyte - Wed, 2018-10-03 09:46
Right now am using the below query to extract the date. FROM c WHERE date <=TO_DATE ('30-SEP-18', 'DD-MON-YY'). Can you suggest me a way where I dont need to hardcode the date like 30-sep-18. Note: the example date is 30 sep 2018 because I'm ...
Categories: DBA Blogs

Fatal NI connect error 12170

Tom Kyte - Wed, 2018-10-03 09:46
Dear Tom, Please help to advice weather the below attachment is weather network error or not?? on our production database, lots of such TNS-12535: TNS:operation timed out errors. *************************************************************...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator