Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of Oracle sharding database", which is easy to understand and well-organized. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "sample Analysis of Oracle sharding database".

Sharding architecture is a slicing technology at the database level, which can make the partitioned data be distributed in different independent databases. Sharding is a new feature of Oracle Database 12c Release 2, which can provide linear expansion and complete error isolation for OLTP applications suitable for Sharding technology. Q We can simply understand Sharding as an extension of Oracle table partitioning technology. The installation operation will be described in detail below.

1.Oracle Sharding installation conditions

12.2 Enterprise Edition

Non-cdb

Use a file system instead of ASM (12.2 Beta requirements, which may change after official release)

The host hosts file is written with the IP resolution of the local machine and each shard node.

The machine must be brand new and cannot leave any information that oracle has been installed before.

two。 Set environment variables, create related directories, and set kernel parameters

Shardcat and gds are installed on the same host, the same oracle user, different ORACLE_HOME.

[oracle12c@sdb1] $cat .bash _ profile# .bash _ profile# Get the aliases and functionsif [- f ~ / .bashrc]; then. ~ / .bashrcfi # User specific environment and startup programs PATH=$PATH:$HOME/bin export PATH # Oracle SettingsTMP=/tmp; export TMPTMPDIR=$TMP; export TMPDIR ORACLE_BASE=/u01/ora12c/app/oracle; export ORACLE_BASEDB_HOME=$ORACLE_BASE/product/12.2.0/db_1; export DB_HOMEGSM_HOME=$ORACLE_BASE/product/12.2.0/gsm; export GSM_HOMEORACLE_HOME=$DB_HOME; export ORACLE_HOMEORACLE_SID=shardcat; export ORACLE_SIDORACLE_TERM=xterm; export ORACLE_TERMBASE_PATH=/usr/sbin:$PATH; export BASE_PATHPATH=/usr/sbin:$PATH Export PATHPATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH=$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 oracleChanging password for user oracle.New password: BAD PASSWORD: The password is shorter than 8 charactersRetype 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 _ profileexport PATH# .bash _ profile# Get the aliases and functionsif [- f ~ / .bashrc] Then. ~ / .bashrcfi # User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHTEMP=/u01/tmpTMPDIR=/u01/tmpexport TEMP TMPDIRexport LD_ASSUME_KERNEL=3.8.13export ORACLE_BASE=/u01/app/oracleDB_HOME=/u01/app/oracle/product/12.2.0/dbGSM_HOME=/u01/app/oracle/product/12.2.0/gsmexport ORACLE_HOME=$DB_HOMEexport ORACLE_SID=jytest2export ORACLE_UNQNAME=jytestexport NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/dataLD_LIBRARY _ PATH=$ORACLE_HOME/lib:/lib:/usr/libLD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/libexport LD_LIBRARY_PATHexport PATH=$PATH:$ORACLE_HOME/binCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibCLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlibexport CLASSPATHumask=022 [oracle@shard1 ~] $alias gsm_env='. / home/oracle/gsm_env' [oracle@shard1 ~] $alias db_env='. / home/oracle/db_env' [oracle@shard1 ~] $vi gsm_envORACLE_HOME=$GSM_HOME; export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH [oracle@shard1 ~] $vi db_envORACLE_HOME=$DB_HOME; export ORACLE_HOMEPATH=$ORACLE_HOME/bin:$BASE_PATH; export PATH

Modify kernel parameter edit / etc/sysctl.conf file

[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 = 1048576fs.file-max = 6815744kernel.shmall = 2097152kernel.shmmax = 4294967295kernel.shmmni = 4096kernel.sem = 25032000100 128net.ipv4.ip_local_port_range = 900065500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576 [root@shard1] # sysctl-pfs.aio-max-nr = 1048576fs.file-max = 6815744kernel.shmall = 2097152kernel.shmmax = 4294967295kernel.shmmni = 4096kernel.sem = 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576

Modify the shell limit of the oarcle parameter to add the following parameters to the / etc/security/limits.conf file of all nodes

Oracle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536oracle soft stack 10240oracle hard stack 32768oracle soft memlock 3145728oracle hard memlock 3145728

Modify the default parameter file for shell to add the following to the / etc/profile file for all nodes:

If [$USER = "oracle"]; thenif [$SHELL = "/ bin/ksh"]; thenulimit-p 16384ulimit-n 65536elseulimit-u 16384-n 65536fifi

3. Install Oracle softwar

[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-oracleLast 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-lrttotal 24-rwxr-xr-x. 1 oracle oinstall 500 Feb 7 2013 welcome.htmldrwxr-xr-x. 4 oracle oinstall 4096 Jan 26 2017 install-rwxr-xr-x. 1 oracle oinstall 8771 Jan 26 2017 runInstallerdrwxr-xr-x. 2 oracle oinstall 34 Jan 26 2017 rpmdrwxrwxr-x. 2 oracle oinstall 28 Jan 26 2017 sshsetupdrwxrwxr-x. 2 oracle oinstall 58 Jan 26 2017 responsedrwxr-xr-x. 14 oracle oinstall 4096 Jan 26 2017 stage [oracle@shard1 database] $. / runInstaller

[root@shard1 soft] # / u01/app/oraInventory/orainstRoot.shChanging 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.shPerforming root user operation.The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= / u01/app/oracle/product/12.2.0/dbEnter 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 byDatabase Configuration Assistant when a database is createdFinished 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]: yesInstalling 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.logFinished installing Oracle Trace File Analyzer (TFA)

The operation of installing Oracle software on the other two shard hosts shard2,shard3 is no longer described as above.

The oracle environment variable on shard2 is set as follows, where ORACLE_SID and

ORACLE_UNQNAME is set to sh2, which will be read later when registering shard with the shard catalog database

[oracle@shard2] $cat .bash _ profile# .bash _ profile# Get the aliases and functionsif [- f ~ / .bashrc]; then. ~ / .bashrcfi # User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHTEMP=/u01/tmpTMPDIR=/u01/tmpexport TEMP TMPDIRexport LD_ASSUME_KERNEL=3.8.13export ORACLE_BASE=/u01/app/oracleDB_HOME=/u01/app/oracle/product/12.2.0/dbGSM_HOME=/u01/app/oracle/product/12.2.0/gsmexport ORACLE_HOME=$DB_HOMEexport ORACLE_SID=sh2export ORACLE_UNQNAME=sh2export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/dataLD_LIBRARY _ PATH=$ORACLE_HOME/lib:/lib:/usr/libLD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/libexport LD_LIBRARY_PATHexport PATH=$PATH:$ORACLE_HOME/binCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibCLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlibexport CLASSPATHumask=022

The oracle environment variable on shard3 is set as follows, where ORACLE_SID and

ORACLE_UNQNAME is set to sh2, which will be read later when registering shard with the shard catalog database

[oracle@shard3] $vi .bash _ profile# .bash _ profile# Get the aliases and functionsif [- f ~ / .bashrc]; then. ~ / .bashrcfi # User specific environment and startup programsPATH=$PATH:$HOME/.local/bin:$HOME/binexport PATHTEMP=/u01/tmpTMPDIR=/u01/tmpexport TEMP TMPDIRexport LD_ASSUME_KERNEL=3.8.13export ORACLE_BASE=/u01/app/oracleDB_HOME=/u01/app/oracle/product/12.2.0/dbGSM_HOME=/u01/app/oracle/product/12.2.0/gsmexport ORACLE_HOME=$DB_HOMEexport ORACLE_SID=sh3export ORACLE_UNQNAME=sh3export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/dataLD_LIBRARY _ PATH=$ORACLE_HOME/lib:/lib:/usr/libLD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/libexport LD_LIBRARY_PATHexport PATH=$PATH:$ORACLE_HOME/binCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlibCLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlibexport CLASSPATHumask=022

4. Install 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-oracleLast 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-lrttotal 24-rwxrwxr-x. 1 oracle oinstall 500 Feb 7 2013 welcome.html-rwxr-xr-x. 1 oracle oinstall 8772 Jan 26 2017 runInstallerdrwxr-xr-x. 4 oracle oinstall 4096 Jan 26 2017 installdrwxrwxr-x. 2 oracle oinstall 28 Jan 26 2017 responsedrwxr-xr-x. 14 oracle oinstall 4096 Jan 26 2017 stage [oracle@shard1 gsm] $. / runInstaller

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.