企业级规范架构下的数据库搭建【ORACLE单实例篇】

最新推荐文章于 2022-03-22 15:29:21 发布

Mr.阿门

最新推荐文章于 2022-03-22 15:29:21 发布

阅读量318

点赞数 1

分类专栏：环境搭建文章标签：数据库

本文链接：https://blog.csdn.net/wc3083/article/details/107297653

版权

环境搭建专栏收录该内容

2 篇文章 0 订阅

订阅专栏

ORACLE单实例规范搭建步骤

注意：命令行头标识如下：
# 表示root用户执行
$ 表示oracle用户执行
SQL> 表示sqlplus命令行执行
RMAN> 表示RMAN命令行执行

1、规范信息：

内容	规范
版本选择	Linux 7 ORACLE 11.2.0.4.0
主机配置	8~32核 32~512G
卷规划	软件卷：/u01 数据卷：/oradata/$ORACLE_SID 日志卷：/orafra/$ORACLE_SID LVM管理，且不同实例的软件卷和数据卷需分开
实例名要求	小写，不超过8个字符。单实例下尽量不出现数字结尾
数据库目录	/oradata/$ORACLE_SID
日志目录	/oradata/$ORACLE_SID 归档日志：/orafra/$ORACLE_SID/flash_recovery_area online/standby redo日志：/orafra/$ORACLE_SID/redolog online redolog配置：最少6组，每组大小一致（500M~2048M，保证平均每小时日志切换次数尽量不超过10次） standby redolog配置：搭建DG时使用，比online redolog多一组，大小保持一致
软件目录	ORACLE_BASE：/u01/app/oracle ORACLE_HOME：/u01/app/oracle/product/11.2.0.4.0 用于存放相应版本的软件
工具目录	工具脚本目录：$ORACLE_BASE/tools/script 用于存放各类工具以及脚本，比如： crontab脚本、dbs等工具日志目录：$ORACLE_BASE/tools/log 用于存放工具生成的日志
域名规划	生产：<dbname>.poracle..com.cn 灾备：<dbname>.soracle..com.cn 测试：<dbname>.orastg.*.com.cn
端口号规划	生产/备库/灾备：1531-1591 测试：1631-1691

注：下面搭建案例的实例名为：oratest

2、划盘

2.1 查看硬盘和分区信息

# fdisk -l

2.2 找到新盘，创建pv

# pvcreate /dev/vdb

2.3 创建vg

# vgcreate vg01 /dev/vdb

2.4 在前述新建的vg上创建10G的swap的lv分区，并初始化启动

# lvcreate -L 10240 -n lv_swap vg01
# mkswap /dev/mapper/vg01-lv_swap
# swapon /dev/mapper/vg01-lv_swap

2.5 创建50G大小的软件卷lv

# lvcreate -L 50000 -n lv_rdbms vg01

2.6 创建适量空间大小的fra卷lv（保证日志能存放3-7天为佳）

# lvcreate -L 50000 -n lv_fra vg01

2.7 将剩余空间分配给data卷

# lvcreate -l +100%Free -n lv_data vg01

2.8 格式化创建对应lv的文件系统

# mkfs.xfs /dev/mapper/vg01-lv_fra
# mkfs.xfs /dev/mapper/vg01-lv_rdbms
# mkfs.xfs /dev/mapper/vg01-lv_data

2.9 创建挂载点

# mkdir -p /orafra/oratest
# mkdir -p /oradata/oratest
# mkdir /u01

2.10 将相关卷写入/etc/fstab后mount -a

# echo "/dev/mapper/vg01-lv_swap         swap                   swap      defaults      0 0" >> /etc/fstab
# echo "/dev/mapper/vg01-lv_rdbms        /u01                   xfs       defaults      0 0" >> /etc/fstab
# echo "/dev/mapper/vg01-lv_fra          /orafra/oratest        xfs       defaults      0 0" >> /etc/fstab
# echo "/dev/mapper/vg_data-lv_data      /oradata/oratest       xfs       defaults      0 0" >> /etc/fstab

# mount -a

3、关闭selinux

将SELINUX改为disabled

# vi /etc/sysconfig/selinux 
SELINUX=disabled

注意别改错参数到SELINUXTYPE，曾经就有学生改到了这个参数上，第二天上课虚拟机起不来了。

4、调整系统参数

# echo "session    required     /lib64/security/pam_limits.so" >>/etc/pam.d/login
# echo "oracle          soft     nproc            2047" >>/etc/security/limits.conf
# echo "oracle          hard     nproc            16384" >>/etc/security/limits.conf
# echo "oracle          soft     nofile           1024" >>/etc/security/limits.conf
# echo "oracle          hard     nofile           65536" >>/etc/security/limits.conf
# echo "oracle          soft     memlock          60397977" >>/etc/security/limits.conf
# echo "oracle          hard     memlock          60397977" >>/etc/security/limits.conf
# echo "fs.aio-max-nr = 1048576" >>/etc/sysctl.conf
# echo "fs.file-max = 6815744" >>/etc/sysctl.conf
# echo "kernel.shmmni = 7546062" >>/etc/sysctl.conf
# echo "####内存85%，getconf PAGE_SIZE" >>/etc/sysctl.conf
# shmall_mem=`cat /proc/meminfo |grep MemTotal|awk '{printf("%.0f\n",$2/4*0.85)}'`
# echo "kernel.shmall = $shmall_mem" >>/etc/sysctl.conf
# echo "#####比sga大，一般内存70%" >>/etc/sysctl.conf
# shmax_mem=`cat /proc/meminfo |grep MemTotal|awk '{printf("%.0f\n",$2*1024*0.7)}'`
# echo "kernel.shmmax = $shmax_mem" >>/etc/sysctl.conf
# echo "kernel.sem = 250 32000 100 128" >>/etc/sysctl.conf
# echo "net.ipv4.ip_local_port_range = 9000 65500" >>/etc/sysctl.conf
# echo "net.core.rmem_default = 262144" >>/etc/sysctl.conf
# echo "net.core.rmem_max = 4194304" >>/etc/sysctl.conf
# echo "net.core.wmem_default = 262144" >>/etc/sysctl.conf
# echo "net.core.wmem_max = 1048576" >>/etc/sysctl.conf
# sysctl -p

5、检查/etc/hosts

确定hostname映射到了/etc/hosts文件中，否则请添加

# cat /etc/hosts

6、系统环境变量配置

# echo 'if [ $USER = "oracle" ]; then' >>/etc/profile
# echo 'if [ $SHELL = "/bin/ksh" ]; then' >>/etc/profile
# echo 'ulimit -p 16384' >>/etc/profile
# echo 'ulimit -n 65536' >>/etc/profile
# echo 'else' >>/etc/profile
# echo 'ulimit -u 16384 -n 65536' >>/etc/profile
# echo 'fi' >>/etc/profile
# echo 'fi' >>/etc/profile

7、创建用户和组

创建oracle用户和组，规范gid和uid便于避免RAC架构或者迁移后出现特殊异常

# groupadd -g 3001 oinstall
# groupadd -g 3002 dba
# groupadd -g 3003 oper
# useradd -g oinstall -G dba,oper oracle -u 3304

8、调整权限

# chown oracle:oinstall /orafra
# chown oracle:oinstall /oradata
# chown oracle:oinstall /u01

9、yum配置

# yum -y install binutils compat-libcap1 compat-libstdc* gcc gcc-c++* glibc glibc-devel ksh libgcc libstdc* libaio libaio-devel make elfutils-libelf-devel sysstat ksh

10、配置oracle用户的环境变量

# su - oracle
$ vi .db_profile
#!/bin/sh
cat /u01/app/oracle/tools/script/db_info.txt
if [ ! "$1" = "" ];then
    SID=$1
else
    echo -n "Please Enter database sid:";
    read SID;
fi
if [ "$SID" = "" ]; then
    echo "you havn't Enter your db name!";
    unset ORACLE_SID
else if awk -F: '{print $1}' /u01/app/oracle/tools/script/db_info.txt|grep -w $SID
then
export ORACLE_SID=$SID;
export ORACLE_HOME=`awk -F: '{if ($1 == "'$ORACLE_SID'") {print $2; exit}}' /u01/app/oracle/tools/script/db_info.txt`
export NLS_LANG=`awk -F: '{if ($1 == "'$ORACLE_SID'") {print $3; exit}}' /u01/app/oracle/tools/script/db_info.txt`
else
  echo "The database name not exist in /u01/app/oracle/tools/script/db_info.txt";
  unset ORACLE_SID;
fi
fi
umask 022
export TERM=vt100
export EDITOR=vi
export ORACLE_BASE=/u01/app/oracle
export PATH=$ORACLE_HOME/bin:$HOME/sh:/sbin:/usr/sbin:/opt/VRTS/bin:/opt/VRTSvcs/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:$PATH
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PS1="`hostname`:$ORACLE_SID > "
export ORACLE_DOC=$ORACLE_BASE/doc
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$GGS_HOME:$LD_LIBRARY_PATH
export PATH=$GGS_HOME:$PATH
export TMP=/tmp
export ORA_ENCRYPT_LOGIN=true
export SHELL_PATH=/u01/app/oracle/tools/script/

11、配置数据库实例信息文件

$ mkdir -p /u01/app/oracle/tools/script
$ mkdir -p /u01/app/oracle/tools/log
$ vi /u01/app/oracle/tools/script/db_info.txt
oratest:/u01/app/oracle/product/11.2.0.4.0:AMERICAN_AMERICA.AL32UTF8

12、上传并解压数据库软件

$ mkdir /u01/software
$ cd /u01/software
$ unzip p13390677_112040_Linux-x86-64_1of7.zip
$ unzip p13390677_112040_Linux-x86-64_2of7.zip
$ cd database

13、静默安装软件

注意根据实际情况调整ORACLE_HOME和ORACLE_BASE参数

$ ./runInstaller -silent oracle.install.option=INSTALL_DB_SWONLY SELECTED_LANGUAGES=en UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/u01/app/oracle/oraInventory ORACLE_HOME=/u01/app/oracle/product/11.2.0.4.0 ORACLE_BASE=/u01/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.isCustomInstall=false oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=oper DECLINE_SECURITY_UPDATES=true SECURITY_UPDATES_VIA_MYORACLESUPPORT=false oracle.installer.autoupdates.option=SKIP_UPDATES

14、按要求执行root脚本

The installation of Oracle Database 11g was successful.
Please check '/u01/app/oracle/oraInventory/logs/silentInstall2020-07-14_09-55-43PM.log' for more details.

As a root user, execute the following script(s):
    1. /u01/app/oracle/oraInventory/orainstRoot.sh
    2. /u01/app/oracle/product/11.2.0.4.0/root.sh


Successfully Setup Software.

15、切换环境变量并dbca建库

注意根据实际情况调整sys密码，内存大小，字符集参数

$ . .db_profile oratest
$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oratest -sid oratest -sysPassword Test2020 -systemPassword Test2020 -responseFile NO_VALUE -datafileDestination /oradata -redoLogFileSize 500 -recoveryAreaDestination /orafra -storageType FS -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 -listeners no -memoryPercentage 58 -totalMemory 2000 -databaseType OLTP -emConfiguration NONE
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oratest/oratest.log" for further details.

16、数据库校验

$ sqlplus / as sysdba
SQL> set line 200
SQL> select name,database_role,open_mode,logins,value CHARACTERSET,startup_time from v$database,v$instance,v$nls_parameters where parameter='NLS_CHARACTERSET';
NAME       DATABASE_ROLE    OPEN_MODE       LOGINS     CHARACTERSET    STARTUP_TIME
---------  ---------------  --------------  ---------- --------------- --------------------
ORATEST    PRIMARY          READ WRITE      ALLOWED    AL32UTF8        2020-07-14 22:23:01

17、配置监听

$ cd $ORACLE_HOME/network/admin
$ vi listener.ora
oratest =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.33)(PORT = 1531))
    )
  )
  
ADR_BASE_oratest = /u01/app/oracle

$ sqlplus /as sysdba
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.133.33)(PORT=1531))))' scope=both;
SQL> exit;

$ lsnrctl start oratest
$ lsnrctl status oratest

18、配置sqlnet.ora

$ vi sqlnet.ora
SQLNET.EXPIRE_TIME=10

19、数据库初始化

这里才是重点，一定要有规范化以及主动预防故障的思想，没有经过初始化的数据库都是不合格的。

$ sqlplus / as sysdba
SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>129600,interval=>15);
SQL> alter profile default limit password_life_time unlimited;
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS UNLIMITED;
SQL> alter system set audit_trail=DB scope=spfile;
SQL> alter database force logging;
SQL> alter system set log_archive_format='oratest%s_%t_%r.arc' scope=spfile;
SQL> alter system set log_buffer=100663296 scope=spfile;
SQL> alter system set archive_lag_target=900 scope=both;
SQL> alter system set DB_SECUREFILE='ALWAYS' scope=both;
SQL> alter system set deferred_segment_creation=false scope=both;
SQL> alter system set fast_start_parallel_rollback=false scope=both;
SQL> alter system set fast_start_mttr_target=300 scope=both;
SQL> alter system set filesystemio_options='SETALL' scope=spfile;
SQL> alter system set parallel_max_servers=32;
SQL> alter system set session_cached_cursors=300 scope=spfile;
SQL> alter system set undo_retention=3600;
SQL> alter system set log_archive_max_processes=10;
SQL> alter system set log_checkpoints_to_alert=true scope=both;
SQL> alter system set optimizer_secure_view_merging=false scope=spfile;
SQL> alter system set max_dump_file_size=552428800;
SQL> alter system set "_serial_direct_read"=NEVER scope=both;
SQL> alter system set "_use_adaptive_log_file_sync"=false scope=both;
SQL> alter system set "_kttext_warning"=1 scope=both;
SQL> alter system set standby_file_management=auto;
SQL> alter system set processes=2000 scope=spfile;
SQL> alter system set db_files=1200 scope=spfile;

20、配置FRA

SQL> show parameter db_recovery_file_dest
NAME                              TYPE             VALUE
------------------------------    --------------   ------------------------------
db_recovery_file_dest             string           /orafra
db_recovery_file_dest_size        big integer      8232M

-----大小取值为“db_recovery_file_dest”参数对应盘空间容量的80%
SQL> alter system set db_recovery_file_dest_size=40G;

21、重启并启动归档模式

SQL> alter system checkpoint;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system switch logfile;

22、调整redolog

$ mkdir -p /orafra/$ORACLE_SID/redolog
$ cd /orafra/$ORACLE_SID/redolog
$ pwd                                                                  
$ sqlplus / as sysdba
SQL> alter database add logfile group 4 ('/orafra/oratest/redolog/redo04') size 500m;
SQL> alter database add logfile group 5 ('/orafra/oratest/redolog/redo05') size 500m;
SQL> alter database add logfile group 6 ('/orafra/oratest/redolog/redo06') size 500m;

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system checkpoint;

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 1 ('/orafra/oratest/redolog/redo01') size 500m;
SQL> alter database add logfile group 2 ('/orafra/oratest/redolog/redo02') size 500m;
SQL> alter database add logfile group 3 ('/orafra/oratest/redolog/redo03') size 500m;

SQL> set line 200
SQL> col member for a50
SOL> select group#,bytes/1024/1024,status from v$log;
--确认有6组，500M一个，status不为UNUSED（如果为unused则执行下列语句直到status改变）
SQL> alter system switch logfile;

SQL> select * from v$logfile;
--确认有6组online redolog，路径正确

23、调整sqlplus格式

日常运维登录sqlplus时，很多情况下都会遇到这些问题：
1、不确定当前登录环境是否正确，生怕登录到错误的环境错误的角色上，所以还得再次查询数据库的角色和dbname信息
2、每次都得set line/col等操作对格式进行优化以便查看执行结果。

基于这个原因，需要对$ORACLE_HOME/sqlplus/admin/glogin.sql文件进行调整：
第一是起到一个提示作用，自动提示出角色，实例信息。进一步减少出现操作到错误环境的情况发生。
第二也是方便了日常运维操作，无需每次登陆都需要查询角色并进行常规语句的set line/col操作。

$ echo "set termout off;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "define gname=idle;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "column global_name new_value gname" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo '--set sqlprompt "_USER@ _CONNECT_IDENTIFIER _PRIVILEGE>"' >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "select lower(user) ||'@'|| substr(instance_name,1,decode(dot,0,length(instance_name),dot-1))||'-'||db_role global_name" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "from (select instance_name,instr(instance_name,'.') dot from v\$instance) a," >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "(select trim(lower(database_role)) db_role from v\$database) b;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "set sqlprompt '&gname> ';" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "set line 300 time on termout on;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "col file_name for a55;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "col member for a55;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql
$ echo "col name for a40;" >>$ORACLE_HOME/sqlplus/admin/glogin.sql

修改前登陆sqlplus：

SQL>

修改后登陆sqlplus：

23:08:43 sys@oratest-primary>

24、配置crontab备份/清理归档日志和trace日志

略。。。

25、根据需求配置rman归档日志清理策略

$ rman target/
-- 备份X次则自动删除
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

-- 应用到（所有）备库则自动删除
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

-- 传输到（所有）备库则自动删除
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO STANDBY;
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO SHIPPED TO ALL STANDBY;

-- 取消自动删除策略
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;