准备数据库

创建实例

• 编辑数据库配置文件：${ORACLE_HOME}/assistants/dbca/dbca.rsp，内容如下：
  dbca.rsp
• 使用命令行创建数据库：

su - oracle
dbca -silent -createDatabase -responseFile ${ORACLE_HOME}/assistants/dbca/dbca.rsp

• 数据库根据配置文件的选项，设置了默认密码和Net Service Name如下：

user:sys
passwd:Oracle123
Net Service Name:orcl

• 通过以下命令查看service和数据库状态：

[oracle@rac3 admin]$ srvctl status service -db orcl -verbose
Database orcl does not have services
 
[oracle@rac3 admin]$ srvctl status database -db orcl -verbose
Instance orcl1 is running on node rac3. Instance status: Open.
Instance orcl2 is running on node rac4. Instance status: Open.

• 通过以下语句查看数据库实例：

[oracle@rac3 admin]$ sqlplus sys/Oracle123@rac3/orcl as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 28 17:15:36 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
orcl1
 
[oracle@rac3 admin]$ sqlplus sys/Oracle123@rac4/orcl as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 28 17:15:57 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select instance_name from v$instance;
 
INSTANCE_NAME
----------------
orcl2

创建测试用户

• 通过以下语句查看当前用户：

[oracle@rac3 db_1]$ sqlplus sys/Oracle123@rac4/orcl as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 29 12:18:51 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> select username from all_users;
 
USERNAME
--------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
SYSBACKUP
SYSDG
SYSKM
SYSRAC
OUTLN
XS$NULL
GSMADMIN_INTERNAL
GSMUSER
 
USERNAME
--------------------------------------------------------------------------------
GSMROOTUSER
DIP
REMOTE_SCHEDULER_AGENT
DBSFWUSER
ORACLE_OCM
SYS$UMF
DBSNMP
APPQOSSYS
GSMCATUSER
GGSYS
XDB
 
USERNAME
--------------------------------------------------------------------------------
ANONYMOUS
WMSYS
MDDATA
OJVMSYS
CTXSYS
ORDSYS
ORDDATA
ORDPLUGINS
SI_INFORMTN_SCHEMA
MDSYS
OLAPSYS
 
USERNAME
--------------------------------------------------------------------------------
DVSYS
LBACSYS
DVF

• 通过以下语句创建用户，并授予权限，查看新创建的用户：

[oracle@rac3 db_1]$ sqlplus sys/Oracle123@rac4/orcl as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 29 12:18:51 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle. All rights reserved.
 
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> create user swingbench identified by swingbench123;
SQL> grant connect,resourse,dba to swingbench;
SQL> select username from all_users;

swingbench测试

环境准备

• swingbench软件从官网下载。将其解压后进入软件目录，准备预埋数据，所谓预埋数据实际就是建立描述不同业务场景的数据库表，让后写入数据，用于之后的性能测试，首先为数据库表建立两个表空间，需要动态扩展：

SQL> CREATE TABLESPACE data01 DATAFILE '+DATADG/DATA01.dbf' SIZE 500M autoextend on next 10M maxsize unlimited  EXTENT MANAGEMENT local autoallocate segment space management auto;
SQL> CREATE TEPORARY TABLESPACE temp01 TEMPFILE '+DATADG\TEMP01.dbf'  SIZE 500M autoextend on next 50M maxsize 20480M extent management local;
SQL> alter database default temporary tablespace temp01

• 使用命令行预埋数据，其中-cs参数指定数据库连接方式//{SCAN_IP}/{SERVICE_NAME}，-dba参数指定连接数据库用户，-dbap指定连接数据库的密码，-u指定swingbench测试过程中创建的用户，-p对应用户的密码，-scale指定预埋的数据量，单位是GB:

su root
cd swingbench
./oewizard -s -v -cl -create -bigfile -cs //scan-ip/orcl -dba swingbench -dbap swingbench123 -u oe -p oe -ts OE -df +DATADG/data01 -tc 48 -scale 2

• 在预埋数据过程中，如果出现报错"insufficient privilege"，如下：
  
• 请按照提示进入数据库授予oe用户对应的权限，我的测试中授予了两个权限：

SQL> grant execute on dbms_lock to oe;
SQL> grant select on SYS.V_$PARAMETER to oe;

• 权限授予后通过以下命令重新加载数据并验证表格式：

[root@rac3 bin]# ./sbutil -soe -cs //scan-ip/orcl -soe -u oe -p oe -code
Reloading PL/SQL Packages
Reloaded PL/SQL Package. Completed in : 0:00:00.344
[root@rac3 bin]# ./sbutil -soe -cs //scan-ip/orcl -soe -u oe -p oe -val
The Order Entry Schema appears to be valid.
--------------------------------------------------
|Object Type    |     Valid|   Invalid|   Missing|
--------------------------------------------------
|Table          |        10|         0|         0|
|Index          |        26|         0|         0|
|Sequence       |         5|         0|         0|
|View           |         2|         0|         0|
|Code           |         1|         0|         0|
--------------------------------------------------

• 最后重新指定预埋数据命令行：

./oewizard -s -v -cl -create -bigfile -cs //scan-ip/orcl -dba swingbench -dbap swingbench123 -u oe -p oe -ts OE -df +DATADG/data01 -tc 48 -scale 2

性能测试

• swingbench提供命令行测试性能的工具，同时有性能测试的参考配置文件，执行命令行如下：

cd $SWINGBENCHPATCH/bin
./charbench -rt 8:00 -min 3 -max 5 -dbau  swingbench -dbap  swingbench123 -u oe -p oe -v users,disk,cpu,trans,tpm,tps,resp -cs //scan-ip/orcl -uc 1 -c ${SWINGBENCHPATCH}/configs/SOE_Server_Side_V2.xml | tee swigbench_test.data