Admin 1. 04장 Managing the Oracle Instance

1.  parameter  파일 수정
2. startup  & shutdown
3. EM 사용법
4. scripting
5. V$

 

EM 접속하는 법

1. port 주소 알기

cd $ORACLE_HOME/install

cat portlist.ini

 

2. EM 기동여부 확인

emctl status dbconsole

 

3. 접속

http://[[자기 ip 주소]]:1158/em

 

username : sys

password : oracle

connect as : sysdba

 

EM 잘 쓰는 방법

1. 긍정적인 사고

2. 4개의 tabbed 페이지

Home : 중요, 일반적

Performance : 성능(tuning)

Administration : 설정

Maintenance: 유지보수

3. related Links, Additional Monitoring Links 잘 보기

4. 스크립트 저장

 

 

window 에서 원격 접속하는 방법

sqlplus sys/oracle@192.168.186.3:1521/orcl.oracle.com as sysdba

 

 

isqlplus dba로 접속하는 방법

 cd /u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/isqlplus/application-deployments/isqlplus

 $ORACLE_HOME/jre/1.4.2/bin/java -Djava.security.properties=$ORACLE_HOME/oc4j/j2ee/home/config/jazn.security.props -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user "iSQL*Plus DBA/admin"

-password welcome -shell

JAZN:> adduser "iSQL*Plus DBA" oracle oracle
JAZN:> grantrole webDba "iSQL*Plus DBA" oracle
JAZN:> exit
JAZN:>

 

 

쉘에서 sql 실행

 s.sql

 insert into dept values(1,'aa',null);
 select * from dept;

[oracle@orcl ~]$ vi s.sql
[oracle@orcl ~]$ sqlplus scott/tiger < s.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 18 17:42:50 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SCOTT>
1 row created.

S SCOTT>
    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 aa
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

S SCOTT> S SCOTT> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@orcl ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 18 17:43:39 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SCOTT> delete from dept where deptno=1;

1 row deleted.

S SCOTT> @s

1 row created.

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 aa
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

S SCOTT>

 

[oracle@orcl ~]$ sqlplus scott/tiger @s.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 18 17:44:52 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

insert into dept values(1,'aa',null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

 

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 aa
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

S SCOTT>

 

host = windows or linux 에서 가능

 

! = linux에서만 가능

 

S SCOTT> @s
insert into dept values(1,'aa',null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

 

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 aa
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

S SCOTT> !sqlplus scott/tiger < s.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 18 18:00:28 2009

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SCOTT> insert into dept values(1,'aa',null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

S SCOTT>
    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 aa
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

S SCOTT> S SCOTT> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

S SCOTT>

 

 

윈도우에서 만든 파일이 리눅스에서 한글 안 보이는 문제 해결

iconv -f eucKR -t utf8 파일 > 새로운파일명

 [oracle@orcl ~]$ cat hangul.txt
abcde
▒▒▒▒▒ٶ▒
[oracle@orcl ~]$ iconv -f eucKR -t utf8 hangul.txt > hangul_u.txt
[oracle@orcl ~]$ cat hangul_u.txt
abcde
가나다라

 

 

 

 

 

shell 예

변수 $1로 만들어서 실행시

./파일명 넣을값

 vi a.sh

mkdir $1

 

ex) ./a.sh dddd

dddd 디렉토리가 만들어짐

 

 

if문 예제

if [ $1 = sys ] ; then

sqlplus / as sysdba

else

sqlplus scott/tiger

fi

 

for문 예제

-- 해당 폴더의 모든 파일 내용을 출력하시오, (sql 확장자를 가진 파일만)

for list in ./*.sql;

do

cat $list;

done

 

iconv 하는 예제 (캐릭터셋 변환)

for f in ./*.sql

do

echo $f;

iconv -f eucKR -t utf8 $f >$f.tmp;

mv $f.tmp $f;

done

 

 

admin I 4-15page

--parameter 파일 백업

[oracle@orcl ~]$ mkdir -setting

 

S SYS> create pfile='/home/oracle/_setting/initorcl.ora' from spfile;

File created.

 --parameter 파일 보기

show parameter

 검색

show parameter NAME

ex) show parameter sga

      show parameter control

 

admin I page4-16 에 적은거 적을것

--modified parameter 테스트

vi _setting/initorcl.ora

 

      6 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
      7 *.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
      8 *.compatible='10.2.0.1.0'
      9 *.control_files='/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_50sxs92d_.ctl        ','/u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_50sxs97m_.ctl'
     10 *.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
     11 *.db_block_size=8192
     12 *.db_create_file_dest='/u01/app/oracle/oradata'
     13 *.db_domain='oracle.com'
     14 *.db_file_multiblock_read_count=16
     15 *.db_name='orcl'
     16 *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
     17 *.db_recovery_file_dest_size=2147483648
     18 *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
     19 *.job_queue_processes=10
     20 *.open_cursors=300
     21 *.pga_aggregate_target=16777216
     22 *.processes=150
     23 *.remote_login_passwordfile='EXCLUSIVE'
     24 *.sga_target=167772160
     25 *.undo_management='AUTO'
     26 *.undo_tablespace='UNDOTBS1'
     27 *.user_dump_dest='/u01/app/oracle/admin/orcl/udump'

이 것이 modifed parameter 파일 임

 

  예)db_recovery_file_dest_size (fra 크기)

	static	dynamic
script	1. 확인      show parameter o7 2. 수정     scope = both ->>     scope = spfile ->> 현재 셋팅은 바꾸지 않고, 파라미터 파일만 바꿈.     alter system set O7_DICTIONARY_ACCESSIBILITY = TRUE scope=spfile; 3. instance 재부팅    shutdown immediate; 4. 확인    show parameter o7	1. 현재값 확인      show parameter db_rec 2. 값 변경      ALTER SYSTEM SET db_recovery_file_dest_size = 3G SCOPE=BOTH 3. 확인      show prarmeter db_rec
em	spfile 탭에서만 수정후 재부팅 yse	current, spfile 탭 두 군데 다 고쳐줘야 함

 

 

통상적인 parameter 파일 백업

create pfile='/home/oracle/_setting/initorcl.ora.090519_1' from spfile;

고장내기--

[oracle@orcl ~]$ rm /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora

S SYS> shutdown abort
ORACLE instance shut down.
S SYS> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'

 

백업한 parameter 파일로 복구하기

   -pfile로 시작만 하기

   startup pfile='/home/oracle/_setting/initorcl.ora.090519_1' nomount;

 

  create spfile from pfile='/home/oracle/_setting/initorcl.ora.090519_1';

 

  shutdown

 

  startup

 

 

alert log 스크립트로 보기

1. alert log 위치 찾기

S SYS> @p
Enter value for key: background

NAME                 VALUE
-------------------- -----------------------------------------------------------             -
background_core_dump partial
background_dump_dest /u01/app/oracle/admin/orcl/bdump

2. alertlog 파일 열기

[oracle@orcl ~]$ cd  /u01/app/oracle/admin/orcl/bdump
[oracle@orcl bdump]$ vi alert_orcl.log

 

3. alter log 검색

/ 로 검색

정규 표현식 특수 문자는 앞에 \(역슬러쉬) 필요

ex)May 19 14:\(4\|5\)

 

 Dynamic Performance Views

momory에서 읽음

v_$ 로 시작

v$fixed_table에 리스트

계속 바뀜 

 

sys로 찾아야 함

 

 

 

 숙제

1.  자신만의 parameter file 내용 정리 체계 확립할 것.

2. alter log 파일 분석할 것,

   1. 내용의 종류 : 시각 기준으로 끊을 것.
   2. 시간 축 : 내용의 종류를 입력.