VNOUG.ORG

Hi cac anh!

Hien tai em dang su dung RAC HP-UX, em gap su co ve tien trinh nen crsd.bin chiem CPU rat cao len den 97.45 %. Binh thuong tien trinh nen nay khoang < 1% CPU, file alert.log (trong bdump) bao loi archive va khong day archive ra duoc.


Trong file alert_HTI1.log
ERROR: Failed to connect with connect string:(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/app/oracle/product/10.2.0/rac/bin/oracle)(ARGV0=oracle+ASM1)(ENVS='ORACLE_HOME=/app/oracle/product/10.2.0/rac,ORACLE_SID=+ASM1')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(enable=setuser))
ERROR: Failed to connect with connect string:(DESCRIPTION=(ADDRESS=(PROTOCOL=beq)(PROGRAM=/app/oracle/product/10.2.0/rac/bin/oracle)(ARGV0=oracle+ASM1)(ENVS='ORACLE_HOME=/app/oracle/product/10.2.0/rac,ORACLE_SID=+ASM1')(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(enable=setuser))
Fri Sep 5 10:22:16 2008
Errors in file /app/HTI/bdump/hti1_arc1_5060.trc:
ORA-19504: failed to create file "+ARCHIVE"
ORA-17502: ksfdcre:4 Failed to create file +ARCHIVE
ORA-15055: unable to connect to ASM instance
ORA-12549: TNS:operating system resource quota exceeded
ORA-15055: unable to connect to ASM instance
ORA-12549: TNS:operating system resource quota exceeded
ARC1: Error 19504 Creating archive log file to '+ARCHIVE'
ARC1: Failed to archive thread 1 sequence 15438 (19504)
ARCH: Archival stopped, error occurred. Will continue retrying


Ngoai ra file /app/oracle/product/10.2.0/crs/log/data_ht1/crsd/crsd.log
bao loi sau
2008-09-05 14:40:13.121: [ OCRSRV][19]th_select_handler: Failed to retrieve pro
cctx from ht. constr = [24070792] retval lht [-27] Signal CV.
2008-09-05 14:41:15.894: [ OCRSRV][19]th_select_handler: Failed to retrieve pro
cctx from ht. constr = [24070792] retval lht [-27] Signal CV.
2008-09-05 14:42:18.745: [ OCRSRV][19]th_select_handler: Failed to retrieve pro
cctx from ht. constr = [24070792] retval lht [-27] Signal CV.
2008-09-05 14:43:21.491: [ OCRSRV][19]th_select_handler: Failed to retrieve pro
cctx from ht. constr = [24070792] retval lht [-27] Signal CV.
2008-09-05 14:44:24.239: [ OCRSRV][19]th_select_handler: Failed to retrieve pro
cctx from ht. constr = [24070792] retval lht [-27] Signal CV.


CPU ma tien trinh nen chiem 97.45%
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
1 ? 1910 root 152 20 86036K 9476K run 17:349:02 97.62 97.45 crsd.bin

Mong cac anh ho tro cho! Trong thoi gian cho doi em xin cam on may anh chi rat nhieu.
Than. Tung

Em làm 2 điều sau đây:

1. là ORACLE user trong UNIX gõ "ulimit -a" xem OS resource là gi?

Nếu cần hiểu thêm "ulimit" thì coi link này: http://linux.die.net/man/1/ulimit

2. "log on sqlplus as sysdba" rồi chạy "sqlplus command" phia dưới và cho biết kết qua?

SQL > select name,state from v$asm_diskgroup;

Chao anh! Em chup nhung yeu cau cua anh. Trong thoi gian cho doi anh support, em cam on anh nhieu


SQL> select state, name from v$asm_diskgroup;

STATE NAME
----------- ------------------------------
MOUNTED ARCHIVE
MOUNTED BCKDATA
MOUNTED DATA

SQL>

oracle@data_ht2[+ASM2]:/home/oracle$ ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 1048576
stack(kbytes) 131072
memory(kbytes) unlimited
coredump(blocks) 4194303
nofiles(descriptors) 60
oracle@data_ht2[+ASM2]:/home/oracle$

Em là 2 điều phía dưới và hy vọng là 1 trong 2 điều đó sẽ giúp giải quyết vấn đề. Còn vấn đề CPU cao (97.45 %) thì có thể vì bị "bug" và mình sẽ thanh toán sau. Chúc em may mắn.

1. Nếu em biết cách thay đổi giới hạn tài nguyên của user ORACLE cố định (permanently reset resource limits of user ORACLE) thì nâng mọi thứ lên "unlimited"; nếu không thì cho thêm (add) 4 dòng (lines) vô phần cuối của ".profile" của user ORACLE trên mọi "node" như sau đây (nhớ "save" ".profile" và "logout/login unix" để sự thay đổi có hiệu lực):

oracle@data_ht2[+ASM2]:/home/oracle$ vi .profile
...
ulimit -c unlimited
ulimit -d unlimited
ulimit -n unlimited
ulimit -s unlimited

2. Trên mỗi node, đem tất cả "database instances" xuống, vô SQLPLUS "ASM instance" và nâng "parameter PROCESSES" trong initASM1.ora/initASM2.ora hay là spfileASM1/spfileASM2 (từ 40?) lên 300, rồi "restart ASM and database instances":

NODE1:
$CRS_HOME/bin/srvctl stop instance -d <database_name> -i <instance_name1>
...
cd $ORACLE_HOME/dbs
vi initASM1.ora
processes=300
...
oracle@data_ht1[+ASM1]:/home/oracle$ export ORACLE_SID=ASM1
$ORACLE_HOME/bin/sqlplus '/as sysdba'
shutdown immediate;
startup;
exit;
...
$CRS_HOME/bin/srvctl start instance -d <database_name> -i <instance_name1>

hay là:

$CRS_HOME/bin/srvctl stop instance -d <database_name> -i <instance_name1>
...
oracle@data_ht1[+ASM1]:/home/oracle$ export ORACLE_SID=ASM1
$ORACLE_HOME/bin/sqlplus '/as sysdba'
alter system set processes=300 scope=spfile;
shutdown immediate;
startup;
exit;
...
$CRS_HOME/bin/srvctl start instance -d <database_name> -i <instance_name1>
------------------------------------------------------------------------------
NODE2
$CRS_HOME/bin/srvctl stop instance -d <database_name> -i <instance_name2>
...
cd $ORACLE_HOME/dbs
vi initASM2.ora
processes=300
...
oracle@data_ht2[+ASM2]:/home/oracle$ export ORACLE_SID=ASM2
$ORACLE_HOME/bin/sqlplus '/as sysdba'
shutdown immediate;
startup;
exit;
...
$CRS_HOME/bin/srvctl start instance -d <database_name> -i <instance_name2>

hay là:

$CRS_HOME/bin/srvctl stop instance -d <database_name> -i <instance_name2>
...
oracle@data_ht2[+ASM2]:/home/oracle$ export ORACLE_SID=ASM2
$ORACLE_HOME/bin/sqlplus '/as sysdba'
alter system set processes=300 scope=spfile;
shutdown immediate;
startup;
exit;
...
$CRS_HOME/bin/srvctl start instance -d <database_name> -i <instance_name2>
-----------------------------------------------------------------------------
...
-----------------------------------------------------------------------------
NODEn
....

Dau tien em cam on anh rat nhieu khi anh da bo chut it thoi gian de ho tro. Hien em lam theo cach support cua anh! Xin loi em co the xin dia chi email cua anh duoc khong ah! Em chan thanh cam on anh rat nhieu.
Than, E.Tung