All Days 무한도전

오라클 설치시 인스톨러에서 데이타베이스 자동 생성을 할 경우
OS 언어에 따라 자동으로 characterset 이 설정되어 설치되는데,
characterset을 임의로 변경하는 방법 입니다.

변경시나리오)

* 기존 개발용 오라클DB OLD_ORA 의 nls_characterset 은 US7ASCII 이다.

* 기존 클라이언트 nls_lang 은 AMERICAN_AMERICA.US7ASCII 이다.

* 새로운 한글windows OS에 오라클DB NEW_ORA 를 설치하였는데 nls_characterset 이 KO16MSWIN949 이다.

* OLD_ORA 의 데이타를 exp 유틸로 덤프를 떠서 NEW_ORA 에 imp 유틸로 복구하였는데,

일본및 중국 언어문자셋이 깨어져 버리고 ??? 등으로 바뀌어 유니크인덱스가 생성되지 않는다.

* 데이타 이전및 개발호환을 위해 nls_characterset 을 맞추기로 하였다.

새로 설치하지 않고 바꾸는 법 입니다.

절차 SQL> SHUTDOWN IMMEDIATE;
<만일의 사태를 대비해 풀백업을 한다>
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET US7ASCII;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

ALTER DATABASE CHARACTER SET US7ASCII 변경할때

큰집합이여야 된다는(superset) 등의 에러메세지 떨어질 경우
update SYS.PROPS$ set value$='US7ASCII' where name='NLS_CHARACTERSET';
shutdown immediate;
startup;

클라이언트 nls_characterset 셋팅 :
regedit
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
- NLS_LANG = AMERICAN_AMERICA.US7ASCII

[nls 환경보기]
--DB
SELECT * FROM nls_database_parameters
WHERE parameter='NLS_CHARACTERSET' OR parameter='NLS_LANGUAGE'

-- session
SELECT * FROM v$nls_parameters

===============================================================================================

위와 같이 설정후 OLD_ORA 데이타 덤프를 NEW_ORA 로 Import 시킨후 한글이 정상적으로 들어와 졌습니다.

그런데 SQLTools,토드 등으로 접속후 객체브라우져로 객체 리스트 보기에서 아래와 같은 에러가 발생하면서

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized

객체리스트가 보이지 않았습니다.

원인) 아래 쿼리를 날렸을때 데이터 타입이 중복되어서 나옴

쿼리)

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112)

order by types_used_in;

결과)

CHARACTERSET TYPES_USED_IN
------------------------------ -------------
KO16MSWIN949 CHAR
US7ASCII CHAR
KO16MSWIN949 CLOB
US7ASCII CLOB
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
KO16MSWIN949 VARCHAR2
US7ASCII VARCHAR2

CHAR,CLOB,VARCHAR2 에 중복된 CHARACTERSET이 존재함.

위와 같은 경우 다음과 같이 조취합니다.

조치2) sysdba로 아래 구문들 실행

주의사항)

a) Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all.
b) Run the next script in SQLPLUS connected "as sysdba"

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
SQL> COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

만약 실행중 다음과 같은 오류발생시 아래단계 실행

오류가 안난다면 하지 않아도 됩니다.

ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE KO16KSC5601
==> ORA-12714: invalid national character set specified

원인)

NATIONAL CHARACTER SET 은 Oracle9i 이후에는 UTF8과 AL16UTF16 만 제공
확인)

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET')
결과)

PARAMETER VALUE
---------------------------------- ---------------
NLS_CHARACTERSET KO16KSC5601

NLS_NCHAR_CHARACTERSET KO16KSC5601

조치)

update sys.props$ set value$='AL16UTF16' where name='NLS_NCHAR_CHARACTERSET';

이후 위의 조치2)를 다시실행하면 아래와 같은 결과를 얻을수 있다..

문제해결)

CHARACTERSET TYPES_USED_IN
------------------------------ -------------
US7ASCII CHAR
US7ASCII CLOB
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
US7ASCII VARCHAR2

이후 SQLTools 나 토드접속시 에러가 나지 않고 객체리스트가 정상적으로 나오게 됩니다.

offline drop 명령어는 control file에서 datafile을 제거 후에
재생성 하는 것과 같은 이치입니다. 단지 차이는 데이터파일 명이 MISSING 인지
기존 파일명을 그대로 가지는지만 다르겠습니다.
- 컨트롤파일 재생성시
SQL> select name from v$datafile;
NAME

일반 데이터가 들어가는 테이블스페이스의 데이터파일은 offline되었으므로
DB는 open이 가능합니다.하지만 해당 데이터파일을 복구하기 위해서는 백업과
archive가 필요합니다. offine drop된 데이터파일의 데이터를
읽으려고 할 경우에는 당연히 아래와 같은 메세지가 나고 해당 데이터는
복구 불능입니다. 일부를 추출하는 것도 여러 자료를 찾아보고
어떻게든 해보려고 했는데 안되네요.
테스트 해보기에는 block corruption 에러가 났을 경우에 수행하는
event를 적용했는데 역시 복구불능이었습니다.
dbms_repair로도 안되었습니다.
....
SQL> exec dbms_repair.admin_tables ( table_name => 'REPAIR_TABLE', -

table_type => dbms_repair.repair_table, -
action => dbms_repair.create_action, tablespace => 'EXAMPLE');

BEGIN dbms_repair.admin_tables ( table_name => 'REPAIR_TABLE', table_type => dbms_repair.repair_table, action => dbms_repair.create_action, tablespace => 'EXAMPLE'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 83:
PLS-00201: identifier 'DBMS_REPAIR.REPAIR_TABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select count(*) from hr.kkk;
select count(*) from hr.kkk
*
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/u01/ora9i/oradata/PROD/users03.dbf'

SQL> !cp /u01/ora9i/oradata/PROD_KKK/users03.dbf /u01/ora9i/oradata/PROD/users03.dbf
SQL> recover datafile '/u01/ora9i/oradata/PROD/users03.dbf';
ORA-00279: change 67160 generated at 02/05/2007 23:56:37 needed for thread 1
ORA-00289: suggestion : /u01/ora9i/oradata/PROD/archive/1_9.dbf
ORA-00280: change 67160 for thread 1 is in sequence #9

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 67297 generated at 02/06/2007 00:41:03 needed for thread 1
ORA-00289: suggestion : /u01/ora9i/oradata/PROD/archive/1_10.dbf
ORA-00280: change 67297 for thread 1 is in sequence #10
ORA-00278: log file '/u01/ora9i/oradata/PROD/archive/1_9.dbf' no longer needed for this recovery

ORA-00279: change 67299 generated at 02/06/2007 00:41:04 needed for thread 1
ORA-00289: suggestion : /u01/ora9i/oradata/PROD/archive/1_11.dbf
ORA-00280: change 67299 for thread 1 is in sequence #11
ORA-00278: log file '/u01/ora9i/oradata/PROD/archive/1_10.dbf' no longer needed for this recovery

Log applied.
Media recovery complete.
SQL> alter database datafile '/u01/ora9i/oradata/PROD/users03.dbf' online;
Database altered.
SQL> select name, status from v$datafile;
NAME STATUS

SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS

- 'PLAN_TABLE' is old version

SQL> drop table plan_table;

@/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

테이블에 CLOB 타입이 포함된 dmp 파일을 import 하고자 할때.

다음과 같은 오류가 발생할수 있다.

IMP-00017: following statement failed with ORACLE error 959:
...
...
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'TS_ICMS_DATA' does not exist

import의 경우 컬럼에 CLOB 데이터타입이 포함되어 있을경우는 export받은 테이블의 테이블스페이스 정보와 import 하려고 하는 테이블스페이스가 같아야 한다.

따라서 다음과 같이 테이블 스페이스를 추가한다.

# tablespace 생성
CREATE TABLESPACE TS_ICMS_DATA

DATAFILE '/export/home/oracle/data/ICMSDATA1.dbf' SIZE 1000M;

# 사용자에게 테이블스페이스 지정
CREATE USER test_user IDENTIFIED BY test_pwd DEFAULT TABLESPACE ts_icms_data TEMPORARY TABLESPACE tmp;

# 권한부여
GRANT CONNECT, RESOURCE TO test_user;

select tablespace_name, bytes, max_bytes
from user_ts_quotas;

Solution : SQL> Alter user AVS quota unlimited on USR_D;
SQL> alter user AVS quota <size> on USR_D;

0003. LOCK object 확인

select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, v$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = 'IBFUSR'
and do.OBJECT_NAME = 'BT_TAB_SYNC_INFO';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID SESSION_ID ORACLE_USERNAME
---------- ---------- ------------------------------
OS_USER_NAME PROCESS LOCKED_MODE
------------------------------ ------------ -----------
IBFUSR
BT_TAB_SYNC_INFO
125142 66 IBFUSR
안민혁 2952:852 3

select SID, SERIAL# from v$session where sid = 66;

SID SERIAL#
---------- ----------
66 48801

alter system kill session '66,48801';
ALTER SYSTEM KILL SESSION '66,48801' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION '66,48801' IMMEDIATE;

Situation 1

To check objects currently being used, e.g. by a DML statement, before executing a DDL command, you can query v$locked_object as in the example below.


Session A

SQL> connect miles/oracle;
Connected.

SQL> drop table test;

Table dropped.

SQL> create table test (a number);

Table created.

SQL> insert into test values (1);

1 row created.

Session B

SQL> connect / as sysdba
Connected.

SQL> truncate table miles.test;
truncate table miles.test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
--> this is expected because miles.test is locked in Session A


SQL>
select do.OWNER, do.OBJECT_NAME, do.OBJECT_ID,
lo.SESSION_ID, lo.ORACLE_USERNAME, lo.OS_USER_NAME,
lo.PROCESS, lo.LOCKED_MODE
from dba_objects do, v$locked_object lo
where do.OBJECT_ID = lo.OBJECT_ID
and do.OWNER = 'MILES'
and do.OBJECT_NAME = 'TEST';


OWNER OBJECT_NAME OBJECT_ID SESSION_ID ORACLE_USERNAME
-------- ------------ ---------- ---------- ------------------------------
OS_USER_NAME PROCESS LOCKED_MODE
-------------------- -------------- -----------
MILES TEST 63754 1079 MILES
oracle 23013 3

From above, you can see miles.test is locked by Oracle user 'MILES' with OS user name 'oracle'.
Session id is 1079, OS process id is 23013.


You can kill the session to release the lock.

SQL> select SID, SERIAL# from v$session where sid = 1079;

SID SERIAL#
---------- ----------
1079 663


SQL> alter system kill session '1079,663';

System altered.

Then the DDL can be executed successfully:

SQL> truncate table miles.test;

Table truncated.

Situation 2

If a DDL statement on an object, e.g. create or replace procedure, is hanging because another user is executing the procedure, you can find the blocking session as in the example below.

Using 3 sessions:

1. Session A: connect as sysdba
2. Session B: connect as user rob/rob
3. Session C: connect as user tony/tony

Session A

SQL>
connect / as sysdba
create user rob identified by rob;
grant connect, resource to rob;
create user tony identified by tony;
grant connect, resource to tony;
grant execute on dbms_lock to rob;
grant execute on dbms_lock to tony;

Session B

SQL>
connect rob/rob

create or replace procedure rob_test_p1 is
begin
null;
end;
/

grant execute on rob_test_p1 to tony;

Session C

SQL>
connect tony/tony
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/

Session A

SQL>
connect / as sysdba

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
ob.kglnaobj obj_name, ob.KGLNAOWN obj_owner,
ses.KSUUDNAM cur_user
from x$kglpn pn, x$kglob ob, x$ksuse ses
where ob.KGLNAOBJ='ROB_TEST_P1'
and (ob.KGLHDPMD <> 0
or
(ob.KGLHDPMD = 0 and ob.KGLHDLMD not in (0,1))
)
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr;

SID SERIAL# OBJ_NAME OBJ_OWNER CUR_USER
---------- ---------- -------------------- --------------- ------------------
1094 5909 ROB_TEST_P1 ROB TONY


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=5909;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob.rob_test_p1; dbms_lock.sleep(120); end;

If the DDL is already hanging due to a lock, you can follow below test case to find the lock details.

Session C

SQL> connect tony/tony

SQL>
begin
rob.rob_test_p1;
dbms_lock.sleep(120);
end;
/

Session B

SQL> connect rob/rob

SQL> alter procedure rob_test_p1 compile;
--> This is hanging

Session A

col obj_name format a30
col pin_cnt format 999
col pin_mode format 999
col pin_req format 999
col state format a30
col event format a30
col wait_time format 999999999
col seconds_in_wait format 999999999

SQL>
select distinct ses.ksusenum sid, ses.ksuseser serial#,
ob.kglnaobj obj_name,
pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req,
w.state, w.event, w.wait_time, w.seconds_in_Wait
from x$kglpn pn, x$kglob ob, x$ksuse ses, v$session_wait w
where pn.kglpnhdl in (select kglpnhdl from x$kglpn where kglpnreq > 0)
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc;


SID SERIAL# OBJ_NAME PIN_CNT PIN_MODE PIN_REQ
---------- ---------- ------------------------------ ------- -------- -------
STATE EVENT WAIT_TIME
------------------------------ ------------------------------ ----------
SECONDS_IN_WAIT
---------------
1082 6765 ROB_TEST_P1 3 2 0
WAITING PL/SQL lock timer 0
39

1074 6060 ROB_TEST_P1 0 0 3
WAITING library cache pin 0
27


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=6765;

SQL_TEXT
--------------------------------------------------------------------------------
begin rob_test_p1; dbms_lock.sleep(120); end;


SQL>
select sql_text
from v$sql a, v$session b
where a.sql_id=b.sql_id
and b.serial#=6060;

SQL_TEXT
--------------------------------------------------------------------------------
alter procedure rob_test_p1 compile

SQL> conn /as sysdba
ERROR:
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925
ORA-09925: Unable to create audit trail file
IBM AIX RISC System/6000 Error: 13: Permission denied
Additional information: 9925

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

1. init<sid>.ora 에 <parameter:AUDIT_FILE_DEST> 존재 확인

2. $ORACLE_HOME/rdbms/audit 스페이스 확보 full 이면 데이터 임시 폴더로 MOVE

3. $ORACLE_HOME/bin/oracle 의 permission 이 6755 인지 확인
아니면 chmod 6755 oracle

0001. Undo Segment 삭제 방법

ORA-01552 ORA-00376 ORA-01110 ORA-01548 - UNDO tablespace

Recently our Helpdesk people configured backup on Payroll server. while running the backup, backup applications locking the writing file.for that issue alert log showing errors like below:

Mon Jul 06 05:07:36 2009
KCF: write/open error block=0x6f6 online=1
file=2 D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
Mon Jul 06 05:07:36 2009
Errors in file d:\oracle9\product\payroll\admin\payroll9\bdump\payroll9_smon_2700.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Immediately we stopped the backup & restarted the database .
Still the Application is not working. Application show error message

"ORA-01552: cannot use system rollback segment for non-system tablespace"

then I checked for the corrupted rollback segments

SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- -----------------
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1

then I recreated another tablespace as UNDOTBS2

CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS02.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 51200K MAXSIZE 800M;

ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

Now I try to drop the old UNDO tablespace but it's giving error:

ora-01548 active rollback segment found

Now the issue is resolved but in Alert log we are getting errors like:

Wed Jul 15 11:50:52 2009
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
……..
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery

& for some transactions from applications we are getting some more errors:

ORA-00376: file 2 cannot be read at this tme ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'

Solution:
1. create pfile from spfile;

2. Add the following line to pfile:

_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')

3. comment undo management-Auto

#undo_management=AUTO

4. create spfile from pfile

5. Start the database again:

SQL> STARTUP RESTRICT

6. Drop bad rollback segments

SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
......
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.

7. Check again

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

No rows returned

8. DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;

9. shutdown immedaite

10. remove added lines & comments from pfile and recreate the spfile

11.startup

Now the issue is resolved.

SELECT tablespace_name,status FROM dba_rollback_segs;

select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

Response 폴더의 clientadmin.rsp 파일을 연다.


#------------------------------------------------------------------------------
#Name : ORACLE_HOME
#Datatype : String
#Description: Complete path of the Oracle Home.
#Example : ORACLE_HOME = "/product/10.2.0/client"
#------------------------------------------------------------------------------
ORACLE_HOME="c:\oracle\product\10.2.0\client"

#------------------------------------------------------------------------------
#Name : ORACLE_HOME_NAME
#Datatype : String
#Description: Oracle Home Name. Used in creating folders and services.
#Example : ORACLE_HOME_NAME = "OraClient10ghome1"
#------------------------------------------------------------------------------
ORACLE_HOME_NAME="OraClient10ghome1"

위와 같이 수정한다

그런다음

실행창에서 cmd.exe 를 입력하여 커멘드 창을 연 후 실행 폴더로 이동한다음

setup.exe -silent -responsefile D:\Download\Oracle\Response\clientadmin.rsp

아래와 같이 입력하고 실행 하면 콘솔창에서 oracle이 설치된다.

PATCH
/data/oracle/Disk1/stage/products.xml

커널 파라미터 설정

# cd /proc/sys/kernel
# echo 2147483648 > shmmax

# vi /etc/sysctl.conf

fs.file-max = 65536
kernel.shmmax = 2147483648
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range= 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144

# sysctl -p

참고 : kernel_parameter.rar

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

CentOS 5.2를 지원하지 않으므로 레드햇 4로 수정.

# vi /etc/redhat-release

Red Hat Enterprise Linux AS release 4 (Taroon)

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

매뉴얼이 제공하는 값과는 다르다.
다른 머신에 설치된 값을 참고했다.

# vi /etc/security/limits.conf

oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384

참고 : Shell_Limits.rar

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

# SELINUX=disabled

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

적당히 최신 버전을 설치한다.

# rpm -Uvh setarch-2*
# rpm -Uvh make-3*
# rpm -Uvh glibc-2*
# rpm -Uvh libaio-0*

# rpm -Uvh compat-libstdc++-33-3*
# rpm -Uvh compat-gcc-34-3*
# rpm -Uvh compat-gcc-34-c++-3*
# rpm -Uvh gcc-4*
# rpm -Uvh libXp-1.0.0-8.1.el5.x86_64.rpm
# rpm -Uvh libXp-1.0.0-8.1.el5.i386.rpm

# rpm -Uvh openmotif-2*
# rpm -Uvh compat-db-4*

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

그룹과 유저 생성

# groupadd oinstall
# groupadd dba
# groupadd oper

# useradd -g oinstall -G dba oracle
# passwd oracle

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

설치 디렉토리 생성.
# mkdir -p /u01/app/oracle/product/10.2.0/db_1
# chown -R oracle.oinstall /u01

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

베이스 디렉토리, 홈 디렉토리, 라이브러리 경로 설정

# su - oracle
$ vi .bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=orcl
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

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

오라클 설치 파일의 압축을 푼다.

$ gzip -d 10201_database_linux_x86_64.cpio.gz
$ cpio -idmvc < 10201_database_linux_x86_64.cpio

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

다음은 오라클 인스톨러를 사일런트 모드로 실행하기 위한 작업이다.

oraInst.loc 파일을 수통 생성한다.

# su -
# cd /etc
# vi oraInst.loc

inventory_loc=/u01/app/oracle/oraInventory
inst_group=

# chown oracle:oinstall oraInst.loc
# chmod 664 oraInst.loc

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

리스폰스 파일 수정, 엔터프라이즈의 다음 항목만 수정했다.

# vi /home/oracle/database/response/enterprise.rsp

ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
ORACLE_HOME_NAME=orcl

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

# su - oracle

./runInstaller -silent -responseFile /home/oracle/database/response/enterprise.rsp

글자가 마구 깨져나왔다.

설치 디렉토리를 삭제하고 재성성, 재권한설정을 한다.

$ echo $LANG
$ ko_KR.UTF-8

$ LANG=en_US.UTF-8

재실행, 설치가 완료되었다면 root.sh를 실행한다.

$ su -
# /$ORACLE_HOME/root.sh

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

사일런트 모드로 인스톨을 했다면 NetCA 와 DBCA를 따로 셋팅해 주어야 한다.
$ vi /home/oracle/database/response/netca.rsp

수정 한 후 다음과 같이 실행한다.

$ $ORACLE_HOME/bin/netca /silent /responsefile /local_dir/netca.rsp

DBCA도 비슷하다.

$ vi /local_dir/dbca.rsp

$ $ORACLE_HOME/bin/dbca {-progressOnly | -silent} [-cloneTemplate] [-datafileDestination /datafilepath] -responseFile /local_dir/dbca.rsp

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

마지막으로 listener.ora, tnsname.ora 를 셋팅하고 리스너를 켜봅시다.
$ cd $ORACLE_HOME/network/admin/

예제: listener.ora tnsnames.ora

$ lsnrctl start

리스너 상태 확인
$lsnrctl status

문자셋을 확인하자.->따로 포스팅 되어있다.

### 신규 롤백 테이블 생성 ###
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/data1/oradata/COSS/undotbs02.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

### 신규 테이블로 변경 ###
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;

### UNDO 테이블 확인 ###
SELECT tablespace_name,status FROM dba_rollback_segs;

### 온/오프라인 확인 ###
select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

### 테이블 스페이스 드랍 ###
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

### 이전 테이블로 변경하기 위한 테이블스페이스 생성 ###
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/data1/oradata/COSS/undotbs01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

### 이전 테이블로 변경 ###
ALTER SYSTEM SET undo_tablespace=UNDOTBS1 SCOPE=BOTH;

### 온/오프라인 확인 ###
select segment_name, tablespace_name, status from dba_rollback_segs order by 2;

### 신규 테이블 스페이스 드랍 ###
DROP TABLESPACE undotbs2 INCLUDING CONTENTS AND DATAFILES;