Oracle Linux Install Oracle 18C RAC

安装环境为Oracle Linux 7.1,Oracle版本为18C,下面是RAC环境的IP配置

ip地址          主机名                   类型     解析方式 
10.10.10.171  18c1                    public   DNS或etc/hosts 
10.10.10.172  18c2                    public   DNS或etc/hosts 
88.88.87.1    18c1-priv               private  DNS或etc/hosts 
88.88.87.2    18c2-priv               private  DNS或etc/hosts 
10.10.10.175  18c1-vip                virtual  DNS或etc/hosts 
10.10.10.176  18c2-vip                virtual  DNS或etc/hosts
10.10.10.177  jycs-scan               scan     DNS或etc/hosts
10.10.10.178  jycs-scan               scan     DNS或etc/hosts
10.10.10.179  jycs-scan               scan     DNS或etc/hosts

[root@localhost soft]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.171 18c1
10.10.10.172 18c2
88.88.87.1 18c1-priv
88.88.87.2 18c2-priv
10.10.10.175 18c1-vip
10.10.10.176 18c2-vip

10.10.10.177 jycs-scan
10.10.10.178 jycs-scan
10.10.10.179 jycs-scan

[root@localhost ~]# vi /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.171 18c1
10.10.10.172 18c2
88.88.87.1 18c1-priv
88.88.87.2 18c2-priv
10.10.10.175 18c1-vip
10.10.10.176 18c2-vip

10.10.10.177 jycs-scan
10.10.10.178 jycs-scan
10.10.10.179 jycs-scan

创建用户组

[root@jytest1 ~]# groupadd -g 1006 asmadmin
[root@jytest1 ~]# groupadd -g 1007 asmdba
[root@jytest1 ~]# groupadd -g 1008 asmoper
[root@jytest1 ~]# groupadd -g 1009 dba
[root@jytest1 ~]# groupadd -g 1010 oper
[root@jytest1 ~]# groupadd -g 1011 oinstall
[root@jytest1 ~]# groupadd -g 1012 backupdba
[root@jytest1 ~]# groupadd -g 1013 dgdba
[root@jytest1 ~]# groupadd -g 1014 kmdba
[root@jytest1 ~]# groupadd -g 1015 racdba


[root@jytest2 ~]# groupadd -g 1006 asmadmin
[root@jytest2 ~]# groupadd -g 1007 asmdba
[root@jytest2 ~]# groupadd -g 1008 asmoper
[root@jytest2 ~]# groupadd -g 1009 dba
[root@jytest2 ~]# groupadd -g 1010 oper
[root@jytest2 ~]# groupadd -g 1011 oinstall
[root@jytest2 ~]# groupadd -g 1012 backupdba
[root@jytest2 ~]# groupadd -g 1013 dgdba
[root@jytest2 ~]# groupadd -g 1014 kmdba
[root@jytest2 ~]# groupadd -g 1015 racdba

创建用户
[root@jytest1 ~]#useradd -u 1001 -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba,oper,asmadmin oracle
[root@jytest1 ~]#useradd -u 1002 -g oinstall -G asmadmin,asmdba,asmoper,dba grid

[root@jytest2 ~]#useradd -u 1001 -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba,oper,asmadmin oracle
[root@jytest2 ~]#useradd -u 1002 -g oinstall -G asmadmin,asmdba,asmoper,dba grid

[root@jytest1 /]# passwd grid
Changing password for user grid.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
You have new mail in /var/spool/mail/root
[root@jytest1 /]# 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@jytest2 /]# passwd grid
Changing password for user grid.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@jytest2 /]# 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.

配置ASM所需磁盘,编辑/etc/udev/rules.d/99-my-asmdevices.rules配置文件

[root@18c1 rules.d]# vi /etc/udev/rules.d/99-my-asmdevices.rules
KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c29b61b89a10988ac7ee8d332517", RUN+="/bin/sh -c 'mknod /dev/asmdisk01 b  $major $minor; chown grid:asmadmin /dev/asmdisk01; chmod 0660 /dev/asmdisk01'"

KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c29dfb622388fc0d35385109c4e9", RUN+="/bin/sh -c 'mknod /dev/asmdisk02 b  $major $minor; chown grid:asmadmin /dev/asmdisk02; chmod 0660 /dev/asmdisk02'"

[root@18c2 rules.d]# vi /etc/udev/rules.d/99-my-asmdevices.rules
KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c29b61b89a10988ac7ee8d332517", RUN+="/bin/sh -c 'mknod /dev/asmdisk01 b  $major $minor; chown grid:asmadmin /dev/asmdisk01; chmod 0660 /dev/asmdisk01'"

KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c29dfb622388fc0d35385109c4e9", RUN+="/bin/sh -c 'mknod /dev/asmdisk02 b  $major $minor; chown grid:asmadmin /dev/asmdisk02; chmod 0660 /dev/asmdisk02'"
[root@18c1 rules.d]#  /sbin/udevadm trigger --type=devices --action=change
[root@18c2 ~]#  /sbin/udevadm trigger --type=devices --action=change

[root@18c1 rules.d]#  ls -lrt /dev/asm*
brw-rw----. 1 grid asmadmin 8, 32 Mar 16 17:00 /dev/asmdisk01
brw-rw----. 1 grid asmadmin 8, 32 Mar 16 17:01 /dev/asmdisk02

[root@18c2 ~]#  ls -lrt /dev/asm*
brw-rw----. 1 grid asmadmin 8, 16 Mar 16 17:00 /dev/asmdisk01
brw-rw----. 1 grid asmadmin 8, 32 Mar 16 17:02 /dev/asmdisk02

以root用户创建“Oracle inventory 目录”

[root@18c1 rules.d]# mkdir -p /u01/app/oraInventory
[root@18c1 rules.d]# chown -R grid:oinstall /u01/app/oraInventory
[root@18c1 rules.d]# chmod -R 775 /u01/app/oraInventory

[root@18c2 ~]# mkdir -p /u01/app/oraInventory
[root@18c2 ~]# chown -R grid:oinstall /u01/app/oraInventory
[root@18c2 ~]# chmod -R 775 /u01/app/oraInventory

以root用户创建“Grid Infrastructure BASE 目录”

[root@18c1 rules.d]# mkdir -p /u01/app/grid
[root@18c1 rules.d]# chown -R grid:oinstall /u01/app/grid
[root@18c1 rules.d]# chmod -R 775 /u01/app/grid

[root@18c2 ~]# mkdir -p /u01/app/grid
[root@18c2 ~]# chown -R grid:oinstall /u01/app/grid
[root@18c2 ~]# chmod -R 775 /u01/app/grid

以root用户创建“Grid Infrastructure Home 目录”

[root@18c1 rules.d]# mkdir -p /u01/app/18.0.0/grid
[root@18c1 rules.d]# chown -R grid:oinstall /u01/app/18.0.0/grid
[root@18c1 rules.d]# chmod -R 775 /u01/app/18.0.0/grid

[root@18c2 ~]# mkdir -p /u01/app/18.0.0/grid
[root@18c2 ~]# chown -R grid:oinstall /u01/app/18.0.0/grid
[root@18c2 ~]# chmod -R 775 /u01/app/18.0.0/grid

以root用户创建“Oracle Base 目录”

[root@18c1 rules.d]#  mkdir -p /u01/app/oracle
[root@18c1 rules.d]#  chown -R oracle:oinstall /u01/app/oracle
[root@18c1 rules.d]# chmod -R 775 /u01/app/oracle

[root@18c2 ~]#  mkdir -p /u01/app/oracle
[root@18c2 ~]#  chown -R oracle:oinstall /u01/app/oracle
[root@18c2 ~]# chmod -R 775 /u01/app/oracle

以root用户创建“Oracle RDBMS Home 目录”

[root@18c1 rules.d]# mkdir -p /u01/app/oracle/18.0.0/db
[root@18c1 rules.d]# chown -R oracle:oinstall /u01/app/oracle/18.0.0/db
[root@18c1 rules.d]# chmod -R 775 /u01/app/oracle/18.0.0/db

[root@18c2 ~]# mkdir -p /u01/app/oracle/18.0.0/db
[root@18c2 ~]# chown -R oracle:oinstall /u01/app/oracle/18.0.0/db
[root@18c2 ~]# chmod -R 775 /u01/app/oracle/18.0.0/db

创建一个tmp目录

[root@18c1 /]# mkdir /u01/tmp
[root@18c1 /]# chmod a+wr /u01/tmp

[root@18c2 ~]# mkdir /u01/tmp
[root@18c2 ~]# chmod a+wr /u01/tmp

设置环境变量

[root@jytest1 ~]# su - grid

[grid@18c1 ~]$ 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/grid
export ORACLE_HOME=/u01/app/18.0.0/grid
export ORACLE_SID=+ASM1
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
[root@jytest2 ~]# su - grid
[grid@18c2 ~]$ 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/grid
export ORACLE_HOME=/u01/app/18.0.0/grid
export ORACLE_SID=+ASM2
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

[root@18c1 /]# su - oracle
[oracle@18c1 ~]$ 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
export ORACLE_HOME=/u01/app/oracle/18.0.0/db
export ORACLE_SID=jycs1
export ORACLE_UNQNAME=jycs
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

[root@18c2 ~]# su - oracle
[oracle@18c2 ~]$ 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
export ORACLE_HOME=/u01/app/oracle/18.0.0/db
export ORACLE_SID=jycs2
export ORACLE_UNQNAME=jycs
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

修改内核参数编辑/etc/sysctl.conf文件

[root@18c1 /]# 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
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304



[root@18c1 /]# 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
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304

[root@18c2 ~]# 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
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304

~
[root@18c2 ~]# 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
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304

修改oarcle参数的shell限制,在所有节点的/etc/security/limits.conf文件中添加以下参数

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

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


if [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

对C shell(csh or tcsh) 在所有节点的/etc/csh.login文件中增加以下代码

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

if ( $USER == "grid" ) then
limit maxproc 16384
limit descriptors 65536
endif

解压GI安装压缩包:

[grid@jytest1 soft]cd /soft/
[grid@18c1 soft]$ unzip LINUX.X64_180000_grid_home.zip -d /u01/app/18.0.0/grid

这里使用xshell与xmanager来执行安装

[root@18c1 ~]# xhost +
access control disabled, clients can connect from any host
[root@18c1 ~]# su - grid
Last login: Mon Mar 16 17:36:02 CST 2020 on pts/1
[grid@18c1 ~]$ cd /u01/app/18.0.0/grid
[grid@18c1 grid]$ export DISPLAY=10.138.130.242:0.0
[grid@18c1 grid]$ ./gridSetup.sh























以root用户分别在两个节点上执行以下脚本,先在主节点执行。

[root@18c1 /]# ./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@18c2 /]# ./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@18c1 /]# ./u01/app/18.0.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/18.0.0/grid

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.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/18.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/18c1/crsconfig/rootcrs_18c1_2020-03-16_07-10-52PM.log
2020/03/16 19:11:26 CLSRSC-594: Executing installation step 1 of 20: 'SetupTFA'.
2020/03/16 19:11:26 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
2020/03/16 19:12:15 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2020/03/16 19:12:15 CLSRSC-594: Executing installation step 2 of 20: 'ValidateEnv'.
2020/03/16 19:12:15 CLSRSC-363: User ignored prerequisites during installation
2020/03/16 19:12:15 CLSRSC-594: Executing installation step 3 of 20: 'CheckFirstNode'.
2020/03/16 19:12:19 CLSRSC-594: Executing installation step 4 of 20: 'GenSiteGUIDs'.
2020/03/16 19:12:24 CLSRSC-594: Executing installation step 5 of 20: 'SaveParamFile'.
2020/03/16 19:12:41 CLSRSC-594: Executing installation step 6 of 20: 'SetupOSD'.
2020/03/16 19:12:41 CLSRSC-594: Executing installation step 7 of 20: 'CheckCRSConfig'.
2020/03/16 19:12:41 CLSRSC-594: Executing installation step 8 of 20: 'SetupLocalGPNP'.
2020/03/16 19:13:25 CLSRSC-594: Executing installation step 9 of 20: 'CreateRootCert'.
2020/03/16 19:13:35 CLSRSC-594: Executing installation step 10 of 20: 'ConfigOLR'.
2020/03/16 19:13:58 CLSRSC-594: Executing installation step 11 of 20: 'ConfigCHMOS'.
2020/03/16 19:13:58 CLSRSC-594: Executing installation step 12 of 20: 'CreateOHASD'.
2020/03/16 19:14:13 CLSRSC-594: Executing installation step 13 of 20: 'ConfigOHASD'.
2020/03/16 19:14:14 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2020/03/16 19:15:02 CLSRSC-594: Executing installation step 14 of 20: 'InstallAFD'.
2020/03/16 19:16:10 CLSRSC-594: Executing installation step 15 of 20: 'InstallACFS'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '18c1'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '18c1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2020/03/16 19:16:55 CLSRSC-594: Executing installation step 16 of 20: 'InstallKA'.
2020/03/16 19:17:10 CLSRSC-594: Executing installation step 17 of 20: 'InitConfig'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '18c1'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '18c1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2672: Attempting to start 'ora.driver.afd' on '18c1'
CRS-2672: Attempting to start 'ora.evmd' on '18c1'
CRS-2672: Attempting to start 'ora.mdnsd' on '18c1'
CRS-2676: Start of 'ora.driver.afd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on '18c1'
CRS-2676: Start of 'ora.cssdmonitor' on '18c1' succeeded
CRS-2676: Start of 'ora.mdnsd' on '18c1' succeeded
CRS-2676: Start of 'ora.evmd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on '18c1'
CRS-2676: Start of 'ora.gpnpd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on '18c1'
CRS-2676: Start of 'ora.gipcd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.cssd' on '18c1'
CRS-2672: Attempting to start 'ora.diskmon' on '18c1'
CRS-2676: Start of 'ora.diskmon' on '18c1' succeeded
CRS-2676: Start of 'ora.cssd' on '18c1' succeeded

[INFO] [DBT-30161] Disk label(s) created successfully. Check /u01/app/grid/cfgtoollogs/asmca/asmca-200316PM071759.log for details.
[INFO] [DBT-30001] Disk groups created successfully. Check /u01/app/grid/cfgtoollogs/asmca/asmca-200316PM071759.log for details.


2020/03/16 19:19:59 CLSRSC-482: Running command: '/u01/app/18.0.0/grid/bin/ocrconfig -upgrade grid oinstall'
CRS-2672: Attempting to start 'ora.crf' on '18c1'
CRS-2672: Attempting to start 'ora.storage' on '18c1'
CRS-2676: Start of 'ora.storage' on '18c1' succeeded
CRS-2676: Start of 'ora.crf' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on '18c1'
CRS-2676: Start of 'ora.crsd' on '18c1' succeeded
CRS-4256: Updating the profile
Successful addition of voting disk b1f6f23bbaef4ff2bf3cdfdec8a72881.
Successfully replaced voting disk group with +CRS.
CRS-4256: Updating the profile
CRS-4266: Voting file(s) successfully replaced
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   b1f6f23bbaef4ff2bf3cdfdec8a72881 (AFD:CRS1) [CRS]
Located 1 voting disk(s).
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '18c1'
CRS-2673: Attempting to stop 'ora.crsd' on '18c1'
CRS-2677: Stop of 'ora.crsd' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.storage' on '18c1'
CRS-2673: Attempting to stop 'ora.crf' on '18c1'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on '18c1'
CRS-2673: Attempting to stop 'ora.mdnsd' on '18c1'
CRS-2677: Stop of 'ora.crf' on '18c1' succeeded
CRS-2677: Stop of 'ora.storage' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on '18c1'
CRS-2677: Stop of 'ora.drivers.acfs' on '18c1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on '18c1' succeeded
CRS-2677: Stop of 'ora.asm' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on '18c1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on '18c1'
CRS-2673: Attempting to stop 'ora.evmd' on '18c1'
CRS-2677: Stop of 'ora.ctssd' on '18c1' succeeded
CRS-2677: Stop of 'ora.evmd' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on '18c1'
CRS-2677: Stop of 'ora.cssd' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.driver.afd' on '18c1'
CRS-2673: Attempting to stop 'ora.gipcd' on '18c1'
CRS-2673: Attempting to stop 'ora.gpnpd' on '18c1'
CRS-2677: Stop of 'ora.driver.afd' on '18c1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on '18c1' succeeded
CRS-2677: Stop of 'ora.gipcd' on '18c1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '18c1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
2020/03/16 19:22:35 CLSRSC-594: Executing installation step 18 of 20: 'StartCluster'.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.evmd' on '18c1'
CRS-2672: Attempting to start 'ora.mdnsd' on '18c1'
CRS-2676: Start of 'ora.mdnsd' on '18c1' succeeded
CRS-2676: Start of 'ora.evmd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on '18c1'
CRS-2676: Start of 'ora.gpnpd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on '18c1'
CRS-2676: Start of 'ora.gipcd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on '18c1'
CRS-2676: Start of 'ora.cssdmonitor' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.crf' on '18c1'
CRS-2672: Attempting to start 'ora.cssd' on '18c1'
CRS-2672: Attempting to start 'ora.diskmon' on '18c1'
CRS-2676: Start of 'ora.diskmon' on '18c1' succeeded
CRS-2676: Start of 'ora.crf' on '18c1' succeeded
CRS-2676: Start of 'ora.cssd' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on '18c1'
CRS-2672: Attempting to start 'ora.ctssd' on '18c1'
CRS-2676: Start of 'ora.ctssd' on '18c1' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.asm' on '18c1'
CRS-2676: Start of 'ora.asm' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.storage' on '18c1'
CRS-2676: Start of 'ora.storage' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.crsd' on '18c1'
CRS-2676: Start of 'ora.crsd' on '18c1' succeeded
CRS-6023: Starting Oracle Cluster Ready Services-managed resources
CRS-6017: Processing resource auto-start for servers: 18c1
CRS-6016: Resource auto-start has completed for server 18c1
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2020/03/16 19:24:23 CLSRSC-343: Successfully started Oracle Clusterware stack
2020/03/16 19:24:23 CLSRSC-594: Executing installation step 19 of 20: 'ConfigNode'.
CRS-2672: Attempting to start 'ora.ASMNET1LSNR_ASM.lsnr' on '18c1'
CRS-2676: Start of 'ora.ASMNET1LSNR_ASM.lsnr' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.asm' on '18c1'
CRS-2676: Start of 'ora.asm' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.CRS.dg' on '18c1'
CRS-2676: Start of 'ora.CRS.dg' on '18c1' succeeded
2020/03/16 19:27:00 CLSRSC-594: Executing installation step 20 of 20: 'PostConfig'.
2020/03/16 19:29:03 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded 

[root@18c2 /]# ./u01/app/18.0.0/grid/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= grid
    ORACLE_HOME=  /u01/app/18.0.0/grid

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.
Relinking oracle with rac_on option
Using configuration parameter file: /u01/app/18.0.0/grid/crs/install/crsconfig_params
The log of current session can be found at:
  /u01/app/grid/crsdata/18c2/crsconfig/rootcrs_18c2_2020-03-16_07-30-27PM.log
2020/03/16 19:30:47 CLSRSC-594: Executing installation step 1 of 20: 'SetupTFA'.
2020/03/16 19:30:47 CLSRSC-4001: Installing Oracle Trace File Analyzer (TFA) Collector.
2020/03/16 19:32:12 CLSRSC-4002: Successfully installed Oracle Trace File Analyzer (TFA) Collector.
2020/03/16 19:32:12 CLSRSC-594: Executing installation step 2 of 20: 'ValidateEnv'.
2020/03/16 19:32:12 CLSRSC-363: User ignored prerequisites during installation
2020/03/16 19:32:12 CLSRSC-594: Executing installation step 3 of 20: 'CheckFirstNode'.
2020/03/16 19:32:15 CLSRSC-594: Executing installation step 4 of 20: 'GenSiteGUIDs'.
2020/03/16 19:32:15 CLSRSC-594: Executing installation step 5 of 20: 'SaveParamFile'.
2020/03/16 19:32:19 CLSRSC-594: Executing installation step 6 of 20: 'SetupOSD'.
2020/03/16 19:32:20 CLSRSC-594: Executing installation step 7 of 20: 'CheckCRSConfig'.
2020/03/16 19:32:20 CLSRSC-594: Executing installation step 8 of 20: 'SetupLocalGPNP'.
2020/03/16 19:32:22 CLSRSC-594: Executing installation step 9 of 20: 'CreateRootCert'.
2020/03/16 19:32:22 CLSRSC-594: Executing installation step 10 of 20: 'ConfigOLR'.
2020/03/16 19:32:27 CLSRSC-594: Executing installation step 11 of 20: 'ConfigCHMOS'.
2020/03/16 19:32:27 CLSRSC-594: Executing installation step 12 of 20: 'CreateOHASD'.
2020/03/16 19:32:29 CLSRSC-594: Executing installation step 13 of 20: 'ConfigOHASD'.
2020/03/16 19:32:29 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.service'
2020/03/16 19:33:05 CLSRSC-594: Executing installation step 14 of 20: 'InstallAFD'.
2020/03/16 19:33:58 CLSRSC-594: Executing installation step 15 of 20: 'InstallACFS'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '18c2'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '18c2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
2020/03/16 19:34:31 CLSRSC-594: Executing installation step 16 of 20: 'InstallKA'.
2020/03/16 19:34:33 CLSRSC-594: Executing installation step 17 of 20: 'InitConfig'.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '18c2'
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '18c2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
CRS-4123: Oracle High Availability Services has been started.
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on '18c2'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on '18c2'
CRS-2677: Stop of 'ora.drivers.acfs' on '18c2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on '18c2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
2020/03/16 19:35:14 CLSRSC-594: Executing installation step 18 of 20: 'StartCluster'.
CRS-4123: Starting Oracle High Availability Services-managed resources
CRS-2672: Attempting to start 'ora.mdnsd' on '18c2'
CRS-2672: Attempting to start 'ora.evmd' on '18c2'
CRS-2676: Start of 'ora.mdnsd' on '18c2' succeeded
CRS-2676: Start of 'ora.evmd' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on '18c2'
CRS-2676: Start of 'ora.gpnpd' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on '18c2'
CRS-2676: Start of 'ora.gipcd' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on '18c2'
CRS-2676: Start of 'ora.cssdmonitor' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.crf' on '18c2'
CRS-2672: Attempting to start 'ora.cssd' on '18c2'
CRS-2672: Attempting to start 'ora.diskmon' on '18c2'
CRS-2676: Start of 'ora.diskmon' on '18c2' succeeded
CRS-2676: Start of 'ora.crf' on '18c2' succeeded
CRS-2676: Start of 'ora.cssd' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on '18c2'
CRS-2672: Attempting to start 'ora.ctssd' on '18c2'
CRS-2676: Start of 'ora.ctssd' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.crsd' on '18c2'
CRS-2676: Start of 'ora.crsd' on '18c2' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.asm' on '18c2'
CRS-2676: Start of 'ora.asm' on '18c2' succeeded
CRS-6017: Processing resource auto-start for servers: 18c2
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on '18c1'
CRS-2672: Attempting to start 'ora.ASMNET1LSNR_ASM.lsnr' on '18c2'
CRS-2672: Attempting to start 'ora.ons' on '18c2'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on '18c1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on '18c1'
CRS-2677: Stop of 'ora.scan1.vip' on '18c1' succeeded
CRS-2672: Attempting to start 'ora.scan1.vip' on '18c2'
CRS-2676: Start of 'ora.scan1.vip' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN1.lsnr' on '18c2'
CRS-2676: Start of 'ora.ASMNET1LSNR_ASM.lsnr' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.asm' on '18c2'
CRS-2676: Start of 'ora.ons' on '18c2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN1.lsnr' on '18c2' succeeded
CRS-2676: Start of 'ora.asm' on '18c2' succeeded
CRS-2672: Attempting to start 'ora.proxy_advm' on '18c1'
CRS-2672: Attempting to start 'ora.proxy_advm' on '18c2'
CRS-2676: Start of 'ora.proxy_advm' on '18c1' succeeded
CRS-2676: Start of 'ora.proxy_advm' on '18c2' succeeded
CRS-6016: Resource auto-start has completed for server 18c2
CRS-6024: Completed start of Oracle Cluster Ready Services-managed resources
CRS-4123: Oracle High Availability Services has been started.
2020/03/16 19:38:09 CLSRSC-343: Successfully started Oracle Clusterware stack
2020/03/16 19:38:09 CLSRSC-594: Executing installation step 19 of 20: 'ConfigNode'.
2020/03/16 19:38:34 CLSRSC-594: Executing installation step 20 of 20: 'PostConfig'.
2020/03/16 19:39:07 CLSRSC-325: Configure Oracle Grid Infrastructure for a Cluster ... succeeded

检查集群信息

[root@18c1 /]# su - grid
Last login: Mon Mar 16 19:40:16 CST 2020
[grid@18c1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.CRS.GHCHKPT.advm
               OFFLINE OFFLINE      18c1                     STABLE
               OFFLINE OFFLINE      18c2                     STABLE
ora.CRS.dg
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.crs.ghchkpt.acfs
               OFFLINE OFFLINE      18c1                     volume /opt/oracle/r
                                                             hp_images/chkbase is
                                                             unmounted,STABLE
               OFFLINE OFFLINE      18c2                     STABLE
ora.helper
               OFFLINE OFFLINE      18c1                     STABLE
               OFFLINE OFFLINE      18c2                     IDLE,STABLE
ora.net1.network
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.ons
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.proxy_advm
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.18c1.vip
      1        ONLINE  ONLINE       18c1                     STABLE
ora.18c2.vip
      1        ONLINE  ONLINE       18c2                     STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       18c2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       18c1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       18c1                     STABLE
ora.MGMTLSNR
      1        OFFLINE OFFLINE                               STABLE
ora.asm
      1        ONLINE  ONLINE       18c1                     Started,STABLE
      2        ONLINE  ONLINE       18c2                     Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       18c1                     STABLE
ora.qosmserver
      1        ONLINE  ONLINE       18c1                     STABLE
ora.rhpserver
      1        OFFLINE OFFLINE                               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       18c2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       18c1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       18c1                     STABLE
--------------------------------------------------------------------------------


安装数据库软件

[oracle@18c1 soft]$ unzip LINUX.X64_180000_db_home.zip -d /u01/app/oracle/18.0.0/db 

[root@18c1 ~]# xhost +
access control disabled, clients can connect from any host
[root@18c1 ~]# su - oracle
Last login: Mon Mar 16 17:20:14 CST 2020 on pts/0
[oracle@18c1 ~]$ cd  $ORACLE_HOME
[oracle@18c1  db]$ export DISPLAY=10.138.130.242:0.0
[oracle@18c1 db]$ ./runInstaller 











以 root用户在所有节点上执行以下脚本,先在主节点执行

[root@18c1 /]# ./u01/app/oracle/18.0.0/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/18.0.0/db

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.

[root@18c2 /]# ./u01/app/oracle/18.0.0/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/18.0.0/db

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.

创建数据库jycs

[oracle@jytest1 database]$ dbca
















检查数据库配置信息

[grid@18c2 18.0.0]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.CRS.GHCHKPT.advm
               OFFLINE OFFLINE      18c1                     STABLE
               OFFLINE OFFLINE      18c2                     STABLE
ora.CRS.dg
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.DATA.dg
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.chad
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.crs.ghchkpt.acfs
               OFFLINE OFFLINE      18c1                     STABLE
               OFFLINE OFFLINE      18c2                     STABLE
ora.helper
               OFFLINE OFFLINE      18c1                     IDLE,STABLE
               OFFLINE OFFLINE      18c2                     IDLE,STABLE
ora.net1.network
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.ons
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
ora.proxy_advm
               ONLINE  ONLINE       18c1                     STABLE
               ONLINE  ONLINE       18c2                     STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.18c1.vip
      1        ONLINE  ONLINE       18c1                     STABLE
ora.18c2.vip
      1        ONLINE  ONLINE       18c2                     STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       18c2                     STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       18c1                     STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       18c1                     STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       18c1                     169.254.11.99 88.88.
                                                             87.1,STABLE
ora.asm
      1        ONLINE  ONLINE       18c1                     Started,STABLE
      2        ONLINE  ONLINE       18c2                     Started,STABLE
      3        OFFLINE OFFLINE                               STABLE
ora.cvu
      1        ONLINE  ONLINE       18c1                     STABLE
ora.jycs.db
      1        ONLINE  ONLINE       18c1                     Open,HOME=/u01/app/o
                                                             racle/18.0.0/db,STAB
                                                             LE
      2        ONLINE  ONLINE       18c2                     Open,HOME=/u01/app/o
                                                             racle/18.0.0/db,STAB
                                                             LE
ora.mgmtdb
      1        ONLINE  ONLINE       18c1                     Open,STABLE
ora.qosmserver
      1        ONLINE  ONLINE       18c1                     STABLE
ora.rhpserver
      1        OFFLINE OFFLINE                               STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       18c2                     STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       18c1                     STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       18c1                     STABLE
--------------------------------------------------------------------------------

到此18C RAC for Oracle Linux 7.1的安装完成!

DMSQL DM自增列的使用

DM自增列的使用
DM自增列定义
1.自增列功能定义
在表中创建一个自增列。该属性与CREATE TABLE语句一起使用,一个表只能有一个自增列。
语法格式
IDENTITY [ (种子, 增量) ]
参数
1.种子 装载到表中的第一个行所使用的值;
2.增量 增量值,该值被添加到前一个已装载的行的标识值上。增量值可以为正数或负数,但不能为0。

使用说明
1.IDENTITY 适用于int(-2147483648~+2147483647)、bigint(-263~+263-2)类型的列;每个表只能创建一个自增列;
2.不能对自增列使用DEFAULT 约束;
3.必须同时指定种子和增量值,或者二者都不指定。如果二者都未指定,则取默认值(1,1);若种子或增量为小数类型,报错;
4.最大值和最小值为该列的数据类型的边界;
5.建表种子和增量大于最大值或者种子和增量小于最小值时报错;
6.自增列一旦生成,无法更新,不允许用Update语句进行修改;
7. 临时表、列存储表、水平分区表、垂直分区表不支持使用自增列。

2.自增列查询函数
1) IDENT_SEED(函数)
语法格式:
IDENT_SEED (‘tablename’)
功能:返回种子值,该值是在带有自增列的表中创建自增列时指定的。
参数:tablename:是带有引号的字符串常量,也可以是变量、函数或列名。tablename的数据类型为char或varchar。其含义是表名,可带模式名前缀。
返回类型:返回数据类型为int / NULL
2) IDENT_INCR(函数)
语法格式:
IDENT_INCR (‘tablename’)
功能:返回增量值,该值是在带有自增列的表中创建自增列时指定的。
参数:tablename:是带有引号的字符串常量,也可以是变量、函数或列名。tablename的数据类型为char或varchar。其含义是表名,可带模式名前缀。
返回类型:返回数据类型为int / NULL
例如用自增列查询函数获得表PERSON_TYPE的自增列的种子和增量信息。

SQL> SELECT IDENT_SEED('PERSON.PERSON_TYPE');

LINEID     IDENT_SEED('PERSON.PERSON_TYPE')
---------- --------------------------------
1          1

used time: 1.529(ms). Execute id is 46903.
SQL> SELECT IDENT_INCR('PERSON.PERSON_TYPE');

LINEID     IDENT_INCR('PERSON.PERSON_TYPE')
---------- --------------------------------
1          1

used time: 0.956(ms). Execute id is 46905.

SET IDENTITY_INSERT 属性
设置是否允许将显式值插入表的自增列中。
语法格式
SET IDENTITY_INSERT [< 模式名>.]< 表名> ON | OFF;
参数
1.< 模式名> 指明表所属的模式,缺省为当前模式;
2.< 表名> 指明含有自增列的表名。
使用说明
1.IDENTITY_INSERT属性的默认值为OFF。SET IDENTITY_INSERT 的设置是在执行或运行时进行的。当一个连接结束,IDENTITY_INSERT属性将被自动还原为OFF;
2.DM要求一个会话连接中只有一个表的IDENTITY_INSERT 属性可以设置为ON,当设置一个新的表IDENTITY_INSERT 属性设置为ON时,之前已经设置为ON的表会自动还原为OFF。当一个表的IDENTITY_INSERT 属性被设置为ON时,该表中的自动增量列的值由用户指定。如果插入值大于表的当前标识值(自增列当前值),则DM自动将新插入值作为当前标识值使用,即改变该表的自增列当前值;否则,将不影响该自增列当前值;
3.当设置一个表的IDENTITY_INSERT 属性为OFF时,新插入行中自增列的当前值由系统自动生成,用户将无法指定;
4.自增列一经插入,无法修改;
5.手动插入自增列,除了将IDENTITY_INSERT设置为ON,还要求在插入列表中明确指定待插入的自增列列名。插入方式与非IDENTITY表是完全一样的。如果插入时,既不指定自增列名也不给自增列赋值,则新插入行中自增列的当前值由系统自动生成。

举例说明
例如SET IDENTITY_INSERT 的使用
1) PERSON_TYPE表中的PERSON_TYPEID列是自增列

SQL> select * from person.person_type;

LINEID     PERSON_TYPEID NAME
---------- ------------- --------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             卫生员
6          10            保洁员

6 rows got

used time: 12.207(ms). Execute id is 46927.

2) 在该表中插入数据,自增列的值由系统自动生成。

SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('销售总监');
affect rows 1

used time: 0.884(ms). Execute id is 46930.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('人力资源部经理');
affect rows 1

used time: 0.749(ms). Execute id is 46931.
SQL> commit;
executed successfully
used time: 21.221(ms). Execute id is 46933.
SQL> select * from person.person_type;

LINEID     PERSON_TYPEID NAME
---------- ------------- --------------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             卫生员
6          10            保洁员
7          11            销售总监
8          12            人力资源部经理

8 rows got

used time: 0.445(ms). Execute id is 46934.

3) 当插入数据并且要指定自增列的值时,必须要通过语句将IDENTITY_INSERT设置为ON 时,插入语句中必须指定PERSON_TYPEID 中要插入的列。例如:

SQL> SET IDENTITY_INSERT PERSON.PERSON_TYPE ON;
executed successfully
used time: 32.673(ms). Execute id is 46938.
SQL> INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES( 14, '广告部经理');
affect rows 1

used time: 0.966(ms). Execute id is 46941.
SQL> INSERT INTO PERSON.PERSON_TYPE(PERSON_TYPEID, NAME) VALUES( 15, '财务部经理');
affect rows 1

used time: 0.788(ms). Execute id is 46943.
SQL> commit;
executed successfully
used time: 25.501(ms). Execute id is 46944.
SQL> select * from person.person_type;

LINEID     PERSON_TYPEID NAME
---------- ------------- --------------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             卫生员
6          10            保洁员
7          11            销售总监
8          12            人力资源部经理
9          14            广告部经理
10         15            财务部经理

10 rows got

used time: 0.467(ms). Execute id is 46945.

4) 不允许用户修改自增列的值。

SQL> UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 14 WHERE NAME = '广告部经理';
UPDATE PERSON.PERSON_TYPE SET PERSON_TYPEID = 14 WHERE NAME = '广告部经理';
[-2664]:Error in line: 1
Try to alter identity column [PERSON_TYPEID].
used time: 0.565(ms). Execute id is 0.

5) 还原IDENTITY_INSERT属性。

SQL> SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;
executed successfully
used time: 0.597(ms). Execute id is 46951.

6) 插入后再次查询。注意观察自增列当前值的变化。

SQL> SET IDENTITY_INSERT PERSON.PERSON_TYPE OFF;
executed successfully
used time: 0.597(ms). Execute id is 46951.
SQL> INSERT INTO PERSON.PERSON_TYPE(NAME) VALUES('市场总监');
affect rows 1

used time: 1.013(ms). Execute id is 46954.
SQL> commit;
executed successfully
used time: 16.449(ms). Execute id is 46955.
SQL> select * from person.person_type;

LINEID     PERSON_TYPEID NAME
---------- ------------- --------------
1          1             采购经理
2          2             采购代表
3          3             销售经理
4          4             销售代表
5          9             卫生员
6          10            保洁员
7          11            销售总监
8          12            人力资源部经理
9          14            广告部经理
10         15            财务部经理
11         16            市场总监

11 rows got

used time: 1.000(ms). Execute id is 46956.

DMSQL LIMIT限定条件

在DM中,可以使用限定条件对结果集做出筛选,支持LIMIT子句和ROW_LIMIT子句两种方式。
LIMIT子句
LIMIT子句按顺序选取结果集中某条记录开始的N条记录。语法如下 ::= LIMIT< <记录数>
| < <记录数>,< 记录数>>
| < <记录数> OFFSET < 偏移量>>
>
< 记录数>::=< 整数>
< 偏移量>::=< 整数>

共支持三种方式:
1. LIMIT N:选择前N条记录;
2. LIMIT M,N:选择第M条记录之后的N条记录;
3. LIMIT M OFFSET N:选择第N条记录之后的M条记录。
注意:LIMIT不能与TOP同时出现在查询语句中。
例如查询前2条记录

SQL> SELECT PRODUCTID , NAME FROM PRODUCTION.PRODUCT LIMIT 2;

LINEID     PRODUCTID   NAME
---------- ----------- ------
1          1           红楼梦
2          2           水浒传

used time: 1.236(ms). Execute id is 39690.

例如查询第3,4个登记的产品的编号和名称。

SQL> SELECT PRODUCTID, NAME FROM PRODUCTION.PRODUCT LIMIT 2 OFFSET 2;

LINEID     PRODUCTID   NAME
---------- ----------- --------------------
1          3           老人与海
2          4           射雕英雄传(全四册)

used time: 1.035(ms). Execute id is 39698.

例如查询前第5,6,7个登记的姓名。

SQL> SELECT PERSONID,NAME FROM PERSON.PERSON LIMIT 4,3;

LINEID     PERSONID    NAME
---------- ----------- ----
1          5           孙丽
2          6           黄非
3          7           王菲

used time: 1.137(ms). Execute id is 39701.

ROW_LIMIT子句
用来指定查询结果中,偏移位置的行数或者百分比行数,以便更为灵活地获取查询结果。
语法如下
< ROW_LIMIT子句>::= [OFFSET ] []
::= FETCH < 大小> [PERCENT] < ROW | ROWS >
参数说明:
offset:指定查询返回行的起始偏移。
FIRST | NEXT:FIRST为从偏移为0的位置开始。NEXT, 为从指定的偏移的下一行开始获取结果。只做注释说明的作用,没有实际的限定作用。< 大小>[PERCENT]:指定返回行的行数(无PERCENT)或者百分比(有PERCENT)。ONLY | WITH TIES:指定结果集是否返回额外的行。额外的行是指与最后一行以相同的排序键排序的所有行。ONLY为只返回指定的行数。WITH TIES必须与ORDER BY子句同时出现,如果没有ORDER BY子句,则忽略WITH TIES。

参数限制:
1、offset:必须为数字。offset为负数的话视为0。offset为NULL或者大于或等于所返回的行数,返回0行。offset为小数时,小数部分截断。
2、< 大小>:只能为数字。percent 指定为负数时,视为0%。percent 为NULL,返回0行。如果percent都没有指定,返回1行。
3、不能与一起使用。
4、使用了选项,select列中不能包含有CURRVAL或者NEXTVAL伪列。
5、视图的查询定义中包含有
,这个视图不会增量刷新。

例如查询价格最便宜的50%的商品

SQL> SELECT NAME, NOWPRICE FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE FETCH FIRST 50 PERCENT ROWS ONLY;

LINEID     NAME             NOWPRICE
---------- ---------------- --------
1          老人与海         6.1000
2          突破英文基础词汇 11.1000
3          工作中无小事     11.4000
4          水浒传           14.3000
5          红楼梦           15.2000

used time: 2.689(ms). Execute id is 39731.

例如查询价格第3便宜开始的3条记录

SQL> SELECT NAME, NOWPRICE FROM PRODUCTION.PRODUCT ORDER BY NOWPRICE OFFSET 2 ROWS FETCH FIRST 3 ROWS ONLY;

LINEID     NAME         NOWPRICE
---------- ------------ --------
1          工作中无小事 11.4000
2          水浒传       14.3000
3          红楼梦       15.2000

used time: 2.543(ms). Execute id is 39735.

DMSQL TOP子句

在DM中,可以使用TOP子句来筛选结果。语法如下:
::=TOP
| < ,>
|
PERCENT
|
WITH TIES
|
PERCENT WITH TIES
::=整数(>=0)
参数
1. TOP
选择结果的前n条记录。
2. TOP , 选择第n1条记录之后的n2条记录。
3. TOP PERCENT 表示选择结果的前n%条记录。
4. TOP
PERCENT WITH TIES 表示选择结果的前n%条记录,同时指定结果集可以返回额外的行。额外的行是指与最后一行以相同的排序
键排序的所有行。WITH TIES必须与ORDER BY子句同时出现,如果没有ORDER BY子句,则忽略WITH TIES。

例如查询现价最贵的两种产品的编号和名称。

SQL> SELECT TOP 2 PRODUCTID,NAME FROM PRODUCTION.PRODUCT
2   ORDER BY NOWPRICE DESC;

LINEID     PRODUCTID   NAME
---------- ----------- -------------------
1          10          噼里啪啦丛书(全7册)
2          6           长征

used time: 2.906(ms). Execute id is 39552.

例如查询现价第二贵的产品的编号和名称。

SQL> SELECT TOP 1,1 PRODUCTID,NAME FROM PRODUCTION.PRODUCT
2   ORDER BY NOWPRICE DESC;

LINEID     PRODUCTID   NAME
---------- ----------- ----
1          6           长征

used time: 2.768(ms). Execute id is 39555.

例如查询最新出版日期的70%的产品编号、名称和出版日期。

SQL> SELECT TOP 70 PERCENT WITH TIES PRODUCTID,NAME,PUBLISHTIME FROM PRODUCTION.PRODUCT ORDER BY PUBLISHTIME DESC;

LINEID     PRODUCTID   NAME                               PUBLISHTIME
---------- ----------- ---------------------------------- -----------
1          7           数据结构(C语言版)(附光盘)      2007-03-01
2          5           鲁迅文集(小说、散文、杂文)全两册 2006-09-01
3          6           长征                               2006-09-01
4          3           老人与海                           2006-08-01
5          8           工作中无小事                       2006-01-01
6          4           射雕英雄传(全四册)               2005-12-01
7          2           水浒传                             2005-04-01
8          1           红楼梦                             2005-04-01

8 rows got

used time: 1.776(ms). Execute id is 39590.

Proudly powered by WordPress | Indrajeet by Sus Hill.