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;
来源: http://www.linuxidc.com/Linux/2019-05/158738.htm