云计算

阿里云9折折扣码

操作系统使用 CentOS 7.6 64位，目前对于oracle没有任何必要使用32位操作系统了！！

磁盘在40GB系统盘的基础上增加一块ESSD云盘大小40GB，作为存放oracle数据库的基础配置。并启用每日自动备份！

网络设置使用默认配置，用户实际使用时：

若应用服务器位于阿里云同机房，一般可以直接使用内网连接，不需要太大的公网带宽
若应用服务器不在阿里云同机房，需要使用公网连接，则需要提高公网带宽

无需启动3389端口

安全组使用默认配置，后续需要为ORACLE监听配置安全组网络端口。

最终配置报价时317元/每月！

最后确认订单，服务器就绪后，ssh远程登陆。

首先划分磁盘并测试ESSD的IO, 第一块ESSD的路径一般为 /dev/vdb，在vdb上划分1个分区，并创建XFS文件系统，挂在到 /d01目录下

fdisk /dev/vdb



Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x5d899fda.

Command (m for help): p

Disk /dev/vdb: 42.9 GB, 42949672960 bytes, 83886080 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x5d899fda

   Device Boot      Start         End      Blocks   Id  System

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-83886079, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-83886079, default 83886079):
Using default value 83886079
Partition 1 of type Linux and of size 40 GiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.



partprobe /dev/vdb

[root@iZuf6fz9mqmeexkh25fbrbZ ~]# ls -l /dev/vdb1
brw-rw---- 1 root disk 253, 17 Sep 11 12:51 /dev/vdb1


 mkfs.xfs /dev/vdb1
 mkdir /d01
 mount /dev/vdb1 /d01
 
 
[root@iZuf6fz9mqmeexkh25fbrbZ ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G   12G   27G  31% /
devtmpfs        3.8G     0  3.8G   0% /dev
tmpfs           3.8G     0  3.8G   0% /dev/shm
tmpfs           3.8G  636K  3.8G   1% /run
tmpfs           3.8G     0  3.8G   0% /sys/fs/cgroup
tmpfs           768M     0  768M   0% /run/user/0
/dev/vdb1        40G   33M   40G   1% /d01


chown oracle /d01
echo "/dev/vdb1  /d01  xfs" >> /etc/fstab

测试一下ESSD的IO :

yum install fio


cd /d01 

fio --randrepeat=1 --ioengine=libaio --direct=1 --gtod_reduce=1 --name=test --filename=test --bs=4k --iodepth=64 --size=4G --readwrite=randrw --rwmixread=75
test: (g=0): rw=randrw, bs=(R) 4096B-4096B, (W) 4096B-4096B, (T) 4096B-4096B, ioengine=libaio, iodepth=64


fio-3.1
Starting 1 process
test: Laying out IO file (1 file / 4096MiB)
Jobs: 1 (f=1): [m(1)][100.0%][r=11.1MiB/s,w=3744KiB/s][r=2848,w=936 IOPS][eta 00m:00s]
test: (groupid=0, jobs=1): err= 0: pid=21835: Wed Sep 11 13:48:03 2019
   read: IOPS=2847, BW=11.1MiB/s (11.7MB/s)(3070MiB/275992msec)
   bw (  KiB/s): min=10560, max=12232, per=100.00%, avg=11393.26, stdev=241.24, samples=551
   iops        : min= 2640, max= 3058, avg=2848.32, stdev=60.34, samples=551
  write: IOPS=951, BW=3807KiB/s (3898kB/s)(1026MiB/275992msec)
   bw (  KiB/s): min= 3344, max= 4256, per=100.00%, avg=3807.62, stdev=165.68, samples=551
   iops        : min=  836, max= 1064, avg=951.90, stdev=41.42, samples=551
  cpu          : usr=0.59%, sys=2.51%, ctx=951009, majf=0, minf=22
  IO depths    : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=0.1%, >=64=100.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.1%, >=64=0.0%
     issued rwt: total=785920,262656,0, short=0,0,0, dropped=0,0,0
     latency   : target=0, window=0, percentile=100.00%, depth=64

Run status group 0 (all jobs):
   READ: bw=11.1MiB/s (11.7MB/s), 11.1MiB/s-11.1MiB/s (11.7MB/s-11.7MB/s), io=3070MiB (3219MB), run=275992-275992msec
  WRITE: bw=3807KiB/s (3898kB/s), 3807KiB/s-3807KiB/s (3898kB/s-3898kB/s), io=1026MiB (1076MB), run=275992-275992msec

Disk stats (read/write):
  vdb: ios=785887/263160, merge=0/3, ticks=13264955/4397142, in_queue=15400027, util=87.17%

可以看到单块阿里云ESSD的随机读写 IOPS ,read: IOPS=2847 write: IOPS=951 还是不错的。

开始安装oracle 19c , 首先上传介质到服务器/root目录

使用浏览器下载以下2个介质并上传到服务器目录

https://download.oracle.com/otn/linux/oracle19c/190000/oracle-database-ee-19c-1.0-1.x86_64.rpm
https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm


su - root 
yum -y install git 
git clone https://github.com/macleanliu/ora-easy-deploy
yum -y localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
rpm -ivh oracle-database-ee-19c-1.0-1.x86_64.rpm
bash ora-easy-deploy/create_db.sh

bash ora-easy-deploy/create_db.sh ，执行该脚本输入数据库名，数据库目录和SYS/SYSTEM密码等，后自动建库。

安装完成后,执行：

#设置ORACLE OS账户密码
su - root
passwd oracle                     
su - oracle

lsnrctl start 
sql
alter system register;
alter system set "_optimizer_aggr_groupby_elim"=false;
alter system set "_drop_stat_segment"=1;
alter system set "_common_data_view_enabled"=false;
alter system set optimizer_adaptive_features=false;
alter system set "_optimizer_dsdir_usage_control"=0; 
alter system set "_enable_automatic_sqltune"=false scope=both; 
alter system set "_serial_direct_read"=false;
alter system set "_nlj_batching_enabled" = 0; 
alter system set "_optimizer_undo_cost_change" = '10.2.0.4';
alter system set "_optimizer_null_aware_antijoin" = false;
alter system set "_optimizer_extend_jppd_view_types" = false;
alter system set "_replace_virtual_columns" = false;
alter system set "_first_k_rows_dynamic_proration" = false;
alter system set "_bloom_pruning_enabled" = false;
alter system set "_optimizer_multi_level_push_pred" = false;
alter system set "_optim_peek_user_binds"=false; 
alter system set client_result_cache_size=0 scope=spfile;
alter system set result_cache_mode=MANUAL;
alter system set "_diag_hm_rc_enabled"=false; 
alter system set audit_trail=none scope=spfile;
alter system set "_memory_imm_mode_without_autosga"=false scope=both; 
alter system set "_enable_shared_pool_durations"=false scope=spfile;
alter system set deferred_segment_creation=false; 
alter system set "_datafile_write_errors_crash_instance"=false ;
alter system set "_fairness_threshold"=6 scope=spfile;
alter system set "_gc_read_mostly_locking"=false scope=spfile;
alter system set "_gc_policy_time"=0 scope=spfile;
alter system set "_gc_defer_time"=3  scope=spfile;
alter system set "parallel_force_local"=false;
alter system set "_gc_bypass_readers"=false;
alter system set "_row_cr"=false;
alter system set ddl_lock_timeout=0;
alter system set "_gby_hash_aggregation_enabled"=False scope=spfile;
alter system set "_cleanup_rollback_entries"=400 scope=spfile;
alter system set "_dbms_sql_security_level"=0                          scope=spfile;
alter system set "_bloom_pruning_enabled"=False                        scope=spfile;
alter system set "_simple_view_merging"=True                           scope=spfile;
alter system set "_enable_NUMA_optimization"=FALSE                     scope=spfile;
alter system set "_fix_control"='9344709:OFF'                           scope=spfile;
alter system set "_px_use_large_pool"=True                              scope=spfile;
alter system set "_mv_refresh_use_hash_sj"=FALSE                      scope=spfile;
alter system set "_mv_refresh_use_stats"=True                          scope=spfile;
alter system set "_like_with_bind_as_equality"=TRUE                    scope=spfile;
alter system set optimizer_secure_view_merging=false                   scope=spfile;
alter system set optimizer_capture_sql_plan_baselines=False            scope=spfile;
alter system set event="10949 TRACE NAME CONTEXT FOREVER:28401 trace name context forever, level 1"  scope=spfile;
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name =>  'auto optimizer stats collection', operation => NULL,window_name => NULL);
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name =>  'auto space advisor', operation => NULL,window_name => NULL);
exec  DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL,window_name => NULL);
commit;
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');


// to disable histogram , you set bucket size to 1

exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );
commit;



// disable 19c automatic indexing feature 

alter system set "_optimizer_auto_index_allow"=NEVER scope=spfile;
alter system set "_optimizer_use_auto_indexes"=OFF scope=spfile;

shutdown immediate;
startup ;

完成配置。

以上完成了基础配置，但外网客户端还是无法访问oracle服务器监听的，例如使用tnsping工具ping服务器监听，或sqlplus均无法登陆

 tnsping 47.XX.XX.XX

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-9月 -2019 10:35:17

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

已使用的参数文件:
sqlnet.ora

已使用 HOSTNAME 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=47.XX.XX.XX)(PORT=1521)))
TNS-12535: TNS: 操作超时

sqlplus system/oracle@47.XX.XX.XX:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 9月 12 10:39:07 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12170: TNS: 连接超时

我们在阿里云控制台，点选ECS服务器，更多=》网络和安全组=》安全组配置=》配置规则

点击添加安全组规则：

选择协议类型，ORACLE 1521 ，如果你要使用非默认ORACLE端口，即不使用1521端口，那么需要在端口范围内自行选择端口；这里我们使用默认端口，因为可以指定默认端口允许的外网IP，所以一般使用默认端口即可；授权对象中输入应用服务器的外网IP 。

点击确定后1分钟后，可以尝试登陆：

tnsping 47.XX.XX.XX

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 12-9月 -2019 10:46:46

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

已使用的参数文件:
sqlnet.ora

已使用 HOSTNAME 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=47.XX.XX.XX)(PORT=1521)))
OK (10 毫秒)


sqlplus system/oracle@47.XX.XX.XX:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on 星期四 9月 12 10:47:33 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

上次成功登录时间: 星期四 9月  12 2019 10:33:17 +08:00

连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

可以看到登陆成功，这样我们就可以愉快的使用阿里云上的 ORACLE 19c了！！

阿里云 ECS 快速安装Oracle 19c