EM 12c and Complex Networks @ Oracle Open World 2013

I have known Tony Kirn for over 8 years.  Not only we do share a common interest in Oracle Databases but we are also great buddies.

Yes my opinion may be considered bias, but he is highly regarded throughout the DBA community within Australia.

Tony has been working in IT since 1996 and has worked from the ground up from running cables and tinkering with hardware until finding his real passion for Oracle databases.

With his expansive experience across computing telephony, banking and finance, government industry he has shown appreciation to understand all the layers of which databases are rests upon.  This has allowed him to make some of the most critical and innovative decisions throughout his career as a database professional.

He has been involved in the first successful deployment of Enterprise Manager 10g in Australia at Telstra and recently was the tech lead responsible for delivering Enterprise Manager 12c.  He will be presenting at Oracle Open World this year.

So if you are attending I strongly recommend you to attend as he will provide some invaluable advice on his experience on deploying Enterprise Manager in complex networks.

The Oracle Open World Session Details are:
Session ID: CON3991
Session Title: Deploying Oracle Enterprise Manager 12c in a Complex Network Without Compromising Security
Date and Time: 24th September 2013, 17:15 - 18:15
Location:  Moscone Center, San Francisco USA
Venue / Room: Moscone South - Room 300

DBCA for RAC returns ORA-15025: could not open disk

An attempt to create a RAC database, the ASM fails when wrong group ownership is set.   It is notable that this issue may also impact standalone database running on ASM.  This issue is likely to occur if you are running different OS user for the Grid Infrastructure Software to your Database Software.

Summary:

Configuration	Grid Infrastructure (GRID)	Database (DB)
Software Owner (OS User)	grid	oracle
Primary OS Group	oinstall	oinstall
Admin Group	asmadmin	dba
ASM Group	asmdba	asmdba
ORACLE_HOME	/u01/app/11.2.0.3/grid	/u01/app/11.2.0.3/db

A. Steps to Reproduce Issue

1.  OS user for Grid software is "grid":

grid@lnx01:[GRID]$ id -a
uid=54320(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54324(asmdba),54325(asmadmin),54326(asmoper)

2. OS user for Database software is "oracle":

oracle@lnx01:[DB]$ id -a
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(asmdba)

NOTE: The "asmdba" OS group is assigned to both OS users "grid" and "oracle".

3. As oracle OS user execute "dbca" to create the database:
Method 1 - DBCA (interactive mode):

oracle@lnx01:[DB]$  dbca
Method 2 - run DBCA (silent mode):
oracle@lnx01:[DB]$  dbca -silent -responsefile dbca11203.rsp

B. Error Reported

DBCA reports the following error when attempting to create a database:
2013-05-02 17:59:22.197000 +10:00
NOTE: Loaded library: System
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA4"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA2"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA3"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk "/dev/oracleasm/disks/DATA1"
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
SUCCESS: diskgroup DATA was mounted
Errors in file /u01/app/diag/rdbms/blue/BLUE/trace/BLUE_ora_29895.trc
(incident=1401):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [],
[], [], [], [], [], [], [], []
Incident details in:
/u01/app/diag/rdbms/blue/BLUE/incident/incdir_1401/BLUE_ora_29895_i1401.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2013-05-02 17:59:24.033000 +10:00
Dumping diagnostic data in directory=[cdmp_20130502175924], requested by
(instance=1, osid=29895), summary=[incident=1401].
Errors in file /u01/app/diag/rdbms/blue/BLUE/trace/BLUE_ora_29895.trc
(incident=1402):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [],
[], [], [], [], []
Incident details in:
/u01/app/diag/rdbms/blue/BLUE/incident/incdir_1402/BLUE_ora_29895_i1402.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2013-05-02 17:59:25.037000 +10:00
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process
29895
Dumping diagnostic data in directory=[cdmp_20130502175925], requested by
(instance=1, osid=29895), summary=[incident=1402].
Shutting down instance (abort)
License high water mark = 2
USER (ospid: 29941): terminating the instance
Instance terminated by USER, pid = 29941
Instance shutdown complete


C. Solution

1. Verify oradism permission has the correct setuid under the ${GRID_HOME}/bin directory:
grid@lnx01:[GRID]$ ls -lrt /u01/app/11.2.0.3/grid/bin/oradism
-rwsr-x--- 1 root oinstall 71758 Sep 17  2011 /u01/app/11.2.0.3/grid/bin/oradism

2. Verify that the oracle permissions (6751) are correct under the ${GRID_HOME}/bin directory:
grid@lnx01:[GRID]$ ls -l /u01/app/11.2.0.3/grid/bin/oracle
-rwsr-s--x 1 grid oinstall 204090154 May  2 13:34 /u01/app/11.2.0.3/grid/bin/oracle

3. Verify that the oracle permissions (6751) are correct under the ${DB_HOME}/bin directory:
grid@lnx01:[GRID]$ ls -l /u01/app/11.2.0.3/db/bin/oracle
-rwsr-s--x 1 oracle oinstall 221309039 May  2 13:49 /u01/app/11.2.0.3/db/bin/oracle

NOTE: If permission is not correct then run as grid OS user under ${GRID_HOME}:
grid@lnx01:[GRID]$ /u01/app/11.2.0.3/grid/bin/setasmgidwrap o=/u01/app/11.2.0.3/db/bin/oracle

4. Change group ownership to asmadmin for oracle binary under ${DB_HOME}:
grid@lnx01:[GRID]$ chgrp asmadmin /u01/app/11.2.0.3/db/bin/oracle

5. Verify that the correct permission and ownership is fixed under ${DB_HOME}:
grid@lnx01:[GRID]$ ls -rlt /u01/app/11.2.0.3/db/bin/oracle
-rwxr-x--x 1 oracle asmadmin 221309039 May  2 13:49 /u01/app/11.2.0.3/db/bin/oracle

6. Verify the SS_ASM_GRP setting in config.c file under ${GRID_HOME}/rdbms/lib:
grid@lnx01:[GRID]$ cat /u01/app/11.2.0.3/grid/rdbms/lib/config.c|grep SS_ASM_GRP
#define SS_ASM_GRP "oinstall"
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};  

6. Verify the SS_ASM_GRP setting in config.c file under ${DB_HOME}/rdbms/lib:
grid@lnx01:[GRID]$ cat /u01/app/11.2.0.3/db/rdbms/lib/config.c|grep SS_ASM_GRP
#define SS_ASM_GRP ""
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};  

7. Edit the file config.c file under ${GRID_HOME}/rdbms/lib and ${DB_HOME}/rdbms/lib  and set the following: SS_ASM_GRP "asmdba"

8. Re-verify that the correct value is now set for SS_ASM_GRP under ${GRID_HOME} and ${DB_HOME}:
grid@lnx01:[GRID]$ cat ${GRID_HOME}/rdbms/lib/config.c|grep SS_ASM_GRP
#define SS_ASM_GRP "asmdba"
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};
oracle@lnx01:[DB]$ cat ${DB_HOME}/rdbms/lib/config.c|grep SS_ASM_GRP
#define SS_ASM_GRP "asmdba"
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};  

9. Relink Grid software as grid OS user using sudo:
grid@lnx01:[GRID]$ sudo /u01/app/11.2.0.3/grid/crs/install/rootcrs.pl -unlock
grid@lnx01:[GRID]$ relink all
sudo /u01/app/11.2.0.3/grid/crs/install/rootcrs.pl -patch
NOTE: Check log for errors: /u01/app/11.2.0.3/grid/install/relink.log

10. Make sure this is correct on all nodes in the cluster.  If not correct this issue using the steps above.

11. And then re-run "dbca"

Unable to Create ASM Disk Group for OCR and Voting Disk

When installing the Grid Infrastructure (GI) software for a 2 node RAC the other day, I came across an issue where the cluster was not able to create the ASM disk group for OCR and Voting Disk as it was not able to find the appropraite ASM disks.

Configuration Setting:

The following Grid Infrastructure (GI) ORACLE_HOME is:
GI_HOME=/u01/app/oracle/11.2.0.3/grid

A. Steps to Reproduce Issue

The issue occured when executing the root.sh with super user equivalent privileges:

oracle@lnx01:[GRID]$ sudo /u01/app/oracle/11.2.0.3/grid/root.sh

B. Error Reported

The following output was reported:
Performing root user operation for Oracle 11g
The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/oracle/11.2.0.3/grid
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/oracle/11.2.0.3/grid/crs/install/crsconfig_params
User ignored Prerequisites during installation
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'lnx01'
CRS-2676: Start of 'ora.cssdmonitor' on 'lnx01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'lnx01'
CRS-2672: Attempting to start 'ora.diskmon' on 'lnx01'
CRS-2676: Start of 'ora.diskmon' on 'lnx01' succeeded
CRS-2676: Start of 'ora.cssd' on 'lnx01' succeeded

Disk Group DG_CCF creation failed with the following message:
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification '/dev/asm_ccf3' matches no disks
ORA-15031: disk specification '/dev/asm_ccf2' matches no disks
ORA-15031: disk specification '/dev/asm_ccf1' matches no disks
This error is also found in the log:
${GI_HOME}/install/root_<HOST>_<TIMESTAMP>.log

C. Solution 

1. Verify partitions are visible and has the appropriate permission and ownership.  If not request your system administrator to correct issue:

oracle@lnx01:[GRID]$ ls -l /dev/sd*1

NOTE:  These devices may have a different name to /dev/sd*1, please check with your system administrator.

2. If running on Linux platform, mark asm disks using oracleasm (ie oracle asmlib is installed). Otherwise this step can be skipped.

oracle@lnx01:[GRID]$ oracleasm createdisk CCF1 /dev/sdb1
oracle@lnx01:[GRID]$ oracleasm createdisk CCF2 /dev/sdc1
oracle@lnx01:[GRID]$ oracleasm createdisk CCF3 /dev/sdd1

3. Edit the ${GI_HOME}/crs/install/crsconfig_params file and correct the path for ASM disks.

Example:
oracle.install.asm.diskGroup.disks=/dev/oracleasm/disks/CCF1,/dev/oracleasm/disks/CCF2,/dev/oracleasm/disks/CCF3
oracle.install.asm.diskGroup.diskDiscoveryString=/dev/oracleasm/disks/*
NOTE:  If using oracleasm specify the /dev/oracleasm/disks path to disks.  Otherwise specify the path to psuedo device name.  Please check with your system administrator for device name allocated for ASM disks.

4. Re-execute the root.sh script as super equivalent user.

oracle@lnx01:[GRID]$ sudo /u01/app/oracle/11.2.0.3/grid/root.sh
5. The execution of the root.sh script should now complete successfully.

Creating Multiple TNS Aliases using a Response file

I often here DBAs run into TNS issues rambling on about:

“I can’t believe there was a missing bracket in the TNS entry” 

or

“The syntax was incorrect in the TNS”. 

Yes I admit, I have been one of those DBAs who has hacked away at the tnsnames.ora and has faced this issue in past. Although not widely known or used, there are solutions to this issue.

Solution A:
You can use the netca wizard, if you prefer to click away, however you need to ensure that you have X session enabled.

Solution B: (I prefer this option!)
A quicker and easy way of creating multiple entries for TNS is to use the netca response file with silent mode.

You can use the netca response file to create listener(s) as well but in this example, we will concentrate on looking at creating TNS aliases (or NET service) using a response file. This is not a new feature and has been available in previous versions of Oracle. 1. I want to create the following TNS alias (or NET service) entries for the following:

TNS Alias	Net Service	Protocol	Hostname	Port
BLUE	BLUE	TCP	lnx121	1521
GREEN	GREEN	TCP	lnx122	1521

2. Take note of the following parameters

NSN_NUMBER – This is the number TNS alias(es) you want to create
NSN_NAMES – List all the TNS alias(es) names
NSN_SERVICE – List all the TNS service(s) associated with the TNS alias(es)
NSN_PROTOCOLS – List the protocol,host and port for each TNS alias

3. The following response file I will create is called 11203_netca.rsp and I have configured it as below:

##################### 11203_netca.rsp ######################
[GENERAL]
RESPONSEFILE_VERSION="11.2"
CREATE_TYPE="CUSTOM"
[oracle.net.ca]
INSTALL_TYPE=""custom""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
#--------------- Modify settings in this section ---------------
NSN_NUMBER=2
NSN_NAMES={"BLUE","GREEN"}
NSN_SERVICE={"BLUE","GREEN"}
NSN_PROTOCOLS={"TCP;lnx121;1521","TCP;lnx122;1521"}
#---------------------------------------------------------------
#######################################################
4. Now execute the netca utility in silent mode using the response file (eg. 11203_netca.rsp)

oracle@lnx121:[BLUE]$ netca /silent /responseFile /home/oracle/11203_netca.rsp

Output:

Parsing command line arguments:
Parameter "silent" = true
Parameter "responsefile" = /home/oracle/11203_netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Warning: Oracle Restart is not running.
Listener configuration will not be registered/unregistered with Oracle Restart.
Default local naming configuration complete.
Created net service name: BLUE
Default local naming configuration complete.
Created net service name: GREEN
Profile configuration complete.
Oracle Net Services configuration successful. The exit code is 0

5. Verify that the TNS aliases for BLUE and GREEN is now created in tnsnames.ora:

oracle@lnx121:[BLUE]$ cat $TNS_ADMIN/tnsnames.ora

Output:

# tnsnames.ora Network Configuration File: /u01/app/oracle/11.2.0.3/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
BLUE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx121)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BLUE)
    )
  )
 
GREEN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx122)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = GREEN)
    )
  )

6. Perform a tnsping for each TNS alias, to confirm entry is working.

a) TNS alias - BLUE 

oracle@lnx121:[BLUE]$ tnsping BLUE

Output:

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2013 11:13:30
 
Copyright (c) 1997, 2011, Oracle. All rights reserved.
 
Used parameter files:
/u01/app/oracle/11.2.0.3/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = lnx121)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =BLUE)))
OK (0 msec)

a) TNS alias - GREEN

oracle@lnx121:[BLUE]$ tnsping GREEN

Output:

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-MAY-2013 11:14:21
 
Copyright (c) 1997, 2011, Oracle. All rights reserved.
 
Used parameter files:
/u01/app/oracle/11.2.0.3/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = lnx122)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =GREEN)))
OK (0 msec)

7. This now completes the TNS alias configuration.

Sessions and Events in the Database

Active Sessions 

When you want to do a verification of current activity in the database, the following sos_sessions_active.sql script will list information on active sessions, blockers, associated events, impacted objects, users, program and services that are currently running across all instance(s) in the database (i.e. this script can run against a single instance or RAC database).

An example of this report can be found here:
(Click on image to zoom in)

Historical Sessions

At times, identifying performance issues in real time can be challenging. By the time the DBA is called upon to investigate the issue disappears. Rather then waiting for the issue to resurface, with Active Session History (ASH) introduced in 10g, the feature has become invaluable source to allow DBAs to perform post analysis and of sessions captured in the past.

Using the sos_sessions_history.sql script, by specifying the time period you wish to review, the report will return details on sessions and associated events.

NOTE: The columns “machine” and “sql_opname” is not available to be reported against DBA_HIST_ACTIVE_SESS_HISTORY in version 10g and 11.1.  So the sos_sessions_history.sql script will be required to edited to remove these columns from the query is executing against versions earlier then 11.2. 

A snippet from a sample output can be found here: (Click on image to zoom in)