Feed aggregator

Partition an Exisiting Table

Tom Kyte - Thu, 2016-11-10 06:06
I am trying to partition an existing table (Range partition) by a date column (BEGTIME). Currently the table is 7.5 GB in size. Our business moves monthly and wanted to partition into months. Also table holds data pertaining to forecasts up to Dec 21...
Categories: DBA Blogs

Fast Generation of CSV and JSON from Oracle Database

Christopher Jones - Thu, 2016-11-10 01:38

Want a fast way to spool CSV or JSON from Oracle Database? Check out the new SQL*Plus 12.2 SET MARKUP CSV option and the new Oracle Database 12.2 JSON_OBJECT operator.

You can try Oracle Database 12.2 now using Oracle Cloud Database Service.

Fast, Easy CSV with SQL*Plus and Oracle Database

First, let's see CSV ("comma separated values") output in SQL*Plus 12.2. Start with this script, t.sql:

  set feedback off

  select department_id, department_name from departments where department_id < 110;

Executed traditionally you get formatted output:

  SQL> @t.sql

  ------------- ------------------------------
	     10 Administration
	     20 Marketing
	     30 Purchasing
	     40 Human Resources
	     50 Shipping
	     60 IT
	     70 Public Relations
	     80 Sales
	     90 Executive
	    100 Finance

Running it with the new CSV mode:

  SQL> set markup csv on
  SQL> @t.sql

  40,"Human Resources"
  70,"Public Relations"


The full CSV syntax is:


You can see the delimiter can be changed from a comma, and quoting of fields can be disabled.

The SET MARKUP option can also be enabled from the command line with the -m option:

  $ sqlplus -s -m 'csv on' cj@pdb1 @t.sql

  40,"Human Resources"
  70,"Public Relations"
(Pro tip: the -s silent option may hide the password prompt on some platforms making SQL*Plus appear to hang! Enter the password and all will be well.)

CSV mode bypasses the traditional SQL*Plus pagination routines, making output faster to generate. And using the -m 'csv on' option additionally invokes a faster I/O subsystem, and also automatically sets some SET options to the values of another new option sqlplus -f. Two of the changes are increasing SET ARRAYSIZE to 100 and SET ROWPRETCH to 2.

Increasing SET ARRAYSIZE allows larger amounts of data to be returned in each low level request to the database, thus improving overall efficiency. Having SET ROWPRETCH to 2 reduces the database 'round trips' required between SQL*Plus and the database for queries that return only single rows. You should adjust the values of these, and other, settings to suit your data size and performance goals.

CSV mode can be used when connecting SQL*Plus 12.2 to Oracle Database 12.2 or earlier.

Fast, Easy JSON with SQL*Plus and Oracle Database

The Oracle Database 12.2 JSON_OBJECT function is a great way to convert relational table output into JSON.

Combined with SQL*Plus 12.2's efficient CSV output - and with quoting of columns disabled - you can spool JSON very easily.

Here's a SQL*Plus script t2.sql to return JSON output:

  set heading off
  set feedback off

  select json_object ('deptId' is d.department_id, 'name' is d.department_name) department 
  from departments d 
  where department_id < 110;


Running it generates the desired JSON output:

  $ sqlplus -s -m 'csv on quote off' cj@pdb1 @t2.sql
  {"deptId":40,"name":"Human Resources"}
  {"deptId":70,"name":"Public Relations"}

SQL*Plus 12.2 and Oracle Database 12.2 allow efficient and easy access to CSV and JSON data. If you don't yet have 12.2, or you want to create web services and do more with the data, check out Oracle ORDS or roll your own solution using the node-oracledb driver for Node.js.

Documentum story – Disable PDF automatic generation but allow jpeg

Yann Neuhaus - Thu, 2016-11-10 00:00

We had a request by a customer where he wanted the pdf automatic rendition to be disabled but keep allowing the jpeg renditions to be used as thumbnails for D2. The pdf is generated by an eternal tool so it wasn’t needed here. Here is what we did:

Login to the ADTS server and edit the following file:

For the repository named “DOCBASE1″, comment the QueueProcessorContext tag as follow and only if it include the dm_autorender_win31 value:

<!-- Start of comment to be added
<QueueProcessorContext DocbaseName="DOCBASE1">
    <CTSServer AttributeName="queueItemName" AttributeValue="dm_autorender_win31"/>
       End of comment to be added -->

Then restart the services

After the restart of the Content Transformation Services, wait for a few minutes and open the CTS log file <CTS_HOME>\logs\CTS_log.txt.

On the CTS log file:
– Ensure that there are no important errors and/or exceptions
– Search for the message “Product ADTS version <CTS_VERSION> is installed on repository: DOCBASE1″

Open the file <CTS_HOME>\logs\Polling_log.txt in order to verify that the changes have been applied successfully.

In this file, find all “DOCBASE1″ related CTSLegacyQueueProcessor log entries by searching for the following text:

DOCBASE1 CTSLegacyQueueProcessor

There should not be any matching text in the log file.


Note that you can use the jpeg renditions in the D2 4.5 Preview widget in order to display a kind of preview directly in the D2 interface.


Cet article Documentum story – Disable PDF automatic generation but allow jpeg est apparu en premier sur Blog dbi services.

Yes, Storage Arrays Can Deduplicate Oracle Database. Here Is Exactly Why It Doesn’t Matter!

Kevin Closson - Wed, 2016-11-09 23:14

I recently had some cycles on a freshly installed Dell EMC XtremIO Storage Array. I took this opportunity to prepare a blog entry about the never-ending topic of whether or not storage arrays are able to reduce physical data capacity through deduplication of blocks in Oracle Database.

Of Course There Is Duplicate Data In Oracle Datafiles

Before I continue, let me say something that may come as a surprise to you. Yes, Oracle Database has duplicate blocks in tablespaces! Yes, modern storage arrays can achieve astonishing data reduction rates through deduplication–even when the only data in the array is Oracle Database (whether ASM or file systems)!

XtremIO computes and displays global data reduction rate. This makes it a bit more difficult to show the effect of deduplication on Oracle Database because averages across diverse data makes pin-point focus impossible. However, as I was saying, I took some time on a freshly-installed XtremIO array and collected what I hope will be interesting information on the topic of deduplication.

Please take a look at Figure 1. To start the testing I created a 4TB XtremIO volume, attached it as a LUN to a test host and then created an XFS file system on it. Please be aware that the contents of an Oracle datafile is precisely the same whether stored in ASM or in a file system file. After the file system was created I used the SLOB database creation kit (SLOB/misc/create_database_kit) to create a small database with Oracle Database 12c. As Figure 1 shows, the small database consumed 11.83GB of logical space in the 4TB volume. However, since the data enjoyed a slight deduplication ratio of 1.1:1 and a healthy compression ratio of 3.3:1 for a 3.6:1 data reduction ratio, only 3.27GB physical space was consumed in the array.


Figure 1

The next step in the testing was to consume the majority of the 4TB file system with a BIGFILE tablespace. Figure 2 shows the DDL I used to create the tablespace.


Figure 2

Figure 3 shows the file system file that corresponds to the tablespace created with DDL in Figure 2.


Figure 3

After creating the 3.9TB BIGFILE tablespace I took a screenshot of the XtremIO GUI Dashboard. As Figure 4 shows, there was no deduplication! Instead, the data was compressed 4.0:1 resulting in only 977.66GB physical space being consumed in the array. So why in the world would I blog the opposite of what I said above? Why show the array did not, in fact, deduplicate the 3.9TB datafile? The answer is in the fact that I said there are duplicate data block in tablespaces. I didn’t say there are duplicate blocks in the same datafile!


Figure 4

To return the array to the state prior to the BIGFILE tablespace creation, I dropped the tablespace (including contents and datafiles thus unlinking the file) and then used the Linux fstrim(8) command to return the space to the array as shown in Figure 5.


Figure 5

Once the fstrim command completed I took another screenshot of the XtremIO GUI Dashboard as shown in Figure 6. Figure 6 shows that the array space utilization and data reduction had returned to that of what was seen before the BIGFILE tablespace creation.


Figure 6

OK, Now For The Duplicate Data

The next step in the testing was to fill up the majority of the 4TB file system with SMALLFILE tablespaces. To do so I created 121 tablespaces each consisting of a single SMALLFILE datafile of 32GB. The output shown in Figure 7 is from a data dictionary query to display the size of each of the 121 datafiles and how the sum of these datafiles consumed 3.87TB of the 4TB file system.


Figure 7

That’s Duplicate Data

Once the file system was filled with SMALLFILE datafiles I took another screenshot of the XtremIO GUI Dashboard. Figure 8 shows that the SMALLFILE datafiles enjoyed a deduplication ratio 81.8:1 combined with a compression ratio of 3.8:1 resulting in a global data reduction rate of 306.9:1. Because of the significant data reduction rate only 12.68GB of physical space was consumed in the array in spite of the 3.79TB logical space (the sum of the SMALLFILE datafiles) being allocated.


Figure 8

So here we have it! I had a database created with Oracle Database 12c that consisted of 121 32GB files for roughly 3.8TB database size yet XtremIO deduplicated the data down by a factor of 82:1!

So arrays can deduplicate Oracle Database contents! Right? Well, yes, but it matters none whatsoever. Allow me to explain.

Oracle datafiles consist of initialized blocks but vast portions of that initialized content is the same from file to file. This fact can be seen with simple md5sum(1) output. Consider Figure 9 where you can see the output of the md5sum command used to compute Oracle datafile checksums but only after skipping the first 8,692 blocks (8KB blocks). It’s the first approximate 68MB of each datafile that is unique when a datafile is freshly initialized. Beyond that threshold we can see (Figure 9) that the rest of the file content is identical.


Figure 9

Thus far this blog post has proven that initialized, but empty, Oracle Database datafiles have duplicate data. As the title of this post says, however, it does not matter.

Introduce Application Data To The Mix

Figure 10 shows the commands I used to populate each of the 121 tablespaces with a single table. The table has the sparse characteristic we are all accustomed to with SLOB. That is, I am only creating a single row in each block. Moreover, I’m populating each of these 121 tables with the same application data! This is precisely why I say deduplication of Oracle Database doesn’t matter because it only holds true until any application data is loaded into the data blocks. Figure 10 shows this set of DDL commands.


Figure 10

After populating the blocks in each of the 121 tables (each residing in a dedicated SMALLFILE tablespace) with blocks containing just a single row of application data I took another screenshot of the XtremIO GUI Dashboard. Figure 11 shows how putting any data into the data blocks reverts the deduplication. Why? Well, remember that the block header of every block has the SCN of the last change made to the block. For this reason I can put the same application data in blocks and still have 100% unique blocks–at least at the 8KB level.

Please note that the application table I used to populate the 121 tables does not consume 100% of the data blocks in each of the SMALLFILE tablespaces. There were a few blocks remaining in each tablespace and thus there remained a scant amount of deduplication as seen in Figure 11. Most XtremIO customers see some insignificant deduplication in their Oracle Database environments. Some even see significant deduplication–at least until they insert data into the database.


Figure 11

In a follow-up post I’ll say a few words about the deduplication granularity and how it affects the ability to achieve small amounts of deduplication of unused space in initialized data blocks. However, bear in mind that the net result of any deduplication of Oracle Database data files is that the only space that can be deduplicated is space that has never had application data in it. After all, a SQL DELETE command doesn’t remove data–it only marks it as free in the block.


I don’t think there are that many Oracle shops that have an urgent need for data reduction of space that’s never been used to store application data. I could be wrong. Until I find out either way, I say that yes you can see deduplication of Oracle Database datafiles but it doesn’t matter one bit.








Filed under: All Flash Array, oracle, XtremIO

Query regarding primary key , foreign key relations and inserts

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, I have a two tables Table1 (id_pk number, name_fk varchar(10)) Table2 (name_pk varchar(10), id_fk number) id_pk : Primary key of table1 name_fk : foreign key referencing name_pk of Table2 name_pk : Primary key of table2 id_fk : Fo...
Categories: DBA Blogs

Complicated query to analyse utilization period

Tom Kyte - Wed, 2016-11-09 11:46
Dear Gents I have a query which is giving the available quantity in each location on specific dates: Location Tdate RunningQty CA02D003A 31-Jul-16 1152 CA02D003A 30-Sep-16 1092 CA02D003A 11-Oct-16 500 CA02D003A 13-Oct-16 0 CA02D003A 20-Oct...
Categories: DBA Blogs

Update one column in 58 millions records table

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, Need suggestions to improve performance. Following are the methods we tried 1. Using <b>merge</b> <code>MERGE INTO /*+ PARALLEL(tbl_temp,8) */ tbl_temp tcm USING (SELECT frn.customer_id, frn.risk FROM temp_new frn ) a ON (a.id = tc...
Categories: DBA Blogs

Unable to use the out type parameter in procedure through DB Link

Tom Kyte - Wed, 2016-11-09 11:46
I have created procedure with the our parameter as type CREATE OR REPLACE TYPE char_array AS TABLE OF varchar2(32000); create or replace procedure GET_INSERTS_PRC(V_TABLE_NAME IN VARCHAR2,OUT_inserts_TYP out char_array ); I can able to run...
Categories: DBA Blogs

Stale statistics

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, I've been asked to review performance on one of our databases, and one of the issues I found was the statistics have been locked since 2011 and are considered stale. My recommendation was to back them up (as a rollback point) and upda...
Categories: DBA Blogs

Sql Loader (NULLIF and NVL(TO_NUMBER) - NULLIF not yielding the correct result. The columns is not being set as NULL but loaded with 0.0.

Tom Kyte - Wed, 2016-11-09 11:46
I have the following Sql Loader Control file : POS_COST_PRC_FUND POSITION(173:202) DECIMAL EXTERNAL NULLIF V4_NULL='?' "NVL(TO_NUMBER(LTRIM(:POS_COST_PRC_FUND,'0')),0)", V4_NULL FILLER POSITION(203:203) CHAR, The column in Oracle is defined a...
Categories: DBA Blogs

APEX 5.0 ORDS 2- Tomcat 7 - Errors on tomcat log and performance issues

Tom Kyte - Wed, 2016-11-09 11:46
Hi Tom, We have implemented Apex 5.0 as our new Developing tool which is a real great tool. The configuration is on HP/UX, tomcat, ORDS and APEX on Oracle 11g r2. We have 4 environnements (dev, qualification, preprod, prod). On the 3 first ...
Categories: DBA Blogs

REST Enable Java or JavaScript in the Database

Kuassi Mensah - Wed, 2016-11-09 10:41
REST Enable Java or JavaScript in the Database
The Oracle REST Data Service (ORDS) allows you to turn Java stored procedures or JavaScript stored procedures into REST Web Services that you may publish these in the Oracle REST Data Service.
See more details @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/overview/index.html
Download and Configure ORDS
1) First step: download the latest ORDS  @ http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/index.html

2) Extract the zip file in a directory say ../ords.3.0.8

3) Configure and Install

$ ls 
docs logs params examples ords.war readme.html

Navigate into params directory and edit the ords_params.properties file.
Modify the standalone.http.port value to a desired port # (e.g., 8090) and save the file.

$vi params/ords_params.properties
#Tue Jul 26 05:23:16 UTC 2016
"params/ords_params.properties" 17L, 467C                     1,1           All

Note: Do not modify any other property

$ java -jar ords.war
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/u01/oracle/ords.3.0.6/ordsi
Enter the name of the database server [localhost]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:
Enter the database service name:pdb1
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.

Enter the username with SYSDBA privileges to verify the installation [SYS]:
Enter the database password for SYS:Welcome1
Confirm password:Welcome1
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Sep 07, 2016 3:53:19 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_core_2016-09-07_035319_00534.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /u01/oracle/ords.3.0.6/logs/ords_install_datamodel_2016-09-07_035342_00050.log
Completed installation for Oracle REST Data Services version Elapsed time: 00:00:23.840

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter 1 if using HTTP or 2 if using HTTPS [1]:1
2016-09-07 03:54:28.867:INFO::main: Logging initialized @158428ms
Sep 07, 2016 3:54:29 AM oracle.dbtools.standalone.StandaloneJetty setupDocRoot
INFO: Disabling document root because the specified folder does not exist: /u01/oracle/ords.3.0.6/ordsi/ords/standalone/doc_root
2016-09-07 03:54:29.525:INFO:oejs.Server:main: jetty-9.2.z-SNAPSHOT
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No encryption key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: No mac key found in configuration, generating key
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.file.ConfigurationFilesBase update
INFO: Updated configurations: defaults
Sep 07, 2016 3:54:29 AM oracle.dbtools.auth.crypto.CryptoKeysGenerator startup
INFO: Updated configuration with generated keys
2016-09-07 03:54:29.793:INFO:/ords:main: INFO: Using configuration folder: /u01/oracle/ords.3.0.6/ordsi/ords
2016-09-07 03:54:29.793:INFO:/ords:main: FINEST: |ApplicationContext [configurationFolder=/u01/oracle/ords.3.0.6/ordsi/ords, services=Application Scope]|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Validating pool: |apex|pu|
Sep 07, 2016 3:54:29 AM oracle.dbtools.common.config.db.DatabasePools validatePool
INFO: Pool: |apex|pu| is correctly configured
2016-09-07 03:54:30.298:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version :|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|
2016-09-07 03:54:30.305:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@429bd883{/ords,null,AVAILABLE}
2016-09-07 03:54:30.346:INFO:oejs.ServerConnector:main: Started ServerConnector@b7f23d9{HTTP/1.1}{}
2016-09-07 03:54:30.348:INFO:oejs.Server:main:
Started @159913ms   
4) Allow your schema to use ORDS 

SQL> exec ords.enable_schema;

Define and Configure Your ORDS Service
Let's use the JavaScript procedure defined in an earlier blog post @

Rem Create a procedure based on the select.js and it's javax.script wrapper (see the previous blog post)
CREATE OR REPLACE PROCEDURE selectproc(id IN varchar2)
output varchar2(10000);
SELECT invokeScriptEval(id) INTO output from dual;

-- delete load.routes module
p_name => 'load.routes');

-- External JS select query
-- URL: load/routes/nashorn/select
-- procedure: selectproc
p_module_name => 'load.routes' ,
p_base_path => '/load/routes/',
p_pattern => 'nashorn/selectbyid/:id',
p_source_type => 'plsql/block',
p_source => 'begin selectproc(:id); end;'

Let's use the JavaScript procedure defined in an earlier blog post 

Open your  web browser and navigate to  http://localhost:8090/ords/ordstest/load/routes/nashorn/selectbyid/100.

 You must see the JSON document of the employee with empid as 100 displayed.

That's it! You have just created your first ORDS service

The same process can be used tor Java stored procedures or other JavaScript procedures in the database.

Editing PL/SQL with Sublime Text 3 and Multiple Cursors

Tony Andrews - Wed, 2016-11-09 09:16
I'm a recent convert to the Sublime Text 3 editor, having managed for many years with TextPad.  One of the most useful features for me is multiple cursors, which allow you to make the same change to multiple lines at once. For example, it comes in very handy for quickly adding an API over a table (assuming you don't have a nice utility to generate one for you already): 1. Describe the table andTony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2016/11/editing-plsql-with-sublime-text-3-and.html

Oracle Cloud Helps Organizations Discover Profit Winners

Oracle Press Releases - Wed, 2016-11-09 07:00
Press Release
Oracle Cloud Helps Organizations Discover Profit Winners Organizational managers and finance executives can easily identify hidden costs and profits with new profitability and cost management solution delivered in the cloud

Redwood Shores, Calif.—Nov 9, 2016

Oracle today announced a purpose-built solution for finance executives and organizational managers to understand specifically what drives profitability, continually assess how resources are being consumed, and take action. Although under constant pressure to improve performance, many organizations lack visibility into what drives cost and profit. Cost, revenue, and operational systems are often fragmented, unconnected, and dispersed, making it difficult to understand the true profitability and costs of the business.

Oracle Profitability and Cost Management Cloud (PCMCS) enables business users to understand the root causes behind costs and revenues. Users can quickly and easily determine the profitability of customers, products, geographies, and sales channels, and take action to improve profitability and lower costs.

“What you don’t know about where your business is making and losing money can really hurt you. In today’s high-paced and increasingly competitive global business climate, it’s essential that your organization focuses resources and cash on the right priorities,” said Hari Sankar, group vice president of enterprise performance management at Oracle. “PCMCS enables organizations to easily identify their profit winners and address inefficiencies, ultimately improving business agility and profitable revenue growth, while minimizing costs.”

Built for the Cloud, Oracle PCMCS delivers new capabilities and innovation in the Cloud, while also leveraging best practices from Oracle’s on-premises offering, Hyperion Profitability and Cost Management.  The solution allows organizations to keep their general ledgers “thin,” fast, and efficient by performing detailed calculations outside the general ledger. Business users can identify profit potential and areas of cost savings with simple one-click reports and dashboards, without reliance on IT. The Cloud solution delivers pre-built reports including profit curves, scatter plots, and traceability, and includes features for detailed financial modeling and ad hoc reporting. Oracle PCMCS handles large data sets, and easily integrates with multiple Enterprise Resource Planning (ERP) systems, as well as with Oracle Enterprise Performance Management (EPM) Cloud and on-premises offerings.

No matter the industry, Oracle PCMCS helps determine where best to spend limited investments and scarce resources to get the biggest impact:

  • Financial services: Banks can gain visibility into complex cost allocations and calculate costs down to product, consumer, and branch.
  • Healthcare: Organizations can provide accurate charge coding in patient profitability.
  • Higher education: Institutions can view the full cost of a course by calculating everything needed to deliver the course, including the cost of building the course.
  • Telecommunications: Companies can apply network costs directly to a product that consumes actions on that network, such as minutes or data.
  • Transportation: Organizations can calculate all costs of moving a plane from point to point, including capitalization on plane, crew, and station costs at each airport, which can be allocated based on factors like number of passengers carried or number of flights taken.
  • Utilities and public services: Users can allocate all shared costs, such as facilities and support staff, into services required while providing transparency for government oversight.

“Businesses today can struggle with knowing exactly where their profit comes from, as traditional financial statements don’t tell the full story, and general ledgers can be inefficient and cluttered,” said Mike Willhelm, managing director at Huron. “Oracle PCMCS enables businesses to easily get insights into profits and costs and act on them quickly. In addition, it integrates well with ERP business processes.”

“The Oracle Profitability and Cost Management Cloud Service strikes the perfect balance between configurability and ease-of-use, and is a welcome addition to Oracle’s market-leading family of cloud-based Enterprise Performance Management applications. It’s the ideal solution for a wide range of allocation based business processes, including management reporting, product and customer profitability, shared service costing and chargebacks, and operational transfer pricing,” said Mike Killeen, senior vice president at Edgewater Ranzal.

Oracle PCMCS is available now for existing and new customers. Businesses can choose flexible deployment options to best fit their needs. For additional information, visit Oracle Cloud and connect with Oracle EPM Cloud on Twitter.

Contact Info
Joann Wardrip
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.


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

Joann Wardrip

  • +1.650.607.1343

Oracle 12cR2: Database parameters

Marco Gralike - Wed, 2016-11-09 05:39
For those in a desperate need to learn all 4841 database parameter variations of the…

Oracle DBaaS database available

Marco Gralike - Wed, 2016-11-09 04:55
Just created by first Extreme Performance database in the cloud.oracle.com. So just after a…

Oracle 12c – DB_UNKNOWN in ASM

Yann Neuhaus - Wed, 2016-11-09 04:06

Have you ever noticed a DB_UNKNOWN directory in your ASM structure? It usually happens in combination with spfile creation in ASM, or with RMAN spfile restores to ASM.

The correct location is +DATA/<SID>/PARAMETERFILE/SPFILE.<#>.<#>, and an ASM alias from +DATA/<SID>/ pointing to it.

But sometimes, the spfile ends up in +DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.<#>.<#>

Technically no issue. The spfile in the DB_UNKNOWN directory is perfectly ok and can be used. However, you might need to adjust your init<SID>.ora in case you have a config like the following

oracle@oel001:/u00/app/oracle/product/ [OCM121] cat initOCM121.ora

Maybe you have a 4 node RAC, then you need to adjust it on every node. Maybe you have a cluster resource with a spfile entry. Then you need to adjust that one as well. And besides that, to what database does the DB_UNKNOWN belong to? Imagine you have 20 DB’s running and you need to find out, which database has something in the DB_UNKNOWN directory, in case there are more entries.

No … it is not a good situation. It has to be corrected. But how?

First of all, let’s create a situation that ends up with a DB_UNKNOWN directory.

It is quite easy to do. Typically, with spfile restores or with a “create spfile from pfile”

  1. Shutdown the DB
  2. Startup RMAN dummy instance
  3. Restore the spfile to pfile
  4. Shutdown the Instance
  5. Adjust the pfile
  6. Create the spfile from pfile while the DB is shutdown

Here is an example with 12cR1 ( I am jumping directly to the RMAN restore, because RMAN dummy instance was already explained in http://blog.dbi-services.com/oracle-12c-when-the-rman-dummy-instance-does-not-start-up/

Ok. Let’s check the current location of the spfile of the cluster resource.

oracle@oel001:/home/oracle/ [OCM121] srvctl config database -d OCM121 | grep -i spfile
Spfile: +DATA/OCM121/spfileOCM121.ora

Now we can run the RMAN restore of the spfile to pfile. Restoring it to a pfile first has the advantage, that we can take a look at all settings and maybe adjust them, before we put it back into production.

run {
restore spfile to pfile '/tmp/initOCM121.ora' for db_unique_name='OCM121' from

Starting restore at 08-NOV-2016 11:01:04
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=364 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=12 device type=DISK
channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_DISK_1: restoring spfile from AUTOBACKUP +fra/OCM121/AUTOBACKUP/2016_10_29/s_926511850.517.926511853
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 08-NOV-2016 11:01:14

The pfile was successfully created. Now we can correct some settings in the pfile if we want and then create a spfile again.

oracle@oel001:/home/oracle/ [OCM121] ls -l /tmp/initOCM121.ora
-rw-r--r-- 1 oracle asmadmin 1777 Nov  8 11:01 /tmp/initOCM121.ora

Ok. Let’s create the new spfile while the DB is shutdown.

oracle@oel001:/home/oracle/ [OCM121] sqh

SQL*Plus: Release Production on Tue Nov 8 11:03:56 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile='+DATA' from pfile='/tmp/initOCM121.ora';

File created.

Oppssss … and now it happened. The directory DB_UNKNOWN is created. While the database is shutdown, Oracle does not know the DB_NAME and so, it has to create a placeholder directory to save the spfile.

ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    CDB121/
                                        Y    DB_UNKNOWN/
                                        Y    OCM121/

ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 08 11:00:00  Y    SPFILE.293.927371209

However, this is not the configuration that we want. To correct it, cleanup the DB_UNKNOWN entries, and start your DB into the nomount state and execute then the spfile from pfile command again.

SQL> startup nomount pfile=/tmp/initOCM121.ora
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2924928 bytes
Variable Size             436211328 bytes
Database Buffers         1157627904 bytes
Redo Buffers               13848576 bytes

SQL> create spfile='+DATA' from pfile='/tmp/initOCM121.ora';

File created.

And here we go. The spfile is the correct location.

ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 08 11:00:00  Y    spfile.291.927372029

The only thing missing is the ASM alias. That one has to be created manually afterwards.

ASMCMD> cd +data/OCM121
ASMCMD> mkalias +data/OCM121/PARAMETERFILE/spfile.291.927372029 spfileOCM121.ora

It makes a big difference if you create your spfile in the nomount state or while the database is shutdown. You might end up with a totally different directory structure in ASM. With and the nomount state is enough to end up in the correct location. In earlier versions you might need to startup mount to have the same effect.





Cet article Oracle 12c – DB_UNKNOWN in ASM est apparu en premier sur Blog dbi services.

Table dependencies

Tom Kyte - Tue, 2016-11-08 17:26
To find databse table dependencies I ran below sql, select rpad( '*', (level-1)*2, '*' ) || table_name table_name from ( select p.table_name , p.constraint_name primary_cons , f.constraint_name foreign_cons , f.r_constr...
Categories: DBA Blogs

finding regular expression

Tom Kyte - Tue, 2016-11-08 17:26
Hi i am trying to match a pattern in a string. It is web log data so basically i need to look as follows find a comma followed by two spaces followed by anynumber of characters other than a quote follwed by = Eg: is string is abc=1, name='1...
Categories: DBA Blogs

Casting complex object IN parameter of a procedure multiple time for inserting into multiple tables VS Looping through the object and inserting into all the tables

Tom Kyte - Tue, 2016-11-08 17:26
Hello Tom First, thanks for all the help and advice you provide to the Oracle world. I have a procedure which takes IN parameter as a complex object (Ex. Array Starts with Product Events which contains Engines(object) which in turn contains par...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator