Yearly Archives: 2013

Process J000 is dead 报错

1

Posted on by

今天数据库后台报错:

Thu Dec 26 02:00:00 2013
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Thu Dec 26 02:06:36 2013
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_21975.trc:
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_21975.trc:
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_21975.trc:
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_21975.trc:
Thu Dec 26 02:06:46 2013
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_21975.trc:
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_cjq0_21975.trc:
Process J000 died, see its trace file

 

 

查了下官方文档,说系统资源耗尽完:

 

CAUSE

This is caused by lack of OS resources or incorrect configuration, where more memory is required and Operating system reaches the limits set.

SOLUTION

* For Unix Platforms:

– run the HCVE script found in NOTE.250262.1 which is responsible to check for the OS resources and perform the suggested changes.

* For Windows platforms, 32 bit architecture:

– enable the /3GB switch in the boot.ini file, following the steps mentioned in Note 225349.1 – Address Windowing Extensions (AWE) or VLM on Windows Platforms

 

果然top一看,有一个占用将近8个cpu的进程,该机器一共就8个cpu;然后直接kill掉后,观察一切正常了。

top - 10:12:11 up 23 days, 16:39,  3 users,  load average: 7.99, 7.87, 7.89
Tasks: 296 total,   1 running, 295 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.1%sy, 99.8%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8110496k total,  5220400k used,  2890096k free,   135824k buffers
Swap:  8191992k total,    17660k used,  8174332k free,  4289168k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                   
31308 root      20   0  760m  11m  888 S 798.1  0.1   3801:42 hald-daemon -B -c /etc/hald-daemon.conf                                                                  
24170 oracle    -2   0 3829m  16m  14m S  1.0  0.2   0:09.45 ora_vktm_orcl                                                                                            
24974 oracle    20   0 15168 1356  908 R  0.7  0.0   0:00.23 top                                   

 

USE Udev for Oracle ASM

0

Posted on by

   下面说明RHEL5和RHEL6通过UUID来绑udev

 

RHEL5:

1.检查一下要绑那几块盘

ll /dev/sd*

 

2.生成脚本

for i in b c d e ;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -s %p\", RESULT==\"`scsi_id -g -u -s /block/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /tmp/99-oracle-asmdevices.rules
done

 

3.检查一下99-oracle-asmdevices.rules

cat /tmp/99-oracle-asmdevices.rules

 

4.copy到udev目录下(rac的话直接copy这个文件到另一个节点上)

cp /tmp/99-oracle-asmdevices.rules /etc/udev/rules.d/99-oracle-asmdevices.rules

 

重启udev

start_udev

 

 

RHEL6:

1.检查一下要绑那几块盘

ll /dev/sd*

 

 

2.生成脚本

for i in b c d e ;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id -g -u -d /dev/$name\", RESULT==\"`scsi_id -g -u -d /dev/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /tmp/99-oracle-asmdevices.rules
done

for i in b c d e ;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\", RESULT==\"`/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\""      >> /tmp/99-oracle-asmdevices.rules
done

 

3.检查一下99-oracle-asmdevices.rules

cat /tmp/99-oracle-asmdevices.rules

 

4.copy到udev目录下(rac的话直接copy这个文件到另一个节点上)

cp /tmp/99-oracle-asmdevices.rules /etc/udev/rules.d/99-oracle-asmdevices.rules

 

重启udev

start_udev

 

udev管理

1.Test the rules are working as expected.(udevtest)

# #OL5
# udevtest /block/sdb
# udevtest /block/sdc
# udevtest /block/sdd
# udevtest /block/sde

# #OL6
# udevadm test /block/sdb
# udevadm test /block/sdc
# udevadm test /block/sdd
# udevadm test /block/sde

 

2.restart the UDEV service.

# #OL5
# /sbin/udevcontrol reload_rules

# #OL6
# udevadm control --reload-rules

# #OL5 and OL6
# /sbin/start_udev

 

3.udev info

# #OL5
# udevinfo -q all -n /dev/sdb

# #OL6
# udevadm info --query=all --path=/sys/block/sdb
# udevadm info --query=all --name=asm-diska

ASM discovered an insufficient number of disks for diskgroup “DATA” 错误

0

Posted on by

在asmca的时候在一边mount了,另一边没有mount:

[grid@rac1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512   4096  1048576      4094     3988                0            1994              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576      6141     4106                0            4106              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576      2047     1651                0            1651              0             Y  OCR/
[grid@rac1 ~]$ 
[grid@rac2 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      2047     1651                0            1651              0             Y  OCR/

 

手动mount:

SQL> alter diskgroup data mount
  2  ;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"

 

经检查,是由于在裸设备绑udev的时候,盘混了。导致asm起不来。

 

下面是正确的绑udev配置:use udev for Oracle ASM

 

 

 

 

拼出加分区表脚本

0

Posted on by

下面的脚本为新建2014年一年的分区。可根据不同的需求修改下。

sql:

select 'alter table ' || b.table_name || ' add partition PA_' ||
       TO_CHAR(a - 1, 'YYYYMM') ||
       
       ' values less than (TO_DATE( ''' ||
       TO_CHAR(a, 'YYYY-MM-DD HH24:MI:SS') || ''',''YYYY-MM-DD HH24:MI:SS'');'

  from (select add_months(trunc(DATE '2014-01-01', 'month'), level) a
          from dual
        connect by level < 13),
       (select owner || '.' || table_name table_name
          from dba_tables
         where table_name in ('T_WCP_FRZ_CREDENTIAL', 'T_WCP_ACCT_QUERY_CREDENTIAL')) b
union all
select 'alter table ' || c.owner||'.'||c.table_name
        ||
       ' add partition PA_MAX values less than (MAXVALUE);'
       
  from dual,(select owner, table_name
          from dba_tables
         where table_name in ('T_WCP_FRZ_CREDENTIAL','T_WCP_ACCT_QUERY_CREDENTIAL')) c

 order by 1

 

执行的结果:

alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201401 values less than (TO_DATE( '2014-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201402 values less than (TO_DATE( '2014-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201403 values less than (TO_DATE( '2014-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201404 values less than (TO_DATE( '2014-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201405 values less than (TO_DATE( '2014-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201406 values less than (TO_DATE( '2014-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201407 values less than (TO_DATE( '2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201408 values less than (TO_DATE( '2014-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201409 values less than (TO_DATE( '2014-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201410 values less than (TO_DATE( '2014-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201411 values less than (TO_DATE( '2014-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_201412 values less than (TO_DATE( '2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_ACCT_QUERY_CREDENTIAL add partition PA_MAX values less than (MAXVALUE);
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201401 values less than (TO_DATE( '2014-02-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201402 values less than (TO_DATE( '2014-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201403 values less than (TO_DATE( '2014-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201404 values less than (TO_DATE( '2014-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201405 values less than (TO_DATE( '2014-06-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201406 values less than (TO_DATE( '2014-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201407 values less than (TO_DATE( '2014-08-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201408 values less than (TO_DATE( '2014-09-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201409 values less than (TO_DATE( '2014-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201410 values less than (TO_DATE( '2014-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201411 values less than (TO_DATE( '2014-12-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_201412 values less than (TO_DATE( '2015-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
alter table EXCH.T_WCP_FRZ_CREDENTIAL add partition PA_MAX values less than (MAXVALUE);

 

附件一个查找数据库中range分区的分区表(我们系统基本是时间的range分区,查找出来的表还需仔细核对,防止出现误执行):

select OWNER, TABLE_NAME
  from dba_part_tables c
 where c.partitioning_type = 'RANGE'
   and table_name not like 'BIN$%'
   and owner not in ('SYS',
                     'SYSTEM',
                     'DBSNMP',
                     'SYSMAN',
                     'OUTLN',
                     'MDSYS',
                     'ORDSYS',
                     'EXFSYS',
                     'DMSYS',
                     'WMSYS',
                     'CTXSYS',
                     'ANONYMOUS',
                     'XDB',
                     'ORDPLUGINS',
                     'OLAPSYS',
                     'PUBLIC')
 order by 1

 

大量update导致的逻辑dataguard延迟

1

Posted on by

处理过程:

 1.关闭apply

alter database stop logical standby apply;

停止不掉可以强制关闭

alter database abort logical standby apply;

 

2.排除表

EXEC DBMS_LOGSTDBY.SKIP('DML','ORADBCA','T_TRANSACTION_ORDER');

update system.logstdby$skip
set esc = '\'
where esc is NULL;
commit;

 

3.重新应用

alter database start logical standby apply immediate;

 

4.关闭应用

alter database stop logical standby apply;

 

5.把排除的表,重新恢复同步

EXEC DBMS_LOGSTDBY.UNSKIP('DML','ORADBCA','T_TRANSACTION_ORDER');

 

6.表初始化

exec dbms_logstdby.instantiate_table('ORADBCA','T_TRANSACTION_ORDER','Pridb');

 

7.起同步进程

alter database start logical standby apply immediate;

 

8.check check

 

 

–附检查sql

select * from V$LOGSTDBY_STATE;

select * from V$LOGSTDBY_STATS;

 

select * from V$LOGSTDBY_PROGRESS;

select * from V$LOGSTDBY_PROCESS;

 

 

select 'Logical standbydb delay '||(sysdate-lp.APPLIED_TIME)*24*3600||'s' delay from v$logstdby_progress lp,v$database d

where d.database_role= 'LOGICAL STANDBY';