Operating the database

• 1: Starting the database
• 2: Stopping the database
• 3: CRC and sort order check
• 3.1: Evaluating CRC errors
• 3.2: Evaluating sort order errors

1 - Starting the database

You can start a database through one of the following:

• Management Console

• Administration Tools

• Command Line

Administration tools

1. Open the Administration Tools and select View Database Cluster State to make sure that all nodes are down and that no other database is running.

2. Open the Administration Tools. See Using the administration tools for information about accessing the Administration Tools.

3. On the Main Menu, select Start Database,and then select OK.

4. Select the database to start, and then click OK.

   Caution

   You should start only one database at a time. If you start more than one database at any time, the results can be unpredictable. Users might encounter resource conflicts or perform operations on the wrong database.

5. Enter the database password and click OK.

6. When prompted that the database started successfully, click OK.

7. Check the log files to make sure that no startup problems occurred.

Command line

You can start a database with the command line tool start_db:

$ /opt/vertica/bin/admintools -t start_db -d db-name \
     [-p password] [-F] [-s host1,host2,...]

The following example uses start_db to start a single-node database:

$ /opt/vertica/bin/admintools -t start_db -d VMart
Info:
no password specified, using none
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (DOWN)
Node Status: v_vmart_node0001: (UP)
Database VMart started successfully

Start just the primary nodes in an Eon Mode database

You can start just the primary nodes in an Eon Mode database using the command line. Pass start_db the list of all primary nodes in your database using the -s option.

The hosts for the primary nodes must already be up to start the database. The start_db tool cannot start stopped hosts (such as cloud-based VMs). You must either manually start the hosts or use the MC to start the cluster.

The following example starts the three primary nodes in a six-node Eon Mode database:

$ admintools -t start_db -d verticadb -p 'password' \
   -s 10.11.12.10,10.11.12.20,10.11.12.30
    Starting nodes:
        v_verticadb_node0001 (10.11.12.10)
        v_verticadb_node0002 (10.11.12.20)
        v_verticadb_node0003 (10.11.12.30)
    Starting Vertica on all nodes. Please wait, databases with a large catalog may take a while to initialize.
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (DOWN) v_verticadb_node0002: (DOWN) v_verticadb_node0003: (DOWN)
    Node Status: v_verticadb_node0001: (UP) v_verticadb_node0002: (UP) v_verticadb_node0003: (UP)
Syncing catalog on verticadb with 2000 attempts.
Database verticadb: Startup Succeeded.  All Nodes are UP

After the database starts, the secondary subclusters are down. You can choose to start them if you need them. See Starting a Subcluster.

Start database with a subset of primary nodes

You can start an Eon Mode database with less than the full set of primary nodes. However, this method is not a best practice. Vertica recommends that you always start your database using all of the primary nodes. You may choose to start a subset of primary nodes if for some reason you cannot start the hosts for all the primary nodes.

Starting the database this way is difficult because the list of hosts you pass to start_db must:

• Include a quorum of nodes (at least 50% + 1 of the total number of primary nodes in the cluster).

• Have shard coverage of all shards in communal storage. The primary nodes you use to start the database do not attempt to rebalance shard subscriptions while starting up.

If either or both of these conditions are not met, the start_db tool returns an error.

This example attempts to start a database with only 3 of the 9 primary nodes:

$ admintools -t start_db -d verticadb -p 'password' \
    -s 10.11.12.10,10.11.12.20,10.11.12.30
    Starting nodes:
        v_verticadb_node0001 (10.11.12.10)
        v_verticadb_node0002 (10.11.12.20)
        v_verticadb_node0003 (10.11.12.30)
Error: Quorum not satisfied for verticadb.
    3 < minimum 5  of 9 primary nodes.
Attempted to start the following nodes:
Primary
        v_verticadb_node0001 (10.11.12.10)
        v_verticadb_node0003 (10.11.12.30)
        v_verticadb_node0002 (10.11.12.20)
Secondary

 hint: you may want to start all primary nodes in the database
Database start up failed.  Cluster partitioned.

If you attempt to start the database with fewer than the full set of primary nodes and the cluster fails to start, Vertica processes may remain running on some of the hosts. In this case, if you attempt to start the database again, you will receive an error similar to the following:

Error: the vertica process for the database is running on the following hosts:
10.11.12.10
10.11.12.20
10.11.12.30
This may be because the process has not completed previous shutdown activities. Please wait and retry again.
Database start up failed.  Processes still running.
Database verticadb did not start successfully: Processes still running.. Hint: you may need to start all primary nodes.

Before you can start the database, you must stop the Vertica server process on the hosts listed in the error message. Use the admintools menus (see Stopping Vertica on host) or the admintools command line's stop_host tool:

$ admintools -t stop_host -s 10.11.12.10,10.11.12.20,10.11.12.30

2 - Stopping the database

There are many occasions when you must stop a database, for example, before an upgrade or performing various maintenance tasks. You can stop a running database through one of the following:

• Administration Tools interface

• Command line
  

You cannot stop a running database if any users are connected or Database Designer is building or deploying a database design.

Administration tools

To stop a running database with admintools:

1. Verify that all cluster nodes are up. If any nodes are down, identify and restart them.

2. Close all user sessions:

   • Identify all users with active sessions by querying the SESSIONS system table. Notify users of the impending shutdown and request them to shut down their sessions.

   • Prevent users from starting new sessions by temporarily resetting configuration parameter MaxClientSessions to 0:

     => ALTER DATABASE DEFAULT SET MaxClientSessions = 0;
     

   • Close all remaining user sessions with Vertica functions CLOSE_SESSION and CLOSE_ALL_SESSIONS.

   Note

   You can also force a database shutdown and block new sessions with the function SHUTDOWN.

3. Open Vertica Administration Tools.

4. From the Main Menu:

   • Select Stop Database

   • Click OK

5. Select the database to stop and click OK.

6. Enter the password (if asked) and click OK.

7. When prompted that database shutdown is complete, click OK.

Command line

You can stop a database with the command line tool stop_db:

$ /opt/vertica/bin/admintools -t stop_db -d db-name [-p password] [-F | --force]

If you omit the -F (or --force) option, the command checks for active sessions. If users are connected to the database, the command aborts with an error message and lists all active sessions. For example:

$ /opt/vertica/bin/admintools -t stop_db -d VMart
Info: no password specified, using none

         Active session details
| Session id                     | Host Ip       | Connected User |
| ------- --                     | ---- --       | --------- ---- |
| v_vmart_node0001-91901:0x162   | 10.20.100.247 | ryan           |
Database VMart not stopped successfully for the following reason:
Unexpected output from shutdown: Shutdown: aborting shutdown
NOTICE: Cannot shut down while users are connected

Use the option -F (or --force) to override user connections and force a shutdown.

3 - CRC and sort order check

As a superuser, you can run the Index tool on a Vertica database to perform two tasks:

• Run a cyclic redundancy check (CRC) on each block of existing data storage to check the data integrity of ROS data blocks.

• Check that the sort order in ROS containers is correct.

If the database is down, invoke the Index tool from the Linux command line. If the database is up, invoke from VSQL with Vertica meta-function RUN_INDEX_TOOL:

If invoked from the command line, the Index tool runs only on the current node. However, you can run the Index tool on multiple nodes simultaneously.

Result output

The Index tool writes summary information about its operation to standard output; detailed information on results is logged in one of two locations, depending on the environment where you invoke the tool:

For information about evaluating output for possible errors, see:

• Evaluating CRC errors
• Evaluating sort order errors

Optimizing performance

You can optimize meta-function performance by narrowing the scope of the operation to one or more projections, and specifying the number of threads used to execute the function. For details, see RUN_INDEX_TOOL.

3.1 - Evaluating CRC errors

Vertica evaluates the CRC values in each ROS data block each time it fetches data disk to process a query. If CRC errors occur while fetching data, the following information is written to the vertica.log file:

CRC Check Failure Details:File Name:
File Offset:
Compressed size in file:
Memory Address of Read Buffer:
Pointer to Compressed Data:
Memory Contents:

The Event Manager is also notified of CRC errors, so you can use an SNMP trap to capture CRC errors:

"CRC mismatch detected on file <file_path>. File may be corrupted. Please check hardware and drivers."

If you run a query from vsql, ODBC, or JDBC, the query returns a FileColumnReader ERROR. This message indicates that a specific block's CRC does not match a given record as follows:

hint: Data file may be corrupt.  Ensure that all hardware (disk and memory) is working properly.
Possible solutions are to delete the file <pathname> while the node is down, and then allow the node
to recover, or truncate the table data.code: ERRCODE_DATA_CORRUPTED

3.2 - Evaluating sort order errors

If ROS data is not sorted correctly in the projection's order, query results that rely on sorted data will be incorrect. You can use the Index tool to check the ROS sort order if you suspect or detect incorrect query results. The Index tool evaluates each ROS row to determine whether it is sorted correctly. If the check locates a row that is not in order, it writes an error message to the log file with the row number and contents of the unsorted row.

Reviewing errors

1. Open the indextool.log file. For example:

   $ cd VMart/v_check_node0001_catalog
   

2. Look for error messages that include an OID number and the string Sort Order Violation. For example:

   <INFO> ...on oid 45035996273723545: Sort Order Violation:
   

3. Find detailed information about the sort order violation string by running grep on indextool.log. For example, the following command returns the line before each string (-B1), and the four lines that follow (-A4):

   [15:07:55][vertica-s1]: grep -B1 -A4 'Sort Order Violation:' /my_host/databases/check/v_check_node0001_catalog/indextool.log
   2012-06-14 14:07:13.686 unknown:0x7fe1da7a1950 [EE] <INFO> An error occurred when running index tool thread on oid 45035996273723537:
   Sort Order Violation:
   Row Position: 624
   Column Index: 0
   Last Row: 2576000
   This Row: 2575000
   --
   2012-06-14 14:07:13.687 unknown:0x7fe1dafa2950 [EE] <INFO> An error occurred when running index tool thread on oid 45035996273723545:
   Sort Order Violation:
   Row Position: 3
   Column Index: 0
   Last Row: 4
   This Row: 2
   --
   

4. Find the projection where a sort order violation occurred by querying system table STORAGE_CONTAINERS. Use a storage_oid equal to the OID value listed in indextool.log. For example:

   => SELECT * FROM storage_containers WHERE storage_oid = 45035996273723545;