공부흔적

문제점 : 컨트롤 파일을 오라클 계정으로 만들어준 것이 아니라 루트 계정으로 만들어 주고 

오라클 DB를 open 하려고 했음 

해결방안 : 컨트롤 파일에 오라클 권한을 주고, 다시 DB를 open하면 해결된다. 

TESTDB =

( DESCRIPTION =

( ADDRESS_LIST =

( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192.168.64.145 ) ( PORT = 1521 ) )

)

( CONNECT_DATA = 

( SERVICE_NAME = testdb )

)

) 

각 보라색 글씨의 의미

- TESTDB : 네트 서비스 이름

- 접속할 아이피와 포트번호

- SID이름

C:\Users\stu>sqlplus scott/tiger@testdb

SQL*Plus: Release 11.2.0.1.0 Production on 화 10월 28 16:55:38 2014

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

다음에 접속됨:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

SQL>

새로운 job을 등록하는 테스트

job_test01 테이블 생성 후 1분에 1번씩 해당테이블에 데이터 insert 함 

테스트용 테이블과 시퀀스, 프로시저를 생성함

SCOTT>create sequence seq_job_seq1;

Sequence created.

SCOTT>create table job_test01

  2  (no number,

  3  name varchar2(5));

Table created.

SCOTT>ed

Wrote file afiedt.buf

create or replace procedure insert_job_test01

 is

  begin

   insert into scott.job_test01

   values(seq_job_seq1.nextval, dbms_random.string('a',3));

end;

/

새로운 job을 등록하는 테스트

job_test1 이라는 테이블에 3초에 1회씩 데이터를 insert 하는 작업

작업을 위해서 create any job의 권한이 있어야 함 

scott 계정에 먼저 이 권한을 주고 실행함

SCOTT>conn / as sysdba

Connected.

SYS>grant create any job to scott;

Grant succeeded.

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 23 13:48:59 2014

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning option

SYS>set line 200

SYS>col tablespace_name for a10

SYS>col file_name for a50

SYS>select tablespace_name, bytes/1024/1024 MB, file_name

  2  from dba_data_files;

TABLESPACE         MB FILE_NAME

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

EXAMPLE       345.625 /home/oracle/disk4/example01.dbf

USERS               5 /home/oracle/disk5/users01.dbf

UNDOTBS1          310 /home/oracle/disk5/undo01.dbf

SYSAUX            510 /home/oracle/disk4/sysaux01.dbf

SYSTEM            710 /home/oracle/disk4/system01.dbf

HAKSA                 /app/oracle/product/11g/dbs/MISSING00006

HAKSA                 /app/oracle/product/11g/dbs/MISSING00007

7 rows selected.

SYS>SYS>create tablespace ts_webhard

  2  datafile '/app/oracle/oradata/testdb/ts_web01.dbf' size 100M;

Tablespace created.

SYS>create tablespace ts_web_idx

  2  datafile '/app/oracle/oradata/testdb/ts_web_idx01.dbf' size 100M;

Tablespace created.

SYS>set line 200

SYS>col default_tablespace for a10

SYS>col temporary_tablespace for a10

SYS>select username, default_tablespace "Defautlt TS", temporary_tablespace "Temp TS"

  2  from dba_users

  3  where username='WEBUSER';

USERNAME                       Defautlt TS                    Temp TS

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

WEBUSER                        TS_WEBHARD                     TEMP_WEB

로그인 시도 5회 실패시 계정을 5일 동안 사용 못하게 할 것

계정의 암호는 10일에 한 번씩 변경하게 할 것

동일한 암호는 10일 동안 사용 못하게 할 것

SYS>create profile sample_prof limit

  2  failed_login_attempts 5

  3  password_lock_time 5

  4  password_life_time 10

  5  password_reuse_time 10;

Profile created.

1명당 연속적으로  cpu를 사용할 수 있는 시간을 10초로 제한할 것

하루 중 8시간만 db에 접속 가능하게 할 것

10분 동안 사용하지 않으면 강제로 접속을 끊을 것

profile을 9i 이상 버전에서 즉시 적용시키기 위해서 입력하는 명령어

SYS>alter system set resource_limit=true;

System altered.

scott 사용자에게 create table, create session 권한을 할당

SYS>grant create table, create session to scott;

Grant succeeded.

scott 사용자에게 create table 권한 해제

SYS>select * from dba_sys_privs

  2  where grantee='SCOTT';

GRANTEE                        PRIVILEGE                                ADM

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

SCOTT                          UNLIMITED TABLESPACE                 NO

SCOTT                          CREATE SESSION                           NO

WEBUSER>create table webtest

  2  ( no number,

  3  name varchar2(10));

Table created.

WEBUSER>insert into webtest values(1, 'sss');

1 row created.

WEBUSER>select * from webtest;

        NO NAME

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

         1 sss

SCOTT>select * from webuser.webtest;

select * from webuser.webtest

                      *

ERROR at line 1:

ORA-00942: table or view does not exist

role 생성하기

SYS>create role trole;

Role created.

role에 create session, create table 권한 할당하기

SYS>grant create session, create table to trole;

Grant succeeded.

scott 사용자에게 trole 할당하기

SYS>grant trole to scott;

Grant succeeded.

어떤 사용자가 어떤 role를 사용하는지 확인하기

SYS>SELECT * FROM dba_sys_privs

  2  WHERE grantee='SCOTT';

GRANTEE                        PRIVILEGE                                ADM

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

SCOTT                          UNLIMITED TABLESPACE                     NO

SCOTT                          CREATE SESSION                           NO

현재 운영중인 control file의 경로 확인 후

/home/oracle/disk1/control01.ctl

/home/oracle/disk2/control02.ctl

/home/oracle/disk3/controlo03.ctl 로 다중화(복사)

1. DB 상태 확인

2. 현재 운영중인 control file의 경로 확인

3. spfile경로 변경

4. instance 종료

5. 대상 디렉토리 생성 및 컨트롤 파일을 spfile 수정한 경로로 생성

6. instance  open -> startup

간략히 

spfile의 내용 변경 -> instance 종료 -> control file 복사 -> instance open

현재 db 상태 확인

SYS>select status from v$instance;

STATUS

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

OPEN

spfile 인지 pfile 인지 확인

SYS>show parameter spfile;

NAME     TYPE VALUE

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

spfile     string /app/oracle/product/11g/dbs/sp

filetestdb.ora

조회한 컨트롤 파일 위치를 아래 경로로 다중화

/home/oracle/disk4/control01.ctl

/home/oracle/disk5/control01.ctl

/home/oracle/disk6/control01.ctl

SYS>!ls $ORACLE_HOME/dbs

hc_DBUA0.dat  hc_testdb.dat  init.ora  lkTESTDB  orapwtestdb  spfiletestdb.ora

SYS>create pfile from spfile;

File created.

SYS>!rm -f $ORACLE_HOME/dbs/spfiletestdb.ora

SYS>!ls $ORACLE_HOME/dbs

hc_DBUA0.dat  hc_testdb.dat  init.ora  inittestdb.ora  lkTESTDB  orapwtestdb

SYS>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS>startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size    1344616 bytes

Variable Size  322964376 bytes

Database Buffers   92274688 bytes

Redo Buffers    6086656 bytes

Database mounted.

Database opened.

pfil 확인, 아래 조회내용에서 value 부분에 아무것도 나오지 않으면 pfile

SYS>show parameter pfile;

NAME     TYPE VALUE

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

spfile     string

SYS>select name from v$controlfile;

NAME

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

/home/oracle/disk1/control01.ctl

/home/oracle/disk2/control02.ctl

/home/oracle/disk3/control03.ctl

SYS>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS>!vi $ORACLE_HOME/dbs/inittestdb.ora

testdb.__db_cache_size=92274688

testdb.__java_pool_size=4194304

testdb.__large_pool_size=4194304

testdb.__oracle_base='/app/oracle'#ORACLE_BASE set from environment

testdb.__pga_aggregate_target=155189248

testdb.__sga_target=268435456

testdb.__shared_io_pool_size=0

testdb.__shared_pool_size=159383552

testdb.__streams_pool_size=0

*.audit_file_dest='/app/oracle/admin/testdb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

주석처리 한 부분을 파란색 글씨처럼 원하는 경로로 수정해줌

#*.control_files='/home/oracle/disk1/control01.ctl','/home/oracle/disk2/control02.ctl','/home/oracle/disk3/control03.ctl'

*.control_files='/home/oracle/disk4/control01.ctl','/home/oracle/disk5/control02.ctl','/home/oracle/disk6/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='testdb'

*.db_recovery_file_dest='/home/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4196401152

*.diagnostic_dest='/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=422576128

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

~                                                                                                                  

"/app/oracle/product/11g/dbs/inittestdb.ora" 26L, 950C

:wq!

SYS>!

[oracle@localhost ~]$ cd /home/oracle

[oracle@localhost ~]$ mkdir disk4 disk5 disk6

[oracle@localhost ~]$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk4/control01.ctl

[oracle@localhost ~]$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk5/control02.ctl

[oracle@localhost ~]$ cp /home/oracle/disk1/control01.ctl /home/oracle/disk6/control03.ctl

[oracle@localhost ~]$ exit

exit

SYS>startup

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size    1344616 bytes

Variable Size  322964376 bytes

Database Buffers   92274688 bytes

Redo Buffers    6086656 bytes

Database mounted.

Database opened.

SYS>select name from v$controlfile;

NAME

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

/home/oracle/disk4/control01.ctl

/home/oracle/disk5/control02.ctl

/home/oracle/disk6/control03.ctl

SYS>startup nomount;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             322964376 bytes

Database Buffers           92274688 bytes

Redo Buffers                6086656 bytes

SYS>alter database mount;

Database altered.

SYS>alter database open;

Database altered.

SYS>startup mount;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             322964376 bytes

Database Buffers           92274688 bytes

Redo Buffers                6086656 bytes

Database mounted.

SYS>alter database open read only;

Database altered.

SYS>select status from v$instance;    db 현재 상태 조회

STATUS

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

OPEN

SYS>alter system enable restricted session;    제한된 모드 사용

System altered.

SYS>alter system disable restricted session;    제한된 모드 사용안 함

System altered.

SYS>show parameter processes;

NAME                                 TYPE        VALUE

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

aq_tm_processes                      integer     0

db_writer_processes                  integer     1

gcs_server_processes                integer     0

global_txn_processes                 integer     1

job_queue_processes                 integer     1000

log_archive_max_processes        integer     4

processes                                 integer     150

SYS>col ksppinm for a40

SYS>col ksppstvl for a40

SYS>SELECT a.ksppinm, b.ksppstvl

  2  FROM x$ksppi a, x$ksppsv b

  3  WHERE a.indx=b.indx

  4  AND a.ksppinm like '%_size';

KSPPINM                                  KSPPSTVL

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

sga_max_size                             423624704

_NUMA_pool_size                        Not specified

_realfree_heap_max_size              32768

_pga_large_extent_size                 1048576

_uga_cga_large_extent_size           262144

[oracle@localhost ~]$ pmap `pgrep -f pmon`

3732:   ora_pmon_testdb

00110000   2172K r-x--  /app/oracle/product/11g/lib/libnnz11.so

0032f000    184K rwx--  /app/oracle/product/11g/lib/libnnz11.so

0035d000      8K rwx--    [ anon ]

0035f000    120K r-x--  /app/oracle/product/11g/lib/libdbcfg11.so

0037d000      8K rwx--  /app/oracle/product/11g/lib/libdbcfg11.so

38000000   4096K rwxs-  /dev/shm/ora_testdb_5341196_96

38400000   4096K rwxs-  /dev/shm/ora_testdb_5341196_97

38800000   4096K rwxs-  /dev/shm/ora_testdb_5341196_98

38c00000   4096K rwxs-  /dev/shm/ora_testdb_5341196_99

39000000   4096K rwxs-  /dev/shm/ora_testdb_5341196_100

39400000   4096K rwxs-  /dev/shm/ora_testdb_5341196_101

bf85d000     84K rwx--    [ stack ]

 total   578232K

만약 발생할 장애를 대비해 변경(DDL,DML,TCL 등 ) 전의 내용과 변경 후의 내용을 모두 기록해 둠

redo                  :  다시 한다.

redo log buffer    :  이 내용이 기록되는 메모리  

redo log file        :  이 내용이 기록되는 리두로그파일

redo log             :  변경되는 내용을 모두 기록, 장애를 대비하는 기능

Redo log에 기록한 후, 실제 데이터를 변경하는 것

- Redo log에 먼저 기록하는 이유?

redo log에 복구 관련 중요 정보가 저장되어 있기 때문

commit 요청 - 모든 redo record 들을 redo log file에 저장 - commit 완료 

redo log file에 저장하지 않으면 commit 완료 안 됨

배경 : 대량의 데이터 변경 후 commit 이 한꺼번에 수행 -> 성능에 악영향을 미침

-Delayed commit(지연된 커밋) 

-Group commit(그룹커밋) : 아주 짧은 시간동안 모아서 한꺼번에 commit을 수행

-비동기식 커밋 : LGWR이 관련된 redo log를 다 기록 후  dbwr이 데이터를 기록(동기식 커밋)하는데 

성능문제 때문에 이것이 나옴

- current  : 현재 LGWR이 내용을 기록하고 있는 상태

- active   : redo log file 내용이 DB buffer cache 에서 data file이 저장이 되지 않은 상태,

                지워지면 안되는 상태임

- inactive : redo log file 의 내용이 data  file 에 모두 저장이 된 상태, 

                삭제되어도 된다는 의미

redo log file의 상태(status) 를 inactive 를 만들고 난 다음

os 명령어로 해당 경로 위치로 rm 명령어로 삭제해야 한다. 

절대로!!!! 이런 변경 없이 os에서 redo log file을 삭제하면 안됨