Database/ORACLE2018.01.17 09:30


오라클 SR 처리를 위해

아래와 같은 정보를 수집하면 편하다...

ulimit -a : 메모리 정보

uname -a : OS 정보

adrci : 문제가 발생한 내역 압축하기 (ips 명령 사용)

opatch : 현재 패치 내역 조회하기



testdb01:/oracle_test] ulimit -a

time(seconds)        unlimited

file(blocks)         unlimited

data(kbytes)         2000000

stack(kbytes)        8192

memory(kbytes)       unlimited

coredump(blocks)     4194303

testdb01:/oracle_test] uname -a

HP-UX testdb01 B.11.23 U ia64 1770507378 unlimited-user license



testdb01:/tmp] adrci


ADRCI: Release 11.1.0.7.0 - Production on Sat Jan 13 09:25:50 2018


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


ADR base = "/oralog_test/dblog"

adrci> show problem


ADR Home = /oralog_test/dblog/diag/rdbms/testdb/testdb1:

*************************************************************************

PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                             

-------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 

14                   ORA 600 [kjuscv]                                            1630464              2018-01-12 22:36:29.336735 +09:00       

13                   ORA 600 [kjucvl:!busy]                                      1630463              2018-01-12 22:36:25.059486 +09:00         

14 rows fetched


adrci> show incident


ADR Home = /oralog_test/dblog/diag/rdbms/testdb/testdb1:

*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                              

-------------------- ----------------------------------------------------------- ---------------------------------------- 

1630464              ORA 600 [kjuscv]                                            2018-01-12 22:36:29.336735 +09:00       

1630463              ORA 600 [kjucvl:!busy]                                      2018-01-12 22:36:25.059486 +09:00       

 

50 rows fetched


adrci> ips pack problem 13 in /tmp

Generated package 9 in file /tmp/ORA600kju_20180113092623_COM_1.zip, mode complete

adrci> ips pack problem 14 in /tmp 

Generated package 10 in file /tmp/ORA600kju_20180113092815_COM_1.zip, mode complete

adrci> ips pack incident 1630464 in /tmp

Generated package 11 in file /tmp/ORA600kju_20180113092948_COM_1.zip, mode complete

adrci> ips pack incident 1630463 in /tmp

Generated package 12 in file /tmp/ORA600kju_20180113093112_COM_1.zip, mode complete


opatch lsinventory

Posted by 하이주니
Database/ORACLE2018.01.08 16:12


오라클 import 과정중에


아래와 같이 에러를 만났다


ora-00257 archiver error. connect internal only until freed


원인은 


아카이브 full로 인해 발생


근데 문제는 ASM 디스크 였다


아래의 쿼리를 통해 조회하면 archive err 상태를 확인 가능하다


SQL> select * from v$archive_dest;



방법은 수동 삭제와 자동 삭제가 있고


개발서버라 고민없이 RMAN으로 접속하여 자동으로 지웠다.


수동삭제


Subject:  How To Delete Archive Log Files Out Of +Asm? 

Doc ID:  Note:300472.1

The information in this document applies to: 

Oracle Server - Enterprise Edition - Version: 10.1.0.3

Information in this document applies to any platform.

Goal

How to delete archive log files out of +ASM? 

Fix

1. Run the following SQL to find the full path for the archivelog files.

SELECT CONCAT('+'||gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path,

dir, sys FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,

a.reference_index rindex, a.ALIAS_DIRECTORY dir, a.SYSTEM_CREATED sys

FROM v$asm_alias a, v$asm_diskgroup g

WHERE a.group_number = g.group_number)

START WITH (MOD(pindex, POWER(2, 24))) = 0

CONNECT BY PRIOR rindex = pindex

ORDER BY dir desc, full_path asc;

The results will look similar to the following.

+DSKGRP1/MAXCP/ARCHIVELOG/2004_11_15/thread_1_seq_970.1236.1

2. When the file is created by Oracle the format in +ASM is:

   DISKGROUP_NAME/db_name/file_type/creation_date/<file_name>.

This SQL will generate the SQL necessary to delete all archivelogs out of +ASM.

Note: Change the <diskgroup> and <dbname> to the actual values from what is returned from previous SQL output.

select 'alter diskgroup DSKGRP1 drop file

''<diskgroup>/<dbname>/ARCHIVELOG/'|| to_char(b.creation_date,'YYYY_MM_DD') ||'/'|| a.name||''';'

from v$asm_alias a, v$asm_file b

where a.group_number = b.group_number

and a.file_number = b.file_number

and b.type='ARCHIVELOG'

order by a.name;

This will generate SQL similar to the following.

alter DISKGROUP DSKGRP1 drop file  '+DSKGRP1/MAXCP/ARCHIVELOG/2004_11_15/thread_1_seq_970.1236.1';


자동삭제


$> rman target /


RMAN> crosscheck archivelog all;


RMAN> delete noprompt archivelog all;


참고 : http://interpiastory.tistory.com/21

http://kooremo.tistory.com/entry/ora00257archiver-error-Connect-internal-only-until-freed

http://database.sarang.net/?inc=read&aid=30826&criteria=oracle&subcrit=&id=&limit=20&keyword=blob&page=3

Posted by 하이주니
Database/ORACLE2018.01.08 15:46


impdp 할때나 오류가 발생하면

3가지 해결 방법이 있는거 같으나...

mount 를 통해 깔끔하게 해결하는것을 권장....


expdp failed with ORA-31641: unable to create dump file ORA-27054: NFS file system where the file is created or resides is not mounted with correct options


expdp failed with ORA-31641: unable to create dump file ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

While performing expdp on database getting below error. This is known error and workaround is available to fix it.

DataPump export fails with below errors:
Export: Release 11.2.0.1.0 - Production on Sun Nov 25 21:00:36 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/devotd_dpump/expdp_metadata_vspp_sun.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


This is the common error DBA faces. Reason for getting this error may be bug or incorrect option while mounting the NFS mount point. Also this known bug.


Solution 
The solution which worked in this case was to set the below event which disables the mount point parameter checking:
sqlplus / as sysdba
alter system set events '10298 trace name context forever, level 32';

Or:

Set the following event in the init.ora
event="10298 trace name context forever, level 32"

Or 
setting event is workaround provided by oracle if this not help you then kindly mount the NFS mount point with correct option as below-
# mount -F nfs -o rw,bg,hard,rsize=32768,wsize=32768,vers=3,forcedirectio,nointr,proto=tcp,suid host:/folder1/to1 /folder2/to2


Reference - ORA-27054: NFS file system where the file is created or resides is not mounted with correct options (Doc ID 781349.1)


Posted by 하이주니

티스토리 툴바