Feed aggregator

resetlogs

Tom Kyte - Tue, 2017-02-28 04:26
Hi Tom, As we all know we need to open up db with resetlogs when ever incomplete recovery done. 1)why we need open up database with resetlogs after rename the database using control file method or using nid tool? 2)and what more situations n...
Categories: DBA Blogs

How to make sure the tablespace is empty

Tom Kyte - Tue, 2017-02-28 04:26
Hello, I am implementing TDE (tablespace level) and once I have moved tables/indexes etc, from a tablespace to an encrypted tablespace, i want to make sure that tablespace has no objects. I am using the following query to make sure that tablesp...
Categories: DBA Blogs

Oracle Service Bus : disable / enable a proxy service via WebLogic Server MBeans with JMX

Amis Blog - Tue, 2017-02-28 04:22

At a public sector organization in the Netherlands an OSB proxy service was (via JMS) reading messages from a WebLogic queue. These messages where then send to a back-end system. Every evening during a certain time period the back-end system was down. So therefor and also in case of planned maintenance there was a requirement whereby it was necessary to be able to stop and start sending messages to the back-end system from the queue. Hence, a script was needed to disable/enable the OSB proxy service (deployed on OSB 11.1.1.7).

This article will explain how the OSB proxy service can be disabled/enabled via WebLogic Server MBeans with JMX.

A managed bean (MBean) is a Java object that represents a Java Management Extensions (JMX) manageable resource in a distributed environment, such as an application, a service, a component, or a device.

First an “high over” overview of the MBeans is given. For further information see “Fusion Middleware Developing Custom Management Utilities With JMX for Oracle WebLogic Server”, via url: https://docs.oracle.com/cd/E28280_01/web.1111/e13728/toc.htm

Next the structure and use of the System MBean Browser in the Oracle Enterprise Manager Fusion Middleware Control is discussed.

Finally the code to disable/enable the OSB proxy service is shown.

To disable/enable an OSB proxy service, also WebLogic Scripting Tool (WLST) can be used, but in this case (also because of my java developer skills) JMX was used. For more information have a look for example at AMIS TECHNOLOGY BLOG: “Oracle Service Bus: enable / disable proxy service with WLST”, via url: https://technology.amis.nl/2011/01/10/oracle-service-bus-enable-disable-proxy-service-with-wlst/

The Java Management Extensions (JMX) technology is a standard part of the Java Platform, Standard Edition (Java SE platform). The JMX technology was added to the platform in the Java 2 Platform, Standard Edition (J2SE) 5.0 release.

The JMX technology provides a simple, standard way of managing resources such as applications, devices, and services. Because the JMX technology is dynamic, you can use it to monitor and manage resources as they are created, installed and implemented. You can also use the JMX technology to monitor and manage the Java Virtual Machine (Java VM).

For another example of using MBeans with JMX, I kindly point you to another article (written by me) on the AMIS TECHNOLOGY BLOG: “Doing performance measurements of an OSB Proxy Service by programmatically extracting performance metrics via the ServiceDomainMBean and presenting them as an image via a PowerPoint VBA module”, via url: https://technology.amis.nl/2016/01/30/performance-measurements-of-an-osb-proxy-service-by-using-the-servicedomainmbean/

Basic Organization of a WebLogic Server Domain

As you probably already know a WebLogic Server administration domain is a collection of one or more servers and the applications and resources that are configured to run on the servers. Each domain must include a special server instance that is designated as the Administration Server. The simplest domain contains a single server instance that acts as both Administration Server and host for applications and resources. This domain configuration is commonly used in development environments. Domains for production environments usually contain multiple server instances (Managed Servers) running independently or in groups called clusters. In such environments, the Administration Server does not host production applications.

Separate MBean Types for Monitoring and Configuring

All WebLogic Server MBeans can be organized into one of the following general types based on whether the MBean monitors or configures servers and resources:

  • Runtime MBeans contain information about the run-time state of a server and its resources. They generally contain only data about the current state of a server or resource, and they do not persist this data. When you shut down a server instance, all run-time statistics and metrics from the run-time MBeans are destroyed.
  • Configuration MBeans contain information about the configuration of servers and resources. They represent the information that is stored in the domain’s XML configuration documents.
  • Configuration MBeans for system modules contain information about the configuration of services such as JDBC data sources and JMS topics that have been targeted at the system level. Instead of targeting these services at the system level, you can include services as modules within an application. These application-level resources share the life cycle and scope of the parent application. However, WebLogic Server does not provide MBeans for application modules.
MBean Servers

At the core of any JMX agent is the MBean server, which acts as a container for MBeans.

The JVM for an Administration Server maintains three MBean servers provided by Oracle and optionally maintains the platform MBean server, which is provided by the JDK itself. The JVM for a Managed Server maintains only one Oracle MBean server and the optional platform MBean server.

MBean Server Creates, registers, and provides access to… Domain Runtime MBean Server MBeans for domain-wide services. This MBean server also acts as a single point of access for MBeans that reside on Managed Servers.

Only the Administration Server hosts an instance of this MBean server. Runtime MBean Server MBeans that expose monitoring, run-time control, and the active configuration of a specific WebLogic Server instance.

In release 11.1.1.7, the WebLogic Server Runtime MBean Server is configured by default to be the platform MBean server.

Each server in the domain hosts an instance of this MBean server. Edit MBean Server Pending configuration MBeans and operations that control the configuration of a WebLogic Server domain. It exposes a ConfigurationManagerMBean for locking, saving, and activating changes.

Only the Administration Server hosts an instance of this MBean server. The JVM’s platform MBean server MBeans provided by the JDK that contain monitoring information for the JVM itself. You can register custom MBeans in this MBean server.

In release 11.1.1.7, WebLogic Server uses the JVM’s platform MBean server to contain the WebLogic run-time MBeans by default. Service MBeans

Within each MBean server, WebLogic Server registers a service MBean under a simple object name. The attributes and operations in this MBean serve as your entry point into the WebLogic Server MBean hierarchies and enable JMX clients to navigate to all WebLogic Server MBeans in an MBean server after supplying only a single object name.

MBean Server Service MBean JMX object name The Domain Runtime MBean Server DomainRuntimeServiceMBean

Provides access to MBeans for domain-wide services such as application deployment, JMS servers, and JDBC data sources. It also is a single point for accessing the hierarchies of all run-time MBeans and all active configuration MBeans for all servers in the domain. com.bea:Name=DomainRuntimeService,Type=weblogic.management.mbeanservers.domainruntime.DomainRuntimeServiceMBean Runtime MBean Servers RuntimeServiceMBean

Provides access to run-time MBeans and active configuration MBeans for the current server. com.bea:Name=RuntimeService,Type=weblogic.management.mbeanservers.runtime.RuntimeServiceMBean The Edit MBean Server EditServiceMBean

Provides the entry point for managing the configuration of the current WebLogic Server domain. com.bea:Name=EditService,Type=weblogic.management.mbeanservers.edit.EditServiceMBean Choosing an MBean Server

If your client monitors run-time MBeans for multiple servers, or if your client runs in a separate JVM, Oracle recommends that you connect to the Domain Runtime MBean Server on the Administration Server instead of connecting separately to each Runtime MBean Server on each server instance in the domain.

The trade off for directing all JMX requests through the Domain Runtime MBean Server is a slight degradation in performance due to network latency and increased memory usage. However, for most network topologies and performance requirements, the simplified code maintenance and enhanced security that the Domain Runtime MBean Server enables is preferable.

System MBean Browser

Oracle Enterprise Manager Fusion Middleware Control provides the System MBean Browser for managing MBeans that perform specific monitoring and configuration tasks.

Via the Oracle Enterprise Manager Fusion Middleware Control for a certain domain, the System MBean Browser can be opened.

Here the previously mentioned types of MBean’s can be seen: Runtime MBeans and Configuration MBeans:

When navigating to “Configuration MBeans | com.bea”, the previously mentioned EditServiceMBean can be found:

When navigating to “Runtime MBeans | com.bea | Domain: <a domain>”, the previously mentioned DomainRuntimeServiceMBean can be found:

Also the later on in this article mentioned MBeans can be found:

For example for the ProxyServiceConfigurationMbean, the available operations can be found:

When navigating to “Runtime MBeans | com.bea”, within each Server the previously mentioned RuntimeServiceMBean can be found.

 

Code to disable/enable the OSB proxy service

The requirement to be able to stop and start sending messages to the back-end system from the queue was implemented by disabling/enabling the state of the OSB Proxy service JMSConsumerStuFZKNMessageService_PS.

Short before the back-end system goes down, dequeuing of the queue should be disabled.
Right after the back-end system goes up again, dequeuing of the queue should be enabled.

The state of the OSB Proxy service can be seen in the Oracle Service Bus Administration 11g Console (for example via the Project Explorer) in the tab “Operational Settings” of the proxy service.

For ease of use, two ms-dos batch files where created, each using MBeans, to change the state of a service (proxy service or business service). As stated before, the WebLogic Server contains a set of MBeans that can be used to configure, monitor and manage WebLogic Server resources.

  • Disable_JMSConsumerStuFZKNMessageService_PS.bat

On the server where the back-end system resides, the ms-dos batch file “Disable_JMSConsumerStuFZKNMessageService_PS.bat” is called.

The content of the batch file is:

java.exe -classpath “OSBServiceState.jar;com.bea.common.configfwk_1.7.0.0.jar;sb-kernel-api.jar;sb-kernel-impl.jar;wlfullclient.jar” nl.xyz.osbservice.osbservicestate.OSBServiceState “xyz” “7001” “weblogic” “xyz” “ProxyService” “JMSConsumerStuFZKNMessageService-1.0/proxy/JMSConsumerStuFZKNMessageService_PS” “Disable”

  • Enable_JMSConsumerStuFZKNMessageService_PS.bat

On the server where the back-end system resides, the ms-dos batch file “Enable_JMSConsumerStuFZKNMessageService_PS.bat” is called.

The content of the batch file is:

java.exe -classpath “OSBServiceState.jar;com.bea.common.configfwk_1.7.0.0.jar;sb-kernel-api.jar;sb-kernel-impl.jar;wlfullclient.jar” nl.xyz.osbservice.osbservicestate.OSBServiceState “xyz” “7001” “weblogic” “xyz” “ProxyService” “JMSConsumerStuFZKNMessageService-1.0/proxy/JMSConsumerStuFZKNMessageService_PS” “Enable”

In both ms-dos batch files via java.exe a class named OSBServiceState is being called. The main method of this class expects the following parameters:

Parameter name Description HOSTNAME Host name of the AdminServer PORT Port of the AdminServer USERNAME Username PASSWORD Passsword SERVICETYPE Type of resource. Possible values are:

  • ProxyService
  • BusinessService
SERVICEURI Identifier of the resource. The name begins with the project name, followed by folder names and ending with the resource name. ACTION The action to be carried out. Possible values are:

  • Enable
  • Disable

Every change is carried out in it´s own session (via the SessionManagementMBean), which is automatically activated with description: OSBServiceState_script_<systemdatetime>

This can be seen via the Change Center | View Changes of the Oracle Service Bus Administration 11g Console:

The response from “Disable_JMSConsumerStuFZKNMessageService_PS.bat” is:

Disabling service JMSConsumerStuFZKNMessageService-1.0/proxy/JMSConsumerStuFZKNMessageService_PS has been succesfully completed

In the Oracle Service Bus Administration 11g Console this change can be found as a Task:

The result of changing the state of the OSB Proxy service can be checked in the Oracle Service Bus Administration 11g Console.

The same applies when using “Enable_JMSConsumerStuFZKNMessageService_PS.bat”.

In the sample code below the use of the following MBeans can be seen:

Provides a common access point for navigating to all runtime and configuration MBeans in the domain as well as to MBeans that provide domain-wide services (such as controlling and monitoring the life cycles of servers and message-driven EJBs and coordinating the migration of migratable services). [https://docs.oracle.com/middleware/1213/wls/WLAPI/weblogic/management/mbeanservers/domainruntime/DomainRuntimeServiceMBean.html]

This library is not by default provided in a WebLogic install and must be build. The simple way of how to do this is described in
“Fusion Middleware Programming Stand-alone Clients for Oracle WebLogic Server, Using the WebLogic JarBuilder Tool”, which can be reached via url: https://docs.oracle.com/cd/E28280_01/web.1111/e13717/jarbuilder.htm#SACLT240.

Provides API to create, activate or discard sessions. [http://docs.oracle.com/cd/E13171_01/alsb/docs26/javadoc/com/bea/wli/sb/management/configuration/SessionManagementMBean.html]

Provides API to enable/disable services and enable/disable monitoring for a proxy service. [https://docs.oracle.com/cd/E13171_01/alsb/docs26/javadoc/com/bea/wli/sb/management/configuration/ProxyServiceConfigurationMBean.html]

Provides API for managing business services. [https://docs.oracle.com/cd/E13171_01/alsb/docs25/javadoc/com/bea/wli/sb/management/configuration/BusinessServiceConfigurationMBean.html]

Once the connection to the DomainRuntimeServiceMBean is made, other MBeans can be found via the findService method.

Service findService(String name,
                    String type,
                    String location)

This method returns the Service on the specified Server or in the primary MBeanServer if the location is not specified.

In the code example below certain java fields are used. For reading purposes the field values are shown in the following table:

Field Field value DomainRuntimeServiceMBean.MBEANSERVER_JNDI_NAME weblogic.management.mbeanservers.domainruntime DomainRuntimeServiceMBean.OBJECT_NAME com.bea:Name=DomainRuntimeService,Type=weblogic.management.mbeanservers.domainruntime.DomainRuntimeServiceMBean SessionManagementMBean.NAME SessionManagement SessionManagementMBean.TYPE com.bea.wli.sb.management.configuration.SessionManagementMBean ProxyServiceConfigurationMBean.NAME ProxyServiceConfiguration ProxyServiceConfigurationMBean.TYPE com.bea.wli.sb.management.configuration.ProxyServiceConfigurationMBean BusinessServiceConfigurationMBean.NAME BusinessServiceConfiguration BusinessServiceConfigurationMBean.TYPE com.bea.wli.sb.management.configuration.BusinessServiceConfigurationMBean

Because of the use of com.bea.wli.config.Ref.class , the following library <Middleware Home Directory>/Oracle_OSB1/modules/com.bea.common.configfwk_1.7.0.0.jar was needed.

Because of the use of weblogic.management.jmx.MBeanServerInvocationHandler.class , the following library <Middleware Home Directory>/wlserver_10.3/server/lib/wlfullclient.jar was needed.

When running the code the following error was thrown:

java.lang.RuntimeException: java.lang.ClassNotFoundException: com.bea.wli.sb.management.configuration.DelegatedSessionManagementMBean
	at weblogic.management.jmx.MBeanServerInvocationHandler.newProxyInstance(MBeanServerInvocationHandler.java:621)
	at weblogic.management.jmx.MBeanServerInvocationHandler.invoke(MBeanServerInvocationHandler.java:418)
	at $Proxy0.findService(Unknown Source)
	at nl.xyz.osbservice.osbservicestate.OSBServiceState.<init>(OSBServiceState.java:66)
	at nl.xyz.osbservice.osbservicestate.OSBServiceState.main(OSBServiceState.java:217)
Caused by: java.lang.ClassNotFoundException: com.bea.wli.sb.management.configuration.DelegatedSessionManagementMBean
	at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
	at java.security.AccessController.doPrivileged(Native Method)
	at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
	at weblogic.management.jmx.MBeanServerInvocationHandler.newProxyInstance(MBeanServerInvocationHandler.java:619)
	... 4 more
Process exited.

So because of the use of com.bea.wli.sb.management.configuration.DelegatedSessionManagementMBean.class the following library <Middleware Home Directory>/Oracle_OSB1/lib/sb-kernel-impl.jar was also needed.

package nl.xyz.osbservice.osbservicestate;


import com.bea.wli.config.Ref;
import com.bea.wli.sb.management.configuration.BusinessServiceConfigurationMBean;
import com.bea.wli.sb.management.configuration.ProxyServiceConfigurationMBean;
import com.bea.wli.sb.management.configuration.SessionManagementMBean;

import java.io.IOException;

import java.net.MalformedURLException;

import java.util.HashMap;
import java.util.Hashtable;
import java.util.Properties;

import javax.management.MBeanServerConnection;
import javax.management.MalformedObjectNameException;
import javax.management.ObjectName;
import javax.management.remote.JMXConnector;
import javax.management.remote.JMXConnectorFactory;
import javax.management.remote.JMXServiceURL;

import javax.naming.Context;

import weblogic.management.jmx.MBeanServerInvocationHandler;
import weblogic.management.mbeanservers.domainruntime.DomainRuntimeServiceMBean;


public class OSBServiceState {
    private static MBeanServerConnection connection;
    private static JMXConnector connector;

    public OSBServiceState(HashMap props) {
        super();
        SessionManagementMBean sessionManagementMBean = null;
        String sessionName =
            "OSBServiceState_script_" + System.currentTimeMillis();
        String servicetype;
        String serviceURI;
        String action;
        String description = "";


        try {

            Properties properties = new Properties();
            properties.putAll(props);

            initConnection(properties.getProperty("HOSTNAME"),
                           properties.getProperty("PORT"),
                           properties.getProperty("USERNAME"),
                           properties.getProperty("PASSWORD"));

            servicetype = properties.getProperty("SERVICETYPE");
            serviceURI = properties.getProperty("SERVICEURI");
            action = properties.getProperty("ACTION");

            DomainRuntimeServiceMBean domainRuntimeServiceMBean =
                (DomainRuntimeServiceMBean)findDomainRuntimeServiceMBean(connection);

            // Create a session via SessionManagementMBean.
            sessionManagementMBean =
                    (SessionManagementMBean)domainRuntimeServiceMBean.findService(SessionManagementMBean.NAME,
                                                                                  SessionManagementMBean.TYPE,
                                                                                  null);
            sessionManagementMBean.createSession(sessionName);

            if (servicetype.equalsIgnoreCase("ProxyService")) {

                // A Ref uniquely represents a resource, project or folder that is managed by the Configuration Framework.
                // A Ref object has two components: A typeId that indicates whether it is a project, folder, or a resource, and an array of names of non-zero length.
                // For a resource the array of names start with the project name, followed by folder names, and end with the resource name.
                // For a project, the Ref object simply contains one name component, that is, the project name.
                // A Ref object for a folder contains the project name followed by the names of the folders which it is nested under.
                Ref ref = constructRef("ProxyService", serviceURI);

                ProxyServiceConfigurationMBean proxyServiceConfigurationMBean =
                    (ProxyServiceConfigurationMBean)domainRuntimeServiceMBean.findService(ProxyServiceConfigurationMBean.NAME +
                                                                                          "." +
                                                                                          sessionName,
                                                                                          ProxyServiceConfigurationMBean.TYPE,
                                                                                          null);
                if (action.equalsIgnoreCase("Enable")) {
                    proxyServiceConfigurationMBean.enableService(ref);
                    description = "Enabled the service: " + serviceURI;
                    System.out.print("Enabling service " + serviceURI);
                } else if (action.equalsIgnoreCase("Disable")) {
                    proxyServiceConfigurationMBean.disableService(ref);
                    description = "Disabled the service: " + serviceURI;
                    System.out.print("Disabling service " + serviceURI);
                } else {
                    System.out.println("Unsupported value for ACTION");
                }
            } else if (servicetype.equals("BusinessService")) {
                Ref ref = constructRef("BusinessService", serviceURI);

                BusinessServiceConfigurationMBean businessServiceConfigurationMBean =
                    (BusinessServiceConfigurationMBean)domainRuntimeServiceMBean.findService(BusinessServiceConfigurationMBean.NAME +
                                                                                             "." +
                                                                                             sessionName,
                                                                                             BusinessServiceConfigurationMBean.TYPE,
                                                                                             null);
                if (action.equalsIgnoreCase("Enable")) {
                    businessServiceConfigurationMBean.enableService(ref);
                    description = "Enabled the service: " + serviceURI;
                    System.out.print("Enabling service " + serviceURI);
                } else if (action.equalsIgnoreCase("Disable")) {
                    businessServiceConfigurationMBean.disableService(ref);
                    description = "Disabled the service: " + serviceURI;
                    System.out.print("Disabling service " + serviceURI);
                } else {
                    System.out.println("Unsupported value for ACTION");
                }
            }
            sessionManagementMBean.activateSession(sessionName, description);
            System.out.println(" has been succesfully completed");
        } catch (Exception ex) {
            if (sessionManagementMBean != null) {
                try {
                   sessionManagementMBean.discardSession(sessionName);
                    System.out.println(" resulted in an error.");
                } catch (Exception e) {
                    System.out.println("Unable to discard session: " +
                                       sessionName);
                }
            }

            ex.printStackTrace();
        } finally {
            if (connector != null)
                try {
                    connector.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
        }
    }


    /*
       * Initialize connection to the Domain Runtime MBean Server.
       */

    public static void initConnection(String hostname, String portString,
                                      String username,
                                      String password) throws IOException,
                                                              MalformedURLException {

        String protocol = "t3";
        Integer portInteger = Integer.valueOf(portString);
        int port = portInteger.intValue();
        String jndiroot = "/jndi/";
        String mbeanserver = DomainRuntimeServiceMBean.MBEANSERVER_JNDI_NAME;

        JMXServiceURL serviceURL =
            new JMXServiceURL(protocol, hostname, port, jndiroot +
                              mbeanserver);

        Hashtable hashtable = new Hashtable();
        hashtable.put(Context.SECURITY_PRINCIPAL, username);
        hashtable.put(Context.SECURITY_CREDENTIALS, password);
        hashtable.put(JMXConnectorFactory.PROTOCOL_PROVIDER_PACKAGES,
                      "weblogic.management.remote");
        hashtable.put("jmx.remote.x.request.waiting.timeout", new Long(10000));

        connector = JMXConnectorFactory.connect(serviceURL, hashtable);
        connection = connector.getMBeanServerConnection();
    }


    private static Ref constructRef(String refType, String serviceURI) {
        Ref ref = null;
        String[] uriData = serviceURI.split("/");
        ref = new Ref(refType, uriData);
        return ref;
    }


    /**
     * Finds the specified MBean object
     *
     * @param connection - A connection to the MBeanServer.
     * @return Object - The MBean or null if the MBean was not found.
     */
    public Object findDomainRuntimeServiceMBean(MBeanServerConnection connection) {
        try {
            ObjectName objectName =
                new ObjectName(DomainRuntimeServiceMBean.OBJECT_NAME);
            return (DomainRuntimeServiceMBean)MBeanServerInvocationHandler.newProxyInstance(connection,
                                                                                            objectName);
        } catch (MalformedObjectNameException e) {
            e.printStackTrace();
            return null;
        }
    }


    public static void main(String[] args) {
        try {
            if (args.length <= 0) {
                System.out.println("Provide values for the following parameters: HOSTNAME, PORT, USERNAME, PASSWORD, SERVICETYPE, SERVICEURI, ACTION.);

            } else {
                HashMap<String, String> map = new HashMap<String, String>();

                map.put("HOSTNAME", args[0]);
                map.put("PORT", args[1]);
                map.put("USERNAME", args[2]);
                map.put("PASSWORD", args[3]);
                map.put("SERVICETYPE", args[4]);
                map.put("SERVICEURI", args[5]);
                map.put("ACTION", args[6]);
                OSBServiceState osbServiceState = new OSBServiceState(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

The post Oracle Service Bus : disable / enable a proxy service via WebLogic Server MBeans with JMX appeared first on AMIS Oracle and Java Blog.

Goldengate 12c PREPARECSN BUG

Michael Dinh - Mon, 2017-02-27 20:56

It looks like I have encountered BUG which applies to any platform but Oracle only has patch for Solaris Sparc.

Anyone working for Oracle able to assist here please?

ADD SCHEMATRANDATA Throws OGG-01780 Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command. (Doc ID 2188988.1)

Oracle GoldenGate – Version 12.2.0.1.0 and later
Information in this document applies to any platform.

Patch 24601324: Patch FOR MLR 24590215: Solaris Sparc: Oracle12c: OGG 12.2.0.1.160517

Only 3 options accepted after ADD SCHEMATRANDATA causing the issue.

The current patch will change the code to accept 5 options after ADD SCHEMATRANDATA

DEMO:
WAIT
Wait for any in-flight transactions and prepare table instantiation.

LOCK
Put a lock on the table (to prepare for table instantiation).

NOWAIT
Default behavior, preparing for instantiation is done immediately.

NONE
No instantiation preparation occurs.

++++++++++++++++++++++++++++++

oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ cat /etc/oracle-release
Oracle Linux Server release 6.6
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.



GGSCI (arrow1.localdomain) 1> DBLOGIN USERIDALIAS ggs_user
Successfully logged into database.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 2> DELETE SCHEMATRANDATA demo

2017-02-27 18:45:16  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:45:16  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 3> ADD SCHEMATRANDATA demo PREPARECSN WAIT

2017-02-27 18:45:57  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:45:57  INFO    OGG-10154  Schema level PREPARECSN set to mode WAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 4> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:00  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:00  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 5> ADD SCHEMATRANDATA demo PREPARECSN NOWAIT

2017-02-27 18:46:10  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:10  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 6> DELETE SCHEMATRANDATA demo

2017-02-27 18:46:18  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema demo.

2017-02-27 18:46:18  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 7> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN NOWAIT

2017-02-27 18:46:29  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 8> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN WAIT

2017-02-27 18:46:38  ERROR   OGG-01780  Missing/Invalid argument(s) on ADD/INFO/DELETE SCHEMATRANDATA command.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 9> ADD SCHEMATRANDATA demo ALLCOLS PREPARECSN

2017-02-27 18:46:46  INFO    OGG-01788  SCHEMATRANDATA has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-01977  SCHEMATRANDATA for all columns has been added on schema demo.

2017-02-27 18:46:46  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema demo.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 10> info SCHEMATRANDATA demo

2017-02-27 18:46:56  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema DEMO.

2017-02-27 18:46:56  INFO    OGG-01981  Schema level supplemental logging is enabled on schema DEMO for all columns.

2017-02-27 18:46:56  INFO    OGG-10462  Schema DEMO have 2 prepared tables for instantiation.

GGSCI (arrow1.localdomain as ggs_admin@HAWKA) 11> exit
oracle@arrow1:HAWKA:/u01/app/12.2.0.1/ggs01
$


If Customers Mind, It Matters: Digital Design Thinking for the Experience Economy

WebCenter Team - Mon, 2017-02-27 11:23

Written by:  Geoffrey Bock, Principal, Bock & Company and Daryl Eicher, Marketing Director, Oracle

 Driving growth in the experience economy means putting customers first. Yet memorable service moments depend on operational excellence. Compelling digital interactions with customers depend on connected employees to make the kinds of memories that build brand loyalty. 

This obsession with simple customer and employee experiences is anything but easy to deliver. It demands digital design thinking. This is a new and in many cases challenging approach to design that effectively balances ever-increasing business expectations with the scarcity of highly skilled developer resources. 

Digital design thinking is far more agile and incremental than traditional application requirements analysis. Business operations teams expect high bandwidth conversations and nearly immediate release of new capabilities. They don’t have time for conventional requirements documentation and approval. But they know a good design when they see it. Digital design thinking starts with mocking up what business operations will see on their phones first and deferring technical details until after the business workflow is flawless. 


Today’s killer apps deliver seamless, multi-channel experiences that assistant and predict, rather than just inform and respond. These seemingly simple apps engage and delight customers, employees, and partners, by contextualizing content to speed decisions, and by anticipating the best next steps to streamline day to day activities.  

Digital design thinking isn’t just about building yet another “app for that.” It’s about making the apps you have easier to utilize in creative ways. So, in addition to focusing on the user experience first, digital design thinking must leave plenty of room for data-driven improvement over time. This “design for change” imperative is antithetical to traditional developer-led automation projects where application enhancements can take months or even years to go live.  

Consider the many decisions and coordinated actions a government agency must take to manage its contractor workforce. Getting a new contractor’s first day on the job to be productive typically requires approved actions spanning multiple departments and existing applications such as payroll, benefits, an enterprise directory, and a skills registry. Retooling every application involved isn’t an option for the department heads involved, so approvals, supporting documentation, and exception handling are typically handled via email and sneaker net. 

Traditional, developer-led automation projects typically put one-off system integration efforts on the critical path to delivering the full end-to-end solution. By comparison, digital design thinking starts with first principles -- delivering a simple mobile app that ensures all needed approvals are in place and easily discoverable for compliance purposes. After this approval workflow is agreed to across department heads, the next consideration is what content is needed to speed time to decision. Subsequent releases of the mobile app may involve supporting documents or forms. 

Only after the workflow is operating smoothly, and any existing email attachments are easily accessible to department heads from within the new app, are the difficult issues of integrating with existing systems of record considered. This pragmatic, human-centric, business-led approach depends on low code app development where visual models replace coding and automation is in the hands of subject matter experts. 

The final stage in digital design thinking is to prioritize connections with selected systems of record based on their impact on operational decision-making. Modern low code app dev relies on the ability to abstract the technical details of integration so that business analysts can focus on the decisions at hand without distractions and delays. Oracle Cloud Platform for digital business enables business agility and compliance with a shared service catalogue for pre-built connections. Third party and in-house developers publish additional services to the catalogue for business analysts to use to refine their simple mobile apps over time. 

How should you get started? 

Be sure to design for simplicity on the front end. Expect to add innovative capabilities, including intelligent services running within cloud environments, to the backend. Ensure that every decision point and every human interaction is informed by contextual content. And finally, anticipate the best next steps to simplify the tasks that end users need to perform to get things done. 

For more on how digital design thinking powers business agility, check out this on-demand webinar and supporting solution brief.  

Getting Apex 4.2 Up and Running on Oracle 12c Using PL/SQL Gateway

Rittman Mead Consulting - Mon, 2017-02-27 10:30

Recently I was asked to create a simple Master-Detail Apex form on top of some metadata tables stored within Oracle 12c. Apex version 4.2 is already pre-installed in 12c but it needs configuring to be able to use. This is where I hit some trouble which stems from a conflict of information across a number of different sources.

I was following the Oracle certified documentation on 4.2 and how to get it running on 12c specifically but it wouldn't work. Time after time I kept getting 'Error 404' messages whenever I tried accessing Apex through the browser. I gave up on the Oracle documentation and decided to scout out some blogs but they were virtually copy and paste and resulted in the same error messages. I asked colleagues at work and also joined the Apex slack domain but nobody quite understood why it wasn't working.

It seemed like a lost cause, until I decided to check the later versions of the Oracle documentation and came across an extra piece of information from an Apex 5.0 document:

Procedures CREATE_ACL, ASSIGN_ACL, ADD_PRIVILEGE and CHECK_PRIVILEGE in DBMS_NETWORK_ACL_ADMIN are deprecated in Oracle Database 12c. Oracle recommends using APPEND_HOST_ACE instead.

Given that 12c comes with Apex 4.2, I was surprised to find it not mentioned anywhere else but in the Apex 5.0 documentation.

So the intention for this blog entry, is to bring together all the information that is needed to get Apex 4.2 using PL/SQL Gateway on Oracle 12c so you're able to take a quick and easy look at what it can offer.

Steps to configure the PL/SQL Gateway on Oracle 12c

  1. Change your working directory where Apex is installed:

    cd $ORACLE_HOME/apex  
    
  2. Connect to SQL*Plus with a user with SYS privileges:

    sqlplus / as sysdba  
    
  3. Switch containers to the pluggable database container:

    ALTER SESSION SET CONTAINER = <PDB_NAME>;  
    
  4. Run the apex_epg_config.sql script along with the $ORACLE_HOME:

    @apex_epg_config.sql /u01/app/oracle/product/12.1.0/dbhome_1/
    

    Log back into SQL*Plus from the Apex directory if you get logged out

  5. Then unlock the ANONYMOUS user within the Root Container:

    ALTER SESSION SET CONTAINER = CDB$ROOT;
    ALTER USER ANONYMOUS ACCOUNT UNLOCK;
    
  6. Switch to the pluggable database container and run apxconf.sql:

    ALTER SESSION SET CONTAINER = <PDB_NAME>; 
    @apxconf.sql;
    

    When prompted change the ADMIN password and assign a port for APEX to connect to - Default is 8080.

  7. Verify the Oracle XDB Protocol Server Port that was set after running the apxconf.sql script:

    ALTER SESSION SET CONTAINER = <PDB_NAME>;  
    SELECT DBMS_XDB.GETHTTPPORT FROM DUAL;
    

    If it returns with [0] then enable the XDB Server

    EXEC DBMS_XDB.SETHTTPPORT(port); COMMIT;  
    
  8. Enable the Network Services in Oracle Database 12c - This will be disabled by default. This following will give access to all hosts:

    BEGIN 
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
    (host => '*', ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => 'apex_040200', 
    principal_type => xs_acl.ptype_db)); 
    END; 
    /
    

    NOTE: If you require stricter security add the host name after the host parameter e.g. host => 'localhost'

  9. Open your browser and connect to Apex:

    http://hostname:8080/apex
    
Summary

This blog has given you the guidelines for getting Apex 4.2, that comes pre-installed on Oracle 12c, up and running.

Note that 4.2 is now quite dated and it is recommended to upgrade to Apex 5. This ensures continued support from Oracle, and access to newer and more efficient features.

Categories: BI & Warehousing

Multiple Partial Search in single field

Tom Kyte - Mon, 2017-02-27 10:06
create table t (id number, name varchar2(30)) insert into t (111, 'MGR_123 NAME 123'); insert into t (123, 'Silvaji_521 main 234'); insert into t (786, 'Rajini_786 sounth 111'); insert into t (678, 'vIKRAm_333 nila 532441'); insert int...
Categories: DBA Blogs

Number internal storage and display

Tom Kyte - Mon, 2017-02-27 10:06
Hi Guys, I tried to execute the below script, but got some unambiguous result while fetching the record. I am trying this query in Windows 7 Operating system. SELECT * FROM V$Version; ----------------------------------------------------...
Categories: DBA Blogs

query issue

Tom Kyte - Mon, 2017-02-27 10:06
hi team , i have a scenario.I have a statement a,b,c, here by using regular expression regexp_replace i need to replace the commas in the statement with ||','|| and the last comma in the statement which is present should be replaced with nul...
Categories: DBA Blogs

copy of table from existing table

Tom Kyte - Mon, 2017-02-27 10:06
Hi, How will you copy the table from existing table to new table by ommiting a particular column name. and if columns are few we can specify the column name but its very big table and so many columns are there.
Categories: DBA Blogs

Report of Schedule Job in Oracel SQL Developer

Tom Kyte - Mon, 2017-02-27 10:06
Dear all, How could I develop a report to run by Schedule Job in Oracel SQL Developer?
Categories: DBA Blogs

how can I partition a table based on Persian months format?

Tom Kyte - Mon, 2017-02-27 10:06
Hello Dear Tom As you know in oracle 12c Iran territory is supported. <code> col PARAMETER format a30; col VALUE format a30; select * from v$nls_valid_values where lower(VALUE) in ('iran','persian'); PARAMETER VALUE ...
Categories: DBA Blogs

Understand the behaviour of nextval of a sequence in SQL and PL/SQL

Tom Kyte - Mon, 2017-02-27 10:06
Hello Oracle Masters, I have a question about the <b>nextval</b> pseudocolumn of a sequence. I thought that the call of nextval produces each time a new integer, while currval returns the current value of the sequence. In the script that you see...
Categories: DBA Blogs

Downgrade a fresh 11.2.0.4 database [not upgraded] to 11.2.0.1

Tom Kyte - Mon, 2017-02-27 10:06
Hi Tom, I need to downgrade a newly installed database 11.2.0.4 to 11.2.0.1, Is it possible?, please let me know the steps involved. There is a metalink note but it is only for databases which were previously upgraded. Thanks a lot in advance. ...
Categories: DBA Blogs

Oracle Expands Oracle Cloud at Customer Portfolio to Database Workloads with Oracle Exadata Cloud Machine

Oracle Press Releases - Mon, 2017-02-27 07:00
Press Release
Oracle Expands Oracle Cloud at Customer Portfolio to Database Workloads with Oracle Exadata Cloud Machine Organizations equipped to reap the benefits of the Oracle Cloud Platform in their own datacenter

Redwood Shores, Calif.—Feb 27, 2017

Continuing to help organizations simplify cloud adoption by bringing the benefits of the cloud inside their own datacenters, Oracle today announced the expansion of the Oracle Cloud at Customer portfolio with the availability of Oracle Exadata Cloud Machine.  With today’s news, Oracle is offering organizations the ultimate in choice and flexibility in where they deploy the world’s most advanced database cloud for mission-critical workloads. Organizations can now deploy Oracle Exadata in a number of ways, including as a cloud service inside their own datacenter, in the Oracle Cloud, and in a traditional on-premises environment.

Since its introduction just over a year ago, Oracle Cloud at Customer has seen tremendous popularity as organizations look for ways to bridge the gap between the public cloud and on-premises in their journey to the cloud.  While organizations look forward to moving their enterprise workloads to the public cloud, many have been constrained by business, legislative, and regulatory requirements that have prevented them from moving their data and applications outside their own datacenter. Oracle Exadata Cloud Machine delivers the full power of the Oracle Exadata Cloud Service that resides in Oracle’s public cloud to customers who require or prefer their databases to be located on-premises.

“Oracle Exadata Cloud Machine is an ideal platform for organizations that want the benefits of the cloud brought to their datacenter,” said Juan Loaiza, senior vice president of systems technologies, Oracle. “For many years, Oracle Exadata has been the platform of choice for running mission critical Oracle databases at thousands of customers, and the Oracle Exadata Cloud Machine extends this value proposition to those customers who want cloud benefits but cannot or aren’t yet ready to move to a public cloud.”

With Oracle Exadata Cloud Machine, customers have subscription access to the most powerful Oracle Database with all options and features, like Real Application Clusters, Database In-Memory, Active Data Guard and Advanced Security, offering extremely high levels of performance, availability and security features for mission-critical workloads.  Additionally, the Oracle Exadata Cloud Machine is 100 percent compatible with on-premises and Oracle Cloud applications and databases, ensuring any existing application can be quickly migrated to the cloud without changes. 

The Oracle Exadata Cloud Service and Oracle Exadata Cloud Machine provide leading functionality, including:

  • Mission-critical database for OLTP, analytics, mixed workloads, and consolidation—all options included
  • Highly proven database hardware platform with NVMe Flash, InfiniBand networking, and the fastest servers
  • Intelligent database platform with Smart Database Algorithms in storage, networking, and compute
  • Advanced database cloud platform with subscription based pricing and real-time online capacity bursting
  • Flexible cloud that can be deployed in Oracle's public cloud or inside the customer's data center with Oracle managing all infrastructure
  • Simple and straightforward migration to the cloud—software and hardware are identical and 100 percent compatible

“Every IT organization is making plans to move to the public cloud, and Oracle customers are no different,” said Carl Olofson, Research Vice President for structured data management software at IDC. “The Oracle Cloud at Customer program provides a means of transitioning to the cloud by starting right in the datacenter, thereby maintaining direct interaction with the applications that remain on the premises. The Oracle Exadata Cloud Machine extends that capability with all the features of Exadata, managed remotely by the Oracle Cloud team. It is a great first step toward eventual cloud deployment.” 

The Oracle Cloud at Customer portfolio of services enables organizations to get all of the benefits of Oracle’s public cloud services in their datacenter. The business model is just like a public cloud subscription; the hardware and software is the same; Oracle experts monitor and manage the infrastructure; and the same tools used in Oracle’s public cloud are used to provision resources on the Cloud Machine.  This is the only offering from a major public cloud vendor that delivers a stack that is 100 percent compatible with the Oracle Cloud but available on-premises. Since the software is seamless with the Oracle Cloud, customers can use it for a number of use cases, including disaster recovery, elastic bursting, dev/test, lift-and-shift workload migration, and a single API and scripting toolkit for DevOps. Additionally, as a fully managed Oracle offering, customers get the same experience and the latest innovations and benefits using it in their datacenter as in the Oracle Cloud.

Oracle Cloud

Oracle Cloud is the industry’s broadest and most integrated public cloud, offering a complete range of services across SaaS, PaaS, and IaaS. It supports new cloud environments, existing ones, and hybrid, and all workloads, developers, and data.  The Oracle Cloud delivers nearly 1,000 SaaS applications and 50 enterprise-class PaaS and IaaS services to customers in more than 195 countries around the world and supports 55 billion transactions each day.

For more information, please visit us at http://cloud.oracle.com.

Contact Info
Nicole Maloney
Oracle PR
+1.650.506.0806
nicole.maloney@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit 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.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Nicole Maloney

  • +1.650.506.0806

Oracle Industry Connect 2017 Convenes Community of Business Leaders to Share Insight, Expertise and Industry-specific Best Practices

Oracle Press Releases - Mon, 2017-02-27 06:59
Press Release
Oracle Industry Connect 2017 Convenes Community of Business Leaders to Share Insight, Expertise and Industry-specific Best Practices Veteran journalist Tom Brokaw, Oracle Chief Executive Officer Mark Hurd and Oracle Executive Vice President Bob Weiler to Headline

Redwood Shores, Calif.—Feb 27, 2017

Oracle is hosting its fourth-annual Oracle Industry Connect, an exclusive, executive-level event by key industry experts for industry experts, to share strategies for innovation and organizational transformation and success. The event takes place March 20-22 in Orlando, Fla.

The conference features distinguished subject matter experts and keynotes from Oracle CEO Mark Hurd; Bob Weiler, executive vice president of Oracle’s Global Business Units; and Tom Brokaw, NBC News correspondent and New York Times best-selling author.

Brokaw, who was awarded the Presidential Medal of Freedom by President Barack Obama, draws on his rich career in network news covering elections, summits, war, political turmoil and other major news events around the world. The legendary newsman entertains and enlightens audiences with his experiences and observations.

The keynotes will be followed by seven industry-specific tracks with business leaders in communications, construction and engineering, energy and utilities, financial services and insurance, hospitality, life sciences and healthcare and retail. Featured speakers include:

  • Robert Hackl, Senior Vice President, Leasing, Sprint
  • Dr. Philip Tetlock, Ph.D, Annenberg University Professor, University of Pennsylvania
  • Kimberly Greene, Executive Vice President and Chief Operating Officer, Southern Company Services, Inc.
  • Lisa Davis, Global Managing Director, Treasury and Trade Solutions, Citi
  • Steven Marks, Founder, Guzman y Gomez
  • Robert B. Darnell, MD, Ph.D, Heilbrunn Professor and Senior Physician at The Rockefeller Center, Investigator at Howard Hughes Medical Institute, Founding Director at NY Genome Center
  • Jeff Wollen, CIO, Wiggle

“Every day, Oracle provides companies the most sophisticated applications in order to transform their businesses,” Oracle CEO Mark Hurd said. “From planning retailers' merchandise, to running wireless communications networks, to serving as the backbone of our power grids, no technology company can provide the range of industry-specific specialization that Oracle can and no other company can provide it in the cloud today.”

“As the cloud transforms the way industries operate, our team works tirelessly to get our customers where they need to go next,” said Bob Weiler, executive vice president or Oracle’s Global Business Units. “Oracle Industry Connect provides a community of distinguished industry innovators to share ideas and collaborate on the path and solutions for their success.”

For more information about how Oracle is committed to empowering organizations through best-in-class, industry-specific business solutions, visit oracle.com/industries. To learn more about Oracle Industry Connect 2017, go to oracle.com/oracleindustryconnect.

Contact Info
Katie Barron
Oracle Communications
+1 202-904-1138
katie.barron@oracle.com
About Oracle

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit 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.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Katie Barron

  • +1 202-904-1138

Configure Oracle HTTP Server (OHS) 12c (12.2.1.2) on port 80 / 443

Online Apps DBA - Mon, 2017-02-27 05:42

On a UNIX system, TCP ports in a reserved range (typically less than 1024) can only be bound by processes with root privilege. Oracle HTTP Server always runs as a non-root user; that is, the user who installed Oracle Fusion Middleware.  On UNIX, special configuration is required to allow Oracle HTTP Server to bind to […]

The post Configure Oracle HTTP Server (OHS) 12c (12.2.1.2) on port 80 / 443 appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Oracle GROUP BY – The Complete Guide

Complete IT Professional - Mon, 2017-02-27 05:00
The Oracle GROUP BY clause is a powerful clause, especially when analysing large amounts of data. Learn what the GROUP BY clause is and everything you can do with it in this article. In this article, we will cover: What Is The GROUP BY Clause? Sample Data Examples of Aggregate Functions with GROUP BY Filtering […]
Categories: Development

Simple Way to Export Your Data from Oracle Cloud

Andrejus Baranovski - Mon, 2017-02-27 02:05
You should not get stuck in the Cloud. There are various options to create Oracle Cloud backup, but is very important to keep a local copy of your data. One of the simplest options to create a local copy of data from the Oracle Cloud - use Oracle SQL Developer.

Define Oracle Cloud DB connection in SQL Developer (the same as regular DB connection):


Use Database Export utility from Oracle SQL Developer:


Allows to export schema DDL, together with data (various formats, SQL INSERT statements one of them):


You can choose from long list of DB objects to export, this includes indexes, triggers, constraints, tables, etc.:


In my use case I select all objects to export (except PS_TXN):


There is option to filter exported data, again I will export all data:


Schema structure along with data is exported successfully:

Oracle 12cR2: Online tablespace encryption

Yann Neuhaus - Sun, 2017-02-26 13:57

By default, all data is visible in the datafiles. Transparent Tablespace Encryption (TDE) can be used to get them encrypted. It requires Enterprise Edition plus Advanced Security Option. Except in the Oracle Cloud when it is available – and mandatory – in all editions. And we can foresee that security policies will be enforced in the future years, by law or because companies realize their files can be stolen. This means that lot of databases will have to be encrypted, and this may take too long to do it during a maintenance window. In 12.2 we can encrypt online. Online means that we can do it while our application is running, but of course there is a performance overhead on the system.

I’ve run a SLOB workload with reads and writes (PCT_UPDATE=25). Four times the same workload:

  • during the first one, I encrypted the tablespace online
  • the second one is running on the encrypted tablespace
  • during the third one, I decrypted the tablespace online
  • the fourth one is running on the decrypted tablespace

Here is the ASH visualized with Orachrome Lighty:

Online_encryption_ASH_Response_Time

The dark blue is ‘db file sequential read’ is ny 4 SLOB sessions activity. Light blue is all background activity (DBWR, LGWR) and the encrypt/decrypt (db file parallel write).
The green is CPU activity. The brown is free buffer gets: DBWR can’t keep up with the rate of changes we are doing, while encrypting the tablespace.

You may wonder how I was able to have un-encrypted tablespaces on 12.2 which is available only on the Oracle Cloud where encryption is mandatory. This is explained in Oracle Public Cloud 12cR2: TDE is not an option. This means that I created the SLOB database and I have created the wallet.

Configure the TDE keystore

I’ve created the directory
mkdir -p /u01/app/oracle/admin/SLOB/tde_wallet

I declared it in sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/SLOB/tde_wallet)))

I created the wallet
administer key management create keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Opened it
administer key management set keystore open identified by oracle;

Created the master key
administer key management set key identified by oracle with backup;

Optionally created an auto-login wallet
administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Statspack

I’ll show some statistics for the following runs: Inital (when not encrypted), Encryption (when encryption is running concurrently), Encrypted (when tablespace encryption has been completed), Decryption (when decrypt is running concurrently) and Decrypted (once decryption is completed).

Run on non-encrypted tablespace

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.0 0.1 0.02 6.39
DB CPU(s): 0.4 0.0 0.00 0.68
Redo size: 1,064,743.9 18,829.0
Logical reads: 15,635.7 276.5
Block changes: 7,293.4 129.0
Physical reads: 9,451.4 167.1
Physical writes: 3,303.2 58.4

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 817,591 1,000 1 48.9
log file parallel write 12,258 408 33 20.0
db file parallel write 18,284 217 12 10.6
CPU time 128 6.3
db file parallel read 46,263 121 3 5.9

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 26-27
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,201.2 100.0
DB CPU 127.8 10.6
PL/SQL execution elapsed time 1.7 .1
parse time elapsed 0.0 .0
connection management call elapsed 0.0 .0
hard parse elapsed time 0.0 .0
Tablespace encryption elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 1,201.7

Run during tablespace encryption

As soon as I started this SLOB run, I started the encryption:
alter tablespace IOPS encryption encrypt;

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.8 0.1 0.03 8.27
DB CPU(s): 0.4 0.0 0.00 0.72
Redo size: 644,447.5 18,839.1
Logical reads: 9,441.5 276.0
Block changes: 4,412.8 129.0
Physical reads: 5,702.6 166.7
Physical writes: 1,952.0 57.1

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 28-29
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,455.0 99.9
DB CPU 126.9 8.7
Tablespace encryption elapsed time 15.4 1.1
Tablespace encryption cpu time 12.1 .8


Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 546,294 660 1 30.8
free buffer waits 30,704 325 11 15.2
log file parallel write 8,057 304 38 14.2
db file parallel write 9,929 260 26 12.1
db file async I/O submit 6,304 185 29 8.7

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 2,860,788 9,441.5 276.0
consistent gets 2,154,358 7,110.1 207.9
blocks decrypted 850,557 2,807.1 82.1
blocks encrypted 1,042,777 3,441.5 100.6

Run on encrypted tablespace

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.0 0.1 0.02 6.95
DB CPU(s): 0.5 0.0 0.00 0.95
Redo size: 1,057,446.8 18,806.5
Logical reads: 15,534.1 276.3
Block changes: 7,248.4 128.9
Physical reads: 9,415.8 167.5
Physical writes: 3,266.7 58.1

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 30-31
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,201.1 99.9
DB CPU 164.7 13.7
Tablespace encryption elapsed time 19.0 1.6
Tablespace encryption cpu time 10.1 .8

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 824,329 958 1 47.3
log file parallel write 12,207 416 34 20.5
db file parallel write 17,405 202 12 10.0
CPU time 166 8.2
db file parallel read 46,394 113 2 5.6

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 4,706,832 15,534.1 276.3
consistent gets 3,546,519 11,704.7 208.2
blocks decrypted 2,852,666 9,414.7 167.4
blocks encrypted 989,254 3,264.9 58.1

Run during tablespace decryption

As soon as I started this SLOB run, I started the decryption:
alter tablespace IOPS encryption decrypt;

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.9 0.2 0.04 7.56
DB CPU(s): 0.4 0.0 0.00 0.61
Redo size: 606,680.3 19,111.0
Logical reads: 8,817.1 277.8
Block changes: 4,121.4 129.8
Physical reads: 5,294.9 166.8
Physical writes: 1,827.2 57.6

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 515,429 629 1 25.9
free buffer waits 34,335 362 11 14.9
log file parallel write 7,287 293 40 12.1
direct path write 7,703 275 36 11.3
db file parallel write 9,966 270 27 11.1

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 32-33
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,464.6 99.9
DB CPU 117.9 8.0
Tablespace encryption elapsed time 9.4 .6
Tablespace encryption cpu time 4.6 .3

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 2,662,776 8,817.1 277.8
consistent gets 2,001,129 6,626.3 208.7
blocks decrypted 1,026,940 3,400.5 107.1
blocks encrypted 696,105 2,305.0 72.6

Run on decrypted tablespace

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 4.0 0.1 0.02 6.79
DB CPU(s): 0.4 0.0 0.00 0.72
Redo size: 1,060,856.5 18,876.7
Logical reads: 15,565.8 277.0
Block changes: 7,258.6 129.2
Physical reads: 9,418.8 167.6
Physical writes: 3,330.5 59.3

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read 818,717 999 1 42.1
log file parallel write 11,799 421 36 17.8
direct path write 8,887 299 34 12.6
db file parallel write 18,817 222 12 9.4
CPU time 129 5.4

Time Model System Stats DB/Inst: SLOB/SLOB Snaps: 34-35
-> Ordered by % of DB time desc, Statistic name
 
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,201.1 100.0
DB CPU 127.1 10.6
PL/SQL execution elapsed time 1.7 .1
parse time elapsed 0.0 .0
connection management call elapsed 0.0 .0
Tablespace encryption cpu time 0.0 .0
Tablespace encryption elapsed time 0.0 .0

Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads 4,685,294 15,565.8 277.0
consistent gets 3,528,610 11,723.0 208.6
blocks decrypted 271 0.9 0.0
blocks encrypted 10 0.0 0.0

Observations

During encryption and decryption, we have contention on ‘free buffer waits’. When running a workload that is I/O bound, and with updates, the DBWR cannot keep-up when encryption/decryption is running in parallel. Online encryption works like online datafile move: there is a double write, one the the current file and one to the file encrypted one. Only when completed, the reads and writes are directed to ne new file and the old one is removed.

The statistics ‘blocks decrypted’ and ‘block encrypted’ are related to reads and writes from an encrypted tablespace.

The Time Model ‘Tablespace encryption’ statistics are significant only when the tablespace is encrypted, or during encryption/decryption. But the time is not so significant: 1% of DB Time. I’m not completely sure about how to interpret it and it is not yet documented. From my test, it looks like it measures the overhead of reading from encrypted tablespaces.

But fore sure, having the tablespaces encrypted is not a big overhead, and online encryption can be useful to avoid a large maintenance window (it can take few hours to encrypt hundred of GB) but don’t run it at a time where you have lot of modifications.

 

Cet article Oracle 12cR2: Online tablespace encryption est apparu en premier sur Blog dbi services.

Pages

Subscribe to Oracle FAQ aggregator