Oracle 12 c Sharding Part 2 Installation Configuration

Oracle 12 c Sharding Part 2 – Installation & Configuration

Oracle Sharding Architecture (12 c R 2) shard 1 db 1 Customer_orders Partition 1 Dataguard Broder 1521 SDB Global Service Primary Shard Director Standby 1521 Select custid, customernu mner, product, qty from customer_orders where cust_id=11030; Takes the hash key of this customer id Connection Pool Shared catalog shard 2 Search Distributed Metadata The requested data is distributed to shard 1 – db 1 shard 2 db 2 Customer_orders Partition 2 Dataguard Broder 1521 Primary Standby High Availability

Configuring Oracle Sharding – Environment Planning Node Type IP Address Home Ports DB Name Scheduler Agent Shard Catalog Node 192. 168. 56. 150 Host: shardcat Oracle Home: /u 01/app/oracle/12. 2. 0. 1/db_1 GSM home: /u 01/app/oracle/12. 2. 0. 1/GSM Listener 1539 DB Listener 1521 SCAT Shard 1 192. 168. 56. 151 Host: shard 1 Oracle Home: /u 01/app/oracle/12. 2. 0. 1/db_1 DB Listener 1521 sh 1 Scheduler Agent - Shard 2 192. 168. 56. 152 Host: shard 2 Oracle Home: /u 01/app/oracle/12. 2. 0. 1/db_1 DB Listener 1521 sh 2 Scheduler Agent -

Steps to Configure Oracle Sharding Step Nodes/Hosts Step Details 1 shardcat/Shard 1/Shard 2 Install Oracle Software Only 12 c. R 2 – Preferably Same Location 2 shardcat Create Database SCAT using DBCA (NON CDB) 3 shardcat Install GSM Software as Separate Home 4 shardcat Create Environment Setup 5 Shardcat Prepare SCAT database for Sharding - Prerequisities 6 shardcat Configure Shard Catalog using GSDCTL – Create Shard Catalog in SCAT 7 shard 1/shard 2 Start the SCH Agent & Register Shard nodes to SCAT database 8 shardcat Create Shard Group/Director/Add Shards 9 shardcat Deploy Shards using GSDCTL – This will eventually create databases in shard 1/shard 2 10 shardcat Verify Shard Configuration 11 shardcat Create Global Service using GSDCTL 12 shardcat Create sample schema and Tablespace set and see that propagate to shard 1/shard 2 13 shardcat Connect to sample schema and create shard table 14 Shardcat/shard 1/shard 2 Verify the sample table is created across shards

Step 1 : Install Oracle 12 c R 2 Software • Install Oracle 12 c. R 2 Software on all three nodes shardcat/shard 1/shard 2 • Complete Pre-requisities • Install with Software only option • Sample options to change in <softwarelocation>/response/db_install. rsp oracle. install. option=INSTALL_DB_SWONLY ORACLE_HOSTNAME=Geek DBA 11 g UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u 02/app/ora. Inventory ORACLE_HOME=/u 01/app/oracle/product/12. 1. 0/db_1 ORACLE_BASE=/u 01/app/oracle. install. db. Install. Edition=EE oracle. install. db. DBA_GROUP=dba oracle. install. db. OPER_GROUP=oper oracle. install. db. BACKUPDBA_GROUP=dba oracle. install. db. DGDBA_GROUP=asmadmin oracle. install. db. KMDBA_GROUP=dba SECURITY_UPDATES_VIA_MYORACLESUPPORT=false DECLINE_SECURITY_UPDATES=true • Create Oracle Home Directories mkdir -p /u 01/app/oracle/product/12. 1. 0/db_1 mkdir -p /u 02/app/ora. Inventory chown -R oracle: oinstall /u 02/app/ora. Inventory • Sample Run Installer Command. /run. Installer -silent -ignoresysprereqs -responsefile /home/oracle/database/response/db_install. rsp

Step 2: Create Shard. Catalog Database On shardcat Node: - • Create Directories mkdir –p /u 01/app/oracle/flash_recover_area/SCAT mkdir –p /u 01/app/oracle/fast_recovery_area/SCAT mkdir –p /u 01/oradata/SCAT mkdir –p /u 01/app/oracle/admin/SCAT/adump • Run DBCA . /dbca -silent -create. Database -template. Name General_Purpose. dbc -gdb. Name SCAT -sid SCAT -Sys. Password **** -create. As. Container. Database false -System. Password *** -em. Configuration NONE -redo. Log. File. Size 100 -recovery. Area. Destination /u 01/app/oracle/SCAT -Datafile. Destination /u 01/app/oracle/SCAT -storage. Type FS -listeners LISTENER 12 -register. With. Dir. Service false -character. Set AL 32 UTF 8 -national. Character. Set AL 16 UTF 16 -database. Type MULTIPURPOSE -memory. Percentage 40 -memory. Type AUTO

Step 3: Install GSM Software in Shardcat On Shardcat Node: • Download from Oracle Downloads page or Edelivery. oracle. com • Unzip the GSM Software • Install as separate Home

Step 4: Create Environment Setup in Shardcat

Step 5 : Prepare SCAT database for Sharding - Prerequisities On Shardcat : SCAT Database alter system set db_create_file_dest='/u 01/ora 12 c/app/oracle/oradata' scope=both; alter system set open_links=16 scope=spfile; alter system set open_links_per_instance=16 scope=spfile; startup force alter user gsmcatuser account unlock; alter user gsmcatuser identified by oracle; CREATE USER mygdsadmin IDENTIFIED BY oracle; GRANT connect, create session, gsmadmin_role to mygdsadmin; grant inherit privileges on user SYS to GSMADMIN_INTERNAL; execute dbms_xdb. sethttpport(8080); commit; @? /rdbms/admin/prvtrsch. plb exec DBMS_SCHEDULER. SET_AGENT_REGISTRATION_PASS('oracleagent');

Step 6: – Create Shard Catalog in SCAT On Shardcat node, in SCAT Database & Set environment to GSM Home # gdsctl GDSCTL> create shardcatalog -database shardcat: 1521: SCAT -chunks 12 -user mygdsadmin/oracle -sdb SCAT -region 1 GDSCTL>add gsm -gsm sharddirector 1 -listener 1571 -pwd oracle -catalog shardcat: 1521: SCAT -region 1 GDSCTL>start gsm -gsm sharddirector 1 GDSCTL>add credential -credential oracle_cred -osaccount oracle -ospassword **** GDSCTL> exit

Step 7: Start the Scheduler Agent on Shard Nodes On Shard 1 & 2 : Scheduler Agent is already installed if you install oracle database software just start it #Set Oracle Home #schagent start #schagent status # echo oracleagent | schagent –resiterdatabase shardcat 8080 provide name of host not database

Step 8 : Create Shard Group/Director/Add Shards GDSCTL>set gsm -gsm sharddirector 1 GDSCTL>connect mygdsadmin/oracle Catalog connection is established GDSCTL>-- add shard group GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region 1 The operation completed successfully GDSCTL>-- add shard 1 GDSCTL>add invitednode shard 1 GDSCTL>create shard -shardgroup primary_shardgroup -destination shard 1 -credential oracle_cred DB Unique Name: sh 1 GDSCTL> --add shard 2 GDSCTL>add invitednode shard 2 GDSCTL>create shard -shardgroup primary_shardgroup -destination shard 2 -credential oracle_cred DB Unique Name: sh 2

Step 8 : Deploy Shards • In shardcat node, using gsdctl, run gsdctl> deploy Note: This will create the databases in shard 1 and shard 2 using dbca and create listeners automatically

Step 10: Verify Shard Status

Step 11 : Create Global Service using GSDCTL Using GDSCTL on shardcat gdsctl> add service –service test_srv –role primar gdsctl> config service gdsctl> start service gdsctl> status service

Step 12: Create sample schema and Tablespace set and see that propagate to shard 1/shard 2 #[oracle 12 c@sdb 1 ~]$ db_env Sqlplus / as sysdba alter session enable shard ddl; create user app_schema identified by oracle; grant all privileges to app_schema; grant gsmadmin_role to app_schema; grant select_catalog_role to app_schema; grant connect, resource to app_schema; grant dba to app_schema; grant execute on dbms_crypto to app_schema; #Create Sharded Tablespace & Test out conn app_schema/oracle alter session enable shard ddl; CREATE TABLESPACE SET TSP_SET_1 using template (datafile size 100 m extent management local segment space management auto ); CREATE TABLESPACE products_tsp datafile size 100 m extent management local uniform size 1 m; Note: For any command that need to be shard, you must enable shard ddl at session level before running the sql command

Step 13: Create Shard Tables

Step 14: Verify Distribution of Tables to shards • On Shard Catalog SDB Database

Step 14: Verify Distribution of Tables to Shards On Shard 1, SH 1 database the customer table is partitioned and some of the partitions created here On Shard 2, SH 2 database the customer table is partitioned and some of the partitions created here

Thanks • In Next Post/Presentation we will be seeing • • Managing Shards Adding / Deleting Shards Verify Data Distribution Shard Restrictions