Sharding Database Infrastructure Horizontally - Sharding Oracle – Part III  [Testing Oracle Sharding]

    By: Nassyam Basha on Apr 16, 2018

    binary-2910663_1280.jpg

    By Nassyam Basha, Data Intensity | Simon Pane, Editor 

     

    This article is the final installment in a three-part series on the topic of sharding. Part one provided background and reviewed in depth the architecture of Oracle Sharding and the various technical components. Part two focused on prerequisites and the initial deployment steps.

    In this final article we will walk through the remainder of the setup - post the initial deployment. The main purpose of this article is to test and demonstrate that the data is indeed being distributed across the shards. We will first create a sharded table and then see how the “chunks” will be sharded across all the shard databases. And finally, we will review the various logs which are useful for troubleshooting.

    Post Deployment Verification Checks of the Oracle Sharding Configuration 

    Services Configuration:
    From GDSCTL we can see the list of the databases associated with the sharding configuration. We will create two additional services: one named FINRW for the primary database and another named PSFIN_NVISION for the standby database.  The later will be used for reporting purposes.

    GDSCTL>databases
    Database: "sh1" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: canada
    Alert: Data Guard observer is not running.
       Registered instances:
         shcat%1
    Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: canada
    Alert: Data Guard observer is not running.
       Registered instances:
         shcat%11
    Database: "sh3" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: india
       Registered instances:
         shcat%21
    Database: "sh4" Registered: Y State: Ok ONS: N. Role: PH_STNDBY Instances: 1 Region: india
       Registered instances:
         shcat%31

    GDSCTL>

     

    While creating services we must specify which service belongs to which database role. We have various options in adding the service in terms of failover, preferred options, etc. After adding the services, we need to start them manually.

    GDSCTL>add service -service FINRW -preferred_all -role primary
    The operation completed successfully
    GDSCTL>add sservice -service PSFIN_NVISION -preferred_all -role physical_standby -failover_primary
    The operation completed successfully
    GDSCTL>start service -service FINRW, PSFIN_NVISION
    The operation completed successfully
    GDSCTL>services
    Service "finrw.shcat.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "shcat%1", name: "sh1", db: "sh1", region: "canada", status: ready.
       Instance "shcat%11", name: "sh2", db: "sh2", region: "canada", status: ready.
    Service "psfin_nvision.shcat.oradbcloud" has 2 instance(s). Affinity: ANYWHERE
       Instance "shcat%21", name: "sh3", db: "sh3", region: "india", status: ready.
       Instance "shcat%31", name: "sh4", db: "sh4", region: "india", status: ready.

    GDSCTL>

     

    Disable FSFO-Observer (optional):
    In part two of this article series, we completed all of the required setup up to the point of deployment of sharding. One important point to keep in mind is that in this case we are deploying Sharding with Data Guard. (Sharding by default deploys with the Data Guard Broker and Fast Start Failover (FSFO) configuration.)

    If this Shard deployment is for testing purpose only or if FSFO is not required then we can stop the observer and disable the FSFO. Recognizing that doing so means that if for some reason the primary database is inaccessible or has no response, then the observer will not initiate a failover to the standby and we will have to perform various other steps again to reinstate the shards and re-establish everything.

    Hence based on the business requirement, we can disable or enable FSFO.

    The following steps show how to disable the FSFO.

     

    DGMGRL> show configuration

    Configuration - sh1

      Protection Mode: MaxPerformance
    Members:
    sh1 - Primary database
      sh3 - Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS   (status updated 44 seconds ago)

    DGMGRL> show fast_start failover

    Fast-Start Failover: ENABLED

      Threshold:          30 seconds
    Target:             sh4
    Observer:           ORA-C2.localdomain
    Lag Limit:          30 seconds
    Shutdown Primary:   TRUE
    Auto-reinstate:     TRUE
    Observer Reconnect: (none)
    Observer Override:  FALSE

    Configurable Failover Conditions
    Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Write Errors          YES

      Oracle Error Conditions:
    (none)

    DGMGRL>
    DGMGRL> disable fast_start failover force
    Disabled.
    DGMGRL>  show fast_start failover

    Fast-Start Failover: DISABLED

      Threshold:          30 seconds
    Target:             (none)
    Observer:           ORA-C2.localdomain
    Lag Limit:          30 seconds
    Shutdown Primary:   TRUE
    Auto-reinstate:     TRUE
    Observer Reconnect: (none)
    Observer Override:  FALSE

    Configurable Failover Conditions
    Health Conditions:
      Corrupted Controlfile          YES
      Corrupted Dictionary           YES
      Inaccessible Logfile            NO
      Stuck Archiver                  NO
      Datafile Write Errors          YES

      Oracle Error Conditions:
    (none)

    DGMGRL

    Prepare User and Create Sharded tables:
    In traditional databases we would create generic tables using the normal syntax but in a sharding configuration we have to create a sharded table using new DDL syntax to realize the benefit of the Sharding feature. Oracle will distribute the rows on all the available shards (in sharding terminology we can say the “chunks” will be allocated to all the shards).

    This brings about an interesting question: Where will the Sharded table be created?

    A) The Sharded catalog database
    B) The various Shards – sh1, sh2?

    Answer: The sharded table will be created only in Sharded catalog database, but the actual data (“chunks”) will be distributed to the shard databases as per our configuration.

    So we will create the sharded tables in sharded catalog database (SDB) only. Note that (as detailed in the previous articles in this series) the SDB should be created by the DBCA using Oracle Managed Files (OMF). If not already configured with OMF then it will be mandatory to set the required initialization parameter before creating sharding tables.

    SQL> alter system set db_create_file_dest=’/u01/app/oracle/oradata’;

    After setting the db_create_file_dest parameter, we can create the tablespace (using the new “CREATE TABLESPACE SET” command for creating a sharded tablespace set), user, and grant privileges as required.

    Notice that we need to start by altering our session using a new sharding specific session setting command.  This is to enable sharding DDL meaning that the DDL commands issued will be applicable to the shard catalog database and all shards.

     

    -bash-4.2$ . oraenv
    ORACLE_SID = [gsm] ? shcat
    The Oracle base has been changed from /home/app/oracle to /u01/app/oracle
    -bash-4.2$ sqlplus / as sysdba

    SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 23 08:31:19 2017

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

    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

     

    SQL> alter session enable shard ddl;

    Session altered.

    SQL> create user sysadm identified by sysadm;

    User created.

    SQL> grant connect, resource, alter session to sysadm;

    Grant succeeded.

    SQL> grant execute on dbms_crypto to sysadm;

    Grant succeeded.

    SQL> grant create table, create procedure, create tablespace, create materialized view to sysadm;

    Grant succeeded.

    SQL> grant unlimited tablespace to sysadm;

    Grant succeeded.

    SQL> grant select_catalog_role to sysadm;

    Grant succeeded.

    SQL> grant all privileges to sysadm;

    Grant succeeded.

    SQL> grant gsmadmin_role to sysadm;

    Grant succeeded.

    SQL> grant dba to sysadm;

    Grant succeeded.

    SQL> CREATE TABLESPACE SET PSDATA using template (datafile size 100m autoextend on next 10M maxsize unlimited extent management local segment space management auto );


    Tablespace created.

    SQL> CREATE TABLESPACE PSDATADUP datafile size 100m autoextend on next 10M maxsize unlimited extent management local uniform size 1m;

    Tablespace created.

    Now that the prerequisites for sharded table creation are complete, we can create sharded tables:

    Connected.
    SQL> connect sysadm/sysadm
    SQL> show user
    USER is "sysadm"
    SQL> alter session enable shard ddl;

    Session altered.

    SQL> CREATE SHARDED TABLE student_info
    (
    studId VARCHAR2(60) NOT NULL,
    FirstName VARCHAR2(60),
    LastName VARCHAR2(60),
    Class VARCHAR2(10),
    password RAW(20),
    StudProfile VARCHAR2(40),
    CONSTRAINT pk_studen_info PRIMARY KEY (studid),
    CONSTRAINT json_student_info CHECK (studProfile IS JSON)
    ) TABLESPACE SET PSDATA
    PARTITION BY CONSISTENT HASH (studId) PARTITIONS AUTO;

    Table created.

    SQL>  CREATE SHARDED TABLE exam_info
    2  (
    Hallticket_no INTEGER NOT NULL,
    studId VARCHAR2(60) NOT NULL,
    resultsdate TIMESTAMP NOT NULL,
    SumTotal NUMBER(19,4),
    Status CHAR(4),
    constraint pk_exam_info primary key (studId, hallticket_no),
    constraint fk_exam_info foreign key (studId)
    references student_info on delete cascade
    ) partition by reference (fk_exam_info)
    TABLESPACE PSDATA;
      3    4    5    6    7    8    9   10   11
    Table created.

    SQL> CREATE SEQUENCE exam_info_seq;

    Sequence created.

    SQL> CREATE SHARDED TABLE schooldata
    (
    Hallticket_no INTEGER NOT NULL,
    studId VARCHAR2(60) NOT NULL,
    Reg_no INTEGER NOT NULL,
    constraint pk_data primary key (studId, Hallticket_no, Reg_no),
    constraint fk_data_main foreign key (studId, Hallticket_no)
    references exam_info on delete cascade
    ) partition by reference (fk_data_main)

    TABLESPACE PSDATA;  

    Table created.

    SQL>

     

    “Duplicated” tables are also created in the Shard Catalog. Unlike Shard tables where only a subset the table’s rows are copied to the appropriate Shard database, the entire contents of duplicated tables are presented in each Shard database.

     

    SQL> CREATE DUPLICATED TABLE studdup
    (
    reg_no INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    summary VARCHAR2(128)
    )  TABLESPACE PSDATADUP; 

    Table created.

    SQL>

     

    Other non-table objects are also created in the Shard Catalog database only and are automatically propagated out to the Shard Databases:

    SQL> CREATE OR REPLACE FUNCTION PasswCreate(password IN RAW)
    RETURN RAW
    IS
    Salt RAW(8);
    BEGIN
    Salt := DBMS_CRYPTO.RANDOMBYTES(8);
    RETURN UTL_RAW.CONCAT(Salt, DBMS_CRYPTO.HASH(UTL_RAW.CONCAT(Salt, password), DBMS_CRYPTO.HASH_SH256));
    END;
    /

    Function created.

    SQL> CREATE OR REPLACE FUNCTION PasswCheck(password IN RAW, PHASH IN RAW)
    RETURN INTEGER IS
    BEGIN
    RETURN UTL_RAW.COMPARE(
    DBMS_CRYPTO.HASH(UTL_RAW.CONCAT(UTL_RAW.SUBSTR(PHASH, 1, 8), password), DBMS_CRYPTO.HASH_SH256),
    UTL_RAW.SUBSTR(PHASH, 9));
    END;
    /

    Function created.

    SQL>

     

    Testing the Sharded Table
    To review the Shard implementation, we can run some basic queries and commands from the SDB:

    SQL> select tablespace_name,bytes/1024/1024,user_bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_data_files where tablespace_name in ('PSDATA','PSDATADUP');

    TABLESPACE_NAME                BYTES/1024/1024 USER_BYTES/1024/1024 MAXBYTES/1024/1024 AUT

    ------------------------------ --------------- -------------------- ------------------ ---

    PSDATA                                     100             93.96875           33554432 YES

    PSDATADUP                                  100                   99         32767.9844 YES

    SQL>

     

    -bash-4.2$ gdsctl
    GDSCTL: Version 12.2.0.1.0 - Production on Sun Jul 23 10:00:37 IST 2017

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

    Welcome to GDSCTL, type "help" for information.

    Current GSM is set to SHARDDIR_DG
    GDSCTL>show ddl
    Catalog connection is established

    id      DDL Text                                 Failed shards

    --      --------                                 -------------

    26      CREATE SHARDED TABLE student_info ( s...
    27       CREATE SHARDED TABLE exam_info ( Hal...
    28      CREATE SEQUENCE exam_info_Seq
    29      CREATE SHARDED TABLE schooldata ( Hal...
    30      CREATE MATERIALIZED VIEW "psfinadm"."ST...
    31      CREATE OR REPLACE FUNCTION PasswCreat...
    32      CREATE OR REPLACE FUNCTION PasswCheck...
    33      CREATE OR REPLACE FUNCTION PasswCheck...

    GDSCTL>

     

    The objective of the sharding is to have the chunks evenly distributed among the Shard databases.  We can verify that this has indeed been accomplished using a simple query:

    SQL> select a.name Shard, count( b.chunk_number) Number_of_Chunks from gsmadmin_internal.database a, gsmadmin_internal.chunk_loc b where a.database_num=b.database_num group by a.name;

    SHARD                          NUMBER_OF_CHUNKS
    ------------------------------ ----------------
    sh1                                           6
    sh2                                           6
    sh3                                           6
    sh4                                           6

    SQL>  select table_name,partition_name from user_tab_partitions;

    TABLE_NAME                    PARTITION_NAME
    ------------------------------ --------------------------------------------------
    EXAM_INFO                     STUDENT_INFO_P1
    SCHOOLDATA                  STUDENT_INFO_P1
    STUDENT_INFO                STUDENT_INFO_P1

    From the output above we can see a successful shard implementation where the chunks are indeed evenly distributed among the shard databases.

    Connectivity using Easy Connect
    To run cross-shard queries, we should connect to the “GDS$CATALOG” service from any of the shards. To connect to the shard catalog database we use the EZconnect format. (Ensure the service “GDS$CATALOG” was registered the listener.)

    Service_name
    Services Summary...
    Service "GDS$CATALOG.oradbcloud" has 1 instance(s).
      Instance "shcat", status READY, has 1 handler(s) for this service...
    Service "shcat" has 2 instance(s).

    -bash-4.2$ sqlplus sysadm/sysadm@ora-ccrep.localdomain:1525/GDS\$CATALOG.oradbcloud

    SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 22 15:37:30 2017
    Copyright (c) 1982, 2016, Oracle.  All rights reserved.
    Last Successful login time: Sun Oct 22 2017 15:37:20 +05:30
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    SQL> show parameter db_name

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_name                              string      shcat
    SQL>

     

    Question: Is it possible to have more shards in primary site and fewer shards in standby site?

    Answer: No, you should have same number of shards on both primary and standby sites.

    Finally, we can see new sharding specific SQL operations in execution plans.  For example:

     

    SQL> select * from schooldata;

    Execution Plan
    ------------------------------------------------------------
    Plan hash value: 2953441084

    -----------------------------------------------------------------
    | Id  | Operation       | Name | Cost (%CPU) | Inst   | IN-OUT  |
    ------------------------------------------------------------------ 
    |   0 | SELECT STATEMENT |      |     0   (0) |        |         |               
    |   1 |  SHARD ITERATOR  |      |             |        |         |                
    |   2 |   REMOTE        |      |             | ORA_S~ | R->S    |
    ------------------------------------------------------------------

    Remote SQL Information (identified by operation id):

    -----------------------------------------------------

       2 - EXPLAIN PLAN SET STATEMENT_ID='PLUS330062' INTO PLAN_TABLE@! FOR
            SELECT "A1"."HALLTICKET_NO","A1"."STUDID","A1"."REG_NO" FROM
            SCHOOLDATA" "A1" /* coord_sql_id=bj6cbuthszkpb */  (accessing
            ORA_SHARD_POOL@ORA_MULTI_TARGET' )

    Troubleshooting (using the log files)
    It can sometimes seem challenging or difficult for DBAs to troubleshoot and fix issues with Sharding configurations.. To properly diagnose and fix problems, we have to look into various log files depending on the issue we are facing.  It may be necessary to look at sharding related logs, database logs, broker logs etc.

    Repository GSM Log
    If we encounter any issues with GSM then we have dedicated log and associated trace files for the Global Service manager on each director. The trace file specifics can be shown by using the “status gsm” command from GDSCTL.  And from the gsm.log we can see the sessions and services details:.

     

    /home/app/oracle/diag/gsm/ora-ccrep/sharddir_dg/trace
    -bash-4.2$ tail -10f alert_gsm.log
    22-OCT-2017 15:13:34 * service_update * shcat%11 * 0
    22-OCT-2017 15:13:35 * service_update * shcat%21 * 0
    2017-10-22T15:13:59.366867+05:30
    22-OCT-2017 15:13:59 * service_update * shcat%31 * 0
    2017-10-22T15:14:00.383964+05:30
    22-OCT-2017 15:14:00 * service_update * SHARDDIR_DG * 0
    2017-10-22T15:14:04.409469+05:30
    22-OCT-2017 15:14:04 * service_update * shcat%1 * 0
    22-OCT-2017 15:14:04 * service_update * shcat%11 * 0
    22-OCT-2017 15:14:05 * service_update * shcat%21 * 0

    GDSCTL>status gsm
    Alias                     SHARDDIR_DG
    Version                   12.2.0.1.0
    Start Date                17-OCT-2017 08:35:39
    Trace Level               off
    Listener Log File         /home/app/oracle/diag/gsm/ora-ccrep/sharddir_dg/alert/log.xml
    Listener Trace File       /home/app/oracle/diag/gsm/ora-ccrep/sharddir_dg/trace/ora_8131_140327754584448.trc
    Endpoint summary          (ADDRESS=(HOST=ora-ccrep.localdomain)(PORT=12121)(PROTOCOL=tcp))
    GSMOCI Version            2.2.1
    Mastership                Y
    Connected to GDS catalog  Y
    Process Id                8134
    Number of reconnections   0
    Pending tasks.     Total  0
    Tasks in  process. Total  0
    Regional Mastership       TRUE
    Total messages published  8795
    Time Zone                 +05:30
    Orphaned Buddy Regions:
         None
    GDS region                europe

    GDSCTL>

    Deployment Logs
    If there are any issues when deploying the shard configuration we will unfortunately only see one or two lines in the CLI interface. Therefore, to get additional detail on any issues encountered we may have to review the log files to find the actual cause of the issue.

    The sharding deployment is sequential going from node to node serially. If the deployment of the primary shard sh1 fails for some reason, there will be minimal information visible in the CLI interface.  Hence, to diagnose we will have to review the deployment log which can be found under  “$ORACLE_BASE/cfgtoollogs/dbca/<shardname>”, for example
    /u01/app/oracle/cfgtoollogs/dbca/sh1/trace.log_<timestamp>

    [Thread-96] [ 2017-07-23 06:30:29.460 NZST ] [StepErrorHandler.setIgnorableErrors:267]  setting Ignorable Error: ORA-06510
    [Thread-96] [ 2017-07-23 06:30:29.461 NZST ] [StepErrorHandler.setIgnorableErrors:267]  setting Ignorable Error: ORA-01403
    [Thread-96] [ 2017-07-23 06:30:29.461 NZST ] [BasicStep.configureSettings:383]  messageHandler being set=null
    [Thread-96] [ 2017-07-23 06:30:29.570 NZST ] [SQLEngine.setSpool:2084]  old Spool  = null[Thread-96] [ 2017-07-23 06:30:29.570 NZST ] [SQLEngine.setSpool:2085]  Setting Spool  = /u01/app/oracle/cfgtoollogs/dbca/sh1/cloneDB
    Creation.log
    [Thread-96] [ 2017-07-23 06:30:29.571 NZST ] [SQLEngine.setSpool:2086]  Is spool appendable? --> true
    [Thread-96] [ 2017-07-23 06:30:29.572 NZST ] [CloneDBCreationStep.executeImpl:382]   size of datfiles in create db 4
    [Thread-96] [ 2017-07-23 06:30:29.573 NZST ] [CloneDBCreationStep.executeImpl:408]  a.createCtlFileSql = Create controlfile reuse set database "sh1"
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    Datafile
    '/u01/app/oracle/oradata/sh1/sysaux01.dbf',
    '/u01/app/oracle/oradata/sh1/system01.dbf',
    [Thread-96] [ 2017-07-23 06:30:29.573 NZST ] [CloneDBCreationStep.executeImpl:412]  b.createCtlFileSql = Create controlfile reuse set database "sh1"

    Also, we can see the associated files that are part of deployment as well.

    [oracle@ORA-C1 sh1]$ pwd
    /u01/app/oracle/cfgtoollogs/dbca/sh1
    [oracle@ORA-C1 sh1]$ ls -ltr
    total 19008
    -rw-r-----. 1 oracle oinstall        0 Jul 23 06:28 rmanUtil
    -rw-r-----. 1 oracle oinstall 18726912 Jul 23 06:30 tempControl.ctl
    -rw-r-----. 1 oracle oinstall      379 Jul 23 06:30 CloneRmanRestore.log
    -rw-r-----. 1 oracle oinstall     2822 Jul 23 06:31 cloneDBCreation.log
    -rw-r-----. 1 oracle oinstall        8 Jul 23 06:31 postScripts.log
    -rw-r-----. 1 oracle oinstall        0 Jul 23 06:32 lockAccount.log
    -rw-r-----. 1 oracle oinstall     1274 Jul 23 06:33 postDBCreation.log
    -rw-r-----. 1 oracle oinstall      334 Jul 23 06:34 customScripts.log
    -rw-r-----. 1 oracle oinstall      915 Jul 23 06:34 sh1.log
    -rw-r-----. 1 oracle oinstall   706980 Oct 22 20:21 trace.log_2017-07-23_06-28-03-AM
    [oracle@ORA-C1 sh1]$

    Another key log file is “<shardname>.log” which will record the overall status and progress of the shard deployment

    [oracle@ORA-C1 sh1]$ cat sh1.log
    [ 2017-07-23 06:28:14.295 NZST ] Copying database files
    DBCA_PROGRESS : 1%
    DBCA_PROGRESS : 2%
    DBCA_PROGRESS : 16%
    DBCA_PROGRESS : 30%
    [ 2017-07-23 06:30:29.457 NZST ] Creating and starting Oracle instance
    DBCA_PROGRESS : 32%
    DBCA_PROGRESS : 36%
    DBCA_PROGRESS : 40%
    DBCA_PROGRESS : 44%
    DBCA_PROGRESS : 45%
    DBCA_PROGRESS : 48%
    DBCA_PROGRESS : 50%
    [ 2017-07-23 06:32:00.969 NZST ] Completing Database Creation
    DBCA_PROGRESS : 51%
    DBCA_PROGRESS : 52%
    DBCA_PROGRESS : 53%
    DBCA_PROGRESS : 56%
    DBCA_PROGRESS : 59%
    DBCA_PROGRESS : 60%
    [ 2017-07-23 06:33:13.537 NZST ] Executing Post Configuration Actions
    DBCA_PROGRESS : 90%
    [ 2017-07-23 06:33:13.539 NZST ] Running Custom Scripts
    DBCA_PROGRESS : 100%
    [ 2017-07-23 06:34:37.047 NZST ] Database creation complete. For details check the logfiles at:
     /u01/app/oracle/cfgtoollogs/dbca/sh1.
    Database Information:
    Global Database Name:sh1.localdomain
    System Identifier(SID):sh1
    [oracle@ORA-C1 sh1]$

    Troubleshooting using GDSCTL 

    Using GDSCTL we can perform some troubleshooting such as checking the databases and services status. The beauty of GDSCTL in Sharding can be described with two key benefits: validation and DDL tracking.

    Validate using GDSCTL.

    The entire configuration can be validated from GDSCTL with single command.  It can validate regions, databases, Shard directors, DDL operations, etc. The example below shows connection errors (as an example) due to listeners being down: 

    GDSCTL>validate
    Validation results: 

    Catalog connection is established
    VLD2: Region "europe" does not have buddy region
    VLD2: Region "canada" does not have buddy region
    VLD2: Region "india" does not have buddy region
    VLD9: Region "europe" does not contain any databases
    VLD10: Region "canada" does not contain any GSMs
    VLD10: Region "india" does not contain any GSMs
    VLD49: Database sh2: last applied DDL number is wrong. On Shard: 9, in catalog: 48
    VLD49: Database sh1: last applied DDL number is wrong. On Shard: 9, in catalog: 48
    VLD49: Database sh4: last applied DDL number is wrong. On Shard: 9, in catalog: 48
    VLD49: Database sh3: last applied DDL number is wrong. On Shard: 9, in catalog: 48
    VLD24: GSM "SHARDDIR_DG" is not connected to any GSM from GDS region "canada"
    VLD24: GSM "SHARDDIR_DG" is not connected to any GSM from GDS region "india" 

     Total errors: 12.
    GDSCTL>config shard
    Name                Shard Group         Status    State       Region    Availability
    -----                -----------         ------    -----       ------    ------------
    sh1                 primary_canada_shg  Ok        DDL error   canada    ONLINE
    sh2                 primary_canada_shg  Ok        DDL error   canada    ONLINE
    sh3                 standby_india_shg   Ok        Deployed    india     READ ONLY
    sh4                 standby_india_shg   Ok        Deployed    india     READ ONLY 

    GDSCTL>

     

    DDL Tracking from GSM

    Tracking all the DDL changes of the shards is easy using GSM.  Effectively, all operations it performs are audited across the shards.  Hence we can review the DDL command issued, and at the same time can also check if there were any DDL errors (failures). 

    GDSCTL>show ddl

    id      DDL Text                                 Failed shards
    --      --------                                 -------------
    39      CREATE TABLESPACE SET PSFINDATA using...
    40      CREATE TABLESPACE PSFINDUP datafile s...
    41      CREATE SHARDED TABLE student_info ( s...
    42      CREATE SHARDED TABLE exam_info ( Hall...
    43      CREATE SHARDED TABLE schooldata ( Hal...
    44      CREATE MATERIALIZED VIEW "SYSADM"."ST...
    45      CREATE OR REPLACE FUNCTION PasswCreat...
    46      CREATE OR REPLACE FUNCTION PasswCheck...
    47      create tablespace test
    48      purge recyclebin 

    GDSCTL>

    Summary

    In this last Sharding article we’ve seen how we can monitor and check the Sharding configuration.  We also  included a few other optional steps to consider such as disabling FSFO if it is not a business requirement. We performed actual testing of sharding by creating sharded tables and confirmed that the chunks were sharded properly across the shards.

    Finally, we concluded with another critical step: troubleshooting the GSM configuration and learning about the various logs we may have to through when investigating issues.

     


    Nassyam Basha is a Database Administrator. He has around 10 years of experience as a Production Oracle DBA, currently working as Senior Principal Consultant at Data Intensity. He holds a master's degree in Computer Applications from the University of Madras. He is an Oracle 11g Certified master an Oracle ACE Director. He actively participates in Oracle-related forums such as OTN with a status of Super Hero, Oracle support awarded as "Guru" and acting as OTN Moderator and written numerous articles with OTN and on Toad World. He maintains an Oracle technology-related blog, www.oracle-ckpt.com and can be reached at nassyambasha@gmail.com.

    Released: April 16, 2018, 7:08 am | Updated: April 30, 2018, 8:42 am
    Keywords: Feature | Nassyam Basha | shard


    Copyright © 2018 Communication Center. All Rights Reserved
    All material, files, logos and trademarks within this site are properties of their respective organizations.
    Terms of Service - Privacy Policy - Contact

    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    IOUG Logo

    Copyright © 1993-2018 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy