1. 기존 스키마와 테이블스페이스 삭제
DROP USER TEST CASCADE;
DROP TABLESPACE TS_TEST INCLUDING CONTENTS AND DATAFILES;
DROP USER INGRIAN CASCADE;
DROP TABLESPACE TS_INGRIAN INCLUDING CONTENTS AND DATAFILES;
2. 시험용 계정 및 테이블스페이스 생성 스크립트 (만들어서 oracle 계정으로 실행)
#!/bin/bash
export ORACLE_SID=SIDTEST
echo " Create Tablespace SID:${ORACLE_SID}..."
sqlplus / as sysdba << EOF
CREATE TABLESPACE TS_TEST
DATAFILE '/oradata/SIDTEST/ts_test.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M
DEFAULT STORAGE(
INITIAL 10K
NEXT 10K
MINEXTENTS 2
PCTINCREASE 80) ONLINE;
CREATE USER TEST
IDENTIFIED BY "P@ss1234"
DEFAULT TABLESPACE TS_TEST;
GRANT CONNECT,RESOURCE,DBA TO TEST;
ALTER USER TEST QUOTA UNLIMITED ON TS_TEST;
GRANT UNLIMITED TABLESPACE TO TEST;
EXIT;
EOF
3. ingrian 계정 및 테이블스페이스 생성 (만들어서 oracle 계정으로 실행)
#!/bin/bash
export ORACLE_SID=SIDTEST
echo " Create Tablespace SID:${ORACLE_SID}..."
sqlplus / as sysdba << EOF
CREATE TABLESPACE TS_INGRIAN
DATAFILE '/oradata/SIDTEST/ts_ingrian.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M
DEFAULT STORAGE(
INITIAL 10K
NEXT 10K
MINEXTENTS 2
PCTINCREASE 80) ONLINE;
CREATE USER INGRIAN
IDENTIFIED BY "P@ss1234"
DEFAULT TABLESPACE TS_INGRIAN;
GRANT CONNECT,RESOURCE,DBA TO INGRIAN;
ALTER USER INGRIAN QUOTA UNLIMITED ON TS_INGRIAN;
GRANT UNLIMITED TABLESPACE TO INGRIAN;
EXIT;
EOF
2. 테스트 테이블에 랜덤 데이터 생성 SQL
-- 시험용 테이블 생성 (SQL Developer에서 실행할 것.)
CREATE TABLE TBL_TEST (
test_no number(10),
test_name varchar2(30),
test_salary number(10)
) TABLESPACE TS_TEST;
--COMMIT
-- 테이블에 100행 삽입
BEGIN
FOR i IN 1..100
LOOP
INSERT INTO TBL_TEST
VALUES (i, DBMS_RANDOM.STRING('U',30), DBMS_RANDOM.VALUE(1000,7000));
IF MOD(i, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
--COMMIT;
4. ProtectDB 설치 (oracle 계정으로 설치)
[oracle@oracle ProtectDB-Oracle]$ ./install.sh
Description: Loads, reloads or removes the ProtectDB Oracle Provider
Usage: ./install.sh install
./install.sh reload
./install.sh localonly Oracle_Architecture
./install.sh uninstall
./install.sh uninstall localonly
[oracle@oracle ProtectDB-Oracle]$ ./install.sh install
Info: Creating logging directory /tmp/inginstall
Success
18:23:00 - ........................................................
18:23:00 - Beginning SafeNet Oracle's ProtectDB Installation
18:23:00 - 2013. 12. 06. (금) 18:23:00 KST
18:23:00 - ........................................................
18:23:00 - Info: Mode = install
Enter the password for the Oracle user SYS:
Confirm the password for the Oracle user SYS:
Enter the password for the Oracle user INGRIAN:
Confirm the password for the Oracle user INGRIAN:
18:23:07 - Info: Creating ProtectDB Instance ID
Please enter the ProtectDB Instance ID for this installation [22639-4024-17276-30561]:
18:23:10 - Using default ProtectDB Instance ID
18:23:10 - Info: Verifying ORACLE_HOME environment variable is set
18:23:10 - Success - using ORACLE_HOME: /oracle/11g
18:23:10 - Info: Locating sqlplus executable
18:23:10 - Success - using sqlplus: /oracle/11g/bin/sqlplus
18:23:10 - Info: Operating System - Linux Version - 2.6.18-308.el5
18:23:10 - Info: Generic OS type set to Linux
18:23:10 - Info: Using Oracle version 11
18:23:10 - Info: Verifying login for user SYS
18:23:10 - Success
18:23:10 - Info: Verifying login for user INGRIAN
18:23:10 - Success
18:23:10 - Info: Checking if DBMS_LOB package installed.
18:23:11 - Success
18:23:11 - Info: Checking if connector is present.
18:23:11 - Success
18:23:11 - Info: Determining Oracle Architecture
18:23:11 - Using 64 Bit Oracle
18:23:11 - Info: Installing 64 bit shared libriaries
18:23:11 - Info: Granting required permissions to INGRIAN user
18:23:11 - Success
18:23:11 - Info: Checking object dependencies
18:23:11 - Success
18:23:11 - Info: Creating ProtectDB Database Schema
18:23:13 - Success
18:23:13 - Info: Creating ProtectDB lib directory /oracle/11g/lib/safenet
18:23:13 - Success
18:23:13 - Info: Copying default ProtectDB.properties
18:23:13 - Info: Loading System Properties
Default ProtectDB.properties is located in:
/oracle/11g/lib/safenet/ProtectDB.properties
Enter full path to the properties file:
ERROR: Properties file path is required.
Enter full path to the properties file: /oracle/11g/lib/safenet/ProtectDB.properties
Enter the IP Address of the ProtectDB NAE Server: 10.10.10.43
Enter the Port of the ProtectDB NAE Server [9000]:
Enter the full path for your ProtectDB Log File: /home/oracle/logfile
No errors.
+ Property_File_Path = /oracle/11g/lib/safenet/ProtectDB.properties
18:24:18 - Success
18:24:18 - Info: Registering Shared Libraries
18:24:18 - Success
18:24:18 - Info: Creating ProtectDB Stored Procedures
18:24:18 - Success
18:24:18 - Info: Installing ProtectDB Indexing Cartridge
18:24:22 - Success
18:24:22 - Info: Creating ProtectDB User Defined Functions
18:24:23 - Success
18:24:23 - Info: Inserting ProtectDB Instance ID
18:24:23 - Success
18:24:23 - Info: ProtectDB lib directory /oracle/11g/lib/safenet already exists
18:24:23 - Info: Copying File
18:24:23 - Source - ./libingdb.so
18:24:23 - Destination - /oracle/11g/lib/safenet/libingdb.so
18:24:23 - Success
18:24:23 - Info: Copying File
18:24:23 - Source - ./libstdc++.so.6
18:24:23 - Destination - /oracle/11g/lib/safenet/libstdc++.so.6
18:24:23 - Success
18:24:23 - Info: Copying File
18:24:23 - Source - ./libgcc_s.so.1
18:24:23 - Destination - /oracle/11g/lib/safenet/libgcc_s.so.1
18:24:23 - Success
18:24:23 - Info: Creating Symbolic Link
18:24:23 - Physical File - /oracle/11g/lib/safenet/libstdc++.so.6
18:24:23 - Linked File - /oracle/11g/lib/libstdc++.so.6
18:24:23 - Success
18:24:23 - Info: Creating Symbolic Link
18:24:23 - Physical File - /oracle/11g/lib/safenet/libgcc_s.so.1
18:24:23 - Linked File - /oracle/11g/lib/libgcc_s.so.1
18:24:23 - Success
18:24:23 - .....................................................................
18:24:23 - Successfully Completed ProtectDB Oracle ProtectDB Installation
18:24:23 - 2013. 12. 06. (금) 18:24:23 KST
18:24:23 - .....................................................................
[oracle@oracle ProtectDB-Oracle]$
[oracle@oracle ProtectDB-Oracle]$ ./grantPermission.sh / ingrian
[oracle@oracle ProtectDB-Oracle]$ ./grantPermission.sh / test
4. Oracle 리스너 설정 변경
# /oracle/11g/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = SafeNetKey))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = SIDTEST)
(ORACLE_HOME = /oracle/11g)
(ENVS="LD_LIBRARY_PATH=/oracle/11g/lib/safenet")
)
)
# /oracle/11g/network/admin/tnsnames.ora
SIDTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SIDTEST)
)
)