반응형

[oracle@rac1:/oracle/grid]$ cd deinstall/

[oracle@rac1:/oracle/grid/deinstall]$ ./deinstall

Checking for required files and bootstrapping ...

Please wait ...

Location of logs /tmp/deinstall2015-09-11_09-37-29AM/logs/


############ ORACLE DEINSTALL & DECONFIG TOOL START ############



######################### CHECK OPERATION START #########################

## [START] Install check configuration ##



Checking for existence of the Oracle home location /oracle/grid

Oracle Home type selected for deinstall is: Oracle Grid Infrastructure for a Cluster

Oracle Base selected for deinstall is: /oracle/app/oracle

Checking for existence of central inventory location /oracle/app/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /oracle/grid

The following nodes are part of this cluster: rac1,rac2

Checking for sufficient temp space availability on node(s) : 'rac1,rac2'


## [END] Install check configuration ##


Traces log file: /tmp/deinstall2015-09-11_09-37-29AM/logs//crsdc.log

Enter an address or the name of the virtual IP used on node "rac1"[rac1-vip]

 > 


The following information can be collected by running "/sbin/ifconfig -a" on node "rac1"

Enter the IP netmask of Virtual IP "192.168.131.120" on node "rac1"[255.255.255.0]

 > 


Enter the network interface name on which the virtual IP address "192.168.131.120" is active

 > 


Enter an address or the name of the virtual IP used on node "rac2"[rac2-vip]

 > 


The following information can be collected by running "/sbin/ifconfig -a" on node "rac2"

Enter the IP netmask of Virtual IP "192.168.131.130" on node "rac2"[255.255.255.0]

 > 


Enter the network interface name on which the virtual IP address "192.168.131.130" is active

 > 


Enter an address or the name of the virtual IP[]

 > 



Network Configuration check config START


Network de-configuration trace file location: /tmp/deinstall2015-09-11_09-37-29AM/logs/netdc_check2015-09-11_10-08-47-AM.log


Specify all RAC listeners (do not include SCAN listener) that are to be de-configured [LISTENER,LISTENER_SCAN1]:


Network Configuration check config END


Asm Check Configuration START


ASM de-configuration trace file location: /tmp/deinstall2015-09-11_09-37-29AM/logs/asmcadc_check2015-09-11_10-08-48-AM.log


ASM configuration was not detected in this Oracle home. Was ASM configured in this Oracle home (y|n) [n]: 

ASM was not detected in the Oracle Home


######################### CHECK OPERATION END #########################



####################### CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /oracle/grid

The cluster node(s) on which the Oracle home deinstallation will be performed are:rac1,rac2

Oracle Home selected for deinstall is: /oracle/grid

Inventory Location where the Oracle home registered is: /oracle/app/oraInventory

Following RAC listener(s) will be de-configured: LISTENER,LISTENER_SCAN1

ASM was not detected in the Oracle Home

Do you want to continue (y - yes, n - no)? [n]: y

A log of this session will be written to: '/tmp/deinstall2015-09-11_09-37-29AM/logs/deinstall_deconfig2015-09-11_09-37-37-AM.out'

Any error messages from this session will be written to: '/tmp/deinstall2015-09-11_09-37-29AM/logs/deinstall_deconfig2015-09-11_09-37-37-AM.err'


######################## CLEAN OPERATION START ########################

ASM de-configuration trace file location: /tmp/deinstall2015-09-11_09-37-29AM/logs/asmcadc_clean2015-09-11_10-08-51-AM.log

ASM Clean Configuration END


Network Configuration clean config START


Network de-configuration trace file location: /tmp/deinstall2015-09-11_09-37-29AM/logs/netdc_clean2015-09-11_10-08-51-AM.log


De-configuring RAC listener(s): LISTENER,LISTENER_SCAN1


De-configuring listener: LISTENER

    Stopping listener: LISTENER

    Warning: Failed to stop listener. Listener may not be running.

Listener de-configured successfully.


De-configuring listener: LISTENER_SCAN1

    Stopping listener: LISTENER_SCAN1

    Warning: Failed to stop listener. Listener may not be running.

Listener de-configured successfully.


De-configuring Naming Methods configuration file on all nodes...

Naming Methods configuration file de-configured successfully.


De-configuring Local Net Service Names configuration file on all nodes...

Local Net Service Names configuration file de-configured successfully.


De-configuring Directory Usage configuration file on all nodes...

Directory Usage configuration file de-configured successfully.


De-configuring backup files on all nodes...

Backup files de-configured successfully.


The network configuration has been cleaned up successfully.


Network Configuration clean config END



---------------------------------------->


The deconfig command below can be executed in parallel on all the remote nodes. Execute the command on  the local node after the execution completes on all the remote nodes.


Run the following command as the root user or the administrator on node "rac2".


/tmp/deinstall2015-09-11_09-37-29AM/perl/bin/perl -I/tmp/deinstall2015-09-11_09-37-29AM/perl/lib -I/tmp/deinstall2015-09-11_09-37-29AM/crs/install /tmp/deinstall2015-09-11_09-37-29AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2015-09-11_09-37-29AM/response/deinstall_Ora11g_gridinfrahome1.rsp"


Run the following command as the root user or the administrator on node "rac1".


/tmp/deinstall2015-09-11_09-37-29AM/perl/bin/perl -I/tmp/deinstall2015-09-11_09-37-29AM/perl/lib -I/tmp/deinstall2015-09-11_09-37-29AM/crs/install /tmp/deinstall2015-09-11_09-37-29AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2015-09-11_09-37-29AM/response/deinstall_Ora11g_gridinfrahome1.rsp" -lastnode


Press Enter after you finish running the above commands


<----------------------------------------

**여기서 Enter 치지 말고, console 를 하나 더 열어 root 로 위에 빨간색 명령어 진행


[root@rac2 ~]# /tmp/deinstall2015-09-11_09-37-29AM/perl/bin/perl -I/tmp/deinstall2015-09-11_09-37-29AM/perl/lib -I/tmp/deinstall2015-09-11_09-37-29AM/crs/install /tmp/deinstall2015-09-11_09-37-29AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2015-09-11_09-37-29AM/response/deinstall_Ora11g_gridinfrahome1.rsp"

Using configuration parameter file: /tmp/deinstall2015-09-11_09-37-29AM/response/deinstall_Ora11g_gridinfrahome1.rsp


...


**기다리면 succeeded 등이 뜨면서 끝날때까지 기다린다.(난.. failed..Y Y)


Successfully deconfigured Oracle clusterware stack on this node <--이런식으로..


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

**똑같이 rac1 에서도 진행 한다.

[root@rac1 ~]# 

[root@rac1 ~]# root

-bash: root: command not found

[root@rac1 ~]# /tmp/deinstall2015-09-11_09-37-29AM/perl/bin/perl -I/tmp/deinstall2015-09-11_09-37-29AM/perl/lib -I/tmp/deinstall2015-09-11_09-37-29AM/crs/install /tmp/deinstall2015-09-11_09-37-29AM/crs/install/rootcrs.pl -force  -deconfig -paramfile "/tmp/deinstall2015-09-11_09-37-29AM/response/deinstall_Ora11g_gridinfrahome1.rsp" -lastnode

...

Successfully deconfigured Oracle clusterware stack on this node



다시 deinstall 하는 곳으로 넘어와서 Enter를 친다.


<----------------------------------------


Setting the force flag to false

Setting the force flag to cleanup the Oracle Base

Oracle Universal Installer clean START


Detach Oracle home '/oracle/grid' from the central inventory on the local node : Done


Delete directory '/oracle/grid' on the local node : Done


Delete directory '/oracle/app/oraInventory' on the local node : Done


Delete directory '/oracle/app/oracle' on the local node : Done


Detach Oracle home '/oracle/grid' from the central inventory on the remote nodes 'rac2' : Done


Delete directory '/oracle/grid' on the remote nodes 'rac2' : Done


Delete directory '/oracle/app/oraInventory' on the remote nodes 'rac2' : Done


Delete directory '/oracle/app/oracle' on the remote nodes 'rac2' : Done


Oracle Universal Installer cleanup was successful.


Oracle Universal Installer clean END



## [START] Oracle install clean ##


Clean install operation removing temporary directory '/tmp/deinstall2015-09-11_09-37-29AM' on node 'rac1'

Clean install operation removing temporary directory '/tmp/deinstall2015-09-11_09-37-29AM' on node 'rac2'


## [END] Oracle install clean ##



######################### CLEAN OPERATION END #########################



####################### CLEAN OPERATION SUMMARY #######################

Following RAC listener(s) were de-configured successfully: LISTENER,LISTENER_SCAN1

Oracle Clusterware is stopped and successfully de-configured on node "rac1"

Oracle Clusterware is stopped and successfully de-configured on node "rac2"

Oracle Clusterware is stopped and de-configured successfully.

Successfully detached Oracle home '/oracle/grid' from the central inventory on the local node.

Successfully deleted directory '/oracle/grid' on the local node.

Successfully deleted directory '/oracle/app/oraInventory' on the local node.

Successfully deleted directory '/oracle/app/oracle' on the local node.

Successfully detached Oracle home '/oracle/grid' from the central inventory on the remote nodes 'rac2'.

Successfully deleted directory '/oracle/grid' on the remote nodes 'rac2'.

Successfully deleted directory '/oracle/app/oraInventory' on the remote nodes 'rac2'.

Successfully deleted directory '/oracle/app/oracle' on the remote nodes 'rac2'.

Oracle Universal Installer cleanup was successful.



Run 'rm -rf /etc/oraInst.loc' as root on node(s) 'rac1,rac2' at the end of the session.


Run 'rm -rf /opt/ORCLfmap' as root on node(s) 'rac1,rac2' at the end of the session.

Oracle deinstall tool successfully cleaned up temporary directories.

#######################################################################



############# ORACLE DEINSTALL & DECONFIG TOOL END #############


[oracle@rac1:/oracle/grid/deinstall]$ 


끝!!이 아니고 아래 마지막으로 root 로 rac1 /rac2 에서 실행

[root@rac1 ~]# rm -rf /etc/oraInst.loc

[root@rac1 ~]# rm -rf /opt/ORCLfmap


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

[root@rac2 ~]# rm -rf /opt/ORCLfmap

[root@rac2 ~]# rm -rf /etc/oraInst.loc


제대로 지워졌는지 directory 등 확인해 보자!



반응형
반응형

정의

SYS_CONTEXT 함수는 세션정보를 얻어오는 함수

Namespace와 관계되는 Parameter 값을 반환

오라클에서 사용하는 Namespace는 USERENV이다.

 

 

형식

SYS_CONTEXT ('Namespace', 'Parameter' [, Length])

 

 

예제

- INSTANCE NAME/ DB NAME을 확인하고 싶은데 권한이 없는 일반 유저가 확인하고 싶을때 가능
 - USERENV : 현재 세션의 환경정보를 반환는 네임스페이스 입니다.

 

 
SELECT SYS_CONTEXT ('USERENV', 'SESSIONID'),   --  사용자 감사세션
       SYS_CONTEXT ('USERENV', 'OS_USER'),     --  사용자 운영체제 시스템 ID

       SYS_CONTEXT ('USERENV', 'HOST'),        --  사용자 PC이름

       SYS_CONTEXT ('USERENV', 'DB_NAME'),     --  DB 이름

       SYS_CONTEXT ('USERENV', 'INSTANCE_NAME'),     --  INSTANCE 이름

       SYS_CONTEXT ('USERENV', 'SID'),         --  SID (session number)

       SYS_CONTEXT ('USERENV', 'TERMINAL'),    --  사용자 운영체제 시스템 ID
       SYS_CONTEXT ('USERENV', 'IP_ADDRESS')   --  사용자 IP주소
  FROM DUAL ; 

파라미터

 정의

 Action

 모듈내 위치로 DBMS_Application_Info 패키지로 설정됨

 Audited_Cursorid

 감사를 트리거하는 SQL의 커서 ID를 반환

 Authenticated_Identity

 authentication 하에 사용중인 ID를 반환

 Authentication_Data

 로그인 사용자가 권한받아 사용중인 데이터

 Authentication_Method

 권한 방법을 반환

 BG_Job_id

 현재 세션의 Job ID

 Client_Identifier

 DBMS_Session을 통한 응용에 의해 설정된 ID

 Client_Info

 사용자 세션정보를 64 Byte 까지 반환

 Current_Bind

 FGA에 대한 Bind Variables

 Current_Schema

 현재 스키마에서 사용중인 Default 스키마 명

 Current_Schemaid

 현재 스키마에서 사용중인 Default 스키마 ID

 Current_SQL

 트리거된 첫 4 KByte

 Current_SQL_n

 n 은 1~7의 4 KByte

 Current_SQL_Length

 현재 SQL문의 길이

 DB_Domain

 DB_Domain 으로 지정한 DB의 도메인

 DB_Name

 DB_Name 으로 지정한 DB의 이름

 DB_Unique_Name

 DB_Unique_Name 으로 지정한 DB의 이름

 Entryid

 현재 Audit Entry 번호

 Entrprise_identity

 사용자의 Enterprise-wide identity

 FG_Job_id

 현재 세션의 Job ID

 Global_Context_Memory  Global Access Context에 의해서 사용중인 SGA의 번호
 Global_Uid  Global User ID
 Host  Host Machin의 이름
 Identification_Type  스키마가 Db에서 생성된 방법
 Instance  현재 인스턴스 ID
 Instance_Name  클라이언트가 연결된 Machine의 IP
 IP_Address  연결된 IP 주소
 isDBA  DBA 권한의 사용자인지 질의 [ True | False ]
 Lang  언어 명
 Language  User Session에서 사용중인 언어
 Module  DBMS_Application_info 패키지로 설정한 모듈 이름
 Network_Protocol  네트워크 프로토콜
 NLS_Calendar  현재 세션의 달력
 NLS_Currency  현재 세션의 화폐
 NLS_Date_Format  현재 세션의 날짜 포멧
 NLS_Date_Language  현재 세션의 날짜 표현 언어
 NLS_Sort  Binary 또는 언어적 Sort
 NLS_Territory  현재 세션의 territory
 OS_User  OS 유저 ID
 Policy_invoker  Row Level Security Policy 함수의 invoker
 Proxy_Enterprise_identity  Proxy 사용자가 Enterprise User인 경우, 오라클 인터넷 디렉토리 DN
 Proxy_Global_Uid  Enterprise User Security Proxy 사용자에 대한 오라클 인터넷 디렉토리의 Global 사용자
 Proxy_User  DB 사용자 명
 Proxy_Userid  DB 사용자 ID
 Server_Host  인스턴스가 동작중인 Machine의 호스트명
 Service_Name  주어진 세션이 접속된 서비스 명
 Session_User  엔터프라이즈 사용자인 경우 스키마 이름, 그외에는 DB 사용자 이름
 Session_Userid  DB 사용자 이름의 ID
 Sessionid  감사 세션 ID
 SID  세션 번호
 Statementid  감사문 ID
 Terminal  운영체제 시스템 ID

 

 

[출처]

http://yysvip.tistory.com/250

http://develop.sunshiny.co.kr/681

http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions079.htm

반응형

'Oracle > DBA' 카테고리의 다른 글

[펌][Oracle] 대용량 자료전환 시 튜닝 포인트 - Hash join  (0) 2015.11.19
[Oracle] Merge 구문 1  (0) 2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리  (0) 2015.09.03
특정 컬럼만 Masking  (0) 2015.09.03
[펌] DBMS_XPLAN  (0) 2015.08.28
[펌]Index Access and Filter  (0) 2015.08.28
반응형

[문제]

 - 일반 유저가 특정 테이블을 select 할 때 특정 컬럼의 내용들을 masking 하기를 원한다. (ex - 주민번호)

 - 물론 특정 유저는 해당 테이블을 select 할 때 masking 안된 모든 내용을 보기로 원함

 

[테이블 생성]

 

CREATE TABLE KIM.MASKING_TEST
AS SELECT

  OBJECT_ID, SUBOBJECT_NAME, DATA_OBJECT_ID

  , OBJECT_TYPE, CREATED, OBJECT_NAME, OWNER

FROM DBA_OBJECTS ;

 

- 이 후, 원하는 값을 masking 하기 위해 FUNCTION 을 생성 (OWNER 에 대해서 masking)

 

 

CREATE OR REPLACE FUNCTION KIM.MSK_OWNER_FUN
 (schema in varchar2, tab in varchar2) return varchar2 as
  predicate  varchar2(8) default NULL;
  BEGIN
   IF LOWER(SYS_CONTEXT('USERENV','POLICY_INVOKER')) in ( 'kim', 'system')

       --해당 아이디들은 모든 컬럼이 보여지도록 설정
     THEN predicate := '1=1';
  ELSE predicate := '1=2';

      -- 이 외의 ID 들은 OWNER 라는 컬럼이 안보여 지도록 설정
  END IF;
  RETURN predicate;
END;

 

-- function 을 정책으로 설정

 

 

 begin
dbms_rls.add_policy(object_schema => 'KIM', --해당 유저
    object_name => 'MASKING_TEST',   -- 해당 테이블
    policy_name =>'MSK_OWN_POLICY',  -- 정책 명
    function_schema => 'KIM',  -- 해당 유저
    policy_function =>'MSK_OWNER_FUN', -- 정책 함수
    statement_types => 'SELECT', --select 만 가능
    SEC_RELEVANT_COLS     => 'OWNER', --owner라는 column 설정
    SEC_RELEVANT_COLS_OPT => DBMS_RLS.ALL_ROWS
    );
end;
/

 

[확인]  User로는 Kim 이라는 메인 User / System 계정 / Scott 계정

 

1. Kim 으로 접속 후 확인 (정상확인 - OWNER 이 정상적으로 보여짐)

 

2. SYSTEM 계정으로 확인(정상확인 - OWNER 이 정상적으로 보여짐) 

 

3. SCOTT 계정으로 확인(확인이 안됨 - OWNER가 null 값으로 표시) 

 

뭔가가 아쉽네...

더 유동적인 방법이 필요한데....능력부족...1

 

[출처들]

 

 SYS_CONTEXT | http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
                        | 한국어 http://yysvip.tistory.com/250

https://docs.oracle.com/cd/E11882_01/server.112/e41481/tdm_data_masking.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_rls.htm
http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm
http://jhroom.co.kr/22627

https://oracle-base.com/articles/misc/dbms_session

 

 

반응형

'Oracle > DBA' 카테고리의 다른 글

[Oracle] Merge 구문 1  (0) 2015.11.12
[펌] [ORACLE] SYS_CONTEXT 및 USERENV 정리  (0) 2015.09.03
특정 컬럼만 Masking  (0) 2015.09.03
[펌] DBMS_XPLAN  (0) 2015.08.28
[펌]Index Access and Filter  (0) 2015.08.28
DBMS_RANDOM  (0) 2015.08.18

+ Recent posts