Feed aggregator

Fast extract from oracle to TEXT/CSV file

Tom Kyte - Thu, 2017-06-08 04:26
Hi Oracle Gurus! We have encountered strange speed issue and need your assistance... Setting: 1) Oracle 11.2 Exadata machine, 4 nodes, 2) several linux servers which need large amounts of data (10-s of gigabytes) from Oracle extracted to tex...
Categories: DBA Blogs

Connection Pool from App Server and RAM usage in Database Server

Tom Kyte - Thu, 2017-06-08 04:26
Hi Tom, Configuration:- --------------------- 1) Oracle weblogic app server is used 2) The connection pool in side weblogic is set as MIN=1200 INITIAL=1200 MAX=1200 3) 8 managed + 1 admin servers inside weblogic 4) so total number sessions es...
Categories: DBA Blogs

trace files location

Tom Kyte - Thu, 2017-06-08 04:26
Tom right now every time I trace my session Iam having to ask my dba to email my trace file and the tkprof outputfile to me, as I dont have access to the files on the database server (os-unix,a nd my client is on windows 2000) side. is there a...
Categories: DBA Blogs

Webcast: Advanced Architectures for Oracle E-Business Suite

Steven Chan - Thu, 2017-06-08 02:00

Oracle University has a large number of free recorded webcasts for Oracle E-Business Suite.  Here's an excellent webcast for a wide range of architectural options for more-complex deployment requirements:

Elke Phelps, Senior Principal Product Manager shares how to build on the three-tier logical Oracle E-Business Suite architecture to meet your business requirements for high availability, scalability, and performance. Topics include load-balancing and clustering. Understand how to securely deploy Oracle E-Business Suite for internet access with a demilitarized zone (DMZ). Apply various topics covered in the session to assist you with planning a disaster recovery project for your environment. Learn about new tools that automate scale-out and other advanced architectures when deploying E-Business Suite to the Oracle Cloud. This material was presented at Oracle OpenWorld 2016.

 

Categories: APPS Blogs

Using DataPump on Oracle/Docker environment

Marcelo Ochoa - Wed, 2017-06-07 14:51
Image: oracletechnocampus blogContinuing with my previous post about Doing full hot backups with RMan now is time for Data Pump.
The example is how to backup a full schema from a RDBMS running as Docker container, let see, a DB started using:
$ docker run --name test \
-p 1521:1521 -p 5500:5500 \
-e ORACLE_SID=TEST \
-e ORACLE_PDB=PDB1 \
-e ORACLE_PWD=Oracle2017\! \
-v /home/data/db/test:/opt/oracle/oradata \
oracle/database:12.2.0.1-ee
Once the DB is ready to use and assuming that there is an SCOTT schema with one table for testing the steps for doing a full schema backup using DataPump are:

  • Create a RDBMS directory object to allows backups on the container external directory
$ docker exec -ti test sqlplus system/Oracle2017\!@pdb1
SQL> !mkdir -p /opt/oracle/oradata/backup
SQL> create directory bdir as '/opt/oracle/oradata/backup';
SQL> exit


  • Start DataPump doing a full schema backup:
$ docker exec test expdp system/Oracle2017\!@pdb1 dumpfile=scott.dmp directory=bdir logfile=scott.log schemas=SCOTT
....
Total estimation using BLOCKS method: 13 MB
....
. . exported "SCOTT"."TEST_SOURCE_BIG"                   10.92 MB  135795 rows
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /opt/oracle/oradata/backup/prod/scott.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jun 7 19:13:18 2017 elapsed 0 00:01:03
ready, a full schema backup will be outside your container at /home/data/db/test/backup.

-rw-r----- 1 oracle oinstall  11743232 jun  7 16:13 scott.dmp
-rw-r--r-- 1 oracle oinstall      1983 jun  7 16:13 scott.log

Announcing My Employer-Related Twitter Account

Kevin Closson - Wed, 2017-06-07 14:47

When I tweet anything about Amazon Web Services it will be on the following twitter handle:  https://twitter.com/ClossonAtWork (@ClossonAtWork).

If you’re interested in following my opinions on that twitter feed, please click and follow. Thanks.


Filed under: oracle

Version Control for PL/SQL

Gerger Consulting - Wed, 2017-06-07 14:23

We are hosting a live webinar for IOUG on June 8th at 10:00am PST. Attend and learn how you can manage your PL/SQL source code with Git. We have a new cool demo to show off. :-)

Sign up at this link.

The sign up page as 12 mandatory questions. :-) We are sorry for the inconvenience. We had no control over it. But please bear with us. Bite the bullet. Take a leap of faith. It'll be worth your time. We promise. :-)


Categories: Development

Unable to drop tables from a database

Tom Kyte - Wed, 2017-06-07 10:06
Hi We have recently created a new database from an existing database via RMAN incremental backup level 1. We have created a user and provided all the privileges to it. But, we are currently facing an issue, we are unable to drop any tables...
Categories: DBA Blogs

about directory location of oracle database files,has it haven one parameter?

Tom Kyte - Wed, 2017-06-07 10:06
SYS@orcl28> col banner for a90 SYS@orcl28> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Rel...
Categories: DBA Blogs

Regarding trigger

Tom Kyte - Wed, 2017-06-07 10:06
Hi, my Question is 1) i have a statement level trigger and row level trigger on the same table but i want to update some date on the same table i finished my update then i select to see the data which has updated but output is no rows select....
Categories: DBA Blogs

direct connect Database links in the exadata environment

Tom Kyte - Wed, 2017-06-07 10:06
Can Dblinks be utilized in the Exadata environment. If not, how are direct connects to external databases done?
Categories: DBA Blogs

Convert loop to bulk collect forall

Tom Kyte - Wed, 2017-06-07 10:06
Here is my code: <code>procedure load_cmpnt_history is cursor c1 is select --+rule h.ndc_emp_id , nvl(upper(h.cmpnt_id),'FAA00000') cmpnt_id , h.compl_dt , nvl(nvl(h.crs_grade,h.cmpl_stat),'OTHER...
Categories: DBA Blogs

Partition Maintenance Induced Concurrency

Tom Kyte - Wed, 2017-06-07 10:06
Hi TOMs, A little back story: Our application incurs many "cursor: pin s wait on x" and "library cache lock" waits during our nightly partition maintenance job. It has been expected behavior and one that we've been living happily with for quit...
Categories: DBA Blogs

Abnormally RAM consuption by sqlldr

Tom Kyte - Wed, 2017-06-07 10:06
Hello, We have problem with sqlldr, we have server with 32 GB RAM and 24 GB swap. While loading big table ( 40 mil records with blobs ), free RAM decreases continuostly, and next swap decreases continuostly too. After some while loading finsih...
Categories: DBA Blogs

Is there a mailing list for APEX release announcements?

Joel Kallman - Wed, 2017-06-07 09:49
There was an interesting question today from Oracle Support, on behalf of a customer.  They asked:
"The customer would like to know if there is any mailing list or other type of subscription to be notified whenever there is a new or upgrade release of APEX."
And I answered with:

As far as mailing lists, they could always sign up for the Oracle Database Insider Newsletter.  Announcements about Oracle Application Express, SQL Developer, Oracle REST Data Services and many other database-related technologies are typically included in this newsletter.

I can also suggest that your customer:
  1. Follow Oracle Application Express on Facebook:  https://www.facebook.com/orclapex/
  2. Join the LinkedIn group:  https://www.linkedin.com/groups/8263065
  3. Follow @oracleapexnews on Twitter:  https://twitter.com/oracleapexnews
  4. Visit the Web site:  https://otn.oracle.com/apex
The APEX releases and patch sets are always announced through these social media channels.

And let's not forget https://apex.world, which is the clearinghouse of information for everything APEX.

OAC: Essbase and DVCS

Rittman Mead Consulting - Wed, 2017-06-07 09:00

Finally managed to get around to having a proper look at Essbase within Oracle Analytics Cloud Service (OAC) after a busy couple of months. This post focusses mainly on initial impressions on the ‘out of the box’ the Essbase side of this - which we will explore in more detail in future posts, as well as more detail on the use of Essbase with DVCS.

Using Essbase with DVCS

One of the features we are keen to explore more in this context is the integration of Essbase and the Data Visualisation Cloud Service (DVCS). One point that we found that we do not think is being expressed clearly anywhere else we have seen is how to configure this: In setting up our OAC instance, we were having difficulty coming up with a combination of configuration selections that enables Essbase and DV to work at the same time.

Oracle documentation (such as the price list) suggest that both should be available within Standard Edition OAC:

But Doc ID 2265410.1 on MoS suggests, by needing to add a security rule to the Essbase OAC, that two OAC instances are required. We could not find any reference to this requirement in Oracle documentation or blogs on the subject, but it transpires after checking with Oracle that this is indeed the case – Essbase and DV need to be on separate OAC instances.

Essbase

Looking purely at Essbase, my initial reaction is very positive…whilst the interface is different (I am sure tears will be shed for EAS & Studio in the foreseeable future…although given the way some stalwarts are still clinging on the last surviving copies of the Excel Add In, maybe not too imminently), once the surface of the new interface is scratched more...ahem…’seasoned’ developers will take comfort from being able to do a lot of the same things as they currently can. I am also confident it will fulfil one of the stated objectives in making it easier for non-experts to be able quickly and easily deploy cubes for analysis purposes.

Whilst the manual application and cube maintenance tools through the OAC front-end seem resilient and work effectively, I think some aspects will be difficult to use as the primary maintenance method in a production system - the ‘breadcrumb’ method afforded to dimension maintenance in particular will start to get fiddly to use with a dimension of any sort of volume. The application and cube Import (from a formatted Excel spreadsheet) facility is great - to my mind, a bit like a supercharged and easier-to-use Outline Load Utility in Hyperion Planning - and the ability to refresh the spreadsheet from a deployed cube is a good feature that shouldn’t have been taken for granted. I know Excel is regarded as the Devil’s work in some BI quarters…I personally don’t feel that way until it is being used as a database (or as some form of primary data storage)…but in this context, it is quick & easy to use, on most people’s desktops straightway, and is intuitive.

Still in the Excel corner, on the Smartview side, the addition of the Cube Designer extension (requiring Smartview 11.1.2.5.700) to be able to consider & change the more generic aspects (not members) of the ‘cube maintenance’ spreadsheets is a nice touch that makes this more straightforward and removes the need to pay strict attention to the spreadsheet layout. The ‘treeview’ style hierarchy viewer also helps make sense of the parent-child members that need to be detailed on the individual dimension tabs.

One issue that has flitted across my mind at this early stage is that of rules files. Whilst the Import facility creates these for you (as with creating a cube from Essbase Studio) which is welcome, and rules files created in an on-prem system can be uploaded (again, welcome), the on-board rules file editor is text based:

I’m not too sure how many people have created or edited rules files like this before (although I’d hazard a guess), but whilst the presence of any means to create, amend, or even tweak a file is good, it remains to be seen how usable this approach is. The alternative is to resubmit from the maintenance spreadsheet thus getting it created / amended for you or to maintain in on-prem system…but seeing as this platform is an alternative to (rather than an augmentation of) on prem for a lot of people, I’m not sure how practical this is.

Whilst the existing tools look really promising, I can’t help but think there will be occasions going forwards where it might be advantageous to be able to create a rules file to run an uploaded file outside of them: time will tell.

The Command Line Tool (downloadable from OAC-Essbase / Utilities) is a little limited at the moment, but goes some way towards filling the potential gap left by the absence of client-side EssMsh and can only grow with further releases: from the Oracle OAC documentation...

In conclusion, first impressions are very favourable. There are changes (eg Security), new features (eg Sandboxing), and I am sure there will be gaps for those considering moving from existing on-prem applications - for example, as I have seen someone else reference, there does not seem to be any reference to partitions in the front end or the import spreadsheet layout - so whilst there is a lot with which we will quite quickly feel familiar, there are also going to be new areas and new practices for us to get into step with: as above, we will look to explore some of these in future posts.

Categories: BI & Warehousing

12c MultiTenant Posts -- 3 : Restore Individual PB

Hemant K Chitale - Wed, 2017-06-07 05:36
Restoring the single PDB in a Container Database.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 7 06:34:47 2017

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> restore pluggable database newpdb;

Starting restore at 07-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
channel ORA_DISK_1: restoring datafile 00018 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-JUN-17

RMAN> recover pluggable database newpdb;

Starting recover at 07-JUN-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_06/o1_mf_1_16_dmgyksjy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_17_dmhj6nbk_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc RECID=4 STAMP=946017330
media recovery complete, elapsed time: 00:00:11
Finished recover at 07-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I had connected to the root to restore the PDB.
.
.

.
Categories: DBA Blogs

Where Can I Find the Latest Patch Wizard Updates for EBS 12.2?

Steven Chan - Wed, 2017-06-07 02:00

Patch Wizard helps you identify useful or critical missing patches for your EBS environment.

This tool is updated as needed, with patches that are cumulative: they include new updates as well as all fixes released previously.  The latest updates are always documented here:

The last updates to the EBS 12.2 Patch Wizard were released in September 2016 via this patch:

This patch fixed the following issues:

  • 14504286 - PATCH WIZARD - 12.1.3 - MANY PATCHES ARE INDIRECTLY APPLIED BUT MARKED UNAPPLIED
  • 16992893 - PATCH WIZARD: PATCH MERGE FAILING - UNABLE TO FIND PATCH FILES
  • 17081891 - 16992893 PCC - PATCH WIZARD: PATCH MERGE FAILING - UNABLE TO FIND PATCH FILES
  • 17244500 - PATCH WIZARD IS RECOMMENDING PATCHES FOR IN USE PRODUCTS = NO
  • 17335985 - 17244500 PCC - PATCH WIZARD IS RECOMMENDING PATCHES FOR IN USE PRODUCTS = NO
  • 17355203 - PCC 14504286 - MANY PATCHES ARE INDIRECTLY APPLIED BUT MARKED UNAPPLIED
  • 18617040 - PATCH WIZARD: ORA-06512: AT "APPS.AD_PATCH_ANALYSIS_ENGINE", LINE 519 
  • 18978837 - R12.2 OAM APPLIED PATCHES PROVIDES WRONG DATES, DOES NOT SHOW ALL PATCHES
  • 19412224 - FLAGGED FILE IMPORTS HANG AFTER 18598941
  • 20318695 - ADPAENGB.PLS 120.13.12020000.8 CAUSES ORA-06512 IN EBS R12.2.3
  • 21184697 - CAN'T FIND RESOURCE FOR BUNDLE ORACLE.APPS.AD.OAM.RESOURCES.PATCHRESOURCEBUNDLE
  • 21806033 - PATCH WIZARD APPLIED PATCHES ADVANCED SEARCH BY PRODUCT IS NOT WORKING
  • 22490144 - FILE HISTORY NOT SHOWING CORRECT VERSIONS OF THE ACTUAL APPLIED/UPLOADED FILES.
  • 9703082 - PROVIDE REPORT OR EXPORT BUTTON FOR PATCH WIZARD OUTPUT SUMMARY+IMPACT ANALYSIS

Related Articles

Categories: APPS Blogs

Schedule a job to migrate the data from SQL Server to Oracle

Tom Kyte - Tue, 2017-06-06 15:46
Hello Experts, Is there any way with which I can schedule a job to migrate a data from MS SQL server to Oracle database on daily basis? Please suggest. Thanks, Ankit
Categories: DBA Blogs

Asynchronous Global Indexing does not work as expected in 12c in a Database upgraded from 11g to 12c

Tom Kyte - Tue, 2017-06-06 15:46
We observed following. 1) 11g database is upgraded to 12c. Set the COMPATIBLE parameter to 12c version. Restart the DB. Created a table, partitioned the table and insert data into that table. Then dropped the partition. This works as expected in ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator