Feed aggregator

SQL Server on Linux in Azure

Yann Neuhaus - Thu, 2016-11-24 14:23

I’m pleased to announce that Microsoft gives now the opportunity to choose Linux as a new operating system for SQL Server.

To try this new version, you can find in the Cloud Azure a new template in the marketplace.

In the search bar, tape “SQL Server vnext” and you’ll find the “SQL Server vNext on Red Hat Enterprise Linux 7.2”.

SQL Server vNext 01 Follow all “classic” steps to create your new VM SQL Server on Linux:

SQL Server vNext 02

First step, choose your “deployment mode”

SQL Server vNext 04

After this first step, choose a name, a disk type, a couple login/password and the location

SQL Server vNext 05

The next step is to choose your storage:

SQL Server vNext 06

At the last step, configure you options:

SQL Server vNext 07

Wait a little bit for the provisioning and at the end, you’ll have your VM SQL Server on Linux ready to use:

SQL Server vNext 11

 

Et voila! My first VM SQL Server on Linux in the Cloud! It’s easy and fast to create…

Next blog is how to use it ;-)

 

Cet article SQL Server on Linux in Azure est apparu en premier sur Blog dbi services.

Rittman Mead at UKOUG Tech 16

Rittman Mead Consulting - Thu, 2016-11-24 11:52
Rittman Mead at UKOUG Tech 16

This year as always Rittman Mead is coming to UKOUG Tech 16 with a strong presence and a great line up of sessions covering OBIEE, ODI, Kafka, advanced visualisation and more. And yes, there will be Cloud!

Rittman Mead at UKOUG Tech 16

Here is the details of the Rittman Mead sessions :

There is no better way to finish a conference than with two success stories from our recent engagements! If the OBIEE 12c upgrade depicted in Francesco's session is something you are also looking to achieve, we would be pleased to tell you more about it and to see how we can help you.

And of course, we are also happy to answer any questions if you see us in sessions or around the conference. You can find some of us during the Oracle Big Data meetup (Monday evening) or the ODTUG Data and Analytics Switzerland meetup (Tuesday evening).

So see you in two weeks in Birmingham !

Categories: BI & Warehousing

Can I do it with PostgreSQL? – 1 – Restore points

Yann Neuhaus - Thu, 2016-11-24 11:03

When discussing with customers about PostgreSQL we often hear that they can do things in one database that they can not do in PostgreSQL. Most of the times this is not true and you actually can do it in PostgreSQL. Maybe not in exactly the same way but this is not surprising as PostgreSQL does implement features not in exactly the same way other vendors do.

To start this series we’ll talk about restore points. Of course you can create restore points in PostgreSQL and then restore up to such a point in case you need to (e.g. after a failed schema or application upgrade or just for testing purposes ). Lets go…

We’ll use the latest version of PostgreSQL which is 9.6.1 currently:

postgres@pgbox:/home/postgres/ [PG961] sqh
psql (9.6.1 dbi services build)
Type "help" for help.

(postgres@[local]:5439) [postgres] > select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

Time: 47.119 ms
(postgres@[local]:5439) [postgres] > 

When we want to do point in time recovery we need to setup archiving. Without going into the details (as this is out of scope here) the parameters which need to be adjusted are these (if not already done):

(postgres@[local]:5439) [postgres] > alter system set wal_level = 'replica';
ALTER SYSTEM
Time: 28.056 ms
(postgres@[local]:5439) [postgres] > alter system set archive_command='test ! -f /u90/pgdata/PG961/%f && cp %p /u90/pgdata/PG961/%f'; 
ALTER SYSTEM
Time: 20.925 ms
(postgres@[local]:5439) [postgres] > alter system set archive_mode ='on';
ALTER SYSTEM
Time: 5.307 ms
(postgres@[local]:5439) [postgres] > select name,context from pg_settings where name in ('archive_mode','archive_command','wal_level');
      name       |  context   
-----------------+------------
 archive_command | sighup
 archive_mode    | postmaster
 wal_level       | postmaster
(3 rows)

Time: 1.460 ms

Be sure to restart your instance before you continue. Changing archive_mode and wal_level can not be done online. Once you restarted make sure that your archive_command really succeeds:

(postgres@[local]:5439) [postgres] > create database test1;
CREATE DATABASE
Time: 1705.539 ms
(postgres@[local]:5439) [postgres] > drop database test1;
DROP DATABASE
Time: 107.283 ms
(postgres@[local]:5439) [restore] > select pg_switch_xlog();
 pg_switch_xlog 
----------------
 0/22001798
(1 row)

Time: 214.216 ms
(postgres@[local]:5439) [postgres] > \! ls -l /u90/pgdata/PG961/
total 16384
-rw-------. 1 postgres postgres 16777216 Nov 24 17:34 000000020000000000000022

When you can not see an archived wal in the last step you did something wrong. The next bit you need when you want to do point in time recovery with PostgreSQL is a base backup:

postgres@pgbox:/u02/pgdata/PG961/ [PG961] mkdir /u90/pgdata/PG961/basebackups
postgres@pgbox:/u02/pgdata/PG961/ [PG961] pg_basebackup -x -D /u90/pgdata/PG961/basebackups/
postgres@pgbox:/u02/pgdata/PG961/ [PG961] ls /u90/pgdata/PG961/basebackups/
backup_label  pg_commit_ts   pg_log        pg_replslot   pg_stat_tmp  PG_VERSION
base          pg_dynshmem    pg_logical    pg_serial     pg_subtrans  pg_xlog
global        pg_hba.conf    pg_multixact  pg_snapshots  pg_tblspc    postgresql.auto.conf
pg_clog       pg_ident.conf  pg_notify     pg_stat       pg_twophase  postgresql.conf

Fine. Lets generate some test data with this simple script:

(postgres@[local]:5439) [postgres] > \! cat a.sql
\c postgres
drop database if exists restore;
create database restore;
\c restore
create table t1 ( a int );
insert into t1 (a)
       values (generate_series(1,1000000));
select count(*) from t1;
\d t1

When you run this you’ll get a table (t1) containing 1 million rows:

(postgres@[local]:5439) [postgres] > \i a.sql
You are now connected to database "postgres" as user "postgres".
DROP DATABASE
Time: 114.000 ms
CREATE DATABASE
Time: 1033.245 ms
You are now connected to database "restore" as user "postgres".
CREATE TABLE
Time: 5.917 ms
INSERT 0 1000000
Time: 2226.599 ms
  count  
---------
 1000000
(1 row)

Time: 65.864 ms
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

Ok, fine. Now we are ready for testing restore points. Lets say you want to do some modifications to your table and to be on the safe side you want to create a restore point before. No problem:

(postgres@[local]:5439) [postgres] > select pg_create_restore_point('RP1');
 pg_create_restore_point 
-------------------------
 0/28D50EF8
(1 row)

Time: 0.825 ms

Quite easy and fast. Now lets play with our table:

(postgres@[local]:5439) [restore] > select count(*) from t1;
  count  
---------
 1000010
(1 row)

Time: 66.214 ms
(postgres@[local]:5439) [restore] > \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

(postgres@[local]:5439) [restore] > alter table t1 add column b varchar(10);
ALTER TABLE
Time: 1.810 ms
(postgres@[local]:5439) [restore] > update t1 set b='b';
UPDATE 1000010
Time: 11004.972 ms
(postgres@[local]:5439) [restore] > drop table t1;
DROP TABLE
Time: 238.329 ms

Ups, table gone. How can we now go back to the restore point created above? Quite easy:

Shutdown your instance and copy back the base backup:

postgres@pgbox:/u02/pgdata/PG961/ [PG961] rm -rf pg_xlog
postgres@pgbox:/u02/pgdata/PG961/ [PG961] cp -pr /u90/pgdata/PG961/basebackups/* $PGDATA
cp: cannot overwrite non-directory ‘/u02/pgdata/PG961/pg_xlog’ with directory ‘/u90/pgdata/PG961/basebackups/pg_xlog’
postgres@pgbox:/u02/pgdata/PG961/ [PG961] ln -s /u03/pgdata/PG961/ pg_xlog

Then create a recovery.conf file (for telling PostgreSQL to go into recovery mode when it comes up) and specify the restore point you created above:

postgres@pgbox:/home/postgres/ [PG961] echo "restore_command = 'cp /u90/pgdata/PG961/%f %p'
> recovery_target_name = 'RP1'" > $PGDATA/recovery.conf
postgres@pgbox:/home/postgres/ [PG961] cat $PGDATA/recovery.conf
restore_command = 'cp /u90/pgdata/PG961/%f %p'
recovery_target_name = 'RP1'

Start the instance and check the log file:

LOG:  database system was interrupted; last known up at 2016-11-24 17:36:28 CET
LOG:  creating missing WAL directory "pg_xlog/archive_status"
LOG:  starting point-in-time recovery to "RP1"

If everything went fine your table should be back without the additional column:

(postgres@[local]:5439) [restore] > \d t1
      Table "public.t1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

(postgres@[local]:5439) [restore] > select count(*) from t1;
  count  
---------
 1000000
(1 row)

Time: 82.797 ms

So, yes, you can definitely use restore points with PostgreSQL :)

If you want me to blog about any feature you are not sure is there in PostgreSQL let me know.

 

Cet article Can I do it with PostgreSQL? – 1 – Restore points est apparu en premier sur Blog dbi services.

Oracle VARIANCE Function with Examples

Complete IT Professional - Thu, 2016-11-24 05:00
In this article, I’ll explain what the Oracle VARIANCE function is, and show you some examples. Purpose of the Oracle VARIANCE Function The purpose of the VARIANCE function is to, well, return the variance of a set of numbers. A variance is a mathematical concept, which represents how different the values are in a set […]
Categories: Development

ORAAH 2.7 released!

The latest release of Oracle R Advanced Analytics for Hadoop (ORAAH), release 2.7.0, is one of the components of the Oracle Big Data Connectors software suite, an option to the Oracle Big Data...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Documentum story – Restrict the number of jpeg renditions

Yann Neuhaus - Thu, 2016-11-24 03:00

As already explained in this blog, we had to remove the PDF renditions for a customer to only keep the jpeg renditions on the ADTS side because the PDF was generated by another third-party tool. If you take a look at the dmr_content items attached to a specific document (parent_id), you will understand that there is by default only one PDF content while there might be a lot more jpeg renditions. Why? The answer to that is pretty simple, the content of the PDF rendition contains all the pages of the document while the jpeg renditions are only for a single page… Therefore if an ADTS is processing a document of 25 pages, then it will create 25 jpeg renditions. Actually that’s even more than that… Indeed by default the ADTS generates two types/formats of jpeg renditions for each page which double the total number of renditions. Here are the two formats available by default:

  • jpeg_lres: (Low Resolutions) that’s actually a real size preview in low resolution of the page
  • jpeg_story: (StoryBoards) that’s a reduced size preview of the page, quite hard to read what’s written…

 

jpeg_lres is the format used by the Thumbnail Server and also by D2 for the preview widget. On the other hand, jpeg_story isn’t used at all in D2 4.5. According to EMC, it *might* has been used for D2 4.1 and previous versions but they aren’t sure about it… ;)

 

Now that this has been said, let’s go back to the title of this blog. Because of this behavior of the ADTS, it might happen that one day you will see hundreds or even thousands of dmr_content items deleted. You might think that there is something wrong, that the cleanup jobs deleted too many objects or something like that… So if this happens to you, please take a look at the format of these dmr_content! Several months ago, the cleanup jobs were inactive for a few weeks because of a bug and when we reactivated them, this happened to us and we finally found out that 95% of these items where only jpeg renditions and that this was actually the expected behavior!

 

After that, we started thinking about how we should handle these jpeg renditions for really big documents? Because having the preview of the documents available in D2 is great but then is it really needed? Generating a preview of the first page or of the 10 first pages might makes sense but would it makes sense to generate a preview for each page of a document bigger than 10 pages? 100 pages? 1 000 pages? These previews are used in D2 and you need to move from one page to the other one starting with the page 1. If you absolutely want to see the preview of the page *364* in D2, then you will need 20 minutes to reach that page in the first place… I think that downloading the document is a little bit faster ;).

 

So is it possible to only generate previews for a few pages and setup a maximum number of jpeg renditions per document? The short answer to that is: yes! And that’s the purpose of this blog, wonderful, isn’t it?!

 

First check out the configuration file that will need to be updated:

[dmadmin@content_server_01 workspace]$ iapi DOCBASE -Udmadmin -Pxxx


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2015
        All rights reserved.
        Client Library Release 7.2.0050.0084


Connecting to Server using docbase DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 013f245a802173d6 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0050.0214  Linux64.Oracle
Session id is s0
API> retrieve,c,dm_document where folder('/System/Media Server/Command Line Files') and object_name = 'storyboard_pdfstoryboard.xml'
...
093f245a801c9075
API> checkout,c,l
...
093f245a801c9075
API> getfile,c,l,/tmp/workspace/storyboard_pdfstoryboard.xml
...
/tmp/workspace/storyboard_pdfstoryboard.xml
API> flushcache,c
...
OK

 

Then retrieve a test document that will be used to see how it is working:

API> retrieve,c,dm_document where r_object_id='093f245a801a8f56'
...
093f245a801a8f56
API> getfile,c,l,/tmp/workspace/TestRenditions.docx
...
/tmp/workspace/TestRenditions.docx

 

Ok so now the two files are stored locally. I retrieved the content of the file TestRenditions.docx in order to be able to regenerate the renditions, you will see how it works later. So let’s check how many renditions this document currently has:

API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='093f245a801a8f56'
r_object_id       full_format         parent_id         content_size  full_content_size       set_time                   set_file                                                                              
----------------  ------------------  ----------------  ------------  ----------------------  -------------------------  ---------------------------------------------------------------------------------------
063f245a801d95cc  jpeg_lres           093f245a801a8f56         60467                   60467  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
063f245a801d95cd  jpeg_lres           093f245a801a8f56        138862                  138862  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
063f245a801d95ce  jpeg_lres           093f245a801a8f56         29596                   29596  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile353776780929575961.tar
.....
063f245a8024b99e  jpeg_story          093f245a801a8f56          3392                    3392  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile7193259325098763580.tar
063f245a8024b99f  jpeg_story          093f245a801a8f56          4718                    4718  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile7193259325098763580.tar
063f245a8024b9a0  jpeg_story          093f245a801a8f56          1567                    1567  7/4/2016 13:22:39          C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile7193259325098763580.tar
.....
063f245a8024b622  msw12               093f245a801a8f56         90535                   90535  7/4/2016 13:22:30          /app/weblogic/tmp/DOCBASE/msD2-01/DefaultFileRenamePolicy.rename8572010254156028259.docx
(52 rows affected)

API> exit
Bye

 

As explained previously in this blog, the document “TestRenditions.docx” (093f245a801a8f56) has 25 pages and therefore there are 25 (jpeg_lres) + 25 (jpeg_story) + 1 (pdf) + 1 (real document) = 52 dmr_content items attached to it and therefore 51 renditions. Now let’s see the content of the configuration file and what to do to change the number of renditions we need:

[dmadmin@content_server_01 workspace]$ pwd
/tmp/workspace
[dmadmin@content_server_01 workspace]$ cat storyboard_pdfstoryboard.xml
<PDFSTORYBOARD_MP_PROPERTIES>
    <FORMAT name="JPEG">
        <PROP name="Format" type="string">JPEG</PROP>
        <PROP name="Width" type="unsigned long" token="doc_token_width">200</PROP>
        <PROP name="Height" type="unsigned long" token="doc_token_height">200</PROP>
        <PROP name="Dpi" type="unsigned long" token="doc_token_dpi">72</PROP>
        <PROP name="KeepRatio" type="boolean">true</PROP>
        <PROP name="Password" type="string">your_password_be_here</PROP>
        <PROP name="Max Pages" type="unsigned long" token="doc_token_maxPages">-1</PROP>
        <PROP name="Frames Requested" type="unsigned long" token="doc_token_frames_requested">-1</PROP>
    </FORMAT>
</PDFSTORYBOARD_MP_PROPERTIES>
[dmadmin@content_server_01 workspace]$ 
[dmadmin@content_server_01 workspace]$ sed -i 's/doc_token_maxPages">-1</doc_token_maxPages">1</' storyboard_pdfstoryboard.xml
[dmadmin@content_server_01 workspace]$ 
[dmadmin@content_server_01 workspace]$ cat storyboard_pdfstoryboard.xml
<PDFSTORYBOARD_MP_PROPERTIES>
    <FORMAT name="JPEG">
        <PROP name="Format" type="string">JPEG</PROP>
        <PROP name="Width" type="unsigned long" token="doc_token_width">200</PROP>
        <PROP name="Height" type="unsigned long" token="doc_token_height">200</PROP>
        <PROP name="Dpi" type="unsigned long" token="doc_token_dpi">72</PROP>
        <PROP name="KeepRatio" type="boolean">true</PROP>
        <PROP name="Password" type="string">your_password_be_here</PROP>
        <PROP name="Max Pages" type="unsigned long" token="doc_token_maxPages">1</PROP>
        <PROP name="Frames Requested" type="unsigned long" token="doc_token_frames_requested">-1</PROP>
    </FORMAT>
</PDFSTORYBOARD_MP_PROPERTIES>

 

As you can see above, I just changed the value assigned to the “doc_token_maxPages” from -1 (unlimited) to 1 (1 page) and that should be it! To apply this change, we need to check in the storyboard_pdfstoryboard.xml file:

[dmadmin@content_server_01 workspace]$ iapi DOCBASE -Udmadmin -Pxxx


        EMC Documentum iapi - Interactive API interface
        (c) Copyright EMC Corp., 1992 - 2015
        All rights reserved.
        Client Library Release 7.2.0050.0084


Connecting to Server using docbase DOCBASE
[DM_SESSION_I_SESSION_START]info:  "Session 013f245a80217406 started for user dmadmin."


Connected to Documentum Server running Release 7.2.0050.0214  Linux64.Oracle
Session id is s0
API> retrieve,c,dm_document where folder('/System/Media Server/Command Line Files') and object_name = 'storyboard_pdfstoryboard.xml'
...
093f245a801c9075
API> setfile,c,l,/tmp/workspace/storyboard_pdfstoryboard.xml
...
OK
API> checkin,c,l
...
093f245a8027254e
API> flushcache,c
...
OK

 

Once this is done, we can remove all current renditions of this document (we saw above pdf, jpeg_lres and jpeg_story renditions) to only let the docx/msw12 file:

API> retrieve,c,dm_document where r_object_id='093f245a801a8f56'
...
093f245a801a8f56
API> removerendition,c,l,pdf
...
OK
API> save,c,l
...
OK
API> flushcache,c
...
OK
API> removerendition,c,l,jpeg_lres
...
OK
API> save,c,l
...
OK
API> flushcache,c
...
OK
API> removerendition,c,l,jpeg_story
...
OK
API> save,c,l
...
OK
API> flushcache,c
...
OK
API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='093f245a801a8f56'
r_object_id       full_format         parent_id         content_size  full_content_size       set_time                   set_file                                                                              
----------------  ------------------  ----------------  ------------  ----------------------  -------------------------  ---------------------------------------------------------------------------------------
063f245a8024b622  msw12               093f245a801a8f56         90535                   90535  7/4/2016 13:22:30          /app/weblogic/tmp/DOMAIN/msD2-01/DefaultFileRenamePolicy.rename8572010254156028259.docx
(1 row affected)

 

The last step is to request the recreation of these renditions (using a setfile), wait 20 seconds or so and then check how much renditions have been recreated:

API> retrieve,c,dm_document where r_object_id='093f245a801a8f56'
...
093f245a801a8f56
API> setfile,c,l,/tmp/workspace/TestRenditions.docx
...
OK
API> save,c,l
...
OK
API> ?,c,select r_object_id, full_format, parent_id, content_size, full_content_size, set_time, set_file from dmr_content where any parent_id='093f245a801a8f56';
r_object_id       full_format         parent_id         content_size  full_content_size       set_time                   set_file                                                                              
----------------  ------------------  ----------------  ------------  ----------------------  -------------------------  ---------------------------------------------------------------------------------------
063f245a8024b622  msw12               093f245a801a8f56         90535                   90535  11/13/2016 12:49:48        /tmp/workspace/TestRenditions.docx
063f245a8024b957  jpeg_lres           093f245a801a8f56         60467                   60467  11/13/2016 12:49:56        C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile1116397023537525059.tar
063f245a8024b958  jpeg_story          093f245a801a8f56          3392                    3392  11/13/2016 12:49:57        C:\Users\SYS_AD~1\AppData\Local\Temp\batchFile6054850753334521340.tar
(3 rows affected)

 

As you can see, there is now only one jpeg rendition per format and that’s for the first page only so that’s a success! If you want to keep only X jpeg renditions per document, now you know how to do it :)

 

Cet article Documentum story – Restrict the number of jpeg renditions est apparu en premier sur Blog dbi services.

UKOUG Conference 2016 coming up fast

Andrew Clarke - Thu, 2016-11-24 02:19
The weather has turned cold, the lights are twinkling in windows and Starbucks is selling pumpkin lattes. Yes, it's starting to look a lot like Christmas. But first there's the wonder-filled advent calendar that is the UKOUG Annual Conference in Birmingham, UK. So many doors to choose from!

The Conference is the premier event for Oracle users in the UK (and beyond). This year has another cracker of an agenda: check it out.

The session I'm anticipating most is Monday's double header with Bryn Llewellyn and Toon Koopelaar's A Real-World Comparison of the NoPLSQL & Thick Database Paradigms. Will they come down on the side of implementing business logic in stored procedures or won't they? It'll be tense :) But it will definitely be insightful and elegantly argued.

Oracle's bailiwick has expanded vastly over the years, and it's become increasingly hard to cover everything. Even so, it's fair to say in recent years older technologies such as Forms have been neglected in favour in favour of shinier baubles. Not this year. There's a good representation of Forms sessions this year, including a talk from Michael Ferrante, the Forms Product Manager. These sessions are all scheduled for the Wednesday, in a day targeted at database developers. If you're an Old Skool developer, especially if you're a Forms developer, and your boss will allow you only one day at the conference, then Wednesday is the day to pick.

Hope to see you there

Data Alignment

Tom Kyte - Thu, 2016-11-24 00:06
Hello, I'm having problems with my data not being aligned and neatly displayed when I perform a SELECT statement. The data is skewed and not aligned with the columns. I've tried statements with the COLUMN and HEADING commands but with no success....
Categories: DBA Blogs

Decryption Issue - Need Help

Tom Kyte - Thu, 2016-11-24 00:06
I have a requirement to decrypt a password coming in source extract file in our project. Currently the same thing is happening through a SQL server stored procedure. But the SQL server will be decommissioned in near future and the whole thing needs t...
Categories: DBA Blogs

Replacing text with extended ascii characters

Tom Kyte - Thu, 2016-11-24 00:06
Hi Tom, I have a column which contains text like [DEG],[MICRO],[PHASE]. I want to replace it with their symbols in oracle. My current version of oracle in 11g. Below link contains the complete list of replacements. http://i.imgur.com/bl7xvZJ.png ...
Categories: DBA Blogs

to_char number format

Tom Kyte - Thu, 2016-11-24 00:06
hi, I am printing a number using below to_char format : declare var number(6,2) begin var := 65.2 dbms_output.put_line(to_char(var,'9999.99')); end; the above code prints ' 65.20' (3 spaces and the number). one extra space is printed...
Categories: DBA Blogs

oracle 12c capacity planning

Tom Kyte - Thu, 2016-11-24 00:06
Hello Tom & Team, Can you please shed some light on capacity planning h/w + Oracle database configuration for Oracle 12c - Multitenant Option being used. We are planning to consolidate/upgrade 10 databases using Multitenant architecture. My thou...
Categories: DBA Blogs

VirtualBox OEL 7.3 Minimal Install for 12c

Michael Dinh - Wed, 2016-11-23 21:47

Finally, I was able to configure OEL 7.3 Minimal Install.

OEL 7.3 was a challenge since NAT and Host Only Networking from VirtualBox was not working.

I broke down and ended up using Bridged Adapter.

Next, blogs out there were using Server with GUI installation which is a really bloated option.

Even tried CentOS 7.0 Gnome and did not like having to install UEK or rebuild RPM.

Did it work?

VirtualBox OEL 7.3 Minimal Install for 12c

Silent Install CRS_SWONLY 12c using OEL 7.3 Minimal Install

Will you try it and let me know?

Happy Thanksgivings!

 


12.2 New Features -- 2 : Partitioning an Existing Table

Hemant K Chitale - Wed, 2016-11-23 19:27
A non-partitioned table can be Partitioned (without having to use DBMS_REDEFINITION) online.

SQL> connect hr/Oracle_4U@PDB1
Connected.
SQL> select count(*) from employees;

COUNT(*)
----------
107

SQL> create table employees_part as select * from employees;

Table created.

SQL> select table_name from user_part_tables;

no rows selected

SQL> alter table employees_part
2 modify
3 partition by range (last_name)
4 (partition p_N values less than ('O'),
5 partition p_Q values less than ('R'),
6 partition p_LAST values less than (MAXVALUE))
7 online;

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'EMPLOYEES_PART'
4 order by partition_position
5 /

PARTITION_NAME
--------------------------------------------------------------------------------
HIGH_VALUE
------------
P_N
'O'

P_Q
'R'

P_LAST
MAXVALUE


SQL>
SQL> select table_name, partitioning_type, partition_count
2 from user_part_tables
3 where table_name = 'EMPLOYEES_PART'
4 /

TABLE_NAME
--------------------------------------------------------------------------------
PARTITION PARTITION_COUNT
--------- ---------------
EMPLOYEES_PART
RANGE 3


SQL>
SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'EMPLOYEES_PART'
4 order by partition_position
5 /

PARTITION_NAME
--------------------------------------------------------------------------------
NUM_ROWS
----------
P_N
71

P_Q
10

P_LAST
26


SQL>


I was able to convert a Non-Partitioned Table to a Range-Partitioned Table online.
.
.
.

Categories: DBA Blogs

12.2 New Features -- 1 : Separate Undo Tablespace for each PDB

Hemant K Chitale - Wed, 2016-11-23 19:06
Unlike 12.1 MultiTenant, 12.2 introduces a separate Undo Tablespace for each PDB.

SQL> l
1 select c.con_id, c.name con_name, t.tablespace_name, t.contents, t.status
2 from v$containers c, cdb_tablespaces t
3 where c.con_id=t.con_id
4 and t.tablespace_name like '%UNDO%'
5* order by 1,2
SQL> /

CON_ID CON_NAME TABLESPACE_NAME CONTENTS STATUS
---------- ---------------- ---------------- --------------------- ---------
1 CDB$ROOT UNDOTBS1 UNDO ONLINE
3 PDB1 UNDOTBS1 UNDO ONLINE
5 PDB2 UNDOTBS1 UNDO ONLINE

SQL>


I have two PDBs and each PDB has an Undo Tablespace.

Let me create a new Undo Tablespace.

SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> create undo tablespace PDB1UNDO ;

Tablespace created.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace='PDB1UNDO';

System altered.

SQL> drop tablespace undotbs1;

Tablespace dropped.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>
SQL> select c.con_id, c.name con_name, t.tablespace_name, t.contents, t.status
2 from v$containers c, cdb_tablespaces t
3 where c.con_id=t.con_id
4 and t.tablespace_name like '%UNDO%'
5 order by 1,2
6 /

CON_ID CON_NAME TABLESPACE_NAME CONTENTS STATUS
---------- ---------------- ---------------- --------------------- ---------
1 CDB$ROOT UNDOTBS1 UNDO ONLINE
3 PDB1 PDB1UNDO UNDO ONLINE
5 PDB2 UNDOTBS1 UNDO ONLINE

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string PDB1UNDO
SQL> select tablespace_name, contents, status
2 from dba_tablespaces
3 where tablespace_name like '%UNDO%'
4 /

TABLESPACE_NAME CONTENTS STATUS
---------------- --------------------- ---------
PDB1UNDO UNDO ONLINE

SQL>


I was able to switch PDB1 to a new Undo Tablespace (and drop the old Undo Tablespace).
.
.
.
Categories: DBA Blogs

Oracle Database 12c Release 2: New Indexing Features – Long Identifiers (Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict)

Richard Foote - Wed, 2016-11-23 17:10
Oracle Database 12c Release 2 has now been officially released (at least on the Oracle Database Cloud and Oracle Database Exadata Express Cloud Services). In the coming weeks, I’ll be blogging about quite a number of new indexing features/capabilities/improvements that have been introduced in Oracle Database 12c Release 2. These include: Advanced Index Compression Tracking […]
Categories: DBA Blogs

Partner Webcast - Oracle Management Cloud- Run IT like a business

Pleasing customers and employees with helpful apps and websites is a worthy endeavor, but one that's becoming more challenging. Customers expect accelerated performance of innovative apps and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Performance aspects of APEX reports

Rob van Wijk - Wed, 2016-11-23 13:30
As this post appears, I'm presenting on this subject for the Dutch Oracle User Group OGh. This blog post won't contain as much detail as the presentation itself, although it's pretty close. If you're interested to see and replay everything for yourself, you can find the material of the presentation in the "Presentations and papers" section and here directly. There are installation instructions inRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.com0

Happy Thanksgiving (Featuring an Email That I Am Thankful For)

Tim Tow - Wed, 2016-11-23 11:12

I get tons of email every day and often have hundreds of emails that have to track on a daily basis which explains, in part, my absence from doing recent blog entries.   Most of the emails I track are sales and support relating to Dodeca.  After all, with Dodeca, the buck stops here, right?  I also get some spam in the mix.  Sometimes, there is an email that really makes my day.  Here is one of those types I received this morning:

From: (masked)
Sent: Wednesday, November 23, 2016 8:48 AM
To: Applied OLAP Support
Subject: Happy Thanksgiving!

Although we are a smaller Essbase shop at 105 users, our users are strong advocates for Essbase and use it extensively. So, it is great timing to say how thankful I am to you and your teams for developing the Dodeca Essbase Add-In. At the end of the day, it’s the user experience that drives the support for Essbase and your product will excite our user base! [And, it requires little or no support from my team!]

Happy Thanksgiving to all!


Wow!  This email really made my day!  To our new customer that sent this email (and who graciously granted me permission to post her words), Happy Thanksgiving to you as well.  We hope this is the first of many Thanksgivings that we work together!


Categories: BI & Warehousing

How to Use Versioning in ODI 12c

Rittman Mead Consulting - Wed, 2016-11-23 09:00
How to Use Versioning in ODI 12c

How many times have you been working on a project and something goes wrong, or the power shuts off, or you go on vacation and someone has messed with your code or somehow your work is lost? Well, now you have an alternative to safe proof your project work.

Versioning in ODI is allowed at various hierarchy levels within the instances and is stored in the master repository. What this means is that if you have multiple work repositories connected to the same master, you can see all the versions when connected to either work repository.

A version is a backup copy of an object that is saved as a checkpoint in ODI. ODI allows you to version Projects, Folders, Packages, Scenarios, Load Plans, Mappings, Procedures, Knowledge Modules, Models, Model Folders and Solutions. You will need to decide which objects to create and manage versions for, but this tutorial will review the process using Packages.

Fast Review: A package is made up of steps organized into a diagram that is executed. The steps include mappings, variables, procedures, ODI tools (such as OdiXMLConcat, OdiZip, etc), models, sub-models and datastores.

  1. Connect and move to the Designer Navigator and expand the Projects folder.

    How to Use Versioning in ODI 12c

  2. Select and expand Packages. In our example I will open up Target Data Load.

    How to Use Versioning in ODI 12c

  3. As you see below a well trained ODI developer has set up the load plans to run in a specific execution and if any fail, to send an email.

    How to Use Versioning in ODI 12c

    In addition to a fail notification, a new manager also wants to know the package executed successfully. We will create an original version of the package and then add a new email notification. Once we have finished our changes, we will create a new version.
  4. Right-click on the Target Data Load package and select Version > Create Version

    How to Use Versioning in ODI 12c

  5. Type 'Target Data Load v1' as the name and 'Existing Target Data Load package (original)' in the description. You can name the original (or next version, depending on where you are in your versions) whatever you would like. Best practice is to keep the name simple and a version. Make sure to put more descriptive details about the purpose of the version in the description box below the version name.

    How to Use Versioning in ODI 12c

    Now that we have our original version safely created, we can make our changes to the package.

  6. If it is not expanded, expand the Internet accordion of the ODI toolbox and locate OdiSendMail. Highlight OdiSendMail and place it on the canvas to the right

    How to Use Versioning in ODI 12c

  7. Using the toolbar connect your last mapping to OdiSendMail 2 using the green ok arrow. Remember to click on the mapping and drag your cursor to OdiSendMail 2.

    How to Use Versioning in ODI 12c

  8. Make sure to click on the white cursor from menu (above the Toolbox, to the left of the green arrows) and click Odi Send Mail 2 so the properties window populates with the mail server information. Press Save. Contact your company email or network admin to get the required smtp information.

    Pro-tip: For our demonstrations and tutorials we use mailtrap.io. It is a working dummy smtp testing server.

  9. Once completed your screen should look similar to the image below.

    How to Use Versioning in ODI 12c

  10. Before we version the changes, we should test them. Execute the new package by right clicking on the newly modified package, in our tutorial it will be Target Data Load. Click OK and accept the defaults in the Run window.

    WARNING: Make sure you are in the correct environment for testing and development. DO NOT run any package that will change or affect any real data.

    How to Use Versioning in ODI 12c

  11. Now check your execution under the Operator tab to make sure it was successfully executed.

    How to Use Versioning in ODI 12c

  12. Now we will create a new version of the package that will be the latest version. We will repeat earlier steps, so return to the Designer Navigator > Projects window and click on the Target Data Load package. Right-click Target Data Load package and go to Version > Create Version. Note when the window appears you see the original version in the bottom box.

    Leave the default name and change it to v2. Then for a description put 'Added successful execution for Target Data Load mappings OdiSendMail' and click ok.

    How to Use Versioning in ODI 12c

    You have now created 2 versions of your package: the original version and the modified version with the success email. Versioning is a key feature in ODI that really allows multiple developers to interact and work together to achieve the best results for data integration projects.

Special Note:

In this post, we reviewed how to use internal versioning in ODI. Rittman Mead always recommend to use an external configuration management systems (ex: GitHub) in ODI releases earlier than 12.2.1.2, rather than the internal versioning. In the next ODI patchset, there will be integration with Git for better work flow control. Subversion integration is currently available as of ODI 12.1.3.

If you are interested in seeing how to use Git in conjunction with older versions of ODI (prior to 12.2.1.2.6) stay tuned and check out an upcoming video here using Rittman Mead Principal Consultant Pete Tamsin's method for 'Using Git in an ODI Procedure'.

Huge thanks to Pete Tamsin and Michael Rainey for their help editing this post. No man, or woman, is an island!

Find me on:

How to Use Versioning in ODI 12c
How to Use Versioning in ODI 12c



Related Videos:





Categories: BI & Warehousing

Pages

Subscribe to Oracle FAQ aggregator