Friday, November 30, 2012

ORA-01858: a non-numeric character was found where a numeric was expected

An application related SQL query with following line
AND TO_DATE(DEP_DATE, 'DD-MM-YYYY') = TO_DATE(SYSDATE+360 , 'DD-MM-YYYY')
was failing on some databases while succeeding on some. Though the logic seems at odd (why to_date a date type column) the issue was why it was failing on some databases with ORA-01858 while succeeding in others when executed using SQLPlus. Even on the database the query was failing it was possible to execute the query using JDBC (reason it was working on the application without the error).
After comparing and contrasting the databases the query succeeded and failed it came down to NLS_DATE_FORMAT parameter set on the bash shell environment on some of the databases.
echo $NLS_LANG
English_United Kingdom.AL32UTF8
$ export NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'
$ sqlplus / as sysdba
SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;
select to_date(sysdate,'YYYY-MM-DD') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

$ unset NLS_DATE_FORMAT
$ sqlplus / as sysdba

SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;

TO_DATE(SYSDATE,'Y
------------------
12-NOV-29


It is possible to run the to_date with NLS_DATE_FORMAT set but this must match the format string.
$ export NLS_DATE_FORMAT='DD-MM-YYYY'
$ sqlplus / as sysdba

--different format to that of NLS_DATE_FORMAT
SQL> select to_date(sysdate,'YYYY-MM-DD') from dual;
select to_date(sysdate,'YYYY-MM-DD') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

--same format as the NLS_DATE_FORMAT
SQL> select to_date(sysdate,'DD-MM-YYYY') from dual;

TO_DATE(SY
----------
29-11-2012
NLS_DATE_FORMAT comes into play if NLS_LANG is also set. If NLS_LANG is unset then NLS_DATE_FORMAT is ignored.

Useful metalink notes
RMAN Backup Fails With RMAN-03009 ORA-01858 ORA-01861 [ID 744047.1]
Query using TO_DATE TO_CHAR failing with ORA-01858 or ORA-01843 [ID 790098.1]
The Priority of NLS Parameters Explained (Where To Define NLS Parameters) [ID 241047.1]
OERR: ORA 1858 "a non-numeric character was found where a numeric was expected" [ID 19182.1]
How To Set a NLS Session Parameter At Database Or Schema Level For All Connections? [ID 251044.1]

Monday, November 26, 2012

ORA-15477 cannot communicate with the volume driver

ORA-15477: cannot communicate with the volume driver could be seen when setting the compatible.admv value on a ASM disk group. The system was running 11.2.0.3 (11.2.0.3.4) RAC on RHEL 6 (2.6.32-220.el6.x86_64) and it's a brand new installation of RAC (1488004.1).
SQL> alter diskgroup flash set attribute 'compatible.advm'='11.2';
alter diskgroup flash set attribute 'compatible.advm'='11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.advm
ORA-15238: 11.2 is not a valid value for attribute compatible.advm
ORA-15477: cannot communicate with the volume driver
It seems the ACFS/ADMV modules are not loaded with system reboot as
[root@rhel6m1 ~]# lsmod | grep oracle
didn't return anything.
Reinstall acfs/admv modules manually (1371067.1)
[root@rhel6m1 bin]# ./acfsroot install
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9309: ADVM/ACFS installation correctness verified.
After this acfs modules will be loaded
lsmod  | grep oracle
oracleacfs           1857627  2
oracleadvm            238976  6
oracleoks             330757  2 oracleacfs,oracleadvm
and change of compatible.admv would proceed without an error and acfs volumes could be created
SQL> alter diskgroup flash set attribute 'compatible.advm'='11.2';
Diskgroup altered.

SQL> alter diskgroup flash add volume volume1 size 1g;
Diskgroup altered.

SQL> select volume_name,volume_device from v$asm_volume;

VOLUME_NAME                    VOLUME_DEVICE
------------------------------ --------------------
VOLUME1                        /dev/asm/volume1-149

# mkfs -t acfs /dev/asm/volume1-149
mkfs.acfs: version                   = 11.2.0.3.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/volume1-149
mkfs.acfs: volume size               = 1073741824
mkfs.acfs: Format complete.

acfsutil registry -a /dev/asm/volume1-149 /opt/acfsvol

#mount -t acfs  /dev/asm/volume1-149 /opt/acfsvol



However after the reboot of the servers the acfs modules do not get auto loaded and following could be seen on ASM alert log
SUCCESS: ALTER DISKGROUP ALL MOUNT /* asm agent call crs *//* {0:0:2} */
SQL> ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent *//* {0:0:2} */
kfvxVolOnOff: Cannot open device file
ORA-15032: not all alterations performed
ORA-15477: cannot communicate with the volume driver
ERROR: ALTER DISKGROUP ALL ENABLE VOLUME ALL /* asm agent *//* {0:0:2} */
To fix this run acfsroot enable. This command requires acfs to be started before running it
[root@rhel6m1 grid]# $GI_HOME/bin/acfsroot enable
ACFS-9167: ADVM/ACFS is not installed or loaded. Run 'acfsroot install'.

[root@rhel6m1 grid]# $GI_HOME/bin/acfsload start -s
[root@rhel6m1 grid]# $GI_HOME/bin/acfsroot enable
ACFS-9376: Adding ADVM/ACFS drivers resource succeeded.
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'rhel6m1'
CRS-2676: Start of 'ora.drivers.acfs' on 'rhel6m1' succeeded
ACFS-9380: Starting ADVM/ACFS drivers resource succeeded.
ACFS-9368: Adding ACFS registry resource succeeded.
CRS-2672: Attempting to start 'ora.registry.acfs' on 'rhel6m1'
CRS-2676: Start of 'ora.registry.acfs' on 'rhel6m1' succeeded
ACFS-9372: Starting ACFS registry resource succeeded.
This issue is mentioned in (1488004.1 and 1486208.1)
Afterwards acfs modules were auto loading on reboot and acfs volumes were getting auto mounted without a problem.

Useful metalink notes
ACFS Supported On OS Platforms. [ID 1369107.1]
ACFS Filesystem Not Mounted After Host Reboot [ID 1473875.1]
How To Install/Reinstall Or Deinstall ACFS Modules/Installation Manually? [ID 1371067.1]
11.2.0.3.3 Grid Infrastructure Patchset: 13919095 Installation On RH 6.3 Fails With ACFS-9459 . [ID 1493491.1]
ORA.REGISTRY.ACFS Resource Appears As OFFLINE On A Brand New 11.2 RAC Installation/Configuration. [ID 1488004.1]
ACFS/ADVM is NOT started automatically after node reboot or after CRS is restarted in non-RAC environment [ID 886407.1]
“ora.drivers.acfs” Resource Was Not Configured Therefore RAC ACFS Filesystem Is Not Mounting During The Reboot. [ID 1486208.1]

Related Post
Creating and Removing ACFS

Friday, November 16, 2012

Applying Patch Set using Standby-First

According to metalink note 278641.1 before applying a patch to a physical standby environment log transport must be stopped on the primary site. Although the note says to shutdown all standby instance, it should still be possible to do a rolling patch on the standby environment if patch allows it. However recovery may need to be stopped before patching and since redo transport is stopped, there won't be anything to recover anyway.
From 11.2.0.1 onwards if the patch set says it's applicable in a standby-first manner then these patches could be applied on standby without stopping redo transport nor recovery on the standby database.
This post shows the highlights of applying GI PSU 11.2.0.3.4 (which is a rolling and Standby-First applicable patch) to a physical data guard environment.
Before the patch apply both standby and primary are at the same patch level.primary
rac5 psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)" 
Standby
rac5b psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Identify the apply instance and start patch applying on the none apply instance first
show database rac11g2s

Database - rac11g2s

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac11g2s1 (apply instance)
    rac11g2s2

Database Status:
SUCCESS
In this case apply instance was rac11g2s1 running on rac4b hence patching will being on rac5b.
Once patched rac5b will be at a higher patch level than rac4b and all nodes on primary
rac5b psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Switch the apply instance to patched node and patch the remaining nodes as well
DGMGRL> edit database rac11g2s set state ='APPLY-ON' with apply instance = 'rac11g2s2';
Succeeded.
DGMGRL> show database rac11g2s
Database - rac11g2s

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac11g2s1
    rac11g2s2 (apply instance)

Database Status:
SUCCESS

rac4b ~]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
At this stage all Oracle binaries in standby environment are patched.
It is possible to run the data guard configuration like this with primary and standby at different patch level but there are limitations to how long this configuration will be supported (less than 1 month) and these are explained in 1265700.1. This gives time to evaluate the patch either by creating a snapshot standby or with role transition.


Once patch is evaluated on standby environment apply it on the primary site as well. Since this patch is applicable in a rolling fashion log transport will be active at all times during the patch application.
DGMGRL> show database rac11g2

Database - rac11g2

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    rac11g21
    rac11g22

Database Status:
SUCCESS
After patching rac5
rac5 ~]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"
Shutdown the remaining instances and apply the patch (rac4 in this case) and at the same time execute the post-installation tasks on the already patched instance.
After patching rac4
rac4 psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "Database Patch Set Update : 11.2.0.3.4 (14275605)"
Sub-patch  13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)"
Sub-patch  13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)"
Sub-patch  13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)"

Useful metalink notes
Oracle Patch Assurance - Data Guard Standby-First Patch Apply [ID 1265700.1]
Mixed Oracle Version support with Data Guard Redo Transport Services [ID 785347.1]
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]

Tuesday, November 6, 2012

warning: ignoring old commands for target `pcscfg.cfg'

Following could be seen when applying PSU 11.2.0.3.4 on a DB home
OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/opt/app/oracle/product/11.2.0/ent11.2.0.3/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
/opt/app/oracle/product/11.2.0/ent11.2.0.3/precomp/lib/ins_precomp.mk:19: warning: overriding commands for target `pcscfg.cfg'
/opt/app/oracle/product/11.2.0/ent11.2.0.3/precomp/lib/env_precomp.mk:2160: warning: ignoring old commands for target `pcscfg.cfg'
This is only a warning and patch has been successfully applied.

Useful metalink notes
Opatch warning: overriding commands for target xxxx [ID 1448337.1]

Related Post
ins_srvm.mk: warning: overriding commands for target libsrvm11.so