Oracle 12.1.0.2c Standard Cluster: New Location / Name For Alert Log

In my last post, I had presumed there is a bug since I discovered an empty clusterware alert log in its conventional location i.e.  $ORACLE_HOME/log/<hostname>in 12.1.0.2 standard cluster.

[grid@host01 ~]$ crsctl query crs activeversion;
Oracle Clusterware active version on the cluster is [12.1.0.2.0]

[root@host01 host01]# ls -l /u01/app/12.1.0/grid/log/host01/alerthost01.log

-rw-rw-r– 1 grid oinstall 0 Jun 15 14:10 /u01/app/12.1.0/grid/log/host01/alerthost01.log

But as commented by Ricardo Portillo Proni,  in 12c,  the location of alert log has been changed to $ORACLE_BASE/diag/crs/<hostname>/crs/trace/

Hence, I could successfully  the alert log on node host01 in directory  $ORACLE_BASE/diag/crs/host01/crs/trace/

[grid@host01 trace]$ ls -l $ORACLE_BASE/diag/crs/host01/crs/trace/alert*

-rw-rw—- 1 root oinstall 812316 Aug 11 10:22 /u01/app/grid/diag/crs/host01/crs/trace/alert.log

Another noticeable thing is that name of clusterware alert log has been changed to alert.log as compared to alert<hostname>.log in 11g.

I would like to mention that I have verified the above only in 12.1.0.2 standard cluster.

In 12.1.0.1 flex cluster though, the location and name of  alert log location is same as in 11g i.e. $ORACLE_HOME/log/host01

[root@host01 host01]# crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [12.1.0.1.0]

[root@host01 host01]# ls -l $ORACLE_HOME/log/host01/alert*
-rw-rw-r-- 1 grid oinstall 497364 Aug 11 11:00 /u01/app/12.1.0/grid/log/host01/alerthost01.log

Conclusion:
12.1.0.2 standard cluster

  • Name of alert log : alert.log
  • location of alert log: $ORACLE_BASE/diag/crs/host01/crs/trace

12.1.0.1 flex cluster

  • Name of alert log : alert<hostname>.log
  • location of alert log: $ORACLE_HOME/log/host01

Hope it helps!

Pls refer to comments for further information.

References:
Oracle RAC 12c (12.1.0.2) Operational Best Practices (Oracle Presentation)

———————————————————————————————————————–

Related Links :

Home

12c RAC Index

Oracle 12.1.0.2 Standard Cluster: Empty Alert Log

Oracle 12.1.0.2c Standard cluster : Empty Alert Log

I have setup Oracle  12.1.0.2 standard  2 node cluster  called cluster01 with ASM storage as shown:

[grid@host01 ~]$ asmcmd showclustermode
ASM cluster : Flex mode disabled

[root@host01 ~]# olsnodes -c
cluster01

[root@host01 host01]# crsctl get cluster mode config
Cluster is configured as type “standard

[grid@host01 ~]$ crsctl query crs activeversion;
Oracle Clusterware active version on the cluster is [12.1.0.2.0]

[root@host01 host01]# crsctl get cluster mode status
Cluster is running in “standard” mode

[root@host01 host01]# olsnodes -n
host01 1
host02 2

[root@host01 host01]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
— —– —————– ——— ———
1. ONLINE aa1ca556ae114f57bf44070be6a78656 (ORCL:ASMDISK01) [DATA]
2. ONLINE ff91dd96594d4f3dbfcb9cff081e3438 (ORCL:ASMDISK02) [DATA]
3. ONLINE 815ddcab94d34f50bf318ba93e19951d (ORCL:ASMDISK03) [DATA]
Located 3 voting disk(s).

[root@host01 host01]# ocrcheck -config
Oracle Cluster Registry configuration is :
Device/File Name : +DATA

[root@host01 host01]# crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

When I tried to check the contents of the cluster alert log, I was surprised to find an empty alert log.

[root@host01 host01]# ls -l /u01/app/12.1.0/grid/log/host01/alerthost01.log

-rw-rw-r– 1 grid oinstall 0 Jun 15 14:10 /u01/app/12.1.0/grid/log/host01/alerthost01.log

It seems that this is a bug.

References:
Oracle RAC 12c (12.1.0.2) Operational Best Practices (Oracle Presentation)

———————————————————————————————————————–

Related Links :

Home

12c RAC Index

Oracle 12.1.0.2 Standard Cluster: New Name / Location Of Alert Log

ASM

Home

12c: Database Backups Using ACFS Snapshots
ASM disk mapping to linux partition 
Integrating ACFS Snapshots With RMAN
ORA-15040: diskgroup is incomplete
Oracle Automatic Storage Management Filter Driver (Oracle ASMFD)
Oracle Multitenant: Convert Single Instance PDB to RAC Plugging It Into  A RAC CDB
Troubleshooting ASM Proxy instance startup

 

CRS-4995: The command ‘Modify resource’ is invalid in crsctl. Use srvctl for this command.

Today, in my 12.1.0.2 cluster,  I encountered above error message when I was trying to modify ACL of an ASM cluster file system created on volume VOL1 in DATA diskgroup as follows:

[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'"

CRS-4995: The command 'Modify resource' is invalid in crsctl. Use srvctl for this command.

I resolved the above problem by using the unsupported flag as follows:

[root@host01 ~]# crsctl modify resource ora.data.vol1.acfs -attr "ACL='owner:root:rwx,pgrp:dba:rwx,other::r--'" -unsupported

 

Hope it helps!!

References:
Oracle Issue running 12.1.0.2 clusterware with 11.2.0.2 database

Oracle Issue running 12.1.0.2 clusterware with 11.2.0.2 database

——————————————————————————————————————-

 Related Links :

Home

12c RAC Index

 

INDEX FULL SCAN (MIN/MAX) Not Used – How To Resolve

If you want to find out  the minimum or the maximum of a column value and the column is indexed, Oracle can very quickly determine the minimum or maximum value of the column by navigating to the first (left-most) or last (right-most) leaf blocks in the index structure to get the  Min or Max values respectively.  This access path known as  Index Full Scan (Min/Max) is extremely cost effective as instead of scanning the entire index / table, only first or last entries in the index need to be read.

In case the Select clause includes another column with a function applied to it, optimizer employs Full table Scan instead. In this post, I will demonstrate this scenario and also the solution to the same.

In my test setup, I have a table HR.EMP having index on SALARY column.

— Let’s first query the MIN(SALARY) and SYSDATE from HR.EMP. It can be seen that optimizer employs INDEX FULL SCAN (MIN/MAX) as desired.

SQL>select min(salary),  sysdate from hr.emp ;
select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 7c3q3s8g2ucxx, child number 0
-------------------------------------
select min(salary), sysdate from hr.emp

Plan hash value: 3077585419
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_SAL | 107 | 428 | |
----------------------------------------------------------------------

— Now if I try to find out MIN(SALARY)  with function applied to SYSDATE, the optimizer chooses  costly TABLE ACCESS FULL instead of  INDEX FULL SCAN (MIN/MAX) .

SQL>select min(salary), to_char(sysdate, 'dd/mm/yy') from hr.emp ;
select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 3dthda93cgm6v, child number 0
-------------------------------------
select min(salary), to_char(sysdate, 'dd/mm/yy') from hr.emp

Plan hash value: 2083865914
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 107 | 428 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------

As a workaround , we can  restructure our query as shown so that  it uses an inline view to get the MIN(SALARY)   so that optimizer chooses   INDEX FULL SCAN (MIN/MAX) and  function to SYSDATE  is applied in the  main SELECT clause.

SQL>select min_salary, to_char(sysdt, 'dd/mm/yy') from
(select min(salary) min_salary, sysdate sysdt from hr.emp) ;
select * from table (dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 5rzz6x8wzkh2k, child number 0
-------------------------------------
select min_salary, to_char(sysdt, 'dd/mm/yy') from (select
min(salary) min_salary, sysdate sysdt from hr.emp)

Plan hash value: 2631972856
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | VIEW | | 1 | 19 | 3 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 4 | | |
| 3 | INDEX FULL SCAN (MIN/MAX)| EMP_SAL | 107 | 428 | | |
-------------------------------------------------------------------------

Hope it helps!

References:

AIOUG -North India Chapter- Performance Tuning By Vijay Sehgal – 30th May 2015
Index Full Scan (MIN/MAX) and Partitioned Table

——————————————————————————————————————-

Related links:

Home
Tuning Index

Webinar: Adaptive Query Optimization

Oracle Database 12c introduces Adaptive Query optimization framework which is designed to overcome pitfalls of oracle database 11g and help the optimizer to generate an optimal plan when existing statistics are insufficient.

There are two techniques of Adaptive Query Optimization:
• Adaptive / Dynamic plans
• Automatic Re-optimization

I will present a webinar on “Adaptive Query Optimization” on Saturday, June 13 at 10:00 AM – 11:00 AM (IST) organized by All India Oracle User Group – North India Chapter.

OR
Hope to meet you at the webinar!!!

Troubleshooting ASM Proxy instance startup

Recently, I had trouble starting ASM proxy instance on one of the nodes in my  2 node flex cluster having nodes host01 and host02. As a result I could not access the volume I created on an ASM  diskgroup.  This post explains  how I resolved it.

While connected to host01, I created a volume VOL1 on DATA diskgroup with corresponding volume device /dev/asm/vol1-106 .

[grid@host01 root]$ asmcmd volcreate -G DATA -s 300m VOL1

[grid@host01 root]$ asmcmd volinfo -G DATA VOL1

Diskgroup Name: DATA

Volume Name: VOL1
Volume Device: /dev/asm/vol1-106
State: ENABLED
Size (MB): 320
Resize Unit (MB): 32
Redundancy: MIRROR
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath:

I created  ACFS file system on the newly created volume

[root@host01 ~]# mkfs -t acfs /dev/asm/vol1-106

I also created corresponding mount point /mnt/acfsmounts/acfs1 on both the nodes in the cluster.

root@host01 ~]# mkdir -p /mnt/acfsmounts/acfs1

root@host02 ~]# mkdir -p /mnt/acfsmounts/acfs1

When I tried to mount the volume device, I could mount the volume device on host01 but not on host02 .

[root@host01 ~]#mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

[root@host01 ~]# mount | grep vol1

/dev/asm/vol1-106 on /mnt/acfsmounts/acfs1 type acfs (rw)

[root@host02 ~]# mount -t acfs /dev/asm/vol1-106 /mnt/acfsmounts/acfs1

mount.acfs: CLSU-00100: Operating System function: open64 failed with error data: 2
mount.acfs: CLSU-00101: Operating System error message: No such file or directory
mount.acfs: CLSU-00103: error location: OOF_1
mount.acfs: CLSU-00104: additional error information: open64 (/dev/asm/vol1-106)
mount.acfs: ACFS-02017: Failed to open volume /dev/asm/vol1-106. Verify the volume exists.

The corresponding volume device was visible on host01 but not on host02

[root@host01 ~]# cd /dev/asm
[root@host01 asm]# ls
vol1-106

[root@host02 ~]# cd /dev/asm
[root@host02 asm]# ls

Since ADVM / ACFS utilize an ASM Proxy instance in a flex cluster to access metadata from a local /  remote  ASM instance ,  I checked whether ASM Proxy instance was running on both the nodes and realized that whereas ASM Proxy instance was running on host01, it  was not running on host02

[root@host01 ~]# ps -elf | grep pmon | grep APX

0 S grid 27782 1 0 78 0 – 350502 – 10:09 ? 00:00:00 apx_pmon_+APX1

[root@host02 asm]# ps -elf | grep pmon | grep APX

[root@host01 ~]# srvctl status asm -proxy

ADVM proxy is running on node host01

[root@host01 ~]# crsctl stat res ora.proxy_advm -t
——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE OFFLINE host02 STABLE

I tried to start ASM  proxy instance manually on host02

[grid@host02 ~]$ . oraenv
ORACLE_SID = [grid] ? +APX2
The Oracle base has been set to /u01/app/grid

[grid@host02 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Sat May 2 10:31:45 2015

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

Connected to an idle instance.

SQL> startup

ORA-00099: warning: no parameter file specified for ASMPROXY instance
ORA-00443: background process "VUBG" did not start

SQL> ho oerr ORA 00443

00443, 00000, "background process \"%s\" did not start"
// *Cause: The specified process did not start.
// *Action: Ensure that the executable image is in the correct place with
// the correct protections, and that there is enough memory.

I checked the memory allocated to VM for host02 – It was 1.5 GB as against 2.5 GB assigned to VM for host01. I  increased the memory of host02 to 2.5 GB and ASM proxy instance started automatically.

[root@host01 ~]# crsctl stat res ora.proxy_advm -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.proxy_advm
ONLINE ONLINE host01 STABLE
ONLINE ONLINE host02 STABLE

Hope it helps!

References: 

Oracle documentation

———————————————————————————————————

Related Links :

Home

12c RAC Index

12c RAC: ORA-15477: cannot communicate with the volume driver