728x90
--diskgroup 상세 정보 
ASMCMD> lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     10236     1804                0            1804              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     10240       44                0              44              0             N  RECO/
MOUNTED  EXTERN  N         512             512   4096  4194304     10240     9884                0            9884              0             Y  VOTE/
--disk 정보
ASMCMD> lsdsk -p
Group_Num  Disk_Num      Incarn  Mount_Stat  Header_Stat  Mode_Stat  State   Path
        1         0  4041319339  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/DATA
        2         0  4041319340  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/RECO
        3         0  4041319341  CACHED      MEMBER       ONLINE     NORMAL  /dev/ASM/VOTE
 
-- asm volume 생성
asmca -silent -createVolume \
-volumeName CDC \
-volumeDiskGroup RECO \
-volumeSizeGB 1TB
 
-- asm volume 정보 
ASMCMD> volinfo --all
sh: /oracle/base/product/19.12/bin/crsctl: No such file or directory
Diskgroup Name: RECO
 
 
     Volume Name: CDC
     Volume Device: /dev/asm/cdc-298
     State: REMOTE
     Size (MB): 10048
     Resize Unit (MB): 64
     Redundancy: UNPROT
     Stripe Columns: 8
     Stripe Width (K): 1024
     Usage: ACFS
     Mountpath: /cdc
 
-- ACFS 생성
asmca -silent -createACFS \
-acfsVolumeDevice /dev/asm/cdc-298 \
-acfsMountPoint /cdc \
-acfsUserName oracle \
-acfsGroupName dba
 
728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] RAC IP 변경 (Public, Private)  (0) 2022.06.28
[ORACLE] DB LINK  (1) 2022.06.28
[ORACLE] TDE  (0) 2022.06.27
[ORACLE] Partitioning  (0) 2022.06.27
[ORACLE] Archive mode 변경  (0) 2022.06.27
728x90
< TDE >
-Transparent Data Encryption 
-응용 프로그램의 수정 없이 DB 내부에서 컬럼, 테이블스페이스 레벨의 암호화를 하는 것
 
<TDE에서 지원되는 암호화 알고리즘>
 
- AES128 : AES (Advanced Encryption Standard), 128 bits (Key Size)
- AES192 : AES (Advanced Encryption Standard), 192 bits(defalut)(Key Size)
- AES256 : AES (Advanced Encryption Standard), 256 bits (Key Size)
- 3DES168 : Three DES (Data Encryption Standard), 168 bits (Key Size)
 
<TDE 암호화>
 1) Columns Level Encryption (10g,11g)
   : 테이블의 특정 컬럼만 암호화, 블록의 특정 부분만 암호화
 
2) Tablespace Level Encryption (11g)
   : 테이블스페이스 전체가 암호화, 테이블 사이즈의 증가가 없음.
 
- System, Sysaux, Undo, Temp 의 경우 테이블스페이스 레벨 암호화 방식을 사용할 수 없다.
- Expdp/Impdp 는 TDE 를 지원 (10g R2, 11g)
- Bitmap index, Index range scan 지원
- Lob 지원(11g), Logminer, logical standby db, streams, HSM(Hardware Security Module)
 
<TDE_Colnums Level Test> - 11g
 
1.Master Key 생성
 
[oracle@11g-:~]$pwd
/home/oracle
[oracle@11g-:~]$mkdir tde_wallet
 
[oracle@11g-:/u01/base/product/11g/network/admin]$vi sqlnet.ora
 
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/tde_wallet)))
 
SQL> alter system set encryption key identified by walltest;
 
System altered.
 
[oracle@11g-:~/tde_wallet]$ll
total 4
-rw-r--r--. 1 oracle dba 2845 Nov 16 00:14 ewallet.p12
 
SQL> select * from v$encryption_wallet
 
WRL_TYPE   WRL_PARAMETER                      STATUS
---------- --------------------------------- ---------------
file       /home/oracle/tde_wallet             OPEN
 
2.Create Encrypted Table & Manage Wallet
 
SQL> CREATE TABLESPACE tde DATAFILE '/oradata/tde.dbf' SIZE 100m autoextend on ;
Tablespace created.
 
SQL> create user tde_user identified by oracle default tablespace tde;
User created.
 
SQL> grant resource, connect to tde_user;
Grant succeeded.
 
SQL> conn tde_user
Enter password:
Connected.
 
SQL> show user
USER is "TDE_USER"
 
SQL> create table tde_table (cust_id number, cust_name varchar2(20), card_num varchar2(20) encrypt);
Table created.
 
=========(sys계정에서 table 생성 X)
ERROR at line 1:
ORA-28336: cannot encrypt SYS owned objects
 
SQL> insert into tde_table values (1, 'a', 'a');
 insert into tde_table values (2, 'a', 'a');
insert into tde_table values (3, 'a', 'a');
insert into tde_table values (4, 'a', 'a');
insert into tde_table values (5, 'a', 'a');
insert into tde_table values (6, 'a', 'a');
 
SQL> select * from user_encrypted_columns
 
TABLE_NAME         COLUMN_NAME      ENCRYPTION_ALG       SALT     INTEGRITY_
-------------------- -------------------- -------------------- --------- ----------
TDE_TABLE         CARD_NUM          AES 192 bits key     YES     SHA-1
 
SQL>select * from v$encryption_wallet
 
WRL_TYPE   WRL_PARAMETER          STATUS
---------- ------------------------------ ------------------------------------------------------
file       /home/oracle/tde_wallet      OPEN
 
SQL> alter system set encryption wallet close identified by walltest;
System altered.
 
SQL> select * from v$encryption_wallet;
 
WRL_TYPE   WRL_PARAMETER          STATUS
---------- ------------------------------ ------------------------------------------------------
file       /home/oracle/tde_wallet      CLOSED
 
SQL> conn tde_user
Enter password:
Connected.
 
SQL> select * from tde_table;
select * from tde_table
              *
ERROR at line 1:
ORA-28365: wallet is not open
 
SQL> select cust_id, cust_name from tde_table;
   CUST_ID CUST_NAME
---------- ------------------------------------------------------------
     1 a
     2 a
     3 a
     4 a
     5 a
     6 a
6 rows selected.
 
SQL> select cust_id, cust_name, cust_num from tde_table;
select cust_id, cust_name, cust_num from tde_table
                           *
ERROR at line 1:
ORA-00904: "CUST_NUM": invalid identifier
 
<TDE_Tablespace Level Test> - 11g
 
1.Master Key 생성
 
SQL> conn / as sysdba
Connected.
 
SQL> show user
USER is "SYS"
 
SQL> alter system set encryption key identified by walltest;
System altered.
 
SQL> CREATE TABLESPACE tde_table DATAFILE '/oradata/tde_table.dbf' SIZE 100m autoextend on encryption using 'AES256' default storage(encrypt);
Tablespace created.
 
SQL> select tablespace_name, encrypted from dba_tablespaces where tablespace_name='TDE_TABLE';                                                
TABLESPACE_NAME                                        ENCRYPTED
------------------------------------------------------------------------------------------ ---------
TDE_TABLE                                           YES
 
2.Create Encrypted Tablespace & Manage Wallet - 11g
 
SQL> conn tde_user/oracle
Connected.
 
SQL> create table tde_tablespace (cust_id number, cust_name varchar2(20), card_num varchar2(20)) tablespace tde_table;
Table created.
 
SQL>insert into tde_tablespace values (2, 'a', 'a');
insert into tde_tablespace values (3, 'a', 'a');
insert into tde_tablespace values (4, 'a', 'a');
insert into tde_tablespace values (5, 'a', 'a');
insert into tde_tablespace values (6, 'a', 'a');
 
SQL> commit;
Commit complete.
 
SQL> select * from tde_tablespace
   CUST_ID CUST_NAME  CARD_NUM
---------- ---------- ----------
     2 a          a
     3 a          a
     4 a          a
     5 a          a
     6 a          a
 
SQL> show user
USER is "TDE_USER"
 
SQL> conn / as sysdba
Connected.
 
SQL> show user
USER is "SYS"
 
SQL> alter system set encryption wallet close identified by walltest;
System altered.
 
SQL> select * from v$encryption_wallet;
WRL_TYPE   WRL_PARAMETER          STATUS
---------- ------------------------------ ------------------------------------------------------
file       /home/oracle/tde_wallet      CLOSED
 
SQL> select * from wallet.tde_tablespace;
select * from wallet.tde_tablespace
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
<TDE Test> - 12c
 
[oracle@12c-:~]$ll
total 0
drwxr-xr-x. 2 oracle dba 6 Nov 16 01:19 wallet
[oracle@12c-:~]$pwd
/home/oracle
 
[oracle@12c-:/u01/base/product/12c/network/admin]$vi sqlnet.ora -- key 생성
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/oracle/wallet)))
 
SQL> administer key management create keystore '/home/oracle/wallet/' identified by wallet; -- key open
keystore altered.
 
SQL> host ls /home/oracle/wallet/
ewallet.p12
 
SQL> administer key management set keystore open identified by wallet; -- key active
keystore altered.
 
SQL> select con_id, key_id from v$encryption_keys;
no rows selected
 
SQL> administer key management set key identified by wallet with backup;
keystore altered.
 
SQL> select con_id, key_id from v$encryption_keys;
    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
     0 AaOoOeolCU+7v+OcVlHybrMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER                    STATUS                   WALLET_TYPE        WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/                OPEN                   PASSWORD         SINGLE    NO         0
 
SQL> select * from v$encryption_wallet
WRL_TYPE         WRL_PARAMETER            STATUS               WALLET_TYPE        WALLET_OR FULLY_BAC    CON_ID
-------------------- ------------------------------ ------------------------------ -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/        OPEN               PASSWORD        SINGLE      NO             0
 
SQL> select * from v$encryption_wallet
WRL_TYPE         WRL_PARAMETER            STATUS     WALLET_TYPE        WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/        OPEN       PASSWORD         SINGLE    NO         0
 
SQL> create tablespace tde_table datafile '/oradata/tde_table.dbf' size 100m encryption using 'AES256' default storage(encrypt);
Tablespace created.
 
SQL> create user tde_user identified by oracle default tablespace tde_table;
User created.
 
SQL> grant resource, connect to tde_user;
Grant succeeded.
 
SQL>  create table tde_tablespace (name number, id varchar2(20), num varchar2(20)) tablespace tde_table;
Table created.
 
SQL> select tablespace_name, encrypted from dba_tablespaces where tablespace_name='TDE_TABLE';
TABLESPACE_NAME            ENC
------------------------------ ---
TDE_TABLE               YES
 
SQL> conn tde_user
Enter password:
Connected.
 
SQL> create table tde_table ( name number, id varchar2(20), num varchar2(20) encrypt) tablespace tde_table;
Table created.
 
SQL> create table tde ( name number, id varchar2(20), num varchar2(20) encrypt);
Table created.
 
SQL> conn / as sysdba     
Connected.
 
SQL> select owner, table_name, column_name, encryption_alg from dba_encrypted_columns where table_name='TDE_TABLE'
 
OWNER       TABLE_NAME COLUMN_NAM ENCRYPTION_ALG
---------- ---------- ---------- -----------------------------
TDE_USER   TDE_TABLE  NUM     AES 192 bits key
 
SQL> select owner, table_name, column_name, encryption_alg from dba_encrypted_columns where table_name='TDE';
 
OWNER       TABLE_NAME COLUMN_NAM ENCRYPTION_ALG
---------- ---------- ---------- -----------------------------
TDE_USER   TDE          NUM     AES 192 bits key
 
SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER            STATUS     WALLET_TYPE        WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/        OPEN       PASSWORD         SINGLE    NO         0
 
SQL> administer key management create auto_login keystore from keystore '/home/oracle/wallet/' identified by wallet;
keystore altered.
 
SQL> select * from v$encryption_wallet;
WRL_TYPE         WRL_PARAMETER            STATUS     WALLET_TYPE        WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/        OPEN       PASSWORD         SINGLE    NO         0
 
SQL> startup force
ORACLE instance started.
 
Total System Global Area 2399141888 bytes
Fixed Size            8623496 bytes
Variable Size          654314104 bytes
Database Buffers     1728053248 bytes
Redo Buffers            8151040 bytes
Database mounted.
Database opened.
 
SQL> select * from v$encryption_wallet;
 
WRL_TYPE         WRL_PARAMETER            STATUS     WALLET_TYPE        WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/        OPEN       AUTOLOGIN        SINGLE    NO         0
 
-- 모든 pdbs에서 keystore 열고 활성화
administer key management set keystore open identified by wallet container=all;
administer key management set key identified by wallet with backup container=all;
 
-- TDE 확인 명령어
 
SQL> select table_name, column_name from dba_encrypted_columns; 
 
TABLE_NAME COLUMN_NAM
---------- ----------
TDE_TABLE  NUM
TDE       NUM
 
SQL> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and  b.encrypted = 'YES'
 
TABLE_NAME         TABLESPACE_NAME
-------------------- ------------------------------
TDE_TABLESPACE         TDE_TABLE
TDE_TABLE         TDE_TABLE
TDE             TDE_TABLE
 
SQL> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%';
 
no rows selected
 
SQL> select * from v$encryption_wallet;
 
WRL_TYPE         WRL_PARAMETER            STATUS     WALLET_TYPE        WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE             /home/oracle/wallet/        OPEN       AUTOLOGIN        SINGLE    NO         0
 
SQL> SELECT con_id, key_id FROM v$encryption_keys;
 
    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
     0 AaOoOeolCU+7v+OcVlHybrMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
<TDE Test> - 19c
 
**혹시 안되면 
[oracle@19c-:~]$echo $ORACLE_UNQNAME
ver19c
 
--19c에서 wallet_root 경로를 설정하면 하위폴더로 tde가 설정됨
ex) 
SQL> alter system set wallet_root="/oradata" scope=spfile;
SQL> select * from v$encryption_wallet
 
WRL_T WRL_PARAMETER        STATUS                   WALLET_TYPE        WALLET_OR KEYSTORE FULLY_BAC     CON_ID
----- ------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE  /oradata/tde/        NOT_AVAILABLE               UNKNOWN            SINGLE    NONE     UNDEFINED      0
 
[oracle@19c-:/u01/base/admin/ver19c]$mkdir wallet
[oracle@19c-:/u01/base/admin/ver19c]$ll
total 4
drwxr-x--- 2 oracle dba 4096 Nov 17 01:47 adump
drwxr-x--- 2 oracle dba   20 Nov 17 01:18 dpdump
drwxr-x--- 2 oracle dba   35 Nov 17 01:34 pfile
drwxr-xr-x 2 oracle dba    6 Nov 17 01:48 wallet
drwxr-x--- 2 oracle dba   44 Nov 17 01:06 xdb_wallet
 
SQL> select * from v$encryption_wallet
 
WRL_T WRL_PARAMETER             STATUS                WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC      CON_ID
----- ------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE  /u01/base/admin/ver19c/wallet  NOT_AVAILABLE            UNKNOWN         SINGLE    NONE     UNDEFINED           0
 
[oracle@19c-:/u01/base/product/19c/network/admin]$vi sqlnet.ora
 
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/base/admin/ver19c/wallet/)))
 
SQL> create pfile from spfile;
File created.
 
[oracle@19c-:/u01/base/product/19c/dbs]$vi initver19c.ora
WALLET_ROOT=/u01/base/admin/ver19c/wallet
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup pfile='/u01/base/product/19c/dbs/initver19c.ora'
ORACLE instance started.
Total System Global Area 2399140616 bytes
Fixed Size            8899336 bytes
Variable Size          520093696 bytes
Database Buffers     1862270976 bytes
Redo Buffers            7876608 bytes
Database mounted.
Database opened.
 
SQL> show parameter spfile
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string
 
SQL> select * from v$encryption_wallet;
WRL_T WRL_PARAMETER             STATUS                WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC      CON_ID
----- ------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE                     NOT_AVAILABLE            UNKNOWN         SINGLE    NONE     UNDEFINED           0
 
SQL> create spfile from pfile;
File created.
 
SQL> alter system set WALLET_ROOT="/u01/base/admin/ver19c/wallet" scope=spfile;
System altered.
 
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
System altered.
 
SQL> select * from v$encryption_wallet;
WRL_T WRL_PARAMETER             STATUS                WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC      CON_ID
----- ------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE                     NOT_AVAILABLE            UNKNOWN         SINGLE    NONE     UNDEFINED           0
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.
Total System Global Area 2399140616 bytes
Fixed Size            8899336 bytes
Variable Size          520093696 bytes
Database Buffers     1862270976 bytes
Redo Buffers            7876608 bytes
Database mounted.
Database opened.
 
SQL> select * from v$encryption_wallet;
WRL_T WRL_PARAMETER             STATUS                WALLET_TYPE      WALLET_OR KEYSTORE FULLY_BAC      CON_ID
----- ------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE  /u01/base/admin/ver19c/wallet/ NOT_AVAILABLE            UNKNOWN         SINGLE    NONE     UNDEFINED           0
      tde/
 
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/base/admin/ver19c/wallet/tde' IDENTIFIED BY wallet;
keystore altered.
 
SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
CLOSED
 
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                   CON_ID
------------------------------ ----------
FILE
/u01/base/admin/ver19c/wallet/tde/
CLOSED                    0
 
SQL> !ls -lrt /u01/base/admin/ver19c/wallet/tde
total 4
-rw------- 1 oracle dba 2555 Nov 17 02:25 ewallet.p12
 
SQL> administer key management create auto_login keystore from keystore '/u01/base/admin/ver19c/wallet/tde' identified by wallet;
keystore altered.
 
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
 
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                   CON_ID
------------------------------ ----------
FILE
/u01/base/admin/ver19c/wallet/tde/
OPEN_NO_MASTER_KEY            0
 
SQL> select * from v$encryption_wallet;
 
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                   WALLET_TYPE        WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
    CON_ID
----------
FILE
/u01/base/admin/ver19c/wallet/tde/
OPEN_NO_MASTER_KEY           AUTOLOGIN        SINGLE    NONE     UNDEFINED
     0
 
SQL> administer key management set keystore open force keystore identified by wallet;
keystore altered.
 
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                   WALLET_TYPE        WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
    CON_ID
----------
FILE
/u01/base/admin/ver19c/wallet/tde/
OPEN_NO_MASTER_KEY           AUTOLOGIN        SINGLE    NONE     UNDEFINED
     0
 
SQL> administer key management set key FORCE KEYSTORE identified by wallet with backup;
keystore altered.
                                                                                     
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                   WALLET_TYPE        WALLET_OR KEYSTORE FULLY_BAC
------------------------------ -------------------- --------- -------- ---------
    CON_ID
----------
FILE
/u01/base/admin/ver19c/wallet/tde/
OPEN                   AUTOLOGIN        SINGLE    NONE     NO
     0
 
SQL> create tablespace TBS_NAME datafile '/oradata/tbs_name.dbf' size 100m autoextend on maxsize 100m EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION DEFAULT STORAGE (ENCRYPT);
Tablespace created.
 
SQL> CREATE TABLESPACE TBS_NAME2 DATAFILE '/oradata/tbs_name2.dbf' SIZE 100m AUTOEXTEND ON  maxsize 200m ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
 
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME            ENC
------------------------------ ---
SYSTEM                   NO
SYSAUX                   NO
UNDOTBS1               NO
TEMP                   NO
USERS                   NO
TBS_NAME               YES
TBS_NAME2               YES
 
 
 
 
 
 
 
 
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] DB LINK  (1) 2022.06.28
[ORACLE] ASMCMD 명령어  (0) 2022.06.28
[ORACLE] Partitioning  (0) 2022.06.27
[ORACLE] Archive mode 변경  (0) 2022.06.27
[ORACLE] template_dbca_silent 설치 (한 엔진에 instance 추가)  (0) 2022.06.27
728x90
- Partitioning
 
테이블, 인덱스 및 인덱스 구성 테이블을 더 작은 조각으로 세분화하여 이러한 데이터베이스 개체를 보다 세밀한 수준에서 관리하고 액세스 할 수 있도록 하는 강력한 기능
 
- Partitioning 종류
 
Range Partitioning : 데이터가 값 범위를 기준으로 분산
List Partitioning : 개별 값 목록을 기준으로 분산, 하나 또는 여러 열을 파티션 키로 사용
Auto-List Partitioning : 새 파티션을 키 값에 자동으로 정의하여 목록 방법의 기능을 확장
Hash Partitioning : 내부 해시 알고리즘이 파티셔닝 키에 적용되어 파티션을 결정
Composite Partitioning : 두 가지 데이터 배포 방법의 조합, 첫 번째 방법으로 데이터를 분할한 다음 두 번째 분배 방법을 사용하여 하위 분할 영역으로 더 세분화
Multi-Column Range Partitioning : 분할 키가 여러 열로 구성되고 후속 열이 이전 열보다 더 높은 수준으로 세분화 
Interval Partitioning : 테이블 데이터에 일부로 간격 정의하여 파티션에 대해 동일하게 파티셔닝 된 범위를 자동으로 정의
Reference Partitioning Partitions : 기존 부모-자식 관계를 활용하는 테이블. 기본 키 관계는 상위 테이블의 파티셔닝을 하위 테이블로 상속하는 데 사용
 
*Virtual Column Based Partitioning : 파티셔닝 키가 테이블의 하나 이상의 기존 컬럼을 사용하고 표현식을 메타 데이터로만 저장하는 표현식이 될 수 있습니다.
*Interval Reference Partitioning : 참조 파티셔닝을 위한 상위 테이블로 간격 파티셔닝 테이블을 사용할 수있는 참조 파티셔닝에 대한 확장입니다.
 
- Partition Table
내부적으로 파티션으로 분할되어 저장되는 것일뿐, 논리적으로 테이블과 동일
 
- Range Partitioning
범위(숫자, 날짜, 문자)를 기준으로 행을 분할하는 파티션 테이블
 
- Range Partitioning 생성
CREATE TABLE <TABLE_NAME>
( <COLUMN_NAME1> <DATA_TYPE1>,
<COLUMN_NAME2> <DATA_TYPE2>,
<COLUMN_NAME3> <DATA_TYPE3>)
PARTITION BY RANGE (<PARTITION_COLUMN_NAME1>, <PARTITION_COLUMN_NAME2>)
(PARTITION <PARTITION_NAME> VALUES LESS THAN (<PARTITION_COLUMN_NAME1_VALUE>, <PARTITION_COLUMN_NAME2_VALUE>) TABLESPACE <TABLESPACE_NAME>);
 
- List Partitioning
범위가 아닌 특정한 값으로 구분되는 파티션 테이블
 
- List Partitioning 생성
CREATE TABLE <TABLE_NAME>
( <COLUMN_NAME1> <DATA_TYPE1>,
<COLUMN_NAME2> <DATA_TYPE2>,
<COLUMN_NAME3> <DATA_TYPE3>)
PARTITION BY LIST (<COLUMN_NAME>)
(PARTITION <PARTITION_NAME1> VALUES (<VALUE1>, <VALUE2>, <VALUE3>), 
 PARTITION <PARTITION_NAME2> VALUES (<VALUE1>, <VALUE2>, <VALUE3>)
);
 
- Partitioning 추가
ALTER TABLE <TABLE_NAME> ADD PARTITION <PARTITION_NAME> VALUES LESS THAN (<PARTITION_COLUMN_NAME1_VALUE>, <PARTITION_COLUMN_NAME2_VALUE>) TABLESPACE <TABLESPACE_NAME>);
 
- Partitioning 조회
SELECT * FROM <TABLE_NAME> PARTITION <PARTITION_NAME>;
 
- Partitioning 삭제
ALTER TABLE <TABLE_NAME> DROP PARTITION <PARTITION_NAME>;
 
- Partitioning 이름변경 
ALTER TABLE <TABLE_NAME> RENAME PARTITION <PARTITION_NAME> TO <NEW_PARTITION_NAME>;
 
- Partitioning 병합 (1+2->2) (Hash Partition, Subpartition 해당 작업 불가)
ALTER TABLE <TABLE_NAME> MERGE PARTITION <PARTITION_NAME1>, <PARTITION_NAME2> INTO PARTITION <PARTITION_NAME2> UPDATE INDEXES;
 
- Partitioning 분할 (2-> 1,2) (Hash Partition, Subpartition 해당 작업 불가)
ALTER TABLE <TABLE_NAME> SPLIT PARTITION <PARTITION_NAME> AT (<VALUE>) INTO (PARTITION <PARTITION_NAME1> TABLESPACE <TABLESPACE_NAME1>, 
PARTITION <PARTITION_NAME2> TABLESPACE <TABLESPACE_NAME2>) 
 
- Partitioning 파티션 테이블스페이스 변경
ALTER TABLE <TABLE_NAME> MOVE PARTITION <PARTITION_NAME> TABLESPACE <TABLESPACE_NAME>;
 
- Hash Partitioning
해시함수에 의해 자동으로 파티션 갯수만큼 데이터가 분할되는 파티션 테이블, 아무 타입이나 해시 파티션키로 사용 가능
데이터가 어느 파티션으로 들어갈 지 알 수 없음, 데이터를 여러 위치에 분산배치하여 Disk I/O 성능 개선 (Hot Block 현상 : 스토리지의 특정 위치에 I/O가 몰리는 현상)
 
- Hash Partitioning 생성
CREATE TABLE <TABLE_NAME>
( <COLUMN_NAME1> <DATA_TYPE1>,
<COLUMN_NAME2> <DATA_TYPE2>,
<COLUMN_NAME3> <DATA_TYPE3>)
PARTITION BY HASH (<COLUMN_NAME>)
PARTITIONS <PARTITION_NUMBER>;
 
- Hash Partitioning 추가
ALTER TABLE <TABLE_NAME> ADD PARTITION <PARTITION_NAME>;
 
- Hash Partitioning 삭제
ALTER TABLE <TABLE_NAME> COALESCE PARTITION;
 
728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] ASMCMD 명령어  (0) 2022.06.28
[ORACLE] TDE  (0) 2022.06.27
[ORACLE] Archive mode 변경  (0) 2022.06.27
[ORACLE] template_dbca_silent 설치 (한 엔진에 instance 추가)  (0) 2022.06.27
[ORACLE] Window HA install  (0) 2022.06.27
728x90
1. Parameter 확인
2. Archive Log Mode 확인
3. Archive Log File이 쌓일 Directory 확인
4. Parameter 변경
5. DB Shutdown
6. DB Mount 후, Archive Log Mode 변경
7. DB Open
8. Archive Log Mode 확인
 
 
1. 사용 중인 Parameter File을 확인 합니다. (SPFILE 사용 중)
 
SQL> show parameter pfile;
 
NAME       TYPE           VALUE
---------- -------------- ---------------------------------------------
spfile     string         /home/oracle/product/10g/dbs/spfileORA11.ora
 
2. Archive Log Mode를 확인 합니다. (현재는 No Archive Log Mode)
 
SQL> archive log list;
 
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           1
 
3. Archive File이 쌓일 Directory를 확인 합니다. (/arch 로 권한은 oracle:dba 권한을 부여)
[root@oracle ~]# chown -R oracle:dba /arch
 
4. Parameter를 변경 합니다. (Archive Log Dest 및 Archive Log File의 Format 설정)
[ORA11]oracle:/home/oracle>sqlplus "/as sysdba"
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 2 13:56:46 2015
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> alter system set log_archive_dest_1='location=/arch' scope=spfile;
 
System altered.
 
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
 
System altered.
5. DB를 Shutdown 합니다. (Parameter 변경 적용)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
6. Archive Log Mode로 변경 합니다. (DB Mount 상태에서 변경 후, DB Open)
 
SQL> startup mount;
ORACLE instance started.
 
Total System Global Area  313159680 bytes
Fixed Size                  2252824 bytes
Variable Size             222302184 bytes
Database Buffers           83886080 bytes
Redo Buffers                4718592 bytes
Database mounted.
 
SQL> alter database archivelog;
SQL> alter database noarchivelog;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
8. Archive Log Mode 확인 (정상적으로 변경)
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /arch
Oldest online log sequence     63
Next log sequence to archive   65
Current log sequence           65
 
9. Archive Log Mode 변동 확인 작업
SQL> alter system switch logfile;
 
SQL> /
SQL> /
 
 
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] TDE  (0) 2022.06.27
[ORACLE] Partitioning  (0) 2022.06.27
[ORACLE] template_dbca_silent 설치 (한 엔진에 instance 추가)  (0) 2022.06.27
[ORACLE] Window HA install  (0) 2022.06.27
[ORACLE] Window RAC install  (0) 2022.06.27
728x90
-instance 생성
  1. template 백업
            $ORACLE_HOME/assistants/dbca/templates/
            cp  New_Database.dbt New_Database._bak.dbt
      2. New_Database.dbt 수정
      3 . dbca 실행
            dbca -silent -createDatabase -templateName /u01/base/product/19c/assistants/dbca/templates/APM.dbt -gdbName APM -sid APM -sysPassword oracle -systemPassword oracle -createAsContainerDatabase false -characterSet AL32UTF8 -nationalCharacterSet UTF8 -totalMemory 2048
      4. tns 추가
      5. local_listener 설정
           
-instance 삭제 
  1. Data파일 삭제
  2. dbca -silent -deleteDatabase -sourceDB <instance_name>

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Partitioning  (0) 2022.06.27
[ORACLE] Archive mode 변경  (0) 2022.06.27
[ORACLE] Window HA install  (0) 2022.06.27
[ORACLE] Window RAC install  (0) 2022.06.27
[ORACLE] Log miner  (0) 2022.06.27
728x90
  • 설치 전 사전 조치 사항
windows 업데이트 확인 후 최신 업데이트 요청 (미완료 시 설치 후 요청)
  • 설치 과정
  1. Active (1번 서버)에 스토리지 구성 후 Windows 싱글 설치 (사전설치요청사항 문서 참고하여) -> DB 접속 확인
  2. Standby (2번 서버)에 스토리지 없이 엔진과 리스너 생성
  3. Active (1번 서버)에 ORACLE_HOME\database 안의 PWD<instance_name>.ora, SPFILE<instance_name>.ora 복사 후
Standby (2번 서버)에 ORACLE_HOME\database에 복사
  1. Standby (2번 서버)에 ORACLE_HOME\database에 INIT<instance_name>.ora 생성 후
해당 파일 안에 spfile=’ORACLE_HOME\database\SPFILE<instance_name>.ora’ 저장
  1. Standby (2번 서버)에 Cmd -> oradim -NEW -SID <SID_NAME> -INTPWD <PASSWORD> -STARTMODE auto -PFILE ‘<ORACLE_HOME>\init<instance_name>.ora'
  2. Standby (2번 서버)에 서비스 -> OracleService<instance_name>의 생성 유무와 status 확인
  3. Standby (2번 서버)에 레지스트리 편집기(Registry Editor) -> Computer -> HKEY_LOCAL_MACHINE -> SOFTWARE -> Oracle -> KEY_OraDB12Home1 -> 생성, 타입 : 문자열 -> ORACLE_SID : instance_name
  4. Standby (2번 서버)에 스토리지 구성 후 DB 접속 확인
  5. Active (1번 서버)와 Standby (2번 서버)의 리스너 ip를 local_ip -> vip (=서비스 ip) 로 변경 (vip는 클러스터가 올라온 상태에 사용이 가능하므로 클러스터 설치 전일 경우 local_ip로 구성 후 접속 확인)
 
  • 설치 과정 (Dummy DB)
**** 해당 설치과정이 어려울 경우 1,2번 수행 후 Standby (2번 서버)에 임의의 폴더 생성 후 해당 폴더에 dbca를 설치한 후 설치한 폴더 삭제하고 3번을 수행하면 이하 내용을 수행하지 않아도 된다고 합니다.
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Archive mode 변경  (0) 2022.06.27
[ORACLE] template_dbca_silent 설치 (한 엔진에 instance 추가)  (0) 2022.06.27
[ORACLE] Window RAC install  (0) 2022.06.27
[ORACLE] Log miner  (0) 2022.06.27
[ORACLE] local_listener  (0) 2022.06.27
728x90
 
 
-- hosts파일(C:\Windows\System32\drivers\etc\hosts)
105.20.1.154       pjsvr1
105.20.1.155       pjsvr2
105.20.1.156       pjsvr1-vip
105.20.1.157       pjsvr2-vip
105.20.1.158       pjsvr-scan
10.10.10.1         pjsvr1-priv
10.10.10.2         pjsvr2-priv
 
-- hostname 수정
-- IP설정
 
-- 방화벽 해제
netsh firewall set opmode DISABLE
netsh advfirewall firewall delete
netsh advfirewall firewall set opmode DISABLE
 
--- 디스크
cmd -> diskpart
list disk
 
select disk 0
online disk
attributes disk clear readonly
create partition extended
create partition logical
 
-- 설치파일에서 asmtool 수행
엔진설치파일\asmtool\asmtoolg 수행
disk별 label 설정해야 asm생성할때 이름이 보임
 
-- registry 수정
- NTP관련
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service\W32Time\Config
MaxPosPhaseCorrection : 600
MaxNegPhaseCorrection : 600
MaxAllowedPhaseOffset : 600
C:\>W32tm /config /update
 
-------------------------------------------------------
CMD> secpol
로컬정책 -> 보안옵션 -> 사용자 계정컨트롤 : 관리자 승인 -> 권한 상승 전에 확인 안함
-------------------------------------------------------
 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\system
새로만들기 -> DWORD(32비트) 값 -> LocalAccountTokenFilterPolicy
값 데이터 : 1
 
-- 방화벽해제
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile
EnableFirewall => 0
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\PublicProfile
EnableFirewall => 0
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\StandardProfile
EnableFirewall => 0
 
-------------------------------------------------------
-DHCP감지해제
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service\Tcpip\Parameters
 
Name : DisableDHCPMediaSense
Data type : REG_DWORD(Boolean)
Value : 1
 
reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters /v DisableDHCPMediaSense
reg add "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters" /v DisableDHCPMediaSense /t REG_DWORD /d 1 /f
 
- IPv6 비활성화
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service\Tcpip6\Parameters
새로만들기 -> DWORD(32비트) 값 -> DisableComponent => 값데이터 : 0xffffffff
-- 기본적으로 비활성화 되어 있음
reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters /v DisabledComponents
 
--  비활성화 방법
reg add "HKLM\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters" /v DisabledComponents /t REG_DWORD /d 0xFFFFFFFF /f
reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters /v DisabledComponents
 
ipconfig | findstr IPv6
 
-- 기능 추가
서버관리자 -> 역할 및 기능추가 -> 역할 기반 또는 기능 기반 설치 다음 -> 다음 -> DNS서버 기능추가 -> 다음 다음 다음 설치
 
서버관리자 -> 도구 -> DNS -> 정방향 조회 영역 -> 새영역 -> 주 영역 -> 영역이름 : oracle50.com -> 다음 다음 마침
  oracle50.com 찍고 우축에서 '새 호스트' -> 이름 : scan   / IP주소 : 192.168.56.105 -> 호스트 추가
                                             이름 :
-------------------------------------------------------
-- 설치중 경고
 
-- 윈도우 RAC설치 환경
- windows 2012
- oracle 11.2 SE
- ASM
 
검사: 사용자 "Administrator"이(가) 도메인 사용자인지 여부 - 검사: 사용자 "Administrator"이(가) 도메인 사용자인지 여부
노드에 확인 경고 결과 발생: gtdb1
 
세부 정보:
PRVF-9998 : 사용자 "Administrator"을(를) 도메인 사용자로 확인할 수 없습니다. "GTDB1" 도메인이 부적합한 도메인이거나 이 도메인에 연결할 수 없습니다.  - Cause:  현재 사용자를 도메인 사용자로 확인할 수 없습니다. 지정된 도메인 이름이 부적합한 도메인 이름이거나 이 도메인에 연결할 수 없습니다.  - Action:  Windows 도메인 서버에 연결할 수 있는 확인하고 OS에 도메인 사용자로 로그인하십시오.
 
1. Bug:17927204 is first fixed in Windows DB Bundle Patch 11.2.0.4.11 or higher. At the time of writting the solution, the latest version is 11.2.0.4.14, which is available as Patch 20502905 WINDOWS DB BUNDLE PATCH 11.2.0.4.14.
2. Complete a software only install of the 11204 Grid software on each node.
3. Apply Patch 20502905 WINDOWS DB BUNDLE PATCH 11.2.0.4.14 to the Grid HOME.
   Run config.bat from GI_HOME\crs\config to configure Grid Infrastructure in a clustered environment (OR 'Grid_home\perl\bin\perl -IGrid_home\perl\lib -IGrid_home\crs\install Grid_home\crs\install\roothas.pl' for a Grid Infrastructure standalone / Oracle Restart environment)
4. The following error may be seen after running the config.bat : "ACFS drivers installation failed" to resolve the error run ASMCA to configure ACFS.  To resolve the error run ASMCA to configure ACFS on cluster env.  Run following in on standalone (Restart) env.
 
   1) cd Grid_home\oui\bin
   2) setup.exe -updateNodeList ORACLE_HOME=Grid_home CLUSTER_NODES= CRS=TRUE
   3) run ASMCA to configure ACFS
   
set ORACLE_HOME=E:\oragrid\product\11.2.0.4
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\crs\install %ORACLE_HOME%\crs\install\rootcrs.pl -unlock
crsctl status resource -t
 
net start | findstr /i ora
 
net stop OracleRemExecService
net stop OracleServiceD112041
net stop OraFenceService
net stop "Oracle Object Service"
net stop "OracleOHService"
 
sc config Winmgmt start= disabled
net stop winmgmt
 
cd 28265827
E:\oragrid\product\11.2.0.4\opatch\opatch apply -local
    
sc config Winmgmt start= auto
%ORACLE_HOME%\perl\bin\perl -I%ORACLE_HOME%\perl\lib -I%ORACLE_HOME%\crs\install %ORACLE_HOME%\crs\install\rootcrs.pl -patch    
 
net start msdtc
net start winmgmt    
 
E:\oragrid\product\11.2.0.4
------------------------------------------------------
## Windows 2012에서 Oracle 11g RAC 설치 시 오류 발생
** 원인은 ASM 드라이버 미지원으로 기동 실패 (Doc ID 1987371.1)
- 패치 후 설치진행이 필요.
-- 윈도우에서  deconfig 수행 (각노드에서 수행)
set GRID_HOME=E:\oragrid\product\11.2.0.4
E:\oragrid\product\11.2.0.4\perl\bin\perl -I E:\oragrid\product\11.2.0.4\perl\lib -I E:\oragrid\product\11.2.0.4\crs\install E:\oragrid\product\11.2.0.4\crs\install\rootcrs.pl -deconfig -force
 
-- 패치적용 : 최신패치도 가능 (각노드에서 수행)
set ORACLE_HOME=E:\oragrid\product\11.2.0.4
cd E:\Oracle_install\28265827
E:\oragrid\product\11.2.0.4\OPatch\opatch apply -local
 
-- RAC config 수행 (1번에서만 수행하면 2번까지 수행됨)
E:\oragrid\product\11.2.0.4\perl\bin\perl -I E:\oragrid\product\11.2.0.4\perl\lib -I E:\oragrid\product\11.2.0.4\crs\install E:\oragrid\product\11.2.0.4\crs\install\rootcrs.pl
 
-- 인벤토리 업데이트 및 ASM구성 : 안하면 DB설치할때 노드리스트가 안나옴
1) cd E:\oragrid\product\11.2.0.4\oui\bin
2) setup.exe -updateNodeList ORACLE_HOME=E:\oragrid\product\11.2.0.4 "CLUSTER_NODES={pjsvr1,pjsvr2}" CRS=TRUE
3) run ASMCA to configure ACFS : CMD> asmca : Data,RECO diskgroup 생성
 
-- 노드 재기동 : DB엔진 설치 시 기동상태체크가 안되서...(각 노드별 수행)
crsctl stop crs
crsctl start crs
 
[INS-35423] Oracle Clusterware가 로컬 노드에서 실행 중이 아닌 것으로 감지되었습니다.
 
  --> 그래도 안되면 (문서 ID 2164220.1)
cd E:\Oracle_install\database
setup.exe ORACLE_HOSTNAME=pjsvr1
 
-- Default Listener 생성
- scan에서 1521포트를 사용해서 추가할때 사용할 수 없다고 하면서 생성안됨.
-> 포트중복시 scan_listener 중지 후 생성하면됨
srvctl stop scan_listener
srvctl add listener -l listener -p TCP:1521
srvctl start listener -l listener
srvctl start scan_listener
 
-------------------------------------------------------
## 패치
-- CRS패치는 진행한 경우 DB만 내리고 진행해도 문제없이 진행
-- 앞에서 최신Patch르 안한경우 아래대로 모두 진행하면됨
 
-- 서비스 중지
set ORACLE_HOME=E:\oragrid\product\11.2.0.4
%ORACLE_HOME%\perl\bin\perl -I %ORACLE_HOME%\perl\lib -I %ORACLE_HOME%\crs\install %ORACLE_HOME%\crs\install\rootcrs.pl -unlock
 
-- 내려갔는지 확인
crsctl status resource -t
 
-- 오라클 서비스 다운
net start | findstr /i ora
 
net stop OracleRemExecService
net stop OracleServiceD112041
net stop OraFenceService
 
sc config Winmgmt start= disabled
net stop winmgmt          
 
-- 패치
unzip p28265827_112040_MSWIN-x86-64.zip
cd E:\Oracle_install\28265827
 
set ORACLE_HOOME=E:\oragrid\product\11.2.0.4
E:\oragrid\product\11.2.0.4\OPatch\opatch apply -local
 
set ORACLE_HOOME=C:\oracle\product\11.2.0\dbhome_1
C:\oracle\product\11.2.0\dbhome_1\OPatch\opatch apply -local
 
E:\oragrid\product\11.2.0.4\OPatch\opatch lsinv -oh E:\oragrid\product\11.2.0.4
E:\oragrid\product\11.2.0.4\OPatch\opatch lsinv -oh C:\oracle\product\11.2.0\dbhome_1
 
-- 기동
set ORACLE_HOME=E:\oragrid\product\11.2.0.4
%ORACLE_HOME%\perl\bin\perl -I %ORACLE_HOME%\perl\lib -I %ORACLE_HOME%\crs\install %ORACLE_HOME%\crs\install\rootcrs.pl -patch      
 
-------------------------------------------------------------------------
## DB패치만 하는경우
-- 서비스 중지
OracleMTSRecoveryService 중지
 
-- 패치
e:
cd E:\Oracle_install\28265827
set ORACLE_HOOME=E:\oracle\product\11.2.0.4
E:\oracle\product\11.2.0.4\OPatch\opatch apply -local
 
-- 서비스 기동
OracleMTSRecoveryService 기동
=============================================================================================================================
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] template_dbca_silent 설치 (한 엔진에 instance 추가)  (0) 2022.06.27
[ORACLE] Window HA install  (0) 2022.06.27
[ORACLE] Log miner  (0) 2022.06.27
[ORACLE] local_listener  (0) 2022.06.27
[ORACLE] Windows Client install  (0) 2022.06.27
728x90
<Log Miner> - 11g
[oracle@19c-:/u01/base/product/19c/rdbms/admin]$sqlplus / as sysdba
 
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 03:01:43 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
 
SQL> @dbmslm.sql
Session altered.
Package created.
Grant succeeded.
Synonym created.
Session altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
 
SQL> alter database add supplemental log data;
Database altered.
 
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
SQL> select * from test;
     A        B
---------- ----------
     1        1
     1        2
     1        3
     1        4
     1        5
     1        6
     2        1
     2        2
     2        3
     2        4
     2        5
     2        6
 
SQL> update test set A=100;
12 rows updated.
 
SQL> commit;
Commit complete.
 
SQL> select * from test;
     A        B
---------- ----------
       100        1
       100        2
       100        3
       100        4
       100        5
       100        6
       100        1
       100        2
       100        3
       100        4
       100        5
       100        6
12 rows selected.
[root@11g ~]# mkdir /logmnr
[root@11g ~]# chown -R oracle:dba /logmnr/
 
SQL> conn / as sysdba
Connected.
 
SQL> alter system set utl_file_dir='/logmnr' scope=spfile;
System altered.
 
SQL> show parameter utl_file_dir
NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir                 string
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.
Total System Global Area 2388422656 bytes
Fixed Size            2255552 bytes
Variable Size          603981120 bytes
Database Buffers     1761607680 bytes
Redo Buffers           20578304 bytes
Database mounted.
Database opened.
 
SQL> show parameter utl_file_dir
NAME                     TYPE                   VALUE
------------------------------------ --------------------------------- ------------------------------
utl_file_dir                 string                   /logmnr
 
 
 
 
SQL> select a.sequence#, a.group#, a.status, a.members, b.status, b.type, b.member from v$log a , v$logfile b where a.group#=b.group# order by a.group# asc, a.sequence# asc
SEQUENCE#     GROUP# STATUS         MEMBERS STATUS      TYPE               MEMBER
---------- ---------- --------------- ---------- --------------- --------------------- ------------------------------
    85        1 INACTIVE               1         ONLINE            /oradata/ver11g/redo01.log
    86        2 INACTIVE               1         ONLINE            /oradata/ver11g/redo02.log
    87        3 CURRENT               1         ONLINE            /oradata/ver11g/redo03.log
 
SQL> exec dbms_logmnr_d.build ('dict','/logmnr');
PL/SQL procedure successfully completed.
 
SQL> exec dbms_logmnr.add_logfile('/oradata/ver11g/redo01.log',1);
PL/SQL procedure successfully completed.
 
SQL> exec dbms_logmnr.add_logfile('/oradata/ver11g/redo02.log',3);
PL/SQL procedure successfully completed.
 
SQL> exec dbms_logmnr.add_logfile('/oradata/ver11g/redo03.log',3)
PL/SQL procedure successfully completed.
 
SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'/logmnr/dict');
PL/SQL procedure successfully completed.
 
SQL> select to_char(timestamp,'YYYY-MM-DD:HH24:MI:SS'), seg_owner, username, sql_redo, sql_undo from v$logmnr_contents where sql_redo like '%update%' and seg_owner='TEST'
TO_CHAR(TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS SEG_O USERN SQL_REDO                                                  SQL_UNDO
---------------------------------------- ----- ----- ---------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAA';          update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAA';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAB';          update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAB';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAC';          update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAC';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAD';          update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAD';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAE';          update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAE';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '1' and ROWID = 'AAAUUPAAHAAAACHAAF';          update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAF';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAG';          update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAG';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAH';          update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAH';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAI';          update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAI';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAJ';          update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAJ';
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAK';          update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAK';
 
TO_CHAR(TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS SEG_O USERN SQL_REDO                                                  SQL_UNDO
---------------------------------------- ----- ----- ---------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------------------------
2020-11-18:21:55:49             TEST  TEST  update "TEST"."TEST" set "A" = '100' where "A" = '2' and ROWID = 'AAAUUPAAHAAAACHAAL';          update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAL';
12 rows selected.
 
SQL> update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAA';
     update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAB';
     update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAC';
     update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAD';
     update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAE';
     update "TEST"."TEST" set "A" = '1' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAF';
     update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAG';
     update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAH';
     update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAI';
     update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAJ';
     update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAK';
     update "TEST"."TEST" set "A" = '2' where "A" = '100' and ROWID = 'AAAUUPAAHAAAACHAAL';
 
SQL> conn test/test
Connected.
 
SQL> select * from test;
     A        B
---------- ----------
     1        1
     1        2
     1        3
     1        4
     1        5
     1        6
     2        1
     2        2
     2        3
     2        4
     2        5
     A        B
---------- ----------
     2        6
12 rows selected.
 
SQL> commit;
Commit complete.
 
SQL> exec dbms_logmnr.end_logmnr();
 

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Window HA install  (0) 2022.06.27
[ORACLE] Window RAC install  (0) 2022.06.27
[ORACLE] local_listener  (0) 2022.06.27
[ORACLE] Windows Client install  (0) 2022.06.27
[ORACLE] Listener.ora Tnsnames.ora Sqlnet.ora  (0) 2022.06.27
728x90

-- DB 접속이 안될 경우 local_listener parameter 확인 

SQL> select name, value from v$spparameter where name='local_listener';
NAME                VALUE
------------------------------- ----------
local_listener
SQL> alter system set local_listener = "(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.79.4)(PORT=1521))";
System altered.
 
SQL> alter system register;
System altered.
 
sqlplus system@orcl -> tns타고 접속!!
 
-- client 접속 안 될 시
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME, EZCONNECT) -> sqlnet.ora

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Window RAC install  (0) 2022.06.27
[ORACLE] Log miner  (0) 2022.06.27
[ORACLE] Windows Client install  (0) 2022.06.27
[ORACLE] Listener.ora Tnsnames.ora Sqlnet.ora  (0) 2022.06.27
[ORACLE] Oradim - Windows 서비스 등록 방법  (0) 2022.06.27
728x90
1. 방화벽 제거 
 
2.  ip, hostname 등록
C:\Windows\System32\drivers\etc\hosts
    ip    hostname
3. ORACLE CLIENT 환경변수 설정 
제어판 -> 시스템 및 보안 -> 시스템 -> 고급 시스템 설정 -> 환경변수
시스템 변수(S) -> PATH 편집
  1. ORACLE CLIENT 설치 경로 입력
  2. 추가 - ORACLE_HOME : ORACLE_CLIENT 설치 경로
  3. 추가 - NLS_LANG :  KOREAN_KOREA.KO16MSWIN949
  4. 추가 - TNS_ADMIN : ORACLE_CLIENT 설치 경로
 
레지스터리 편집기 ( 윈도우 + R -> regedit or cmd -> regedit ) 
  1. COMPUTER\HKEY_LOCAL_MACHINE\SOFTWARE\Oracle
  2. 추가 - NLS_LANG :  KOREAN_KOREA.KO16MSWIN949
  3. 추가 - ORACLE_HOME : ORACLE_CLIENT 설치 경로
4. client 설치
 
5. service 확인
Service_name : OracleRemExecService
6. Listener, Tnsnames 파일 -> DB 서버에서 복사
7. 접속 테스트
sqlplus system/oracle@TNS명

 

728x90
저작자표시 비영리 동일조건 (새창열림)

'ORACLE' 카테고리의 다른 글

[ORACLE] Log miner  (0) 2022.06.27
[ORACLE] local_listener  (0) 2022.06.27
[ORACLE] Listener.ora Tnsnames.ora Sqlnet.ora  (0) 2022.06.27
[ORACLE] Oradim - Windows 서비스 등록 방법  (0) 2022.06.27
[ORACLE] CLSRSC-214 CRS-2101  (0) 2022.05.31

+ Recent posts

티스토리툴바