July 2017

12.2 New Feature: the FLEX ASM disk group part 2

In the first part of this series I explained the basics and some potential motivation behind the use of ASM Flex disk groups. In this part I would like to complete the description of new concepts.

New Concepts related to FLEX ASM Disk Groups

With the Flex disk group mounted, the next steps are to create a few new entities. First, I want to create a Quota Group. The Quota Group – as the name implies – will enforce quotas for entities residing within it. It is optional to add one yourself, Oracle creates a default Quota Group for you that does not enforce storage limits. As you will see later, the default Quota Group will be assigned to all new databases in the Flex ASM disk group.

ODTUG’s KSCOPE 2nd Annual Geekathon

After returning from KSCOPE two weeks ago, I was again approached to be a judge this year on the Geekathon 2017.

Enough with AFIEDT.BUF

You are in SQL*Plus. You entered a command and urgh, there was a typo. No worries, you bring up the command in an editor by typing:

SQL> ed

This opens up an editor, such as notepad.exe in Windows or vi in Unix, etc. And it puts the last SQL you entered in a file, oddly named, afiedt.buf. You don't like it and you want a name easier to type. Is it possible? Of course.

History of Afiedt.Buf

First a little bit of background information on the odd name. SQL*Plus as a tool evolved from another tool Oracle provided a long, long time ago, called--rather uncreatively--User Friendly Interface, or UFI. When the editor wanted to bring up a file for editing, the file had to be given a name unique enough so as not to conflict with anything else. Therefore the file was named ufiedt.buf, which roughly indicated UFI Editor Buffer.

12.2 Introduction to Real-Time Materialized Views (The View)

Although I usually focus on index related topics, I’ve always kinda considered Materialized Views (MVs) as an index like structure, which Oracle can automatically update and from which Oracle can efficiently retrieve data. The cost of maintaining a Materialized View Log is not unlike the cost of maintaining an index structure, the benefits of which […]

Postgresql block internals, part 3

This is the third part in a series of blogposts about how postgresql manages data in its blocks (called ‘pages’ in postgres speak). If you found this post and did not read the previous ones, it might be beneficial to read block internals (part 1) and block internals, part 2 first. In these blogposts I’ve shown how heap and index pages look like, and how these can be investigated, including looking at the raw block information.

This blogpost is intended to show the effects on pages when DML happens. This is inherently different from my personal reference of database implementation, which is the oracle database.

Setting APPINFO in SQL*Plus

Ever used the MODULE column of V$SESSION view? If you haven't, you are missing out on a very important piece of instruemntation code built right into the Oracle database session management. This allows you to assign a completely arbitary name, as you would see will properly describe for your application. Later when you want to identify that session, you can check the MODULE column in V$SESSION. Even though the userid is the same for all these sessions; the module column will help you identify the session.

Let's see how it works by a little example. Here is how you check the module information.


select module
from v$session
where username = 'SYS'

MODULE
-----------------------------
sqlplus.exe
sqlplus.exe

In both cases the MODULE column shows the same value. You can set he module to a more descriptive name by issuing this command:

12.2 ACFS compression, part I

One of the new 12.2 features is the ability to transparently compress ACFS filesystems.

Compression is enabled using acfsutil utility:

[root@raca1 ~]# acfsutil -h compress on
Usage: acfsutil [-h] compress on [-a ]
- Set default compression algorithm
Currently only 'lzo' available
- Enable compression on volume

Clearly there is support for more compression algorithms to be added in the future but right now only lzo is supported.

Let's go ahead and enable compression on the ACFS filesystem I have:

[root@raca1 ~]# acfsutil compress on /u02/oradata

Compression status can be checked using acfsutil info fs:

[root@raca1 ~]# acfsutil info fs /u02/oradata
/u02/oradata
ACFS Version: 12.2.0.1.0

Oracle Security Audit and Open Ports on a Database Server

As part of a detailed security audit of an Oracle database performed by our company we look at most areas that are related to two things; the security of the Oracle platform itself, i.e. the Oracle database and its software....[Read More]

Posted by Pete On 07/07/17 At 04:31 PM

OFE

The title is a well-known shorthand for parameter optimizer_features_enable and it has been the topic of a recent blog post by Mike Dietrich in which he decries the practice of switching the parameter back to an older version on an upgrade (even though, as he points out, Oracle support has been known to recommend it and the manuals describe – though not with 100% accuracy – why you might do so).

I am one of the people who will suggest that on the upgrade a client should consider setting the optimizer_features_enable to the version just left behind as a strategy for getting to a newer version of the base code while minimising the threat of plan instability, so I’m going to play devil’s advocate in this case even though, as we shall see, I am nearly 100% in favour of Mike’s complaint.

Friday Philosophy – Robots Rising & Tech Taking Over?

Today I saw some cracking photographs of a lighthouse. How many of us have at some point wondered if it might be nice to be a lighthouse keeper? The solitude, the scenery, the stoic fortitude in the face of the storm – quite literally. (Thank you Brendan Tierney for the photo I’ve stolen from him).