RMAN archivelog backup

Yesterday I encountered a problem with a RMAN backup. There were no backups of archivelogs, only datafile backups and archivelogs. So I investigated the issue and found the following backup script:

backup database archivelog all delete input;
delete noprompt obsolete;

Looks good at the first glance but what it does is not what I wanted it to do. The “redundancy” is configured to be 1. So what happens is this, first the database is being backued up and right after it the archivelogs are being backed up to. But only those archivelogs that existed at the time the backup command started. No archivelogs that were created during the database backup are being backed up. So the archivelog backups become obsolete immediately and are deleted by the following “delete obsolete” command.
The thing is, the script should look like this:

backup database plus archivelog delete input;
delete noprompt obsolete;

Minor change with a major impact.

Let’s see what happens in both cases

Case 1

Check what sequences are in the online logs and generate some more:

[oracle@oel6u4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 13 10:45:04 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt                                                                ions

SYS@db12cb> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ---------------
         1         16 CURRENT
         2         14 ACTIVE
         3         15 ACTIVE

SYS@db12cb> alter system switch logfile;

System altered.

SYS@db12cb> alter system switch logfile;

System altered.

SYS@db12cb> alter system switch logfile;

System altered.

SYS@db12cb> alter system checkpoint;

System altered.

SYS@db12cb> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ---------------
         1         19 CURRENT
         2         17 INACTIVE
         3         18 INACTIVE

At that point we have archivelogs until sequnce 18. Now we start the RMAN backup:

[oracle@oel6u4 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 13 10:48:07 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB12C (DBID=1357631873)

RMAN> backup database archivelog all delete input;

Starting backup at 13.09.2017 10:48:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=276 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_example_d3lq5h14_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_system_d3lq3d22_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_sysaux_d3lq4lpd_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_marco_d3qvwcwt_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_undotbs1_d3lq5ctw_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_users_d3lq6w0q_.dbf
channel ORA_DISK_1: starting piece 1 at 13.09.2017 10:48:17
channel ORA_DISK_1: finished piece 1 at 13.09.2017 10:51:02
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_nnndf_TAG20170913T104816_dvkw6kls_.bkp tag=TAG20170913T104816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:45
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=43 STAMP=954585900
input archived log thread=1 sequence=15 RECID=44 STAMP=954585901
input archived log thread=1 sequence=16 RECID=46 STAMP=954585995
input archived log thread=1 sequence=17 RECID=48 STAMP=954585996
input archived log thread=1 sequence=18 RECID=50 STAMP=954585998
channel ORA_DISK_1: starting piece 1 at 13.09.2017 10:51:02
channel ORA_DISK_1: finished piece 1 at 13.09.2017 10:51:05
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_annnn_TAG20170913T104816_dvkwcpnp_.bkp tag=TAG20170913T104816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_14_dvkw0cm1_.arc RECID=43 STAMP=954585900
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_15_dvkw0f1w_.arc RECID=44 STAMP=954585901
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_16_dvkw3c38_.arc RECID=46 STAMP=954585995
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_17_dvkw3d1f_.arc RECID=48 STAMP=954585996
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_18_dvkw3g00_.arc RECID=50 STAMP=954585998
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13.09.2017 10:51:09
channel ORA_DISK_1: finished piece 1 at 13.09.2017 10:51:10
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_ncsnf_TAG20170913T104816_dvkwcx24_.bkp tag=TAG20170913T104816 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13.09.2017 10:51:10

We see, that all archivelogs until and including sequence 18 are being backed up. No sign of sequence 19 or any sequence beyond that.

Case 2

Again, we check the sequences in the online logs. There is no change to what we had ad the beginning of Case-1-Backup. That’s why I create some more archivelogs.

[oracle@oel6u4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 13 12:36:06 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@db12cb> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ---------------
         1         19 CURRENT
         2         17 INACTIVE
         3         18 INACTIVE

SYS@db12cb> alter system switch logfile;

System altered.

SYS@db12cb> alter system switch logfile;

System altered.

SYS@db12cb> alter system checkpoint;

System altered.

SYS@db12cb> select group#, sequence#, status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ---------------
         1         19 INACTIVE
         2         20 INACTIVE
         3         21 CURRENT

Now we start the backup with the slightly modified command:

[oracle@oel6u4 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Sep 13 12:36:41 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DB12C (DBID=1357631873)

RMAN> backup database plus archivelog delete input;


Starting backup at 13.09.2017 12:36:49
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=19 RECID=52 STAMP=954592574
input archived log thread=1 sequence=20 RECID=54 STAMP=954592575
input archived log thread=1 sequence=21 RECID=56 STAMP=954592610
channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:36:50
channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:36:51
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_annnn_TAG20170913T123650_dvl2l2o8_.bkp tag=TAG20170913T123650 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_19_dvl2jx41_.arc RECID=52 STAMP=954592574
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_20_dvl2jzhj_.arc RECID=54 STAMP=954592575
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_21_dvl2l23m_.arc RECID=56 STAMP=954592610
Finished backup at 13.09.2017 12:36:51

Starting backup at 13.09.2017 12:36:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_example_d3lq5h14_.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_system_d3lq3d22_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_sysaux_d3lq4lpd_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_marco_d3qvwcwt_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_undotbs1_d3lq5ctw_.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/DB12CB/datafile/o1_mf_users_d3lq6w0q_.dbf
channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:36:52
channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:39:07
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_nnndf_TAG20170913T123651_dvl2l435_.bkp tag=TAG20170913T123651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:39:08
channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:39:09
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_ncsnf_TAG20170913T123651_dvl2pd9n_.bkp tag=TAG20170913T123651 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13.09.2017 12:39:09

Starting backup at 13.09.2017 12:39:09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=22 RECID=58 STAMP=954592749
channel ORA_DISK_1: starting piece 1 at 13.09.2017 12:39:09
channel ORA_DISK_1: finished piece 1 at 13.09.2017 12:39:10
piece handle=/u01/app/oracle/fra/DB12CB/backupset/2017_09_13/o1_mf_annnn_TAG20170913T123909_dvl2pfxh_.bkp tag=TAG20170913T123909 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fra/DB12CB/archivelog/2017_09_13/o1_mf_1_22_dvl2pflz_.arc RECID=58 STAMP=954592749
Finished backup at 13.09.2017 12:39:10

Looks different now. We have a logswitch at the very beginning of the backup. Then all the archivelogs that exists at that point are being backed up. Then there is the database backup. And finally another logswitch is done and all the logs that were created during the database backup are being backed up too.
That is, what I really want.

Conclusion

The effect of the “plus archivelog” syntax is completely different from the combined “backup” command for different file types. So be very careful which option you use and most important: check your backup strategy by doing test restores on a regular basis.

Advertisements

NLS_LANG, character sets & more – Part 2

Some days ago I wrote about NLS_LANG, character sets & more. Today I saw another neat effect. I was trying to do it right, but my messages from SQL*Plus still looked scrambled:

12-win-1252

The thing is, that the characters I need are simply not present in the default font. When I change the font, the same screen now looks somehow different:

13-win-1252

Maybe I should switch from German to English and ASCII 7 completely…

Oracle Grid Infrastructure 12.2 – HASMB

Oracle Grid Infrastructure used to have a feature to make a NFS share highly available. It simply uses an additional virtual IP address (VIP) and controls the NFS server to use this VIP. The client simply connects the NFS share using this VIP. So the NFS share is always available regardless on which node it currently running. This feature is restricted to ACFS, we can create NFS exports for an ACFS only which makes kind of sense. This whole thing is also known as HANFS.

New in Grid Infrastructure 12.2 is the ability to create highly available SMB shares, consequently called HASMB. Let’s see if and how this works. Some steps needs to be done as “root” while other steps can be done as “oracle”, see the command prompt to identify which user I actually used.

First, we need to install the samba server if not already done.

[root@vm151 ~]# yum install samba samba-winbind

Of couse this needs to be done on all servers in the cluster.

Now, we add a line to /etc/samba/smb.conf to allow guest access:

[global highlight="5"]
        workgroup = SAMBA
        security = user
        map to guest = Bad Password   

Finally, we start the samba server and enable it for autostart:

[root@vm151 ~]# systemctl start smb
[root@vm151 ~]# systemctl enable smb
Created symlink from /etc/systemd/system/multi-user.target.wants/smb.service to /usr/lib/systemd/system/smb.service.

For high availability we need to create a VIP:

[root@vm151 ~]# srvctl add havip -id mmiexport -address vm156.support.robotron.de

Next step is to create an ACFS filesystem that we will export later on:

[oracle@vm151 ~]$ asmcmd volcreate -G data -s 1G mmivolume
[oracle@vm151 ~]$ asmcmd volinfo -G data mmivolume
Diskgroup Name: DATA

         Volume Name: MMIVOLUME
         Volume Device: /dev/asm/mmivolume-1
         State: ENABLED
         Size (MB): 1024
         Resize Unit (MB): 64
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage:
         Mountpath:

[oracle@vm151 ~]$ /sbin/mkfs -t acfs /dev/asm/mmivolume-1
mkfs.acfs: version                   = 12.2.0.1.0
mkfs.acfs: on-disk version           = 46.0
mkfs.acfs: volume                    = /dev/asm/mmivolume-1
mkfs.acfs: volume size               = 1073741824  (   1.00 GB )
mkfs.acfs: Format complete.

Ok, now we configure Grid Infrastructure resources for this filesystem:

[oracle@vm151 ~]$ mkdir -p /u01/app/oracle/acfs/mmivolume
[oracle@vm151 ~]$ ssh vm152 mkdir -p /u01/app/oracle/acfs/mmivolume



[root@vm151 ~]# srvctl add filesystem -volume mmivolume -diskgroup data -path /u01/app/oracle/acfs/mmivolume -user oracle
[root@vm151 ~]# srvctl start filesystem -volume mmivolume -diskgroup data
[root@vm151 ~]# srvctl status filesystem -volume mmivolume -diskgroup data
ACFS file system /u01/app/oracle/acfs/mmivolume is mounted on nodes vm151,vm152
[root@vm151 ~]# ll /u01/app/oracle/acfs/mmivolume
total 64
drwx------ 2 root root 65536 Jul 27 11:23 lost+found
[root@vm151 ~]# df -h /u01/app/oracle/acfs/mmivolume
Filesystem            Size  Used Avail Use% Mounted on
/dev/asm/mmivolume-1  1.0G  487M  538M  48% /u01/app/oracle/acfs/mmivolume

This is the moment when we can create the export:

[root@vm151 ~]# srvctl add exportfs -name mmiexportfs -path /u01/app/oracle/acfs/mmivolume -id mmiexport -type SMB
[oracle@vm151 ~]$ srvctl start havip -id mmiexport
[oracle@vm151 ~]$ srvctl status exportfs
export file system mmiexportfs is enabled
export file system mmiexportfs is exported on node vm151

Let’s see what it did to the samba config files:

[root@vm152 ~]# cat /etc/samba/smb.conf
...
# ACFS HASMB include
  include = /etc/samba/acfs/acfsinc.conf
...

[root@vm151 ~]# cat /etc/samba/acfs/acfsinc.conf
include=/etc/samba/acfs/mmiexportfs.conf

[root@vm151 ~]# cat /etc/samba/acfs/mmiexportfs.conf
[mmiexportfs]
path=/u01/app/oracle/acfs/mmivolume
read only = Yes
browsable = yes

It created an include for all the ACFS related things which in turn consists of additional includes for all the exports that we defined and that are currently active on the specific node.

So let’s try:

[oracle@vm152 ~]$ echo "Hallo" > /u01/app/oracle/acfs/mmivolume/mein.txt
C:\>dir \\vm156.support.robotron.de\mmiexportfs
Zugriff verweigert

Makes sense somehow. As we can see above, there is no option that allows guest access to the newly created share. So let’s add that:

[root@vm151 ~]# srvctl modify exportfs -name mmiexportfs -options "read only=no,browsable=yes,guest ok=yes,writeable=yes,public=yes"
[root@vm151 ~]# cat /etc/samba/acfs/mmiexportfs.conf
[mmiexportfs]
path=/u01/app/oracle/acfs/mmivolume
read only = Yes
browsable = yes

Ok, it does not apply the new options immediatly. It requires a restart:

[root@vm151 ~]# srvctl stop exportfs -name mmiexportfs -force
[root@vm151 ~]# srvctl start havip -id mmiexport -node vm151
[root@vm151 ~]# cat /etc/samba/acfs/mmiexportfs.conf
[mmiexportfs]
path=/u01/app/oracle/acfs/mmivolume
read only = no
browsable = yes
guest ok = yes
writeable = yes
public = yes

Let’s check again:

C:\>dir \\vm156.support.robotron.de\mmiexportfs
 Datenträger in Laufwerk \\vm156.support.robotron.de\mmiexportfs: ist mmiexportfs
 Volumeseriennummer: 6691-6876

 Verzeichnis von \\vm156.support.robotron.de\mmiexportfs

27.07.2017  12:51    <DIR>          .
27.07.2017  11:16    <DIR>          ..
27.07.2017  11:23    <DIR>          lost+found
27.07.2017  12:45                 6 mein.txt
               2 Datei(en),             15 Bytes
               3 Verzeichnis(se),    496.132.096 Bytes frei

C:\>type \\vm156.support.robotron.de\mmiexportfs\mein.txt
Hallo

How about creating files from the Windows end of the line?

C:\>echo Cheers > \\vm156.support.robotron.de\mmiexportfs\windows.txt
Zugriff verweigert

The reason for this are the filesystem permissions on the Linux side, we need to change these:

[root@vm152 ~]# ls -la /u01/app/oracle/acfs/mmivolume
total 100
drwxrwxr-x 4 oracle oinstall 32768 Jul 27 12:45 .
drwxr-xr-x 3 oracle oinstall    22 Jul 27 11:16 ..
drwx------ 2 root   root     65536 Jul 27 11:23 lost+found
-rw-r--r-- 1 oracle oinstall     6 Jul 27 12:45 mein.txt
[root@vm152 ~]# chmod 777 /u01/app/oracle/acfs/mmivolume
[root@vm152 ~]# ls -la /u01/app/oracle/acfs/mmivolume
total 100
drwxrwxrwx 4 oracle oinstall 32768 Jul 27 12:45 .
drwxr-xr-x 3 oracle oinstall    22 Jul 27 11:16 ..
drwx------ 2 root   root     65536 Jul 27 11:23 lost+found
-rw-r--r-- 1 oracle oinstall     6 Jul 27 12:45 mein.txt

And voila, now it works:

C:\>echo Cheers > \\vm156.support.robotron.de\mmiexportfs\windows.txt

C:\>type \\vm156.support.robotron.de\mmiexportfs\windows.txt
Cheers

And from the other end too:

[oracle@vm151 ~]# cat /u01/app/oracle/acfs/mmivolume/windows.txt
Cheers

The last step is to try a relocate of the export:

[oracle@vm152 ~]$ srvctl status exportfs -id mmiexport
export file system mmiexportfs is enabled
export file system mmiexportfs is exported on node vm151
[oracle@vm152 ~]$ srvctl relocate havip -id mmiexport -force
HAVIP was relocated successfully
[oracle@vm152 ~]$ srvctl status exportfs -id mmiexport
export file system mmiexportfs is enabled
export file system mmiexportfs is exported on node vm152

During the relocate operation, which is pretty fast by the way, I tried a “dir” opertion from my windows client. It simply “hangs” for a couple of seconds and returns results as soon as the share becomes available again. Pretty straight forward.

So all in all this is a nice expansion to include the Windows world without having to use NFS on Windows.
Of cause my example is not very bullet-proof in terms of security. This can (and should) be changed for production purposes.

NLS_LANG, character sets & more – How to do it right

Preface

Globalization is confusing. Everyone and everything is using different characters in different charcter sets. Oracle is prepared for that and offers several parameters and variables to control the behaviour. But one must be careful when setting these, that’s why I want to give a rough overview of the basic things.

Basics

When sending or retrieving character data to/from a database, there are three to four settings that can influence the display of that data.

nls-kette

  1. That is the encoding inside the database itself.
  2. The character set which the Oracle Client is using to display character data.
  3. The charcter set that is used by the operating system at the client side.
  4. The character set that PuTTY (or whatever terminal emulation you prefer) asumes the remote side is using.

The picture outlines the route which character data takes during the process of reading from or writing to the database. In this post I will talk about the yellow part of the diagram.

Let’s start with the database. There is some character data stored inside that is encoded using the configured database characterset. Now we want to retrieve data from the database. That means the Oracle Client (2) sends the SQL to the database (1) and in turn gets the data which is being converted automatically to the character set that is configured for the Oracle Client. That is typically done using the NLS_LANG variable. Beside that, this is the only point where a character set conversion might happen.

Next the charcter data is being displayed by the operating system (3) which is using it’s own character set. There is no conversion anymore, that means our NLS_LANG setting must match the setting of our OS.

And lastly maybe there is a terminal emulation like PuTTY which also defines a character set which obviously must match to the one used by the OS we are connecting to.

What can go wrong

Having this process in mind we see, that wrong parameter settings may not be recognized immediately. When inserting data into the database with wrong settings and then querying this data will get correct results as the translation is done in the same way. We start seeing wrong characters when we query the data with the correct settings.

Let’s say we have a Windows client and use SQL*Plus inside CMD to insert data. The system wide NLS_LANG variable is set to MSWIN1252 as Windows is using this character set. But as described in a previous post the CMD is using another character set, PC850.

So let’s create a table, insert some data and query that data:

01-win-1252

So we see that the special characters that I inserted are displayed correctly when querying the data because the same wrong transformation happens in both directions. More or less, I have no idea why the Euro sign gets messed up… Maybe because PC850 has no Euro sign…

Now I query the data from SQL Developer which is using the windows character set to display data:

02-win-1252

Now again a transformation might take place, depends on the database setting. And this time we see wrong data because the transformation was wrong when I inserted the data.

Next step, insert data with SQL Developer:

03-win-1252

Inserted and displayed correctly. But obviously CMD shows it as follows:

04-win-1252

This is all simply because the CMD renders characters in a different character set as the rest of Windows. So when we change CMD to use the proper code page, it looks like this:

05-win-1252

Now the first dataset is rendered differently, but the data from SQL Developer is shown properly.

Another way to display the data properly is to modify the NLS_LANG setting inside CMD:

06-win-1252

Only the Euro sign is missing since it is not part of the PC850 character set.

Now I set the NLS_LANG again to MSWIN1252 and insert a third record:

07-win-1252

Looks good so far, but again, I should crosscheck that with SQL Developer:

08-win-1252

Ok, the data is still displayed properly. So this is the correct setting that we should use for Windows.

But what about Linux? Linux is using UTF8 internally:

linux-nls

So I should set NLS_LANG to AL32UTF in order to get my data displayed correctly:

09-win-1252

As expected, the data shows up as it should be. But this is only because my PuTTY is using the right setting. What if I modify PuTTY to use MSWIN1252? Might be a valid setting because my Windows where PuTTY runs on is using that character set:

10-win-1252

How does the result now looks like?

11-win-1252

Totally messed up since my multibyte output from Linux is being interpreted as singlebyte. So that is not a good idea. The PuTTY character set setting must match the character set that is used by the OS that we connect to:

putty-nls

Conclusion

Be careful when setting NLS parameters on both, client and server, side. You might not notice a misconfiguration as long as you are using the same track for data retrival and insertion. Just use another client to crosscheck the data that you are dealing with. It all depends on the OS and the correct NLS_LANG setting at the client side.

There is a good FAQ from Oracle that outlines the whole NLS things.

Default Collation and PL/SQL

One of the new features that got introduced in Oracle Database 12.2 is the possibility to define the default collation at schema-, table- or column-level. This is quite nice because we do not have to use NLS_SORT in each and every query anymore.
But obviously there are some restrictions. Let’s create two users, one with no specific collation and one that uses a non-default collation.

SQL> show user
USER is "SYS"
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDBMMI                         READ WRITE NO

         
SQL> create user usr_default identified by xxx;

User created.

SQL> grant create session, alter session, create procedure to usr_default;

Grant succeeded.

SQL> create user usr_collate identified by xxx default collation binary;

User created.

SQL> grant create session, alter session, create procedure to usr_collate;

Grant succeeded.

Now I create a simple piece of PL/SQL, first with the default user.

SQL> conn usr_default/xxx@pdbmmi
Connected.
SQL> show user
USER is "USR_DEFAULT"
SQL> select default_collation from user_users;

DEFAULT_COLLATION
--------------------------------------------------------------------------------
USING_NLS_COMP

SQL> create procedure do_nothing
  2  as
  3  begin
  4    null;
  5  end;
  6  /

Procedure created.

SQL> exec do_nothing;

PL/SQL procedure successfully completed.

SQL> drop procedure do_nothing;

Procedure dropped.

Nothing special here. Everything works as expected and as we know it since years. So let’s try the same with the user that has a different default collation.

SQL> conn usr_collate/xxx@pdbmmi
Connected.
SQL> show user
USER is "USR_COLLATE"
SQL> select default_collation from user_users;

DEFAULT_COLLATION
--------------------------------------------------------------------------------
BINARY

SQL> create procedure do_nothing
  2  as
  3  begin
  4    null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE DO_NOTHING:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
0/0      PLS-00761: Program unit collation may only be USING_NLS_COMP

Obviously the PL/SQL compiler is not very happy with the different collation option. So we need to change that, at session level in my case, to make it compile.

SQL> alter session set default_collation=USING_NLS_COMP;

Session altered.

SQL> alter procedure do_nothing compile;

Procedure altered.

SQL> show err
No errors.
SQL> exec do_nothing;

PL/SQL procedure successfully completed.

SQL> drop procedure do_nothing;

Procedure dropped.

Now it compiles without any warnings or errors.

So keep that in mind when you create users with a specific collation option.

Oracle 12.2 – SQL*Plus Command History

Currently I am preparing some slides for future events. I will share some of the topics beforehand. First topic is the one mentioned in the title, with Oracle Database 12.2 we finally get a command history. Though only a tiny new bit this is one of my favorite new features.
Let’s see how this works.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 8 09:49:04 2017

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


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

SQL> history
SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.

Ok, the feature is inactive by default. We need to enable it by setting it to “ON” or to a number specifying how many statements should be kept in the history.

SQL> set history 50

SQL> select * from dual;
D
-
X
SQL> history
  1  select * from dual;

Let’s see what else is possible with the “history” command:

SQL> help history

 HISTORY
 -------

 Stores, lists, executes, edits of the commands
 entered during the current SQL*Plus session.

 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]

 N is the entry number listed in the history list.
 Use this number to recall, edit or delete the command.

 Example:
 HIST 3 RUN - will run the 3rd entry from the list.

 HIST[ORY] without any option will list all entries in the list.

We can easily run any command by specifying the number from the history or remove a command from the history.

SQL> hist
  1  select * from dual;
  2  select count(*) from dba_objects where status <> 'VALID';

SQL> hist 2 run

  COUNT(*)
----------
         0

SQL> hist 1 del
SQL> hist
  1  select count(*) from dba_objects where status <> 'VALID';

And by the way, the history runs at statement level, not at line level. So this is maybe the most important advantage over OS level command history using arrow-up and arrow-down (natively on Windows or with “rlwrap” on Linux).

SQL> select count(*) from dba_users
  2  where oracle_maintained = 'Y'
  3  and account_status = 'OPEN';

  COUNT(*)
----------
         2

SQL> hist
  1  select count(*) from dba_objects where status <> 'VALID';
  2  select count(*) from dba_users
     where oracle_maintained = 'Y'
     and account_status = 'OPEN';

SQL> hist 2 run

  COUNT(*)
----------
         2

Enjoy the new feature.

Patching an OMS 13.2 on Windows

Yesterday I had to patch an Enterprise Manager 13.2 that is running on a Windows box. It was never patched before so it was clear, that it would require some activities beforehand. But there were some other unforeseen things which I will describe in this post. Sorry for the german output in some of the snippets, it was a german system….

First, I needed to update “OPatch” and “OMSPatcher”. Refreshing the latter one was easy since I just needed to replace the “OMSPatcher” directory in the Middleware Home with the new one. For refreshing “OPatch” it used to be the same procedure, but I learned that it has changed. Reading My Oracle Support docs is really helpful sometimes. Here is how it works:

D:\CloudControl_cc13r2\Update\opatch_13.9.1.3.0\6880880>D:\oracle\product\mw13cR2\oracle_common\jdk\bin\java.exe -jar .\opatch_generic.jar -silent ORACLE_HOME=%ORACLE_HOME%
Launcher-Logdatei ist C:\Users\XXX\AppData\Local\Temp\2\OraInstall2017-04-06_11-15-14AM\launcher2017-04-06_11-15-14AM.log.
Installationsprogramm wird extrahiert... . . Fertig
Es wird geprüft, ob CPU-Geschwindigkeit über 300 MHz liegt   Tatsächlich 3500    Erfolgreich
Swap-Bereich wird geprüft: muss größer sein als 512 MB    Erfolgreich
Es wird geprüft, ob diese Plattform eine 64-Bit-JVM erfordert   Tatsächlich 64    Erfolgreich (64-Bit nicht erforderlich)
Temporärer Speicherplatz wird geprüft: muss größer sein als 300 MB   Tatsächlich 46996 MB    Erfolgreich


Vorbereitung für das Starten von Oracle Universal Installer aus C:\Users\XXX\AppData\Local\Temp\2\OraInstall2017-04-06_11-15-14AM
Installationszusammenfassung


Speicherplatz: erforderlich 27 MB, verf³gbar 397.477 MB
Zu installierende Featuresets:
        Next Generation Install Core 13.9.1.0.1
        OPatch 13.9.1.3.0
        OPatch Auto OPlan 13.9.1.0.0
Sessionlogdatei ist C:\Users\XXX\AppData\Local\Temp\2\OraInstall2017-04-06_11-15-14AM\install2017-04-06_11-15-14AM.log

Die Produktliste wird geladen. Warten.
 1%

[...]

Die Logs finden Sie hier: C:\Users\XXX\AppData\Local\Temp\2\OraInstall2017-04-06_11-15-14AM.

Drücken Sie zum Beenden die Eingabetaste

Now I wanted to apply the OMS side patch using omspatchter:

D:\CloudControl_cc13r2\Update\25501489>omspatcher apply 
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


OMSPatcher version : 13.8.0.0.2
OUI version        : 13.9.1.0.0
Running from       : d:\oracle\product\mw13cR2
Log file location  : d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\opatch2017-04-06_11-22-19AM_1.log

OMSPatcher log file: d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\25501489\omspatcher_2017-04-06_11-22-22AM_deploy.log

Please enter OMS weblogic admin server URL(t3s://omshost.acme.com:7101):>
Please enter OMS weblogic admin server username(weblogic):>
Please enter OMS weblogic admin server password:>


OMSPatcher could not read installed OMS owner from OUI inventory by itself.
Please add OMSPatcher.OMS_USER=<OMS installed user> to command line and try again.


[ Error during Get Central Inventory Information Phase]. Detail: OMSPatcher was not able to read OUI inventory to retrieve installed user & system details.
OMSPatcher failed: OMSPatcher could not read installed OMS owner from OUI inventory by itself.
Please add OMSPatcher.OMS_USER=<OMS installed user> to command line and try again.

Log file location: d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\25501489\omspatcher_2017-04-06_11-22-22AM_deploy.log

Recommended actions: Please check if OUI inventory is locked by some other processes. Please check if OUI inventory is readable.

OMSPatcher failed with error code = 236

…and failed. But the required action is stated quite clearly, so I added the owner of the OMS home:

D:\CloudControl_cc13r2\Update\25501489>omspatcher apply OMSPatcher.OMS_USER=adkaiser
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


OMSPatcher version : 13.8.0.0.2
OUI version        : 13.9.1.0.0
Running from       : d:\oracle\product\mw13cR2
Log file location  : d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\opatch2017-04-06_11-25-23AM_1.log

OMSPatcher log file: d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\25501489\omspatcher_2017-04-06_11-25-29AM_deploy.log

Please enter OMS weblogic admin server URL(t3s://omshost.acme.com:7101):>
Please enter OMS weblogic admin server username(weblogic):>
Please enter OMS weblogic admin server password:>


OMSPatcher could not read installed OMS owner from OUI inventory by itself.
Please add OMSPatcher.OMS_USER=<OMS installed user> to command line and try again.


[ Error during Get Central Inventory Information Phase]. Detail: OMSPatcher was not able to read OUI inventory to retrieve installed user & system details.
OMSPatcher failed: OMSPatcher could not read installed OMS owner from OUI inventory by itself.
Please add OMSPatcher.OMS_USER=<OMS installed user> to command line and try again.

Log file location: d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\25501489\omspatcher_2017-04-06_11-25-29AM_deploy.log

Recommended actions: Please check if OUI inventory is locked by some other processes. Please check if OUI inventory is readable.

OMSPatcher failed with error code = 236

The logfile is not very helpful either:

D:\CloudControl_cc13r2\Update\25501489>type d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\25501489\omspatcher_2017-04-06_11-25-29AM_deploy.log
[06.04.2017 11:25:29]        OMSPatcher has successfully verified min_patching_tool_version check
[06.04.2017 11:25:47]        [ Error during Get Central Inventory Information phase ] Detail:                              OMSPatcher was not able to read OUI inventory to retrieve installed user & system details.
OMSPatcher could not read installed OMS owner from OUI inventory by itself.
                             Please add OMSPatcher.OMS_USER=<OMS installed user> to command line and try again.

So I started researching and found EM13c: OMSPatcher Analyze Command For 13c OMS Fails With Error “OMSPatcher failed with error code 236” (Doc ID 2136840.1) which says that the parameter needs to be specified inside a properties file. There is another doc EM 13c: How to Apply a Patch to the Enterprise Manager 13c Cloud Control OMS Oracle Home (Doc ID 2091619.1) which describes how to create this properties file.

First step is to create the WebLogic encrypted configuration and key files.

D:\CloudControl_cc13r2\Update>%ORACLE_HOME%\OMSpatcher\wlskeys\createkeys.cmd -oh %ORACLE_HOME% -location D:\CloudControl_cc13r2\Update

[...]

Your environment has been set.
Please enter weblogic admin server username::> weblogic
Please enter weblogic admin server password::>
CreateKeys Weblogic API executed successfully.

User configuration file created: D:\CloudControl_cc13r2\Update\config
User key file created: D:\CloudControl_cc13r2\Update\key
'createkeys.bat' succeeded.

These two lines can I now use in my properties file along with the OMS owner. This is the content of my properties file:

AdminServerURL=t3s://omshost.acme.com:7101
AdminConfigFile=D:\\CloudControl_cc13r2\\Update\\config
AdminKeyFile=D:\\CloudControl_cc13r2\\Update\\key
OPatchAuto.OMS_USER=administrator

Be aware of the double backslashes, otherwise it won’t work.

Now finally I am able to patch my OMS using this properties file.

D:\CloudControl_cc13r2\Update\25501489>omspatcher apply -property_file D:\CloudControl_cc13r2\Update\properties.txt
OMSPatcher Automation Tool
Copyright (c) 2017, Oracle Corporation.  All rights reserved.


OMSPatcher version : 13.8.0.0.2
OUI version        : 13.9.1.0.0
Running from       : d:\oracle\product\mw13cR2
Log file location  : d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\opatch2017-04-06_13-08-43PM_1.log

OMSPatcher log file: d:\oracle\product\mw13cR2\cfgtoollogs\omspatcher\25501489\omspatcher_2017-04-06_13-08-53PM_analyze.log



WARNING: Could not apply the patch "25414328" because the "oracle.sysman.vi.oms.plugin with version 13.2.1.0.0" core component of the OMS or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "25414306" because the "oracle.sysman.emfa.oms.plugin with version 13.2.1.0.0" core component of the OMS or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "25118889" because the "oracle.sysman.vt.oms.plugin with version 13.2.1.0.0" core component of the OMS or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "25414263" because the "oracle.sysman.csm.oms.plugin with version 13.2.1.0.0" core component of the OMS or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "25414255" because the "oracle.sysman.ssa.oms.plugin with version 13.2.1.0.0" core component of the OMS or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.
WARNING: Could not apply the patch "25414356" because the "oracle.sysman.smf.oms.plugin with version 13.2.1.0.0" core component of the OMS or the plug-in for which the patch is intended is either not deployed or deployed with another version in your Enterprise Manager system.

Configuration Validation: Success


Running apply prerequisite checks for sub-patch(es) "25414294,25414339,25414245,25414317,25414281" and Oracle Home "d:\oracle\product\mw13cR2"...
Sub-patch(es) "25414294,25414339,25414245,25414317,25414281" are successfully analyzed for Oracle Home "d:\oracle\product\mw13cR2"

[...]