By Nassyam Basha, Data Intensity | Edited by Simon Pane
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.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.
About the Author
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.