一、系统基础参数调整准备。 1.1 root用户环境变量准备： vim /etc/profile 新增如下： # set PS1 environment if [ $UID -eq 0 ]; then export PS1=$LOGNAME@`hostname`['$PWD']#" " else export PS1=$LOGNAME@`hostname`['$PWD']'$'" " fi # set LANG export LANG=C # set histimeformat environment export HISTTIMEFORMAT='%F %T ' # Record logging and command export PROMPT_COMMAND='{ z=`history 1 | { read x y; echo $y; }`; echo -n $z ::; who am i; } >> /var/log/cmdlog/`id -un`_`who am i 2>/dev/null | awk -F[\(\)] "{if(\\$2)print \\$2;else print \"local\"}"`_`date +%Y%m%d`.log' readonly PROMPT_COMMAND # set CORE NO SHM echo 0x1 > /proc/self/coredump_filter # set oracle environment if [ $USER = "oracle" ]; then if [ $SHELL = "/bin/ksh" ]; then ulimit -p 16384 ulimit -n 65536 else ulimit -u 65536 -n 65536 fi umask 027 fi ###################################### mkdir -p /var/log/cmdlog chmod 777 /var/log/cmdlog 1.2 修改主机名称 hostnamectl set-hostname db01 1.3 关闭selinux vi /etc/selinux/config SELINUX=disabled 1.4 关闭防火墙 systemctl stop firewalld firewall-cmd --state systemctl disable firewalld systemctl list-unit-files | grep firewalld 1.5 关闭networkmanager ----redhat8系统忽略，不要操作。 systemctl stop NetworkManager systemctl is-active NetworkManager systemctl disable NetworkManager systemctl list-unit-files | grep NetworkManager 1.6 关闭Transparent HugePages cp /boot/grub2/grub.cfg /boot/grub2/grub.cfg.bak cp /etc/default/grub /etc/default/grub.bak vi /etc/default/grub 文件添加transparent_hugepage=never至GRUB_CMDLINE_LINUX行末尾 ------- rd.lvm.lv=vg00/lvswap rd.lvm.lv=vg00/lvusr rhgb quiet transparent_hugepage=never" ------- 加载配置到文件 grub2-mkconfig -o /boot/grub2/grub.cfg 检查是否加载透明大页配置： cat /boot/grub2/grub.cfg |grep never 编辑文件，尾增加配置：vi /etc/rc.d/rc.local # Disable Transparent HugePages if test -f /sys/kernel/mm/transparent_hugepage/enabled; then echo never > /sys/kernel/mm/transparent_hugepage/enabled fi if test -f /sys/kernel/mm/transparent_hugepage/defrag; then echo never > /sys/kernel/mm/transparent_hugepage/defrag fi 赋予权限 chmod +x /etc/rc.d/rc.local 重启服务器后，检查配置是否生效 cat /sys/kernel/mm/transparent_hugepage/defrag always defer defer+madvise [madvise] never # cat /sys/kernel/mm/transparent_hugepage/enabled always madvise [never] grep HugePages /proc/meminfo AnonHugePages: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 1.7 配置系统参数文件 ----redhat8.6忽略。 more /etc/security/limits.d/20-nproc.conf # Default limit for number of user's processes to prevent # accidental fork bombs. # See rhbz #432903 for reasoning. # For Zsmart * soft nofile 65536 * hard nofile 65536 * soft nproc 65536 * hard nproc 65536 root soft nproc unlimited cp /etc/security/limits.conf /etc/security/limits.confbak vi /etc/security/limits.conf 末尾新增配置 /etc/security/limits.conf # For Zsmart * soft core 10485760 * hard core 10485760 * soft nofile 65536 * hard nofile 65536 * soft nproc 65536 * hard nproc 65536 * soft stack 10240 1.8 配置pam的login模块 cp /etc/pam.d/login /etc/pam.d/loginbak vi /etc/pam.d/login 配置文件末尾新增配置：/etc/pam.d/login # Enbale limit setting session required pam_limits.so vi /etc/sysctl.conf (free -b 查看内存，设置共享内存时比之小) （上面两个数相除的结果） ##########Shared Memory############### kernel.shmmax = 16000000000 kernel.shmmni = 4096 kernel.shmall = 3,906,250 ########VM CONFIG########### vm.drop_caches=1 vm.max_map_count=655360 vm.vfs_cache_pressure=10000 vm.dirty_writeback_centisecs=500 vm.overcommit_memory=0 vm.swappiness=5 vm.min_free_kbytes=3145728 vm.dirty_ratio=20 vm.dirty_background_ratio=10 ##########Message Queues############## kernel.msgmax = 655360 kernel.msgmni = 4096 kernel.msgmnb = 1024000 ##########Semaphore Arrays############ kernel.sem = 10240 83886080 4096 8192 ##########open file################### fs.file-max = 6815744 ##########aio######################### fs.aio-max-nr = 3145728 #########net.ipv4 Tuning############## net.ipv4.ip_forward=0 net.ipv4.ip_local_port_range=9000 65500 net.ipv4.tcp_fin_timeout=30 net.ipv4.tcp_keepalive_time=900 net.ipv4.tcp_keepalive_probes=6 net.ipv4.tcp_keepalive_intvl=30 net.ipv4.tcp_syncookies=1 net.ipv4.tcp_window_scaling=1 net.ipv4.tcp_rmem=32768 436600 16777216 net.ipv4.tcp_wmem=8192 436600 16777216 net.ipv4.icmp_echo_ignore_broadcasts=1 net.ipv4.icmp_ignore_bogus_error_responses=1 net.ipv4.conf.all.send_redirects=0 net.ipv4.conf.default.send_redirects=0 net.ipv4.conf.all.rp_filter=1 net.ipv4.conf.default.rp_filter=1 net.ipv4.conf.all.accept_source_route=0 net.ipv4.conf.default.accept_source_route=0 net.ipv4.conf.all.accept_redirects=0 net.ipv4.conf.default.accept_redirects=0 net.ipv4.conf.all.secure_redirects=0 net.ipv4.conf.default.secure_redirects=0 #########net.ipv6 Tuning############## net.ipv6.conf.all.accept_ra=0 net.ipv6.conf.default.accept_ra=0 net.ipv6.conf.default.accept_redirects=0 net.ipv6.conf.all.accept_redirects=0 net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.conf.lo.disable_ipv6 = 1 net.core.rmem_default=8388608 net.core.wmem_default=8388608 net.core.rmem_max=16777216 net.core.wmem_max=16777216 net.core.netdev_max_backlog=32768 net.core.somaxconn=32768 ###Other Kernel############# kernel.sysrq=0 kernel.core_uses_pid=1 kernel.hung_task_timeout_secs = 120 ###Hugepages############# #vm.nr_hugepages= #vm.hugetlb_shm_group ########磁盘准备#### 二、业务环境部署准备 lvcreate -L 20G -n lvoracle vgdata lvcreate -L 20G -n lvccdata vgdata lvcreate -L 10G -n lvccredo vgdata lvcreate -L 1G -n lvarchcc vgdata lvcreate -L 20G -n lvrman vgdata lvcreate -L 5G -n lvccexp vgdata mkfs.xfs /dev/vgdata/lvoracle mkfs.xfs /dev/vgdata/lvccdata mkfs.xfs /dev/vgdata/lvccredo mkfs.xfs /dev/vgdata/lvarchcc mkfs.xfs /dev/vgdata/lvrman mkfs.xfs /dev/vgdata/lvccexp mkdir /oracle mkdir /ccdata mkdir /ccredo mkdir /archcc mkdir /rman mkdir /ccexp echo "/dev/vgdata/lvoracle /oracle xfs defaults 0 0 ">>/etc/fstab echo "/dev/vgdata/lvccdata /ccdata xfs defaults 0 0 ">>/etc/fstab echo "/dev/vgdata/lvccredo /ccredo xfs defaults 0 0 ">>/etc/fstab echo "/dev/vgdata/lvarchcc /archcc xfs defaults 0 0 ">>/etc/fstab echo "/dev/vgdata/lvrman /rman xfs defaults 0 0 ">>/etc/fstab echo "/dev/vgdata/lvccexp /ccexp xfs defaults 0 0 ">>/etc/fstab mount /oracle mount /ccdata mount /ccredo mount /archcc mount /rman mount /ccexp groupadd -g 1501 dba useradd -u 1501 -g dba -d /oracle oracle chown -R oracle:dba /oracle chown -R oracle:dba /ccdata chown -R oracle:dba /ccredo chown -R oracle:dba /archcc chown -R oracle:dba /rman chown -R oracle:dba /ccexp chmod -R 750 /oracle chmod -R 750 /ccdata chmod -R 750 /ccredo chmod -R 750 /archcc chmod -R 750 /rman chmod -R 750 /ccexp passwd oracle 密码设置为oracle 存放安装介质的目录 su - oracle mkdir -p /soft/iso/ mkdir -p /soft/orasoft/ora12201 mkdir -p /soft/orasoft/opatch 1.oracle环境变量： su - oracle vi /oracle/.bash_profile export ORACLE_BASE=/oracle export ORACLE_HOME=$ORACLE_BASE/product/11g export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32 export LIBPATH=$ORACLE_HOME/lib export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 stty erase ^h umask 027 bash-4.2$ ulimit -l 64 -bash-4.2$ ulimit -a core file size (blocks, -c) 10485760 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 256855 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 65536 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 65536 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited -bash-4.2$ 2.软件包准备 2.1本地yum准备 vi /etc/yum.repos.d/local.repo [root@localhost iso]# more /etc/yum.repos.d/local.repo [ol8_baseos_latest] name=Oracle Linux 8 BaseOS Latest ($basearch) baseurl=file:///iso/BaseOS gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle gpgcheck=0 enabled=1 [os_latest] name=Oracle Linux 8 BaseOS Latest ($basearch) baseurl=file:///iso/AppStream/ gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle gpgcheck=0 enabled=1 mkdir /iso mount -o loop /soft/iso/rhel-server-7.9-x86_64-dvd.iso /iso mount -o loop /dev/cdrom /iso 2.2 系统工具软件 yum -y install binutils.x86_64 yum -y install compat-libcap1.i686 yum -y install compat-libcap1.x86_64 yum -y install compat-libstdc++-33.i686 yum -y install compat-libstdc++-33.x86_64 yum -y install gcc.x86_64 yum -y install gcc-c++.x86_64 yum -y install glibc.i686 yum -y install glibc.x86_64 yum -y install glibc-devel.i686 yum -y install glibc-devel.x86_64 yum -y install ksh.x86_64 yum -y install libaio.i686 yum -y install libaio.x86_64 yum -y install libaio-devel.i686 yum -y install libaio-devel.x86_64 yum -y install libX11.i686 yum -y install libX11.x86_64 yum -y install libXau.i686 yum -y install libXau.x86_64 yum -y install libgcc.i686 yum -y install libgcc.x86_64 yum -y install libstdc++.i686 yum -y install libstdc++.x86_64 yum -y install libstdc++-devel.i686 yum -y install libstdc++-devel.x86_64 yum -y install libXi.i686 yum -y install libXi.x86_64 yum -y install libXtst.i686 yum -y install libXtst.x86_64 yum -y install libxcb.i686 yum -y install libxcb.x86_64 yum -y install make.x86_64 yum -y install sysstat.x86_64 yum -y install nfs-utils.x86_64 yum -y install net-tools.x86_64 yum -y install smartmontools.x86_64 yum -y install perl yum -y install libnsl yum -y install glibc-devel yum install -y psmisc unixODBC.i686 \ ----需要确认。 unixODBC.x86_64 \ unixODBC-devel.i686 \ unixODBC-devel.x86_64 图形化调试软件 yum -y install xclock xorg-x11-utils yum install chrony -y 安装vim编辑器 yum install vim -y yum install unzip -y 安装psu依赖 yum install psmisc -y 2.3 .验证包是否安装 rpm -q binutils.x86_64 \ compat-libcap1.i686 \ compat-libcap1.x86_64 \ compat-libstdc++-33.i686 \ compat-libstdc++-33.x86_64 \ gcc.x86_64 \ gcc-c++.x86_64 \ glibc.i686 \ glibc.x86_64 \ glibc-devel.i686 \ glibc-devel.x86_64 \ ksh.x86_64 \ libaio.i686 \ libaio.x86_64 \ libaio-devel.i686 \ libaio-devel.x86_64 \ libX11.i686 \ libX11.x86_64 \ libXau.i686 \ libXau.x86_64 \ libgcc.i686 \ libgcc.x86_64 \ libstdc++.i686 \ libstdc++.x86_64 \ libstdc++-devel.i686 \ libstdc++-devel.x86_64 \ libXi.i686 \ libXi.x86_64 \ libXtst.i686 \ libXtst.x86_64 \ libxcb.i686 \ libxcb.x86_64 \ make.x86_64 \ sysstat.x86_64 \ nfs-utils.x86_64 \ net-tools.x86_64 \ smartmontools.x86_64 \ unixODBC.i686 \ unixODBC.x86_64 \ unixODBC-devel.i686 \ unixODBC-devel.x86_64 ntp的配置 systemctl enable chronyd systemctl restart chronyd systemctl enable chronyd systemctl is-enabled chronyd root@juba-db01[/root]# vi /etc/chrony.conf # Use public servers from the pool.ntp.org project. # Please consider joining the pool (http://www.pool.ntp.org/join.html). #server 0.rhel.pool.ntp.org iburst #server 1.rhel.pool.ntp.org iburst #server 2.rhel.pool.ntp.org iburst #server 3.rhel.pool.ntp.org iburst server 192.168.56.101 iburst local stratum 8 chronyc tracking chronyc sources bash-4.2$ ulimit -l 64 -bash-4.2$ ulimit -a core file size (blocks, -c) 10485760 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 256855 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 65536 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 65536 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited -bash-4.2$ https://www.oracle.com/database/technologies/oracle19c-linux-downloads.html chmod -R 755 /soft su - oracle mkdir -p /oracle/product/19c unzip /soft/LINUX.X64_193000_db_home.zip -d /oracle/product/19c/ 1.检查电脑ip地址。10.45.94.225 2.开启Xmanager - Passive su - oracle export DISPLAY=192.168.2.159:0.0 export CV_ASSUME_DISTID=RHEL7.6 /oracle/product/19c/runInstaller 按照集成规范，部署数据库实例。 share pool 1024m buffer cache 102128 java pool 128m large pool 1024m pga 512m ######## 安装oracle补丁 -rwxr--r-- 1 oracle dba 122119435 Nov 25 15:32 p6880880_200000_Linux-x86-64.zip oracle@juba-db01[/soft/orasoft/opatch]$ cd /oracle/product/122/ oracle@juba-db01[/oracle/product/122]$ mv OPatch/ OPatchbak oracle@juba-db01[/oracle/product/122]$ mv /soft/orasoft/opatch/OPatch/ ./ oracle@juba-db01[/oracle/product/122]$ /oracle/product/122/OPatch/opatch version oracle@juba-db01[/oracle/product/122]$ /oracle/product/122/OPatch/opatch version OPatch Version: 12.2.0.1.23 OPatch succeeded. oracle@juba-db01[/oracle/product/122]$ /oracle/product/122/OPatchbak/opatch version OPatch Version: 12.2.0.1.6 OPatch succeeded. oracle@juba-db01[/oracle/product/122]$ 12CBD-80741-893A6-34184-E5FA0-485BC-59A37-5BE93-00 oracle@juba-db01[/oracle]$ cd /soft/orasoft/PSU/31741641/ oracle@juba-db01[/soft/orasoft/PSU/31741641]$ /oracle/product/122/OPatch/opatch apply Oracle Interim Patch Installer version 12.2.0.1.23 Copyright (c) 2020, Oracle Corporation. All rights reserved. Oracle Home : /oracle/product/122 Central Inventory : /oracle/oraInventory from : /oracle/product/122/oraInst.loc OPatch version : 12.2.0.1.23 OUI version : 12.2.0.1.4 Log file location : /oracle/product/122/cfgtoollogs/opatch/opatch2020-11-25_15-58-55PM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 31741641 Do you want GETGE y User Responded with: Y All checks passed. Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/oracle/product/122') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Applying interim patch '31741641' to OH '/oracle/product/122' ApplySession: Optional component(s) [ oracle.swd, 12.2.0.1.0 ] , [ oracle.swd.oui, 12.2.0.1.0 ] , [ oracle.rdbms.drdaas, 12.2.0.1.0 ] , [ oracle.oid.client, 12.2.0.1.0 ] , [ oracle.ons.eons.bwcompat, 12.2.0.1.0 ] , [ oracle.ons.cclient, 12.2.0.1.0 ] , [ oracle.ons.daemon, 12.2.0.1.0 ] , [ oracle.network.cman, 12.2.0.1.0 ] not present in the Oracle Home or a higher version is found. OPatch found the word "error" in the stderr of the make command. Please look at this stderr. You can re-run this make command. Stderr output: chmod: changing permissions of '/oracle/product/122/bin/extjobO': Operation not permitted make: [iextjob] Error 1 (ignored) Patch 31741641 successfully applied. OPatch Session completed with warnings. Log file location: /oracle/product/122/cfgtoollogs/opatch/opatch2020-11-25_15-58-55PM_1.log OPatch completed with warnings. oracle@juba-db01[/soft/orasoft/PSU/31741641]$ oracle@juba-db01[/soft/orasoft/PSU/31741641]$ /oracle/product/122/OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.23 Copyright (c) 2020, Oracle Corporation. All rights reserved. Oracle Home : /oracle/product/122 Central Inventory : /oracle/oraInventory from : /oracle/product/122/oraInst.loc OPatch version : 12.2.0.1.23 OUI version : 12.2.0.1.4 Log file location : /oracle/product/122/cfgtoollogs/opatch/opatch2020-11-25_16-12-01PM_1.log Lsinventory Output file location : /oracle/product/122/cfgtoollogs/opatch/lsinv/lsinventory2020-11-25_16-12-01PM.txt -------------------------------------------------------------------------------- Local Machine Information:: Hostname: juba-db01 ARU platform id: 226 ARU platform description:: Linux x86-64 Installed Top-level Products (1): Oracle Database 12c 12.2.0.1.0 There are 1 products installed in this Oracle Home. Interim patches (1) : Patch 31741641 : applied on Wed Nov 25 16:01:18 EAT 2020 Unique Patch ID: 23847088 Patch description: "Database Oct 2020 Release Update : 12.2.0.1.201020 (31741641)" Created on 6 Oct 2020, 01:59:24 hrs PST8PDT Bugs fixed: 7391838, 8480838, 8932139, 8975044, 12763598, 13554903, 14221306 31718134, 31771858, 31867037, 31905033 -------------------------------------------------------------------------------- OPatch succeeded. oracle@juba-db01[/soft/orasoft/PSU/31741641]$ 创建数据库实例： cd /archcc/ mkdir fast_recovery_area mkdir -p /ccdata/cc/datafile 复杂密码校验设置 export ORACLE_SID=cc sqlplus / as sysdba @?/rdbms/admin/utlpwdmg.sql set linesize 1000 set pagesize 1000 col PROFILE for a15 SELECT profile,resource_type,resource_name,limit FROM dba_profiles WHERE resource_type='PASSWORD' AND profile='DEFAULT'; redo配置 alter database add logfile group 1 （‘/ccredo/cc/onlinelog/redo1_1.dbf’,'/ccredo/cc/onlinelog/redo1_2.dbf') size 512M; alter database add logfile group 2 （‘/ccredo/cc/onlinelog/redo2_1.dbf’,'/ccredo/cc/onlinelog/redo2_2.dbf') size 512M; alter database add logfile group 3（‘/ccredo/cc/onlinelog/redo3_1.dbf’,'/ccredo/cc/onlinelog/redo3_2.dbf') size 512M; alter database add logfile group 4（‘/ccredo/cc/onlinelog/redo4_1.dbf’,'/ccredo/cc/onlinelog/redo4_2.dbf') size 512M; alter database add logfile group 5（‘/ccredo/cc/onlinelog/redo5_1.dbf’,'/ccredo/cc/onlinelog/redo5_2.dbf') size 512M; alter database add logfile group 6（‘/ccredo/cc/onlinelog/redo6_1.dbf’,'/ccredo/cc/onlinelog/redo6_2.dbf') size 512M; alter database add logfile group 7（‘/ccredo/cc/onlinelog/redo7_1.dbf’,'/ccredo/cc/onlinelog/redo7_2.dbf') size 512M; alter database add logfile group 8（‘/ccredo/cc/onlinelog/redo8_1.dbf’,'/ccredo/cc/onlinelog/redo8_2.dbf') size 512M; alter database drop logfile group 1; alter database drop logfile group 2; alter database drop logfile group 3; select group#,type, member from v$logfile order by group#; select GROUP#,THREAD#, BYTES,MEMBERS,STATUS from v$log; 数据库参数调整 alter system set streams_pool_size=0M scope=spfile; alter system set log_buffer =268435456 scope=spfile; alter system set db_files=2000 scope=spfile; alter system set control_file_record_keep_time=14 scope=spfile; alter system set deferred_segment_creation=FALSE scope=spfile; alter system set filesystemio_options=setall scope=spfile; 参数验证： show parameter streams_pool_size show parameter log_buffer show parameter db_files show parameter control_file_record_keep_time show parameter deferred_segment_creation show parameter filesystemio_options alter database datafile '/ccdata/cc/system01.dbf' resize 20480m; alter database datafile '/ccdata/cc/sysaux01.dbf' resize 20480m; alter database datafile '/ccdata/cc/undotbs01.dbf' resize 12000m; alter database tempfile '/ccdata/cc/temp01.dbf' resize 12000m; alter database datafile '/ccdata/cc/users01.dbf' resize 2048m; alter database datafile '/ccdata/cc/system01.dbf' autoextend off; alter database datafile '/ccdata/cc/sysaux01.dbf' autoextend off; alter database datafile '/ccdata/cc/undotbs01.dbf' autoextend on maxsize 30720m; alter database tempfile '/ccdata/cc/temp01.dbf' autoextend off; alter database datafile '/ccdata/cc/users01.dbf' autoextend off; create tablespace tab_cc datafile '/ccdata/cc/datafile/tab_cc01.dbf' size 30720m; alter tablespace tab_cc add datafile ‘/ccdata/cc/datafile/tab_cc02.dbf' size 30720m; alter tablespace tab_cc add datafile ‘/ccdata/cc/datafile/tab_cc03.dbf' size 30720m; alter tablespace tab_cc add datafile ‘/ccdata/cc/datafile/tab_cc04.dbf' size 30720m; alter tablespace tab_cc add datafile ‘/ccdata/cc/datafile/tab_cc05.dbf' size 30720m; alter database datafile ‘/ccdata/cc/datafile/tab_cc01.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/tab_cc02.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/tab_cc03.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/tab_cc04.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/tab_cc05.dbf' autoextend off; create tablespace tab_def datafile '/ccdata/cc/tab_def01.dbf' size 1G; alter database datafile '/ccdata/cc/tab_def01.dbf' size' autoextend off; create tablespace idx_cc datafile ‘/ccdata/cc/datafile/idx_cc01.dbf' size 30720m; alter tablespace idx_cc add datafile ‘/ccdata/cc/datafile/idx_cc02.dbf' size 30720m; alter tablespace idx_cc add datafile ‘/ccdata/cc/datafile/idx_cc03.dbf' size 15000m; alter database datafile ‘/ccdata/cc/datafile/idx_cc01.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/idx_cc02.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/idx_cc03.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/idx_cc04.dbf' autoextend off; alter database datafile ‘/ccdata/cc/datafile/idx_cc05.dbf' autoextend off; 创建profile create profile ZSMART limit sessions_per_user unlimited cpu_per_session unlimited cpu_per_call unlimited connect_time unlimited idle_time unlimited logical_reads_per_session unlimited logical_reads_per_call unlimited composite_limit unlimited private_sga unlimited failed_login_attempts unlimited password_life_time unlimited password_reuse_time unlimited password_reuse_max unlimited password_lock_time 1/24 password_grace_time 7 password_verify_function ORA12C_VERIFY_FUNCTION; commit; create profile MT_ZSMART limit sessions_per_user unlimited cpu_per_session unlimited cpu_per_call unlimited connect_time unlimited idle_time unlimited logical_reads_per_session unlimited logical_reads_per_call unlimited composite_limit unlimited private_sga unlimited failed_login_attempts 6 password_life_time 90 password_reuse_time 60 password_reuse_max 2 password_lock_time 90 password_grace_time 100 password_verify_function ORA12C_VERIFY_FUNCTION; commit; set linesize 1000 set pagesize 1000 col PROFILE for a20 col LIMIT for a30 select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles order by profile ; create user CC identified by "iwhale2020!" default tablespace TAB_CC profile ZSMART; create user MSREAD identified by "iwhale2020!" default tablespace USERS profile MT_ZSMART; 用户授权 grant SET CONTAINER to CC; grant CREATE SESSION to CC; grant CREATE TRIGGER to CC; grant CREATE SEQUENCE to CC; grant CREATE TYPE to CC; grant CREATE PROCEDURE to CC; grant CREATE CLUSTER to CC; grant CREATE OPERATOR to CC; grant CREATE INDEXTYPE to CC; grant CREATE TABLE to CC; grant CREATE SYNONYM to CC; grant CREATE VIEW to CC; grant CREATE MATERIALIZED VIEW to CC; grant CREATE DATABASE LINK to CC; grant ALTER SESSION to CC; grant DEBUG ANY PROCEDURE to CC; grant DEBUG CONNECT SESSION to CC; grant SELECT ANY TABLE to CC; grant SELECT ANY TRANSACTION to CC; grant CREATE JOB to CC; grant EXP_FULL_DATABASE to CC; grant IMP_FULL_DATABASE to CC; grant UNLIMITED TABLESPACE to CC; col GRANTED_ROLE for a20 select * from dba_role_privs where grantee='CC'; 归档目录的配置 alter system set log_archive_dest_1='LOCATION=/archcc' scope=spfile; shutdown immeidate; startup mount; alter database archivelog; alter database open; 配置监听 vi /oracle/product/19c/network/admin/listener.ora LISTENER_CC = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 11521)) ) ) ) SID_LIST_LISTENER_CC = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cc) (ORACLE_HOME = /oracle/product/122) (SID_NAME = cc) ) ) lsnrctl start LISTENER_CC lsnrctl status LISTENER_CC CC = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.150.31.3 )(PORT = 11521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cc) ) ) RB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.150.31.4 )(PORT = 11521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rb) ) ) sett = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.150.31.37 )(PORT = 11521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = sett) ) ) med = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.150.31.38 )(PORT = 11521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = med) ) ) $ sqlplus ‘ /as sysdba’ col ACTION for a10 col STATUS for a10 col DESCRIPTION for a55 SELECT PATCH_ID,ACTION,STATUS,DESCRIPTION FROM dba_registry_sqlpatch; set linesize 1000 set pagesize 1000 col PARAMETER for a25 col VALUE for a15 select * from v$nls_parameters where PARAMETER in('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); set linesize 1000 set pagesize 1000 col PROFILE for a20 col LIMIT for a25 SELECT profile,resource_type,resource_name,limit FROM dba_profiles where resource_name='PASSWORD_VERIFY_FUNCTION'; create profile MT_ZSMART limit sessions_per_user unlimited cpu_per_session unlimited cpu_per_call unlimited connect_time unlimited idle_time unlimited logical_reads_per_session unlimited logical_reads_per_call unlimited composite_limit unlimited private_sga unlimited failed_login_attempts 6 password_life_time 90 password_reuse_time 60 password_reuse_max 2 password_lock_time 90 password_grace_time 100 password_verify_function ORA12C_VERIFY_FUNCTION; alter system set streams_pool_size=512M scope=spfile; alter system set log_buffer=268435456 scope=spfile; alter system set db_files=8192 scope=spfile; alter system set control_file_record_keep_time=31 scope=spfile; alter system set deferred_segment_creation=FALSE scope=spfile; alter system set filesystemio_options=setall scope=spfile; alter system set open_cursors=2000 scope=spfile; alter system set session_cached_cursors=200 scope=spfile; alter system set audit_trail=NONE scope=spfile; alter system set log_archive_format='cc_%t_%s_%r.arc' scope=spfile; alter system set "_partition_large_extents"=false scope=spfile; alter system set "_cursor_obsolete_threshold"=1000 scope=spfile; alter system set "_optimizer_use_feedback" = false scope=spfile; alter system set archive_lag_target=1200 scope=spfile; alter system set parallel_execution_message_size=16384 scope=spfile; alter system set temp_undo_enabled=FALSE scope=spfile; alter system set log_checkpoints_to_alert=TRUE scope=spfile; alter system set fast_start_mttr_target=300 scope=spfile; alter system set fast_start_parallel_rollback=HIGH scope=spfile; alter system set session_max_open_files=20 scope=spfile; alter system set pga_aggregate_limit=(大于等于pga_aggregate_target) alter system set optimizer_adaptive_plans=false scope=spfile; alter system set event="10949 trace name context forever:28401 trace name context forever,level 1:44951 trace name context forever, level 1024" scope=spfile; alter system set "_resource_manager_always_off"= true scope=spfile; alter system set resource_manager_plan='' scope=spfile; alter system set undo_retention=18000 scope=spfile; alter system set "_undo_autotune"=false scope=spfile; 表空间 create tablespace tab_cc datafile '/ccdata/CC/tab_cc_001.dbf' size 100M; alter database datafile '/ccdata/CC/tab_cc_001.dbf' autoextend off; create tablespace idx_cc datafile '/ccdata/CC/idx_cc_001.dbf' size 50M; alter database datafile '/ccdata/CC/idx_cc_001.dbf' autoextend off; alter tablespace tab_cc add datafile '/ccdata/CC/tab_cc_002.dbf' size 50m autoextend off; create profile ZSMART limit sessions_per_user unlimited cpu_per_session unlimited cpu_per_call unlimited connect_time unlimited idle_time unlimited logical_reads_per_session unlimited logical_reads_per_call unlimited composite_limit unlimited private_sga unlimited failed_login_attempts unlimited password_life_time unlimited password_reuse_time unlimited password_reuse_max unlimited password_lock_time 1/24 password_grace_time 7 password_verify_function ORA12C_VERIFY_FUNCTION; commit; create user CC identified by "Zsmart.888" default tablespace TAB_CC profile ZSMART; grant SET CONTAINER to CC; grant CREATE SESSION to CC; grant CREATE TRIGGER to CC; grant CREATE SEQUENCE to CC; grant CREATE TYPE to CC; grant CREATE PROCEDURE to CC; grant CREATE CLUSTER to CC; grant CREATE OPERATOR to CC; grant CREATE INDEXTYPE to CC; grant CREATE TABLE to CC; grant CREATE SYNONYM to CC; grant CREATE VIEW to CC; grant CREATE MATERIALIZED VIEW to CC; grant CREATE DATABASE LINK to CC; grant ALTER SESSION to CC; grant DEBUG ANY PROCEDURE to CC; grant DEBUG CONNECT SESSION to CC; grant SELECT ANY TABLE to CC; grant SELECT ANY TRANSACTION to CC; grant CREATE JOB to CC; grant EXP_FULL_DATABASE to CC; grant IMP_FULL_DATABASE to CC; grant UNLIMITED TABLESPACE to CC; ALTER USER cc DEFAULT TABLESPACE tab_cc PROFILE Zsmart; 监听器 #################### LISTENER_CC = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.136.94)(PORT = 11521)) ) ) ) SID_LIST_LISTENER_CC = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = cc) (ORACLE_HOME = /oracle/product/19c) (SID_NAME = cc) ) ) ########################## CC = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.136.94)(PORT = 11521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cc) ) ) Undo表空间： alter system set log_archive_dest_1='LOCATION=/archcc' scope=spfile; alter database add logfile group 10 ('/ccredo/CC/redo4_1.dbf','/ccredo/CC/redo4_2.dbf') size 2048M; ALTER SYSTEM SWITCH LOGFILE; ALTER DATABASE DROP LOGFILE GROUP 1; alter tablespace undotbs1 add datafile '/ccdata/CC/undotbs1_02.dbf' size 100m autoextend off; alter tablespace temp add tempfile '/ccdata/CC/temp_02.dbf' size 100m autoextend off; 在 Oracle 数据库中操作这些任务，具体的 SQL 命令如下： 1、CREATE PFILE='/oracle/pfilecc.ora-202308' FROM SPFILE; 2、集成手册 3、 SHOW PARAMETER sga_max_size; ALTER SYSTEM SET sga_max_size= 13472m SCOPE=SPFILE; shutdown immediate startup SHOW PARAMETER sga_max_size; 4、 SHOW PARAMETER pga_aggregate_target; ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE; shutdown immediate startup SHOW PARAMETER pga_aggregate_target; 5、 SHOW PARAMETER processes; ALTER SYSTEM SET processes = 6200 SCOPE=SPFILE; shutdown immediate startup SHOW PARAMETER processes; 6、 SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; ALTER SYSTEM SET log_archive_dest_1='LOCATION=/rmancc/CC/ARCHIVELOG' SCOPE=SPFILE; ALTER SYSTEM ARCHIVE LOG CURRENT; 7、 EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 30, retention => 43200);