Feed aggregator

Multi-tenancy database vs. limited disk read throughput

Tom Kyte - Tue, 2017-02-28 04:26
Hi AskTom team, I have relatively small SaaS app with "poor man" multi-tenancy database architecture, there is an tenantId field in each table and application server properly generates SQL queries with tenantId in the where clause. There are th...
Categories: DBA Blogs

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

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