Oracle 12c DG库trace目录增长过快解决方案

160次阅读

共计 6392 个字符，预计需要花费 16 分钟才能阅读完成。

Oracle 版本为 12c R2：搭建完成 DG 库有 trace 目录中日志增长过快，其中一类 mmon 的日志中主要为如下内容

AUTO SGA: kmgs_parameter_update_timeout gen0 0 mmon alive 1

需要安装补丁 (Doc ID 2298766.1)

另一类为 ORA-19527 和 ORA-312 报错，需要设置 log_file_name_convert

一、补丁安装
1. 解压并进入到补丁目录
[oracle@oratest u02]$ cd software/
[oracle@oratest software]$ ll
total 340
-rw-r–r– 1 oracle oinstall 344963 Feb 12 10:31 p25415713_122010_Linux-x86-64.zip
[oracle@oratest software]$ unzip p25415713_122010_Linux-x86-64.zip
Archive: p25415713_122010_Linux-x86-64.zip
creating: 25415713/
creating: 25415713/etc/
creating: 25415713/etc/config/
inflating: 25415713/etc/config/inventory.xml
inflating: 25415713/etc/config/actions.xml
inflating: 25415713/README.txt
creating: 25415713/online/
creating: 25415713/online/etc/
creating: 25415713/online/etc/xml/
inflating: 25415713/online/etc/xml/ShiphomeDirectoryStructure.xml
inflating: 25415713/online/etc/xml/GenericActions.xml
creating: 25415713/online/etc/config/
inflating: 25415713/online/etc/config/actions.xml
inflating: 25415713/online/etc/config/deploy.xml
inflating: 25415713/online/etc/config/inventory.xml
creating: 25415713/online/files/
creating: 25415713/online/files/hpatch/
inflating: 25415713/online/files/hpatch/bug25415713.pch
creating: 25415713/files/
creating: 25415713/files/lib/
creating: 25415713/files/lib/libserver12.a/
inflating: 25415713/files/lib/libserver12.a/kmgs.o
inflating: PatchSearch.xml
[oracle@oratest software]$
[oracle@oratest software]$ ll
total 348
drwxrwxr-x 5 oracle oinstall 4096 Jul 20 2017 25415713
-rw-r–r– 1 oracle oinstall 344963 Feb 12 10:31 p25415713_122010_Linux-x86-64.zip
-rw-rw-r– 1 oracle oinstall 2510 Jul 20 2017 PatchSearch.xml
[oracle@oratest software]$ cd 25415713/
[oracle@oratest 25415713]$ ll
total 24
drwxrwxr-x 3 oracle oinstall 4096 Jul 20 2017 etc
drwxrwxr-x 3 oracle oinstall 4096 Jul 20 2017 files
drwxr-xr-x 4 oracle oinstall 4096 Jul 20 2017 online
-rw-rw-r– 1 oracle oinstall 10021 Jul 20 2017 README.txt
[oracle@oratest 25415713]$ pwd
/u02/software/25415713

2，在线安装补丁
[oracle@oratest 25415713]$ ps -ef |grep smon
oracle 22391 1 0 09:46 ? 00:00:00 ora_smon_ora12c
oracle 23245 22783 0 09:53 pts/1 00:00:00 grep smon

实例名:sys:sys 的密码: – 注意这里有 3 个冒号
[oracle@oratest 25415713]$ opatch apply online -connectString ora12c:sys:oracle:
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation. All rights reserved.

Oracle Home : /u02/oracle/product/db12cr2
Central Inventory : /u01/oraInventory
from : /u02/oracle/product/db12cr2/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u02/oracle/product/db12cr2/cfgtoollogs/opatch/opatch2018-05-03_09-52-29AM_1.log

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 25415713

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files…

The patch should be applied/rolled back in ‘-all_nodes’ mode only.
Converting the RAC mode to ‘-all_nodes’ mode.
Applying interim patch ‘25415713’ to OH ‘/u02/oracle/product/db12cr2’

Patching component oracle.rdbms, 12.2.0.1.0…
Installing and enabling the online patch ‘bug25415713.pch’, on database ‘ora12c’.

Patch 25415713 successfully applied.
Log file location: /u02/oracle/product/db12cr2/cfgtoollogs/opatch/opatch2018-05-03_09-52-29AM_1.log

OPatch succeeded.
[oracle@oratest 25415713]$
[oracle@oratest 25415713]$ opatch lsinv
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2018, Oracle Corporation. All rights reserved.

Oracle Home : /u02/oracle/product/db12cr2
Central Inventory : /u01/oraInventory
from : /u02/oracle/product/db12cr2/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u02/oracle/product/db12cr2/cfgtoollogs/opatch/opatch2018-05-03_09-52-47AM_1.log

Lsinventory Output file location : /u02/oracle/product/db12cr2/cfgtoollogs/opatch/lsinv/lsinventory2018-05-03_09-52-47AM.txt

——————————————————————————–
Local Machine Information::
Hostname: oratest
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 (online) 25415713: applied on Thu May 03 09:52:36 CST 2018
Unique Patch ID: 21399806
Created on 19 Jul 2017, 19:55:08 hrs PST8PDT
Bugs fixed:
25415713

——————————————————————————–

OPatch succeeded.

二、清理备库 redo

DG 备库中的日志大量增长，其中有 ORA-19527 和 ORA-312 可以按以下方案处理

注意：本操在备库操作，禁止在主库清理 online redo

1. 确认为 spfile 启动的数据库并备份
SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u02/oracle/product/db12cr2/db
s/spfileora12c.ora

SQL> create pfile from spfile;

File created.

2. 确认主备 online redo 位置
SQL> select MEMBER from v$logfile;

MEMBER
——————————————————————————–
/u02/datafile/ora12c/redo01.log
/u02/datafile/ora12c/redo02.log
/u02/datafile/ora12c/redo03.log

3. 确认并修改参数
SQL> show parameter log_file_name_convert

NAME TYPE VALUE
———————————— ———– ——————————
log_file_name_convert string

SQL> alter system set log_file_name_convert = ‘/u02/datafile/ora12c’,’/u02/datafile/ora12c’ scope=spfile;

System altered.

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 8793352 bytes
Variable Size 553648888 bytes
Database Buffers 1073741824 bytes
Redo Buffers 7983104 bytes
Database mounted.
\SQL> show parameter log_file_name_convert

NAME TYPE VALUE
———————————— ———– ——————————
log_file_name_convert string /u02/datafile/ora12c, /u02/dat
afile/ora12c

如果使用了 pfile，则在文本文件中修改，并重启数据库!
*.log_file_name_convert = ‘/xxxx/xxxx’,’/xxxx/xxxx’

4. 执行如下命令清空备库上所有的日志组
SQL>alter database clear logfile group 1;
SQL>alter database clear logfile group 2;
SQL>alter database clear logfile group 3;
SQL>alter database clear logfile group 4;
SQL>alter database clear logfile group 5; —- 有几组 online 则都清理掉

5. 检查 v$log 视图确认日志文件的状态和大小
SQL> select group#,thread#,bytes/1024/1024 mb ,status from v$log; —- status 下全部为 UNUSED

6. 打开备库日志应用
alter database open read only;

alter database recover managed standby database using current logfile disconnect from session;

7. 检查备库同步状态
select process,status,sequence#,DELAY_MINS from v$managed_standby;

正文完

星哥说事-微信公众号