Feed aggregator

Archival strategy from huge data tables

Tom Kyte - Thu, 2017-03-23 03:06
Hi Tom, We have a requirement to archive data from huge data into some backup tables based upon a date range or number of rows. As per the stats that i extracted these tables contains data around 275 MM records. Can you please let me know the b...
Categories: DBA Blogs

Loading two files with one CTL having different column

Tom Kyte - Thu, 2017-03-23 03:06
Hello Tom, Good Morning. I am in need to load two csv files with same CTL through SQLLoader. File f1 has 3 columns and ctl loads them in corresponding table columns. But file f2 has 2 columns only(last column of f1 is not present). ...
Categories: DBA Blogs

How to reduce the size of a tablespace?

Tom Kyte - Thu, 2017-03-23 03:06
today I could purge a "table space" that apparently occupied 70 gb, but actually used 3 gb, therefore now that "table space" measures 70 gb with only 3 gb occupied, then I want to minimize the "table space" at least 5 gb, but not let me make the oper...
Categories: DBA Blogs

PFCLScan - A Security Scanner For Oracle Databases - New Website

Pete Finnigan - Thu, 2017-03-23 03:06
Our software product PFCLScan can be used to assess your Oracle databases for security issues that could make your data vulnerable to loss or attack. PFCLScan initially had its own website, PFCLScan.com but since the restyle and redesign of our....[Read More]

Posted by Pete On 22/03/17 At 08:24 PM

Categories: Security Blogs

12cR2 tightens up ORA-01841 for zero year ANSI dates, but not for Oracle SQL syntax

Jeff Moss - Thu, 2017-03-23 01:00

In moving some more code from an 11gR2 database to a 12cR2 database, I found another change where a piece of code that works in 11gR2 doesn’t compile in 12cR2.

In this instance a view was being created with a projected date column which used the ANSI DATE syntax. Here is a simplified test script:

CREATE OR REPLACE VIEW test1 AS
SELECT date '0000-01-01' date_col
FROM dual
/
DROP VIEW test
/

CREATE OR REPLACE VIEW test2 AS
SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
FROM dual
/

Running this on 11gR2 gives:

SQL>CREATE OR REPLACE VIEW test1 AS
  2  SELECT date '0000-01-01' date_col
  3  FROM   dual
  4  /

View created.

SQL>CREATE OR REPLACE VIEW test2 AS
  2  SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
  3  FROM   dual
  4  /

View created.

Now running this on 12cR2 gives:

SQL> CREATE OR REPLACE VIEW test1 AS
  2  SELECT date '0000-01-01' date_col
  3  FROM   dual
  4  /
SELECT date '0000-01-01' date_col
            *
ERROR at line 2:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL> CREATE OR REPLACE VIEW test2 AS
  2  SELECT TO_DATE('0000-01-01','YYYY-MM-DD') date_col
  3  FROM   dual
  4  /

View created.

The date is zero and thus the error message is correct in 12cR2 for the ANSI DATE syntax.

PURGEOLDEXTRACTS Not Purging Trail Files

Michael Dinh - Wed, 2017-03-22 20:16

The best part of playing poker is misreading the hand and won.

Well, today this applied to help troubleshoot a Goldengate issue.
Granted it might not be the root cause but it did solve the issue for the time being.

Given:

1. Goldengate version 10.4

2. mgr process started with uid vs username

$ id -a
uid=19208(ggsuser) gid=1601(dba) groups=1601(dba)

$ ps -ef|grep ./mgr
19208    18576     1  0 16:05 ?        00:00:00 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. mgr.prm contains PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2

5. ggserr.log - trail files not being purged
2017-03-22 16:09:35  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 16:09:35  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2 (MINKEEPFILES option not used.).
2017-03-22 16:09:35  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

6. send mgr, getpurgeoldextracts
GGSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...
PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              24        0        0        0   Y
OK	
No extract trails

GGSCI> 

Comparison:

1. Goldengate version 12.2

2. mgr process started with username vs uid
ggsuser  73929     1  0 Mar21 ?        00:00:46 ./mgr 

3. dirdat is symbolic link to /ggs/dirdat

4. send mgr, getpurgeoldextracts
GSCI> send mgr, getpurgeoldextracts

Sending GETPURGEOLDEXTRACTS request to MANAGER ...

PurgeOldExtracts Rules
Fileset                              MinHours MaxHours MinFiles MaxFiles UseCP
/ggs/dirdat/*                              48        0        1        0   Y
OK	
Extract Trails
Filename                        Oldest_Chkpt_Seqno  IsTable  IsVamTwoPhaseCommit
/ggs/dirdat/aa                              45

GGSCI>

Research:

1. Did not apply
Goldengate Manager Not Purging Trail Files (Doc ID 1460097.1)

2. Did not apply
Trail Files Not Automatically Purged by 11.1 When PurgeOldExtracts for MGR Has Been Configured (Doc ID 1943702.1)

3. Helped resolve the problem by misreading.
PURGEOLDEXTRACTS Not Purging Trail Files (Doc ID 967934.1)

The use of a UNIX symbolic link to a process can prevent MANAGER from being able to find the appropriate trail files that need to be purged.

There was not symbolic link to a process, that's just insane and who would do such a thing!

Initially read this incorrectly as symbolic link and check dirdat (symbolic link) which lead to the solution.

Solution:

Replace PURGEOLDEXTRACTS /ggs/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
With    PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1

2017-03-22 19:49:10  GGS INFO        399  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start mgr.
2017-03-22 19:49:10  GGS WARNING     201  Oracle GoldenGate Manager for Oracle, mgr.prm:  PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 1 (MINKEEPFILES option not used.).
2017-03-22 19:49:10  GGS INFO        330  Oracle GoldenGate Manager for Oracle, mgr.prm:  Manager started (port 7949).

2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000150, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 150.
2017-03-22 19:49:10  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000151, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 151.
.........
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000183, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 183.
2017-03-22 19:49:11  GGS INFO        226  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purged old extract file /ggs/dirdat/aa000184, applying UseCheckPoints purge rule: Oldest Chkpt Seqno 185 > 184.

Using Delphix to support Oracle upgrade

Bobby Durrett's DBA Blog - Wed, 2017-03-22 17:47

I’m working on upgrading a very buggy unpatched 11.1.0.7 Oracle database to a fully patched 11.2.0.4 version. I’m using Delphix to support the upgrade and it has been a big help so far. This is on the HP-UX 11.31 Itanium platform.

The great thing about using Delphix to support an upgrade is that my very first pass through the upgrade scripts was with a full-sized clone of production. In the past I probably started with a tiny subset or even an out of the box demo database for my first upgrade pass and even when I got to QA it wasn’t a full test of a production upgrade. This time, my first test was with all the data and that was very cool.

The main example of how this helped is that we had a lot of data in the SYS.WRI$_OPTSTAT_HISTGRM_HISTORY and SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY tables in production and this made the first upgrade of its clone take a long time. After two or three attempts at other ways to speed things up, I ended up applying patch 12683802 on an 11.1.0.7 Oracle home and this allowed me to truncate these two tables.

Delphix helped me here because I had an unused 11.1.0.7 Oracle home on a different host from the one I was doing the upgrade on. I didn’t want to apply a patch on the upgrade host because there were three other databases using the home and I didn’t want to bring them down or patch them. Delphix let me move the VDB that I was upgrading over to the host that had the unused home. Then I applied the patch there and ran the truncate using the dbms_stats.purge_stats(dbms_stats.purge_all) procedure call that the patch enabled.

Then I moved the VDB back to the host where I intended to do the upgrade, which already had the fully patched 11.2.0.4 binaries installed, and ran the upgrade there. Pretty cool. I think I did the most recent upgrade in about 3.5 hours with the empty OPTSTAT tables.

By the way, doing an upgrade within Delphix is easy. You just bring the VDB up on the old oracle home, do the upgrade as you normally would include all the shutdown and startup commands, and then within the Delphix GUI you let Delphix know the VDB is now on a new Oracle home by shutting it down, choosing the new home, and bringing it back up. Piece of cake.

Bobby

Categories: DBA Blogs

PTC Windchill Success Story: The Benefits of Moving from PDM to PLM

PTC Windchill Success Story: The Benefits of Moving from PDM to PLM A Prominent Furniture Manufacturer deploys Fishbowl’s System Generated Drawing Automation to Increase Efficiencies with their Enterprise Part deployment within PTC Windchill

Our client has numerous global manufacturing facilities and is using PTC Windchill to streamline eBOM and mBOM processes. However, not all modifications to parts information propagates automatically/accurately at the drawing level. Updating plant specific drawings with enterprise part information was a time-consuming process that was manual, error prone, full of delays and diverted valuable engineering resources away from their value-added work.

The client desired a go-forward approach with their Windchill PLM implementation that would automatically update this critical enterprise part information. They became aware of our System Generated Drawing solution from a presentation at PTC LiveWorx. From the time of first contact the Fishbowl Solutions team worked to deliver a solution that helped them realize their vision.

BUSINESS PROBLEMS
  • Manufacturing waste due to ordering obsolete or incorrect parts
  • Manufacturing delays due to drawing updates needed for non-geometric changes – title block, lifecycle, BOM, as well as environmental/regulatory compliance markings, variant designs, etc.
  • Manually updating product drawings with plant specific parts information took away valuable engineering time
SOLUTION HIGHLIGHTS
  • Fishbowl’s System Generated Drawing Automation Systematically combines data from BOM, CAD, Drawing/Model, Part Attributes and enterprise resource planning (ERP) systems
  • Creates complete, static views of drawings based on multiple event triggers
  • Creates a template-based PDF that is overlaid along with the CAD geometry to produce a final document that can be dynamically stamped along with applicable lifecycle and approval information
  • Real-time watermarking on published PDFs
RESULTS

Increased accuracy of enterprise parts information included on drawings reduced product manufacturing waste
Allowed design changes to move downstream quickly, allowing a increase in design to manufacturing operational efficiencies

 

“Fishbowl’s System Generated Drawing Automation solution is the linchpin to our enterprise processes. It provides us with an automated method to include, update and proliferate accurate parts information throughout the business. This automation has in turn led to better data integrity, less waste, and more process efficiencies.” -PTC Windchill Admin/Developer

 

For more information about Fishbowl’s solution for System Generated Drawing Automation Click Here

The post PTC Windchill Success Story: The Benefits of Moving from PDM to PLM appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Oracle BPM: Time for Time Out

Jan Kettenis - Wed, 2017-03-22 11:47
In this posting I describe how to time out a specific BPM scope with the option to re-initiate the timer.

In case you need to model a time out for a specific scope within a process where you want to be able to modify the time out run-time, then you can model it similar to this:

A parallel flow is used where the top flow covers the main process, and the bottom flow handles the timeout. To make the timeout configurable, the bottom flow uses an Event Gateway with a Message event to interrupt the timer and re-initiate it again. The first of the two flows that reaches the Complex Merge aborts the other one (first come, first served), as configured in the Complex Merge:

Note: If you want re-initiation to happen based on a Signal, than you cannot use that in an Event Gateway. However, as a work-around you can define a separate component in the composite that is subscribed to the Signal event, and then calls the "Reinitiation Requested" Message Start event.

Time Out FlowThe timer is configured using an expression that results in a duration:

Furthermore you need some variable that is initiated in the Start operation as false, e.g. called a "mainProcessTimesOut":


"mainProcessTimeOut" is set to true in the "Set Timed Out" Script activity, and used in the "timed out?" Exclusive Gateway to go to the "End" or "Timed Out" End event.

Reinitiate FlowThe "Reinitiation Requested" Message Catch event exposes a "reinitiateTimer" operation that takes the new expiry duration as input, plus an id to correlate the instance:


As the "Reinitiation Requested" Message Catch is only activated in case re-initialization of the timer is requested, the condition of the no-flow from "reinitiate?" can simply be set to false, and the yes-flow as the default. 

How to create a Sankey Diagram

Nilesh Jethwa - Wed, 2017-03-22 11:45
What is a sankey diagram?

Sankey Diagram is kind of a flow chart that has multi-purpose use. It can be applied in lot of different domains. Even though they are popular to visualize manufacturing flow or energy flow diagrams, there are other domains like HR, Finance, Web analytics etc where you can find the benefit of using Sankey visualization. As part of data visualization best practices, it is highly recommended to incorporate Sankey Visualizations as your new pie chart replacement.

Build Sankey Diagram using InfoCaptor
In this tutorial, we shall learn about creation of Sankey diagrams using InfoCaptor. Using the tools you can visualize the flow from one node to another. It is a part of the visualization library, hence user does not to have to work on the complicated scripting. [NOTE: InfoCaptor is more than a sankey diagram creator, it contains other visualizations from d3.js charts]

Read more at http://www.infocaptor.com/dashboard/sankey-diagrams

Webcast: "Installation, Cloning and Configuration of EBS 12.2"

Steven Chan - Wed, 2017-03-22 10:51

Install EBS 12.2Oracle University has a wealth of free webcasts for Oracle E-Business Suite.  If you're looking for an overview of how to install, clone, and configure EBS 12.2, see:

Max Arderius, Senior Principal Product Manager covers the technology stack for Oracle E-Business Suite 12.2, including the use of Oracle WebLogic Server (Oracle Fusion Middleware 11g) and Oracle Database functionality. Topics include an architectural overview of the latest updates, installation options, configuration options, and new tools for automated cloning. Also learn how Online Patching (based on the Oracle Database Edition-Based Redefinition feature) will reduce your database patching downtimes. This material was presented at OOW 2015.

Categories: APPS Blogs

Webcast: "Installation, Cloning and Configuration of EBS 12.2"

Steven Chan - Wed, 2017-03-22 10:51

Install EBS 12.2Oracle
University has a wealth of free webcasts for Oracle E-Business Suite. 
If you're looking for an overview of how to install, clone, and configure EBS 12.2, see:

Max Arderius, Senior Principal Product Manager covers the technology stack for Oracle E-Business Suite 12.2, including the use of Oracle WebLogic Server (Oracle Fusion Middleware 11g) and Oracle Database functionality. Topics include an architectural overview of the latest updates, installation options, configuration options, and new tools for automated cloning. Also learn how Online Patching (based on the Oracle Database Edition-Based Redefinition feature) will reduce your database patching downtimes. This material was presented at OOW 2015.

 

Categories: APPS Blogs

Webinar: Improve WebCenter Portal Performance by 30% and get out of Oracle ADF Development Hell

DATE: Thursday, March 30th
TIME: 12:00 PM CST, 1:00 PM EST

Jerry AberJoin Fishbowl’s Enterprise Architect, Jerry Aber, as he shares recommendations on performance improvements for WebCenter-based portals. Jerry has been delivering portal projects for over 15 years, and has been instrumental in developing a technology framework and methodology that provides repeatable and reusable development patterns for portal deployments and their ongoing administration and management. In this webinar, Jerry will share how leveraging modern web development technologies like Oracle JET, instead of ADF taskflows, can dramatically improve the performance of a portal – including the overall time to load the home page, as well as making content or stylistic changes.

Jerry will also share how to architect a portal implementation to include a caching layer that further enhances performance. These topics will all be backed by real world customer metrics Jerry and Fishbowl team have seen through numerous, successful customer deployments.

If you are a WebCenter Portal administrator and are frustrated with challenges of improving your ADF-centric portal, this webinar is for you. Come learn how to overhaul the ADF UI, which will lead to less development complexities and ensure more happy users.

Register today. 

New to Zoom? Go to zoom.us/test to ensure you can access the webinar.

The post Webinar: Improve WebCenter Portal Performance by 30% and get out of Oracle ADF Development Hell appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Partitioning in 11g/12c

Tom Kyte - Wed, 2017-03-22 08:46
Hi Chirs/Connor, We have 10 tables (Size around 510 GigaBytes). We need to convert these non-partitioned table to Partition based on certain columns we identifed. Currently we are running on Oracle 11g and there is no direct option available t...
Categories: DBA Blogs

Using a view with table/cast/multiset within a cte utilizing a union statement returns null

Tom Kyte - Wed, 2017-03-22 08:46
Trying to utilize TABLE/CAST/MULTISET in Ora11 to duplicate some functionality from converted SQL using outer apply. Since Oracle doesn't have that in version 11 I defined an object and table of said object, cross join it and basically get the same t...
Categories: DBA Blogs

Explain plan cardinality and cost

Tom Kyte - Wed, 2017-03-22 08:46
Hi, I checked EXPLAIN PLAN of a Select Query(In SQL Devloper Tool by pressing F10 Short cut Key), there i noticed one column Cardinality with COST. As Cardinality Increases i noticed increase in COST, i read Optimizer chooses less cost plan to ...
Categories: DBA Blogs

Full table scan with sorting.

Tom Kyte - Wed, 2017-03-22 08:46
Dear Team, I have one analytic question, please help me to understand this. Suppose I have SGA_TARGET=5 GB DB_CACHE_SIZE= 2 GB PGA_SIZE= 2 GB TABLE SIZE= 15 GB (e.g. table_name employee) If I fire command like "select * from employee or...
Categories: DBA Blogs

rac undo tablespace

Tom Kyte - Wed, 2017-03-22 08:46
Hi , Why we need separate undo and temp table space in rac and please justify
Categories: DBA Blogs

Merging Partitions Of Two Different Tables

Tom Kyte - Wed, 2017-03-22 08:46
Hi, Suppose i have a table A in which there is a varchar2(200) and a DATE column. I have created a range partition on the date column.I have another table B in same table space and in same database with similar structure. Is there any way by which...
Categories: DBA Blogs

test question ignore

Tom Kyte - Wed, 2017-03-22 08:46
test question ignore
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator