Switchover Failed in Data Guard Broker

Recently I did some Data Guard tests on 11.2.0.3 RAC. Both primary and standby databases were on different Exadata QuarterRack. During one test, I might mess up some data guard parameters. When I performed switchover operation wzsdb->wzpdb, it failed in the middle of the process. This is an interesing scenario I have never run into in the past. Here is the result from the execution:

DGMGRL> show configuration
Configuration - DG_Config
  Protection Mode: MaxPerformance
  Databases:
	wzsdb - Primary database
	wzpdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> switchover to wzpdb
Performing switchover NOW, please wait...
Error: ORA-16664: unable to receive the result from a database
Failed.
Unable to switchover, primary database is still "wzsdb"

two_primary
Majority of the time when there is an issue during the switchover using DG Broker, bounce both new primary database and new standby can usually resolve the issue. It didn’t work this time. Tried multiple bounce of both databases, restarted MRP manually. None of them works. Both databases claimed to be Primary database in the DG Broker, just like the two bears above. Here is what the result from DG Broker looks like.

Database wzpdb (Supposed new primary database)
DGMGRL> show configuration
Configuration – DG_Config
Protection Mode: MaxPerformance
Databases:
wzsdb – Primary database
wzpdb – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-16623: database detected role change
ORA-16625: cannot reach database “wzsdb”
DGM-17017: unable to determine configuration status

Database wzsdb (Supposed new standby database)
DGMGRL> show configuration
Configuration – DG_Config
Protection Mode: MaxPerformance
Databases:
wzpdb – Primary database
wzsdb – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-16597: Data Guard broker detects two or more primary databases
ORA-16625: cannot reach database “wzpdb”
DGM-17017: unable to determine configuration status

Obvious I should not have two primary databases in Data Guard. Next thing I would like to check whether this is the issue inside Data Guard Broker. I run the the following queries on both databases.
Database wzpdb (Supposed new primary database)

SYS:wzdb1> @db_mode
    INST_ID DATABASE_ROLE    OPEN_MODE            LOG_MODE     FLASHBACK_ON       FOR
----------- ---------------- -------------------- ------------ ------------------ ---
          1 PRIMARY          READ WRITE           ARCHIVELOG   NO                 YES
          2 PRIMARY          READ WRITE           ARCHIVELOG   NO                 YES

The above result is what I expected and wzpdb is in primary role

Database wzsdb (Supposed new standby database)

SYS@wzdb1> @db_mode
   INST_ID DATABASE_ROLE    OPEN_MODE            LOG_MODE     FLASHBACK_ON       FOR
---------- ---------------- -------------------- ------------ ------------------ ---
	 2 PHYSICAL STANDBY MOUNTED              ARCHIVELOG   NO                 YES
	 1 PHYSICAL STANDBY MOUNTED              ARCHIVELOG   NO                 YES

The result is also correct in wzsdb and it is in standby role.

Tried to test out MRP process

SYS@wzdb1> @dg_sby_process
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
8 rows selected.

SYS@wzdb1> alter database recover managed standby database using current logfile disconnect;
Database altered.				 

SYS@wzdb1> @dg_sby_process
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
MRP0      N/A             168 WAIT_FOR_LOG
9 rows selected.

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
MRP0      WAIT_FOR_LOG          1        168          0          0

The data guard processes on standby also looked ok. Retried show configuration command on both databases and got the same errors. At this moment, it seems like the solution is to recreate the DG Broker. So I went ahead and do the followings to recreate the brokers:
Standby Database (wzsdb)
Step 1. Make sure to stop MRP first
SYS@wzdb1> alter database recover managed standby database cancel;
Database altered.
Step 2. Stop dg broker and remove the files
SYS@wzdb1> alter system set dg_broker_start=false scope=both sid=’*’;
System altered.

ASMCMD> cd +data/wzsdb/dataguardconfig
ASMCMD> ls
dgb_config01.ora
wzsdb.1188.852022993
ASMCMD> rm dgb_config*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> cd +reco/wzsdb/dataguardconfig
ASMCMD> ls
dgb_config02.ora
wzsdb.1049.852022971
ASMCMD> rm dgb_config*
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> ls

Step 3. Recreate the broker
SYS@wzdb1> alter system set dg_broker_start=false scope=both sid=’*’;
System altered.
SYS@wzdb1> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1=’+reco/wzsdb/DATAGUARDCONFIG/dgb_config02.ora’ SCOPE=BOTH sid=’*’;
System altered.
SYS@wzdb1> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2=’+data/wzsdb/DATAGUARDCONFIG/dgb_config01.ora’ SCOPE=BOTH sid=’*’;
System altered.
SYS@wzdb1> alter system set dg_broker_start=true scope=both sid=’*’;
System altered.

Primary Database (wzpdb)
Perform similar steps as above on primary database.

Reconfigure the DG Broker
At this moment, both dg broker on both primary and standby were started. Configure and enable the configuration.
[enkdb01:oracle:wzdb1] /home/oracle/wzhou/dg
> dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 – 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist
Configuration details cannot be determined by DGMGRL

DGMGRL> CREATE CONFIGURATION ‘DG_Config’ AS PRIMARY DATABASE IS ‘wzpdb’ CONNECT IDENTIFIER IS ‘wzpdb’;
Configuration “DG_Config” created with primary database “wzpdb”
DGMGRL> ADD DATABASE ‘wzsdb’ AS CONNECT IDENTIFIER IS wzsdb;
Database “wzsdb” added
DGMGRL> enable configuration;
Enabled.

Let see the result.

DGMGRL> show configuration
Configuration - DG_Config
  Protection Mode: MaxPerformance
  Databases:
	wzpdb - Primary database
	wzsdb - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

We are back in business. Another possible solution is not to recreate the DG Broker files completely, but just remove the DG Broker configuration from dgmgrl, and then recreate the the configuration in dgmgrl. Next time if I run into the similar issue, I will try it out.

Script used in the blog:
db_mode.sql
select inst_id, database_role, open_mode, log_mode, flashback_on, force_logging from gv$database;

dg_sby_process.sql

select process, client_process, sequence#, status from v$managed_standby;select process, status, thread#, sequence#, block#, blocks from gv$managed_standby where thread# <> 0;

Exachk Database Only on Exadata

As many people know, exachk is a great utility to perform the healthchk on Exadata and provides many useful information about errors and warnings. When running exachk, the most common way is to run with -a option as follows:
./exachk -a

The result covers everything from OS, database, clusterware, db nodes, cell nodes and IB switches. To execute -a option, you need to know root password for many componenets, like db nodes, and cell nodes. Sometimes Oracle DBAs do not have the  root access, but still want to use the features from exachk utility, just to check out database configuration. Starting v2.2.1, exachk allows the execution of a subset of the checks. For example, you could check a subset of cluster nodes, or just check only a selected cell nodes or IB switches. In this blog, I am going to discuss database only (my own piece of pie).

apple_pie

First, Go to Oracle Support, check out Oracle Exadata Database Machine exachk or HealthCheck (Doc ID 1070954.1) and download the latest copy of the exachk (v2.2.4).

Next, run the following command to execut exachk against a database, for example dbm database. I highlight the line you need to provide an input.
$ ./exachk -dbnames dbm

CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/11.2.0.3/grid?[y/n][y]y
Checking ssh user equivalency settings on all nodes in cluster
Node enkx3db02 is configured for ssh user equivalency for oracle user

Checking Status of Oracle Software Stack – Clusterware, ASM, RDBMS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
——————————————————————————————————-
Oracle Stack Status
——————————————————————————————————-
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
——————————————————————————————————-
enkx3db01 Yes Yes Yes Yes Yes Yes dbm1
enkx3db02 Yes Yes Yes Yes Yes Yes dbm2
——————————————————————————————————-

Copying plug-ins
. . . . . . . . . . . . . . . . . . . . . . . . . . .

root user equivalence is not setup between enkx3db01 and STORAGE SERVER enkx3cel01 (192.168.12.3).
1. Enter 1 if you will enter root password for each STORAGE SERVER when prompted.
2. Enter 2 to exit and configure root user equivalence manually and re-run exachk.
3. Enter 3 to skip checking best practices on STORAGE SERVER.

Please indicate your selection from one of the above options for STORAGE SERVER[1-3][1]:- 3
. . . . . . .

125 of the included audit checks require root privileged data collection on DATABASE SERVER. If sudo is not configured or the root password is not available, audit checks which require root privileged data collection can be skipped.

1. Enter 1 if you will enter root password for each on DATABASE SERVER host when prompted
2. Enter 2 if you have sudo configured for oracle user to execute root_exachk.sh script on DATABASE SERVER
3. Enter 3 to skip the root privileged collections on DATABASE SERVER
4. Enter 4 to exit and work with the SA to configure sudo on DATABASE SERVER or to arrange for root access and run the tool later.

Please indicate your selection from one of the above options for root access[1-4][1]:- 3

*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***
Checking for prompts in /home/oracle/.bash_profile on enkx3db01 for oracle user…
Checking for prompts in /home/oracle/.bash_profile on enkx3db02 for oracle user…

Starting to run exachk in background on enkx3db02

=============================================================
Node name – enkx3db01
=============================================================

Collecting – ASM Diskgroup Attributes
Collecting – ASM initialization parameters
Collecting – Database Parameters for dbm database
Collecting – Database Undocumented Parameters for dbm database
Collecting – RDBMS Feature Usage for dbm database
Collecting – CPU Information
Collecting – Clusterware and RDBMS software version
Collecting – Compute node PCI bus slot speed for infiniband HCAs
Collecting – Kernel parameters
Collecting – Maximum number of semaphore sets on system
Collecting – Maximum number of semaphores on system
Collecting – Patches for Grid Infrastructure
Collecting – Patches for RDBMS Home
Collecting – RDBMS patch inventory
Collecting – number of semaphore operations per semop system call

. . . .

A few notes:
1. If there is a high security requirement, usually you can not directly ssh into db node as oracle user. You have to login as your user id, and then su to oracle user. In this case, you need to add localnode option to force the execution running locally.
$ ./exachk -dbnames dbm -localnode

2. If you want to check multiple databases at the same time, you can specify the databases separate by comma as follows:
$ ./exachk -dbnames dbm,wzpdb

Make sure the databases list above has an entry in /etc/oratab file. If there is not match, there will be no check against the mismatched database.

3. If the database does not have Data Guard configuration, you might want to add -m option to exclude checks for Maximum Availability Architecture (MAA).
$ ./exachk -dbnames dbm,wzpdb -m

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.