一次生产库案例_deferredlog-CSDN博客

本文链接：https://blog.csdn.net/techsupporter/article/details/52098695

环境如下：

---操作系统

[oracle@jkda archbak]$ cat /proc/version
Linux version 2.6.18-194.el5 (mockbuild@x86-005.build.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-48)) #1 SMP Tue Mar 16 21:52:39 EDT 2010
[oracle@jkda archbak]$ lsb_release -a
LSB Version: :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Release: 5.5
Codename: Tikanga
[oracle@jkda archbak]$

---数据库

SQL> select * from v$version;

BANNER
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

--问题:

第1天:

DG环境有问题，主库因空间不够改为noarchivelog模式，主库删除归档日志删除不了

DELETE ARCHIVELOG ALL COMPLETED BEFORE'SYSDATE-7';

RMAN-08137:警告：归档日志未删除，因为备用或上游捕获进程需要它

归档日志文件名：/oradata/archlog/ORCL/archivelog/2016_07_30/o1_mf_1_42614_.dbf

解决：修改_deferred_log_dest_is_valid"为FALSE，此参数修改无需重启数据库

SOLUTION

As long as the Archive Destination (log_archive_dest_n) is stillset, we consider the Standby Database as still existing and preserve theArchiveLogs on the Primary Database to perform Gap Resolution when the ArchiveDestination is valid again.There are Situations when this is not wanted, eg.the Standby Database was activated or removed but you still keep the ArchiveDestination

because you want to rebuild the Standby Database later again. Inthis Case you can set the hidden Parameter"_deferred_log_dest_is_valid" to FALSE (default TRUE) which willconsider deferred Archive Destinations as completely unavailable and will notpreserve ArchiveLogs for those Destinations any more. It is a dynamic Parameterand can be set this Way:

SQL>alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

NOTE: This Parameter has been introduced with Oracle Database11.2.0.x. In earlier Versions you have to unset thelog_archive_dest_n-Parameter pointing to the remote Standby Database to

make the Primary Database considering it as completelyunavailable. There also exists a Patch on Top of 11.1.0.7 for some Platforms toinclude this Parameter in 11.1.0.7, too.

This is Patch Number 8468117.

主库删除日志后，备库跑脚本拷贝主库，具体见http://blog.csdn.net/techsupporter/article/details/52089066

第2天:

添加3组日志，每组750M，增加到5组日志

SQL> alter database add logfile group 3 '/opt/oracle/oradata/orcl/redo03.log' size 750M;
Database altered.
SQL> alter database add logfile group 4 '/opt/oracle/oradata/orcl/redo05.log' size 750M;
Database altered.
SQL> alter database add logfile group 5 '/opt/oracle/oradata/orcl/redo06.log' size 750M;
Database altered.

（具体修改日志大小的操作不细写了，注意drop的时候要INACTIVE状态

最后结果：

SQL> SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
GROUP# MEMBERS BYTE_MB STATUS
---------- ---------- ---------- --------------------------------
3 1 750 CURRENT
4 1 750 ACTIVE
5 1 750 ACTIVE

修改了之后，还是归档太频繁了，证据如下：

SQL> select * from (select to_char(trunc(first_time, 'HH'),'yyyy-mm-dd hh24') time, count(*)
from v$loghist group by to_char(trunc(first_time, 'HH'),'yyyy-mm-dd hh24') order by time desc)
where to_date(time,'yyyy-mm-dd hh24')>sysdate-7;

TIME COUNT(*)
-------------------------- ----------
2016-08-02 22 2
2016-08-02 21 18
2016-08-02 20 21
2016-08-02 19 22
2016-08-02 18 21
2016-08-02 17 23
2016-08-02 16 22
2016-08-02 15 22
2016-08-02 14 22
2016-08-02 13 22
2016-08-02 12 23

TIME COUNT(*)
-------------------------- ----------
2016-08-02 11 285
2016-08-02 10 500
2016-08-02 09 450
2016-08-02 08 490
2016-08-02 07 577
2016-08-02 06 578
2016-08-02 05 576
2016-08-02 04 578
2016-08-02 03 580
2016-08-02 02 579
2016-08-02 01 571

TIME COUNT(*)
-------------------------- ----------
2016-08-02 00 568
2016-08-01 23 544
2016-08-01 22 547
2016-08-01 21 568
2016-08-01 20 572
2016-08-01 19 490
2016-08-01 18 430
2016-08-01 17 48
2016-08-01 16 476
2016-08-01 15 573
2016-08-01 14 574

TIME COUNT(*)
-------------------------- ----------
2016-08-01 13 572
2016-08-01 12 575
2016-08-01 11 575
2016-08-01 10 548
2016-08-01 09 489
2016-08-01 08 530
2016-08-01 07 576
2016-08-01 06 576
2016-08-01 05 572
2016-08-01 04 576
2016-08-01 03 576

TIME COUNT(*)
-------------------------- ----------
2016-08-01 02 576
2016-08-01 01 575
2016-08-01 00 573
2016-07-31 23 555
2016-07-31 22 556
2016-07-31 21 569
2016-07-31 20 557
2016-07-31 19 575
2016-07-31 18 576
2016-07-31 17 575
2016-07-31 16 575

TIME COUNT(*)
-------------------------- ----------
2016-07-31 15 575
2016-07-31 14 575
2016-07-31 13 571
2016-07-31 12 568
2016-07-31 11 576
2016-07-31 10 525
2016-07-31 09 300
2016-07-31 08 156
2016-07-30 21 371
2016-07-30 20 297
2016-07-30 16 177

TIME COUNT(*)
-------------------------- ----------
2016-07-30 15 578
2016-07-30 14 572
2016-07-30 13 578
2016-07-30 12 579
2016-07-30 11 578
2016-07-30 10 569
2016-07-30 09 579
2016-07-30 08 561
2016-07-30 07 580
2016-07-30 06 574
2016-07-30 05 578

TIME COUNT(*)
-------------------------- ----------
2016-07-30 04 579
2016-07-30 03 580
2016-07-30 02 580
2016-07-30 01 577
2016-07-30 00 575
2016-07-29 23 572
2016-07-29 22 512
2016-07-29 21 455
2016-07-29 20 523
2016-07-29 19 578
2016-07-29 18 573

TIME COUNT(*)
-------------------------- ----------
2016-07-29 17 558
2016-07-29 16 465
2016-07-29 15 55
2016-07-29 14 128
2016-07-29 13 126
2016-07-29 12 126
2016-07-29 11 129
2016-07-29 10 127
2016-07-29 09 127
2016-07-29 08 132
2016-07-29 07 133

TIME COUNT(*)
-------------------------- ----------
2016-07-29 06 134
2016-07-29 05 132
2016-07-29 04 133
2016-07-29 03 130
2016-07-29 02 125
2016-07-29 01 128
2016-07-29 00 124
2016-07-28 23 128
2016-07-28 22 126
2016-07-28 21 128
2016-07-28 20 132

TIME COUNT(*)
-------------------------- ----------
2016-07-28 19 137
2016-07-28 18 137
2016-07-28 17 138
2016-07-28 16 139
2016-07-28 15 138
2016-07-28 14 140
2016-07-28 13 145
2016-07-28 12 139
2016-07-28 11 141
2016-07-28 10 144
2016-07-28 09 144

TIME COUNT(*)
-------------------------- ----------
2016-07-28 08 148
2016-07-28 07 150
2016-07-28 06 155
2016-07-28 05 150
2016-07-28 04 162
2016-07-28 03 161
2016-07-28 02 175
2016-07-28 01 179
2016-07-28 00 178
2016-07-27 23 172
2016-07-27 22 188

TIME COUNT(*)
-------------------------- ----------
2016-07-27 21 204
2016-07-27 20 194
2016-07-27 19 222
2016-07-27 18 235
2016-07-27 17 258
2016-07-27 16 255
2016-07-27 15 266
2016-07-27 14 23
2016-07-27 13 21
2016-07-27 12 10
2016-07-27 11 14

TIME COUNT(*)
-------------------------- ----------
2016-07-27 10 16
2016-07-27 09 4
2016-07-27 08 1
2016-07-27 07 1
2016-07-27 05 1
2016-07-27 01 13
2016-07-27 00 13
2016-07-26 23 2

1
SQL>

[root@jkda archivelog]# pwd
/oradata/archlog/ORCL/archivelog
[root@jkda archivelog]# ll|grep 08-02|wc
3998 31984 295852

以下是归档大小：有35M的，还有改了之后有500M-600M
[root@jkda archivelog]# du -sh *|sort

35M 1_452928_849881221.dbf
35M 1_452932_849881221.dbf
35M 1_452951_849881221.dbf
36M 1_452847_849881221.dbf
36M 1_452881_849881221.dbf
37M 1_452283_849881221.dbf
37M 1_452716_849881221.dbf
494M 1_453772_849881221.dbf
536M 1_453762_849881221.dbf
539M 1_453576_849881221.dbf
542M 1_453795_849881221.dbf
544M 1_453791_849881221.dbf
545M 1_453786_849881221.dbf
546M 1_453559_849881221.dbf
546M 1_453677_849881221.dbf
546M 1_453716_849881221.dbf
546M 1_453792_849881221.dbf
548M 1_453568_849881221.dbf
548M 1_453581_849881221.dbf
548M 1_453681_849881221.dbf
548M 1_453747_849881221.dbf
549M 1_453673_849881221.dbf

解决措施：修改cpu_count参数

--- 查看

SQL> select name,type,value,display_value from v$parameter where name like'%cpu%';

NAME TYPE VALUE DISPLAY_VALUE
----------------------------------- ---------- ---------- ------------------------------
cpu_count 3 48 48
resource_manager_cpu_allocation 3 48 48
parallel_threads_per_cpu 3 2 2

SQL>alter system set cpu_count=24;

第3天上午突然数据hang住了，这个问题后来自动好了，可能是压力释放之后，具体原因还没找到，于是开始排查

查看占用内存资料最多的进程

[root@jkda archivelog]# ps auxw|head -1;ps auxw|sort -rn -k4|head -10
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
oracle 11880 88.5 15.9 23314004 5254104 ? Rs Aug01 2350:22 ora_j000_orcl
oracle 11814 0.0 13.9 23293364 4598320 ? Ss Aug01 2:26 ora_dbw1_orcl
oracle 11812 0.0 13.7 23291316 4523540 ? Ss Aug01 2:28 ora_dbw0_orcl
oracle 11822 0.0 13.6 23293364 4485820 ? Ss Aug01 2:37 ora_dbw5_orcl
oracle 11820 0.0 13.6 23293364 4507984 ? Ss Aug01 2:26 ora_dbw4_orcl
oracle 11818 0.0 13.6 23293364 4501820 ? Ss Aug01 2:20 ora_dbw3_orcl
oracle 11816 0.0 13.6 23291316 4480124 ? Ss Aug01 2:21 ora_dbw2_orcl
oracle 11810 0.0 7.0 23280952 2323764 ? Ss Aug01 0:14 ora_mman_orcl
oracle 1610 10.1 5.3 23302788 1749584 ? Rs 11:01 12:15 oracleorcl (LOCAL=NO)
oracle 1582 8.0 5.2 23287980 1720292 ? Ss 10:59 9:59 oracleorcl (LOCAL=NO)
找到了进程号11880 发现是ora_j000_orcl

通过系统进程号找到数据库对的进程的addr

SQL> select addr from v$process where spid = '11880';

ADDR
----------------
0000000500DE18C0

通过addr打开sql_id
SQL> select sql_id from v$session where paddr= '0000000500DE18C0';

ADDR
----------------
0000000500DE18C0

SQL> select sql_id from v$session where paddr= '0000000500DE18C0';

6h70h7v2bufrp

SQL> select sql_text from v$sql where sql_id='6h70h7v2bufrp';

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN get_qysj.jxkh_exec_index; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

SQL>

查看job运行

select * from dba_jobs_running; 始终是361这个session 在运行job

----查看锁

SQL> select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b where b.object_id=l.object_id;

OWNER OBJECT_NAME SESSION_ID LOCKED_MODE
--------------------------------------------------------------------------------------------------------------------------------------------------------------
JKDA YXQYJCSJB 361 3

-----查看session对应的session 和sql_id
SQL> select sid,status,sql_id from v$session where sid=361;

SID STATUS SQL_ID
---------- ---------------- --------------------------
361 ACTIVE 9cvwzutvsfr5q

----查看sql_id对的sql

SQL> select sql_text from v$sql where sql_id='9cvwzutvsfr5q';

SQL_TEXT
----------------------------------------------------------------------------------------------------
UPDATE YXQYJCSJB JB SET JB.QYYSZCCS=:B2 WHERE JB.SFZH=:B1
UPDATE YXQYJCSJB JB SET JB.QYYSZCCS=:B2 WHERE JB.SFZH=:B1
UPDATE YXQYJCSJB JB SET JB.QYYSZCCS=:B2 WHERE JB.SFZH=:B1

SQL>

SQL> select sql_id, name, datatype_string, case datatype when 180 then to_char(ANYDATA.accesstimestamp(t.value_anydata), 'YYYY/MM/DD HH24:MI:SS') else t.value_string end as bind_value, last_captured from gv$sql_bind_capture t where sql_id = '9cvwzutvsfr5q';

SQL> /

SQL_ID NAME DATATYPE_STRING BIND_VALUE LAST_CAPTURED
-------------------------- ---------- ------------------------------------------------------------ ------------------------------ --------------
9cvwzutvsfr5q :B2 NUMBER
9cvwzutvsfr5q :B1 VARCHAR2(128) 330333333333333725 03-816
9cvwzutvsfr5q :B2 NUMBER
9cvwzutvsfr5q :B1 VARCHAR2(128) 330333333333333725 03-816
9cvwzutvsfr5q :B2 NUMBER
9cvwzutvsfr5q :B1 VARCHAR2(128) 330333333333333725 02-816

SQL>