暂无图片
暂无图片
13
暂无图片
暂无图片
暂无图片
首页 / 【安装】-Oracle linux7.9 19c RAC 安装后期配置指导

【安装】-Oracle linux7.9 19c RAC 安装后期配置指导

原创 闫伟 2021-08-08
6229

本文主要是安装完成oracle19c之后的后期配置工作总结。

Table of Contents

1 配置sqlplus

1.1 安装readline和rlwrap

readline
rpm -qa|grep readline
readline-6.2-11.el7.x86_64
如何需要安装则yum -y install readline*
rlwrap
 tar-xvf rlwrap-0.41.tar.gz
编译安装
[root@localhost Downloads]#./configure
[root@localhost Downloads]#make
[root@localhost Downloads]#make install

1.2 配置.bash_profile

#最后配置使用rlwrap sqlplus，在oracle环境变量文件中添加：
vi /home/oracle/.bash_profile
#添加
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias ggsci='rlwrap /u01/ogg/ggsci'
#立即生效
[oracle@standby1 rlwrap-0.30]# source  /home/oracle/.bash_profile

1.3 配置glogin


SQL*Plus 在启动时会自动运行脚本：glogin.sql 。
glogin.sql 存放在目录$ORACLE_HOME/sqlplus/admin/下。
每当用户启动 SQLPlus 会话并成功建立 Oracle 数据库连接时，SQLPlus 就会执行此脚本。
该脚本可以写入在 SQL*Plus 脚本中的任何内容，例如系统变量设置或 DBA 想要实现的其他全局设置
[oracle@oracle19c admin]$ cat <<EOF>>$ORACLE_HOME/sqlplus/admin/glogin.sql
> --设置编辑器用vi打开，windows客户端可以换成NotePad
> define _editor=vi
> --设置dbms_output输出缓冲区大小
> set serveroutput on size 1000000
> --设置输出格式
> set long 200
> set linesize 500
> set pagesize 9999
> --去除重定向输出每行拖尾空格
> set trimspool on
> --设置name列长
> col Name format a80
> --查询当前实例名
> set termout off
> col global_name new_value gname
> 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 _DATE> '
> --设置session时间格式
> ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
> set termout on
> EOF
[oracle@oracle19c admin]$ cat glogin.sql
--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
--设置编辑器用vi打开，windows客户端可以换成NotePad
define _editor=vi
--设置dbms_output输出缓冲区大小
set serveroutput on size 1000000
--设置输出格式
set long 200
set linesize 500
set pagesize 9999
--去除重定向输出每行拖尾空格
set trimspool on
--设置name列长
col Name format a80
--查询当前实例名
set termout off
col global_name new_value gname
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 _DATE>  '
--设置session时间格式
ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
set termout on
[oracle@oracle19c admin]$

2 部署iotop和htop和iptraf

2.1 iostop

​ iotop是一个用来监视磁盘I/O使用状况的 top 类工具，可监测到哪一个程序使用的磁盘IO的信息（requires 2.6.20 or later),安装简单

直接yum -y install iotop即可。

​ help信息

Controls: left and right arrows to change the sorting column, r to invert the
sorting order, o to toggle the --only option, p to toggle the --processes
option, a to toggle the --accumulated option, i to change I/O priority, q to
quit, any other key to force a refresh.

Options:
  --version             show program's version number and exit
  -h, --help            show this help message and exit
  -o, --only            only show processes or threads actually doing I/O
  -b, --batch           non-interactive mode
  -n NUM, --iter=NUM    number of iterations before ending [infinite]
  -d SEC, --delay=SEC   delay between iterations [1 second]
  -p PID, --pid=PID     processes/threads to monitor [all]
  -u USER, --user=USER  users to monitor [all]
  -P, --processes       only show processes, not all threads
  -a, --accumulated     show accumulated I/O instead of bandwidth
  -k, --kilobytes       use kilobytes instead of a human friendly unit
  -t, --time            add a timestamp on each line (implies --batch)
  -q, --quiet           suppress some lines of header (implies --batch)

各个参数说明：

　　-o, --only只显示正在产生I/O的进程或线程。除了传参，可以在运行过程中按o生效。
　　-b, --batch非交互模式，一般用来记录日志。
　　-n NUM, --iter=NUM设置监测的次数，默认无限。在非交互模式下很有用。
　　-d SEC, --delay=SEC设置每次监测的间隔，默认1秒，接受非整形数据例如1.1。
　　-p PID, --pid=PID指定监测的进程/线程。
　　-u USER, --user=USER指定监测某个用户产生的I/O。
　　-P, --processes仅显示进程，默认iotop显示所有线程。
　　-a, --accumulated显示累积的I/O，而不是带宽。
　　-k, --kilobytes使用kB单位，而不是对人友好的单位。在非交互模式下，脚本编程有用。
　　-t, --time 加上时间戳，非交互非模式。
　　-q, --quiet 禁止头几行，非交互模式。有三种指定方式。
　　-q 只在第一次监测时显示列名
　　-qq 永远不显示列名。
　　-qqq 永远不显示I/O汇总。
交互按键：
　　和top命令类似，iotop也支持以下几个交互按键。
　　left和right方向键：改变排序。　　
　　r：反向排序。
　　o：切换至选项--only。
　　p：切换至--processes选项。
　　a：切换至--accumulated选项。
　　q：退出。
　　i：改变线程的优先级。

简单使用说明

1 只显示正在产生IO的进程
iotop -o 
2 间隔1秒执行10次
iOtop -d 1 -n 10
3 非交互式，输出pid为8382的进程信息
iotop -botq -p 8382

2.2 htop

htop是top的加强版，不在默认的iso中，在epel源中，也可以单独下载源码安装安装。

1 配置epel源
cat >/etc/yum.repos.d/huaweiepel.repo<<EOF
[huaweiepel]
name=epel
baseurl=https://mirrors.huaweicloud.com/epel/7Server/x86_64/
enabled=1
gpgcheck=0
EOF

2 安装htop
[root@oracle19c network-scripts]# yum -y install htop
已加载插件：langpacks, ulninfo
正在解决依赖关系
--> 正在检查事务
---> 软件包 htop.x86_64.0.2.2.0-3.el7 将被 安装
--> 解决依赖关系完成

依赖关系解决

=======================================================================================================================================
 Package                     架构                          版本                                源                                 大小
=======================================================================================================================================
正在安装:
 htop                        x86_64                        2.2.0-3.el7                         huaweiepel                        103 k

事务概要
=======================================================================================================================================
安装  1 软件包

总下载量：103 k
安装大小：218 k
Downloading packages:
htop-2.2.0-3.el7.x86_64.rpm                                                                                     | 103 kB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  正在安装    : htop-2.2.0-3.el7.x86_64                                                                                            1/1
  验证中      : htop-2.2.0-3.el7.x86_64                                                                                            1/1

已安装:
  htop.x86_64 0:2.2.0-3.el7

完毕！

3 使用

[root@oracle19c network-scripts]# htop -h
htop 2.2.0 - (C) 2004-2019 Hisham Muhammad
Released under the GNU GPL.

-C --no-color               Use a monochrome color scheme
-d --delay=DELAY            Set the delay between updates, in tenths of seconds
-h --help                   Print this help screen
-s --sort-key=COLUMN        Sort by COLUMN (try --sort-key=help for a list)
-t --tree                   Show the tree view by default
-u --user=USERNAME          Show only processes of a given user
-p --pid=PID,[,PID,PID...]  Show only the given PIDs
-v --version                Print version info

Long options may be passed with a single dash.

Press F1 inside htop for online help.
See 'man htop' for more information.
[root@oracle19c network-scripts]#

配置

image-20210807113707060

排序

image-20210807113651143

使用：

image-20210807114039712

2.3 iptraf

​ iptraf是一个基于ncurses开发的IP局域网监控工具，它可以实时地监视网卡流量，可以生成各种网络统计数据，包括TCP信息、UDP统计、ICMP和OSPF信息、以太网负载信息、节点统计、IP校验和错误和其它一些信息

安装

[root@oracle19c network-scripts]# yum -y install iptraf
已加载插件：langpacks, ulninfo
正在解决依赖关系
--> 正在检查事务
---> 软件包 iptraf-ng.x86_64.0.1.1.4-7.el7 将被 安装
--> 解决依赖关系完成

依赖关系解决

=======================================================================================================================================
 Package                          架构                          版本                                 源                           大小
=======================================================================================================================================
正在安装:
 iptraf-ng                        x86_64                        1.1.4-7.el7                          base                        300 k

事务概要
=======================================================================================================================================
安装  1 软件包

总下载量：300 k
安装大小：644 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  正在安装    : iptraf-ng-1.1.4-7.el7.x86_64                                                                                       1/1
  验证中      : iptraf-ng-1.1.4-7.el7.x86_64                                                                                       1/1

已安装:
  iptraf-ng.x86_64 0:1.1.4-7.el7

使用

iptraf-ng

image-20210807114523061

image-20210807114734605

界面分上下两部分，上部分可详细显示哪个与之相连的IP，发了多少包，即时流量是多少，下部分，可以显示udp等信息。

3 部署nmon

3.1 下载安装

nmon：http://nmon.sourceforge.net/pmwiki.php 根据自己系统的版本下载相应的版本即可

nmon analyser ：http://nmon.sourceforge.net/pmwiki.php?n=Site.Nmon-Analyser

nmon16m_helpsystems.tar.gz

nmon_analyser_v66.zip

我这里翻不过去墙，因此只能通过epel直接安装

[root@oracle19c network-scripts]# yum -y install nmon
已加载插件：langpacks, ulninfo
正在解决依赖关系
--> 正在检查事务
---> 软件包 nmon.x86_64.0.16g-3.el7 将被 安装
--> 解决依赖关系完成

依赖关系解决

=======================================================================================================================================
 Package                     架构                          版本                                源                                 大小
=======================================================================================================================================
正在安装:
 nmon                        x86_64                        16g-3.el7                           huaweiepel                         70 k

事务概要
=======================================================================================================================================
安装  1 软件包

总下载量：70 k
安装大小：156 k
Downloading packages:
nmon-16g-3.el7.x86_64.rpm                                                                                       |  70 kB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  正在安装    : nmon-16g-3.el7.x86_64                                                                                              1/1
  验证中      : nmon-16g-3.el7.x86_64                                                                                              1/1

已安装:
  nmon.x86_64 0:16g-3.el7

完毕！

2.2 使用nmon交互式

在上面的交互式窗口中，可以使用nmon 快捷键来显示不同的系统资源统计数据：

q : 停止并退出 Nmon
h : 查看帮助
c : 查看 CPU 统计数据
m : 查看内存统计数据
d : 查看硬盘统计数据
k : 查看内核统计数据
n : 查看网络统计数据
N : 查看 NFS 统计数据
j : 查看文件系统统计数据
t : 查看高耗进程
V : 查看虚拟内存统计数据
v : 详细模式

image-20210807120538605

image-20210807120820591

2.3 定时收集

为了配合性能测试，我们往往需要将一个时间段内系统资源消耗情况记录下来，这时可以使用命令在远程窗口执行命令：

mkdir -p /usr/local/src/nmon/logs
nmon -s 1 -c 60 -f -m /usr/local/src/nmon/logs

参数说明：
-s 1 每隔n秒抽样一次，这里为1秒
-c 60 取出多少个抽样数量，这里为60，即监控=1*60/60=1分钟
-f 按标准格式输出文件名称：_YYMMDD_HHMM.nmon
-m 指定监控文件的存放目录，-m后跟指定目录 //如果不指定目录会在该命令的源目录下生成相应的监控文件

不需要去手动干预。如果想停止监控，查询进程号，然后杀死该进程即可。

[root@oracle19c network-scripts]# ps -ef|grep nmon
root     15609     1  0 12:13 pts/7    00:00:00 nmon -s 1 -c 60 -f -m /usr/local/src/nmon/logs
root     15705 10928  0 12:13 pts/7    00:00:00 grep --color=auto nmon
[root@oracle19c network-scripts]#  /usr/local/src/nmon/logs
-bash: /usr/local/src/nmon/logs: 是一个目录
[root@oracle19c network-scripts]# cd  /usr/local/src/nmon/logs
[root@oracle19c logs]# ll
总用量 48
-rw-r--r-- 1 root root 48351 8月   7 12:14 oracle19c_210807_1213.nmon
[root@oracle19c logs]#

2.4 分析

1.将生成的监控文件下载到本地

2.将本地的 nmon_analyser_v66.zip 解压，解压后的文件夹内有.pdf和.xlsm两个文件，我们打开.xlsm文件。

3.点击Analyse nomn data按钮，将下载到本地的监控文件添加进去，最后会生成一个.xlsx文件，这个文件里就是我们需要的数据报告。

image-20210807121738114

4 部署OSW

4.1 安装

默认安装了ahf自动安装了osw，并且自动执行

[oracle@centos7 ~]$ ps -ef|grep osw
oracle    55871      1  0 15:57 ?        00:00:01 /bin/sh ./OSWatcher.sh 30 48 NONE /opt/oracle.ahf/data/repository/suptools/centos7/oswbb/oracle/archive
oracle    56141  55871  0 15:57 ?        00:00:00 /bin/sh ./OSWatcherFM.sh 48 /opt/oracle.ahf/data/repository/suptools/centos7/oswbb/oracle/archive
oracle    99177  96548  0 17:02 pts/11   00:00:00 grep --color=auto osw
[root@centos7 oracle]# find / -name OSWatcher.sh
/opt/oracle.ahf/data/repository/suptools/centos7/oswbb/root/oswbb/OSWatcher.sh
/opt/oracle.ahf/data/repository/suptools/centos7/oswbb/oracle/oswbb/OSWatcher.sh
/opt/oracle.ahf/tfa/ext/oswbb/OSWatcher.sh

$1表示收集间隔，单位为秒，默认为30
$2表示采集的数据的保留时间，单位为小时，默认为48
$3（可选）表示使用何种压缩工具去压缩收集到的数据

4.2启动、配置和关闭OSW

首先，默认的osw采集间隔是30s，保存时间为48h：

ps -ef|grep osw
grid      8378     1  0 Jun27 ?        00:01:11 /bin/sh ./OSWatcher.sh 30 48 NONE /opt/app/grid/oracle.ahf/data/repository/suptools/db01/oswbb/grid/archivegrid      8961  8378  0 Jun27 ?        00:00:27 /bin/sh ./OSWatcherFM.sh 48 /opt/app/grid/oracle.ahf/data/repository/suptools/db01/oswbb/grid/archivegrid     11868 11846  0 23:34 pts/0    00:00:00 grep --color=auto osw

我们将其修改为采集间隔15s，保存时间为168h(也就是7天)：
tfactl stop oswbb
tfactl start oswbb 45 48

重启配置虽然不会丢失，但是oswbb没有自启动，需要手工启动。

4.3分析

OSWatcher Analyzer User Guide (Doc ID 461053.1)

[root@centos7 oswbb]# tfactl 
tfactl> oswbb

Starting OSW Analyzer V8.3.0
OSWatcher Analyzer Written by Oracle Center of Expertise
Copyright (c)  2019 by Oracle Corporation

Parsing Data. Please Wait...

Scanning file headers for version and platform info...

Parsing file centos7.localdomain_pidstat_21.06.24.1500.dat ...
Parsing file centos7.localdomain_pidstat_21.06.24.1600.dat ...
Parsing file centos7.localdomain_pidstat_21.06.24.1700.dat ...

Parsing file centos7.localdomain_iostat_21.06.24.1500.dat ...
Parsing file centos7.localdomain_iostat_21.06.24.1600.dat ...
Parsing file centos7.localdomain_iostat_21.06.24.1700.dat ...
This directory already exists. Rewriting...

Parsing file centos7.localdomain_vmstat_21.06.24.1500.dat ...
Parsing file centos7.localdomain_vmstat_21.06.24.1600.dat ...
Parsing file centos7.localdomain_vmstat_21.06.24.1700.dat ...

Parsing file centos7.localdomain_netstat_21.06.24.1500.dat ...
Parsing file centos7.localdomain_netstat_21.06.24.1600.dat ...
Parsing file centos7.localdomain_netstat_21.06.24.1700.dat ...

Parsing file centos7.localdomain_top_21.06.24.1500.dat ...
Parsing file centos7.localdomain_top_21.06.24.1600.dat ...
Parsing file centos7.localdomain_top_21.06.24.1700.dat ...

Parsing file centos7.localdomain_ps_21.06.24.1500.dat ...
Parsing file centos7.localdomain_ps_21.06.24.1600.dat ...
Parsing file centos7.localdomain_ps_21.06.24.1700.dat ...

Parsing Completed.

Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs
Enter 61 to Display Individual OS Process I/O RPS Graphs
Enter 62 to Display Individual OS Process I/O WPS Graphs
Enter 63 to Display Individual OS Process Percent User CPU Graphs
Enter 64 to Display Individual OS Process Percent System CPU Graphs
Enter 65 to Display Individual OS Process Percent Total CPU (User + System) Graphs
Enter 66 to Display Individual OS Process Percent Memory Graphs

Enter GP to Generate Individual Process Profile
Enter GC to Generate All CPU Gif Files
Enter GM to Generate All Memory Gif Files
Enter GD to Generate All Disk Gif Files
Enter GN to Generate All Network Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter Z to Zoom Graph Time Scale (Does not change analysis dataset)
Enter B to Returns to Baseline Graph Time Scale (Does not change analysis dataset)
Enter R to Remove Currently Displayed Graphs

Enter X to Export Parsed Data to Flat File
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter A to Analyze Data
Enter D to Generate DashBoard

Enter Q to Quit Program

Please Select an Option:Z

Specify Chart Start Time. Valid entry between Jun 24 15:57:58 2021 and Jun 24 17:27:49 2021
Example Format To Enter Time: Jun 24 15:57:58 2021  :

Z可以修改时间间隔

5 TFA工具测试

5.1 介绍

  Oracle在2019年10月18日发布自治健康框架Autonomous Health Framework (AHF) 19.3，将ORAchk，EXAchk，TFA三种诊断工具合并入AHF，作为一个单一的安装软件被称作AHF。AHF可以使用root或者非root用户安装，而且所有的命令行指令和之前版本一样，ORAchk，EXAchk，TFA被放置在AHF_LOC/bin的目录下。

AHF主要功能如下：

1、自动降低风险 自动主动预防性法规遵从性检查，并在最具影响的问题影响您之前发出警告

2、故障时的诊断 确保你总能得到解决问题所需的一切

3、单一诊断接口 不需要学习许多不同工具的命令

4、一个综合框架 所有你需要的诊断工具一起工作

5.2 包含工具

TFA

Primary diagnostic collection tool, with Database Support Tools Bundle

ORAchk / EXAchk

Oracle Stack Compliance Checks

procwatcher

Automate & capture database performance diagnostics & session level hangs

See 459694.1 for more details.

events

Reports warnings and errors seen in the logs

managelogs

Shows disk space usage and purges ADR log and trace files

alertsummary

Provides summary of events for one or more database or ASM alert files from all nodes

ls / dir

Lists all files TFA knows about for a given file name pattern across all nodes

summary

High level summary of the configuration

vi / notepad

Open alert or trace files for viewing a given database and file name pattern in the vi editor

tail

Run a tail on an alert or trace files for a given database and file name pattern

param

Show all database and OS parameters that match a specified pattern

oswatcher

Collect and archive OS metrics, useful for instance / node evictions & performance Issues.

See 301137.1 for more details.

oratop

Near real-time database monitoring

See 1500864.1 for more details.

pstack

Generate process stack for specified processes across all nodes

grep / findstr

Search alert or trace files with a given database and file name pattern, for a search string

dbglevel

Set and unset multiple CRS trace levels with one command

history

Show the shell history for the tfactl shell

changes

Report any noted changes in the system setup over a given time period. This includes database a parameters, OS parameters, patches applied etc

calog

Reports major events from the Cluster Event log

ps / tasklist

Finds processes

triage

Summarize oswatcher/exawatcher data

5.3 安装

最好是root安装可以，这样可以有更多的能力。

[oracle@centos7 sw]$ ./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_211300_35310_2021_06_24-14_41_20.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 21.1.3 Build Date: 202106071249

Default AHF Location : /home/oracle/oracle.ahf

Do you want to install AHF at [/home/oracle/oracle.ahf] ? [Y]|N :

AHF Location : /home/oracle/oracle.ahf

AHF Data Directory : /home/oracle/oracle.ahf/data

Extracting AHF to /home/oracle/oracle.ahf

Configuring TFA in Standalone Mode...

Build Version : 211300  Build Date : 202106071249

Discovering Nodes and Oracle Resources

.----------------------------------------------------------------.
|                  Summary of TFA Configuration                  |
+----------------+-----------------------------------------------+
| Parameter      | Value                                         |
+----------------+-----------------------------------------------+
| TFA Location   | /home/oracle/oracle.ahf/tfa                   |
| Data Directory | /home/oracle/oracle.ahf/data/centos7/tfa      |
| Repository     | /home/oracle/oracle.ahf/data/repository       |
| Diag Directory | /home/oracle/oracle.ahf/data/centos7/diag/tfa |
| Java Home      | /home/oracle/oracle.ahf/jre                   |
'----------------+-----------------------------------------------'

.------------------------------------------------------------------------------------------------.
| Host    | Status of TFA | PID | Port    | Version    | Build ID             | Inventory Status |
+---------+---------------+-----+---------+------------+----------------------+------------------+
| centos7 | RUNNING       | -   | OFFLINE | 21.1.3.0.0 | 21130020210607124914 | COMPLETED        |
'---------+---------------+-----+---------+------------+----------------------+------------------'

AHF is deployed at /home/oracle/oracle.ahf

ORAchk is available at /home/oracle/oracle.ahf/bin/orachk

AHF binaries are available in /home/oracle/oracle.ahf/bin

AHF is successfully installed

Moving /tmp/ahf_install_211300_35310_2021_06_24-14_41_20.log to /home/oracle/oracle.ahf/data/centos7/diag/ahf/

5.4 卸载

To uninstall Oracle Autonomous Health Framework, run the uninstall command as root, or install user.

$ tfactl uninstall
Running the command:

Stops Oracle ORAchk
Stops Oracle Trace File Analyzer
Deletes the Oracle Autonomous Health Framework installation directory

[oracle@centos7 bin]$ ./tfactl uninstall
Starting AHF Uninstall
NOTE : Uninstalling does not return all the space used by the AHF repository
Unable to determine host list from TFA. So running local AHF uninstall.

AHF will be uninstalled on: centos7

Do you want to continue with AHF uninstall ? [Y]|N : y

Stopping AHF service on local node centos7...
Sleeping for 10 seconds...

Stopping TFA Support Tools...

Removing AHF setup on centos7:
Removing /home/oracle/oracle.ahf/rpms
Removing /home/oracle/oracle.ahf/jre
Removing /home/oracle/oracle.ahf/common
Removing /home/oracle/oracle.ahf/bin
Removing /home/oracle/oracle.ahf/python
Removing /home/oracle/oracle.ahf/analyzer
Removing /home/oracle/oracle.ahf/tfa
Removing /home/oracle/oracle.ahf/orachk
Removing /home/oracle/oracle.ahf/ahf
Removing /home/oracle/oracle.ahf/data/centos7
Removing /home/oracle/oracle.ahf/install.properties

[oracle@centos7 bin]$

5.5 配置orachk

To remove auto start:
orachk -autostop
To auto start, run:
orachk -autostart

5.6 升级

执行$ ahf_setup自动会进行升级

5.7 查看工具状态

[oracle@centos7 ~]$ tfactl toolstatus

.------------------------------------------------------------------.
|                   TOOLS STATUS - HOST : centos7                  |
+----------------------+--------------+--------------+-------------+
| Tool Type            | Tool         | Version      | Status      |
+----------------------+--------------+--------------+-------------+
| Development Tools    | orachk       |   20.2.2.0.0 | DEPLOYED    |
|                      | oratop       |       14.1.2 | DEPLOYED    |
+----------------------+--------------+--------------+-------------+
| Support Tools Bundle | darda        | 2.10.0.R6036 | DEPLOYED    |
|                      | oswbb        |        8.3.2 | NOT RUNNING |
|                      | prw          | 12.1.13.11.4 | NOT RUNNING |
+----------------------+--------------+--------------+-------------+
| TFA Utilities        | alertsummary |   20.2.2.0.0 | DEPLOYED    |
|                      | calog        |   20.2.2.0.0 | DEPLOYED    |
|                      | dbcheck      |   18.3.0.0.0 | DEPLOYED    |
|                      | dbglevel     |   20.2.2.0.0 | DEPLOYED    |
|                      | grep         |   20.2.2.0.0 | DEPLOYED    |
|                      | history      |   20.2.2.0.0 | DEPLOYED    |
|                      | ls           |   20.2.2.0.0 | DEPLOYED    |
|                      | managelogs   |   20.2.2.0.0 | DEPLOYED    |
|                      | menu         |   20.2.2.0.0 | DEPLOYED    |
|                      | param        |   20.2.2.0.0 | DEPLOYED    |
|                      | ps           |   20.2.2.0.0 | DEPLOYED    |
|                      | pstack       |   20.2.2.0.0 | DEPLOYED    |
|                      | summary      |   20.2.2.0.0 | DEPLOYED    |
|                      | tail         |   20.2.2.0.0 | DEPLOYED    |
|                      | triage       |   20.2.2.0.0 | DEPLOYED    |
|                      | vi           |   20.2.2.0.0 | DEPLOYED    |
'----------------------+--------------+--------------+-------------'

Note :-
  DEPLOYED    : Installed and Available - To be configured or run interactively.
  NOT RUNNING : Configured and Available - Currently turned off interactively.
  RUNNING     : Configured and Available.

5.8 oratop

12cR1 以后的版本默认在： $Oracle_HOME/suptools/oratop 目录下
unning oratop from within TFA Collector

Use the tfactl CLI to set the database context for oratop, ie., the target database that oratop should connect to, eg.,

$ $TFA_HOME/bin/tfactl db orcl       <== set the target database context in TFA for oratop to use

$ $TFA_HOME/bin/tfactl oratop        <== oratop will connect to the orcl database, no need to worry about any of the oratop pre-reqs, TFA takes care of all that

OR

Use the tfactl shell to set the database context for oratop, ie., the target database that oratop should connect to, eg.,

$ $TFA_HOME/bin/tfactl              <== invoke the tfactl shell prompt

tfactl> db orcl                     <== set the target database context in TFA for oratop to use

Set db to orcl

orcl tfactl> oratop

To run oratop in "batch mode" for a spoecific number os snapshots

$ $TFA_HOME/bin/tfactl

tfactl> db orcl

Set db to orcl

orcl tfactl> oratop -bn10         <== this example would run 10 snapshots at the default 5 second interval unless the interval had been changed eg. oratop -i 10 -bn10

image-20210807122531550

5.9 收集日志

收集2个小时之前的由TFA管理的所有的日志：
#tfactl diagcollect –all –since 2h
收集1天内由TFA管理的所有日志，并压缩存放在本地foo为后缀
#tfactl diagcollect -since 1d -z foo
tfa收集某段时间的日志
$TFA_HOME/bin/tfactl diagcollect -all -from "Jan/21/2013" -to "Jan/21/2013 13:00:00"

6 ADR

1．进入ADRCI

# adrci

2.显示存在的ADR home

adrci>show home

4.设定ADR home

adrci>set home

5.显示所有问题

adrci>show problem

6.显示所有事件

adrci>show incident

7.打包事件的诊断信息

adrci>ips pack incident <incident id>

8.清理日志

purge -age 144000 -type incident

show tracefile

purge -age 8760 -type TRACE

set homepath diag/tnslsnr/hostname/listener

help purge

purge -age 1440 -type alert

7 禁用HAIP

使用root用户执行以下步骤：
停止所有节点的CRS
# crsctl stop has -f
依次在每个节点中执行以下命令
# crsctl start crs -excl -nocrs
# crsctl stop res ora.asm -init
# crsctl modify res ora.cluster_interconnect.haip -attr "ENABLED=0" -init
# crsctl modify res ora.asm -attr \
"START_DEPENDENCIES='hard(ora.cssd,ora.ctssd)pullup(ora.cssd,ora.ctssd)weak(ora.drivers.acfs)',STOP_DEPENDENCIES='hard(intermediate:ora.cssd)'" -init
# crsctl stop has -f
依次在每个节点启动CRS
# crsctl start has
检查HAIP是否禁用
# crsctl stat res -t -init 
确认cluster_interconnect.haip为offline
ifconfig -a 
查看是否还有169.254开头的地址，如果没有了，说明已经禁用成功；如果还有169.254开头的地址，说明没有禁用成功需要进一步排查原因。

8 调整temp，redo，undo

REDO

--查看相关设置 
select member from v$logfile;  
select group#,members,bytes/1024/1024,status from v$log;  
--增加日志组 
alter database add logfile group 4 ('/home/u01/app/oracle/oradata/nwppdb/redo04.log') size 100M;  
alter database add logfile group 5 ('/home/u01/app/oracle/oradata/nwppdb/redo05.log') size 100M;  
alter database add logfile group 6 ('/home/u01/app/oracle/oradata/nwppdb/redo06.log') size 100M;  
--redo每组默认3组每组一个成员，建议每组两个成员以上  
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo04_2.log' to group 4;  
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo05_2.log' to group 5;  
alter database add logfile member '/home/u01/app/oracle/oradata/nwppdb/redo06_2.log' to group 6;  
--切换 
alter system switch logfile;  
--删除之前小的日志组  
alter database drop logfile group 1; 
alter database drop logfile group 2; 
alter database drop logfile group 3；

UNDO

--设置undo_retention 参数，默认是900s ，推荐设置为设置为10800，即3个小时  
show parameter undo_retention; 
alter system set undo_retention=10800 scope=spfile;  
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' resize 2048M; 
Database altered.
SQL>  alter database datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' autoextend on maxsize 4g;
Database altered. 
--查看现在undo表空间大小  
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';

TEMP

-查看临时表空间大小、是否自动扩展 
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;  
--增大临时表空间文件的大小，把20M 调整成10240 M  
 alter database tempfile '/u01/app/oracle/oradata/ytzx/temp01.dbf' resize 10240M autoextend on next 100M maxsize 10G;  
--增加临时文件 
alter tablespace temp add tempfile '/u01/app/oracle/oradata/ytzx/temp02.dbf' size 10240M aitpextemd pm mext 100M maxsize 10G;

9 调整dump大小

alter system set MAX_DUMP_FILE_SIZE='200M';
MAX_DUMP_FILE_SIZE 定义了跟踪文件的最大大小。设置过大会导致使用大量空间，过小导致有问题时无
法记录下跟踪文件。
如果只是一个数值，则定义的是操作系统块数。
如果数值后跟K或者M，则直接定义大小。
如果定义大小时，需要加引号。
alter system set MAX_DUMP_FILE_SIZE='200M';
否则
alter system set MAX_DUMP_FILE_SIZE=200M;
报错
ora-02065:非法选项。

10 调整内存

oltp系统参考(要设置好，防止内存抖动)： sga=内存*80%*80% pga=内存*80%*20%
 show parameter memory;
 show parameter sga;
 show parameter pga;
 --如果要防止高并发情况下的内存抖动,考虑固定内存
 alter system set memory_target=4096m scope=spfile;
 alter system set memory_max_target=4096m scope=spfile;
 alter system set sga_target=3027m scope=spfile;
 alter system set sga_max_size=3027m scope=spfile;
 alter system set pga_aggregate_target=3027m scope=spfile;
 alter system set pga_aggregate_target=1024m scope=spfile;

11 调整连接数

--查看进程及会话数 
show parameter process; 
show parameter sessions; 
--修改进程及会话数 
alter system set processes=1200 scope=spfile;  
alter system set sessions=1325 scope=spfile;  
--查看游标数 
show parameter open_cursors; 
--查看当前打开的游标数目 
select count(*) from v$open_cursor; 
--修改最大游标数 
alter system set open_cursors=1000 scope=both

12 老客户端连接

image-20210807125514554

ORA-28040:No matching authentication protocol 没有匹配的认证协议
在Oracle 19c服务器端的oracle用户下：
cd $ORACLE_HOME/network/admin目录下 新建文件sqlnet.ora
vi sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
不需重启数据库和监听，新的连接就可以正常连接了。
SQLNET.ALLOWED_LOGON_VERSION_SERVER：控制可以连接到19c数据库的客户端版本（client —>oracle 19c db ）
SQLNET.ALLOWED_LOGON_VERSION_CLIENT:控制19c数据库可以连到哪些版本的数据库（oracle 19c db —>其它版本的oracle db），例如：控制通过DB LINK可连接到哪些版本的oracle库。

13 开启归档和自动删除

13.1 开启归档

节点1：
sqlplus / as sysdba
SQL>shutdown immediate;
SQL>startup mount;

节点2：
sqlplus / as sysdba
SQL>shutdown immediate;
SQL>startup mount;

确定都启动到mount状态后，开启归档：
SQL>alter database archivelog;

设置归档目录（归档目录选为共享存储）：
SQL> alter system set log_archive_dest_1='location=+ARCH' scope=both;

查看归档:
SQL>archive log list;

13.2 自动删除脚本

直接参考了Lucifer三思而后行的脚本。

##进入oracle用户
su - oracle
mkdir -p /home/oracle/scripts/
##写入脚本
{
	echo '#!/bin/bash'
    echo 'source ~/.bash_profile'
    echo 'deltime=`date +"20%y%m%d%H%M%S"`'
    echo "rman target / nocatalog msglog /home/oracle/scripts/del_arch_\${deltime}.log<<EOF"
    echo 'crosscheck archivelog all;'
    echo "delete noprompt archivelog until time 'sysdate-7';"
    echo "delete noprompt force archivelog until time 'SYSDATE-10';"
    echo "--delete noprompt backup completed before 'sysdate-40/24';"
    echo 'EOF'
} >>;/home/oracle/scripts/del_arch.sh
chmod +x /home/oracle/scripts/del_arch.sh

cat >>EOF<</var/spool/cron/oracle
12 00 * * * /home/oracle/scripts/del_arch.sh
EOF

##手动执行测试
su - oracle
/home/oracle/scripts/del_arch.sh

14 AWR调整

sys@ORCL 10:26:25>
col SNAP_INTERVAL for a20
col RETENTION for a20
sys@ORCL 10:26:31> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1605941334 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT             0 1605941334 CDB$ROOT
sys@ORCL 10:26:32>
sys@ORCL 10:26:41>
BEGIN
   DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
     interval  =>  30,
     retention =>  14*24*60);
   END;
  5    6     /
PL/SQL procedure successfully completed.
sys@ORCL 10:26:49> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID   SRC_DBID SRC_DBNAME
---------- -------------------- -------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1605941334 +00000 00:30:00.0    +00014 00:00:00.0    DEFAULT             0 1605941334 CDB$ROOT
sys@ORCL 10:26:55>

15 字符集调整

1 首先19c支持cdb和pdb的字符集不一致的
2 具体调整的过程：
alter session set container=pdbsample;
select userenv('language') from dual;
alter pluggable database pdbsample close immediate;
alter pluggable database pdbsample open restricted;
alter database character set INTERNAL_CONVERT AL32UTF8;
alter pluggable database pdbsample close immediate;
alter pluggable database pdbsample open;
或：
alter system enable restricted session;
alter database character set INTERNAL_CONVERT ZHS16GBK;
alter system DISABLE restricted session;



12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)

16 审计关闭

-考虑关闭审计（oracle 11g 默认打开审计）
 alter system set audit_trail=none sid='*' scope=spfile;
 --说明：11g 默认打开数据库审计，为了避免审计带来的 SYSTEM 表空间的过多占用，可以关闭
 --最近一年审计记录
 select * from aud$ where substr(sysdate-NTIMESTAMP#,2,9)<360;
 --清理数据库审计
 truncate table sys.aud$ reuse storage;
 alter table sys.aud$ deallocate unused keep 25000m;
 alter table sys.aud$ deallocate unused keep 20000m;
 alter table sys.aud$ deallocate unused keep 15000m;
 alter table sys.aud$ deallocate unused keep 10000m;
 alter table sys.aud$ deallocate unused keep 5000m;
 alter table sys.aud$ deallocate unused keep 2000m;
 alter table sys.aud$ deallocate unused keep 500m;
 alter table sys.aud$ deallocate unused keep 250m;
 alter table sys.aud$ deallocate unused keep 10m;
 --查看SYSTEM表空间占用空间排名前10的段是否有AUD以及FGA_LOG
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = ‘SYSTEM’
ORDER BY BYTES DESC)
WHERE ROWNUM < 10;

17 参数调整

"alter system set control_files='+DATADG/devdb/controlfile/ current.256.852038305','+DATADG','+DATADG'
scope=spfile;"
alter system set recyclebin=on scope=spfile;
alter system set undo_retention=5400 scope=spfile;
alter system set processes=2000 scope=spfile;
alter system set db_files=2000 scope=spfile;
alter system set audit_trail=none scope=spfile;
alter system set memory_max_target=0 scope=spfile;
alter system set memory_target=0 scope=spfile;
alter system set sga_max_size=80g scope=spfile;
alter system set sga_target=80g scope=spfile;
alter system set pga_aggregate_target=20g scope=spfile;
alter system set max_dump_file_size=104857600 scope=spfile;
alter system set parallel_force_local=TRUE scope=spfile;
alter system set control_file_record_keep_time=31 scope=spfile;
alter system set parallel_max_servers=96 scope=spfile;
alter system set parallel_min_servers=0 scope=spfile;
alter system set open_cursors=1000 scope=spfile;
alter system set utl_file_dir='/tmp' scope=spfile;
alter system set resource_manager_plan='FORCE:' scope=spfile;
alter system set deferred_segment_creation=FALSE scope=spfile;
alter system set fast_start_parallel_rollback='LOW' scope=spfile;
alter system set pga_aggregate_limit=0 scope=spfile;
alter system set optimizer_adaptive_features=FALSE scope=spfile;
alter system set sec_case_sensitive_logon=FALSE scope=spfile;
alter system set temp_undo_enabled=FALSE scope=spfile;
alter system set session_cached_cursors=300 scope=spfile;
alter system set max_shared_servers=0 scope=spfile;
alter system set shared_servers=0 scope=spfile;
alter system set sec_max_failed_login_attempts=100 scope=spfile;
alter system set open_links=40 scope=spfile;
alter system set open_links_per_instance=40 scope=spfile;
alter system set result_cache_max_size=0 scope=spfile;
alter system set enable_ddl_logging=true sid='*';

18 隐含参数调整

不建议随意修改，根据实际情况处理

稳定性：
-- RAC DRM：
alter system set "_gc_policy_time"=0 sid='*' scope=spfile;
alter system set "_gc_undo_affinity"=FALSE sid='*' scope=spfile;
 --数据文件出现I/O问题时，不强制将实例crash
alter system set "_datafile_open_errors_crash_instance"=false  sid='*';
alter system set "_datafile_write_errors_crash_instance"=false sid='*' scope=spfile;
--当_clusterwide_global_transactions=false时，ORACLE会将这些本地事务当做单独的事务通过多阶段提交协调处理。
alter system set "_clusterwide_global_transactions"=FALSE  scope=spfile;
--undo自动管理模式
alter system set "_smu_debug_mode"=134217728;

--10949（12c）：Bug 18498878 - medium size tables do not cached consistently (文档 ID 18498878.8)
--28401：High 'library cache lock' Wait Time Due to Invalid Login Attempts (文档 ID 1309738.1)
--10849：Core dump - Access Violation in Client Applications After Upgrade to 9.2.0.8, 10.1.0.5 , 10.2.0.x 11.1 and the client or server is still a prior version (文档 ID 455832.1)
19823：
ALTER SYSTEM SET event='10949 trace name context forever:28401 trace name context forever,level 1:10849 trace name context forever, level 1:19823 trace name context forever, level 90' scope=spfile;

性能相关：
--禁用自适应游标共享
alter system set "_optimizer_extended_cursor_sharing"='NONE' sid='*';
alter system set "_optimizer_extended_cursor_sharing_rel"='NONE'  sid='*';
alter system set "_optimizer_adaptive_cursor_sharing"=FALSE  sid='*';
--Cardinality Feedback，Cardinality 关闭
alter system set "_optimizer_use_feedback"=false               sid='*';
--Adaptive Log File Sync关闭
alter system set "_use_adaptive_log_file_sync"=false sid='*' scope=spfile;

19 关闭必要的OCM

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

20 关闭CHM

每个节点，GRID用户下执行。
crsctl stop res ora.crf -init
crsctl modify res ora.crf -attr ENABLED=0 -init  
Oracle RAC 12.1+，任一节点GRID用户执行。
srvctl stop mgmtdb
srvctl disable mgmtdb
srvctl stop listener -l MGMTLSNR
srvctl disable listener -l MGMTLSNR
删除CHM数据库和监听，在GRID用户执行
dbca -silent -deleteDatabase -sourceDB -MGMTDB
srvctl remove mgmtdb
srvctl remove listener -l MGMTLSNR

21 tnsname的配置参考

sbc=
   (DESCRIPTION=
     (LOAD_BALANCE=on) 
     (FAILOVER=on) 
     (ADDRESS=(PROTOCOL=tcp)(HOST= 172.28.206.4)(PORT=1521)) 
     (ADDRESS=(PROTOCOL=tcp)(HOST= 172.28.206.5)(PORT=1521)) 
         (CONNECT_DATA=
              (SERVICE_NAME=sbc) 
              (FAILOVER_MODE=
                  (TYPE=select) 
                  (METHOD=basic)
              )
         )



JDBC连接串（一整行）：       
(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=on)(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.206.4)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=172.28.206.5)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sbc)(FAILOVER_MODE=(TYPE=select)(METHOD=basic))))

22 调整用户profile

alter profile "DEFAULT" limit PASSWORD_GRACE_TIME UNLIMITED;

alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED;

alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED;

alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

23 调整密码大小写

--关闭密码大小写敏感：
alter system set sec_case_sensitive_logon=false scope=spfile sid='*';
--延迟登录
在 Oracle 11g 中，为了提升安全性，Oracle 引入了『密码延迟验证』的新特性。这个特性的作用是，如果用户输入了错误的密码尝试登录，那么随着登录错误次数的增加，每次登录前验证的时间也会增加，以此减缓可能对于数据库重复的口令尝试攻击。

但是对于正常的系统，由于口令的更改，可能存在某些被遗漏的客户端，不断重复尝试，从而引起数据库内部长时间的 Library Cache Lock的等待，这种情形非常常见。

如果遇到这一类问题，可以通过Event 28401关闭这个特性，从而消除此类影响，以下命令将修改设置在参数文件中：

ALTER SYSTEM SET EVENT =

 '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE;

 在Oracle的11g中是常见和确定的，在MOS上可以找到相应的记录：High 'library cache lock' Wait Time Due to Invalid Login Attempts(1309738.1）此外Oracle 11g开启了密码大小写验证，如果从Oracle 10g升级过来，需要特别的当心这个变化，通过初始化参数SEC_CASE_SENSITIVE_LOGON 可以来控制这个特性
oracle19c安装部署
最后修改时间：2021-08-08 22:22:45
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Murkey的Oracle回顾之旅（共49篇）
多年的工作和学习，一些文档和blog的汇总和分享。

评论