물리적인 DB관리 (1)

oracle = Database (물리적인파일) + Instance (메모리)

**Database (물리적인파일) 설명
D:\oracle\oradata\DBH1 밑에 파일...

① Control file (CTL) : 오라클 서버의 현재(전체) 상태정보를 나타내는 파일, 물리적인 파일의 상태 동기화, 최소1개

② redolog file (LOG) : Database상의 (변경상황) 트랜잭션(insert,update,delete) 저장, 최소2개 파일(그룹) = MEMBER

③ Data file (DBF) : 실제로 생각하는 데이터가 저장되는 파일(실제 데이터 저장)
- data
- undo (rollback) segment : DB복구시 버퍼와 (변경전 값) 비교해서 일관성 검사

④ initial parameter file : 초기 파라미터 파일(오라클서버 시작시 초기 환경변수를 읽어들이는 파일)

⑤ password file : 오라클 관리가자(특정계정이) 사용자 정보를 넣어주는 파일, 인증관련파일

파라미터(변수처럼사용)=>오라클이 시작될 때 필요한 모든정보를 가진 파일=>초기 파라미터

 

SQL> select * from v$controlfile;

STATUS   NAME
-------   -------------------------------------

D:\ORACLE\ORADATA\DBH1\CONTROL01.CTL

D:\ORACLE\ORADATA\DBH1\CONTROL02.CTL

D:\ORACLE\ORADATA\DBH1\CONTROL03.CTL

 

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE     MEMBER
---------- ------- -------  --------------------------------------------------
         3      STALE   ONLINE     D:\ORACLE\ORADATA\DBH1\REDO03.LOG
         2                  ONLINE     D:\ORACLE\ORADATA\DBH1\REDO02.LOG

         1         ONLINE
D:\ORACLE\ORADATA\DBH1\REDO01.LOG

SQL> select * from v$log;

  GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS               FIRST_CHANGE#   FIRST_TI
---------- ---------- ---------- ---------- ---------- --- ---------------- -------------     --------
         1              1               2        104857600          1        NO  CURRENT                 257848             06/07/04
         2              1               0        104857600          1        YES UNUSED                       0
         3              1               1        104857600          1        NO  INACTIVE                  190578             06/07/03

SQL> select name from v$datafile;

NAME
-------------------------------------
-------------------------------------
D:\ORACLE\ORADATA\DBH1\SYSTEM01.DBF
D:\ORACLE\ORADATA\DBH1\UNDOTBS01.DBF
D:\ORACLE\ORADATA\DBH1\CWMLITE01.DBF
D:\ORACLE\ORADATA\DBH1\DRSYS01.DBF
D:\ORACLE\ORADATA\DBH1\EXAMPLE01.DBF
D:\ORACLE\ORADATA\DBH1\INDX01.DBF
D:\ORACLE\ORADATA\DBH1\ODM01.DBF
D:\ORACLE\ORADATA\DBH1\TOOLS01.DBF
D:\ORACLE\ORADATA\DBH1\USERS01.DBF
D:\ORACLE\ORADATA\DBH1\XDB01.DBF

SQL> show parameter db

NAME                                                    TYPE        VALUE
------------------------------------ ----------- ----------
db_16k_cache_size                                    big integer    0
db_2k_cache_size                                      big integer    0
db_32k_cache_size                                     big integer    0
db_4k_cache_size                                       big integer    0
db_8k_cache_size                                       big integer    0
db_block_buffers                                         integer         0
db_block_checking                                     boolean     FALSE
db_block_checksum                                    boolean     TRUE
db_block_size                                             integer     8192
db_cache_advice                                         string      ON
db_cache_size                                           big integer 25165824
.....

%% 위 5가지를 모두 백업해놓아야 오라클 시작가능, 파일이 다르게 백업되면 복구안됨 %%

D:\oracle\ora92\database\PWDDBH1.ora
D:\oracle\ora92\database\SPFILEDBH1.ORA => 9i부터 기본값으로 오라클 시작함

 

**  Instance (메모리) 설명

Instance = SGA + BGP
SGA => 크기가 클수록 성능이 좋아진다

DB buffer cache : 사용되었던 Data를 가지는 메모리, block 단위 저장
- dirty block : 데이터가 save된 block, 기본 8kbyte block
- free block : 데이터가 비어있는 block
- pin block : 데이터가 저장중인 block

LRU 알고리즘(LRU 원리) = FIFO (가장최근에 사용된 데이터가 입구에 저장)

DBWR : DB buffer cache에서 실제로 DB 파일로 dirty block 저장
(실제로 기억해서 저장)
redolog buffer : 트랜잭션 저장
SMON : 트랜잭션과 undo segment file 을 비교해서 일관성 체크
           1개 block의 size 4배, DB buffer size의 기본 4배
LGWR : redolog buffer 를 실제 redolog file(*.LOG)로 저장해주는 역할

Shared pool : 여러사용자들이 객체정보를 공유해서 사용하기 때문에 공유영역이라한다
- Library cache : 가장 최근에 사용한(분석된,파싱된) 문장(명령,객체명,뷰 등)
- Data dictionary cache : 데이터 사전으로부터 가장 최근의 객체정보를 가지고 있음
                                  대소문자 정확하게 구분한다

PMON : 프로세서가 오류를 발생시키면 감시(프로세서의 오류 체크,감시)

%% 메모리에 있는 정보를 많이 사용하는게 좋고, 메모리가 크면 튜닝 성능이 높아진다

CKPT : 버퍼 캐쉬에 있는 내용이 check point 가 발생시마다 DB 상태 정보 변경(갱신,보관)
check point (강제 발생 가능) : 버퍼에 있는 내용이 영구적으로 파일에 기록되는 시점

ARCH : redolog file 을 *.ARC 파일로 백업하는 역할, 며칠이 지나도 백업됨

DB mode
- ARCHIVE
- NOARCHIVE (default)

PGA
java_pool
large_pool

SQL> show sga

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

SQL> select * from v$sga;

NAME                              VALUE
-------------------- ----------
Fixed Size                        453492
Variable Size                109051904
Database Buffers            25165824
Redo Buffers                    667648

SQL> show parameter db_cache

NAME                                 TYPE        VALUE
----------------------- ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 25165824

SQL> show parameter log_buffer  (redolog buffer size)

NAME                                 TYPE        VALUE
-------------------- ----------- ------------------------------
log_buffer                           integer     524288

SQL> show parameter share

NAME                                 TYPE        VALUE
----------------------- ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer     20
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 2516582
shared_pool_size                     big integer 50331648
shared_server_sessions               integer     165
shared_servers                       integer     1

SQL> select name from v$bgprocess;  (bgprocess 를 보는 동적뷰)

NAME
-----
PMON
DIAG
FMON
LMON
LMD0
LMS0
LMS1
LMS2
LMS3
LMS4
LMS5
......

SQL> select name,log_mode from v$database;

NAME      LOG_MODE
--------- ------------
DBH1      NOARCHIVELOG

SQL> show parameter pool

NAME                                 TYPE        VALUE
---------------------- -----------  ---------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size          string
java_pool_size                       big integer 33554432
large_pool_size                      big integer 8388608
olap_page_pool_size                integer     33554432
shared_pool_reserved_size       big integer 2516582
shared_pool_size                    big integer 50331648

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME       VERSION           STARTUP_ STATUS
--------------- ---------------- ---------------- ----------------- --------------------
                1               dbh1             KH40219               9.2.0.1.0                06/07/04

       PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU
 ------------ --- ---------- -------      ----------- ---------- ---
        OPEN     NO           1      STOPPED      ALLOWED    NO

DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
----------------- ------------------ ---------
ACTIVE            PRIMARY_INSTANCE   NORMAL

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME   CONTENTS
------------------  ---------
SYSTEM                   PERMANENT
UNDOTBS1               UNDO
TEMP                       TEMPORARY  -- temp 정렬작업,임시테이블스페이스
CWMLITE                  PERMANENT
DRSYS                     PERMANENT
EXAMPLE                  PERMANENT
INDX                         PERMANENT
ODM                        PERMANENT
TOOLS                     PERMANENT
USERS                     PERMANENT
XDB                          PERMANENT

SQL> desc dba_users
 이름                                                                                                      널?      유형
 ----------------------------------------------------------------------------------------------------------------- -------- ------
----------------------------------------------------------------------
 USERNAME                                                                                                  NOT NULL VARCHAR2(30)
 USER_ID                                                                                                   NOT NULL NUMBER
 PASSWORD                                                                                                           VARCHAR2(30)
 ACCOUNT_STATUS                                                                                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                                                                                          DATE
 EXPIRY_DATE                                                                                                        DATE
 DEFAULT_TABLESPACE                                                                                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                                                                                      NOT NULL VARCHAR2(30)
 CREATED                                                                                                   NOT NULL DATE
 PROFILE                                                                                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                                                                                        VARCHAR2(30)
 EXTERNAL_NAME                                                                                                      VARCHAR2(4000)

 

SQL> select username,temporary_tablespace,default_tablespace from dba_users;

USERNAME          TEMPORARY_TABLESPACE   DEFAULT_TABLESPACE
--------------- ------------------------ -------------------------
SYS                            TEMP                           SYSTEM
SYSTEM                         TEMP                           SYSTEM
DBSNMP                         TEMP                           SYSTEM
SKY                            TEMP                           SYSTEM
SCOTT                          TEMP                           SYSTEM
TEST                           TEMP                           SYSTEM
OUTLN                          TEMP                           SYSTEM
WMSYS                          TEMP                           SYSTEM
ORDSYS                         TEMP                           SYSTEM
ORDPLUGINS                     TEMP                           SYSTEM
MDSYS                          TEMP                           SYSTEM

USERNAME          TEMPORARY_TABLESPACE   DEFAULT_TABLESPACE
--------------- ------------------------ -------------------------
CTXSYS                         TEMP                           DRSYS
QS_ES                          TEMP                           EXAMPLE
QS_WS                          TEMP                           EXAMPLE
QS                             TEMP                           EXAMPLE
QS_ADM                         TEMP                           EXAMPLE
SH                             TEMP                           EXAMPLE
PM                             TEMP                           EXAMPLE
OE                             TEMP                           EXAMPLE
HR                             TEMP                           EXAMPLE
RMAN                           TEMP                           TOOLS
QS_CS                          TEMP                           EXAMPLE

USERNAME          TEMPORARY_TABLESPACE   DEFAULT_TABLESPACE
--------------- ------------------------ -------------------------
QS_CB                          TEMP                           EXAMPLE
QS_CBADM                       TEMP                           EXAMPLE
QS_OS                          TEMP                           EXAMPLE
XDB                            TEMP                           XDB
ANONYMOUS                      TEMP                           XDB
WKSYS                          TEMP                           DRSYS
WKPROXY                        TEMP                           DRSYS
ODM                            TEMP                           ODM
ODM_MTR                        TEMP                           ODM
OLAPSYS                        TEMP                           CWMLITE

%%% 위에서 본 물리적인 파일 경로 => D:\oracle\oradata\DBH1

@@@ DEFAULT_TABLESPACE 를 SYSTEM->USERS 로 변경해주기
SQL> conn /as sysdba
연결되었습니다.

SQL> alter user scott default tablespace users;

사용자가 변경되었습니다.

$$$ alter user user_name default tablespace users;

 

↑↑↑↑↑↑↑↑↑↑↑↑↑↑ 오전까지 수요일 쪽지시험 ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

<<오후>>
오라클 서비스의 단계

관리자 로그인
SQL> conn /as sysdba

SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

SQL> startup nomount
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes

SQL> alter database mount;

데이타베이스가 변경되었습니다.

SQL> alter database open;

데이타베이스가 변경되었습니다.

SQL> desc v$session
SQL> select username,sid,serial# from v$session;

USERNAME                     SID         SERIAL#
-------------------- ---------- ----------
                                        1          1
                                        2          1
                                        3          1
                                        4          1
                                        5          1
                                        6          1
                                        7          1
                                        8          1
SYS                                  9          1

-- 다른창을 열고 SKY 로 로그인후 select 하면~
SQL> select username,sid,serial# from v$session;

USERNAME                     SID         SERIAL#
-------------------- ---------- ----------
                                        1          1
                                        2          1
                                        3          1
                                        4          1
                                        5          1
                                        6          1
                                        7          1
                                        8          1
SYS                                  9          1
SKY                                  10         32

SQL> alter system kill session '10,32';

시스템이 변경되었습니다.

SQL> select username,sid,serial# from v$session;
sky 로 로그인한 창에 가서~

SQL> select * from dept;
select * from dept
*
1행에 오류:
ORA-00028: 세션이 종료되었습니다

SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

SQL> startup open restrict;
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SQL> conn sky/pass
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

경고: 이제는 ORACLE에 연결되어 있지 않습니다.

==>>> 일반계정은 로그온 못하게 할때,현재는 관리자만 로그인가능

SQL> conn /as sysdba
연결되었습니다.

SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.
SQL> alter system disable restricted session;

SQL> startup open force
==>>강제종료 후 다시 시작(무조건 서비스를 다시 시작하는 옵션)

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
dbh1

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
DBH1      READ WRITE

SQL> help shutdown

 SHUTDOWN
 --------

 Shuts down a currently running Oracle instance, optionally closing
 and dismounting a database.

 SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]

SQL> conn /as sysoper
ERROR:
ORA-01031: insufficient privileges

경고: 이제는 ORACLE에 연결되어 있지 않습니다.

<< D:\oracle\ora92\network\admin\sqlnet.ora >>

SQL> col name format a40
SQL> select name,status from v$datafile;

NAME                                     STATUS
---------------------------------------- -------
D:\ORACLE\ORADATA\DBH1\SYSTEM01.DBF      SYSTEM
D:\ORACLE\ORADATA\DBH1\UNDOTBS01.DBF     ONLINE
D:\ORACLE\ORADATA\DBH1\CWMLITE01.DBF     ONLINE
D:\ORACLE\ORADATA\DBH1\DRSYS01.DBF       ONLINE
D:\ORACLE\ORADATA\DBH1\EXAMPLE01.DBF     ONLINE
D:\ORACLE\ORADATA\DBH1\INDX01.DBF        ONLINE
D:\ORACLE\ORADATA\DBH1\ODM01.DBF         ONLINE
D:\ORACLE\ORADATA\DBH1\TOOLS01.DBF       ONLINE
D:\ORACLE\ORADATA\DBH1\USERS01.DBF       ONLINE
D:\ORACLE\ORADATA\DBH1\XDB01.DBF         ONLINE

SQL> select name,status,enabled from v$datafile;

NAME                                     STATUS  ENABLED
---------------------------------------- ------- ----------
D:\ORACLE\ORADATA\DBH1\SYSTEM01.DBF      SYSTEM  READ WRITE
D:\ORACLE\ORADATA\DBH1\UNDOTBS01.DBF     ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\CWMLITE01.DBF     ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\DRSYS01.DBF       ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\EXAMPLE01.DBF     ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\INDX01.DBF        ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\ODM01.DBF         ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\TOOLS01.DBF       ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\USERS01.DBF       ONLINE  READ WRITE
D:\ORACLE\ORADATA\DBH1\XDB01.DBF         ONLINE  READ WRITE

10 개의 행이 선택되었습니다.

SQL> select name,status,enabled from v$tempfile;   (9i 부터 temp파일만 따로 볼수 있게되었다)

NAME                                     STATUS  ENABLED
---------------------------------------- ------- ----------
D:\ORACLE\ORADATA\DBH1\TEMP01.DBF        ONLINE  READ WRITE

-- 데이터파일과 관련된 뷰

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ------------------------------
D:\ORACLE\ORADATA\DBH1\SYSTEM01.DBF      SYSTEM
D:\ORACLE\ORADATA\DBH1\UNDOTBS01.DBF     UNDOTBS1
D:\ORACLE\ORADATA\DBH1\CWMLITE01.DBF     CWMLITE
D:\ORACLE\ORADATA\DBH1\DRSYS01.DBF       DRSYS
D:\ORACLE\ORADATA\DBH1\EXAMPLE01.DBF     EXAMPLE
D:\ORACLE\ORADATA\DBH1\INDX01.DBF        INDX
D:\ORACLE\ORADATA\DBH1\ODM01.DBF         ODM
D:\ORACLE\ORADATA\DBH1\TOOLS01.DBF       TOOLS
D:\ORACLE\ORADATA\DBH1\USERS01.DBF       USERS
D:\ORACLE\ORADATA\DBH1\XDB01.DBF         XDB

10 개의 행이 선택되었습니다.

SQL> select file_name,tablespace_name from dba_temp_files;

FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ------------------------------
D:\ORACLE\ORADATA\DBH1\TEMP01.DBF        TEMP

-- 로그파일과 관련된 뷰

SQL> select group#,status,type,member from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------
         3 STALE   ONLINE  D:\ORACLE\ORADATA\DBH1\REDO03.LOG
         2         ONLINE  D:\ORACLE\ORADATA\DBH1\REDO02.LOG
         1         ONLINE  D:\ORACLE\ORADATA\DBH1\REDO01.LOG

SQL> select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ----------------
         1          2          1 CURRENT
         2          0          1 UNUSED
         3          1          1 INACTIVE

SQL> select * from v$thread;

   THREAD# STATUS ENABLED      GROUPS INSTANCE         OPEN_TIM CURRENT_GROUP#
---------- ------ -------- ---------- ---------------- -------- --------------
 SEQUENCE# CHECKPOINT_CHANGE# CHECKPOI ENABLE_CHANGE# ENABLE_T DISABLE_CHANGE#
---------- ------------------ -------- -------------- -------- ---------------
DISABLE_
--------
         1 OPEN   PUBLIC            3 dbh1             06/07/04              1
         2             337870 06/07/04         190578 06/07/03               0

 

-- 컨트롤 파일과 관련된 뷰

SQL> select * from v$controlfile;

STATUS  NAME
------- ----------------------------------------
        D:\ORACLE\ORADATA\DBH1\CONTROL01.CTL
        D:\ORACLE\ORADATA\DBH1\CONTROL02.CTL
        D:\ORACLE\ORADATA\DBH1\CONTROL03.CTL

SQL> select * from v$sgastat;  -- SGA 정보
-- BGP 정보

SQL> select * from v$bgprocess where paddr>'00';

SQL> col name format a10
SQL> col description format a30

SQL> /

PADDR    NAME       DESCRIPTION                         ERROR
-------- ---------- ------------------------------ ----------
681E46BC PMON       process cleanup                ##########
681E4A7C DBW0       db writer process 0            ##########
681E4E3C LGWR       Redo etc.                      ##########
681E51FC CKPT       checkpoint                     ##########
681E55BC SMON       System Monitor Process         ##########
681E597C RECO       distributed recovery           ##########
681E5D3C CJQ0       Job Queue Coordinator          ##########
681E60FC QMN0       AQ Time Manager Process 0      ##########

SQL> select * from v$process;

D:\oracle\ora92\database\SPFILEDBH1.ORA -- 오라클시작파일-- 열리지않는 샘플
D:\oracle\admin\DBH1\pfile\init.ora.632006143048 --물리적으로 볼수 있는 파일샘플

SQL>alter system enable restricted session;   -- 일반사용자 로그온 못하게...
SQL>alter system disable restricted session;   -- 반대

select * from dictionary;
select * from dict;
col table_name format a30
col comments format a45
(table_name : 뷰 이름, comments : 설명서)

SQL> select * from dict where upper(comments) like upper('%user%');

SQL> select * from dict where table_name like '%INDEX%';

SQL> select * from dict_columns where table_name='DBA_SYS_PRIVS';

TABLE_NAME              COLUMN_NAME    COMMENTS
-------------------- -------------   ----------------------------------------
DBA_SYS_PRIVS          GRANTEE              Grantee Name, User or Role receiving the grant

DBA_SYS_PRIVS          PRIVILEGE             System privilege
DBA_SYS_PRIVS          ADMIN_OPTION      Grant was with the ADMIN option

-- 동적 성능 뷰를 보는 뷰

SQL> select * from v$fixed_table;

-- data dictionary 뷰 목록표 만드는방법

SQL> set pages 800
SQL> spool c:\oracle_views
SQL> select * from dict order by 1;
SQL> select * from dict where table_name like '%USER%';
SQL> select * from dict where table_name like '%TABLE%';
SQL> spool off

select * from dictionary / dict_columns / v$fixed_table;

*** 초기 파라미터 파일 관리
-- initSID.ora / spfileSID.ora
-- oracle 9i 버전은 기본적으로 spfileSID.ora 를 사용한다
     경로 : [oracle\ora92\database\]

① initSID.ora : 반드시 shutdown 된 후 수동으로 편집(변경)한다
    기본경로 : D:\oracle\admin\DBH1\pfile\
-- 파라미터 값 변경
     DB shutdown => init 파일 메모장에서수정 => DB startup

② spfileSID.ora : 온라인 상태에서 (서비스(온라인)가 구동중에) 편집(변경) 가능하다 (바로 적용)
                        정적(수동) 파라미터인 경우는 반드시 (offline) 변경후 restart 해야 한다
                        기본경로 : D:\oracle\ora92\database\ (9i 는 이경로에 있어야만 파라미터로 인정한다)
-- 파라미터 값 변경 : 무조건 명령으로 수정만 가능
     alter system set 파라미터명=값 scope=[both | spfile];
      (both : 파일도 수정되고 지금사용되고 있는 메모리도 수정해라)
     => (옵션) [=> shutdown => startup]

-- 파라미터 생성
create spfile='경로' from pfile='경로';  == pfile 을 가지고 spfile 을 생성
create spfile from pfile;  == 기본경로로 생성
create pfile from spile;

*** 컨트롤 파일 관리 (multiplexing control file)
--방법 (spfile)
1.파라미터 변경(alter system ~~)
2.shutdown
3.원본에서 control file 복사
4.startup

--pfile 을 사용하는 경우
1.shutdown
2.파라미터 변경
3.원본에서 control file 복사
4.startup

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      D:\oracle\oradata\DBH1\CONTROL
                                                 01.CTL, D:\oracle\oradata\DBH1
                                                 \CONTROL02.CTL, D:\oracle\orad
                                                 ata\DBH1\CONTROL03.CTL
①
SQL> ed 0704_1
alter system set control_files=
"D:\oracle\oradata\DBH1\CONTROL01.CTL",
"D:\oracle\oradata\DBH1\CONTROL02.CTL",
"D:\oracle\oradata\DBH1\CONTROL03.CTL",
"D:\oracle\oradata\DBH1\CONTROL04.CTL" scope=spfile;
SQL> @ 0704_1
시스템이 변경되었습니다.

②
SQL> shutdown immediate
데이터베이스가 닫혔습니다.
데이터베이스가 마운트 해제되었습니다.
ORACLE 인스턴스가 종료되었습니다.

③
windows탐색기에서 CONTROL01.CTL 원본을 복사해서 이름바꾸기 CONTROL04.CTL

④
SQL> startup
ORACLE 인스턴스가 시작되었습니다.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
데이터베이스가 마운트되었습니다.
데이터베이스가 열렸습니다.

SQL> select * from v$controlfile;

 NAME
--------------------------------------------
 D:\ORACLE\ORADATA\DBH1\CONTROL01.CTL
 D:\ORACLE\ORADATA\DBH1\CONTROL02.CTL
 D:\ORACLE\ORADATA\DBH1\CONTROL03.CTL
 D:\ORACLE\ORADATA\DBH1\CONTROL04.CTL