Updated Oracle password algorithm article for 18c.


https://seanstuber.wordpress.com/how-oracle-stores-passwords/

Just minor updates.

18c supports the same hashes as 12cR2 and doesn’t add any new ones. 18c also uses the same sqlnet.ora parameters and values as 12cR2.

Updated links to 18c documentation and added note that 18c is the same as 12.1.0.2 and 12cR2.

Advertisements

How to use DBMS_LDAP (part 5: Timeouts)


Table of Contents

  1. Introduction
  2. Establishing a Connection
  3. Searching Hierarchical Data
  4. Browsing Attributes
  5. Timeouts (this page)

Timeouts

LDAP servers are designed to be fast; but it is still possible for an action to take a long time. A deep sub-tree search with an open search criteria could require from seconds to even minutes to return data if the directory was large and no other criteria limited the results.

Thus clients are able to put timeout limits on their requests to abort if they take too long. The public servers I’ve been using are relatively small in scope and/or have result limits on them so they tend to be fast but we’ll create an intentionally expensive search and time it. I’ll use the approximate equality operator to do an expensive search into a deep and wide subtree.

DECLARE
    v_result           PLS_INTEGER;
    v_session          DBMS_LDAP.session;
    v_search_attrs     DBMS_LDAP.string_collection;
    v_search_results   DBMS_LDAP.MESSAGE;
    v_start            TIMESTAMP WITH TIME ZONE;
    v_end              TIMESTAMP WITH TIME ZONE;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_start := SYSTIMESTAMP;
    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'dc=cmu,dc=edu',
                           scope      => DBMS_LDAP.scope_subtree,
                           filter     => 'maillocaladdress~=ayushb',
                           attrs      => v_search_attrs,
                           attronly   => 0,
                           res        => v_search_results);
    v_end := SYSTIMESTAMP;

    DBMS_OUTPUT.put_line(v_end - v_start);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

+000000000 00:00:20.977627000

So, just over 20 seconds to do that search. If that is too long, a time out can be created using the DBMS_LDAP.TIMEVAL type. Then instead of using SEARCH_S, we use SEARCH_ST – “T” for timed. If the timeout is exceeded an exception will be raised. In order to more clearly capture the time, I’ll disable exceptions and examine the result code of the SEARCH_ST function instead. We’ll put a 2 second time limit on the search and check the timing when we’re done.

DECLARE
    v_result           PLS_INTEGER;
    v_session          DBMS_LDAP.session;
    v_search_attrs     DBMS_LDAP.string_collection;
    v_search_results   DBMS_LDAP.MESSAGE;
    v_start            TIMESTAMP WITH TIME ZONE;
    v_end              TIMESTAMP WITH TIME ZONE;
    v_timeout          DBMS_LDAP.timeval;
BEGIN
    DBMS_LDAP.use_exception := FALSE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_timeout.seconds := 2;

    v_start := SYSTIMESTAMP;
    v_result :=
        DBMS_LDAP.search_st(ld         => v_session,
                            base       => 'dc=cmu,dc=edu',
                            scope      => DBMS_LDAP.scope_subtree,
                            filter     => 'maillocaladdress~=ayushb',
                            attrs      => v_search_attrs,
                            attronly   => 0,
                            tv         => v_timeout,
                            res        => v_search_results);
    v_end := SYSTIMESTAMP;

    DBMS_OUTPUT.put_line('Error code: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    DBMS_OUTPUT.put_line(v_end - v_start);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Error code: 85 Timed out
+000000000 00:00:02.000382000

As expected, the search quit after 2 seconds and exited.

The TIMEVAL type is actually a record of two fields: seconds and microseconds.

    TYPE TIMEVAL IS RECORD
      ( seconds  PLS_INTEGER,
        useconds PLS_INTEGER
      );

Either or both fields may be populated. If both are populated the timeout value will be the sum of the two times. As in the following example where seconds is populated with 1 and useconds with 1.5 million, for a total timeout of 2.5 seconds.

DECLARE
    v_result           PLS_INTEGER;
    v_session          DBMS_LDAP.session;
    v_search_attrs     DBMS_LDAP.string_collection;
    v_search_results   DBMS_LDAP.MESSAGE;
    v_start            TIMESTAMP WITH TIME ZONE;
    v_end              TIMESTAMP WITH TIME ZONE;
    v_timeout          DBMS_LDAP.timeval;
BEGIN
    DBMS_LDAP.use_exception := FALSE;

    v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_timeout.seconds := 1;
    v_timeout.useconds := 1500000;

    v_start := SYSTIMESTAMP;
    v_result :=
        DBMS_LDAP.search_st(ld         => v_session,
                            base       => 'dc=cmu,dc=edu',
                            scope      => DBMS_LDAP.scope_subtree,
                            filter     => 'maillocaladdress~=ayushb',
                            attrs      => v_search_attrs,
                            attronly   => 0,
                            tv         => v_timeout,
                            res        => v_search_results);
    v_end := SYSTIMESTAMP;

    DBMS_OUTPUT.put_line('Error code: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
    DBMS_OUTPUT.put_line(v_end - v_start);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Error code: 85 Timed out
+000000000 00:00:02.500799000

While the datatype of each value is PLS_INTEGER, which accepts negative values, if either value is negative you will receive an error code 1027 (Unknown error.)
Similarly if both values are left unpopulated (both NULL) a 1027 error will be returned.

If exceptions are enabled then the following exception will be raised.

ORA-31207: DBMS_LDAP: PL/SQL - Invalid LDAP search time value.

If both values are set to 0, that is treated as no timelimit. The search will run to completion or the server’s limit.

How to use DBMS_LDAP (part 4: Attributes)


Table of Contents

  1. Introduction
  2. Establishing a Connection
  3. Searching Hierarchical Data
  4. Browsing Attributes (this page)
  5. Timeouts

Searching Hierarchical Data with Attributes

Returning attributes with search results

The examples in the prior chapter use the “1.1” code to return no attributes.
In this example we’ll return two of the attributes associated with an entry.
Instead of building a collection of one element containing “1.1”, the collection will have the name of each attributes we want to return.
Also note, the attronly => 0 parameter. If attronly is 1 then only the names of attributes will be returned. With 0, the values associated with each attributes are also returned.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_values               DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := 'description';
    v_search_attrs(2) := 'admin';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=beach,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => 0,
                           res        => v_search_results);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
    DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);

    FOR i IN 1 .. v_search_attrs.COUNT
    LOOP
        v_values := DBMS_LDAP.get_values(v_session, v_entry, v_search_attrs(i));

        IF v_values.COUNT > 0
        THEN
            FOR j IN v_values.FIRST .. v_values.LAST
            LOOP
                DBMS_OUTPUT.put_line(v_search_attrs(i) || ' : ' || v_values(j));
            END LOOP;
        ELSE
            DBMS_OUTPUT.put_line(v_search_attrs(i) || ' not found');
        END IF;
    END LOOP;

    v_result := DBMS_LDAP.msgfree(v_entry);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/
DN: host=beach,ou=hosts,dc=debian,dc=org
description : bugs.debian.org web frontend
admin : debian-admin@lists.debian.org

Sometimes you might not know all of the attributes for an entry. Perhaps you’re doing a sub-tree search across different entry types and the attributes might change. In these scenarios you may need to employ a wildcard search and return all of the attributes for a given entry.

To return all of them, simply define an attribute array of one element consisting of the value ‘*’. It is legal syntax to include other attribute names in the array, but there is no functional value in doing so.

In the snippet below we’ll pull all of the attributes for the “host=beach” entry. Because we don’t know their names, in order to retrieve them we use a new data type: “DBMS_LDAP.BER_ELEMENT”. “BER” stands for Basic Encoding Rule. The datatype is a RAW, holding a pointer to the encoded attributes. With attronly set to 1, only the names of the attributes will be returned.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_attribute_name       VARCHAR2(256); 
    v_ber                  DBMS_LDAP.ber_element;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';    

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=beach,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => 1,
                           res        => v_search_results);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
    DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);

    v_attribute_name := DBMS_LDAP.first_attribute(v_session, v_entry, v_ber);

    WHILE v_attribute_name IS NOT NULL
    LOOP
        DBMS_OUTPUT.put_line(v_attribute_name);

        v_attribute_name := DBMS_LDAP.next_attribute(v_session, v_entry, v_ber);
    END LOOP;

    DBMS_LDAP.ber_free(v_ber, 1);

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/
DN: host=beach,ou=hosts,dc=debian,dc=org
objectClass
distribution
access
admin
architecture
host
purpose
allowedGroups
description
hostname
sshRSAHostKey
mXRecord
rebootPolicy
physicalHost
sponsor
ipHostNumber

Once you have the names you could then go back and pull values for specific attributes as shown in the first example above. Or, we can return the attributes from a wildcard along with their attributes.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_attribute_name       VARCHAR2(256);
    v_attribute_values     DBMS_LDAP.string_collection;
    v_ber                  DBMS_LDAP.ber_element; -- Basic Encoding Rule
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '*';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=beach,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => 0,
                           res        => v_search_results);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
    DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);

    v_attribute_name := DBMS_LDAP.first_attribute(v_session, v_entry, v_ber);

    WHILE v_attribute_name IS NOT NULL
    LOOP
        v_attribute_values := DBMS_LDAP.get_values(v_session, v_entry, v_attribute_name);

        CASE v_attribute_values.COUNT
            WHEN 0
            THEN
                NULL;
            WHEN 1
            THEN
                DBMS_OUTPUT.put_line(v_attribute_name || ' : ' || v_attribute_values(0));
            ELSE
                DBMS_OUTPUT.put_line(v_attribute_name || ' :');

                FOR i IN v_attribute_values.FIRST .. v_attribute_values.LAST
                LOOP
                    DBMS_OUTPUT.put_line('...' || v_attribute_values(i));
                END LOOP;
        END CASE;

        v_attribute_name := DBMS_LDAP.next_attribute(v_session, v_entry, v_ber);
    END LOOP;

    DBMS_LDAP.ber_free(v_ber, 1);

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/
DN: host=beach,ou=hosts,dc=debian,dc=org
objectClass :
...top
...debianServer
distribution : Debian GNU/Linux
access : restricted
admin : debian-admin@lists.debian.org
architecture : amd64
host : beach
purpose : [[*bugs.debian.org|bugs web frontend]]
allowedGroups :
...debbugs
...Debian
description : bugs.debian.org web frontend
hostname : beach.debian.org

Note, some of the attributes can have more than one value. The “objectClass” attribute is a common one to have multiple values. This is important to remember when retrieving values for an entry that there isn’t a 1:1 distribution.

Also note, while you can wildcard the entirety of an attribute set, the “attrs” parameter collection does not support wildcards within an attribute name.
For example there is no syntax to request all attributes starting with the letter “a”. Trying something like “v_search_attrs(1) := ‘a*’;” won’t produce an error, it will simply fail to find an attribute with the exact name “a*”.

If you wanted to simulate such functionality you would need to return the list of all attribute names, then use that list to generate just the subset of attributes in which you were interested and build a collection of those. Then search again using just those names, as in the first example, but with attrsonly=>0 to return their values.

In the next chapter we’ll explore setting timeouts on searches.

How to use DBMS_LDAP (part 3: Searching)


Table of Contents

  1. Introduction
  2. Establishing a Connection
  3. Searching Hierarchical Data (this page)
  4. Browsing Attributes
  5. Timeouts

Searching Hierarchical Data

Once you’ve established a connection, the next thing you’ll probably want to do is start searching for records. Either to pull the data back to a client, or to modify one or more records or simply to check for existence of some records. Such as authorization checks based on a user’s membership in one or more groups; or searching for users with particular attributes.

One of the important things to remember about LDAP searches is, unlike the flat nature of a SQL WHERE-clause against a table, the data is hierarchical. That is, you must specify a depth scope when looking at data.

So, using the sample hierarchy from the introduction:

DC=NET
    DC=Example
        OU=People
            OU=Managers
                uid=1234
                    CN=Jane Doe
                    hiredate=2005-04-03
                    email=jane.doe@example.net
                    department=Acquisitions
                    objectClass=person
                uid=3456
                    CN=John Doe
                    hiredate=2006-05-04
                    email=john.doe@example.net
                    department=Sales
                    objectClass=person
            OU=Consultants
                uid=1987
                    CN=Mickey Mouse
                    hiredate=2005-12-11
                    email=mickey.mouse@example.net
                    department=Acquisitions
                    objectClass=person
                uid=6543
                    CN=Donald Duck
                    hiredate=2008-07-06
                    email=donald.duck@example.net
                    department=Sales
                    objectClass=person
  • We could search for the existence of a particular record directly.

    uid=1987,ou=consultants,ou=people,dc=example,dc=net
  • We could search one level, perhaps getting a list of all consultants.

    ou=consultants,ou=people,dc=example,dc=net
  • We can search an entire subtree, for example, all people regardless of group.

    ou=people,dc=example,dc=net

Within DBMS_LDAP the three search scopes are defined by constants:

SCOPE_BASE     CONSTANT NUMBER := 0;
SCOPE_ONELEVEL CONSTANT NUMBER := 1;
SCOPE_SUBTREE  CONSTANT NUMBER := 2;

While some LDAP servers and clients support a 4th type subordinate sub-tree searching; DBMS_LDAP does not provide such a mechanism. You could mimic the functionality (but not the efficiency) with a sub-tree search combined with an appropriate filter to eliminate the top level group from the results.

In addition to the search scope, you must also specify what will be returned from a search that matches the criteria. There are three levels of detail in the results:

  • Entry Distinguished Names only
  • Entry Distinguished Names, along with names of Attributes for each entry
  • Entry Distinguished Names, along with the names and values of Attributes for each entry

Attributes are specified by a string collection regardless of the level of detail. You can provide a string collection populated with the names of the attributes to be returned for each entry. Alternately, define the collection to have only one entry with a wildcard “*” to get all attributes, or provide a collection of one entry consisting of the magic string “1.1”, meaning return no attributes at all.
Within this article, all searches will use “1.1”, hence excluding attributes from the results. Including the attributes and browsing their names and values will be explored in the next chapter of this series.

Searching with BASE scope, no attributes

The simplest search is just a BASE scope, meaning you will provide the entire DN as a location. If you include no attributes, then this type of search is mosty just a an existence check. If you can find the record it exists, if you can’t, it doesn’t.
In the example below we’ll search the public Debian LDAP server for a specific host name. To use a BASE scope, we use the constant DBMS_LDAP.SCOPE_BASE. We’ll return no attributes, so our search list is “1.1”. You must specify a filter on the search, in this case we’ll leave it open to any record that matches the specified DN. Every record has an objectclass attribute, so our filter is a wildcard on that attribute: “objectclass=*”. The attronly parameter doesn’t apply since we aren’t returning any attributes so just leave it NULL.

In these examples, if we find a match we’ll display the DN as a single string and also break the DN into its constituent levels within the hierarchy.

If we don’t find a matching entry then the search_s function will raise an exception.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_dn_pieces            DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=beach,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => NULL,
                           res        => v_search_results);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
    DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
    v_dn_pieces := DBMS_LDAP.explode_dn(dn => v_distinguished_name, notypes => 0);

    FOR i IN v_dn_pieces.FIRST .. v_dn_pieces.LAST
    LOOP
        DBMS_OUTPUT.put_line(i || ': ' || v_dn_pieces(i));
    END LOOP;

    v_result := DBMS_LDAP.msgfree(v_entry);
    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

DN: host=beach,ou=hosts,dc=debian,dc=org
0: host=beach
1: ou=hosts
2: dc=debian
3: dc=org

Instead of raising an exception we could disable exceptions for the search and then check the result value of the search_s function. You can test this by changing “beach” in the sample to any host name not in their server, like “xxxxxxx”.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
    v_dn_pieces            DBMS_LDAP.string_collection;
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    DBMS_LDAP.use_exception := FALSE;
    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'host=xxxxxxx,ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_base,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => NULL,
                           res        => v_search_results);

    IF v_result = DBMS_LDAP.no_such_object
    THEN
        DBMS_OUTPUT.put_line('Base DN not found');
    ELSE
        DBMS_LDAP.use_exception := TRUE;
        v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

        v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
        DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
        v_dn_pieces := DBMS_LDAP.explode_dn(dn => v_distinguished_name, notypes => 0);

        FOR i IN v_dn_pieces.FIRST .. v_dn_pieces.LAST
        LOOP
            DBMS_OUTPUT.put_line(i || ': ' || v_dn_pieces(i));
        END LOOP;

        v_result := DBMS_LDAP.msgfree(v_entry);
    END IF;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Base DN not found

For the remainder of the examples I will simply raise exceptions on a failed search.

Searching with scope of One Sub-Level, no attributes

Instead of searching for a record at a specific DN, you could instead search for any entries within one level of the directory matching your search criteria. Here we’ll again search within the hosts level, but we’ll allow all records under hosts. As the name implies, the constant to do one-level searching is DBMS_LDAP.SCOPE_ONELEVEL.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'ou=hosts,dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_onelevel,
                           filter     => 'objectclass=*',
                           attrs      => v_search_attrs,
                           attronly   => 1,
                           res        => v_search_results);

    v_result := DBMS_LDAP.count_entries(v_session, v_search_results);

    DBMS_OUTPUT.put_line('Entry count: ' || v_result);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    WHILE v_entry IS NOT NULL
    LOOP
        v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
        DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
       
        v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
    END LOOP;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Entry count: 192
DN: host=klecker,ou=hosts,dc=debian,dc=org
DN: host=lobos,ou=hosts,dc=debian,dc=org
DN: host=wieck,ou=hosts,dc=debian,dc=org
DN: host=schumann,ou=hosts,dc=debian,dc=org
DN: host=handel,ou=hosts,dc=debian,dc=org
DN: host=geo1,ou=hosts,dc=debian,dc=org
DN: host=geo2,ou=hosts,dc=debian,dc=org
DN: host=draghi,ou=hosts,dc=debian,dc=org
DN: host=kaufmann,ou=hosts,dc=debian,dc=org
DN: host=wolkenstein,ou=hosts,dc=debian,dc=org
DN: host=sibelius,ou=hosts,dc=debian,dc=org
DN: host=tchaikovsky,ou=hosts,dc=debian,dc=org
DN: host=pettersson,ou=hosts,dc=debian,dc=org
DN: host=lully,ou=hosts,dc=debian,dc=org
DN: host=abel,ou=hosts,dc=debian,dc=org
DN: host=arnold,ou=hosts,dc=debian,dc=org
DN: host=antheil,ou=hosts,dc=debian,dc=org
...


Searching with scope of entire Sub-Tree, no attributes

And finally the last scope to explore is the Sub-Tree scope which will search for records at any and all depths within a directory tree below the level of the specified base DN. Here again the package includes a constant: DBMS_LDAP.SCOPE_SUBTREE. Since we’re searching the entire tree the results could get large, especially if our filter allows every object class to be returned.

So, in this example, we’ll restrict the filter to object classes of type “debiangroup”. We’ll start the search at the top of the tree looking for everything under “dc=debian,dc=org”.

DECLARE
    v_result               PLS_INTEGER;
    v_session              DBMS_LDAP.session;
    v_search_attrs         DBMS_LDAP.string_collection;
    v_search_results       DBMS_LDAP.MESSAGE;
    v_entry                DBMS_LDAP.MESSAGE;
    v_distinguished_name   VARCHAR2(256);
BEGIN
    DBMS_LDAP.use_exception := TRUE;

    v_session := DBMS_LDAP.init(hostname => 'db.debian.org', portnum => DBMS_LDAP.port);

    v_search_attrs(1) := '1.1';

    v_result :=
        DBMS_LDAP.search_s(ld         => v_session,
                           base       => 'dc=debian,dc=org',
                           scope      => DBMS_LDAP.scope_subtree,
                           filter     => 'objectclass=debiangroup',
                           attrs      => v_search_attrs,
                           attronly   => 1,
                           res        => v_search_results);

    v_result := DBMS_LDAP.count_entries(v_session, v_search_results);

    DBMS_OUTPUT.put_line('Entry count: ' || v_result);

    v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);

    WHILE v_entry IS NOT NULL
    LOOP
        v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
        DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
       
        v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
    END LOOP;

    v_result := DBMS_LDAP.unbind_s(v_session);
END;
/

Entry count: 2172
DN: gid=nm,ou=users,dc=debian,dc=org
DN: gid=qa,ou=users,dc=debian,dc=org
DN: gid=adm,ou=users,dc=debian,dc=org
DN: gid=wnpp,ou=users,dc=debian,dc=org
DN: gid=list,ou=users,dc=debian,dc=org
DN: gid=dput_OBS,ou=users,dc=debian,dc=org
DN: gid=visi,ou=users,dc=debian,dc=org
DN: gid=above,ou=users,dc=debian,dc=org
DN: gid=udcvs,ou=users,dc=debian,dc=org
DN: gid=guest,ou=users,dc=debian,dc=org
DN: gid=debwww,ou=users,dc=debian,dc=org
DN: gid=doccvs,ou=users,dc=debian,dc=org
DN: gid=globus,ou=users,dc=debian,dc=org
DN: gid=debcvs,ou=users,dc=debian,dc=org
DN: gid=popcon,ou=users,dc=debian,dc=org
DN: gid=webwml,ou=users,dc=debian,dc=org
DN: gid=wb-ppc,ou=users,dc=debian,dc=org
DN: gid=buildd,ou=users,dc=debian,dc=org
DN: gid=aptcvs,ou=users,dc=debian,dc=org
DN: gid=Debian,ou=users,dc=debian,dc=org
DN: gid=wb-arm,ou=users,dc=debian,dc=org
...

Searching with Filters

The Search API requires a filter, so each of the examples above includes one but all were fairly simple. Either the generic all filter of ‘objectclass=*’
or a specific object class ‘objectclass=”debian group”‘. The LDAP filter syntax is much richer though. The parameter is simply a VARCHAR2, and the filter string is passed “as is” to the server, so the supported functionality is determined by the server and not the DBMS_LDAP package.

A basic LDAP filter takes the form of:

(atttribute_name operator value)
  • The attribute name is the name of any attribute that might be found within an entry. It need not be an attribute that exists in every entry, as many(most) will not.
  • The operator could be =, >=, or <=. Inequality comparisons can be a little tricky. From the client side, all of the data appears as text; but within the LDAP server the data is managed by a schema with types and defined comparison rules. Those rules may use case-sensitive text comparison, or case-insensitive. Numerals may be compared as numeric or text data depending on the schema's definition for that attribute. Some data may not have ordering rules making less-than or greater-than comparisons meaningless. An attribute classifying an entry as "animal", "vegetable", or "mineral" for example would not necessarily have an ordering criteria, only an equality or inequality comparison.
  • The filter value could be a string like the “debian group” example earlier, or a wild card like “*”, or a combination of string and wildcard such as “abc*” to find all entries with an attribute beginning with “abc”.
  • Technically, a filter should be wrapped in parentheses; but if you have a single filter condition with no modifiers, then DBMS_LDAP will accept it as is.
  • The basic filters can then be modified by AND, OR, or NEGATION operators.

    • The AND operator “&” applies the intersection of conditions that follow.
      (&(a=1)(b=2)) – Find records where a=1 and b=2, i.e. (a,b) = (1,2)
      (&(x=10)(y=100)(z=1000)) – Find records where x=10 and y=100 and z=1000, i.e. (x,y,z) = (10,100,1000)
      As a matter of efficiency, it is usually better to have an AND condition with many sub-filters than a more extensive filter using many AND conditions with fewer filters in each.

      It is legal to have a filter consisting of only the AND operator: (&). This filter condition simply evaluates to TRUE for any entry.

    • The OR operator “|” applies the union of the conditions that follow.
      (|(a=1)(b=2)) – Find any records where a=1 or b=2
      (|(x=10)(y=100)(z=1000)) – Find records where x=10 or y=100 or z=1000
      Similar to AND operators, it is usually better to have an OR condition with many sub-filters than to use many OR conditions across fewer sub-filters.

      It is legal to have a filter consisting of only the OR operator: (|). This filter condition simply evaluates to FALSE for any entry.

    • The NEGATION operator “!” simply negates whatever condition follows it.
      (!(objectClass=server)) – Find all records that do not have a server object class
      Unlike the AND and OR operators, the NEGATION operator can not operate on a list of conditions, only a single filter condition can be negated with a single NEGATION operator.

    And finally, you can nest filter conditions within one another, thus allowing more complicated conditions such as: Find all Managers named Jane Doe or Consultants named Mickey Mouse.

    (|(&(OU=Managers)(CN=Jane Doe))(&(OU=Consultants)(CN=Mickey Mouse)))

    With some formatting, the logic is a little easier to follow.

    (|
     (&(OU=Managers)(CN=Jane Doe))
     (&(OU=Consultants)(CN=Mickey Mouse))
    )
    

    Or, we could find John Doe and Donald Duck by negating the previous search.

    (!
       (|
          (&(OU=Managers)(CN=Jane Doe))
          (&(OU=Consultants)(CN=Mickey Mouse))
       )
    )
    

    Data size limits and result set pagination

    There is one additional filtering criteria you may run into with large directories. That is a size limit on returned results. LDAP servers may be willing to pump an unlimited amount of data back to a client, or may have the limits set high enough that your searches never exceed their thresholds; but eventually you will likely come across a result set too large to be returned. When that happens you will get an exception:

    ORA-31202: DBMS_LDAP: LDAP client/server error: Sizelimit exceeded

    or, if you have exceptions turned off, then search_s will return a result code of DBMS_LDAP.SIZELIMIT_EXCEEDED.

    While many LDAP servers support paginated results to handle this situation, DBMS_LDAP, unfortunately, does not have a means of invoking such functionality.

    If you encounter this the only way to get around it from the client side is to construct a series of filters that will span the entire data set.

    In the example below I use the following filter:

    filter     => '&(status=Active)(uid=ab*)'

    Simply searching for all accounts failed. Searching for all Active accounts still failed. Searching for all Active accounts with a uid starting with an “a” still failed. When I got to the point of limiting the uid to those starting with “ab” for just Active accounts I finally got small enough results to pass the server’s size limit. I could then iterate across aa-zz combinations for both Active and not Active accounts to pull all of the data. Of course I might miss ids with leading numeric characters; but hopefully, if you had to do manual pagination you would work with the server’s admins to determine what the best spanning ranges would be.

    DECLARE
        v_result               PLS_INTEGER;
        v_session              DBMS_LDAP.session;
        v_search_attrs         DBMS_LDAP.string_collection;
        v_search_results       DBMS_LDAP.MESSAGE;
        v_entry                DBMS_LDAP.MESSAGE;
        v_distinguished_name   VARCHAR2(256);
        v_dn_pieces            DBMS_LDAP.string_collection;
    BEGIN
        DBMS_LDAP.use_exception := TRUE;
    
        v_session := DBMS_LDAP.init(hostname => 'ldap.andrew.cmu.edu', portnum => DBMS_LDAP.port);
    
        v_search_attrs(1) := '1.1';
        DBMS_LDAP.use_exception := FALSE;
        v_result :=
            DBMS_LDAP.search_s(ld         => v_session,
                               base       => 'ou=account,dc=cmu,dc=edu',
                               scope      => DBMS_LDAP.scope_onelevel,
                               filter     => '&(status=Active)(uid=ab*)',
                               attrs      => v_search_attrs,
                               attronly   => 1,
                               res        => v_search_results);
    
        IF v_result != DBMS_LDAP.success
        THEN
            DBMS_OUTPUT.put_line('Search failed with code: ' || v_result || ' ' || DBMS_LDAP.err2string(v_result));
        ELSE
            DBMS_LDAP.use_exception := FALSE;
            v_result := DBMS_LDAP.count_entries(v_session, v_search_results);
    
            DBMS_OUTPUT.put_line('Entry count: ' || v_result);
    
            v_entry := DBMS_LDAP.first_entry(v_session, v_search_results);
    
            WHILE v_entry IS NOT NULL
            LOOP
                v_distinguished_name := DBMS_LDAP.get_dn(v_session, v_entry);
                DBMS_OUTPUT.put_line('DN: ' || v_distinguished_name);
    
                v_entry := DBMS_LDAP.next_entry(v_session, v_entry);
            END LOOP;
        END IF;
    
        v_result := DBMS_LDAP.unbind_s(v_session);
    END;
    /
    Entry count: 71
    DN: uid=ABHAT,ou=account,dc=cmu,dc=edu
    DN: uid=ABRUNK,ou=account,dc=cmu,dc=edu
    DN: uid=ABIHAUS,ou=account,dc=cmu,dc=edu
    DN: uid=ABUSE,ou=account,dc=cmu,dc=edu
    DN: uid=ABOY,ou=account,dc=cmu,dc=edu
    DN: uid=ABERENDEYEV,ou=account,dc=cmu,dc=edu
    DN: uid=abenavides,ou=account,dc=cmu,dc=edu
    DN: uid=abelsaj,ou=account,dc=cmu,dc=edu
    DN: uid=abhinav,ou=account,dc=cmu,dc=edu
    DN: uid=abeer,ou=account,dc=cmu,dc=edu
    DN: uid=abhishekveldurthy,ou=account,dc=cmu,dc=edu
    DN: uid=ab,ou=account,dc=cmu,dc=edu
    DN: uid=abhay,ou=account,dc=cmu,dc=edu
    DN: uid=abinajay,ou=account,dc=cmu,dc=edu
    DN: uid=abhayjoseph,ou=account,dc=cmu,dc=edu
    DN: uid=abhishekjain,ou=account,dc=cmu,dc=edu
    DN: uid=abreis,ou=account,dc=cmu,dc=edu
    DN: uid=abhinavas,ou=account,dc=cmu,dc=edu
    ...
    

    That covers all of the basic of searching for entries within an LDAP hierarchy.
    In the next section we’ll extend the search results to include attribute lists with and without their values.

Unplug PDB from 12.2 CDB and Plug into 18c CDB


Last week the 18c Linux on-premises software was released for download. So this weekend I installed it on my test server and gave it a whirl.

The software installs themselves went smoothly and then it was time to do an upgrade. Below I’ve capture the steps and output of an upgrade by unplug/plug from older release to newer release.

The Setup:

12cR2 CDB:  sdscdb2
12cR2 PDB: sdspdb2
18c CDB: cdb181

Pre-upgrade steps

Use the preupgrade.jar to check current db status and create pre- and post- upgrade fix scripts.
Run the preupgrade_fixups.sql script

[oracle@sdsserver ~]$ . oraenv
ORACLE_SID = [] ? sdscdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@sdsserver ~]$ /u01/app/oracle/product/12.2.0/db_home1/jdk/bin/java -jar /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/preupgrade.jar dir /tmp -c sdspdb2
==================
PREUPGRADE SUMMARY
==================
  /tmp/preupgrade.log
  /tmp/preupgrade_fixups.sql
  /tmp/postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/ -b preup_sdscdb2 /tmp/preupgrade_fixups.sql

2. Review logs under /tmp/

After the upgrade:

1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/ -b postup_sdscdb2 /tmp/postupgrade_fixups.sql

2. Review logs under /tmp/

Preupgrade complete: 2018-07-28T23:01:31
[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/ -b preup_sdscdb2 /tmp/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/tmp//preup_sdscdb2_catcon_30175.lst]
catcon: See [/tmp//preup_sdscdb2*.log] files for output generated by scripts
catcon: See [/tmp//preup_sdscdb2_*.lst] files for spool files, if any
catcon.pl: completed successfully

Unplug from old release

Shutdown the pdb.
Unplug the pdb, creating an xml descriptor file.
Drop the pdb from the old container.

[oracle@sdsserver ~]$ sqlplus sys@sdscdb2 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jul 28 23:26:59 2018

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter pluggable database sdspdb2 close immediate;

Pluggable database altered.

SQL> alter pluggable database sdspdb2 unplug into '/tmp/sdspdb2.xml';

Pluggable database altered.

SQL> drop pluggable database sdspdb2 keep datafiles;

Pluggable database dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Plug into new release and open for upgrade

Use the xml descriptor to plug into new 18c cdb.
I used “nocopy” option, no need to move files around or copy them, so much faster.
Then open the pdb in upgrade mode

[oracle@sdsserver ~]$ . oraenv
ORACLE_SID = [sdscdb2] ? cdb181
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@sdsserver ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jul 28 23:29:57 2018
Version 18.3.0.0.0

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


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create pluggable database sdspdb2 using '/tmp/sdspdb2.xml' nocopy;

Pluggable database created.

SQL> alter session set container=sdspdb2;

Session altered.

SQL> alter pluggable database open upgrade;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Upgrade pdb

Run catupgrd.sql script to complete the transition.

[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -c 'sdspdb2' -l $ORACLE_BASE catupgrd.sql

Argument list for [/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl]
Run in                c = sdspdb2
Do not run in         C = 0
Input Directory       d = /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = 0
Child Process         I = 0
Log Dir               l = /u01/app/oracle
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 0
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [18.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]


/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/dbhome_1]
/u01/app/oracle/product/18.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/app/oracle]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/catupgrd_catcon_12454.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrd_*.lst] files for spool files, if any


Number of Cpus        = 8
Database Name         = cdb18_1
DataBase Version      = 18.0.0.0.0
Parallel SQL Process Count (PDB)      = 2
Parallel SQL Process Count (CDB$ROOT) = 8
Concurrent PDB Upgrades               = 4
Generated PDB Inclusion:[SDSPDB2]
CDB$ROOT  Open Mode = [OPEN]

Start processing of PDBs (SDSPDB2)
[/u01/app/oracle/product/18.0.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl -d /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin -c 'SDSPDB2' -l /u01/app/oracle -I -i sdspdb2 -n 2 catupgrd.sql]

Argument list for [/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catctl.pl]
Run in                c = SDSPDB2
Do not run in         C = 0
Input Directory       d = /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin
Echo OFF              e = 1
Simulate              E = 0
Forced cleanup        F = 0
Log Id                i = sdspdb2
Child Process         I = 1
Log Dir               l = /u01/app/oracle
Priority List Name    L = 0
Upgrade Mode active   M = 0
SQL Process Count     n = 2
SQL PDB Process Count N = 0
Open Mode Normal      o = 0
Start Phase           p = 0
End Phase             P = 0
Reverse Order         r = 0
AutoUpgrade Resume    R = 0
Script                s = 0
Serial Run            S = 0
RO User Tablespaces   T = 0
Display Phases        y = 0
Debug catcon.pm       z = 0
Debug catctl.pl       Z = 0

catctl.pl VERSION: [18.0.0.0.0]
           STATUS: [Production]
            BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627]


/u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/dbhome_1]
/u01/app/oracle/product/18.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/dbhome_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/app/oracle]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/catupgrdsdspdb2_catcon_12742.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrdsdspdb2*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/catupgrdsdspdb2_*.lst] files for spool files, if any


Number of Cpus        = 8
Database Name         = cdb18_1
DataBase Version      = 18.0.0.0.0
Generated PDB Inclusion:[SDSPDB2]
CDB$ROOT  Open Mode = [OPEN]
Components in [SDSPDB2]
    Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-108]         Start Time:[2018_07_29 00:25:33]
Container Lists Inclusion:[SDSPDB2] Exclusion:[NONE]
------------------------------------------------------
***********   Executing Change Scripts   ***********
Serial   Phase #:0    [SDSPDB2] Files:1    Time: 271s
***************   Catalog Core SQL   ***************
Serial   Phase #:1    [SDSPDB2] Files:5    Time: 1197s
Restart  Phase #:2    [SDSPDB2] Files:1    Time: 0s
***********   Catalog Tables and Views   ***********
Parallel Phase #:3    [SDSPDB2] Files:19   Time: 484s
Restart  Phase #:4    [SDSPDB2] Files:1    Time: 0s
*************   Catalog Final Scripts   ************
Serial   Phase #:5    [SDSPDB2] Files:7    Time: 509s
*****************   Catproc Start   ****************
Serial   Phase #:6    [SDSPDB2] Files:1    Time: 188s
*****************   Catproc Types   ****************
Serial   Phase #:7    [SDSPDB2] Files:2    Time: 198s
Restart  Phase #:8    [SDSPDB2] Files:1    Time: 0s
****************   Catproc Tables   ****************
Parallel Phase #:9    [SDSPDB2] Files:66   Time: 507s
Restart  Phase #:10   [SDSPDB2] Files:1    Time: 0s
*************   Catproc Package Specs   ************
Serial   Phase #:11   [SDSPDB2] Files:1    Time: 1110s
Restart  Phase #:12   [SDSPDB2] Files:1    Time: 0s
**************   Catproc Procedures   **************
Parallel Phase #:13   [SDSPDB2] Files:94   Time: 79s
Restart  Phase #:14   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:15   [SDSPDB2] Files:117  Time: 128s
Restart  Phase #:16   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:17   [SDSPDB2] Files:17   Time: 32s
Restart  Phase #:18   [SDSPDB2] Files:1    Time: 1s
*****************   Catproc Views   ****************
Parallel Phase #:19   [SDSPDB2] Files:32   Time: 267s
Restart  Phase #:20   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:21   [SDSPDB2] Files:3    Time: 87s
Restart  Phase #:22   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:23   [SDSPDB2] Files:24   Time: 604s
Restart  Phase #:24   [SDSPDB2] Files:1    Time: 1s
Parallel Phase #:25   [SDSPDB2] Files:12   Time: 314s
Restart  Phase #:26   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:27   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:28   [SDSPDB2] Files:3    Time: 39s
Serial   Phase #:29   [SDSPDB2] Files:1    Time: 0s
Restart  Phase #:30   [SDSPDB2] Files:1    Time: 1s
***************   Catproc CDB Views   **************
Serial   Phase #:31   [SDSPDB2] Files:1    Time: 2s
Restart  Phase #:32   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:34   [SDSPDB2] Files:1    Time: 0s
*****************   Catproc PLBs   *****************
Serial   Phase #:35   [SDSPDB2] Files:288  Time: 224s
Serial   Phase #:36   [SDSPDB2] Files:1    Time: 0s
Restart  Phase #:37   [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:38   [SDSPDB2] Files:2    Time: 18s
Restart  Phase #:39   [SDSPDB2] Files:1    Time: 0s
***************   Catproc DataPump   ***************
Serial   Phase #:40   [SDSPDB2] Files:3    Time: 146s
Restart  Phase #:41   [SDSPDB2] Files:1    Time: 0s
******************   Catproc SQL   *****************
Parallel Phase #:42   [SDSPDB2] Files:13   Time: 274s
Restart  Phase #:43   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:44   [SDSPDB2] Files:11   Time: 55s
Restart  Phase #:45   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:46   [SDSPDB2] Files:3    Time: 4s
Restart  Phase #:47   [SDSPDB2] Files:1    Time: 0s
*************   Final Catproc scripts   ************
Serial   Phase #:48   [SDSPDB2] Files:1    Time: 125s
Restart  Phase #:49   [SDSPDB2] Files:1    Time: 1s
**************   Final RDBMS scripts   *************
Serial   Phase #:50   [SDSPDB2] Files:1    Time: 8s
************   Upgrade Component Start   ***********
Serial   Phase #:51   [SDSPDB2] Files:1    Time: 0s
Restart  Phase #:52   [SDSPDB2] Files:1    Time: 1s
**********   Upgrading Java and non-Java   *********
Serial   Phase #:53   [SDSPDB2] Files:2    Time: 1636s
*****************   Upgrading XDB   ****************
Restart  Phase #:54   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:56   [SDSPDB2] Files:3    Time: 10s
Serial   Phase #:57   [SDSPDB2] Files:3    Time: 24s
Parallel Phase #:58   [SDSPDB2] Files:9    Time: 11s
Parallel Phase #:59   [SDSPDB2] Files:25   Time: 34s
Serial   Phase #:60   [SDSPDB2] Files:4    Time: 85s
Serial   Phase #:61   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:62   [SDSPDB2] Files:31   Time: 33s
Serial   Phase #:63   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:64   [SDSPDB2] Files:6    Time: 11s
Serial   Phase #:65   [SDSPDB2] Files:2    Time: 21s
Serial   Phase #:66   [SDSPDB2] Files:3    Time: 73s
****************   Upgrading ORDIM   ***************
Restart  Phase #:67   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:69   [SDSPDB2] Files:1    Time: 1s
Parallel Phase #:70   [SDSPDB2] Files:2    Time: 84s
Serial   Phase #:71   [SDSPDB2] Files:1    Time: 76s
Restart  Phase #:72   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:73   [SDSPDB2] Files:2    Time: 14s
Serial   Phase #:74   [SDSPDB2] Files:2    Time: 1s
*****************   Upgrading SDO   ****************
Restart  Phase #:75   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:77   [SDSPDB2] Files:1    Time: 76s
Serial   Phase #:78   [SDSPDB2] Files:1    Time: 47s
Restart  Phase #:79   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:80   [SDSPDB2] Files:1    Time: 52s
Restart  Phase #:81   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:82   [SDSPDB2] Files:3    Time: 239s
Restart  Phase #:83   [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:84   [SDSPDB2] Files:1    Time: 14s
Restart  Phase #:85   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:86   [SDSPDB2] Files:1    Time: 32s
Restart  Phase #:87   [SDSPDB2] Files:1    Time: 0s
Parallel Phase #:88   [SDSPDB2] Files:4    Time: 174s
Restart  Phase #:89   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:90   [SDSPDB2] Files:1    Time: 1s
Restart  Phase #:91   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:92   [SDSPDB2] Files:2    Time: 11s
Restart  Phase #:93   [SDSPDB2] Files:1    Time: 0s
Serial   Phase #:94   [SDSPDB2] Files:1    Time: 1s
Restart  Phase #:95   [SDSPDB2] Files:1    Time: 0s
*******   Upgrading ODM, WK, EXF, RUL, XOQ   *******
Serial   Phase #:96   [SDSPDB2] Files:1    Time: 86s
Restart  Phase #:97   [SDSPDB2] Files:1    Time: 0s
***********   Final Component scripts    ***********
Serial   Phase #:98   [SDSPDB2] Files:1    Time: 5s
*************   Final Upgrade scripts   ************
Serial   Phase #:99   [SDSPDB2] Files:1    Time: 779s
*******************   Migration   ******************
Serial   Phase #:100  [SDSPDB2] Files:1    Time: 1s
***   End PDB Application Upgrade Pre-Shutdown   ***
Serial   Phase #:101  [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:102  [SDSPDB2] Files:1    Time: 9s
Serial   Phase #:103  [SDSPDB2] Files:1    Time: 59s
*****************   Post Upgrade   *****************
Serial   Phase #:104  [SDSPDB2] Files:1    Time: 13s
****************   Summary report   ****************
Serial   Phase #:105  [SDSPDB2] Files:1    Time: 3s
***   End PDB Application Upgrade Post-Shutdown   **
Serial   Phase #:106  [SDSPDB2] Files:1    Time: 1s
Serial   Phase #:107  [SDSPDB2] Files:1    Time: 7s
Serial   Phase #:108  [SDSPDB2] Files:1     Time: 0s

------------------------------------------------------
Phases [0-108]         End Time:[2018_07_29 03:22:15]
Container Lists Inclusion:[SDSPDB2] Exclusion:[NONE]
------------------------------------------------------

Grand Total Time: 10603s [SDSPDB2]

 LOG FILES: (/u01/app/oracle/catupgrdsdspdb2*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/upg_summary.log

Total Upgrade Time:          [0d:2h:56m:43s]

     Time: 10608s For PDB(s)

Grand Total Time: 10608s

 LOG FILES: (/u01/app/oracle/catupgrd*.log)


Grand Total Upgrade Time:    [0d:2h:56m:48s]

Start upgraded pdb

Upgrade is complete, start the pdb

[oracle@sdsserver ~]$ sqlplus sys@sdspdb2 as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jul 29 10:29:45 2018
Version 18.3.0.0.0

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

Enter password:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> startup
Pluggable Database opened.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Run Post-upgrade script

Run the postupgrade_fixups.sql script generated in the first step.
Run utlrp to recompile invalid objects.

[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -c 'sdspdb2' -n 1 -e -b postfixups -d '''.''' /tmp/postupgrade_fixups.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/postfixups_catcon_2676.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/postfixups*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/postfixups_*.lst] files for spool files, if any

catcon.pl: completed successfully
[oracle@sdsserver ~]$ $ORACLE_HOME/perl/bin/perl /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/catcon.pl -c 'sdspdb2' -n 1 -e -b comp -d '''.''' /u01/app/oracle/product/18.0.0/dbhome_1/rdbms/admin/utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/comp_catcon_3476.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/comp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/comp_*.lst] files for spool files, if any

catcon.pl: completed successfully

And that’s it. The upgrade duration was longer than I would have hoped for but the manual steps were minimal and easy.

For Oracle documentation on these steps:
https://docs.oracle.com/en/database/oracle/oracle-database/18/upgrd/manual-upgrade-scenarios-multitenant-architecture-oracle-databases.html#GUID-8F9AAFA1-690D-4F70-8448-E66D765AF136

18.3 available for on-premises download


woohoo finally!

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/oracle18c-linux-180000-5022980.html

BACKUP_TYPE changes with 12c RMAN


I was working on a report from our RMAN catalog and found I was having trouble reconciling the data I found in RC_BACKUP_SET with what I was expecting to see. Furthermore I couldn’t find anything in the Reference Guide to adequately explain what I was seeing.

In particular I’ll reference the descriptions of the BACKUP_TYPE and INCREMENTAL_LEVEL columns.

BACKUP_TYPE VARCHAR2(1) The type of the backup: D (full backup or level 0 incremental), I (incremental level 1), L (archived redo log).
INCREMENTAL_LEVEL NUMBER The level of the incremental backup: NULL, 0, or 1.

Seems pretty simple, but below I have copied an excerpt of recent backup data for a single small db. Note the two highlighted rows.
 

BACKUP_TYPE INCREMENTAL_LEVEL START_TIME CONTROLFILE_INCLUDED
L   2018-07-19 15:40:18 NONE
D   2018-07-19 15:40:21 BACKUP
D
0
2018-07-19 16:20:49 NONE
D   2018-07-19 16:21:06 BACKUP
L   2018-07-19 16:40:12 NONE
D   2018-07-19 16:40:15 BACKUP
D   2018-07-19 19:15:20 NONE
D   2018-07-19 19:15:49 BACKUP
L   2018-07-19 19:45:11 NONE
L   2018-07-19 19:45:12 NONE
D   2018-07-19 19:45:44 BACKUP
I
0
2018-07-19 19:52:42 NONE
D   2018-07-19 19:52:58 BACKUP

I know what a “Full Backup” is and I know what a “Level-0 Incremental Backup” is. From the documentation I would expect a “Full Backup” to have a backup type of “D” and an incremental level of NULL. However, at 4:20pm, my backup showed up as a “D” but with an incremental level of 0. What does that mean?

And why just a few hours later do I have another Level-0 backup, but this time instead of “full (D)” it’s “incremental (I)?” The data doesn’t seem to be consistent.

After some searches via Google and some searching on MOS, I found an explanation.

It turns out the database was upgraded from 11g to 12cR2 that day. So, at 4:20 another DBA took a backup before beginning. Then a few hours later after the upgrade was complete, the DBA took another backup. In both cases, the DBA took a Level-0 Incremental backup. The difference in the backup data is not just in the RMAN catalog RC_BACKUP_SET but also in the database’s own local V$BACKUP_SET data.
And the cause is due to changes in RMAN itself with 12c.

In older versions of the database “D” meant an offline backup, or the backup of a controlfile or an spfile. But, strangely, “D” was also used for an Incremental backup if the increment level was 0. So “I” type was only used for Incremental Level-1 backups.

Now that the database is 12c, when the Incremental Level-0 backups occur, they are recorded with the “I” type instead of the 11g “D” type.

So there is both a functional change as well as a documentation quirk to be aware of. That little change in the flag, without any obvious change in the documentation caused me quite a bit of confusion. Hopefully this will clear up the problem for others.

For more information on the RMAN change, see MOS Doc ID 2219155.1

%d bloggers like this: