溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要為大家展示了“Oracle sharding database的示例分析”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“Oracle sharding database的示例分析”這篇文章吧。
Sharding架構是數據庫層面的一種分片技術，可以使分過區的數據分布在各不相同的獨立數據庫里。Sharding是Oracle Database 12c Release 2的新特性，它能為適合于 Sharding技術的OLTP應用提供線性擴展和完全錯誤隔離的能力，q 我們可以將 Sharding簡單地理解為Oracle 表分區技術的擴展，下面將詳細描述安裝操作。
1.Oracle Sharding 安裝條件
12.2企業版
non-cdb
使用文件系統而非ASM （12.2 Beta要求，正式發行后，可能會改）
主機hosts文件寫上本機和各個shard node的IP解析
機器必須全新，不能殘留之前有安裝過oracle的信息。
2.設置環境變量，創建相關目錄與設置內核參數
shardcat和gds都安裝在一個主機上，同一個oracle用戶，不同ORACLE_HOME。
[oracle12c@sdb1 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH # Oracle Settings TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/ora12c/app/oracle; export ORACLE_BASE DB_HOME=$ORACLE_BASE/product/12.2.0/db_1; export DB_HOME GSM_HOME=$ORACLE_BASE/product/12.2.0/gsm; export GSM_HOME ORACLE_HOME=$DB_HOME; export ORACLE_HOME ORACLE_SID=shardcat; export ORACLE_SID ORACLE_TERM=xterm; export ORACLE_TERM BASE_PATH=/usr/sbin:$PATH; export BASE_PATH PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH #LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL
[root@shard1 ~]# groupadd -g 1009 dba [root@shard1 ~]# groupadd -g 1010 oper [root@shard1 ~]# groupadd -g 1011 oinstall [root@shard1 ~]# useradd -u 1001 -g oinstall -G dba,oper oracle [root@shard1 ~]# passwd oracle Changing password for user oracle. New password: BAD PASSWORD: The password is shorter than 8 characters Retype new password: passwd: all authentication tokens updated successfully. [root@shard1 ~]# mkdir -p /u01/app/oraInventory [root@shard1 ~]# chown -R oracle:oinstall /u01/app/oraInventory [root@shard1 ~]# chmod -R 775 /u01/app/oraInventory [root@shard1 ~]# mkdir -p /u01/app/oracle [root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle [root@shard1 ~]# chmod -R 775 /u01/app/oracle [root@shard1 ~]# mkdir -p /u01/app/oracle/product/12.2.0/db [root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/db [root@shard1 ~]# chmod -R 775 /u01/app/oracle/product/12.2.0/db [root@shard1 ~]# mkdir /u01/tmp [root@shard1 ~]# chmod a+wr /u01/tmp [root@shard1 ~]# mkdir -p /u01/app/oracle/product/12.2.0/gsm [root@shard1 ~]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/gsm [root@shard1 ~]# chmod -R 775 /u01/app/oracle/product/12.2.0/gsm [root@shard1 ~]# su - oracle
[oracle@shard1 ~]$ vi .bash_profile export PATH # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH TEMP=/u01/tmp TMPDIR=/u01/tmp export TEMP TMPDIR export LD_ASSUME_KERNEL=3.8.13 export ORACLE_BASE=/u01/app/oracle DB_HOME=/u01/app/oracle/product/12.2.0/db GSM_HOME=/u01/app/oracle/product/12.2.0/gsm export ORACLE_HOME=$DB_HOME export ORACLE_SID=jytest2 export ORACLE_UNQNAME=jytest export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export LD_LIBRARY_PATH export PATH=$PATH:$ORACLE_HOME/bin CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib export CLASSPATH umask=022 [oracle@shard1 ~]$ alias gsm_env='. /home/oracle/gsm_env' [oracle@shard1 ~]$ alias db_env='. /home/oracle/db_env' [oracle@shard1 ~]$ vi gsm_env ORACLE_HOME=$GSM_HOME; export ORACLE_HOME PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH [oracle@shard1 ~]$ vi db_env ORACLE_HOME=$DB_HOME; export ORACLE_HOME PATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH
修改內核參數編輯/etc/sysctl.conf文件
[root@shard1 ~]# vi /etc/sysctl.conf # System default settings live in /usr/lib/sysctl.d/00-system.conf. # To override those settings, enter new settings here, or in an /etc/sysctl.d/.conf file # # For more information, see sysctl.conf(5) and sysctl.d(5). fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 4294967295 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 [root@shard1 ~]# sysctl -p fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 4294967295 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576
修改oarcle參數的shell限制,在所有節點的/etc/security/limits.conf文件中添加以下參數
oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 oracle soft stack 10240 oracle hard stack 32768 oracle soft memlock 3145728 oracle hard memlock 3145728
修改shell的默認參數文件，在所有節點的/etc/profile文件中添加以下內容：
if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 16384 -n 65536 fi fi
3.安裝Oracle軟件
[root@shard1 soft]# unzip linuxx64_12201_database.zip [root@shard1 soft]# chown -R oracle:oinstall database [root@shard1 Desktop]# xhost + access control disabled, clients can connect from any host [root@shard1 Desktop]# su - oracle Last login: Thu Oct 12 12:01:58 CST 2017 on pts/1 [oracle@shard1 ~]$ export DISPLAY=:1 [oracle@shard1 ~]$ cd /soft/database [oracle@shard1 database]$ ls -lrt total 24 -rwxr-xr-x. 1 oracle oinstall 500 Feb 7 2013 welcome.html drwxr-xr-x. 4 oracle oinstall 4096 Jan 26 2017 install -rwxr-xr-x. 1 oracle oinstall 8771 Jan 26 2017 runInstaller drwxr-xr-x. 2 oracle oinstall 34 Jan 26 2017 rpm drwxrwxr-x. 2 oracle oinstall 28 Jan 26 2017 sshsetup drwxrwxr-x. 2 oracle oinstall 58 Jan 26 2017 response drwxr-xr-x. 14 oracle oinstall 4096 Jan 26 2017 stage [oracle@shard1 database]$ ./runInstaller
[root@shard1 soft]# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to oinstall. The execution of the script is complete. [root@shard1 soft]# /u01/app/oracle/product/12.2.0/db/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/12.2.0/db Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : yes Installing Oracle Trace File Analyzer (TFA). Log File: /u01/app/oracle/product/12.2.0/db/install/root_shard1_2017-10-12_13-06-02-537061115.log Finished installing Oracle Trace File Analyzer (TFA)
其它兩個shard主機shard2,shard3安裝Oracle軟件的操作不再描述同上。
shard2上的oracle環境變量設置如下,其中ORACLE_SID與
ORACLE_UNQNAME設置為sh2，在后面向shard catalog數據庫注冊shard時會讀取到
[oracle@shard2 ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH TEMP=/u01/tmp TMPDIR=/u01/tmp export TEMP TMPDIR export LD_ASSUME_KERNEL=3.8.13 export ORACLE_BASE=/u01/app/oracle DB_HOME=/u01/app/oracle/product/12.2.0/db GSM_HOME=/u01/app/oracle/product/12.2.0/gsm export ORACLE_HOME=$DB_HOME export ORACLE_SID=sh2 export ORACLE_UNQNAME=sh2 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export LD_LIBRARY_PATH export PATH=$PATH:$ORACLE_HOME/bin CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib export CLASSPATH umask=022
shard3上的oracle環境變量設置如下,其中ORACLE_SID與
ORACLE_UNQNAME設置為sh2，在后面向shard catalog數據庫注冊shard時會讀取到
[oracle@shard3 ~]$ vi .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PATH=$PATH:$HOME/.local/bin:$HOME/bin export PATH TEMP=/u01/tmp TMPDIR=/u01/tmp export TEMP TMPDIR export LD_ASSUME_KERNEL=3.8.13 export ORACLE_BASE=/u01/app/oracle DB_HOME=/u01/app/oracle/product/12.2.0/db GSM_HOME=/u01/app/oracle/product/12.2.0/gsm export ORACLE_HOME=$DB_HOME export ORACLE_SID=sh3 export ORACLE_UNQNAME=sh3 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib export LD_LIBRARY_PATH export PATH=$PATH:$ORACLE_HOME/bin CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib export CLASSPATH umask=022
4.安裝Shard Director Software
[root@shard1 soft]# unzip linuxx64_12201_gsm.zip [root@shard1 Desktop]# xhost + access control disabled, clients can connect from any host [root@shard1 Desktop]# su - oracle Last login: Thu Oct 12 18:05:56 CST 2017 on pts/0 [oracle@shard1 ~]$ export DISPLAY=:1 [oracle@shard1 ~]$ cd /soft/gsm [oracle@shard1 gsm]$ ls -lrt total 24 -rwxrwxr-x. 1 oracle oinstall 500 Feb 7 2013 welcome.html -rwxr-xr-x. 1 oracle oinstall 8772 Jan 26 2017 runInstaller drwxr-xr-x. 4 oracle oinstall 4096 Jan 26 2017 install drwxrwxr-x. 2 oracle oinstall 28 Jan 26 2017 response drwxr-xr-x. 14 oracle oinstall 4096 Jan 26 2017 stage [oracle@shard1 gsm]$ ./runInstaller
[root@shard1 soft]# /u01/app/oracle/product/12.2.0/gsm/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /u01/app/oracle/product/12.2.0/gsm Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The contents of "oraenv" have not changed. No need to overwrite. The contents of "coraenv" have not changed. No need to overwrite. Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed.
5.創建shard catalog數據庫
運行dbca開始建立數據庫實例，這個實例是放分片數據的元數據的。我們把這個實例名叫shardcat。
[root@shard1 Desktop]# xhost + access control disabled, clients can connect from any host [root@shard1 Desktop]# su - oracle Last login: Thu Oct 12 18:35:35 CST 2017 on pts/1 [oracle@shard1 ~]$ export DISPLAY=:1 [oracle@shard1 ~]$ dbca
[oracle@shard1 arch]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-OCT-2017 18:58:06 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shard1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 12-OCT-2017 18:26:22 Uptime 0 days 0 hr. 31 min. 43 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/shard1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "shardcat" has 1 instance(s). Instance "shardcat", status READY, has 1 handler(s) for this service... Service "shardcatXDB" has 1 instance(s). Instance "shardcat", status READY, has 1 handler(s) for this service... The command completed successfully
6.設置Oracle Sharding Management and Routing Tier
登錄shardcat主機，登錄shardcat數據庫：--建立tablespace set需要使用omf，所以需要指定db_create_file_dest參數。
[oracle@shard1 ~]$ echo $ORACLE_SID
shardcat
[oracle@shard1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 12 19:01:30 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 system set db_create_file_dest='/u01/app/oracle/oradata' scope=both;
System altered.
SQL> alter system set open_links=16 scope=spfile;
System altered.
SQL> alter system set open_links_per_instance=16 scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 620760392 bytes
Database Buffers 1509949440 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> alter user gsmcatuser account unlock;
User altered.
SQL> alter user gsmcatuser identified by oracle;
User altered.
SQL> create user mygdsadmin identified by oracle;
User created.
SQL> grant connect, create session, gsmadmin_role to mygdsadmin;
Grant succeeded.
SQL> grant inherit privileges on user SYS to GSMADMIN_INTERNAL;
Grant succeeded.
（update 2016-11-10 注：在12.2. beta 2后可以不做這步）
SQL> alter system set events 'immediate trace name GWM_TRACE level 7';
System altered.
（update 2016-11-10 注：在12.2. beta 2后可以不做這步）
SQL> alter system set event='10798 trace name context forever, level 7' scope=spfile;
System altered.
SQL> execute dbms_xdb.sethttpport(8080);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> @?/rdbms/admin/prvtrsch.plb
PL/SQL procedure successfully completed.
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Session altered.
Session altered.
Package created.
No errors.
Grant succeeded.
Session altered.
Session altered.
Package body created.
No errors.
Session altered.
Session altered.
Procedure created.
No errors.
Function created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Procedure created.
No errors.
Audit policy altered.
1 row updated.
Commit complete.
Session altered.
PL/SQL procedure successfully completed.
SQL> exec DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('oracle');
PL/SQL procedure successfully completed.
SQL> startup force
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 620760392 bytes
Database Buffers 1509949440 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.在所有的shard節點分別執行Agent注冊
登錄shard2主機：
[oracle@shard2 ~]$ schagent -start Scheduler agent started using port 65121 [oracle@shard2 ~]$ schagent status Agent running with PID 12078 Agent_version:12.2.0.1.2 Running_time:00:00:10 Total_jobs_run:0 Running_jobs:0 Platform:Linux ORACLE_HOME:/u01/app/oracle/product/12.2.0/db ORACLE_BASE:/u01/app/oracle Port:65121 Host:shard2 [oracle@shard2 ~]$ echo oracle|schagent -registerdatabase shard1 8080 Agent Registration Password ? Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent Agent Registration Successful! [oracle@shard2 ~]$ mkdir -p /u01/app/oracle/oradata [oracle@shard2 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area
登錄shard3主機：
[oracle@shard3 ~]$ source .bash_profile [oracle@shard3 ~]$ schagent -start Scheduler agent started using port 35374 [oracle@shard3 ~]$ schagent -status Agent running with PID 13019 Agent_version:12.2.0.1.2 Running_time:00:00:07 Total_jobs_run:0 Running_jobs:0 Platform:Linux ORACLE_HOME:/u01/app/oracle/product/12.2.0/db ORACLE_BASE:/u01/app/oracle Port:35374 Host:shard3 [oracle@shard3 ~]$ echo oracle|schagent -registerdatabase shard1 8080 Agent Registration Password ? Oracle Scheduler Agent Registration for 12.2.0.1.2 Agent Agent Registration Successful! [oracle@shard3 ~]$ mkdir -p /u01/app/oracle/oradata [oracle@shard3 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area
7.Deploying and Managing a System-Managed SDB
我們開始部署，以最簡單的System-Managed SDB為例。
另外，admin guide中介紹的是4臺主機做shard node，其中每2臺互為dataguard主備。我們這邊為了節約空間和資源，不搞dataguard了，只建立primary庫。因此只要2臺主機做shard node。先設置gsm的環境變量
進入到GDSCTL命令行，創建shard catalog。
[oracle@shard1 ~]$ export ORACLE_BASE=/u01/app/oracle [oracle@shard1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsm [oracle@shard1 ~]$ export PATH=/u01/app/oracle/product/12.2.0/gsm/bin:$PATH:$HOME/bin [oracle@shard1 ~]$ gdsctl GDSCTL: Version 12.2.0.1.0 - Production on Thu Oct 12 19:24:36 CST 2017 Copyright (c) 2011, 2016, Oracle. All rights reserved. Welcome to GDSCTL, type "help" for information. Warning: current GSM name is not set automatically because gsm.ora contains zero or several GSM entries. Use "set gsm" command to set GSM for the session. Current GSM is set to GSMORA GDSCTL> GDSCTL>create shardcatalog -database shard1:1521:shardcat -chunks 12 -user mygdsadmin/oracle -sdb shardcat -region region1 -agent_port 8080 -agent_password oracle Catalog is created 創建和啟動shard director. 參數含義： -gsm: 指定shard director名稱 -listener: 指定shard director的監聽端口，注意不能與數據庫的listener端口沖突 -catalog: 指定catalog database 信息，catalog數據庫的主機名:監聽器port: catalog 數據庫db_name GDSCTL>add gsm -gsm sharddirector1 -listener 1571 -pwd oracle -catalog shard1:1521:shardcat -region region1 GSM successfully added GDSCTL>start gsm -gsm sharddirector1 GSM is started successfully 添加操作系統認證. GDSCTL>add credential -credential oracle_cred -osaccount oracle -ospassword oracle The operation completed successfully
開始布署SharedDatabase。本例將布署System-ManagedSDB。
部署system-managed SDB
1.連接到shard director/GSM服務器(shard1)
[oracle@shard1 ~]$ export ORACLE_BASE=/u01/app/oracle [oracle@shard1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/gsm [oracle@shard1 ~]$ export PATH=/u01/app/oracle/product/12.2.0/gsm/bin:$PATH:$HOME/bin [oracle@shard1 ~]$ gdsctl GDSCTL: Version 12.2.0.1.0 - Production on Thu Oct 12 19:35:21 CST 2017 Copyright (c) 2011, 2016, Oracle. All rights reserved. Welcome to GDSCTL, type "help" for information. Current GSM is set to SHARDDIRECTOR1
設置當前session為sharddirector1 shard director
GDSCTL>set gsm -gsm sharddirector1 GDSCTL>connect mygdsadmin/oracle Catalog connection is established
添加shardgroup， shardgroup是一組shard的集合，shardgroup名稱為primary_shardgroup，-deploy_as primary表示這個group中的shard都是主庫。
GDSCTL>add shardgroup -shardgroup primary_shardgroup -deploy_as primary -region region1 The operation completed successfully 將每個shard地址添加到catalog的valid node checking for registration (VNCR)列表，并且創建shard GDSCTL>add invitednode shard2 GDSCTL>create shard -shardgroup primary_shardgroup -destination shard2 -credential oracle_cred The operation completed successfully DB Unique Name: sh2 GDSCTL>add invitednode shard3 GDSCTL>create shard -shardgroup primary_shardgroup -destination shard3 -credential oracle_cred The operation completed successfully DB Unique Name: sh3
檢查配置
GDSCTL>config Regions ------------------------ region1 GSMs ------------------------ sharddirector1 Sharded Database ------------------------ shardcat Databases ------------------------ sh2 sh3 Shard Groups ------------------------ primary_shardgroup Shard spaces ------------------------ shardspaceora Services ------------------------ GDSCTL pending requests ------------------------ Command Object Status ------- ------ ------ Global properties ------------------------ Name: oradbcloud Master GSM: sharddirector1 DDL sequence #: 0 GDSCTL>config shardspace Shard space Chunks ----------- ------ shardspaceora 12 GDSCTL>config shardgroup Shard Group Chunks Region Shard space ----------- ------ ------ ----------- primary_shardgroup 12 region1 shardspaceora GDSCTL>config vncr Name Group ID ---- -------- shard2 shard3 10.138.130.180 GDSCTL>config shard Name Shard Group Status State Region Availability ---- ----------- ------ ----- ------ ------------ sh2 primary_shardgroup U none region1 - sh3 primary_shardgroup U none region1 -
部署deploy
Shard數據庫部署過程采用靜默安裝方式。
GDSCTL>deploy
此時，就開始部署shard了。在shard node上的agent會自動的調用netca和dbca，創建listener和database，2個shard node的操作是并行進行的。（如果是有datauard，那么是先建立一對主備，再建立另一對主備。）你可以在分別是兩個shard node上ps -ef|grep ora_ 看到已經有sh2和sh3的實例了。我們可以執行在shard2與shard3上執行ps -ef | grep dbca與lsnrctl status,ps -ef | grep pmon來檢查。
[root@shard2 ~]# ps -ef | grep dbca oracle 20437 20429 99 19:49 pts/0 00:00:19 /u01/app/oracle/product/12.2.0/db/jdk/jre/bin/java -Doracle.installer.not_bootstrap=true -DCV_HOME=/u01/app/oracle/product/12.2.0/db -DORACLE_HOME=/u01/app/oracle/product/12.2.0/db -XX:-OmitStackTraceInFastThrow -XX:CompileCommand=quiet -XX:CompileCommand=exclude,javax/swing/text/GlyphView,getBreakSpot -DSET_LAF= -Dsun.java2d.font.DisableAlgorithmicStyles=true -Dice.pilots.html4.ignoreNonGenericFonts=true -DDISPLAY= -DJDBC_PROTOCOL=thin -mx512m -classpath /u01/app/oracle/product/12.2.0/db/assistants/dbca/jlib/dbca.jar:/u01/app/oracle/product/12.2.0/db/assistants/dbca/jlib/dbcaext.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/assistantsCommon.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/rconfig.jar:/u01/app/oracle/product/12.2.0/db/assistants/jlib/asstcommonext.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraInstaller.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraPrereq.jar:/u01/app/oracle/product/12.2.0/db/inventory/prereqs/oui/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraPrereqChecks.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/OraCheckPoint.jar:/u01/app/oracle/product/12.2.0/db/jlib/cvu.jar:/u01/app/oracle/product/12.2.0/db/install/jlib/installcommons_1.0.0b.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/ssh.jar:/u01/app/oracle/product/12.2.0/db/jlib/ewt3.jar:/u01/app/oracle/product/12.2.0/db/jlib/ewtcompat-3_3_15.jar:/u01/app/oracle/product/12.2.0/db/jlib/share.jar:/u01/app/oracle/product/12.2.0/db/jlib/help4.jar:/u01/app/oracle/product/12.2.0/db/oui/jlib/jewt4.jar:/u01/app/oracle/product/12.2.0/db/jlib/oracle_ice.jar:/u01/app/oracle/product/12.2.0/db/jlib/kodiak.jar:/u01/app/oracle/product/12.2.0/db/lib/xmlparserv2.jar:/u01/app/oracle/product/12.2.0/db/jlib/orai18n.jar:/u01/app/oracle/product/12.2.0/db/jlib/ldapjclnt12.jar:/u01/app/oracle/product/12.2.0/db/jlib/netcfg.jar:/u01/app/oracle/product/12.2.0/db/jlib/ojmisc.jar:/u01/app/oracle/product/12.2.0/db/jlib/oraclepki.jar:/u01/app/oracle/product/12.2.0/db/jlib/opm.jar:/u01/app/oracle/product/12.2.0/db/jdbc/lib/ojdbc8.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvm.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvmhas.jar:/u01/app/oracle/product/12.2.0/db/jlib/srvmasm.jar:/u01/app/oracle/product/12.2.0/db/dv/jlib/dvca.jar:/u01/app/oracle/product/12.2.0/db/jlib/gns.jar:/u01/app/oracle/product/12.2.0/db/jlib/commons-compress-1.8.jar oracle.assistants.dbca.driver.DBConfigurator -silent -responseFile /u01/app/oracle/product/12.2.0/db/shard_sh2_dbca.rsp -createDatabase -gdbName sh2 -sid sh2 -initparams db_unique_name=sh2,db_name=sh2,db_domain= -templateName /u01/app/oracle/product/12.2.0/db/shard_sh2_template.dbt -customscripts /u01/app/oracle/product/12.2.0/db/shard_sh2_postCR.sql -listeners LISTENER_sh2 root 20881 12186 0 19:49 pts/1 00:00:00 grep --color=auto dbca [oracle@shard2 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 12-OCT-2017 19:56:01 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER_sh2 Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 12-OCT-2017 19:48:45 Uptime 0 days 0 hr. 7 min. 18 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0/db/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/shard2/listener_sh2/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shard2)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "sh2" has 1 instance(s). Instance "sh2", status RESTRICTED, has 1 handler(s) for this service... Service "sh2_DGMGRL" has 1 instance(s). Instance "sh2", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@shard2 ~]$ ps -ef | grep pmon oracle 22086 1 0 19:54 ? 00:00:00 ora_pmon_sh2 oracle 22982 5293 0 19:56 pts/0 00:00:00 grep --color=auto pmon
GDSCTL>deploy deploy: examining configuration... deploy: deploying primary shard 'sh2' ... deploy: network listener configuration successful at destination 'shard2' deploy: starting DBCA at destination 'shard2' to create primary shard 'sh2' ... deploy: deploying primary shard 'sh3' ... deploy: network listener configuration successful at destination 'shard3' deploy: starting DBCA at destination 'shard3' to create primary shard 'sh3' ... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: waiting for 2 DBCA primary creation job(s) to complete... deploy: DBCA primary creation job succeeded at destination 'shard2' for shard 'sh2' deploy: waiting for 1 DBCA primary creation job(s) to complete... deploy: DBCA primary creation job succeeded at destination 'shard3' for shard 'sh3' deploy: requesting Data Guard configuration on shards via GSM deploy: shards configured successfully The operation completed successfully
我們可以檢查一下shard的情況了：
GDSCTL>config shard Name Shard Group Status State Region Availability ---- ----------- ------ ----- ------ ------------ sh2 primary_shardgroup Ok Deployed region1 ONLINE sh3 primary_shardgroup Ok Deployed region1 ONLINE GDSCTL>databases Database: "sh2" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1 Registered instances: shardcat%1 Database: "sh3" Registered: Y State: Ok ONS: N. Role: PRIMARY Instances: 1 Region: region1 Registered instances: shardcat%11 GDSCTL>config shard -shard sh2 Name: sh2 Shard Group: primary_shardgroup Status: Ok State: Deployed Region: region1 Connection string: shard2:1521/sh2:dedicated SCAN address: ONS remote port: 0 Disk Threshold, ms: 20 CPU Threshold, %: 75 Version: 12.2.0.0 Failed DDL: DDL Error: --- Failed DDL id: Availability: ONLINE Rack: Supported services ------------------------ Name Preferred Status ---- --------- ------ GDSCTL>config shard -shard sh3 Name: sh3 Shard Group: primary_shardgroup Status: Ok State: Deployed Region: region1 Connection string: shard3:1521/sh3:dedicated SCAN address: ONS remote port: 0 Disk Threshold, ms: 20 CPU Threshold, %: 75 Version: 12.2.0.0 Failed DDL: DDL Error: --- Failed DDL id: Availability: ONLINE Rack: Supported services ------------------------ Name Preferred Status ---- --------- ------
創建service
GDSCTL>add service -service oltp_rw_srvc -role primary The operation completed successfully GDSCTL>start service -service oltp_rw_srvc The operation completed successfully GDSCTL>status service Service "oltp_rw_srvc.shardcat.oradbcloud" has 2 instance(s). Affinity: ANYWHERE Instance "shardcat%1", name: "sh2", db: "sh2", region: "region1", status: ready. Instance "shardcat%11", name: "sh3", db: "sh3", region: "region1", status: ready.
(其實這個service，用于adg的主備切換后，這個service漂移到備庫上)
創建用戶和對象
1. 在catalog數據庫中創建業務用戶
[oracle@shard2 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 13:24:45 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 app_schema identified by oracle; User created. SQL> grant all privileges to app_schema; Grant succeeded. SQL> grant gsmadmin_role to app_schema; Grant succeeded. SQL> grant select_catalog_role to app_schema; Grant succeeded. SQL> grant connect, resource to app_schema; Grant succeeded. SQL> grant dba to app_schema; Grant succeeded. SQL> grant execute on dbms_crypto to app_schema; Grant succeeded.
2. 利用應用用戶登錄,創建表空間集合
SQL> conn app_schema/oracle Connected. SQL> alter session enable shard ddl; Session altered. SQL> create tablespace set tsp_set_1 using template (datafile size 100m autoextend on next 10m maxsize unlimited extent management local segment space management auto); Tablespace created.
3. 為duplicated tables創建表空間，這個測試中duplicated table是Products table.
SQL> create tablespace products_tsp datafile size 100m autoextend on next 10m maxsize unlimited extent management local uniform size 1m; Tablespace created.
4. 創建shard表
SQL> create sharded table customers 2 ( 3 custid varchar2(60) not null, 4 firstname varchar2(60), 5 lastname varchar2(60), 6 class varchar2(10), 7 geo varchar2(8), 8 custprofile varchar2(4000), 9 passwd raw(60), 10 constraint pk_customers primary key (custid), 11 constraint json_customers check (custprofile is json) 12 ) tablespace set tsp_set_1 13 partition by consistent hash (custid) partitions auto; Table created. SQL> create sharded table orders 2 ( 3 orderid integer not null, 4 custid varchar2(60) not null, 5 orderdate timestamp not null, 6 sumtotal number(19,4), 7 status char(4), 8 constraint pk_orders primary key (custid, orderid), 9 constraint fk_orders_parent foreign key (custid) 10 references customers on delete cascade 11 ) partition by reference (fk_orders_parent); Table created.
5.為orders表的orderid列創建序列
SQL> create sequence orders_seq; Sequence created.
6. 創建SHARDED TABLE LineItems
SQL> create sharded table lineitems 2 ( 3 orderid integer not null, 4 custid varchar2(60) not null, 5 productid integer not null, 6 price number(19,4), 7 qty number, 8 constraint pk_items primary key (custid, orderid, productid), 9 constraint fk_items_parent foreign key (custid, orderid) 10 references orders on delete cascade 11 ) partition by reference (fk_items_parent); Table created.
7. 創建duplicated tables.
SQL> create duplicated table products 2 ( 3 productid integer generated by default as identity primary key, 4 name varchar2(128), 5 descruri varchar2(128), 6 lastprice number(19,4) 7 ) tablespace products_tsp; Table created.
8. 創建function，目的是為了后面的DEMO：
SQL> create or replace function passwcreate(passw in raw) 2 return raw 3 is 4 salt raw(8); 5 begin 6 salt := dbms_crypto.randombytes(8); 7 return utl_raw.concat(salt, dbms_crypto.hash(utl_raw.concat(salt, 8 passw), dbms_crypto.hash_sh356)); 9 end; 10 / Function created. SQL> create or replace function passwcheck(passw in raw, phash in raw) 2 return integer is 3 begin 4 return utl_raw.compare( 5 dbms_crypto.hash(utl_raw.concat(utl_raw.substr(phash, 1, 8), 6 passw), dbms_crypto.hash_sh356), 7 utl_raw.substr(phash, 9)); 8 end; 9 / Function created.
GDSCTL>connect mygdsadmin/oracle
Catalog connection is established
GDSCTL>show ddl
id DDL Text Failed shards
-- -------- -------------
7 grant execute on dbms_crypto to app_s...
8 create tablespace set tsp_set_1 using...
9 create tablespace products_tsp datafi...
10 create sharded table customers ( ...
11 create sharded table orders ( orde...
12 create sequence orders_seq
13 create sharded table lineitems ( o...
14 CREATE MATERIALIZED VIEW "APP_SCHEMA"...
15 create or replace function passwcreat...
16 create or replace function passwcheck...
10. 檢查每個shard是否有DDL錯誤
GDSCTL>config shard -shard sh2 Name: sh2 Shard Group: primary_shardgroup Status: Ok State: Deployed Region: region1 Connection string: shard2:1521/sh2:dedicated SCAN address: ONS remote port: 0 Disk Threshold, ms: 20 CPU Threshold, %: 75 Version: 12.2.0.0 Failed DDL: DDL Error: --- 沒有DDL錯誤 Failed DDL id: Availability: ONLINE Rack: Supported services ------------------------ Name Preferred Status ---- --------- ------ oltp_rw_srvc Yes Enabled GDSCTL>config shard -shard sh3 Name: sh3 Shard Group: primary_shardgroup Status: Ok State: Deployed Region: region1 Connection string: shard3:1521/sh3:dedicated SCAN address: ONS remote port: 0 Disk Threshold, ms: 20 CPU Threshold, %: 75 Version: 12.2.0.0 Failed DDL: DDL Error: --- 沒有DDL錯誤 Failed DDL id: Availability: ONLINE Rack: Supported services ------------------------ Name Preferred Status ---- --------- ------ oltp_rw_srvc Yes Enabled
驗證環境-表空間/chunks
1. 在gsm(shard1)節點，檢查chunks信息
前面創建shardcatalog時指定chunks為12，因此后續創建shard table分配12個chunks
GDSCTL>config chunks Chunks ------------------------ Database From To -------- ---- -- sh2 1 6 sh3 7 12 SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name; TABLESPACE_NAME MB ------------------------------------------------------------ ---------- PRODUCTS_TSP 100 SYSAUX 520 SYSTEM 810 TSP_SET_1 100 UNDOTBS1 70 USERS 5 6 rows selected. SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name; no rows selected SQL> col TABLE_NAME for a20 SQL> col PARTITION_NAME for a20 SQL> col TABLESPACE_NAME for a20 SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like '%SET%'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME -------------------- -------------------- -------------------- CUSTOMERS CUSTOMERS_P1 TSP_SET_1 ORDERS CUSTOMERS_P1 TSP_SET_1 LINEITEMS CUSTOMERS_P1 TSP_SET_1 SQL> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files; TABLESPACE_NAME MB -------------------- ---------- SYSTEM 810 SYSAUX 520 UNDOTBS1 70 USERS 5 TSP_SET_1 100 PRODUCTS_TSP 100 6 rows selected. 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 ------------------------------------------------------------ ---------------- sh2 6 sh3 6
2. 在shard2節點檢查表空間和chunks信息
--表空間
[oracle@shard2 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:25:30 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> select TABLESPACE_NAME, BYTES/1024/1024 MB from sys.dba_data_files order by tablespace_name; TABLESPACE_NAME MB ------------------------------------------------------------ ---------- C001TSP_SET_1 100 C002TSP_SET_1 100 C003TSP_SET_1 100 C004TSP_SET_1 100 C005TSP_SET_1 100 C006TSP_SET_1 100 PRODUCTS_TSP 100 SYSAUX 520 SYSTEM 810 TSP_SET_1 100 UNDOTBS1 70 USERS 5 12 rows selected.
創建了6個表空間，分別是C001TSP_SET_1 ~ 表空間C006TSP_SET_1，因為設置chunks=12，每個shard有6個chunks。每個表空間有一個datafile，大小是100M，這個是在創建tablespace set時設置的datafile 100M。
--檢查chunks
SQL> set linesize 140 SQL> column table_name format a20 SQL> column tablespace_name format a20 SQL> column partition_name format a20 SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_unique_name string sh2 SQL> select table_name, partition_name, tablespace_name from dba_tab_partitions where tablespace_name like 'C%TSP_SET_1' order by tablespace_name; TABLE_NAME PARTITION_NAME TABLESPACE_NAME -------------------- -------------------- -------------------- LINEITEMS CUSTOMERS_P1 C001TSP_SET_1 CUSTOMERS CUSTOMERS_P1 C001TSP_SET_1 ORDERS CUSTOMERS_P1 C001TSP_SET_1 CUSTOMERS CUSTOMERS_P2 C002TSP_SET_1 ORDERS CUSTOMERS_P2 C002TSP_SET_1 LINEITEMS CUSTOMERS_P2 C002TSP_SET_1 CUSTOMERS CUSTOMERS_P3 C003TSP_SET_1 LINEITEMS CUSTOMERS_P3 C003TSP_SET_1 ORDERS CUSTOMERS_P3 C003TSP_SET_1 LINEITEMS CUSTOMERS_P4 C004TSP_SET_1 CUSTOMERS CUSTOMERS_P4 C004TSP_SET_1 ORDERS CUSTOMERS_P4 C004TSP_SET_1 CUSTOMERS CUSTOMERS_P5 C005TSP_SET_1 ORDERS CUSTOMERS_P5 C005TSP_SET_1 LINEITEMS CUSTOMERS_P5 C005TSP_SET_1 CUSTOMERS CUSTOMERS_P6 C006TSP_SET_1 ORDERS CUSTOMERS_P6 C006TSP_SET_1 LINEITEMS CUSTOMERS_P6 C006TSP_SET_1 18 rows selected.
4. 在catalog數據庫檢查chunks信息
SQL> set echo off 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 ------------------------------------------------------------ ---------------- sh2 6 sh3 6
5. 驗證環境-tables
--catalog數據庫
SQL> conn app_schema/oracle Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------- CUSTOMERS ORDERS LINEITEMS PRODUCTS MLOG$_PRODUCTS RUPD$_PRODUCTS 6 rows selected.
--shard節點shard2和shard3
[oracle@shard2 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:36:12 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> conn app_schema/oracle Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- CUSTOMERS ORDERS LINEITEMS PRODUCTS [oracle@shard3 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Fri Oct 13 16:36:06 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> conn app_schema/oracle Connected. SQL> select table_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- CUSTOMERS ORDERS LINEITEMS PRODUCTS
--插入數據
SQL> INSERT INTO Customers (CustId, FirstName, LastName, CustProfile,
2 Class, Geo, Passwd) VALUES ('james.parker@x.bogus', 'James', 'Parker',
3 NULL, 'Gold', 'east', hextoraw('8d1c00e'));
1 row created.
SQL> commit;
Commit complete.
SQL> set termout on
SQL> set linesize 120
SQL> set echo on
SQL> column firstname format a20
SQL> column lastname format a20
SQL> explain plan for SELECT FirstName,LastName, geo, class FROM Customers;
Explained.
SQL> select plan_table_output from table(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
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):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------
2 - EXPLAIN PLAN INTO PLAN_TABLE@! FOR SELECT
"A1"."FIRSTNAME","A1"."LASTNAME","A1"."GEO","A1"."CLASS" FROM
"CUSTOMERS" "A1" /* coord_sql_id=9j0dws979r7rr */ (accessing
'ORA_SHARD_POOL@ORA_MULTI_TARGET' )
18 rows selected.以上是“Oracle sharding database的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
