Feed aggregator

Temporary Tables

Tom Kyte - Tue, 2018-08-28 13:06
Hi All, I'm a recent convert from PL/SQL to SQL Developer & Oracle. As such, I used #temptables a ton and was highly discouraged from writing 'super SQL' scripts that tried to do everything in one query. Now, I'm learning that just the opposit...
Categories: DBA Blogs

RMAN PITR recover table Oracle 12c

Yann Neuhaus - Tue, 2018-08-28 10:18

At one client’s site, I had to restore a table someone had partially deleted one week before. Before Oracle 12c, we had to duplicate the target database to another server, and then to export and import data to the target database. But depending on the database size, it could cost a lot of time, and as nobody knew when the delete action happened, it was more practical to use the rman recover table command in order to have multiple versions of the table content.

At first for security, we save the application table:

SQL> create table appuser.employe_save as select * from appuser.employe;

Table created.

My backups are configured on sbt_tape with ddboost, so I thought I only have to run such a command :

run {
ALLOCATE CHANNEL C1 DEVICE TYPE SBT_TAPE PARMS 'BLKSIZE=1048576, 
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so, 
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda_proddb.cfg)' 
FORMAT '%d_%U' ;
recover table appuser.employe
until time "to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')"
auxiliary destination '/tmp/proddb/aux';
}

But I got this error message:

RMAN-03002: failure of recover command at 08/23/2018 10:50:04
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06101: no channel to restore a backup or copy of the control file

The problem is documented with bug 17089942:

The table recovery fails when channels are allocated manually within a run block. The solution consists in defining the channel device type in the rman configuration:

rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Aug 23 13:52:39 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODDB (DBID=271333692)
connected to recovery catalog database

RMAN> configure channel device type sbt_tape parms 'BLKSIZE=1048576, 
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so, 
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda_proddb.cfg)';

starting full resync of recovery catalog
full resync complete
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'BLKSIZE=1048576, 
SBT_LIBRARY=/opt/dpsapps/dbappagent/lib/lib64/libddboostora.so, 
SBT_PARMS=(CONFIG_FILE=/opt/dpsapps/dbappagent/config/oracle_ddbda_proddb.cfg)';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Then connected with rman we can run the following recover command in order to restore the employe table with a new name employe_16082018:

RMAN> run {
 recover table appuser.employe
until time "to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')"
auxiliary destination '/tmp/proddb/aux'
remap table appuser.employe:employe_16082018;
}

What happens ? Oracle will create a pseudo database under /tmp/proddb/aux with SYSTEM SYSAUX TEMP UNDO and data tablespaces, then it restores the appuser.employe table at the specified date and renames it with the specified new name. Finally Oracle deletes the pseudo database.

RMAN> run {
2> recover table appuser.employe
3> until time "to_date('16-AUG-2018 08:00:00','DD-MON-YYYY HH24:MI:SS')"
4> auxiliary destination '/tmp/PRODDB/aux'
5> remap table appuser.employe:employe_16082018;
6> }

Starting recover at 23-AUG-2018 14:03:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=765 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=2562 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: database app agent Oracle v4.5.0.0

Creating automatic instance, with SID='ecvh'

initialization parameters used for automatic instance:
db_name=PRODDB
db_unique_name=ecvh_pitr_PRODDB
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u00/app/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/tmp/PRODDB/aux
log_archive_dest_1='location=/tmp/PRODDB/aux'
#No auxiliary parameter file used

…..

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_ecvh_cesy" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_ecvh_cesy":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "APPUSER"."EMPLOYE_16082018"           7.137 MB   16173 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_ecvh_cesy" successfully completed at Thu Aug 23 14:10:28 2018 elapsed 0 00:00:10
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_temp_fqx8w89p_.tmp deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/onlinelog/o1_mf_3_fqx90jyn_.log deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/onlinelog/o1_mf_2_fqx90hyd_.log deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/onlinelog/o1_mf_1_fqx90gwo_.log deleted
auxiliary instance file /tmp/PRODDB/aux/ECVH_PITR_PRODDB/datafile/o1_mf_affac_1_fqx8xybx_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_sysaux_fqx8p1p7_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_undotbs1_fqx8nskn_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/datafile/o1_mf_system_fqx8olyx_.dbf deleted
auxiliary instance file /tmp/PRODDB/aux/PRODDB_CI/controlfile/o1_mf_fqx8nb57_.ctl deleted
auxiliary instance file tspitr_ecvh_63884.dmp deleted
Finished recover at 23-AUG-2018 14:10:29

The recover was quite fast, so I had the possibility to run multiple recover at different times allowing me to understand at which time the delete command happened:

SQL> select table_name from all_tables where owner = 'APPUSER' and table_name like 'EMPLOYE%'

TABLE_NAME
--------------------------------------------------------------------------------
EMPLOYE
EMPLOYE_16082018
EMPLOYE_22072018
EMPLOYE_SAVE

SQL> select count(*) from appuser.employe_22072018;

  COUNT(*)
----------
     16141

SQL> r
  1* select count(*) from appuser.employe_16082018

  COUNT(*)
----------
     16173

SQL> select count(*) from appuser.employe;

  COUNT(*)
----------
     16226

I already tested this recover feature on my own virtual machine on a test database. Running this recover command on a production database allowed me to discover the Oracle bug when your backups are on tape. Finally using ddboost with rman is so fast that you do not have to hesitate to restore tables with Oracle 12c even with a huge volumetry.

 

Cet article RMAN PITR recover table Oracle 12c est apparu en premier sur Blog dbi services.

Podruzhka Brings Strategic Pricing Approach to Russian Drugstore Market

Oracle Press Releases - Tue, 2018-08-28 07:01
Press Release
Podruzhka Brings Strategic Pricing Approach to Russian Drugstore Market Specialty Retailer Creates Market Agility with Oracle Retail Solutions and Veltio Integration

Redwood Shores, Calif.—Aug 28, 2018

Specialty retailer Podruzhka has deployed Oracle Retail Planning and Optimization to engage and grow a loyal customer base with competitive pricing. With the help of Oracle Partner Network Platinum Partner Veltio, Podruzhka streamlined a pricing process that offered automation, best practices and flexibility to respond to the market demand with Oracle Retail solutions.  Podruzhka had previously implemented Oracle Retail Demand Forecasting and Veltio Allocation and Execution solutions which led the retailer to further their investment as they sought to improve the accuracy and agility of their pricing strategy.

Podruzhka is uniquely positioned within the Russian drugstore market with 210 stores catered to providing women with inexpensive personal care products, cosmetics, perfumes, jewelry, accessories and cleaning products. Each Podruzhka store carries up to 15,000 SKUs within a compact 140-180 square meter footprint in residential areas, establishing the drugstore as a local staple.

“In the Oracle Retail 4-Dimensions of Retail research, we discovered that 84% of global consumers say that competitive pricing and promotions first and foremost are the most important aspect of their shopping experience,” said Chris James, Vice President EMEA, Oracle Retail. “In Russia, market conditions can change quickly and with Oracle Retail Podruzhka has created ability to manage multiple price zones that appeal to the consumer in the moment of intent.”

“With our responsive pricing approach, we hope to increase customer loyalty through competitive pricing and selling opportunities while respecting the financial targets of the company,” said Alexander Golubev, IT Director, Podruzhka. “Oracle Retail provided us with an industry rich solution with flexibility and scale. Veltio delivered the implementation on-time and on-budget with a deep understanding our business needs.”

“This project was a great milestone for Veltio in the Russian market by highlighting the opportunity of Oracle Retail solutions in regional context. Local market conditions and the crash of the ruble forced Russian retailers to start thinking more about the efficiencies offered by advanced pricing, assortment and supply chain management,” said Kostya Prays, EMEA Business Development Director, Veltio. “We are happy to help retailers like Podruzhka leverage this technology to find a competitive advantage.”

Contact Info
Matt Torres
Oracle
4155951584
matt.torres@oracle.com
About Oracle Retail

Oracle provides retailers with a complete, open, and integrated suite of best-of-breed business applications, cloud services, and hardware that are engineered to work together and empower commerce. Leading fashion, grocery, and specialty retailers use Oracle solutions to anticipate market changes, simplify operations and inspire authentic brand interactions. For more information, visit our website at www.oracle.com/retail.

About Oracle

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

Trademarks

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

Talk to a Press Contact

Matt Torres

  • 4155951584

Oracle Cloud Helps Detroit Save $1 Million

Oracle Press Releases - Tue, 2018-08-28 07:00
Press Release
Oracle Cloud Helps Detroit Save $1 Million Oracle Cloud revolutionizes operations of one of the country’s oldest water and sewer systems

Redwood Shores, Calif.—Aug 28, 2018

The City of Detroit Water and Sewerage Department, (DWSD), a Michigan municipal utility that provides water and sewer services to the residents of Detroit, today announced major decreases in operational costs along with improved availability and performance after transitioning its traditional on-premise data center to Oracle Cloud. In total, DWSD calculated a 15-17 percent decrease in operational costs since it began its collaboration with Oracle.

Oracle works with numerous state governments and local municipalities to deploy innovative solutions designed to provide government agencies more agility and options as they manage operations and deliver constituent services. These new savings for DWSD further highlight Oracle’s ongoing commitment to modernizing services that impact citizen services.

“Moving to Oracle’s Cloud puts us on a modern database platform for the first time,” said Dan Rainey, CIO, Detroit Water and Sewerage Department. “Oracle is a strategic partner for us and has transformed our platform to be highly available, resilient, and affordable.”

The citizens of Detroit expect improved service delivery and better response to questions about essential services—without the burden of increasing costs. The utility saw an opportunity to meet this need by fully integrating technology into daily work functions to modernize DWSD, enhancing customer service to internal and external users while reducing predictable IT costs. At the same time, DWSD’s Oracle Cloud deployment creates greater flexibility to meet changing requirements from the board and the city. Now, the 185-year-old utility relies on Oracle Database Exadata Cloud Service, Oracle Cloud Infrastructure, Oracle Cloud Platform and Oracle Cloud Applications as the foundation for its digital transformation.

As a result of Oracle Cloud and other tools implemented starting in May 2017, DWSD has reinvested capital dollars, otherwise spent on traditional IT hardware and software, on tools to improve customer service. Cloud technology has proven essential for DWSD in enhancing the level of performance of its systems, transforming the water utility into an organization that provides highly responsive customer service.

“Our work with the city of Detroit is an example of how we can quickly help our public sector customers realize greater efficiencies,” said Mark Johnson, senior vice president, Oracle Public Sector. “By reducing their cost structures with modern technology, DWSD can focus business strategies and priorities toward their number one priority—their customers.”

To support DWSD’s efforts to lower costs and enhance the customer experience, the utility is leveraging a number of additional technologies, including Oracle Database Backup Service, Oracle Integration Cloud, Oracle SOA Cloud, Oracle Storage Cloud and Oracle Business Intelligence Cloud Service.

Contact Info
Katie Barron
Oracle
202.904.1138
katie.barron@oracle.com
Dan Muñoz
Oracle
650.506.2904
dan.munoz@oracle.com
Bryan Peckinpaugh
DWSD
313.410.2954
peckinpaughb@detroitmi.gov
About Oracle

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

About Detroit Water and Sewerage Department

The Detroit Water and Sewerage Department (DWSD) serves over 200,000 Detroit residential and commercial customers. DWSD’s water network consists of more than 2,700 miles of transmission and distribution mains and nearly 3,000 miles of sewer collection piping.

To learn more about the Detroit Water and Sewerage Department or to request water services, make payments, or report water problems, call DWSD Customer Care at 313.267.8000 or visit: www.detroitmi.gov/dwsd.

Trademarks

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

Talk to a Press Contact

Katie Barron

  • 202.904.1138

Dan Muñoz

  • 650.506.2904

Bryan Peckinpaugh

  • 313.410.2954

Oracle Critical Patch Update July 2018: Oracle E-Business Suite Analysis & Impact

Online Apps DBA - Tue, 2018-08-28 04:48

The Critical Patch Update for July 2018 was released on July 17th, 2018. [BLOG] Oracle Critical Patch Update July 2018: Oracle E-Business Suite Analysis & Impact Visit: https://k21academy.com/appsdba33 if you want to learn about it. The Critical Patch Update for July 2018 was released on July 17th, 2018. [BLOG] Oracle Critical Patch Update July 2018: […]

The post Oracle Critical Patch Update July 2018: Oracle E-Business Suite Analysis & Impact appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

[VLOG]: Oracle Cloud Infrastructure (OCI): New Features Aug 2018

Online Apps DBA - Tue, 2018-08-28 03:03

Do You know about the Oracle Cloud Infrastructure New Features? [VLOG] Oracle Cloud Infrastructure (OCI) Visit https://k21academy.com/oci14 & know more about, how these features are useful to you! Do You know about the Oracle Cloud Infrastructure New Features? [VLOG] Oracle Cloud Infrastructure (OCI) Visit https://k21academy.com/oci14 & know more about, how these features are useful to […]

The post [VLOG]: Oracle Cloud Infrastructure (OCI): New Features Aug 2018 appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Deploy WebLogic docker images using Docker Toolbox and Virtual Box on Windows

Yann Neuhaus - Tue, 2018-08-28 02:16

I was interested to run Docker on my Windows machine and found out the Docker Toolbox for Windows that configure itself with the already installed VirtualBox at installation time.

Once installed, You can start the Docker QuickStart shell preconfigured for a Docker command-line environment. At startup time it will start a VM named default and will be ready to work with Docker.
Starting "default"...
(default) Check network to re-create if needed...
(default) Waiting for an IP...
Machine "default" was started.
Waiting for SSH to be available...
Detecting the provisioner...
Started machines may have new IP addresses. You may need to re-run the `docker-machine env` command.
Regenerate TLS machine certs? Warning: this is irreversible. (y/n): Regenerating TLS certificates
Waiting for SSH to be available...
Detecting the provisioner...
Copying certs to the local machine directory...
Copying certs to the remote machine...
Setting Docker configuration on the remote daemon...

## .
## ## ## ==
## ## ## ## ## ===
/"""""""""""""""""\___/ ===
~~~ {~~ ~~~~ ~~~ ~~~~ ~~~ ~ / ===- ~~~
\______ o __/
\ \ __/
\____\_______/

docker is configured to use the default machine with IP 192.168.99.100
For help getting started, check out the docs at https://docs.docker.com

Start interactive shell
$
The “docker-machine env” displays the machine environment that has been created:
$ docker-machine env
export DOCKER_TLS_VERIFY="1"
export DOCKER_HOST="tcp://192.168.99.100:2376"
export DOCKER_CERT_PATH="C:\Users\PBR\.docker\machine\machines\default"
export DOCKER_MACHINE_NAME="default"
export COMPOSE_CONVERT_WINDOWS_PATHS="true"
# Run this command to configure your shell:
# eval $("C:\Program Files\Docker Toolbox\docker-machine.exe" env)

Here is how to directly set the environment from it:

$ eval $("C:\Program Files\Docker Toolbox\docker-machine.exe" env)

Once the environment is set, it can be displayed as follow:


$ docker info
Containers: 9
Running: 0
Paused: 0
Stopped: 9
Images: 2
Server Version: 18.06.0-ce
Storage Driver: aufs
Root Dir: /mnt/sda1/var/lib/docker/aufs
Backing Filesystem: extfs
Dirs: 34
Dirperm1 Supported: true
Logging Driver: json-file
Cgroup Driver: cgroupfs
Plugins:
Volume: local
Network: bridge host macvlan null overlay
Log: awslogs fluentd gcplogs gelf journald json-file logentries splunk syslog
Swarm: inactive
Runtimes: runc
Default Runtime: runc
Init Binary: docker-init
containerd version: d64c661f1d51c48782c9cec8fda7604785f93587
runc version: 69663f0bd4b60df09991c08812a60108003fa340
init version: fec3683
Security Options:
seccomp
Profile: default
Kernel Version: 4.9.93-boot2docker
Operating System: Boot2Docker 18.06.0-ce (TCL 8.2.1); HEAD : 1f40eb2 - Thu Jul 19 18:48:09 UTC 2018
OSType: linux
Architecture: x86_64
CPUs: 1
Total Memory: 1.955GiB
Name: default
ID: AV7B:Z7GA:ZWLU:SNMY:ALYL:WTCT:2X2F:NHPY:2TRP:VK27:JY3L:PHJO
Docker Root Dir: /mnt/sda1/var/lib/docker
Debug Mode (client): false
Debug Mode (server): false
Username: pbrand
Registry: https://index.docker.io/v1/
Labels:
provider=virtualbox
Experimental: false
Insecure Registries:
127.0.0.0/8
Live Restore Enabled: false

I will use a Docker image provided by Oracle on the Docker store: the Oracle WebLogic 12.2.1.3 image. First I need to sign in to the Docker Store

docker login
Login with your Docker ID to push and pull images from Docker Hub. If you don't have a Docker ID, head over to https://hub.docker.com to create one.
Username: pbrand
Password:
Login Succeeded

Then I can pull the Oracle WebLogic 12.2.1.3 image


docker pull store/oracle/weblogic:12.2.1.3
12.2.1.3: Pulling from store/oracle/weblogic
9fd8609e6e4d: Pull complete
eac7b4a33e34: Pull complete
b6f7d13c859b: Pull complete
e0ca246b2272: Pull complete
7ba4d6bfba43: Pull complete
5e3b8c4731f0: Pull complete
97623ceb6339: Pull complete
Digest: sha256:4c7ce451c093329784a2808a55cd4fc4f1e93d8444b8492a24148d283936add9
Status: Downloaded newer image for store/oracle/weblogic:12.2.1.3

Display all images in my Docker:


$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
store/oracle/weblogic 12.2.1.3 c6bb22ff0ea8 2 weeks ago 1.14GB

In the docker repository, for the Oracle WebLogic 12.2.1.3 image, it is written that the Administrator user should be provided using a domain.properties file having the format below and provided in the command lline to start the Docker image.
The format of the domain.properties file is key value pair:

username=myadminusername
password=myadminpassword

The command line suggested is the following:


$ docker run -d -p 7001:7001 -p 9002:9002 -v $PWD/domain.properties:/u01/oracle/properties/domain.properties store/oracle/weblogic:12.2.1.3

This run command is fine on Linux but doesn’t suite to Windows environment. The created domain.properties file is on the C: drive on windows and the mapping can’t use environment variables like PWD.
In my case, the Docker run command to run is the following:

$ docker run -d --name wls12213 -p 7001:7001 -p 9002:9002 -v //c/Users/PBR/docker_weblogic/domain.properties:/u01/oracle/properties/domain.properties store/oracle/weblogic:12.2.1.3
670fc3bd2c8131b71ecc6a182181d1f03a4832a4c0e8d9d530e325e759afe151

With the -d option it displays only the instance ID, no logs.

Checking the logs using the Docker log command:

$ docker logs wls12213

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

domain_name : [base_domain] admin_listen_port : [7001] domain_path : [/u01/oracle/user_projects/domains/base_domain] production_mode : [prod] admin name : [AdminServer] administration_port_enabled : [true] administration_port : [9002]

I noticed from the logs that the Administration channel is enabled and listening on HTTPS Port 9002. The URL to browse to the WebLogic Administration Console is then:
https://192.168.99.100:9002/console
wls12213_console_servers1

 

Cet article Deploy WebLogic docker images using Docker Toolbox and Virtual Box on Windows est apparu en premier sur Blog dbi services.

Tunning Between clause

Tom Kyte - Mon, 2018-08-27 18:46
i am trying to tune a query which contains between clause in Oracle 11g. i have table employee(id number, join_dt date, end_dt date) which has 10 million records. and it has index on join_dt,end_dt first run, dbms_stats.gather_table_stats(owne...
Categories: DBA Blogs

FOPEN to sub folders

Tom Kyte - Mon, 2018-08-27 18:46
Hello, I am trying to find a way to write a file into the sub folder of an Oracle Directory. I can write into the base of the oracle directory but not into the sub folders. To keep it simple, This is what we have that currently works, after that i...
Categories: DBA Blogs

ORA-00600: internal error code, arguments: [156057], [], [], [], [], [], [], [], [], [], [],

Tom Kyte - Mon, 2018-08-27 18:46
Hi Tom, Our database is oracle 11.2.0.3. My customer met an error "ORA-00600: internal error code, arguments: [156057], [], [], [], [], [], [], [], [], [], []," when he did 'select * from UPL_SECTOR'. UPL_SECTOR is a table he created by himself...
Categories: DBA Blogs

Move historical data between databases

Tom Kyte - Mon, 2018-08-27 18:46
Hello Tom See how you could optimize moving records (historical by date) from one table in a production database to another table in another historical database in an automatic way. Could you support me in Oracle Partition? It could be used ex...
Categories: DBA Blogs

Integrate Oracle E-Business Suite (EBS) R12 with OAM/OID/OUD 12c (12.2.1.3.0) High level Steps

Online Apps DBA - Mon, 2018-08-27 03:31

Do you want to learn How to Integrate Oracle E-Business Suite Release R12 (12.2 & 12.1) with Oracle Identity & Access Management 12c Release 2 Patchset 3 (12.2.1.3.0)? [BLOG] Integrate Oracle E-Business Suite (EBS) R12 with OAM/OID/OUD 12c (12.2.1.3.0) High level Steps Visit: https://k21academy.com/ebsoam25 to get the answer. Do you want to learn How to […]

The post Integrate Oracle E-Business Suite (EBS) R12 with OAM/OID/OUD 12c (12.2.1.3.0) High level Steps appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

[BLOG] Oracle EBS Cloud Admin Tool | OCI – C | OCI | Cloud at Customer (Part -1)

Online Apps DBA - Sun, 2018-08-26 22:00

Do you now What is EBS Cloud Admin tool? Visit: https://k21academy.com/ebscloud25 & learn more on the Centralized Tool which is used for managing multiple EBS environment on Oracle Cloud… Do you now What is EBS Cloud Admin tool? Visit: https://k21academy.com/ebscloud25 & learn more on the Centralized Tool which is used for managing multiple EBS environment […]

The post [BLOG] Oracle EBS Cloud Admin Tool | OCI – C | OCI | Cloud at Customer (Part -1) appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Partitioning -- 3c : Unique Index[es] on Partitioned Table

Hemant K Chitale - Sun, 2018-08-26 03:49
Let's explore what sort of Unique Indexes you can create on a Partitioned Table.

There are three types of partitioning for Indexes :

a  Global (Non-Partitioned)

b  Global Partitioned

c  Local Partitioned

Can a Unique Index be created using either type ?

Let me start with another table, SALES_DATA_2  which has the same structure and Partition Key as SALES_DATA, except that it doesn't have the Primary Key definition that builds the Unique Index.

SQL> l
1 CREATE TABLE SALES_DATA_2
2 ( SALE_ID NUMBER,
3 SALE_DATE DATE,
4 INVOICE_NUMBER VARCHAR2(21),
5 CUSTOMER_ID NUMBER,
6 PRODUCT_ID NUMBER,
7 SALE_VALUE NUMBER
8 )
9 TABLESPACE HEMANT
10 PARTITION BY RANGE (SALE_DATE)
11 (PARTITION P_2018 VALUES LESS THAN (TO_DATE(' 2019-01-01','YYYY-MM-DD'))
12 TABLESPACE TBS_YEAR_2018 ,
13 PARTITION P_2019 VALUES LESS THAN (TO_DATE(' 2020-01-01','YYYY-MM-DD'))
14 TABLESPACE TBS_YEAR_2019 ,
15 PARTITION P_2020 VALUES LESS THAN (TO_DATE(' 2021-01-01','YYYY-MM-DD'))
16 TABLESPACE TBS_YEAR_2020 ,
17 PARTITION P_MAXVALUE VALUES LESS THAN (MAXVALUE)
18* TABLESPACE HEMANT )
SQL> /

Table created.

SQL>


Next, I try a Global (Non-Partitioned) Unique Index on SALE_ID.  Note that the "GLOBAL" Keyword is optional if it is Non-Partitioned.

SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id) global
3 tablespace hemant
4 /

Index created.

SQL>
SQL> select partitioned, status
2 from user_indexes
3 where index_name = upper('sales_2_uk')
4 /

PAR STATUS
--- --------
NO VALID

SQL> drop index sales_2_uk;

Index dropped.

SQL>


Effectively, this Global Index is the same as the Primary Key index on SALES_DATA that I built earlier.

Next, I try a Unique Global Partitioned Index on the same column.

SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id) global
3 partition by range (sale_id)
4 (partition p_1mill values less than (1000001) tablespace new_indexes,
5 partition p_2mill values less than (2000001) tablespace new_indexes,
6 partition p_3mill values less than (3000001) tablespace new_indexes,
7 partition p_maxval values less than (maxvalue) tablespace new_indexes)
8 /

Index created.

SQL>
SQL> select uniqueness, partitioned, status
2 from user_indexes
3 where index_name = upper('sales_2_uk')
4 /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE YES N/A

SQL>
SQL> l
1 select column_position, column_name
2 from user_part_key_columns
3 where name = upper('sales_2_uk')
4* order by column_position
SQL> /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
1 SALE_ID

SQL>
SQL> select partition_name, status
2 from user_ind_partitions
3 where index_name = upper('sales_2_uk')
4 order by partition_position
5 /

PARTITION_NAME STATUS
------------------------------ --------
P_1MILL USABLE
P_2MILL USABLE
P_3MILL USABLE
P_MAXVAL USABLE

SQL>


So, that is a valid Unique Global Partitioned Index.

The next attempt is a Unique Local Partitioned Index -- i.e. partitioned by the same key as the Table.

SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id) local
3 /
on sales_data_2 (sale_id) local
*
ERROR at line 2:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index


SQL> !oerr ora 14039
14039, 00000, "partitioning columns must form a subset of key columns of a UNIQUE index"
// *Cause: User attempted to create a UNIQUE partitioned index whose
// partitioning columns do not form a subset of its key columns
// which is illegal
// *Action: If the user, indeed, desired to create an index whose
// partitioning columns do not form a subset of its key columns,
// it must be created as non-UNIQUE; otherwise, correct the
// list of key and/or partitioning columns to ensure that the index'
// partitioning columns form a subset of its key columns

SQL>
SQL> create unique index sales_2_uk
2 on sales_data_2 (sale_id, sale_date) local
3 /

Index created.

SQL>
SQL> select uniqueness, partitioned, status
2 from user_indexes
3 where index_name = upper('sales_2_uk')
4 /

UNIQUENES PAR STATUS
--------- --- --------
UNIQUE YES N/A

SQL> select column_position, column_name
2 from user_part_key_columns
3 where name = upper('sales_2_uk')
4 order by column_position
5 /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
1 SALE_DATE

SQL> select column_position, column_name
2 from user_ind_columns
3 where index_name = upper('sales_2_uk')
4 order by column_position
5 /

COLUMN_POSITION COLUMN_NAME
--------------- ----------------
1 SALE_ID
2 SALE_DATE

SQL>
SQL> select partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name = upper('sales_2_uk')
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
P_2018 TBS_YEAR_2018 USABLE
P_2019 TBS_YEAR_2019 USABLE
P_2020 TBS_YEAR_2020 USABLE
P_MAXVALUE HEMANT USABLE

SQL>


So, a Unique Local Partitioned Index must include the Table Partition Key as a subset of the Index Key columns.  This is something you must consider when Partitioning the Table and Index both.
(Also, note how USER_PART_KEY_COLUMNS doesn't show SALE_ID as a Partition Key.  This is in 11.2.0.4)



Categories: DBA Blogs

ODC Latin America Tour : It’s a Wrap!

Tim Hall - Sat, 2018-08-25 19:43

The ODC Latin America Tour (Northern Leg) is now over for me. I still can’t really believe I get invited to these tours and actually do them.

I’m simultaneously excited and terrified by these tours. I have to admit I hate the travelling, but I love meeting people around the world who share a mutual interest. Give me an opportunity to geek out and I’m all over it.

After the year I’ve had so far (see here) I was more nervous about this tour than any previous one. My nightmare seemed to be coming true when I needed medical attention on the plane in Quito, but after that glitch it went really well, and I’m glad I didn’t chicken out!

Thanks to all the individual user groups for inviting me and making me welcome in your country. Thank to all the attendees for coming along and supporting the events. Meeting all of you is the best bit of doing this. Thanks as always to the Oracle ACE Program and Oracle Developer Champion program for making this possible for me, without ever expecting anything from me other than contributing to the community.

The posts that I put out related to this tour are listed here.

Cheers

Tim…

ODC Latin America Tour : It’s a Wrap! was first posted on August 26, 2018 at 1:43 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

systemd: systemd-notify not working for non-root-users

Dietrich Schroff - Sat, 2018-08-25 12:53
Sometimes you have to write your own startup scripts. Recent linux distributions require systemd scripts. This is not really a problem except you have to fulfill the following requirements:
  • Run the service as a non-root-user
  • The service has a startup phase and you want to start the next startup scripts after this startup phase
So the systemd-script has to look like this:
# cat /lib/systemd/system/TEST.service
[Unit]
Description=MyTestSystemdConfiguration

[Service]
User=schroff
Type=notify
ExecStart=/home/schroff/bin/test.sh
NotifyAccess=allThe service startup scripts have to look like this:
$ cat /home/schroff/bin/test.sh
#!/bin/bash

echo Starting serivce
sleep 10
#Starting your services
echo Services started

/bin/systemd-notify --ready
echo Notify done

while test 1 do
  sleep 600
done
#keep this scripts running, as long your service runsIn the startup phase you will get the following:
schroff@zerberus:~/bin$ systemctl status TEST.service
● TEST.service - MyTestSystemdConfiguration
   Loaded: loaded (/lib/systemd/system/TEST.service; static; vendor preset: enabled)
   Active: activating (start) since 19:39:27 CET; 7s ago
 Main PID: 17390 (test.sh)
    Tasks: 2 (limit: 4915)
   Memory: 532.0K::
      CPU: 7ms
   CGroup: /system.slice/TEST.service
           ├─17390 /bin/bash /home/schroff/bin/test.sh
           └─17395 sleep 10

19:39:27 zerberus systemd[1]: Starting MyTestSystemdConfiguration...
19:39:27 zerberus test.sh[17390]: Starting serivceAnd after the startup phase this is the output (if there were no errors):
schroff@zerberus:~/bin$ systemctl status TEST.service
● TEST.service - MyTestSystemdConfiguration
   Loaded: loaded (/lib/systemd/system/TEST.service; static; vendor preset: enabled)
   Active: active (running) since 19:38:38 CET; 3s ago
 Main PID: 17242 (test.sh)
    Tasks: 2 (limit: 4915)
   Memory: 932.0K
      CPU: 9ms
   CGroup: /system.slice/TEST.service
           ├─17242 /bin/bash /home/schroff/bin/test.sh
           └─17259 sleep 600

19:38:28 zerberus systemd[1]: Starting MyTestSystemdConfiguration...
19:38:28 zerberus test.sh[17242]: Starting serivce
19:38:38 zerberus test.sh[17242]: Services started
19:38:38 zerberus systemd[1]: Started MyTestSystemdConfiguration.
19:38:38 zerberus test.sh[17242]: Notify doneBut sometime you will get:
# systemctl restart TEST.service
Job for TEST.service failed because a timeout was exceeded.
See "systemctl  status TEST.service" and "journalctl  -xe" for details.19:44:46 zerberus systemd[1]: TEST.service: Start operation timed out. Terminating.
19:44:46 zerberus systemd[1]: Failed to start MyTestSystemdConfiguration.
-- Subject: Unit TEST.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Unit TEST.service has failed.
--
-- The result is failed.
19:44:46 zerberus systemd[1]: TEST.service: Unit entered failed state.
19:44:46 zerberus systemd[1]: TEST.service: Failed with result 'timeout'.
Note that this will happen after 600s (default). You can change this with the parameter (systemd configuration, see manpage systemd.service)
TimeoutSecBut changing this Parameter will not help, because systemd status will never enter the state "active (running)".

The problem is systemd-notify doesn't work, since it lives too short (Redhat Bugzilla).


A workaround is described in that bug entry:
Instead of
systemd-notify --readyuse
python -c "import systemd.daemon, time; systemd.daemon.notify('READY=1'); time.sleep(5)"

How Backup Spfile to Pfile Saved My Arse

Michael Dinh - Sat, 2018-08-25 11:43

Typically when I perform backup review, I always suggest to add the following:

run {
allocate channel c1 device type disk;
SQL "alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
SQL "create pfile=''/tmp/init@.ora'' from spfile";
release channel c1;
}

Example:

RMAN> run {
allocate channel c1 device type disk;
2> 3> SQL "alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
4> SQL "create pfile=''/tmp/init@.ora'' from spfile";
5> release channel c1;
6> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=31 instance=hawk1 device type=DISK

sql statement: alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs

sql statement: create pfile=''/tmp/init@.ora'' from spfile

released channel: c1

RMAN> exit

[oracle@racnode-dc1-1 tmp]$ ll /tmp/init*
-rw-r--r-- 1 oracle dba 1978 Aug 25 21:11 /tmp/inithawk1.ora
[oracle@racnode-dc1-1 tmp]$ ll /tmp/ctl*
-rw-r--r-- 1 oracle dba 7318 Aug 25 21:11 /tmp/ctl_hawk1_trace.sql
[oracle@racnode-dc1-1 tmp]$ sysresv|tail -1
Oracle Instance alive for sid "hawk1"
[oracle@racnode-dc1-1 tmp]$

When on RAC, do not create pfile to it’s default destination, e.g. SQL “create pfile from spfile”;

ORIGINAL LOCATION:
====================================================================================================
$ asmcmd ls -l +DATA/SOXPA/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 17 10:00:00  Y    spfile.321.984394591
PARAMETERFILE  UNPROT  COARSE   AUG 17 10:00:00  N    spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.321.984394591

BIG OOPS: NOTHING THERE!
====================================================================================================
$ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE

ERROR: Created wrong spfile.
====================================================================================================
$ asmcmd ls -l +DATA/WH02A/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 23 19:00:00  Y    spfile.380.984672023
PARAMETERFILE  UNPROT  COARSE   AUG 23 19:00:00  N    spfilewh02a.ora => +DATA/WH02A/PARAMETERFILE/spfile.380.984672023
PARAMETERFILE  UNPROT  COARSE   AUG 24 14:00:00  N    spfilehawka.ora => +DATA/WH01A/PARAMETERFILE/spfile.321.985008497

Create copy of pfile.
====================================================================================================
$ ll *.good
-rw-r--r--. 1 oracle oinstall 2207 Aug 24 14:57 inithawka4.ora.good

Check controlfile location for pfile.
====================================================================================================
$ cat inithawka4.ora.good 
*.control_files='+DATA/hawka/controlfile/current.272.984393927','+FRA/hawka/controlfile/current.307.984393927'#Restore Controlfile

Check database and create new spfile from pfile.
====================================================================================================
HOST04:(SYS@hawka4):PRIMARY> show parameter spfile;

NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------------------------------------------------------------------------------------
spfile                         string      +DATA/hawka/parameterfile/spfilehawka.ora

HOST04:(SYS@hawka4):PRIMARY> show parameter control_file 

NAME                           TYPE        VALUE
------------------------------ ----------- ----------------------------------------------------------------------------------------------------
control_file_record_keep_time  integer     7
control_files                  string      +DATA/hawka/controlfile/current.272.984393927, +FRA/hawka/controlfile/current.307.984393927

HOST04:(SYS@hawka4):PRIMARY> create spfile='+DATA/HAWKA/PARAMETERFILE/spfilehawka4.ora' from pfile='/u01/app/oracle/db/11.2.0.4/dbs/inithawka4.ora.good';

File created.

HOST04:(SYS@hawka4):PRIMARY> exit

rmalias and mkalias for NEW SPFILE.
====================================================================================================
$ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka4.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077

$ asmcmd
ASMCMD> cd +DATA/HAWKA/PARAMETERFILE
ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka4.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077

ASMCMD> rmalias spfilehawka4.ora

ASMCMD> mkalias +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 spfilehawka.ora

ASMCMD> ls -lt
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077
ASMCMD> exit

$ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  Y    spfile.1077.985015077
PARAMETERFILE  UNPROT  COARSE   AUG 24 15:00:00  N    spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077
$ 

Verify pfile can be created from spfile.
====================================================================================================
HOST04:(SYS@hawka4):PRIMARY> create pfile='/tmp/init@.ora' from spfile;

File created.

HOST04:(SYS@hawka4):PRIMARY> exit
====================================================================================================
oracle@p2dbccx04:hawka4:/home/oracle
$ ll /tmp/inithawka4.ora
-rw-r--r--. 1 oracle asmadmin 2207 Aug 24 15:24 /tmp/inithawka4.ora

<p>&#160; [BLOG] Big Data Hadoop:

Online Apps DBA - Sat, 2018-08-25 08:00

  [BLOG] Big Data Hadoop: Introduction to Apache Spark Visit: https://k21academy.com/hadoop19 & find all the answers for: 1) What is Apache Spark 2) It’s Features 3) Apache Spark Components & Architecture   [BLOG] Big Data Hadoop: Introduction to Apache Spark Visit: https://k21academy.com/hadoop19 & find all the answers for: 1) What is Apache Spark 2) It’s Features 3) […]

The post appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Spreadsheet Upload

Tom Kyte - Fri, 2018-08-24 17:26
Hi there, If there is a way to upload the spreadsheet data in our existing application? If possible please send your answers. Regards, Aravindan Prem
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator