Evaluate Index Usage on Exadata

Exadata is well-known with great performance in scanning large amount of data. If need to pull huge amount of data from Exadata in the join operation, full table scan is more efficient than index scan. This is especially true in the data warehouse environment on Exadata.

The smartscan feature on Exadata allows database query to offload the full scan to cell nodes instead of the regular database nodes. But do you want to drop all indexes and in favor of full table scan for everything? Well, although this strategy is used by many database warehouse appliance vendors, you might not want to go for this path, especially when you have a mixed workload environment. For example, in OLTP environment, you might just need to get a few rows or a small amount of data from a large table. In this case, index access might be faster than the full table scan.

I have been to many Exadata clients and one of the top questions from my clients is always related to index. Many Exadata clients migrate their databases from other non-Exadata environment to Exadata. Ideally you want to take this opportunity to clean up the tables and indexes in the database and migrate only the portion of data needed. The tables are relatively easy to identified while indexes are not. You have no idea which index Exadata is going to pick it up in the execution plan before the migration. In addition, many large databases have way too many indexes for the purpose “in case” Oracle optimizer want to use it. Going through each of them before the Exadata migration is going to be painful and time-consuming task. So many companies do the migration first, then go back to evaluate index usage later. So the popular question I get is which index I can keep and which one I can drop.

It is not easy to evaluate whether you want to keep every index, but relatively easier just focus on the large indexes. Just like the “new version” of Mission Impossible below, identifying index usage is the way to help you to determine which one to keep and which one to drop.
mission-impossible
Here are a few ways to determine index usage as follows.

V$OBJECT_USAGE

The first way is using V$OBJECT_USAGE. By default, index monitoring is disabled and you need to run the command as follows to enable the monitoring.

alter index your_index_name monitoring usage;

WZHOU@dbm2> select * from v$object_usage;
no rows selected

WZHOU@dbm2> alter index BIGTAB_OLTP_PK monitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME        TABLE_NAME	MON USE START_MONITORING    END_MONITORING
----------------- ------------- --- --- ------------------- ------------------
BIGTAB_OLTP_PK    BIGTAB_OLTP   YES NO 02/17/2014 10:57:10

WZHOU@dbm2> select owner, object_name from bigtab_oltp where id = 30;
OWNER        OBJECT_NAME
------------ ------------------------------
SYS          I_PROXY_DATA$

WZHOU@dbm2> select * from v$object_usage;

INDEX_NAME      TABLE_NAME     MON USE START_MONITORING    END_MONITORING
--------------- -------------- --- --- ------------------- -------------------
BIGTAB_OLTP_PK  BIGTAB_OLTP    YES YES 02/17/2014 10:57:10

Please note. If you rerun the monitoring usage command, the previous usage information is wiped out and the USE flag is reset to NO.

WZHOU@dbm2>  alter index BIGTAB_OLTP_PK monitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME        TABLE_NAME     MON USE START_MONITORING    END_MONITORING
----------------- -------------- --- --- ------------------- ---------------
BIGTAB_OLTP_PK    BIGTAB_OLTP    YES NO  02/17/2014 11:01:01

WZHOU@dbm2> select owner, object_name from bigtab_oltp where id = 31;
OWNER        OBJECT_NAME
------------ ------------------------------
SYS          I_OBJ1

WZHOU@dbm2> select * from v$object_usage;

INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    YES YES 02/17/2014        11:01:01

If you rebuild the index, the index is reported to be used.

WZHOU@dbm2> alter index BIGTAB_OLTP_PK monitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;

INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    YES NO  02/17/2014 12:04:21

WZHOU@dbm2> alter index bigtab_oltp_pk rebuild;

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    NO  YES 02/17/2014 12:04:21

To disable the monitoring, run the following command:
alter Index your_index_name nomonitoring usage;

WZHOU@dbm2>  alter index BIGTAB_OLTP_PK nomonitoring usage;
Index altered.

WZHOU@dbm2> select * from v$object_usage;
INDEX_NAME       TABLE_NAME     MON USE START_MONITORING  END_MONITORING
---------------- -------------- --- --- ----------------- -----------------
BIGTAB_OLTP_PK   BIGTAB_OLTP    NO  YES 02/17/2014 11:01:01 02/17/2014 11:03:37

Although this approach can tell you whether the index is used or not, it does not tell you how many times the index has been used. There is a big difference between 1 time usage and 1,000 times usage. I also do not like the way to run alter index command first to enable usage and have to remember to run the alter index to disable the monitor after the analysis. In addition, this alter index has to be performed on every indexes you want to monitor.

DBA_HIST_SQL_PLAN
Another way is to use dba_hist_sql_plan to find out the index usage. This is not cover 100% usage, but majority of the usage. For the purpose to get a big picture of an index, the information is good enough.

As there are tons of indexes in the database, I usually starts from the index with largest size. Here is the script and sample output.

set line 160 pages 100
col object_owner for a20
col object_name for a35
col index_operation for a30
col total_megs for 999,999,999,999
col total_exec for 999,999,999

break on total_megs skip 1

with top_index as
(
    select * from (
        select
            owner,
            segment_name,
            sum(bytes/1024/1024) as total_megs,
            tablespace_name
        from dba_segments
        where
            segment_name in (
                select index_name
                from dba_indexes
                where
                    owner not in ('SYS', 'SYSTEM', 'SYSMAN','TSMSYS','DBSNMP','OUTLN'))
        group by owner, segment_name, tablespace_name
        order by total_megs desc
    )
    where
        rownum <= 100
)
select
    sp.object_owner,
    sp.object_name,
    decode( options, null, '   -', options) index_operation,
    ti.total_megs,
    count(sp.operation) total_exec,
    min(sp.timestamp) min_exec_date,
    max(sp.timestamp) max_exec_date
from dba_hist_sql_plan sp, top_index ti
where
    sp.object_owner = ti.owner
    and sp.object_name = ti.segment_name
group by
    sp.object_owner,
    sp.object_name,
    decode( options, null, '   -', options),
    ti.total_megs
order by
    ti.total_megs desc,
    sp.object_owner,
    sp.object_name,
    total_exec desc;

Let’s run a test.
WZHOU@dbm2> select /*+ index(bigtab_oltp BIGTAB_OLTP_PK) */ * from bigtab_oltp where id = 31;

ID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED
———- ——————– ———————————– —————————— ———- ————– ——————- ———
LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
——— ——————- ——- – - – ———- ——————————
31 SYS I_OBJ1 36 36 INDEX 12-MAR-13
12-MAR-13 2013-03-12:14:00:57 VALID N N N 4

Here is the result.

OBJECT_OWNER  OBJECT_NAME        INDEX_OPERATION TOTAL_MEGS TOTAL_EXEC MIN_EXEC_ MAX_EXEC_
------------- ------------------ --------------- ---------- ---------- --------- ---------
WZHOU         TESTCP_OBJID_IDX   RANGE SCAN       176	    2          17-FEB-14 17-FEB-14
WZHOU         TESTCP_OBJID_IDX   FULL SCAN                  1          17-FEB-14 17-FEB-14

WZHOU         TESTCP_PK          UNIQUE SCAN      175       1          17-FEB-14 17-FEB-14

WZHOU         BIGTAB_OLTP_PK     UNIQUE SCAN      117	    2          17-FEB-14 17-FEB-14
WZHOU         BIGTAB_OLTP_PK     RANGE SCAN                 2          21-FEB-14 21-FEB-14

WZHOU         BIGTAB_PK          UNIQUE SCAN      121       1          17-FEB-14 17-FEB-14   

If you could not find any rows from the above rows. Replace dba_hist_sql_plan with V$SQL_PLAN_STATISTICS_ALL.

Once you identify the indexes with less usage, you could make the index invisible first. Then after some period of time, if no negative impact on your system, you can safely drop these indexes.

Here is the command to make index invisible.
WZHOU@dbm2> alter index TESTCP_OBJID_IDX invisible;

Query to verify which indexes are in invisible state.

column tname format a39
column index_name for a30
column index_type format a15
select table_owner||’.’||table_name tname , index_name, index_type, status, visibility
from dba_indexes
where owner like nvl(‘&owner’,owner)
and table_name like nvl(‘&table_name’,table_name)
and index_name like nvl(‘&index_name’,index_name)
and visibility = ‘INVISIBLE’
order by 1,2
/

Enter value for owner: WZHOU
Enter value for table_name:
Enter value for index_name:

TNAME INDEX_NAME INDEX_TYPE STATUS VISIBILITY
————– ——————- ————— ——– ———
WZHOU.TESTCP TESTCP_OBJID_IDX NORMAL VALID INVISIBLE

There is a scenario that you have mixed workload in the same database. Sometimes for data warehouse type of queries, you would like not use the index, but do the full table scan. But for OLTP type queries, you want to use the index. One solution to handle this scenario is to make the index invisible, so optimizer will go for full table scan for the data warehouse type of queries. For OTLP queries, set OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at session level and will pick up this invisible index for the OLTP queries.

Replace Cisco Ethernet Switch on Exadata

network_port

Usually there is no need to replace Cisco switch on Exadata. However, certain enterprises might have their own standards to use different switch as part of enterprise standard. In this case, the Cisco ethernet switch on Exadata will be replaced. Oracle Support has a nice document about the process to replace Cisco switch, How to replace a Cisco ethernet switch in an Engineered Systems rack (Doc ID 1531203.1). This document is a good one, but focuses only on steps of replacing Cisco Switch, not enough to specify whether additional steps that need to be performed.

At first, I thought Cisco switch only affected the traffic on Management Network on Exadata and don’t have to shut down database and cell nodes. After discussing with my colleague, Andy Colvin, he brought some good points. Although it is not required to shut down the system, there will be no way to get into any of the components via SSH. Furthermore the storage servers will lose connectivity to DNS, which will have adverse consequences on performance. With so many network cables moving around, it would definitely be easier to shut down the entire system and replace the switch. Yes, that makes sense. Here are the high level steps to replace Cisco Switch.
1. Shutdown database nodes
2. Shutdown cell nodes
3. Flip off the switches on PDUs to make sure everything is down.
4. Replace the Cisco switch
5. Turn on PDUs and verify new Ethernet switch
6. Start cell nodes
7. Start database nodes.

Here are the detail steps.
Step 1. Shudown database nodes

1) Logon as oracle user to db node 1 and source one db env, get the status of the database.

crsctl status res -­t | more

check status of oracle instances.
ps -ef |grep pmon

The above steps are optional. Just to make sure all databases are running normal. If seeing issues in database, you might want to resolve it first before replacing Cisco switch. You don’t add the complexity of issues in the middle of switch changes.

2) Stop all the database currently running on Exadata by using srvctl command.
srvctl stop database -d yourdbname

3) Logon as root user to db node 1 and stop crs on the current node.
/u01/app/11.2/grid/bin/crsctl stop crs

During the shutdown process of CRS, run the following command regularly to check the number of oracle processes. It should reduce to 0 when CRS is stopped.
ps -ef|grep d.bin|grep -v grep|wc -l

4) Verify all oracle databases are shut down.
ps -­ef | grep pmon

5) Power off the node
Logon to ILOM to Power Off Server
or
shutdown -h -y now

6) Repeat the above steps for the rest of database nodes

Step 2. Shutdown cell nodes
1) Logon as root user to cell node 1

2) Check cell service, verify the disk status
service celld status
or
cellcli -e list cell detail

3) Verify disk status and they should be ONLINE, not SYNCING state
cellcli -e list griddisk attributes name,asmmodestatus

4) Stop the cell service
service celld stop
or
cellcli -e alter cell shutdown services all

5) Verify the cell services are down
service celld status

6) Logon to ILOM
Logon to ILOM to Power Off Server
or
shutdown -h -y now

7) Repeat the same process for the rest of cell nodes

Step 3. Turn off the PDUs
There is no power button on IB switches. As long as PDUs is on, the IB switches are always on. Before turn off the PDUS, verify ILOM for IB switch is working.

Login to IB switches using ILOM to verify it you can login from there.
http://ib-switch-name/

If ILOM for IB switches is working, flip off the switch on PDUs

Step 4. Replace the Cisco Switch
Use Oracle Support document to replace the switch
How to replace a Cisco ethernet switch in an Engineered Systems rack (Doc ID 1531203.1)

Step 5. Turn on PDU and verify accessibility to/from IB switches

1) Turn on PDUs
After turning on PDU, the IB switches are automatically starts. Make sure to give a few minutes to allow IB switches fully boot up before doing anything.

2) Verify the IB switch
To verify IB switch is ok, run the following command as root user on IB switch
env_test

3) Verify the network connectivity to/from the IB switch. You don’t want start cell nodes if you know you have connectivity issues from/to IB switches. There is no nslookup command on IB switch. So you have to use ping command to figure out whether DNS is working or not on IB switches.
a. First ping IB switch and ssh to it as root user

b. After login, ping a server outside Exadata by hostname. It should work.

c. Then ping a db node and a cell node by hostname

d. Finally, login to IB switch using ILOM to verify it you can login from there
http://ib-switch-name/

Step 6. Start the cell nodes
1) Verify you can access ALL cell nodes from ILOM
http://cell-node-ilom/

2) From ILOM, boot up the cell node, monitor the progress from remote console

3) ssh to cell node as root user, this is to verify NET0 connection is working

4) Verify all cell services are up
service celld status
or
cellcli -e list cell detail

5) Verify all disks are from SYNCING state to ONLINE state
cellcli -e list griddisk attributes name, asmmodestatus

6) Wait until all cell nodes’ disks showing ONLINE state. Highly recommend to wait them complete the SYNCING before starting the db node.

Step 7. Start the DB Nodes
1) Verify you can access ALL db nodes from ILOM.
http://db-node-ilom/

2) From ILOM, boot up the db node 1, monitor the progress from remote console.

3) ssh to db node as oracle user, source ASM environment.

4) Check whether database are online by the following command.
crsctl stat res -t

5) Repeat the above process for the rest of database nodes.

6) Verify database alert files to see anything unusual.

Compression Methods on Exadata: Compression on partition tables – Part 4 of 6

partitiondataIn the part 1, part 2, part 3 of the blog series, I show the examples to create compress tables using CREATE TABLE and ALTER TABLE MOVE and how to improve the compression performance using parallelism. For big database, partition table is quite common. This post shows how partition table behaves for different compression methods. There is a nice thing about partition table is that you can compress certain partitions while leave other partition uncompressed. This is very useful feature as you might need to compress historical data and only keep the current partition uncompressed. Printer manufacturer Brother has an interesting newspaper ads for its USP product shown above. Obviously this guy needs a better partition strategy as well as compression approaches to save his files digitally.

First let’s create a partition table. I made a slight change in the create table script from part 1.

CREATE TABLE PARTAB
(
    ID NUMBER,
    OWNER VARCHAR2(30) NOT NULL ENABLE,
    OBJECT_NAME VARCHAR2(30) NOT NULL ENABLE,
    SUBOBJECT_NAME VARCHAR2(30),
    OBJECT_ID NUMBER NOT NULL ENABLE,
    DATA_OBJECT_ID NUMBER,
    OBJECT_TYPE VARCHAR2(19),
    CREATED DATE NOT NULL ENABLE,
    LAST_DDL_TIME DATE NOT NULL ENABLE,
    TIMESTAMP VARCHAR2(19),
    STATUS VARCHAR2(7),
    TEMPORARY VARCHAR2(1),
    GENERATED VARCHAR2(1),
    SECONDARY VARCHAR2(1),
    NAMESPACE NUMBER NOT NULL ENABLE,
    EDITION_NAME VARCHAR2(30)
)
PARTITION BY RANGE (OBJECT_ID)
    (PARTITION P1 values less than (1500),
    PARTITION P2 values less than (4000),
    PARTITION P3 values less than (6000),
    PARTITION P4 values less than (maxvalue)
    );

Then, populate some data with 10 million rows in total.

declare
    l_cnt  number;
    l_rows number := 10000000;
begin
    insert /*+ append */
    into partab
    select rownum, a.*
      from all_objects a;
    l_cnt := sql%rowcount;
    commit;

    while (l_cnt < l_rows)
    loop
        insert /*+ APPEND */ into partab
        select rownum+l_cnt,
               OWNER, OBJECT_NAME, SUBOBJECT_NAME,
               OBJECT_ID, DATA_OBJECT_ID,
               OBJECT_TYPE, CREATED, LAST_DDL_TIME,
               TIMESTAMP, STATUS, TEMPORARY,
               GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
          from partab
         where rownum <= l_rows-l_cnt;         l_cnt := l_cnt + sql%rowcount;         commit;     end loop; end; / 

Check out the row count and table size.

 WZHOU@dbm1> select count(*) from partab;

  COUNT(*)
----------
  10000000

Elapsed: 00:00:07.43

WZHOU@dbm1> select count(*) from partab partition(p1);

  COUNT(*)
----------
   1364992

Elapsed: 00:00:00.08

WZHOU@dbm1> select count(*) from partab partition(p2);

  COUNT(*)
----------
   2560000

Elapsed: 00:00:00.15

WZHOU@dbm1> select count(*) from partab partition(p3);

  COUNT(*)
----------
   1445504

Elapsed: 00:00:00.08

WZHOU@dbm1> select count(*) from partab partition(p4);

  COUNT(*)
----------
   4629504

Elapsed: 00:00:00.27

Before doing the compression work, let me check out the table size and partition size. You can find part_size.sql from the Script page. This script is also one of the Enkitec scripts I use frequently.

WZHOU@dbm1> @table_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for type:

OWNER  SEGMENT_NAME TYPE             TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ ---------------- -------------- ------------------------------
WZHOU        PARTAB TABLE PARTITION       1,112.0   USERS
                                     --------------
sum                                       1,112.0

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name:
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P1       TABLE PARTITION       152.0    USERS
WZHOU  PARTAB             P2       TABLE PARTITION       280.0    USERS
WZHOU  PARTAB             P3       TABLE PARTITION       152.0    USERS
WZHOU  PARTAB             P4       TABLE PARTITION       528.0    USERS
                                                   --------------
sum                                                    1,112.0

Right now, let me start with the Basic compression.

Basic Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 COMPRESS;

Table altered.

Elapsed: 00:00:14.00

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
    where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             BASIC

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION       192.0    USERS
                                                   --------------
sum                                                      192.0

There is little change we need to make before we proceed to the next test. We need to restore this partition to the non-compression stage by using nocompress keyword.

OLTP Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:05.99

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for oltp;

Table altered.

Elapsed: 00:00:14.22
WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS     where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             OLTP

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION       192.0    USERS
                                                   --------------
sum                                                      192.0

Query Low Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:06.01
WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for query low;

Table altered.

Elapsed: 00:00:06.83
WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
    where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             QUERY LOW

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION        80.0    USERS
                                                   --------------
sum                                                       80.0

Query High Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:05.26

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for query high;

Table altered.

Elapsed: 00:00:13.08

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
    where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             QUERY HIGH

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION        32.0    USERS
                                                   --------------
sum                                                       32.0

Archive Low Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:05.09

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for archive low;

Table altered.

Elapsed: 00:00:13.60

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
  where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             ARCHIVE LOW

Elapsed: 00:00:00.00

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION         32.0   USERS
                                                   --------------
sum                                                        32.0

Archive High Compression

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 nocompress;

Table altered.

Elapsed: 00:00:04.93

WZHOU@dbm1> ALTER TABLE partab MOVE partition p4 compress for archive high;

Table altered.

Elapsed: 00:01:13.33

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
  where table_name = 'PARTAB' and partition_name='P4';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P4             ARCHIVE HIGH

Elapsed: 00:00:00.01

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name: P4
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P4       TABLE PARTITION        24.0    USERS
                                                   --------------
sum                                                       24.0

The timing is quite different from 13 seconds on majority of compression methods to about 73 seconds on Archive High method, about 500% time increase. The compression ratio is similar to what we see in the non-partition table. The partition size drops from 528 MB to about 25~40 MB for most of compression methods.

There is another interest aspect in partition tables is that you can use different compression methods for different partitions. This is useful that allows to compress the recent data in faster methods while very old historical data can use a method with longer processing time, but higher compression ratio. Here is the test to show different compression methods for different partitions.

WZHOU@dbm1> ALTER TABLE partab MOVE partition p1 compress for query low;

Table altered.

Elapsed: 00:00:01.88

WZHOU@dbm1> ALTER TABLE partab MOVE partition p2 compress for query high;

Table altered.

Elapsed: 00:00:05.75

WZHOU@dbm1> ALTER TABLE partab MOVE partition p3 compress for archive low;

Table altered.

Elapsed: 00:00:02.34

WZHOU@dbm1> select table_owner,table_name,partition_name,compress_for from DBA_TAB_PARTITIONS
     where table_name = 'PARTAB';

TABLE_OWNER TABLE_NAME PARTITION_NAME COMPRESS_FOR
----------- ---------- -------------- ------------
WZHOU       PARTAB     P1             QUERY LOW
WZHOU       PARTAB     P2             QUERY HIGH
WZHOU       PARTAB     P3             ARCHIVE LOW
WZHOU       PARTAB     P4             ARCHIVE HIGH

Elapsed: 00:00:00.04

WZHOU@dbm1> @part_size
Enter value for owner: WZHOU
Enter value for table_name: PARTAB
Enter value for partition_name:
Enter value for type:

OWNER  SEGMENT_NAME PARTITION_NAME TYPE            TOTALSIZE_MEGS TABLESPACE_NAME
------ ------------ -------------- --------------- -------------- ---------------
WZHOU  PARTAB             P1       TABLE PARTITION        16.0    USERS
WZHOU  PARTAB             P2       TABLE PARTITION        16.0    USERS
WZHOU  PARTAB             P3       TABLE PARTITION         8.0    USERS
WZHOU  PARTAB             P4       TABLE PARTITION        24.0    USERS
                                                   --------------
sum                                                       64.0

See enough code? In the next part, I am going to go back the basic stuff and discuss the layout of HCC as well as some recommendation when to use and when not to use HCC.

Commonly Used Logfiles on Exadata

treasure_map

When treasure hunters looking for lost treasure, they are using treasure map. On Exadata,
when you are hunting down issues, logfiles are your friend. There are tons of logfiles on Exadata and I list the frequently used ones as follows in two major categories, database node and cell node.

Database Node

Database alert logfile
$ORACLE_BASE/diag/rdbms/{DBNAME}/{sid}/trace/alert_{sid}.log
Example: /u01/app/oracle/diag/rdbms/dbm/dbm1/trace/alert_dbm1.log

Listener logfile
$ORACLE_BASE/diag/tnslsnr/{node name}/listener/trace/listener.log
Example: /u01/app/oracle/diag/tnslsnr/enkx3db01/listener/trace/listener.log

ASM alert logfile
$ORACLE_BASE/diag/asm/+asm/+ASM{instance number}/trace/ alert_+ASM{instance number}.log
Example: /u01/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log

CRS alert logfile
$GRID_HOME/log/{node name}/alert{node name}.log
Example: /u01/app/11.2.0.3/grid/log/enkx3db01/alertenkx3db01.log

Note: there are also many CRS related logfiles under this $GRID_HOME/log/{node name} directory

[enkx3db01:oracle:+ASM1] /u01/app/11.2.0.3/grid/log/enkx3db01
> ls -l /u01/app/11.2.0.3/grid/log/enkx3db01
total 1176
drwxr-xr-x 2 root   root    4096 Apr 24 11:53 acfs
drwxr-x--- 2 oracle dba     4096 Mar 12  2013 acfslog
drwxr-x--- 2 oracle dba     4096 Mar 12  2013 acfsrepl
drwxr-x--- 2 root   dba     4096 Mar 12  2013 acfsreplroot
drwxr-xr-x 2 root   dba     4096 Mar 12  2013 acfssec
drwxr-x--- 2 oracle dba     4096 Mar 12  2013 admin
drwxrwxr-t 4 root   dba     4096 Mar 12  2013 agent
-rw-rw-r-- 1 oracle dba  1096949 Oct 14 15:16 alertenkx3db01.log
drwxr-x--x 2 oracle dba    12288 Oct 14 06:48 client
drwxr-x--- 2 root   dba     4096 Oct  8 15:51 crflogd
drwxr-x--- 2 root   dba     4096 Sep 10 12:54 crfmond
drwxr-x--- 2 root   dba     4096 Oct 11 10:39 crsd
drwxr-x--- 2 oracle dba     4096 Oct 13 21:19 cssd
drwxr-x--- 2 root   dba     4096 Oct 12 12:07 ctssd
drwxr-x--- 4 oracle dba     4096 Mar 12  2013 cvu
drwxr-x--- 2 oracle dba     4096 Oct  9 14:11 diskmon
drwxr-x--- 2 oracle dba     4096 Mar 12  2013 evmd
drwxr-x--- 2 oracle dba     4096 Oct 14 10:48 gipcd
drwxr-x--- 2 root   dba     4096 Mar 12  2013 gnsd
drwxr-x--- 2 oracle dba     4096 Oct 14 15:14 gpnpd
drwxr-x--- 2 oracle dba     4096 Sep  6 12:22 mdnsd
drwxr-x--- 2 root   dba     4096 Oct 13 19:14 ohasd
drwxrwxr-t 5 oracle dba     4096 Mar 12  2013 racg
drwxr-x--- 2 oracle dba     4096 Mar 12  2013 srvm

The following chart shows the hierarchy of CRS related log directories.
crs_log_directories

OS Watcher output files
All OS Watcher files are under /opt/oracle.oswatcher/osw/archive/ directory.

[root@enkx3db01 ~]# cd /opt/oracle.oswatcher/osw/archive
[root@enkx3db01 archive]# ls -l
total 252
drwxr----- 2 root root 24576 Oct 14 15:14 ExadataOSW
drwxr----- 2 root root 20480 Oct 14 15:14 ExadataRDS
drwxr----- 2 root root  4096 Feb 20  2013 oswcellsrvstat
drwxr----- 2 root root 24576 Oct 14 15:15 oswdiskstats
drwxr----- 2 root root 20480 Oct 14 15:15 oswiostat
drwxr----- 2 root root 20480 Oct 14 15:15 oswmeminfo
drwxr----- 2 root root 24576 Oct 14 15:15 oswmpstat
drwxr----- 2 root root 20480 Oct 14 15:15 oswnetstat
drwxr----- 2 root root  4096 Feb 20  2013 oswprvtnet
drwxr----- 2 root root 24576 Oct 14 15:15 oswps
drwxr----- 2 root root 24576 Oct 14 15:15 oswslabinfo
drwxr----- 2 root root 20480 Oct 14 15:14 oswtop
drwxr----- 2 root root 24576 Oct 14 15:15 oswvmstat

For example, if we want to get OS watcher data for October 11, 2013, just run the folowing
To get OS watcher data for a specific date :
cd /opt/oracle.oswatcher/osw/archive
find . -name ‘*13.10.11*’ -print -exec zip /tmp/osw_`hostname`.zip {} \;
where 12- year 01- Month 13-day

[root@enkx3db01 tmp]# cd /opt/oracle.oswatcher/osw/archive
[root@enkx3db01 archive]# find . -name ‘*13.10.11*’ -print -exec zip /tmp/osw_`hostname`.zip {} \;

It will create a zip file with the name of osw_enkx3db01.enkitec.com.zip for all data happened on October 11, 2013. If we want just one hour of data, for example, 3pm’s data, we just make a little change in the above command

[root@enkx3db01 archive]# find . -name ‘*13.10.11.1400*’ -print -exec zip /tmp/osw_`hostname`.zip {} \;
./oswps/enkx3db01.enkitec.com_ps_13.10.11.1400.dat.bz2
updating: oswps/enkx3db01.enkitec.com_ps_13.10.11.1400.dat.bz2 (deflated 12%)
./ExadataRDS/enkx3db01.enkitec.com_ExadataRDS_13.10.11.1400.dat.bz2
updating: ExadataRDS/enkx3db01.enkitec.com_ExadataRDS_13.10.11.1400.dat.bz2 (deflated 6%)
./oswdiskstats/enkx3db01.enkitec.com_diskststs_13.10.11.1400.dat.bz2
updating: oswdiskstats/enkx3db01.enkitec.com_diskststs_13.10.11.1400.dat.bz2 (deflated 1%)
./oswtop/enkx3db01.enkitec.com_top_13.10.11.1400.dat.bz2
updating: oswtop/enkx3db01.enkitec.com_top_13.10.11.1400.dat.bz2 (deflated 7%)
./oswnetstat/enkx3db01.enkitec.com_netstat_13.10.11.1400.dat.bz2
updating: oswnetstat/enkx3db01.enkitec.com_netstat_13.10.11.1400.dat.bz2 (deflated 11%)
./oswslabinfo/enkx3db01.enkitec.com_slabinfo_13.10.11.1400.dat.bz2
updating: oswslabinfo/enkx3db01.enkitec.com_slabinfo_13.10.11.1400.dat.bz2 (deflated 1%)
./oswmpstat/enkx3db01.enkitec.com_mpstat_13.10.11.1400.dat.bz2
updating: oswmpstat/enkx3db01.enkitec.com_mpstat_13.10.11.1400.dat.bz2 (stored 0%)
./oswvmstat/enkx3db01.enkitec.com_vmstat_13.10.11.1400.dat.bz2
adding: oswvmstat/enkx3db01.enkitec.com_vmstat_13.10.11.1400.dat.bz2 (stored 0%)
./oswmeminfo/enkx3db01.enkitec.com_meminfo_13.10.11.1400.dat.bz2
adding: oswmeminfo/enkx3db01.enkitec.com_meminfo_13.10.11.1400.dat.bz2 (deflated 0%)
./oswiostat/enkx3db01.enkitec.com_iostat_13.10.11.1400.dat.bz2
adding: oswiostat/enkx3db01.enkitec.com_iostat_13.10.11.1400.dat.bz2 (deflated 1%)

[root@enkx3db01 archive]# ls -l /tmp/osw_`hostname`.zip
-rw-r–r– 1 root root 93306416 Oct 14 19:47 /tmp/osw_enkx3db01.enkitec.com.zip

OS message logfile
/var/log/messages

Secure logfile
/var/log/secure file records all SSH connections into the server. There are also two more log files related to /var/log/secure, /var/log/faillog and /var/log/tallylog. /var/log/faillog records the failed attempts to login while /var/log/tallylog is used as the “counter” for PAM and this counter is used to determine whether to lock a user account. But only /var/log/secure is “human readable” and it is difficult to manually edit faillog or tallylog files.
Here is a sample of secure file
[root@enkx3db01 log]# tail -30 /var/log/secure
Oct 14 16:53:19 enkx3db01 sshd[121950]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 16:53:20 enkx3db01 sshd[121955]: Accepted password for oracle from 192.168.9.89 port 62530 ssh2
Oct 14 16:53:20 enkx3db01 sshd[121955]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 16:53:20 enkx3db01 sshd[121959]: subsystem request for sftp
Oct 14 17:04:29 enkx3db01 su: pam_unix(su-l:session): session closed for user root
Oct 14 17:04:31 enkx3db01 sshd[16252]: Received disconnect from 192.168.9.111: 11: disconnected by user
Oct 14 17:04:31 enkx3db01 sshd[15720]: pam_unix(sshd:session): session closed for user oracle
Oct 14 17:35:59 enkx3db01 sshd[29330]: pam_unix(sshd:session): session closed for user oracle
Oct 14 19:03:20 enkx3db01 sshd[120214]: pam_unix(sshd:session): session closed for user oracle
Oct 14 19:04:35 enkx3db01 sshd[121955]: pam_unix(sshd:session): session closed for user oracle
Oct 14 19:09:42 enkx3db01 sshd[121950]: pam_unix(sshd:session): session closed for user oracle
Oct 14 19:23:49 enkx3db01 sshd[120198]: pam_unix(sshd:session): session closed for user oracle
Oct 14 20:25:29 enkx3db01 sshd[82077]: pam_unix(sshd:auth): authentication failure; logname= uid=0 euid=0 tty=ssh ruser= rhost=192.168.9.154 user=oracle
Oct 14 20:25:30 enkx3db01 sshd[82077]: Failed password for oracle from 192.168.9.154 port 50358 ssh2
Oct 14 20:25:36 enkx3db01 sshd[82077]: Failed password for oracle from 192.168.9.154 port 50358 ssh2
Oct 14 20:25:38 enkx3db01 sshd[82077]: Accepted password for oracle from 192.168.9.154 port 50358 ssh2
Oct 14 20:25:38 enkx3db01 sshd[82077]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 20:25:38 enkx3db01 sshd[82350]: Accepted password for oracle from 192.168.9.154 port 50359 ssh2
Oct 14 20:25:38 enkx3db01 sshd[82350]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 20:25:38 enkx3db01 sshd[82352]: subsystem request for sftp
Oct 14 20:26:28 enkx3db01 sshd[83425]: Accepted password for oracle from 192.168.9.154 port 50361 ssh2
Oct 14 20:26:28 enkx3db01 sshd[83425]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 20:26:28 enkx3db01 sshd[83428]: Accepted password for oracle from 192.168.9.154 port 50362 ssh2
Oct 14 20:26:28 enkx3db01 sshd[83428]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 20:26:29 enkx3db01 sshd[83430]: subsystem request for sftp
Oct 14 20:49:46 enkx3db01 sshd[107563]: Accepted password for root from 192.168.9.54 port 58377 ssh2
Oct 14 20:49:46 enkx3db01 sshd[107563]: pam_unix(sshd:session): session opened for user root by (uid=0)
Oct 14 20:57:57 enkx3db01 sshd[115745]: Accepted password for oracle from 192.168.9.145 port 57148 ssh2
Oct 14 20:57:57 enkx3db01 sshd[115745]: pam_unix(sshd:session): session opened for user oracle by (uid=0)
Oct 14 20:58:01 enkx3db01 su: pam_unix(su-l:session): session opened for user root by oracle(uid=500)

In addition to secure file, last command is frequently used to identify the login/logout history for a certain user. For example, to find out the login/logout history for oracle user, just run the following:
[root@enkx3db01 log]# last oracle
oracle pts/0 192.168.9.89 Mon Oct 14 15:35 still logged in
oracle pts/1 192.168.9.111 Mon Oct 14 15:16 still logged in
oracle pts/0 192.168.9.89 Mon Oct 14 15:11 – 15:35 (00:24)
oracle pts/5 192.168.9.245 Mon Oct 14 10:32 – crash (04:37)
oracle pts/3 192.168.9.89 Mon Oct 14 10:32 – crash (04:38)
oracle pts/0 192.168.9.89 Mon Oct 14 09:24 – crash (05:46)
oracle pts/5 192.168.9.233 Sun Oct 13 23:29 – 02:27 (02:58)
oracle pts/4 192.168.9.233 Sun Oct 13 23:28 – 02:08 (02:40)
oracle pts/2 192.168.9.233 Sun Oct 13 22:06 – 02:09 (04:02)
oracle pts/0 enk02.enkitec.co Sun Oct 13 21:26 – 01:26 (03:59)
oracle pts/4 192.168.9.233 Sun Oct 13 18:15 – crash (02:58)
oracle pts/2 192.168.9.233 Sun Oct 13 17:38 – crash (03:36)
oracle pts/0 192.168.9.233 Sun Oct 13 17:15 – crash (03:59)
oracle pts/8 192.168.9.245 Fri Oct 11 12:44 – 17:55 (05:11)
oracle pts/5 192.168.9.89 Fri Oct 11 09:42 – 18:08 (08:25)
oracle pts/2 192.168.9.89 Fri Oct 11 08:28 – 18:30 (10:01)
oracle pts/1 192.168.9.89 Fri Oct 11 08:28 – 18:31 (10:03)
oracle pts/0 192.168.9.89 Fri Oct 11 08:06 – 16:40 (08:34)
oracle pts/6 192.168.9.245 Thu Oct 10 15:30 – 18:43 (03:13)
oracle pts/5 192.168.9.245 Thu Oct 10 14:54 – 19:05 (04:11)
oracle pts/4 192.168.9.89 Thu Oct 10 07:54 – 17:03 (09:08)
oracle pts/1 192.168.9.89 Thu Oct 10 07:53 – 17:03 (09:10)
oracle pts/0 192.168.9.89 Thu Oct 10 07:51 – 17:03 (09:12)
oracle pts/1 192.168.9.234 Wed Oct 9 12:37 – 20:22 (07:45)
. . . .

Print Message buffer of kernel
The dmesg command is used to write the kernel messages to standard output (which by default is the display screen). The output of dmesg is maintained in the log file /var/log/dmesg file, and it can be easily viewed by vi or cat command.

[root@enkx3db01 log]# dmesg
. . . .
RPC: Registered udp transport module.
RPC: Registered tcp transport module.
RPC: Registered tcp NFSv4.1 backchannel transport module.
Slow work thread pool: Starting up
Slow work thread pool: Ready
FS-Cache: Loaded
FS-Cache: Netfs ‘nfs’ registered for caching
svc: failed to register lockdv1 RPC service (errno 97).
i2c /dev entries driver
megasas: fasync_helper was not called first
warning: `ntpdate’ uses 32-bit capabilities (legacy support in use)
eth0: no IPv6 routers present
mpt2sas version 12.105.11.00 loaded
RDS/IB: connected to 192.168.12.2 version 3.1
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: packet len 2144 (> 2048) too long to send, dropping
ib0: packet len 2144 (> 2048) too long to send, dropping
ib0: packet len 2144 (> 2048) too long to send, dropping
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0001:8000:0048:fe80:0000
ib0: Unicast, no dst: type 0000, QPN 060000 0020:0800:1404:0002:0000:0000:0000:0000
RDS/IB: connected to 169.254.87.194 version 3.1
RDS/IB: connected to 169.254.87.194 version 3.1
RDS/IB: connected to 169.254.97.245 version 3.1

Disk controller firmware logs
[root@enkx3db01 log]# /opt/MegaRAID/MegaCli/MegaCli64 -fwtermlog -dsply -a0
Firmware Term Log Information on controller 0:
= 0 internalIndex = 0 externalIndex = ff
T4: setPhyMap: phy = 1 internalIndex = 0 externalIndex = ff
T4: setPhyMap: phy = 2 internalIndex = 0 externalIndex = ff
T4: setPhyMap: phy = 3 internalIndex = 0 externalIndex = ff
T4: setPhyMap: phy = 4 internalIndex = 1 externalIndex = ff
T4: setPhyMap: phy = 5 internalIndex = 1 externalIndex = ff
T4: setPhyMap: phy = 6 internalIndex = 1 externalIndex = ff
T4: setPhyMap: phy = 7 internalIndex = 1 externalIndex = ff
T4: setPhyMap: phy = 8 internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = 9 internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = a internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = b internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = c internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = d internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = e internalIndex = ff externalIndex = ff
T4: setPhyMap: phy = f internalIndex = ff externalIndex = ff
. . . .

10/14/13 15:19:06: InhibitCharging : i2cExpander_GPIOstatus = 0xde
10/14/13 15:19:06: i2BufferBatDis : i2cExpander_GPIOstatus = 0xce
10/14/13 15:19:06: EVT#06367-10/14/13 15:19:06: 151=Battery relearn started
10/14/13 15:20:11: EVT#06368-10/14/13 15:20:11: 148=Battery is discharging
10/14/13 15:20:11: EVT#06369-10/14/13 15:20:11: 152=Battery relearn in progress
10/14/13 16:32:46: i2BufferBatDis : i2cExpander_GPIOstatus = 0xde
10/14/13 16:32:46: Learn completed successfully
10/14/13 16:32:46: EVT#06370-10/14/13 16:32:46: 153=Battery relearn completed
10/14/13 16:32:46: Next Learn will start on 11 13 2013
10/14/13 16:32:46: Given BBUMode 7, is the same as Current BBUMode

10/14/13 16:32:46: *** BATTERY FEATURE PROPERTIES ***
10/14/13 16:32:46: _________________________________________________

10/14/13 16:32:46: Auto Learn Period : 30 days
10/14/13 16:32:46: Next Learn Time : 437675566
10/14/13 16:32:46: Battery ID : 414a28fe
10/14/13 16:32:46: bbuMode : 7
10/14/13 16:32:46: Delayed Learn Interval: 0 hours from scheduled time
10/14/13 16:32:46: AutoLearnMode : 0
10/14/13 16:32:46: Next Learn scheduled on: 11 13 2013 16:32:46
10/14/13 16:32:46: _________________________________________________

10/14/13 16:32:46: i2cEepromWriteEnable : i2cExpander_GPIOstatus = 0x9e
10/14/13 16:32:49: i2cEepromWriteEnable : i2cExpander_GPIOstatus = 0xde

Exit Code: 0×00

Cell Node

Cell alert logfile
There are two possible locations for the cell alert logfile and both are the same
/opt/oracle/cell/log/diag/asm/cell/{node name}/trace/alert.log or $CELLTRACE/alert.log
[root@enkx3cel01 trace]# ls -l /opt/oracle/cell/log/diag/asm/cell/enkx3cel01/trace/alert.log
-rw-rw—- 1 root celladmin 776278 Oct 14 18:16 /opt/oracle/cell/log/diag/asm/cell/enkx3cel01/trace/alert.log
[root@enkx3cel01 trace]# ls -l $CELLTRACE/alert.log
-rw-rw—- 1 root celladmin 776278 Oct 14 18:16 /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/enkx3cel01/trace/alert.log

MS logfile
Similar MS logfile has two possible locations
[root@enkx3cel01 trace]# ls -l /opt/oracle/cell/log/diag/asm/cell/enkx3cel01/trace/ms-odl.log
-rw-r—– 1 root celladmin 1184111 Oct 14 19:35 /opt/oracle/cell/log/diag/asm/cell/enkx3cel01/trace/ms-odl.log
[root@enkx3cel01 trace]# ls -l $CELLTRACE/ms-odl.log
-rw-r—– 1 root celladmin 1184111 Oct 14 19:35 /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/enkx3cel01/trace/ms-odl.log

Cell patching issues related logfiles
There are many cell patching related logfiles under /var/log/cellos directory

[root@enkx3cel01 cellos]# ls -lart /var/log/cellos
total 1708
-rw-r-----  1 root root       25726 Apr 29 12:41 do_image.sh.log
-rw-r-----  1 root root       67099 Apr 29 13:04 install.post.10commonos.install.sh.log
-rw-r-----  1 root root        5509 Apr 29 13:04 install.post.30kernel.install.sh.log
-rw-r-----  1 root root         957 Apr 29 13:05 install.post.40debugos.install.sh.log
-rw-r-----  1 root root       17907 Apr 29 13:05 install.post.50ofed.install.sh.log
-rw-r-----  1 root root         955 Apr 29 13:05 install.post.60cellrpms.install.sh.log
-rw-r-----  1 root root        5216 Apr 29 13:06 install.post.70sunutils.install.sh.log
-rw-r-----  1 root root         598 Apr 29 13:06 install.post.90last.install.sh.log
-rw-r-----  1 root root         323 Apr 29 13:07 install.post.status
-rw-r-----  1 root root        3288 Apr 29 13:07 cell.bin.install.log
-rw-r-----  1 root root          63 Apr 29 13:11 vldrun.reimage_boot.log
-rw-r-----  1 root root          59 Apr 29 13:38 vldrun.first_boot.log
drwxr-xr-x  2 root root        4096 Apr 29 13:38 validations
drwxr-xr-x  2 root root        4096 Apr 29 13:38 saved
drwxr-xr-x  2 root root        4096 Apr 29 14:18 sosreports
-rw-r-----  1 root root       20061 Jul 17 11:56 ipconf.log
-rw-r-----  1 root root       11754 Oct 14 13:10 cellFirstboot.log
-rw-r-----  1 root celladmin 408910 Oct 14 13:17 checkdeveachboot.log
-rw-r-----  1 root root         944 Oct 14 13:17 exachkcfg.log
-rw-r-----  1 root root         670 Oct 14 13:17 checkconfigs.alert.txt
-rw-r--r--  1 root root       85976 Oct 14 13:17 SerialNumbers
-rw-rw----  1 root celladmin  23069 Oct 14 13:17 CheckHWnFWProfile.log
-rw-r-----  1 root root         406 Oct 14 13:19 vldrun.each_boot.log
-rw-r-----  1 root root       58416 Oct 14 13:19 validations.log
-rw-r-----  1 root root      674195 Oct 14 13:19 checkconfigs_configuration_summaries.txt
drwxr-xr-x 12 root root        4096 Oct 14 13:19 ..
drwxr-xr-x  5 root root        4096 Oct 14 13:20 .
-rw-r-----  1 root root      138088 Oct 14 16:47 start_oswatcher.log

Among these logfiles, validations.log is a useful one
[root@enkx3cel01 cellos]# tail -50 validations.log
2013-10-05 04:02:18 vldrun.pl: Run validation oswatcher – 2013-10-05 04:02:18 vldrun.pl: [PASSED]
2013-10-06 04:02:19 vldrun.pl: Logging started to /var/log/cellos/validations.log
2013-10-06 04:02:19 vldrun.pl: Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher
2013-10-06 04:02:19 vldrun.pl: Run validation oswatcher – 2013-10-06 04:02:19 vldrun.pl: [PASSED]
2013-10-07 04:02:19 vldrun.pl: Logging started to /var/log/cellos/validations.log
2013-10-07 04:02:19 vldrun.pl: Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher
2013-10-07 04:02:19 vldrun.pl: Run validation oswatcher – 2013-10-07 04:02:19 vldrun.pl: [PASSED]
2013-10-08 04:02:18 vldrun.pl: Logging started to /var/log/cellos/validations.log
2013-10-08 04:02:18 vldrun.pl: Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher
2013-10-08 04:02:18 vldrun.pl: Run validation oswatcher – 2013-10-08 04:02:18 vldrun.pl: [PASSED]
2013-10-09 04:02:19 vldrun.pl: Logging started to /var/log/cellos/validations.log
2013-10-09 04:02:19 vldrun.pl: Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher
. . . .
2013-10-14 04:02:24 vldrun.pl: Logging started to /var/log/cellos/validations.log
2013-10-14 04:02:24 vldrun.pl: Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -script oswatcher
2013-10-14 04:02:24 vldrun.pl: Run validation oswatcher – 2013-10-14 04:02:24 vldrun.pl: [PASSED]
2013-10-14 13:12:38 vldrun.pl: Logging started to /var/log/cellos/validations.log
2013-10-14 13:12:38 vldrun.pl: Command line is /opt/oracle.cellos/validations/bin/vldrun.pl -quiet -all
2013-10-14 13:12:38 vldrun.pl: Run validation ipmisettings – 2013-10-14 13:13:01 vldrun.pl: [PASSED]
2013-10-14 13:13:01 vldrun.pl: Run validation misceachboot – 2013-10-14 13:13:36 vldrun.pl: [PASSED]
2013-10-14 13:13:36 vldrun.pl: Run validation celldstatus – 2013-10-14 13:13:51 vldrun.pl: [PASSED]
2013-10-14 13:13:51 vldrun.pl: Run validation sosreport – 2013-10-14 13:13:51 vldrun.pl: [NOHUP RUN]
2013-10-14 13:13:51 vldrun.pl: Run validation oswatcher – 2013-10-14 13:13:51 vldrun.pl: [PASSED]
2013-10-14 13:13:51 vldrun.pl: Run validation biosbootorder – 2013-10-14 13:14:06 vldrun.pl: [PASSED]
2013-10-14 13:14:06 vldrun.pl: Run validation checkconfigs – 2013-10-14 13:19:53 vldrun.pl: [FAILED]
2013-10-14 13:19:53 vldrun.pl: Check log in /var/log/cellos/validations/checkconfigs.log
2013-10-14 13:19:53 vldrun.pl: Run validation saveconfig – 2013-10-14 13:19:53 vldrun.pl: [BACKGROUND RUN]

The followings are similar as the ones on database node and I am not going to repeat them to give more detail.
OS Watcher output files
/opt/oracle.oswatcher/osw/archive/

OS message logfile
/var/log/messages

Disk controller firmware logs
/opt/MegaRAID/MegaCli/MegaCli64 -fwtermlog -dsply -a0

For more information, you can also check Oracle’s document – Location of Different Logfiles in Exadata Environment (Doc ID 1326382.1)

Cold Failover Single Instance DB to Different Node on ODA

server-disaster

Oracle Database Appliance (ODA) is the entry level of Oracle Engineering System. It is like Exadata, but it does not have cell nodes and InfiniBand. It does have two db nodes and a shared storage with 24 disks. Both ODA and Exadata are using the same Grid Infrastructure to manage ASM across multiple database nodes. As the ASM db files can be accessed by all db nodes, usually companies are implementing RAC or RAC ONE options to handle the failover in case of hardware failure. As both ODA and Exadata can support single instance database, it makes me wonder how the failover works and how I can bring up the database on a different node in case there is a hardware failure or a failure show above.

As many people know, the failover on RAC and RAC ONE is super easy and you don’t need do anything if the db node is down, the db can continue to run on the other db node. The difference for RAC and RAC ONE is that RAC perform hot failover while RAC ONE perform cold failover. I wouldn’t expect single instance or non-rac db can do the hot failover, but some of kind of cold failover is fine with me. It’s better than nothing.

At first, I thought just shutdown the db and created some necessary directories, add an entry to oratab file on another node should work. I remember reading one article about this procedure long time ago by changing certain db parameters from the pfile, and bring up the db by using the modified pfile. Search the internet and could not find it anymore. The most closed article I can find is, How To Setup ASM (11.2) On An Active/Passive Cluster (Non-RAC). (Doc ID 1296124.1). The document discusses a lot of steps to install and configure GI on the 2nd db node, so I basically can skip those steps. When I worked on the most important step, srvctl add database, I got the following errors:

oracle:testdb@odap2 ~ > /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/srvctl add database -d testdb -n odap2 -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +data/TESTDB/spfiletestdb.ora -s OPEN -y AUTOMATIC -a DATA,RECO,REDO -t IMMEDIATE
PRCS-1007 : Server pool testdb already exists
PRCR-1086 : server pool ora.testdb is already registered

It looks weird that this db exists on db node 2 even before I created it. Later I realized the srvctl command can see the resource across all db nodes. Obviously this document is not going to work for me. So let me try another way. What if I shutdown the db on node 1 and bring it up on node 2 directly. Wrong! After I did that, I got many errors during mount stage as follows:

SQL> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 838862008 bytes
Database Buffers 3388997632 bytes
Redo Buffers 45686784 bytes
SQL> alter database mount
2 ;
alter database mount
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 2838
Session ID: 584 Serial number: 1

The alert log also shows the following errors:
Thu Sep 05 19:56:38 2013
alter database mount
Thu Sep 05 19:56:38 2013
NOTE: Loaded library: System
ORA-15025: could not open disk “/dev/mapper/SSD_E0_S20_805849611p1″
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk “/dev/mapper/SSD_E0_S21_805849344p1″
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk “/dev/mapper/SSD_E0_S22_805849349p1″
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
ORA-15025: could not open disk “/dev/mapper/SSD_E0_S23_805849238p1″
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 9
Thu Sep 05 19:56:38 2013
SUCCESS: diskgroup REDO was mounted
Thu Sep 05 19:56:38 2013
NOTE: dependency between database testdb and diskgroup resource ora.REDO.dg is established
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ckpt_2813.trc (incident=24145):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb/incident/incdir_24145/testdb_ckpt_2813_i24145.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ckpt_2813.trc (incident=24146):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/testdb/testdb/incident/incdir_24146/testdb_ckpt_2813_i24146.trc

Out of ideas, I posted my question to Enkitec‘s internal technical distribution email list. My colleague, Andy Colvin, came back with an interesting idea. He recommended to use srvctl modify database command to tell the OCR to use the other node. After that, just run srvctl start database to start the database. It is an interesting idea and seem make sense to me. However, when I checked out the Oracle document about srvctl modify database, there is no parameter that can specify the hostname. The srvctl is used to use -n to specify hostname in many occasion. For this command, -n means db name. Here is the syntax about this command from the document.

srvctl modify database -d db_unique_name [-o oracle_home] [-u oracle_user]
[-m domain_name] [-n db_name] [-p spfile] [-s start_options]
[-t stop_options] [-r {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY |
SNAPSHOT_STANDBY}] [-y {automatic | manual}] [-a disk_group_list] [-z]

It looks weird to me and I pretty sure it must have some ways to specify hostname somewhere when creating the db. Luckily, whenever I created a db using dbca, I always save a copy of the creation script. So I run the following to search the creation scripts.

oracle:testdb@odap1 scripts> cd /u01/app/oracle/admin/testdb/scripts
oracle:testdb@odap1 scripts> grep odap1 *
testdb.sql:host /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/srvctl add database -d testdb -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +DATA/testdb/spfiletestdb.ora -n testdb -x odap1 -a “DATA,RECO”

Ah, here is the answer, it does have undocument parameter -x that can specify the hostname. So I did the following:
/u01/app/oracle/product/11.2.0.3/dbhome_1/bin/srvctl modify database -d testdb -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +DATA/testdb/spfiletestdb.ora -n testdb -x odap2 -a “DATA,RECO”

Then the database can be brought up by using sqlplus on the 2nd db node.

Sounds like the end of story. Later on, another colleague, Frits Hoogland, brought up a even nicer and cleaner solution than what I did. He mentioned the approach can be used on Exadata. Basically on 2nd db node, I don’t need to do make change in oratab file, and create directories, but must use srvctl command instead of sqlplus to do the work. Another interesting idea, so I reproduced his recommendation and it works easily without any issue. To make the example to use for different scenarios, I created a db on node 1 with the following information:

db name : mytestdb
db unquie name : mytestprd
service : mytest

Here are the steps:
Step 1. Verificate some information about the db and directories
on node 1

oracle:mytestdb@odap1 ~> srvctl config database -d mytestprd
Database unique name: mytestprd
Database name: mytestdb
Oracle home: /u01/app/oracle/product/11.2.0.3/dbhome_1
Oracle user: oracle
Spfile: +DATA/mytestprd/spfilemytestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mytestprd
Database instance: mytestdb
Disk Groups: DATA,RECO,REDO
Mount point paths:
Services: mytest
Type: SINGLE
Database is administrator managed

SYS@mytestdb> show parameter name

NAME TYPE                   VALUE
--------------------------- ----------- ------------------------------
db_file_name_convert        string
db_name                     string	 mytestdb
db_unique_name              string	 mytestprd
global_names                boolean      TRUE
instance_name               string	 mytestdb
lock_name_space             string
log_file_name_convert       string
processor_group_name        string
service_names               string	 mytest

On db node 2, check the entry and db directories exist or not. Of course, they should not exit.

oracle:testdb@odap2 ~> grep mytest /etc/oratab

oracle:testdb@odap2 ~> ls -l /u01/app/oracle/diag/rdbms/mytestprd/mytestdb/trace
ls: /u01/app/oracle/diag/rdbms/mytestprd/mytestdb/trace: No such file or directory

oracle:testdb@odap2 ~> ls -l /u01/app/oracle/admin/mytestprd/adump
ls: /u01/app/oracle/admin/mytestprd/adump: No such file or directory

Step 2. Configure the db to move from db node 1 to node 2

oracle:testdb@odap2 ~> srvctl status database -d mytestprd
Instance mytestdb is running on node odap1

oracle:testdb@odap2 ~> srvctl status service -d mytestprd -s mytest
Service mytest is running on instance(s) mytestdb

oracle:testdb@odap2 ~> srvctl stop database -d mytestprd
oracle:testdb@odap2 ~> srvctl modify database -d mytestprd -x odap2
oracle:testdb@odap2 ~> srvctl start database -d mytestprd

oracle:testdb@odap2 ~> srvctl status database -d mytestprd
Instance mytestdb is running on node odap2
oracle:testdb@odap2 ~> srvctl status service -d mytestprd -s mytest
Service mytest is running on instance(s) mytestdb

oracle:testdb@odap2 ~> srvctl config database -d mytestprd
Database unique name: mytestprd
Database name: mytestdb
Oracle home: /u01/app/oracle/product/11.2.0.3/dbhome_1
Oracle user: oracle
Spfile: +DATA/mytestprd/spfilemytestdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: mytestprd
Database instance: mytestdb
Disk Groups: DATA,RECO,REDO
Mount point paths:
Services: mytest
Type: SINGLE
Database is administrator managed
[/code]

Right now, we can verify whether the oratab entry and directories exist or not.

verify the entry in oratab file

oracle:testdb@odap2 ~> grep mytest /etc/oratab
mytestdb:/u01/app/oracle/product/11.2.0.3/dbhome_1:N    # line added by Agent

oracle:testdb@odap2 ~> ls -l /u01/app/oracle/diag/rdbms/mytestprd/mytestdb/trace
total 160
-rw-r----- 1 oracle asmadmin 10037 Sep  7 10:20 alert_mytestdb.log
-rw-r----- 1 oracle asmadmin   893 Sep  7 10:20 mytestdb_asmb_31403.trc
-rw-r----- 1 oracle asmadmin    64 Sep  7 10:20 mytestdb_asmb_31403.trm
-rw-r----- 1 oracle asmadmin  1062 Sep  7 10:20 mytestdb_cjq0_31545.trc
....

oracle:testdb@odap2 ~> ls -l /u01/app/oracle/admin/mytestprd/adump
total 32
-rw-r----- 1 oracle asmadmin  796 Sep  7 10:20 mytestdb_ora_31064_2.aud
-rw-r----- 1 oracle asmadmin 2600 Sep  7 10:20 mytestdb_ora_31421_1.aud
....

Sqlplus command also show the db is relocated to 2nd db node

SQL> col host_name for a20
select instance_name, host_name from v$instance;

SQL>
INSTANCE_NAME	 HOST_NAME
---------------- --------------------
mytestdb	     odap2

Let's do some check under $ORACLE_HOME/dbs directory on node 2.

oracle:mytestdb@odap2 dbs> ls -l *mytestdb*
-rw-rw---- 1 oracle asmadmin 1544 Sep  7 10:20 hc_mytestdb.dat
-rw-r--r-- 1 oracle oinstall   67 Sep  7 10:19 initmytestdb.ora

oracle:mytestdb@odap2 dbs> cat initmytestdb.ora
SPFILE='+DATA/mytestprd/spfilemytestdb.ora'		# line added by Agent

Check out the same $ORACLE_HOME/dbs directory on node 1

oracle:mytestdb@odap1 dbs> ls -l *mytest*
-rw-rw---- 1 oracle asmadmin 1544 Sep  7 10:19 hc_mytestdb.dat
-rw-r----- 1 oracle oinstall   44 Sep  6 09:12 initmytestdb.ora
-rw-r----- 1 oracle oinstall 1536 Sep  6 09:05 orapwmytestdb

You can notice that the password file is missing on the node 2. So do the copy work from db node 1.

oracle:mytestdb@odap2 dbs> cd $ORACLE_HOME/dbs
oracle:mytestdb@odap2 dbs> scp -p oracle@odap1:$PWD/orapwmytestdb .
orapwmytestdb                                                                     100% 1536     1.5KB/s   00:00
oracle:mytestdb@odap2 dbs> ls -lart *mytest*
-rw-r----- 1 oracle oinstall 1536 Sep  6 09:05 orapwmytestdb
-rw-r--r-- 1 oracle oinstall   67 Sep  7 10:19 initmytestdb.ora
-rw-rw---- 1 oracle asmadmin 1544 Sep  7 10:20 hc_mytestdb.dat

Run the crsstat command, it also shows the right result.

oracle:mytestdb@odap2 dbs> crsstat
Resource Name            Resource Type    Target     State        Node            FC RC State Details
------------------------ ---------------- ---------- ------------ --------------- -- -- ------
ora.DATA.dg               diskgroup      C ONLINE     ONLINE       odap1       0  0
ora.DATA.dg               diskgroup      C ONLINE     ONLINE       odap2       0  0
ora.LISTENER.lsnr         Listener       C ONLINE     ONLINE       odap1       0  0
ora.LISTENER.lsnr         Listener       C ONLINE     ONLINE       odap2       0  0
ora.LISTENER_SCAN1.lsnr   SCAN Listener  L ONLINE     ONLINE       odap1       0  0
ora.LISTENER_SCAN2.lsnr   SCAN Listener  L ONLINE     ONLINE       odap2       0  0
ora.RECO.dg               diskgroup      C ONLINE     ONLINE       odap1       0  0
ora.RECO.dg               diskgroup      C ONLINE     ONLINE       odap2       0  0
ora.REDO.dg               diskgroup      C ONLINE     ONLINE       odap1       0  0
ora.REDO.dg               diskgroup      C ONLINE     ONLINE       odap2       0  0
ora.asm                   ASM            C ONLINE     ONLINE       odap1       0  0 Started
ora.asm                   ASM            C ONLINE     ONLINE       odap2       0  0 Started
ora.cvu                   cvu            L ONLINE     ONLINE       odap2       0  0
ora.dbm.db                database       L ONLINE     ONLINE       odap1       0  0 Open
ora.odap1.vip             Cluster VIP    L ONLINE     ONLINE       odap1       0  0
ora.odap2.vip             Cluster VIP    L ONLINE     ONLINE       odap2       0  0
ora.gsd                   Gbl Svc Daemon C OFFLINE    OFFLINE      (odap1)     0  0
ora.gsd                   Gbl Svc Daemon C OFFLINE    OFFLINE      (odap2)     0  0
ora.mytestprd.db          database       L ONLINE     ONLINE       odap2       0  0 Open
ora.mytestprd.mytest.svc  service        L ONLINE     ONLINE       odap2       0  0
ora.net1.network          Network (VIP)  C ONLINE     ONLINE       odap1       0  0
ora.net1.network          Network (VIP)  C ONLINE     ONLINE       odap2       0  0
ora.oc4j                  OC4J           L ONLINE     ONLINE       odap2       0  0
ora.ons                   Ora Notif Svc  C ONLINE     ONLINE       odap1       0  0
ora.ons                   Ora Notif Svc  C ONLINE     ONLINE       odap2       0  0
ora.reco.acfsvol.acfs     acfs           C ONLINE     ONLINE       odap1       0  0 mounted on /cloudfs
ora.reco.acfsvol.acfs     acfs           C ONLINE     ONLINE       odap2       0  0 mounted on /cloudfs
ora.registry.acfs         registry       C ONLINE     ONLINE       odap1       0  0
ora.registry.acfs         registry       C ONLINE     ONLINE       odap2       0  0
ora.scan1.vip             SCAN VIP       L ONLINE     ONLINE       odap1       0  0
ora.scan2.vip             SCAN VIP       L ONLINE     ONLINE       odap2       0  0
ora.testdb.db             database       L ONLINE     ONLINE       odap2       0  0 Open
ora.testwz.db             database       L ONLINE     ONLINE       odap1       0  0 Open

After the db is relocated to node 2, I want to do some testings to try out some interesting scenarios

Scenario 1. While db is running on node 2, what's happened if I try to bring it up on node 1?
Of course, everyone know it should not work for single-instance db. But just want to see what is the result.

SYS@mytestdb> startup nomount
ORA-00304: requested INSTANCE_NUMBER is busy

As I expected, the db can not start. Otherwise, who is going to buy Oracle RAC license!

Scenario 2. what happened if I stop the db from node 2 and start the db on node 1 using sqlplus?

On node 1
oracle:mytestdb@odap1 dbs> srvctl stop database -d mytestdb
oracle:mytestdb@odap1 dbs> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 7 10:44:56 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@mytestdb> startup nomount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 822084792 bytes
Database Buffers 3422552064 bytes
Redo Buffers 28909568 bytes
SYS@mytestdb> alter database mount;
Database altered.

SYS@mytestdb> alter database open;
Database altered.

SYS@mytestdb> col host_name for a20
select instance_name, host_name from v$instance;

INSTANCE_NAME HOST_NAME
---------------- --------------------
mytestdb odap1

It's an interesting result as I am expecting it can not start on the node 1. So what's happened if shutdown the db and start the db using srvctl on db node 1.

SYS@mytestdb> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@mytestdb> quit

oracle:mytestdb@odap1 dbs> srvctl start database -d mytestprd
oracle:mytestdb@odap1 dbs> srvctl status database -d mytestprd
Instance mytestdb is running on node odap2
oracle:mytestdb@odap1 dbs> srvctl status service -d mytestprd -s mytest
Service mytest is running on instance(s) mytestdb

Not surprise. The db and service were started on db node 2 as expected.

Scenario 3. Based on the scenario 2 with db startup on node 1, can I start service on node 1 as well?

oracle:mytestdb@odap1 dbs> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 7 10:59:20 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.
SYS@mytestdb> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2235208 bytes
Variable Size 822084792 bytes
Database Buffers 3422552064 bytes
Redo Buffers 28909568 bytes
Database mounted.
Database opened.
SYS@mytestdb> quit

oracle:mytestdb@odap1 dbs> srvctl status database -d mytestprd
Instance mytestdb is not running on node odap2
oracle:mytestdb@odap1 dbs> srvctl status service -d mytestprd -s mytest
Service mytest is not running.
oracle:mytestdb@odap1 dbs> srvctl start service -d mytestprd -s mytest
PRCD-1084 : Failed to start service mytest
PRCR-1079 : Failed to start resource ora.mytestprd.mytest.svc
CRS-5017: The resource action "ora.mytestprd.db start" encountered the following error:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0.3/grid/log/odap2/agent/crsd/oraagent_oracle/oraagent_oracle.log".

CRS-2674: Start of 'ora.mytestprd.db' on 'odap2' failed
CRS-2632: There are no more servers to try to place resource 'ora.mytestprd.mytest.svc' on that would satisfy its placement policy

oracle:mytestdb@odap1 dbs> ls -l /u01/app/11.2.0.3/grid/log/odap2/agent/crsd/oraagent_oracle/oraagent_oracle.log
ls: /u01/app/11.2.0.3/grid/log/odap2/agent/crsd/oraagent_oracle/oraagent_oracle.log: No such file or directory

There is no odap2 directory under /u01/app/11.2.0.3/grid/log on node 1. It is on db node 2.
oracle:mytestdb@odap1 dbs> ls -l /u01/app/11.2.0.3/grid/log
total 12
drwxr-xr-x 2 grid oinstall 4096 Apr 5 22:29 crs
drwxr-xr-t 25 root oinstall 4096 Jul 1 12:10 odap1
drwxrwxr-x 5 grid asmadmin 4096 Jul 1 12:44 diag

oracle:mytestdb@odap1 dbs> crsstat | egrep 'Target|mytest'
Resource Name             Resource Type    Target State    Node            FC RC State Details
ora.mytestprd.db          database       L ONLINE OFFLINE  (odap2)     0  0 Instance Shutdown
ora.mytestprd.mytest.svc  service        L ONLINE OFFLINE  (odap2)     0  0

Therefore, from the above test case, once configured the db starting up on a different node. You can bring up the db from either database nodes using sqlplus. However, if you are using service name, you have to start db from the server you associate db with by the "srvctl modify database" command. In general, this trick can be used in non-RAC environment on ODA, Exadata and any cluster that is using the same GI across multiple nodes.