h1.Chapter 02 아키텍터 개요
#오라클은 이식이 매우 편리하도록 설계된 데이터베이스(Windows, Unix, Linux, Mainframe)
-. Unix - 멀티 Process 기반 위에서 실행
-. Windows - Thread(단일 프로세스처럼 실행)
인스턴스 기동 전
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> pwd
/u02/app/oracle/product/10.2.0/db_1/dbs
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> ls -l
total 0
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> ps -aef|grep oracle
root 8284 8283 0 16:11 ? 00:00:00 login -- oracle
oracle 8285 8284 0 16:11 pts/1 00:00:00 -bash
oracle 8354 8285 0 16:11 pts/1 00:00:00 ps -aef
oracle 8355 8285 0 16:11 pts/1 00:00:00 grep oracle
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> ipcs -a |grep oracle
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs>
***** 아직 기동 전, 프로세스, 메모리, 세마포아 모두 존재 하지 않음
SID 해제 후
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> set |grep ORACLE_SID
ORACLE_SID=TGGO
PS1='[${ORACLE_SID}]teng.com:$PWD> '
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> unset ORACLE_SID
[]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> set |grep ORACLE_SID
PS1='[${ORACLE_SID}]teng.com:$PWD> '
_=ORACLE_SID
[]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> echo $ORACLE_SID
[]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt>
[]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> echo $ORACLE_SID
[]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 14 16:15:23 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12162: TNS:net service name is incorrectly specified
Enter user-name:
***** 이와 같은 오류 메시지는 데이터베이스 소프트웨어가 접속할 대상을 알 수 없을 때 발생
임의의 SID 지정 후 접속 테스트
[]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> export ORACLE_SID=TESTGO
[TESTGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs/ttt> sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 14 16:17:03 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> !ps -ef |grep oracle |grep -v grep
root 8284 8283 0 16:11 ? 00:00:00 login -- oracle
oracle 8285 8284 0 16:11 pts/1 00:00:00 -bash
oracle 8535 8285 0 16:17 pts/1 00:00:00 sqlplus
oracle 8536 8535 0 16:17 ? 00:00:00 oracleTESTGO (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 8555 8554 0 16:17 pts/1 00:00:00 ps -ef
SQL> !ipcs -a |grep oracle
SQL>
***** 오라클 서버 프로세스만 기동(다른 프로세스 없음), 공유 메모리 아직 할당 안됨
초기화 화일(DB_NAME만 설정) 후
SQL> !ls -al $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 93300139 Aug 31 15:57 /u02/app/oracle/product/10.2.0/db_1/bin/oracle
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u02/app/oracle/product/10.2.0/db_1/dbs/initTESTGO.ora'
***** 기동을 위해서 파라미터 파일 필요
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> cd $ORACLE_HOME/db
[TGGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> export ORACLE_SID=TESTGO
[TESTGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> echo db_name=TESTGO > initTESTGO.ora
[TESTGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/dbs> cat initTESTGO.ora
db_name=TESTGO
SQL> startup nomount
ORACLE instance started.
Total System Global Area 113246208 bytes
Fixed Size 1218004 bytes
Variable Size 58722860 bytes
Database Buffers 50331648 bytes
Redo Buffers 2973696 bytes
SQL> !ps -ef |grep oracle |grep -v grep |grep -v login | grep -v bash
oracle 8535 8285 0 16:17 pts/1 00:00:00 sqlplus
oracle 8805 1 0 16:23 ? 00:00:00 ora_pmon_TESTGO
oracle 8807 1 0 16:23 ? 00:00:00 ora_psp0_TESTGO
oracle 8809 1 0 16:23 ? 00:00:00 ora_mman_TESTGO
oracle 8811 1 0 16:23 ? 00:00:00 ora_dbw0_TESTGO
oracle 8813 1 0 16:23 ? 00:00:00 ora_lgwr_TESTGO
oracle 8815 1 0 16:23 ? 00:00:00 ora_ckpt_TESTGO
oracle 8822 1 0 16:23 ? 00:00:00 ora_smon_TESTGO
oracle 8824 1 0 16:23 ? 00:00:00 ora_reco_TESTGO
oracle 8826 1 0 16:23 ? 00:00:00 ora_mmon_TESTGO
oracle 8836 1 0 16:23 ? 00:00:00 ora_mmnl_TESTGO
oracle 8837 8535 0 16:23 ? 00:00:00 oracleTESTGO (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 10882 10881 0 17:23 pts/1 00:00:00 ps -ef
SQL>
***** pmon, lgwr, ckpt, smon 등 백그라운드 프로세스 확인
SQL> !ipcs -a |grep oracle
0x1dfca164 65537 oracle 640 115343360 11
0x9cac152c 98304 oracle 640 44
SQL> !ipcs -m |grep oracle
0x1dfca164 65537 oracle 640 115343360 11
SQL> !ipcs -s |grep oracle
0x9cac152c 98304 oracle 640 44
***** -m shared memory segments
***** -s semaphore arrays
SQL> show parameters sga
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 108M
sga_target big integer 0
SQL> show parameter shared_pool
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
shared_pool_reserved_size big integer 1677721
shared_pool_size big integer 32M
SQL> show parameters buffer
SQL> show parameters background_dump_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
background_dump_dest string /u02/app/oracle/product/10.2.0/db_1/rdbms/log
***** 이후 ALERT LOG
[TESTGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/rdbms/log> cat alert_TESTGO.log
Fri Sep 14 16:23:32 2012
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as /u02/app/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned off.
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.
System parameters with non-default values:
db_name = TESTGO
PMON started with pid=2, OS id=8805
MMAN started with pid=4, OS id=8809
PSP0 started with pid=3, OS id=8807
DBW0 started with pid=5, OS id=8811
LGWR started with pid=6, OS id=8813
SMON started with pid=8, OS id=8822
RECO started with pid=9, OS id=8824
CKPT started with pid=7, OS id=8815
MMON started with pid=10, OS id=8826
MMNL started with pid=11, OS id=8836
[TESTGO]teng.com:/u02/app/oracle/product/10.2.0/db_1/rdbms/log>
DB 생성 테스트
SQL> create database ;
create database
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Fri Sep 14 17:27:53 2012
create database
Fri Sep 14 17:27:53 2012
WARNING: Default Temporary Tablespace not specified in CREATE DATABASE command
Default Temporary Tablespace will be necessary for a locally managed database in future release
Fri Sep 14 17:27:54 2012
Database mounted in Exclusive Mode
Fri Sep 14 17:28:01 2012
Successful mount of redo thread 1, with mount id 3467921881
Assigning activation ID 3467921881 (0xceb449d9)
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: /u02/app/oracle/product/10.2.0/db_1/dbs/log1TESTGO.dbf
Successful open of redo thread 1
Fri Sep 14 17:28:01 2012
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri Sep 14 17:28:01 2012
SMON: enabling cache recovery
Fri Sep 14 17:28:01 2012
create tablespace SYSTEM datafile '?/dbs/dbs1@.dbf' SIZE 81920000 REUSE
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
Fri Sep 14 17:28:07 2012
Completed: create tablespace SYSTEM datafile '?/dbs/dbs1@.dbf' SIZE 81920000 REUSE
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
Fri Sep 14 17:28:07 2012
create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Fri Sep 14 17:28:08 2012
Errors in file /u02/app/oracle/product/10.2.0/db_1/rdbms/log/testgo_ora_8837.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4108 bytes of shared memory ("shared pool","insert into ts$ (ts#,name,on...","Typecheck","kgghtInit")
Fri Sep 14 17:28:08 2012
Errors in file /u02/app/oracle/product/10.2.0/db_1/rdbms/log/testgo_ora_8837.trc:
ORA-01501: CREATE DATABASE failed
ORA-01519: error while processing file '?/rdbms/admin/sql.bsq' near line 1740
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4108 bytes of shared memory ("shared pool","insert into ts$ (ts#,name,on...","Typecheck","kgghtInit")
Error 1519 happened during db open, shutting down database
USER: terminating instance due to error 1519
Instance terminated by USER, pid = 8837
ORA-1092 signalled during: create database
...
데이타베이스 구성 화일 조회
[TESTGO]teng.com:/u01/app/oracle> export ORACLE_SID=TGGO
[TGGO]teng.com:/u01/app/oracle>
SQL> select name from v$datafile ;
NAME
------------------------------------------------------
/u02/oracle/DATA/TGGO/system01.dbf
/u02/oracle/DATA/TGGO/undotbs01.dbf
/u02/oracle/DATA/TGGO/sysaux01.dbf
/u02/oracle/DATA/TGGO/users01.dbf
/u01/oracle/DATA/TGGO/sysaux02.dbf
SQL> select member from v$logfile ;
MEMBER
-------------------------------------------------------
/u02/oracle/DATA/TGGO/redo03.log
/u02/oracle/DATA/TGGO/redo02.log
/u02/oracle/DATA/TGGO/redo01.log
SQL> select name from v$controlfile ;
NAME
-------------------------------------------------------
/u02/oracle/DATA/TGGO/control01.ctl
/u02/oracle/DATA/TGGO/control02.ctl
/u02/oracle/DATA/TGGO/control03.ctl
하루살이 인스턴스
SQL> alter database close ;
Database altered.
SQL> alter database open ;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
SQL>
**** 인스턴스는 인스턴스가 구동 중에는 기껏해야 하나의 데이터베이스만 마운트하고 오픈할 수 있다.
새로운 데이터베이스나 다른 데이타베이스를 열기 위해서는 이 인스턴스를 버리고(shutdown)
새로운 인스턴스를 생성(startup)하여야 한다.
*요약*
1. 하나의 인스턴스는 백그라운드 프로세스와 공유 메모리의 합집합
2. 데이터베이스는 디스크에 저장된 데이터의 집합이다.
3. 인스턴스는 오직 하나의 데이터베이스만 마운트하고 오픈할 수 있다.
4. 하나의 데이터베이스를 하나 혹은 여러 인스턴스(RAC)에서 마운트하고 오픈할수 있고,
하나의 데이터베이스에 마운트된 인스턴스 수는 시간에 따라 변경 될 수 있다.
*Installing Oracle RAC on Different Operating Systems*
All nodes must run the same operating system, that is, they must be binary compatible.
Oracle RAC does not support machines having different chip architectures in the same cluster.
However, you can have machines of different speeds and sizes in the same cluster.
백그라운드 프로세스 조회
SQL> col program for a50
SQL> select PROGRAM, SPID from v$process WHERE BACKGROUND = 1 ;
PROGRAM SPID
-------------------------------------------------- ---------------
oracle@teng.com (PMON) 11702
oracle@teng.com (PSP0) 11704
oracle@teng.com (MMAN) 11706
oracle@teng.com (DBW0) 11708
oracle@teng.com (LGWR) 11710
oracle@teng.com (CKPT) 11712
oracle@teng.com (SMON) 11714
oracle@teng.com (RECO) 11716
oracle@teng.com (CJQ0) 11718
oracle@teng.com (MMON) 11720
oracle@teng.com (MMNL) 11722
oracle@teng.com (ARC0) 11732
oracle@teng.com (ARC1) 11734
oracle@teng.com (QMNC) 11738
oracle@teng.com (q000) 11762
oracle@teng.com (q001) 11764
16 rows selected