Wednesday, July 26, 2017

Configure SQL Server 2016 Always On with Core Version of Windows Server 2016 without Active Directory Domain

This is a step by step demonstration to help database administrator deploy SQL Server 2016 Always On high availability solution on core version of Windows server 2016 without Active Directory domain. The demonstration environment will be created as following,

Tuesday, February 21, 2017

Oracle Rules Manager 11.2.0.4.0 INVALID with invalid JAVA Classes

Oracle database 11.2.0.4 was created with INVALID component 'Oracle Rules Manager',  
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             INVALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID

Find out invalid objects owned by schema 'EXFSYS' which could INVALID the component Oracle Rules Manager,
system@DB01> select object_name,object_type,status
  2  from dba_objects
  3  where owner='EXFSYS' and status='INVALID';
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
/53ad31cb_TagsLocator          JAVA CLASS          INVALID
/357abd97_XPathFilter          JAVA CLASS          INVALID

Check errors of found invalid JAVA CLASSes,
system@DB01> select name,text
  2  from dba_errors
  3  where owner='EXFSYS' and name in ('/53ad31cb_TagsLocator','/357abd97_XPathFilter');
NAME                      TEXT
------------------------- ------------------------------------------------------------
/357abd97_XPathFilter     ORA-29534: referenced object EXFSYS.oracle/expfil/xpath/TagsLocator could not be resolved
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/DOMParser could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLParseException could not be found
/53ad31cb_TagsLocator     ORA-29521: referenced name oracle/xml/parser/v2/XMLDocument could not be found

Oracle Rules Manager is in INVALID status because the referenced object/names created by installing XDK cannot be resolved/found. EXFSYS (owner of Oracle Rules Manager) JAVA Classes are dependent on XDK. Therefore installing XDK will validate Oracle Rules Manager,
conn / as sysdba
@?/xdk/admin/initxml.sql
@?/rdbms/admin/utlrp.sql

Check component status,
system@DB01> select schema,comp_name,version,status from dba_registry;
SCHEMA                         COMP_NAME                                          VERSION                STATUS
------------------------------ -------------------------------------------------- ------------------------------
APEX_050000                    Oracle Application Express                         5.0.3.00.03            VALID
EXFSYS                         Oracle Expression Filter                           11.2.0.4.0             VALID
EXFSYS                         Oracle Rules Manager                               11.2.0.4.0             VALID
XDB                            Oracle XML Database                                11.2.0.4.0             VALID
WMSYS                          Oracle Workspace Manager                           11.2.0.4.0             VALID
SYS                            Oracle Database Catalog Views                      11.2.0.4.0             VALID
SYS                            Oracle Database Packages and Types                 11.2.0.4.0             VALID
SYS                            JServer JAVA Virtual Machine                       11.2.0.4.0             VALID


Thursday, January 26, 2017

OMS version not checked yet while emctl upload agent

After OEM managed host was bounced, the agent status shows 'DOWN' in OEM. It happened because old version agent which pointed to old OMS server was started.

Find out if OEM agent is running on target,
$ ps -ef | grep emagent
  oracle 63111344 41484628   0 14:10:15  pts/0  0:00 grep emagent
  oracle  7143666        1   0 13:55:39  pts/4  0:00 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/perl/bin/perl /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emwd.pl agent /u01/app/oracle/product/12.1.0/agent/agent_inst/sysman/log/emagent.nohup
  oracle 26280338  7143666   0 13:55:51  pts/4  0:06 /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdk/bin/java -Xmx160M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=7143666 -cp /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jdbc/lib/ojdbc5.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/ucp/lib/ucp.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.http_client_11.1.1.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/xmlparserv2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/jsch.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/lib/optic.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.dms_11.1.1/dms.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/modules/oracle.odl_11.1.1/ojdl2.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/jlib/gcagent_core.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain

There is an instance of OEM agent running from Oracle Home '/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0'. Test the communication between managed host and the Management Service,
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error:full upload has failed: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS related errors. (OMS_DOWN)

$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl pingOMS
Oracle Enterprise Manager Cloud Control 12c Release 4 
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: Failure connecting to https://oem.dbaplus.ca:1159/empbs/upload , err Connection refused

Command 'emctl upload agent' returned error 'OMS version not checked yet', and 'emctl pingOMS' is complaining that OMS server cannot be connected. However, current OMS server is oms.dbaplus.ca and not oem.dbaplus.ca which is retired. Apparently, bouncing host automatically started an old (obsolete) agent which worked with old OMS server.

Let's fix it,
$ cat /etc/oragchomelist

/u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0:/u01/app/oracle/product/12.1.0/agent/agent_inst

/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst

The first entry in file /etc/oragchomelist makes OS start old agent (version 12.1.0.4), remove it from /etc/oragchomelist and restart host. After host is up, check agent status,
$ cat /etc/oragchomelist
/u01/app/oracle/product/em/agent/agent_13.2.0.0.0:/u01/app/oracle/product/em/agent/agent_inst

$ ps -ef | grep emagent
  oracle 57082074 41484628   0 16:21:18  pts/0  0:00 grep emagent
  oracle 63766548        1   0 15:00:53  pts/0  0:00 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/perl/bin/perl /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emwd.pl agent /u01/app/oracle/product/em/agent/agent_inst/sysman/log/emagent.nohup
  oracle 29950448 63766548   0 15:01:00  pts/0  1:20 /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/jdk/bin/java -Xmx183M -server -Djava.security.egd=file:///dev/./urandom -Dsun.lang.ClassLoader.allowArraySyntax=true -Xgcpolicy:gencon -Dwatchdog.pid=63766548 -cp /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jdbc/lib/ojdbc7.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/ucp/lib/ucp.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/jsch-0.1.53.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/com.oracle.http_client.http_client_12.1.3.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.xdk_12.1.3/xmlparserv2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.dms_12.1.3/dms.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/oracle_common/modules/oracle.odl_12.1.3/ojdl2.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/lib/optic.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/log4j-core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib/gcagent_core.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK-intg.jar:/u01/app/oracle/product/em/agent/agent_13.2.0.0.0/sysman/jlib/emagentSDK.jar oracle.sysman.gcagent.tmmain.TMMain

$ /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

$ /u01/app/oracle/product/em/agent/agent _13.2.0.0.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 13.2.0.0.0
OMS Version            : 13.2.0.0.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/em/agent/agent_inst
Agent Log Directory    : /u01/app/oracle/product/em/agent/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0
Core JAR Location      : /u01/app/oracle/product/em/agent/agent_13.2.0.0.0/jlib
Agent Process ID       : 11665618
Parent Process ID      : 13238612
Agent URL              : https://host01.dbaplus.ca:3872/emd/main/
Local Agent URL in NAT : https://host01.dbaplus.ca:3872/emd/main/
Repository URL         : https://oms.dbaplus.ca:4903/empbs/upload
Started at             : 2017-01-26 14:25:33
Started by user        : oracle
Operating System       : AIX version 7.1 (ppc64)
Number of Targets      : 23
Last Reload            : (none)
Last successful upload                       : 2017-01-26 14:27:22
Last attempted upload                        : 2017-01-26 14:27:22
Total Megabytes of XML files uploaded so far : 0.62
Number of XML files pending upload           : 734
Size of XML files pending upload(MB)         : 0.31
Available disk space on upload filesystem    : 59.62%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2017-01-26 14:26:26
Last successful heartbeat to OMS             : 2017-01-26 14:26:26
Next scheduled heartbeat to OMS              : 2017-01-26 14:27:28

---------------------------------------------------------------
Agent is Running and Ready

Agent is running on managed host, and it also shows 'UP' in OEM.

Monday, August 8, 2016

Automated SQL Tuning Task Failed with ORA-00600: [kpdbSwitchPreRestore: Txn]

Database: Oracle 12.1.0.2 RAC + Proactive Bundle Patch 12.1.0.2.160419
Operation System:  AIX 7.1


Automated SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK) failed and alert log shows errors:
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
2016-08-07 22:00:21.020000 -05:00
 <<Message truncated>>
2016-08-07 22:10:53.145000 -05:00
Errors in file /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/trace/CDB01_1_j004_31719682.trc  (incident=109991) (PDBNAME=PDB01):
ORA-00600: internal error code, arguments: [kpdbSwitchPreRestore: txn], [0x7000101ECFA4F00], [0], [2685403136], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/cdb01/CDB01_1/incident/incdir_109991/CDB01_1_j004_31719682_i109991.trc
2016-08-07 22:11:11.467000 -05:00
Dumping diagnostic data in directory=[cdmp_201608071221111], requested by (instance=1, osid=85983625 (J004)), summary=[incident=109991].


The issue was not seen in non-cdb databases. According to Oracle Support explanation, the task is executed from a pluggable database but also accessing the container database through dblink to tune a remote query which triggers a bug. The bug can be fixed by patch 21283337 if it is available for your platform.

Before the patch is available or the Automated SQL Tuning Task is not really needed, as a workaround, the task (client_name is 'sql tuning advisor' in view dba_autotask_client) can be disabled,
system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                ENABLED

system@PDB01> begin
  2  dbms_auto_task_admin.disable(client_name=>'SQL TUNING ADVISOR',
  3                               operation=>null,
  4                               window_name=>null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

system@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
sql tuning advisor                                               DISABLED
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED

system@PDB01> select client_name,operation_name,status from dba_autotask_operation;

CLIENT_NAME                              OPERATION_NAME                           STATUS
---------------------------------------- ---------------------------------------- --------
auto optimizer stats collection          auto optimizer stats job                 ENABLED
auto space advisor                       auto space advisor job                   ENABLED
sql tuning advisor                       automatic sql tuning task                DISABLED


Usage notes for dbms_auto_task_admin.disable,

 * If operation and window_name are both NULL, the client is disabled.
 * If operation is not NULL, window_name is ignored and the operation is disabled
 * If operation is NULL and window_name is not NULL, the client is disabled in the specified window.

Note: If disabling the task as workaround, we have to disable it in all containers (PDB & root),

SYSTEM@PDB01> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               DISABLED

SYSTEM@PDB01> alter session set container=cdb$root;

Session altered.

SYSTEM@CDB$ROOT> select client_name,status from dba_autotask_client;

CLIENT_NAME                                                      STATUS  
---------------------------------------------------------------- --------
auto space advisor                                               ENABLED
auto optimizer stats collection                                  ENABLED
sql tuning advisor                                               ENABLED

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS 
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               ENABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

SYSTEM@CDB$ROOT> exec dbms_auto_task_admin.disable('SQL TUNING ADVISOR',null,null);

PL/SQL procedure successfully completed.

SYSTEM@CDB$ROOT> select con_id,client_name,status from cdb_autotask_client;

    CON_ID CLIENT_NAME                                                      STATUS  
---------- ---------------------------------------------------------------- --------

         1 auto optimizer stats collection                                  ENABLED
         1 auto space advisor                                               ENABLED
         1 sql tuning advisor                                               DISABLED
         3 auto optimizer stats collection                                  ENABLED
         3 auto space advisor                                               ENABLED
         3 sql tuning advisor                                               DISABLED

6 rows selected.

Monday, March 21, 2016

RMAN got ORA-00245 while backing up RAC control file

When backing up control file for RAC databases, you could got ORA-00245 as following,
RMAN> backup device type sbt current controlfile ;

Starting backup at 2016-03-20 19:08:49
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1941 instance=racdb_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 03/20/2016 19:08:50
ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

In order to have a consistent version of the control file, RMAN creates a snapshot when asked to back up the control file,
then copy the snapshot control file to backup media.  The default file name of the snapshot is system-dependent, each instance
of same RAC database has default file name,
[oracle@host01 ~]$ export ORACLE_SID=racdb_1
[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016

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

connected to target database: racdb (DBID=435209035)

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_1.f'; # default


[oracle@host02]$ export ORACLE_SID=racdb_2
[oracle@host02]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 21 16:54:07 2016

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

connected to target database: racdb (DBID=435209035)

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name racdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_racdb_2.f'; # default

Depending on whether the RMAN session, which creates snapshot file, is connected to instance racdb_1 running on node host01 or instance racdb_2
running on node host02, the snapshot file name will be snapcf_racdb_1.f or snapcf_racdb_2.f respectively.

When RMAN backs up the control file and the snapshot file is create on other node,  ORA-00245 will be raised up. To avoid the error, the
RMAN Snapshot Control File Location has to be configured on shared storage which is accessible by all nodes in the cluster (all instances of
the RAC databases),
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/racdb/snapcf_racdb.f';
new RMAN configuration parameters are successfully stored

Friday, October 2, 2015

Invalid password while logging 12c ASM instance

Customer complains that he cannot log into 12c ASM instance because of invalid username/password, but he is pretty sure username and password are correct.
$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app
$
$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 2 10:26:38 2015

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

SQL> connect sys as sysasm
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Try to log in through OS authentication and reset the password:
SQL> conn / as sysasm
Connected.

SQL> alter user sys identified by abc123;
alter user sys identified by abc123
                             *
ERROR at line 1:
ORA-01918: user 'INTERNAL' does not exist

SQL> select * from v$pwfile_users;

no rows selected

Looks like the password file is missing:
$ ls -l $ORACLE_HOME/dbs
total 18
-rw-rw----   1 oragrid  oracle      2180 Jul 24 13:32 ab_+ASM.dat
-rw-rw----   1 oragrid  oracle      1544 Oct  2 10:28 hc_+ASM.dat
-rw-r--r--   1 oragrid  oracle      2992 Feb  3  2012 init.ora

Ok, the password file is not there as before. And we know it can be stored in ASM diskgroup from 12c, check if it is the case:
$ srvctl config asm -a
ASM home: 
Password file:
ASM listener: LISTENER
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.885907629
ASM diskgroup discovery string:
ASM is enabled.
ASM is individually enabled on nodes:
ASM is individually disabled on nodes:
$

The entry 'Password file' is empty, it is not expected configuration if password file does not exist in $ORACLE_HOME/dbs. Let us see if there is password file in ASM:
$ asmcmd
ASMCMD> ls -l
State    Type    Rebal  Name
MOUNTED  EXTERN  N      DATA/
MOUNTED  EXTERN  N      FRA/
ASMCMD> cd DATA
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    ASM/
                                            Y    DB01/
PASSWORD  UNPROT  COARSE   JUL 14 09:00:00  N    orapwasm => +DATA/ASM/PASSWORD/pwdasm.256.885030391
ASMCMD> exit

The password file 'orapwasm' is in 'DATA' diskgroup, update Oracle Restart registry:
$ srvctl modify asm -pwfile +DATA/orapwasm
$ srvctl config asm -a
ASM home: 
Password file: +DATA/orapwasm
ASM listener: LISTENER
Spfile: +DATA/ASM/ASMPARAMETERFILE/registry.253.885907629
ASM diskgroup discovery string:
ASM is enabled.
ASM is individually enabled on nodes:
ASM is individually disabled on nodes:

try logging again:
$ sqlplus sys as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 16 11:16:04 2015

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  TRUE  FALSE FALSE FALSE          0
ASMSNMP                        TRUE  FALSE FALSE FALSE FALSE FALSE          0

It works.

Monday, September 21, 2015

Using Corrective Action to capture and email database status

As DBA, were you asked questions like:
I was blocked by other database sessions last night, could you please check who blocked me and what he was doing at that time?
My query ran into an temp tablespace space error, could you please check who was using large temporary space at that moment?
They are not hard questions, but you will find that it is not easy to answer them unless you have proactive consideration.

Here, I am going to show how easy it is to do that using Corrective Action in Oracle Enterprise Manager Cloud Control 12c.

1. Setting Up a Mail Server for Notifications
Log into OEM, from the [Setup] menu, select [Notifications], then select [Notification Methods]



2. Setting Up E-mail for Yourself
From [username] drop-down menu, select [Enterprise Manager Password & E-mail]. Click [Add Another Row] to create a new e-mail entry field in the E-mail Addresses table. Specify the e-mail associated with your Enterprise Manager account. All e-mail notifications you receive from  Enterprise Manager will be sent to the e-mail addresses you specify.



3. Set up a Notification Schedule 
Once you have defined your e-mail notification addresses, you will need to define a notification schedule.
From [Setup] menu, select [Notifications], then select [My Notification Schedule]. Follow the directions on the Notification Schedule page to specify when you want to receive e-mails


4.  Creating Incident Rules for the events you want to monitor
From the [Setup] menu, select [Incidents], then select [Incident Rules]. Click [Create Rule Set...]


In [Create Rule Set] page,  specify the [Name], [Description], and the [Targets] to which the rules set should apply. Then click [Create...] to display [Select Type of Rule to Create] window, select [Incoming Events and updates to events], click [Continue].


In [Create New Rule: Select Events] page, select [Specific events of type Metric Alert], click [Add] to display [Select Specific Metric Alert] page, select [User Block] for metric and appropriate options for [Severity] and [Corrective action status], then click [Ok]



Back to [Create New Rule: Select Events] page, click [Next] to display [Create New Rule: Add Actions] page. Click [Add] to display
[Add Actions] page, specify your username for [E-mail To] Notificatios, then click [Continue] to return to [Create New Rule: Add Actions] page. Click [Next] to specify Name and Description for new rule, then click [Next] to display [Create New Rule: Review] page, click [Continue]



Back to [Create Rule Set] page, click [Save] to return to [Incident Rules - All Enterprise Rules] page.



5.     Create Corrective Actions

In database home page, from [Oracle Database] menu, select [Monitoring], then click [Metric and Collection Settings] to display [Metric and Collection Settings]

In [Metric and Collection Settings], click [Disabled] in the [Collection Schedule] field after metric [User Block] to enable metric collection. Then click edit image after metric [Blocking Session Count] to display [Edit Advanced Settings: Blocking Session Count].
In [Edit Advanced Settings: Blocking Session Count], select [All others] then click [Edit] button to display [Edit Advanced Settings: Blocking Session Count for All others]

In  [Edit Advanced Settings: Blocking Session Count for All others], enter 2 for [Warning Threshold] and 5 for [Critical Threshold], then click [Add] button beside [Warning].

In [Add Corrective Action] page, select [SQL Script] from dropbox then click [Continue] button to display [Create 'SQL Script' Corrective Action].

In [Create 'SQL Script' Corrective Action]
* Enter Name and Description in tab [Gerneral]
* Enter SQL statements in tab [Parameters], here, SQL statements are what you want to run immediately after the alert is generated.
* Enter database credential and host credential information.



Then click [Continue] button and back to [Edit Advanced Settings: Blocking Session Count] page, then click [Continue], then click [Continue] again and back to [Metric and Collection Settings], then click [Ok] button to save changes.

6.    Make sure Notification Email include message of Corrective Actions' status

From [Setup] menu, select [Notifications], then select [Customize Email Formats]


Click [Customize] to bring up following window and make sure Corrective Actions' section is there (It is default configuration)


Now, you will receive a email when blocked sessions are more than 2 sessions and the query result of SQL statements created in Corrective Action will be included in the email.


Thursday, July 2, 2015

Agent status shows 'Availability Evaluation Error' on EM 12c Console

Background
During deploying agent 12.1.0.4 to new host for EM 12.1.0.4, the local firewall was enabled by accident and blocked incoming access. The deployment is successful. However, OEM shows error:
'Unable to connect to the agent at https://host01.dbaplus.ca:3872/emd/main/ [Connection establishment timed out])'

After stopping firewall on the new host, agent status is shown 'Availability Evaluation Error' on EM 12c Console.
Diagnose
On the new host:
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version          : 12.1.0.4.0
OMS Version            : 12.1.0.4.0
Protocol Version       : 12.1.0.1.0
Agent Home             : /u01/app/oracle/product/12.1.0/agent/agent_inst
Agent Log Directory    : /u01/app/oracle/product/12.1.0/agent/agent_inst/sysman/log
Agent Binaries         : /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0
Agent Process ID       : 8366
Parent Process ID      : 8280
Agent URL              : https://host01.dbaplus.ca:3872/emd/main/
Local Agent URL in NAT : https://host01.dbaplus.ca:3872/emd/main/
Repository URL         : https://omshost.dbaplus.ca:1159/empbs/upload
Started at             : 2015-07-02 11:29:46
Started by user        : oracle
Operating System       : Linux version 2.6.39-400.17.1.el6uek.x86_64 (amd64)
Last Reload            : (none)
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far : 0
Number of XML files pending upload           : 0
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 21.46%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2015-07-02 11:30:49
Last successful heartbeat to OMS             : 2015-07-02 11:30:49
Next scheduled heartbeat to OMS              : 2015-07-02 11:31:49

---------------------------------------------------------------
Agent is Running and Ready

We can find that 'Last successful upload' is '(none)', it is supposed to be a valid date & time. Try to manually upload:
$ /u01/app/oracle/product/12.1.0/agent/core/12.1.0.4.0/bin/emctl upload agent
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

It does not help. 'Last successful upload' is still '(none)' and agent status is still shown 'Availability Evaluation Error'.
Try to re-synchronize the agent on OMS server:
$ . oraenv
ORACLE_SID = [oracle] ? OMS
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/OMS/oms
The Oracle base has been set to 
$ which emcli
/u01/app/oracle/product/12.1.0/OMS/oms/bin/emcli
$ 
$ emcli  login -username=SYSMAN
Enter password 

Login successful
$ emcli sync
Synchronized successfully
$ emcli resyncAgent -agent=host01.dbaplus.ca:3872
Resync job RESYNC_20150702142805361 successfully submitted

Now agent is shown UP on EM 12c console.

Wednesday, June 10, 2015

Failed to retrieve the password file location used by ASM asm

After Oracle Restart (Grid Infrastructure standalone) is upgraded from 11g to 12c, srvctl get errors:
$ srvctl config asm
ASM home: 
PRCA-1057 : Failed to retrieve the password file location used by ASM asm
PRCR-1097 : Resource attribute not found: PWFILE

Check environment and password file:
$ echo $ORACLE_HOME
/u01/app/grid/product/12.1.0/grid

$ ls -l /u01/app/grid/product/12.1.0/grid/dbs
total 28
-rw-rw---- 1 grid oinstall 2783 Jun  9 14:27 ab_+ASM.dat
-rw-rw---- 1 grid oinstall 1544 Jun  9 14:27 hc_+ASM.dat
-rw-r--r-- 1 grid oinstall 2992 Feb  3  2012 init.ora
-rw-r----- 1 grid oinstall 8704 Jun  9 14:10 orapw+ASM
-rw-r----- 1 grid oinstall 1536 Jun  9 14:27 spfile+ASM.ora

The password file was created and ORACLE_HOME is set correctly. Checked with DBA, the upgrade was done successfully. There was no errors/exceptions during upgrade.
Oracle Support explains that:
12c ASM instance has a attribute called PWFILE(password file), but 11g ASM does not. Looks like the upgrade utility does not create the new attribute for an ASM instance which is upgraded from 11g.
Try to manually re-create the ora.asm resource with -pwfile option:
$ crsctl status resource ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE
STATE=ONLINE on host01

$ srvctl stop asm
PRCR-1065 : Failed to stop resource ora.asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.DATA.dg', but the force option was not specified
$ srvctl stop asm -f
$ 
$ srvctl config asm
PRCR-1001 : Resource ora.asm does not exist
$ 
$ srvctl add asm -listener LISTENER -spfile /u01/app/grid/product/12.1.0/grid/dbs/spfile+ASM.ora -pwfile /u01/app/grid/product/12.1.0/grid/dbs/orapw+ASM

Test if it is fixed:
$ srvctl sconfig asm
ASM home: 
Password file: /u01/app/grid/product/12.1.0/grid/dbs/orapw+ASM
ASM listener: LISTENER
Spfile: /u01/app/grid/product/12.1.0/grid/dbs/spfile+ASM.ora
ASM diskgroup discovery string: ++no-value-at-resource-creation--never-updated-through-ASM++

There is no error any more.

Wednesday, April 29, 2015

Insufficient archive log space caused ORA-03113

Oracle 11g database was shut down for hardware maintenance. Unfortunately, the database can not be started after maintenance because of error ORA-03113.

$ srvctl sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 27 14:10:05 2015

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 33947688
Session ID: 749 Serial number: 3


Apparently, something happended while instance status is changed to OPEN from MOUNT. Check alert log file:

ARC3 started with pid=30, OS id=51183782
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+FRA2'
Errors in file /u01/app/oracle/diag/rdbms/db01/DB01/trace/DB01_ora_45220056.trc:
ORA-16038: log 3 sequence# 32464 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+REDO1/db01/onlinelog/group_3.310.857090421'
ORA-00312: online log 3 thread 1: '+REDO2/db01/onlinelog/group_3.310.857090421'
USER (ospid: 45220056): terminating the instance due to error 16038


Instance is terminated due to error 16038: log 3 sequence# 32464 cannot be archived. Redo log cannot be archived because the recovery area is 100% used.

Backup archive log and release space by running:

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 27 14:17:52 2015

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 835104768 bytes

Fixed Size 2226096 bytes
Variable Size 532678736 bytes
Database Buffers 276824064 bytes
Redo Buffers 23375872 bytes

RMAN> backup archivelog all delete all input;

... ...


Try to open database:

RMAN> alter database open;

database opened


Database is opened successfully.

Friday, February 13, 2015

Integrating BI Publisher with Enterprise Manager using the configureBIP Script

As we know, Oracle has deprecated Information Publisher (IP) Reports for Enterprise Manager 12c and introduce new report tool BI Publisher (BIP) Enterprise Reports. Here, I am going to configure BIP after OEM 12c (12.1.0.4) has been installed and used for a while.

Configure environment and set ORACLE_HOME to OMS installation path:
$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/oms12104/oms
$ export PATH=$ORACLE_HOME/bin:$PATH
$ which emctl
/u01/app/oracle/product/12.1.0/oms12104/oms/bin/emctl

Backup OEM using command: emctl exportconfig oms
$ emctl exportconfig oms -dir /u01/app/oracle/OMS_BACKUP -oms_only -keep_host
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Enter Enterprise Manager Root (SYSMAN) Password :
ExportConfig started...
Backup directory is /u01/app/oracle/OMS_BACKUP
Performing OMS backup...
Exporting emoms properties...
Exporting secure properties...

Export has determined that the OMS is not fronted
by an SLB. You have chosen to export the local
hostname. The exported data may ONLY be imported
on a host with the same hostname. Please see the
EM Advanced Configuration Guide for more details.

Warning: Could not export files for module bip: Unable to read file /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/embip.properties for exporting
Exporting configuration for pluggable modules...
Preparing archive file...
Backup has been written to file: /u01/app/oracle/OMS_BACKUP/opf_OMS_20150210_111433.bka

The export file contains sensitive data.
You must keep it secure.

ExportConfig completed successfully!

Configure BIP using script configureBIP
$ cd $ORACLE_HOME/bin
$
$ ./configureBIP
Configuring BI Publisher Version "11.1.1.7.0" to work with Enterprise Manager
Logging started at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log.
Before this command is run, a backup of Enterprise Manager should be performed using the :emctl exportconfig oms: command. Have you made a valid backup of Enterprise Manager (yes/no) [no] ? yes
Enter sysdba user name (sys):sys
Enter sysdba user password:
Enter Administration Server user password:
Configuring BI Publisher in Oracle Home located in /u01/app/oracle/product/12.1.0/oms12104/Oracle_BI1 ...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Creating Tablespaces
Validating and Creating Tablespaces
Repository Creation Utility - Create
Repository Create in progress.
Percent Complete: 0
Percent Complete: 10
Percent Complete: 30
Percent Complete: 50
Percent Complete: 50
Percent Complete: 100
Repository Creation Utility: Create - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.dbaplus.ca)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEMREP)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/emBIPLATFORM.log
Component schemas created:
Component Status Logfile
Business Intelligence Platform Success /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/biplatform.log

Repository Creation Utility - Create : Operation Completed
Successfully created SYSMAN_BIPLATFORM schema...
Enter an integer between 9701 and 49152 for the BI Publisher HTTP server port. (9701):
Enter an integer between 9702 and 49152 for the BI Publisher HTTPS server port. (9702):
Extending domain with BI Publisher. This operations can take some time. Do not interrupt this command while it is running...
Locking Enterprise Manager ...
OMS Console is locked. Access the console over HTTPS ports.
Restart OMS.
Restarting Enterprise Manager ...
Stopping Enterprise Manager, this can take some time ...
Failed to stop Enterprise Manager. Diagnostic code 1.
See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
Error extending domain
See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.

OMG, a bad luck! Check log file for what happened,
$ tail -20 /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log

[2015-02-10T11:30:51.192-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Stopping WebTier...[[

]]
[2015-02-10T11:30:55.221-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Error Occurred: Cannot run program "/u01/app/oracle/product/12.1.0/gc_inst/WebTierIH1/bin/opmnctl": error=12, Not enough space[[

]]
[2015-02-10T11:30:55.222-05:00] [sysman] [NOTIFICATION] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 17] [userId: oracle] [ecid: 0000KhoL3KH0rmjrh0r2E41KqYwd000004,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.ProcessOutputReader] [SRC_METHOD: run] Please check /u01/app/oracle/product/12.1.0/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details[[

]]
[2015-02-10T11:30:55.265-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: lockAndBounceEM] Failed to stop Enterprise Manager. Diagnostic code 1.
[2015-02-10T11:30:55.273-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: extendDomainWithBIP] See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
[2015-02-10T11:30:55.274-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Error extending domain
[2015-02-10T11:30:55.275-05:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: host01.dbaplus.ca] [nwaddr: 10.10.0.34] [tid: 10] [userId: oracle] [ecid: 0000KhoI3ci0rmjrh0r2E41KqYwd000000,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Fatal error:[[
java.lang.Exception: See log at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150210111722.log for details.
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:2370)
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1531)

At the begining of the error, I found messages: 'opmnctl', 'Not enough space', 'Failed to stop Enterprise Manager'. What a familiar look! Yes, it is familiar. Let's try to fix it following "Got ‘Not enought space’ while ‘emctl stop oms’". Then, restart the process. Of course, I have to clean/rollback the failed configuration first before re-running configureBIP.

Clean/rollback failed configureBIP run

Stop BIP server
$ emctl stop oms -bip_only
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.
Stopping BI Publisher Server...
BI Publisher Server Successfully Stopped
BI Publisher Server is Down

Unregister BIP from Enterprise Manager
$ emcli login -username=sysman
Enter password :

Login successful
$ emcli sync
Synchronized successfully

$ emcli unregister_bipublisher -force
Warning: BI Publisher was not registered for use with Enterprise Manager, no action taken.

BIP was not registered with Enterprise Manager yet, but it does not really matter, just go ahead.
Drop schema created by script configureBIP from Enterprise Manager repository database
$ sqlplus sys@OEMREP as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Fri Feb 13 08:39:01 2015

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters and Automatic Storage Management options

SQL> drop user SYSMAN_BIPLATFORM cascade;

User dropped.

SQL> select comp_id,COMP_NAME,owner,version,status from system.schema_version_registry where comp_id = 'BIPLATFORM';

COMP_ID COMP_NAME OWNER VERSION STATUS
--------------- ------------------------------ ------------------------------ ------------------------------ -----------
BIPLATFORM OracleBI and EPM SYSMAN_BIPLATFORM 11.1.1.7.0 VALID

SQL> delete from system.schema_version_registry where comp_id = 'BIPLATFORM';

1 row deleted.

SQL> commit;

Commit complete.

SQL> select comp_id,COMP_NAME,owner,version,status from system.schema_version_registry where comp_id = 'BIPLATFORM';

no rows selected

Re-run script configureBIP to configure BIP with Enterprise Manager
$ ./configureBIP
Configuring BI Publisher Version "11.1.1.7.0" to work with Enterprise Manager
Logging started at /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/bipca_20150213084523.log.
Before this command is run, a backup of Enterprise Manager should be performed using the :emctl exportconfig oms: command. Have you made a valid backup of Enterprise Manager (yes/no) [no] ? yes
Enter sysdba user name (sys):
Enter sysdba user password:
Enter Administration Server user password:
Configuring BI Publisher in Oracle Home located in /u01/app/oracle/product/12.1.0/oms12104/Oracle_BI1 ...
Processing command line ....
Repository Creation Utility - Checking Prerequisites
Checking Global Prerequisites
Repository Creation Utility - Checking Prerequisites
Checking Component Prerequisites
Repository Creation Utility - Creating Tablespaces
Validating and Creating Tablespaces
Repository Creation Utility - Create
Repository Create in progress.
Percent Complete: 0
Percent Complete: 10
Percent Complete: 30
Percent Complete: 50
Percent Complete: 50
Percent Complete: 100
Repository Creation Utility: Create - Completion Summary
Database details:
Connect Descriptor : (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host01.dbaplus.ca)(PORT=1521)))(LOAD_BALANCE=ON)(CONNECT_DATA=(SERVICE_NAME=OEMREP)))
Connected As : sys
Prefix for (prefixable) Schema Owners : SYSMAN
RCU Logfile : /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/emBIPLATFORM.log
Component schemas created:
Component Status Logfile
Business Intelligence Platform Success /u01/app/oracle/product/12.1.0/oms12104/oms/cfgtoollogs/bip/biplatform.log

Repository Creation Utility - Create : Operation Completed
Successfully created SYSMAN_BIPLATFORM schema...
Enter an integer between 9701 and 49152 for the BI Publisher HTTP server port. (9701):
Enter an integer between 9702 and 49152 for the BI Publisher HTTPS server port. (9702):
Extending domain with BI Publisher. This operations can take some time. Do not interrupt this command while it is running...
Locking Enterprise Manager ...
OMS Console is locked. Access the console over HTTPS ports.
Restart OMS.
Restarting Enterprise Manager ...
Stopping Enterprise Manager, this can take some time ...
Starting Enterprise Manager. This operation can take some time. Do not interrupt this command while it is running.
OMS Started Successfully
BI Publisher server named :BIP: running at https://host01.dbaplus.ca:9702/xmlpserver.
Registering BI Publisher with Enterprise Manager and deploying reports...
Performing automatic backup of Enterprise Manager using the command :emctl exportconfig oms:.
Successfully backed up Enterprise Manager. The backup file is located in the INSTANCE_HOME sysman backup directory.
Successfully setup BI Publisher with Enterprise Manager

Now, we can use BI Publisher to do report instead of Information Publisher.

Monday, February 9, 2015

Apply GI PSU 12.1.0.2.2 while using Software-Only installation of GI12.1.0.2

A software-only installation only copies the Oracle Grid Infrastructure for a standalone server binaries to the specified location. After the installation, Oracle Grid Infrastructure (GI) is not configured and HAS (GI standalone)/ CRS(GI cluster) stack is not up yet. Therefore, "optach auto" introduced the the PSU document can not be used to apply the patch.

1. Start Oracle Universal Installer as the Oracle Restart software owner user (grid)
[grid@host01 grid]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 415 MB.   Actual 82041 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-02-05_02-16-33PM. Please wait ...
[grid@host01 grid]$ You can find the log of this install session at: /u01/app/oraInventory/logs/installActions2015-02-05_02-16-33PM.log
... ...

2. In the Select Installation Option screen, select the Install Oracle Grid Infrastructure Software Only option to install Oracle Restart and Oracle ASM software only. Click Next.

3. In the Select Product Languages screen, select one or more languages. Move the languages from the Available Languages list to the Selected Languages list. Click Next.

4. In the Privileged Operating System Groups screen, select the name of the operating system group you created for the OSDBA group, the OSASM group, and the Oracle ASM operator group OSOPER. If you create only the dba group, then you can use that group for all three privileged groups. If you created a separate asmadmin group, then use that value for the OSASM group. Click Next.

5. In the Specify Installation Location screen, enter the following information, and click Next:
Oracle Base: Enter the location for the Oracle base directory. Do not include spaces in the path. Here, I am using: /u01/app/grid
Software Location: Accept the default value or enter the directory path in which you want to install the software.The directory path must not contain
spaces. Here, I am using /u01/app/grid/product/12.1.0/grid12102

6. In the pop-up window, the warning message is for cluster installation, click Yes to continue if the GI is installed as Standalone. Otherwise, Oracle Home location has to be out of ORACLE_BASE.

7. The Create Inventory screen is displayed only if this is the first time you are installing Oracle software on your system. Change the path for the Inventory Directory (e.g. /u01/app/oraInventory), if required. Select oinstall for the oraInventory Group Name, if required. Click Next.

8. The Perform Prerequisite Checks screen checks if the minimum system requirements are met to perform the Oracle Grid Infrastructure installation. If all the system requirements are met, then you are directed to the Summary screen. If an installation fails, you can review the error.

9. Review the contents of the Summary screen, and click Install.

10. The Install Product screen displays the progress of the Oracle Grid Infrastructure installation. Oracle Universal Installer prompts you to run the root.sh script and, if required, the orainstRoot.sh script as the root user to complete the installation. During this process, the Execute Configuration Scripts window appears. Do not click OK until you successfully apply the patch (PSU 12.1.0.2.2).

11. If required, run orainstRoot.sh script as the root user
[root@host01 app]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.

12. Install new version of OPatch
[grid@host01 tmp]$ cd /u01/app/grid/product/12.1.0/grid12102
[grid@host01 grid12102]$ OPatch/opatch version
OPatch Version: 12.1.0.1.3

OPatch succeeded.
[grid@host01 grid12102]$ mv OPatch OPatch.12.1.0.1.3
[grid@host01 grid12102]$ unzip /u01/app/media/p6880880_121010_Linux-x86-64.zip
Archive:  /u01/app/media/p6880880_121010_Linux-x86-64.zip
   creating: OPatch/
  inflating: OPatch/opatchdiag.bat
    ... ...
[grid@host01 grid12102]$ OPatch/opatch version
OPatch Version: 12.1.0.1.6

OPatch succeeded.

13. Extract the GI PSU 12.1.0.2.2 (Patch 19954978) into the empty stage directory as the GI software owner (grid), we will find four patched included in the PSU bundle: 19769473, 19769479, 19769480, 19872484
[grid@host01 grid12102]$ unzip -d /u01/app/media/ /u01/app/media/p19954978_121020_Linux-x86-64.zip
[grid@host01 grid12102]$ ls -l /u01/app/media/19954978
total 104
drwxr-xr-x. 5 grid oinstall  4096 Jan 16 04:30 19769473
drwxr-xr-x. 5 grid oinstall  4096 Jan 16 04:30 19769479
drwxrwxr-x. 4 grid oinstall  4096 Jan 16 04:30 19769480
drwxr-xr-x. 4 grid oinstall  4096 Jan 16 04:30 19872484
drwxr-xr-x. 2 grid oinstall  4096 Jan 16 04:30 automation
-rw-rw-r--. 1 grid oinstall  4987 Jan 16 05:53 bundle.xml
-rw-rw-r--. 1 grid oinstall 77081 Jan 19 12:32 README.html
-rw-r--r--. 1 grid oinstall     0 Jan 16 04:30 README.txt

14.  Apply patch to the newly installed 12.1.0.2 GI home as the GI software owner, since "opatch auto" cannot be used, we have to apply patches in the PSU bundle one by one using opatch apply,
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769479
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769473
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19872484
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769480
[grid@host01 ~]$ export ORACLE_HOME=/u01/app/grid/product/12.1.0/grid12102
[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769479
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19769479' to OH '/u01/app/grid/product/12.1.0/grid12102'
Verifying environment and performing prerequisite checks...
   ... ...
Verifying the update...
Patch 19769479 successfully applied
Log file location: /u01/app/grid/product/12.1.0/grid12102/cfgtoollogs/opatch/opatch2015-02-09_14-33-35PM_1.log

OPatch succeeded.

[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769473
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19769473' to OH '/u01/app/grid/product/12.1.0/grid12102'
Verifying environment and performing prerequisite checks...
   ... ...
Patching component oracle.usm, 12.1.0.2.0...

Verifying the update...
Patch 19769473 successfully applied
Log file location: /u01/app/grid/product/12.1.0/grid12102/cfgtoollogs/opatch/opatch2015-02-09_14-41-04PM_1.log

OPatch succeeded.
[grid@host01 ~]$
[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19872484
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19872484' to OH '/u01/app/grid/product/12.1.0/grid12102'
   ... ...
Patching component oracle.wlm.dbwlm, 12.1.0.2.0...

Verifying the update...
Patch 19872484 successfully applied
Log file location: /u01/app/grid/product/12.1.0/grid12102/cfgtoollogs/opatch/opatch2015-02-09_14-45-29PM_1.log

OPatch succeeded.
[grid@host01 ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/grid/product/12.1.0/grid12102 -local /u01/app/media/19954978/19769480
Oracle Interim Patch Installer version 12.1.0.1.6
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
   ... ...
Applying interim patch '19769480' to OH '/u01/app/grid/product/12.1.0/grid12102'
Verifying environment and performing prerequisite checks...
Patch 19769480: Optional component(s) missing : [ oracle.xdk, 12.1.0.2.0 ] , [ oracle.oraolap, 12.1.0.2.0 ]
   ... ...
OPatch succeeded.

15. Run root.sh as user root


[root@host01 bin]# /u01/app/grid/product/12.1.0/grid12102/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/grid/product/12.1.0/grid12102

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
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.

To configure Grid Infrastructure for a Stand-Alone Server run the following command as the root user:
/u01/app/grid/product/12.1.0/grid12102/perl/bin/perl -I/u01/app/grid/product/12.1.0/grid12102/perl/lib -I/u01/app/grid/product/12.1.0/grid12102    /crs/install /u01/app/grid/product/12.1.0/grid12102/crs/install/roothas.pl


To configure Grid Infrastructure for a Cluster execute the following command as grid user:
/u01/app/grid/product/12.1.0/grid12102/crs/config/config.sh
This command launches the Grid Infrastructure Configuration Wizard. The wizard also supports silent operation, and the parameters can be passed     through the response file that is available in the installation media.



16. Back to Oracle Universal Installer. In Execute Configuration Scripts screen, click Ok.
17. The Finish screen displays the installation status. Click Close to end the installation and exit Oracle Universal Installer.

References:
Supplemental Readme - Patch Installation and Deinstallation For 12.1.0.x.x GI PSU (Doc ID 1591616.1)
How to Apply a Grid Infrastructure Patch Before root script (root.sh or rootupgrade.sh) is Executed? (Doc ID 1410202.1)

Thursday, January 29, 2015

Database link cannot be dropped because of ORA-02024

When dropping database link got following error:
ORA-02024: database link not found.
SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
----------------- ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

SQL> drop database link REFRESH_DBLINK;
drop database link REFRESH_DBLINK
*
ERROR at line 1:
ORA-02024: database link not found

Can not find an existing database link? What if create a new link with same name?
SQL> create database link REFRESH_DBLINK
2 connect to system identified by Ax356 using 'DBPROD';

Database link created.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13
REFRESH_DBLINK.WORLD SYSTEM DBPROD 29-JAN-15

The name of new database link is appended with '.WORLD'. Did more test and found that all new created database link will be added suffix '.WORLD' in the name. Try DROP DATABASE LINK to see what is going to happen:
SQL> drop database link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
------------------------ ------------- ---------- ----------- ---------
REFRESH_DBLINK SYSTEM DBPROD 15-OCT-13

See, I was dropping REFRESH_DBLINK, but REFRESH_DBLINK.WORLD was dropped. I always get REFRESH_DBLINK.WORLD dropped no matter whether I include '.WORLD' in the DROP DATABASE LINK command. Why did we get the weird term '.WORLD'? It was popular long time ago as default database domain and often found in global_name & db_domain of the database:
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBPROD

SQL> show parameter db_domain

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
DBPROD.WORLD

It does be a part of database global_name. Does domain part in global_name affect database link name space? Unfortunately, it does. Delete the domain part from global_name:

 
SQL> alter database rename global_name to DBPROD;

Database altered.

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------------------
DBPROD.WORLD

Interesting, the ALTER DATABASE command does not help. Let us manually change global_name bypass ALTER DATABASE command,
Note: Take a complete consistent backup of the database before you continue:
SQL> conn / as sysdba
Connected.

SQL> select name,value$ from props$ where GLOBAL_name='GLOBAL_DB_NAME';

NAME VALUE$
------------------------------ --------------------------------------------------
GLOBAL_DB_NAME DBPROD.WORLD

SQL> update props$ set value$='DBPROD' where name='GLOBAL_DB_NAME';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from global_name;

GLOBAL_NAME
---------------------
DBPROD

Oracle never suggests direct update on props$. We are not supposed to do that. Hmmm, today is special :)

Drop the database link:
SQL> connect system

SQL> drop datbaetabase link REFRESH_DBLINK;

Database link dropped.

SQL> select * from user_db_links;

no rows selected

Finally, database link is really dropped.

Monday, January 26, 2015

TNSPING of 11g RAC database got very slow response

Business users complain it took several minutes to log into database. It used to take several seconds.

Confirmed informations as following:

Server: Solaris 10 SPARC + Oracle Database Server 11.2.0.3 RAC
Client: windows 7 + Oracle Client 11.2.0.3

Testing connectivity from workstation:
C:\>tnsping RACPD

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 26-JAN-2015 10:49:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\app\oracle\product\11.2.0\client11203\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host-scan.dbaplus.ca)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACPD)))
OK (120000 msec)

Feel like the SCAN listener(s) is/are alive and there is network issue. If it is network problem, the issue should be reproduced on all three IP addresses bound with SCAN name (host-scan.dbaplus.ca). Let's find IPs first:
C:\>nslookup host-scan.dbaplus.ca
Server: ns01.dbaplus.ca
Address: 10.10.2.253

Name: host-scan.dbaplus.ca
Addresses: 10.10.2.30
10.10.2.31
10.10.2.29

Checking the IP availability:
C:\>ping 10.10.2.30

Pinging 10.10.2.30 with 32 bytes of data:
Reply from 10.10.2.30: bytes=32 time=5ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252
Reply from 10.10.2.30: bytes=32 time<1ms TTL=252

Ping statistics for 10.10.2.30:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 5ms, Average = 1ms

C:\>ping 10.10.2.31

Pinging 10.10.2.31 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 10.10.2.31:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

C:\>ping 10.10.2.29

Pinging 10.10.2.29 with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Ping statistics for 10.10.2.29:
Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

Two of three SCAN virtual IPs are timed out, the other one (10.10.2.30) is working. It is not network problem. Log onto database servers:
$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/oracle
$
$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.FRA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE OFFLINE host02
ora.asm
ONLINE ONLINE host01 Started
ONLINE ONLINE host02 Started
ora.gsd
OFFLINE OFFLINE host01
OFFLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN2.lsnr
1 ONLINE OFFLINE
ora.LISTENER_SCAN3.lsnr
1 ONLINE OFFLINE
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE UNKNOWN host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.scan1.vip
1 ONLINE ONLINE host02
ora.scan2.vip
1 ONLINE UNKNOWN host01
ora.scan3.vip
1 ONLINE UNKNOWN host01

SCAN VIP scan2 & scan3 are in UNKNOWN status, corresponding SCAN listeners LISTENER_SCAN2 & LISTENER_SCAN3 are OFFLINE.

When I connect to database from workstation, Oracle client will work as following:
1. Ask DNS server for IPs for the SCAN name host-scan.dbaplus.ca, and get three IPs
2. Choose one of them to connect. If I am lucky, the one should be SCAN1 and I will not find any issues. Actually, I was not. scan2 (or scan3) was chosen and the request waited for response from server until time out because the SCAN VIP is not ONLINE, then failed over to another SCAN VIP scan3 (or scan2) or scan1. The worst thing is failover to scan3, it will get timeout again and have to fail over finally to scan1.
3. No matter where it starts, SCAN VIP scan1 will be the final and only choice for the connection. How fast the connection can be established depends on how fast the ONLINE SCAN VIP scan1 is chosen.

To fix the issue, try to start them:
$ srvctl start scan_listener -i 2
$ srvctl start scan_listener -i 3
$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node host02
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node host01
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node host01

$ crsctl status res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.FRA.dg
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.LISTENER.lsnr
ONLINE ONLINE host01
ONLINE OFFLINE host02
ora.asm
ONLINE ONLINE host01 Started
ONLINE ONLINE host02 Started
ora.gsd
OFFLINE OFFLINE host01
OFFLINE OFFLINE host02
ora.net1.network
ONLINE ONLINE host01
ONLINE ONLINE host02
ora.ons
ONLINE ONLINE host01
ONLINE ONLINE host02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE host02
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE host01
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.host01.vip
1 ONLINE ONLINE host01
ora.host02.vip
1 ONLINE UNKNOWN host01
ora.racdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
ora.scan1.vip
1 ONLINE ONLINE host02
ora.scan2.vip
1 ONLINE ONLINE host01
ora.scan3.vip
1 ONLINE ONLINE host01

All SCAN VIP and SCAN listeners are ONLINE. Back to workstation and test the connection:
C:\>tnsping RACPD

TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 26-JAN-2015 10:49:15

Copyright (c) 1997, 2011, Oracle. All rights reserved.

Used parameter files:
c:\app\oracle\product\11.2.0\client11203\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host-scan.dbaplus.ca)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=RACPD)))
OK (10 msec)

'10 msec' response time is not bad. The issue got fixed.