At the time of writing Oracle’s Cloud Infrastructure as a Service (IaaS) offers 2 ways to connect block storage to virtual machines: paravirtualised and via iSCSI. There are important differences between the two so please read the documentation to understand all the implications. I need all the performance I can get with my systems so I’m going with iSCSI.

It’s the little differences

Using the paravirtualised driver couldn’t be easier: you boot the VM, and all block devices are automatically attached and available. When using iSCSI you need to run a few iscsiadm commands (once) to discover and mount the remote storage. These commands are available on the click of a button in the GUI. It’s been ages that I used the GUI and I prefer a scripted approach to cloud infrastructure. My tool of choice when it comes to “infrastructure as code” is terraform. 

Until fairly recently I have made use of the null provider combined with a remote-exec provisioner in my terraform scripts. The combination allows me to execute the iscsiadm commands necessary to attach the iSCSI devices to the VM. A number of enhancements in this space allowed me to ditch the rather cumbersome remote-exec step and use cloud-init combined with OCI utilities instead. As I hope to show you, using these two combined make the management of iSCSI device just as simple as the paravirtualised ones.

Cloud Init

When creating VMs I often need to perform a few extra steps that don’t quite justify the creation of a custom image. The cloud-init toolkit in OCI allows me to pass a shell script as “user_data” to the instance’s metadata, provided it’s encoded in base64. Have a look at the documentation I just referenced for more details about restrictions etc. In my terraform script, I use something like this:

resource "oci_core_instance" "docker_tf_instance" {
[...]
   
    metadata {
        ssh_authorized_keys = "${var.ssh_public_key}"
        user_data = "${base64encode(file("bootstrap.sh"))}"
    }

[...]
}

Most examples I found specify the input to the file() function as a variable, I didn’t do this in this post for the sake of simplicity. The script I’m passing as user_data makes use of the OCI utilities.

OCI Utilities

I wasn’t aware of these until my colleague Syed asked me why I didn’t use them. It couldn’t be easier: just install a RPM package and start a service. This will take care of the iSCSI part for you. The only caveat is that currently they can only be used for Oracle provided images based on Oracle Linux. Here is a really basic example of a shell script calling the OCI utilities:

$ cat bootstrap.sh 
#!/bin/bash

cp /etc/motd /etc/motd.bkp
cat << EOF > /etc/motd

I have been modified by cloud-init at $(date)

EOF

yum install -y python-oci-cli
systemctl enable ocid.service
systemctl start ocid.service
systemctl status ocid.service

The first line has to start with #!/bin/bash to indicate to cloud-init that you want to run a shell script. Following the instructions for using OCI utilities, I am installing the python-oci-cli and start the ocid.service. This in turn will perform the iSCSI volume attachment for me – super nice! After my terraform script completed, I can log in to see if this worked:

[root@docker-tf-instance ~]# lsblk
NAME   MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sdb      8:16   0   50G  0 disk 
sda      8:0    0 46.6G  0 disk 
├─sda2   8:2    0    8G  0 part [SWAP]
├─sda3   8:3    0 38.4G  0 part /
└─sda1   8:1    0  200M  0 part /boot/efi
[root@docker-tf-instance ~]# 

[root@docker-tf-instance ~]# systemctl status ocid.service
● ocid.service - Oracle Cloud Infrastructure utilities daemon
   Loaded: loaded (/etc/systemd/system/ocid.service; enabled; vendor preset: enabled)
   Active: active (running) since Tue 2018-11-27 19:52:45 GMT; 19min ago
 Main PID: 15138 (python2.7)
   CGroup: /system.slice/ocid.service
           └─15138 python2.7 /usr/libexec/ocid

Nov 27 19:52:04 docker-tf-instance python2.7[15138]: ocid - INFO - Starting ocid thread 'iscsi'
Nov 27 19:52:04 docker-tf-instance python2.7[15138]: ocid - INFO - Starting ocid thread 'vnic'
...
Nov 27 19:52:09 docker-tf-instance python2.7[15138]: ocid - INFO - secondary VNIC script reports: Info: no changes, IP configuration is up-to-date
Nov 27 19:52:44 docker-tf-instance python2.7[15138]: ocid - INFO - Attaching iscsi device: 169.254.a.b:3260 (iqn.2015-12.com.oracleiaas:e1af1...)
Nov 27 19:52:45 docker-tf-instance systemd[1]: Started Oracle Cloud Infrastructure utilities daemon.

You can see cloud-init in action by checking /var/log/messages for occurrences of “cloud-init”. The file /var/log/cloud-init.log doesn’t contain information relevant to the “user-data” processed by the way. If you want to see how your script arrived on the VM, check /var/lib/cloud/instance/user-data.txt.

Summary

It would seem you can have the cake and eat it. Using cloud-init for bootstrapping my VM and OCI utilities to attach my block devices I don’t need to write any remote-exec hacks using the null provider and use the iSCSI volumes with the same ease of use as the paravirtualised ones. Without having to make compromises. I like it!

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?

OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick

I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming ) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.

SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') x from dual

X
-------------------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
 (    "X" NUMBER(*,0)
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (  
  BUFFER_POOL DEFAULT 
  FLASH_CACHE DEFAULT 
  CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO"
PARTITION BY LIST ("X")
(PARTITION "P1"  VALUES (1) 
   SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   STORAGE (
     INITIAL 8388608 
     NEXT 1048576 
     MINEXTENTS 1 
     MAXEXTENTS 2147483645  
     PCTINCREASE 0 
     FREELISTS 1 
     FREELIST GROUPS 1
     BUFFER_POOL DEFAULT 
     FLASH_CACHE DEFAULT 
     CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ,
 PARTITION "P2"  VALUES (2) 
   SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   STORAGE (
     INITIAL 8388608 
     NEXT 1048576 
     MINEXTENTS 1 
     MAXEXTENTS 2147483645  
     PCTINCREASE 0 
     FREELISTS 1 
     FREELIST GROUPS 1
     BUFFER_POOL DEFAULT 
     FLASH_CACHE DEFAULT 
     CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "LARGETS" )

With a little colour coding, you can see that there are three tablespaces that pertain to this table:

• DEMO
• USERS
• LARGETS

But look what happens when I query the data dictionary for those tablespaces:

SQL> select tablespace_name
  2  from   dba_tablespaces
  3  where  tablespace_name in ('DEMO','USERS','LARGETS');

TABLESPACE_NAME
------------------------
LARGETS
USERS

Where is DEMO? Where has it gone? More startlingly, how can I have an existing table that needs that tablespace, and yet the tablespace is not present in the database? Have I lost data? Is there corruption?

Fortunately, the answer to all of these questions do not involve data loss and/or corruption. It is a quirk of the syntax that can be used for partitioned tables. Here is the DDL as I wrote it for the table T.

SQL> create table t ( x int ) tablespace demo
  2  partition by list ( x )
  3  ( partition p1 values (1) tablespace users,
  4    partition p2 values (2) tablespace largets
  5  );

Table created.

And immediately after I created the table, I did the following

SQL> drop tablespace demo including contents and datafiles;

Tablespace dropped.

You might be thinking that such an operation would surely drop the table I just created as well, but it is still here just fine.

SQL> desc t
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------
 X                                                                          NUMBER(38)

The specification of the tablespace at table level for a partitioned table is nominating the default tablespace for each partition in case it is not specified at partition level. Because I specified a tablespace explicitly for the two partitions on T, the tablespace DEMO does not contain any data, or any partitions for that matter. Which is why I was able to drop it without any problems. Compounding the confusion that often arises is that you won’t see the tablespace name DEMO listed in the USER_TABLES dictionary view even if I had not dropped the tablespace.

SQL> select tablespace_name from user_tables
  2  where table_name = 'T';

TABLESPACE_NAME
------------------------
(null)

The tablespace name in USER_TABLES nominates the tablespace for the segment that will be associated with this table. You will see a similar null value in this column when the table is an Index Organized Table, because it is the underlying index that maps to a tablespace, not the table definition. For a partitioned table, to see which tablespace is the default tablespace, you need to query the USER_PART_TABLES dictionary view.

SQL> select def_tablespace_name
  2  from user_part_tables
  3  where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------
DEMO

Besides this being some trickery with tablespace definitions, there is a good reason why you should know about the segments tablespace and the default tablespace for partitioned objects. As it stands, I could run a datapump export command on the table T and it will successfully be unloaded to a data pump file. However, if I attempt to run a data pump import, the creation of the table will fail, because of the (now illegal) reference to the DEMO tablespace.

So if you are planning to run a data pump export, here’s a query I whipped up to run a check against your database to ensure that you don’t have any references to tablespaces that no longer exist in your database.

SQL> with all_possible_ts as
  2  (
  3  select tablespace_name from dba_lobs                       union all
  4  select tablespace_name from dba_clusters                   union all
  5  select tablespace_name from dba_indexes                    union all
  6  select tablespace_name from dba_rollback_segs              union all
  7  select tablespace_name from dba_tables                     union all
  8  select tablespace_name from dba_object_tables              union all
  9  select def_tablespace_name from dba_part_tables            union all
 10  select def_tablespace_name from dba_part_indexes           union all
 11  select tablespace_name from dba_tab_partitions             union all
 12  select tablespace_name from dba_ind_partitions             union all
 13  select tablespace_name from dba_tab_subpartitions          union all
 14  select tablespace_name from dba_ind_subpartitions          union all
 15  select def_tablespace_name from dba_part_lobs              union all
 16  select tablespace_name from dba_lob_partitions             union all
 17  select tablespace_name from dba_lob_subpartitions          union all
 18  select tablespace_name from dba_subpartition_templates     union all
 19  select tablespace_name from dba_lob_templates              union all
 20  select tablespace_name from dba_segments                   union all
 21  select tablespace_name from dba_extents                    union all
 22  select tablespace_name from dba_undo_extents
 23  )
 24  select tablespace_name from all_possible_ts
 25  minus
 26  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
--------------------
DEMO

If you are using an Oracle Database Enterprise Edition chances are that there is at least one environment in your estate making use of Data Guard. And if you are using Data Guard, why not use the broker? I have been using Data Guard broker for a long time now, and it has definitely improved a lot over the first releases, back in the day. I like it so much these days that I feel hard done by if I can’t make use of it. This is of course a matter of personal preference, and I might be exaggerating a little :)

One of the nice additions to the broker in Oracle 12.1 was the ability to validate a database before a role change. This is documented in the Data Guard broker documentation. I certainly don’t solely rely on the output of the command, I have my own checks I’m running that go over and above what a validate can do.

During research I ran the command on my 12.2 system and noticed much enhanced output. This is super exciting and worth blogging about. Well, that is, to me at least-I hope you find this post useful. I love discovering little changes like this, they aren’t always advertised on slide 1 in “What’s new in release X” presentations, but nevertheless great boosts to productivity.

This post was nearly complete, but then rested in my drafts folder for just a little too long and 18c has been released. Time for an update!

The environment

Before moving on, here’s the stack in case you find this via a search engine:

• Oracle Linux 7.4 powering 2 VMs: server1 and server2
• Oracle 18.3.0, single instance, no Oracle Restart
• Data Guard Broker configuration managing 2 databases: NCDBA and NCDBB

The broker is quite happy with my setup, at least for now.

DGMGRL> show configuration

Configuration - test

  Protection Mode: MaxAvailability
  Members:
  NCDBA - Primary database
    NCDBB - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 51 seconds ago)

DGMGRL> 

This is my setup, YMMV as always. Refer to your standard documents or other relevant documentation for more details about your configuration

New things to validate in 18c

With 12.2 it was possible to validate a datafile in addition to validating the database. Oracle 18c enhances the validate command quite a bit more:

DGMGRL> help validate

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] ;

  VALIDATE DATABASE [VERBOSE]  DATAFILE  
    OUTPUT=;

  VALIDATE DATABASE [VERBOSE]  SPFILE;

  VALIDATE FAR_SYNC [VERBOSE]  
    [WHEN PRIMARY IS ];

  VALIDATE NETWORK CONFIGURATION FOR { ALL |  };

  VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL |  };

DGMGRL> 

In this post I am going to focus on the verbose output generated by validate database, if I can find the time I’ll write about the other new options as well.

Validate database in Oracle 18c

Let’s have a look at the output of validate database verbose … Looking at the configuation status, NCDBB is currently running in recovery mode with NCDBA acting as the primary database. It makes sense to start with the validation of NCDBB first.

The output of the command is rather comprehensive as you will see, I have decided to annotate the output so you don’t have to scroll up and down that much.

DGMGRL> validate database verbose 'NCDBB'

  Database Role:     Physical standby database
  Primary Database:  NCDBA

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    NCDBA :  Off
    NCDBB :  Off

  Capacity Information:
    Database  Instances        Threads
    NCDBA      1               1
    NCDBB      1               1

  Managed by Clusterware:
    NCDBA :  NO
    NCDBB:  NO
    Validating static connect identifier for database NCDBA...
    The static connect identifier allows for a connection to database "NCDBA".

The first part of the output is related to the database’s role and status. Oracle reckons my standby database is ready for a role change (which I’ll double-check using my own tools and scripts). You can see that flashback database is not enabled (for reasons that don’t matter for this post).

Since both members are single instance databases it makes sense for them to have a single redo thread.

Another important piece of information can be found in the “managed by Clusterware” section. In releases prior to 12.1.0.2 you always had to statically register your databases with the listener for use with the broker. This has changed in 12.1.0.2: another one of these productivity boosters :) In modern releases you don’t need to statically register your databases with the listener provided Clusterware manages them. See MOS DocID 1387859.1 for all the details.

You read in the introduction that I’m using single instance Oracle databases without any Grid Infrastructure at all so this shiny new feature does not apply. There’s something else that might help though: Oracle validates the static connection identifier for you. It doesn’t simply ping the tns alias, the broker actually establishes a connection to the database. I noticed this in the listener.log: a program named dgmgrl connects to the database using the static conncetion identifier (as per “show database memberName staticconnectidentifier”). Output is formatted for readability:

2018-08-14 10:54:16.377000 +01:00
14-AUG-2018 10:54:16 * (CONNECT_DATA=(SERVICE_NAME=NCDBB_DGMGRL)(INSTANCE_NAME=NCDBB)(SERVER=DEDICATED)
(STATIC_SERVICE=TRUE)(CID=(PROGRAM=dgmgrl)(HOST=server1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)
(HOST=192.168.100.21)(PORT=27049)) * establish * NCDBB_DGMGRL * 0

Let’s continue with the output of the validate database command:

  Temporary Tablespace File Information:
    NCDBA TEMP Files:   1
    NCDBB TEMP Files:   1

  Data file Online Move in Progress:
    NCDBA:  No
    NCDBB:  No

This little section compares the number of temp files and warns you of any online data file move operations.

  Standby Apply-Related Information:
    Apply State:      Running
    Apply Lag:        0 seconds (computed 0 seconds ago)
    Apply Delay:      0 minutes

  Transport-Related Information:
    Transport On:      Yes
    Gap Status:        No Gap
    Transport Lag:     0 seconds (computed 0 seconds ago)
    Transport Status:  Success


  Log Files Cleared:
    NCDBA Standby Redo Log Files:  Cleared
    NCDBB Online Redo Log Files:   Not Cleared
    NCDBB Standby Redo Log Files:  Available

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (NCDBA)                 (NCDBB)
    1         2                       3                       Sufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (NCDBB)                 (NCDBA)
    1         2                       3                       Sufficient SRLs

  Current Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (NCDBA)                    (NCDBB)
    1          200 MBytes                200 MBytes

  Future Configuration Log File Sizes:
    Thread #   Smallest Online Redo      Smallest Standby Redo
               Log File Size             Log File Size
               (NCDBB)                   (NCDBA)
    1          200 MBytes                200 MBytes

This section is quite interesting as well as it allows you to have a gander at the transport lag and apply lag respectively. I can also see that I have standby redo logs both for my primary as well as the standby database. Sometimes the broker doesn’t get the number of standby redo logs right in “future log file groups configuration”. I found this to rectify itself after a switchover and switch-back.
Another reassuring fact is presented in the current and future configuration log file sizes: I follwed th documentation carefully and created all my (online and standby) redo logs the exact same size.

  Apply-Related Property Settings:
    Property                        NCDBA Value              NCDBB Value
    DelayMins                       0                        0
    ApplyParallel                   AUTO                     AUTO
    ApplyInstances                  0                        0

  Transport-Related Property Settings:
    Property                        NCDBA Value              NCDBB Value
    LogXptMode                      sync                     sync
    Dependency                                        
    DelayMins                       0                        0
    Binding                         optional                 optional
    MaxFailure                      0                        0
    MaxConnections                  1                        1
    ReopenSecs                      300                      300
    NetTimeout                      30                       30
    RedoCompression                 DISABLE                  DISABLE
    LogShipping                     ON                       ON

The section just above compares apply and transport related settings between the primary and standby databases. These are stored in Data Guard broker properties eventually mapping back to database initialisation parameters.

  Automatic Diagnostic Repository Errors:
    Error                       NCDBA    NCDBB
    No logging operation        NO       NO
    Control file corruptions    NO       NO
    SRL Group Unavailable       NO       NO
    System data file missing    NO       NO
    System data file corrupted  NO       NO
    System data file offline    NO       NO
    User data file missing      NO       NO
    User data file corrupted    NO       NO
    User data file offline      NO       NO
    Block Corruptions found     NO       NO

DGMGRL> 

And finally, it appears as if the Automatic Diagnostic Repository (ADR) didn’t show any issues. This doesn’t mean there aren’t, maybe there is still something looming in your configuration that hasn’t been detected yet.

Summary

The validate database command is really quite useful in my opinion performing basic checks before role changes. It doesn’t take the responsibility off you for ensuring the role change will be a success but I found it to be a useful first indicator.

Here’s a little procedure I’ve been using since Oracle 8i to dump the contents of the current log file – I’ve mentioned it several times in the past but never published it, so I’ll be checking for references to it and linking to it.

The code hasn’t changed in a long time, although I did add a query to get the full tracefile name from v$process when that became available. There’s also an (optional) called to dbms_support.my_sid to pick up the SID of the current session that slid into the code when that package became available.

rem
rem     Script:         c_dump_log.sql
rem     Author:         Jonathan Lewis
rem     Dated:          December 2002
rem     Purpose:        Create procedured to dump the current online redo log file.
rem
rem     Last tested
rem             18.3.0.0
rem             12.2.0.1
rem             11.1.0.7
rem             11.2.0.6
rem             10.2.0.5
rem             10.1.0.4
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Notes:
rem     Must be run as a DBA
rem     Very simple minded - no error trapping
rem     

create or replace procedure dump_log
as
        m_log_name      varchar2(255);
        m_process       varchar2(255);
        m_trace_name    varchar2(255);

begin
        select 
                lf.member
        into
                m_log_name
        from
                V$log           lo,
                v$logfile       lf
        where 
                lo.status = 'CURRENT'
        and     lf.group# = lo.group#
        and     rownum = 1
        ;

        execute immediate
        'alter system dump logfile ''' || m_log_name || '''';

        select
                spid
        into
                m_process
        from
                v$session       se,
                v$process       pr
        where
                se.sid = --dbms_support.mysid
                        (select sid from v$mystat where rownum = 1)
        and     pr.addr = se.paddr
        ;

        select
                tracefile
        into
                m_trace_name
        from
                v$session       se,
                v$process       pr
        where
                se.sid = --dbms_support.mysid
                        (select sid from v$mystat where rownum = 1)
        and     pr.addr = se.paddr
        ;

        dbms_output.put_line('Trace file is: ' || m_trace_name);
        dbms_output.put_line('Log file name is: ' || m_log_name);
        dbms_output.put_line('Trace file name includes: ' || m_process);


end;
/

show errors

drop public synonym dump_log;
create public synonym dump_log for dump_log;
grant execute on dump_log to public;

I don’t use the package often but if I want to find out what redo is generated during a test I usually follow the sequence:

• alter system switch logfile;
• do the experiment
• execute dump_log

If you’re running in a PDB there’s an extra step needed as you can’t “switch logfile” inside a PDB so I’ll either do a log file switch before I start the test or (if there are steps in the test script that could generate a lot of log file I don’t want to see) I include a “pause” in the test script and use another session to do the logfile switch – in both cases the second session has to be connected to the CDB.

You will have noticed the creation of the public synonym and granting of the execute privilege to public. In my own sandbox database that’s a convenience – you may want to be a little more protective in your development and test systems.

The “dump logfile” command has a number of options for selective dumping – I have a note in my file commenting on these options, but I haven’t checked if there are any new ones (or changes to existing ones) for a long time:

alter system dump logfile '{filename}'
        scn min {first SCN to dump}
        scn max {last SCN to dump}
        time min {seconds since midnight at the end of 1st Sept 1987}
        time max {see redo_time_calc.sql}
        layer {integer} opcode {integer} e.g.:
                layer 23        Block Written Records
                layer 5         Undo handling in general
                layer 5 opcode 4        Undo Seg header on commit; or rollback;
                layer 9999 opcode 9999  Trick to validate the whole log file structure
        xid {usn} {slot} {sequence}     -- 10g only, may break on IMU redo (see below)
        objno {object_id}               -- 10g only, may break on IMU redo (see below)
        dba min {datafile no} . {blockno} -- with spaces either side of the dot.
        dba max {datafile no} . {blockno} -- with spaces either side of the dot.
        rba min {log file seq no} . {blockno} -- with spaces either side of the dot.
        rba max {log file seq no} . {blockno} -- with spaces either side of the dot..
(The dots in the last four options becomes invalid syntax in 10g).

The introduction to this note references back to a presentation I did in the year 2000, but the closing comment suggests that I probably haven’t checked the list since some time in the 10g timeline.

The reference to redo_time_calc.sql points to the following script, that expresses the time as the number of seconds since Jan 1988, with the unfortunate simplification that Oracle thinks there are 31 days in every month of the year:

rem
rem     Script:         redo_time_calc3.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2012
rem     Purpose:
rem

select 
        86400 * (
                31 *
                        months_between(
                                trunc(sysdate,'MM'),
                                to_date('01-Jan-1988','dd-mon-yyyy')
                        ) +
                sysdate - trunc(sysdate,'MM')
        )       redo_now
from 
        dual
;



select 
        86400 * (
                (sysdate - 10/1440) - trunc((sysdate-10/1440),'MM') + 
                31 * 
                        months_between(
                                trunc((sysdate - 10/1440),'MM'),
                                to_date('01-Jan-1988','dd-mon-yyyy')
                        )
                )               ten_minutes_ago,
        86400 * (
                sysdate - trunc(sysdate,'MM') + 
                31 * 
                        months_between(
                                trunc(sysdate,'MM'),
                                to_date('01-Jan-1988','dd-mon-yyyy')
                        )
                )               redo_now,
        to_char(sysdate,'mm/dd/yyyy hh24:mi:ss')        now
from 
        dual
;

This isn’t a piece of code I use much – the original version (which I published in Oracle Core, p.241) was something I wrote in 2003 and had to adjust by hand each time I used it without realising that I’d got it wrong. Luckily someone pointed out my error and gave me the corrected code a little while after I’d published the book. (It was one of those “why didn’t I think of that” moments – it seemed so obvious after he’d told me the right answer.)