systemctl  stop  firewalld.service

systemctl disable firewalld.service

systemctl status firewalld.service

# add by xieyunhui 2020-11-10

session   required     pam_limits.so

systemctl disable avahi-daemon.socket

[cdrom]

name=Local cdrom

baseurl=file:///mnt/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release

# kernel parameters for 19C  installation,add by xieyunhui 2019-12-19

fs.file-max = 6815744

kernel.sem = 250 32000 100 128

kernel.shmmni = 4096

kernel.shmall = 1073741824

kernel.shmmax = 4398046511104

kernel.panic_on_oops = 1

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

net.ipv4.conf.all.rp_filter = 2

net.ipv4.conf.default.rp_filter =  2

fs.aio-max-nr = 1048576

net.ipv4.ip_local_port_range =  9000 65500

net.ipv4.ipfrag_high_thresh =  16777216

net.ipv4.ipfrag_low_thresh =  15728640

vm.min_free_kbytes=409600

vm.vfs_cache_pressure=200

vm.max_map_count=262144

vm.swappiness=40

vm.nr_hugepages  = 38788

vm.hugetlb_shm_group=54321

[root@dw01 ~]# more  /proc/sys/vm/hugetlb_shm_group

0

[root@dw01 ~]# id oracle

uid=54321(oracle)  gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(asmadmin),54324(asmdba),54325(asmoper)

本环境中数据库启动正常,大页也可以正常使用,但是在系统日志中有类似Mar 11  12:12:33 i-q2ghx82t kernel: oracle (3677): Using mlock ulimits for  SHM_HUGETLB is deprecated 这样的告警.通过分析,是由于少配置了hugetlb_shm_group参数导致(vm.hugetlb_shm_group 参数设置为有权使用 HugePages  的操作系统组。默认情况下，此参数设置为 0，从而允许所有组使用 HugePages。可以将此参数设置为 Oracle 数据库进程所属的操作系统组，如  oinstall),在本系统中在sysctl.conf中增加vm.hugetlb_shm_group=54321,然后重启系统(测试中,如果只是重启数据库,非系统重启后第一次重启数据库,不会出现该告警),系统日志没有出现相关告警.

# add by xieyunhui 2019-12-19

oracle    soft   nofile    65536

oracle    hard   nofile    65536

oracle    soft   nproc    16384

oracle    hard   nproc    16384

oracle    soft   stack    10240

oracle    hard   stack    32768

oracle    hard   memlock    134217728

oracle    soft   memlock    134217728

grid      soft   nofile    1024

grid      hard   nofile    65536

grid      soft   nproc    16384

grid      hard   nproc    16384

grid      soft   stack    10240

grid      hard   stack    32768

grid      hard   memlock    134217728

grid      soft   memlock    134217728

 ll  /etc/localtime
 lrwxrwxrwx. 1 root root 35 Dec 12 09:50  /etc/localtime -> ../usr/share/zoneinfo/Asia/Shanghai

 [oracle@hs-ylb-DB-yc ~]$ date -R
 Mon, 27 Apr 2020 10:53:11 +0800

[root@dw02 ~]# cat /etc/chrony.conf |grep  -v ^#|grep -v ^$

server 10.69.89.1

maxchange 500 0 -1

maxslewrate 500

driftfile /var/lib/chrony/drift

rtcsync

logdir /var/log/chrony

cups

打印服务

postfix

邮件服务

pcscd

smart卡登录服务

smartd

磁盘监控服务，对于做过RAID的磁盘无效

alsasound

声卡服务

iscsitarget

iscsi target服务

smb

与windows互访问的文件服务

acpid

acpi高级电源管理服务

cpuspeed

Cpu节能工具

iptables

ipv4防火墙服务

ip6tables

ipv6防火墙服务

firewall

防火墙服务（RHEL7上才有的）

systemctl disable firewalld

systemctl disable avahi-daemon

systemctl disable bluetooth

systemctl disable cpuspeed

systemctl disable cups

systemctl disable firstboot

systemctl disable ip6tables

systemctl disable iptables

systemctl disable pcmcia

systemctl stop autofs

systemctl stop nfslock

systemctl stop rpcidmapd

systemctl stop rpcgssd

systemctl stop ntpd

systemctl stop bluetooth.service

systemctl stop cups.path

systemctl stop cups.socket

systemctl stop postfix.service

systemctl stop rpcbind.service

systemctl stop rpcbind.socket

systemctl stop  NetworkManager-dispatcher.service

systemctl stop  dbus-org.freedesktop.NetworkManager.service

systemctl stop abrt-ccpp.service

systemctl stop cups.service

systemctl stop libvirtd

systemctl disable autofs

systemctl disable nfslock

systemctl disable rpcidmapd

systemctl disable rpcgssd

systemctl disable ntpd

systemctl disable bluetooth.service

systemctl disable cups.path

systemctl disable cups.socket

systemctl disable postfix.service

systemctl disable rpcbind.service

systemctl disable rpcbind.socket

systemctl disable  NetworkManager-dispatcher.service

systemctl disable  dbus-org.freedesktop.NetworkManager.service

systemctl disable abrt-ccpp.service

systemctl disable cups.service

systemctl disable  libvirtd

kdump

内存转储服务

cron

定时服务

auditd

审计服务

rsyslog

日志服务

sysstat

系统监控

sshd

安全shell服务

irqbalance

中断平衡服务（低时延应用需要关闭)

network

网络服务

groupadd -g 54321 oinstall

groupadd -g 54322 dba

groupadd -g 54323 asmadmin

groupadd -g 54324 asmdba

groupadd -g 54325 asmoper

useradd -u 54321 -g oinstall -G  dba,asmadmin,asmdba,asmoper oracle

echo "zAq1XsW2CdE3VfR4"|passwd  --stdin oracle

useradd -u 54322 -g oinstall -G  asmadmin,asmdba,asmoper,dba grid

echo "Oracle_123"|passwd  --stdin grid

mkdir -p /u01/app/19c/grid

mkdir -p /u01/app/grid

mkdir -p  /u01/app/oracle/product/19c/db_1/

chown -R grid:oinstall /u01

chown oracle:oinstall /u01/app/oracle

chmod -R 775 /u01/

ORACLE_SID=+ASM1

ORACLE_BASE=/u01/app/grid

ORACLE_HOME=/u01/app/19c/grid

PATH=$ORACLE_HOME/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export ORACLE_SID ORACLE_BASE ORACLE_HOME  PATH LD_LIBRARY_PATH NLS_LANG

stty erase ^H

alias obase='cd $ORACLE_BASE'

alias ohome='cd $ORACLE_HOME'

alias tns='cd $ORACLE_HOME/network/admin'

alias envo='env | grep ORACLE'

ORACLE_SID=jzdb1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/19c/db_1

PATH=$ORACLE_HOME/bin:$PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

export ORACLE_SID ORACLE_BASE ORACLE_HOME  PATH LD_LIBRARY_PATH NLS_LANG

stty erase ^H

alias obase='cd $ORACLE_BASE'

alias ohome='cd $ORACLE_HOME'

alias tns='cd $ORACLE_HOME/network/admin'

alias envo='env | grep ORACLE'

DG_OCR

3块5G

DG_FRA

300G

DG_DATA

6块500G

chown grid:oinstall /dev/mapper/mpath*

chown grid.asmadmin /dev/mapper/mpatha

chown grid.asmadmin /dev/mapper/mpathb

chown grid.asmadmin /dev/mapper/mpathc

Verifying /dev/shm mounted as temporary  file system ...FAILED

/dev/shm 提示检查失败，值过小，后面启动实例时可能会失败

Verifying /dev/shm mounted as temporary  file system ...FAILED

rac2: PRVE-0426 : The size of in-memory  file system mounted as /dev/shm is

       "871" megabytes which is less than the required size of  "2048" megabytes

       on node "rac2"

解决方案：增加操作内存，增大shm

[root@rac2 ~]# mount -o  remount,size=2100M /dev/shm

Cluster name长度必须小于15个字符

[grid@dw01 ~]$ crsctl stat res ora.crf  -init

NAME=ora.crf

TYPE=ora.crf.type

TARGET=ONLINE

STATE=ONLINE on dw01

[grid@dw02 ~]$ crsctl stat  res ora.crf -init -p|grep AUTO

AUTO_START=always

[root@dw01 db]#  /u01/app/19c/grid/bin/crsctl modify resource "ora.crf" -attr  "AUTO_START=0" -init

[grid@dw01 ~]$ crsctl stat res ora.crf  -init -p|grep AUTO

AUTO_START=0

+0x264):  undefined reference to `sslss_async_call_cnt_'

skswr.c:(text.hot+0x29e):  undefined reference to `sslss_async_call_cnt_'

skswr.c:(text.hot+0x2ac):  undefined reference to `sslss_async_call_cnt_'

skswr.c:(text.hot+0x2b8):  undefined reference to `sslss_sig_pending_'

skswr.c:(text.hot+0x2cd):  undefined reference to `sslss_sigset_raise_signals'

INFO:  

make:  *** [/u01/app/oracle/product/19c/db_1/rdbms/lib/oracle] Error 1

INFO:  End output from spawned process.

INFO:  ----------------------------------

SEVERE:  oracle.sysman.oii.oiil.OiilActionException: Error in invoking target 'irman  ioracle idrdactl idrdalsnr idrdaproc rac_on dnfs_on' of makefile  '/u01/app/oracle/product/19c/db_1/rdbms/lib/ins_rdbms.mk'. See  '/u01/app/oracle/product/19c/db_1/install/relinkActions2020-09-27_11-25-24AM.log'  for details.

       at oracle.sysman.oii.oiis.OiisMakeDeps.invokeMakefile(OiisMakeDeps.java:537)

       at  oracle.sysman.oii.oiis.OiisMakeDeps.doRelink(OiisMakeDeps.java:614)

       at  oracle.sysman.oii.oiis.OiisMakeDeps.doOperation(OiisMakeDeps.java:799)

       at  oracle.sysman.oii.oiis.OiisMakeDeps.main(OiisMakeDeps.java:809)

SQL>  select dbtimezone from dual; # CDB和PDB
 
 DBTIME
 ------
 +00:00

--修改时区

SQL>  alter database set time_zone='+08:00'; # 重启数据库

SQL>  select dbtimezone from dual;
 
 DBTIME
 ------
 +08:00
 
 SQL>

如果是RAC的环境，还需要检查其它的服务，比如 db ，nodeapps,listener ,asm等等的时区是不是都是错的。
 
 srvctl getenv database -d <dbname>
 srvctl getenv nodeapps -n <nodename>
 srvctl getenv listener
 srvctl getenv asm
 
 -- 如果需要修改，例子：
 e.g
 srvctl setenv asm -t 'TZ=<the TZ you  want>'

1) Set  awr_pdb_autoflush_enabled=true on PDB level:

alter session set container=PDB1;
 alter system set awr_pdb_autoflush_enabled=true sid=’*’ scope=both;

2) Set AWR  snpashot properly

select * from  cdb_hist_wr_control;
 
 DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
 2580889417 +40150 00:01:00.0 +00008 00:00:00.0 DEFAULT 3
 
 execute dbms_workload_repository.modify_snapshot_settings(interval => 60);
 
 select * from cdb_hist_wr_control;
 
 DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
 2580889417 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 3

3) Also please set AWR_SNAPSHOT_TIME_OFFSET to  1000000 to avoid performance issue when multiple PDBs creating snapshots in  same time.（CDB级）
 alter system set  AWR_SNAPSHOT_TIME_OFFSET=1000000 scope=both;

4) Wait for 1-2 hours  for automatic snapshots to be generated:

select * from  awr_pdb_snapshot;

Or you can  create snapshots manually:

SQL> connect  / as sysdba
 SQL> alter session set container=PDB1;
 SQL> exec dbms_workload_repository.create_snapshot();

Create AWR or  ADDM report:

 @?/rdbms/admin/awrrpt
 @$ORACLE_HOME/rdbms/admin/addmrpt.sql
 Specify the location of AWR Data
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 AWR_ROOT - Use AWR data from root (default)
 AWR_PDB - Use AWR data from PDB    ** Use the location AWR_PDB  for a PDB level report

#!/bin/bash
 #
 # hugepages_settings.sh
 #
 # Linux bash script to compute values for the
 # recommended HugePages/HugeTLB configuration
 # on Oracle Linux
 #
 # Note: This script does calculation for all shared memory
 # segments available when the script is run, no matter it
 # is an Oracle RDBMS shared memory segment or not.
 #
 # This script is provided by Doc ID 401749.1 from My Oracle Support
 # http://support.oracle.com
 
 # Welcome text
 echo "
 This script is provided by Doc ID 401749.1 from My Oracle Support
 (http://support.oracle.com) where it is intended to compute values for
 the recommended HugePages/HugeTLB configuration for the current shared
 memory segments on Oracle Linux. Before proceeding with the execution please note following:
  * For ASM instance, it needs to configure ASMM instead of AMM.
  * The 'pga_aggregate_target' is outside the SGA and
    you should accommodate this while calculating the overall size.
  * In case you changes the DB SGA size,
    as the new SGA will not fit in the previous HugePages configuration,
    it had better disable the whole HugePages,
    start the DB with new SGA size and run the script again.
 And make sure that:
  * Oracle Database instance(s) are up and running
  * Oracle Database 11g Automatic Memory Management (AMM) is not setup
    (See Doc ID 749851.1)
  * The shared memory segments can be listed by command:
      # ipcs -m
 
 
 Press Enter to proceed..."
 
 read
 
 # Check for the kernel version
 KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
 
 # Find out the HugePage size
 HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
 if [ -z "$HPG_SZ" ];then
     echo "The hugepages may not be supported in the system where the script is being executed."
     exit 1
 fi
 
 # Initialize the counter
 NUM_PG=0
 
 # Cumulative number of pages required to handle the running shared memory segments
 for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
 do
     MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
     if [ $MIN_PG -gt 0 ]; then
         NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
     fi
 done
 
 RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`
 
 # An SGA less than 100MB does not make sense
 # Bail out if that is the case
 if [ $RES_BYTES -lt 100000000 ]; then
     echo "***********"
     echo "** ERROR **"
     echo "***********"
     echo "Sorry! There are not enough total of shared memory segments allocated for
 HugePages configuration. HugePages can only be used for shared memory segments
 that you can list by command:
 
     # ipcs -m
 
 of a size that can match an Oracle Database SGA. Please make sure that:
  * Oracle Database instance is up and running
  * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
     exit 1
 fi
 
 # Finish with results
 case $KERN in
     '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
            echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
     '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
     '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
     '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
     '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
     '4.14') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
     *) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
 esac
 
 # End

alter system set open_cursors=2000  scope=both sid='*';

alter system set  session_cached_cursors=500 scope=spfile sid='*';

alter system set inmemory_size=5G  scope=spfile sid='*';

alter system set db_files=3000  scope=spfile sid='*';

alter system set enable_ddl_logging=true  scope=both sid='*';

alter profile default limit  PASSWORD_LIFE_TIME unlimited;

alter system set  "_lm_drm_disable"=7 scope=spfile sid='*';

alter system set temp_undo_enabled=true  scope=both sid='*';

alter system set "_undo_autotune"  = false scope=both sid='*';

alter system set undo_retention=1800  scope=both sid='*';

alter system set shared_pool_size=5G  scope=both sid='*';

select dbtimezone from dual;

--SQLPLUS默认编辑器设置为vi

define _editor=vi

--默认打开DBMA_OUTPUT,这样不必要每次在输入这个命令，同时将默认缓冲池设置得尽可能大

set serveroutput on size 1000000

--假脱机输出文本时，会去除文本行两端的空格，而且行宽不定，如果设置为off（默认设置），假脱机输出的文本行宽度则等于所设置的linesize

set trimspool on

--设置选择LONG和CLOB列时显示的默认字节数

set long 5000

--设置显示的文本宽为200个字符

set linesize 200

--设置SQLPLUS多久打印一次标题，将此参数设置大些这样每页只显示一次标题

set pagesize 9999

--设置AUTOTRACE得到解释计划输出的默认宽度，一般80足够放下整个计划

column plan_plus_exp format a80

--设置SQLPLUS提示符，显示格式为用户@数据库名

column global_name new_value gname

set termout off

define gname=idle

column global_name new_value gname

select lower(user) ||'@'||  substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name

from (select  global_name,instr(global_name,'.') dot from global_name);

set sqlprompt '&gname>'

set termout on

SQL> create role pub_role;

Role created.

SQL> grant connect,create table,create  view,create materialized view,create job, manage scheduler,create synonym  ,debug any procedure, debug connect session ,RESTRICTED SESSION,create  sequence,create trigger,create type,CREATE   PROCEDURE to pub_role;

select 'revoke execute on  '||table_name||' from public;' from dba_tab_privs where grantee='PUBLIC' and  privilege='EXECUTE' and table_name in  ('UTL_FILE','UTL_TCP','UTL_HTTP','UTL_SMTP','DBMS_LOB','DBMS_SYS_SQL','DBMS_JOB');

SQL> grant pub_role to XTCRM;

Grant succeeded.

CREATE TABLESPACE XTCRM     LOGGING DATAFILE  '+DG_DATA/JZDB/DATAFILE/xtcrm01.dbf' SIZE 100M AUTOEXTEND ON ;

Alter user xtcrm qutoa unlimited on  xtcrm;

create user XTCRM

   identified by "1QaZ#eDc"

   default tablespace XTCRM

   profile jzdb_profile;

User created.

SQL> alter user xtcrm quota unlimited  on XTCRM;

User altered.

CREATE TABLESPACE TBS_BSS_XTDC DATAFILE  '+DG_DATA/JZDB/DATAFILE/xtdc01.dbf' SIZE 100M AUTOEXTEND ON ;

create user XTDC

   identified by "5TgB^yHn"

   default tablespace TBS_BSS_XTDC

   profile jzdb_profile;

SQL> alter user xtcrm quota unlimited  on TBS_BSS_XTDC;

User altered.

SQL> grant pub_role to XTDC;

Grant succeeded.

--创建角色

create role pub_role;

--给角色权限

grant connect,resource,create view,create  job,create synonym ,debug any procedure, debug connect session to pub_role;

grant connect,resource,create view,create  materialized view,on commit refresh ,global query rewrite,create job,create  synonym ,debug any procedure, debug connect session to pub_role;

--取消public角色的execute权限

select 'revoke execute on  '||table_name||' from public;' from dba_tab_privs where grantee='PUBLIC' and  privilege='EXECUTE' and table_name in  ('UTL_FILE','UTL_TCP','UTL_HTTP','UTL_SMTP','DBMS_LOB','DBMS_SYS_SQL','DBMS_JOB');