오라클 데이터베이스 RAC Global Lock Scripts

  * 2018-04          최초생성

  * 2018-05          Lock Type 추가

SELECT GV_S.INST_ID,

       GV_S.SID AS GV_SESSION_ID,

       GLS.SID AS GV_LOCK_SID,

       GV_S.INST_ID AS GV_SESSION_ID,

       GV_S.SERIAL#,

       LPAD(' ', (LEVEL - 1) * 4, ' ') || DECODE(LEVEL, 1, '', 'L-->') ||

       GV_S.USERNAME AS USERNAME,

       GV_S.STATUS,

       GV_S.PROCESS,

       GV_S.SCHEMANAME,

       GV_S.OSUSER,

       GV_S.PROCESS,

       GV_S.MACHINE,

       GV_S.TERMINAL,

       GV_S.PROGRAM,

       GV_S.TYPE,

       GLS.NAME,

       GV_S.SQL_ID,

       GV_S.PREV_SQL_ID,

       GV_S.MODULE,

       GV_S.COMMAND,

       GV_SC.COMMAND_NAME,

       GV_S.ACTION,

       GV_S.BLOCKING_SESSION_STATUS,

       GV_S.BLOCKING_INSTANCE,

       GV_S.BLOCKING_SESSION,

       GV_S.FINAL_BLOCKING_SESSION_STATUS,

       GV_S.FINAL_BLOCKING_INSTANCE,

       GV_S.FINAL_BLOCKING_SESSION,

       GV_S.EVENT,

       GV_S.SERVICE_NAME,

       GLS.TYPE,

       GLS.ID1,

       GLS.ID1_TAG,

       GLS.ID2,

       GLS.ID2_TAG,

       GLS.LMODE,

       GLS.REQUEST

  FROM GV$SESSION GV_S,

       GV$SQLCOMMAND GV_SC,

       (SELECT GV_L.INST_ID,

               GV_L.SID,

               GV_L.REQUEST,

               GV_L.LMODE,

               GV_L.ID1,

               GV_L.ID2,

               GV_L.TYPE,

               GV_L.BLOCK,

               GV_LT.NAME,

               GV_LT.ID1_TAG,

               GV_LT.ID2_TAG,

               GV_LT.IS_USER,

               GV_LT.DESCRIPTION

          FROM GV$LOCK GV_L, GV$LOCK_TYPE GV_LT

         WHERE 1 = 1

           AND GV_L.INST_ID = GV_LT.INST_ID

           AND GV_L.TYPE = GV_LT.TYPE) GLS --GLOBAL LOCK STATUS

 WHERE 1 = 1

   AND GV_S.INST_ID = GLS.INST_ID(+)

   AND GV_S.SID = GLS.SID(+)

   AND GV_S.INST_ID = GV_SC.INST_ID

   AND GV_S.COMMAND = GV_SC.COMMAND_TYPE

 START WITH GLS.REQUEST = 0

        AND GLS.BLOCK = 1 -- RAC CHECK =2

        AND GLS.LMODE > 0

CONNECT BY PRIOR GV_S.SID = GV_S.BLOCKING_SESSION

       AND GLS.REQUEST > 0;

• Application Development
• Availability
• Big Data and Data Warehousing
• Database Overall
• Diagnosability
• Performance
• RAC and Grid
• Security

• Application Development
  
• Availability
  
• Big Data and Data Warehousing
  
• Compression and Archiving
  
• Database Lifecycle Management
  
• Database Overall
  
• Diagnosability
  
• Exadata
  
• Manageability
  
• Miscellaneous
  
• Performance
  
• Public Cloud
  
• RAC and Grid
  
• Security
  
• Spatial and Graph
  
• Windows
  

• Section 1.1, "Advanced Index Compression"

• Section 1.2, "Approximate Count Distinct"

• Section 1.3, "Attribute Clustering"

• Section 1.4, "Automatic Big Table Caching"

• Section 1.5, "FDA Support for CDBs"

• Section 1.6, "Full Database Caching"

• Section 1.7, "In-Memory Aggregation"

• Section 1.8, "In-Memory Column Store"

• Section 1.9, "JSON Support"

• Section 1.10, "New FIPS 140 Parameter for Encryption"

• Section 1.11, "PDB CONTAINERS Clause"

• Section 1.12, "PDB File Placement in OMF"

• Section 1.13, "PDB Logging Clause"

• Section 1.14, "PDB Metadata Clone"

• Section 1.15, "PDB Remote Clone"

• Section 1.16, "PDB Snapshot Cloning Additional Platform Support"

• Section 1.17, "PDB STANDBYS Clause"

• Section 1.18, "PDB State Management Across CDB Restart"

• Section 1.19, "PDB Subset Cloning"

• Section 1.20, "Rapid Home Provisioning"

• Section 1.21, "Zone Maps"

• Application Development

• Business Intelligence and Data Warehousing

• Compression and Archiving

• Database Overall

• High Availability

• Manageability

• Performance

• Oracle RAC and Grid Infrastructure

• Security

• Spatial and Graph

• Unstructured Data

• Upgrades

• Windows

• Application Development

• Availability

• Database Overall

• Business Intelligence and Data Warehousing

• Clustering

• Content Management Services

• Fault Diagnosability

• Information Integration

• Location Services and Specialty Data

• Manageability

• Performance

• Security

• Windows

• Application Development

• Availability

• Business Intelligence

• Clustering

• Content Management

• Database Overall

• Database Security

• Grid Computing

• Grid Management

• Information Integration

• Location Services

• Performance and Scalability

• Server Manageability

• Windows

• Workspace Manager

참고
http://docs.oracle.com/cd/NOSQL/html/quickstart.html

linux 비트 확인
1.
 getconf LONG_BIT
2.
file /sbin/init

/**************** 자바 관련 설정 *************/
http://www.oracle.com/technetwork/java/javase/downloads/index.html
1.자바 다운로드 .rpm.bin
   -- A. NO SQL rpm.bin 실행법
   -- B. 권한 확인후
   -- C. ./파일명
  
  
2.복사후 ./파일명

3.위치 확인
4. 기존 자바 버젼 변경

/usr/sbin/alternatives --install /usr/bin/java java /usr/java/default/bin/java 2

/usr/sbin/alternatives --config java

There are 2 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
*+ 1           /usr/lib/jvm/jre-1.4.2-gcj/bin/java
   2           /usr/java/default/bin/java
  
Enter to keep the current selection[+], or type selection number: 2
/**************** 자바 관련 설정 *************/

1. download
http://www.oracle.com/technetwork/database/nosqldb/downloads/index.html
 gunzip kv-ce-1.2.116.tar.gz
 tar xvf kv-ce-1.2.116.tar

설치후 테스트
콘솔1
java -jar lib/kvstore-1.2.116.jar kvlite

콘솔2
java -jar lib/kvstore-1.2.116.jar ping -port 5000 -host localhost.localdomain

자바 예제
javac -cp examples:lib/kvclient-1.2.116.jar examples/hello/HelloBigDataWorld.java
java -cp examples:lib/kvclient-1.2.116.jar hello.HelloBigDataWorld

결과
Hello Big Data World!

-----------------------------------------------------------------------
테스트 종료

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

추가1 : Nosql host 변경작업 테스트  (config.xml 에서 host 부분 설정)

추가2 : Toad For Cloud Database 사용해서 접속시도 (삽질후 포기; 지원을 아직 안하는지 내가 엄한데 파고 있는지 파악 불가)

추가3 : mongo db  설치후 Toad For Cloud Database 툴 테스트예정  (2012.05.)

추가4: mongo db 설치후 리눅스에서 작동확인 -Toad For Cloud Database 사용해서 접속시도 (실패: 삽질후 방화벽 해제 (원인 :가상머신 재부팅후 방화벽 자동 실행됨: 망할;;;;;) ->방화벽 해제후 접속성공

-- 로컬 가상머신에서 테스트 및 http console 확인 되었을경우 방화벽 포트 확인(기본적으로 방화벽 부터 확인해놓자)

순서

환경 :

local os : Windows 7 64bit 

Oracle Database 11gR2

1. R INSTALL (사용버젼 1.5 )

2. 필요 패키지 설치 (DBI,RODBC...)

3. ODBC 설정

64-bit Oracle Data Access Components (ODAC) 설치

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

ODAC112030_x64.zip

압축해제후 Setup (기존 Oracle 제품 설치 확인 : Oracle Home 관련 주의)

설치된 Database TNS 확인

ADDRESS 항목의 HOST 변경

1.DataBase 가 설치된 위치를 설정 해야 합니다.

  -- 접속하려는 데이터 베이스가 있는 IP 를 직접 입력하거나 , 이름을 넣고,

      Host 파일을 수정

HOST 파일 설정

1. 위치 :C:\Windows\System32\drivers\etc

 DSN 추가 및 설정

    시스템-> 제어판-> 관리도구 -> 데이터원본(ODBC)

data source name : DB11G

접속확인

4.R에서 결과 보기

> library("DBI");library("RODBC"); hdb <- odbcConnect("DB11G")
> res <-sqlQuery(hdb, "SELECT * FROM emp")
> res
   EMPNO  ENAME       JOB  MGR   HIREDATE  SAL COMM DEPTNO
1   7369  SMITH     CLERK 7902 1980-12-17  800   NA     20
2   7499  ALLEN  SALESMAN 7698 1981-02-20 1600  300     30
3   7521   WARD  SALESMAN 7698 1981-02-22 1250  500     30
4   7566  JONES   MANAGER 7839 1981-04-02 2975   NA     20
5   7654 MARTIN  SALESMAN 7698 1981-09-28 1250 1400     30
6   7698  BLAKE   MANAGER 7839 1981-05-01 2850   NA     30
7   7782  CLARK   MANAGER 7839 1981-06-09 2450   NA     10
8   7788  SCOTT   ANALYST 7566 1987-04-19 3000   NA     20
9   7839   KING PRESIDENT   NA 1981-11-17 5000   NA     10
10  7844 TURNER  SALESMAN 7698 1981-09-08 1500    0     30
11  7876  ADAMS     CLERK 7788 1987-05-23 1100   NA     20
12  7900  JAMES     CLERK 7698 1981-12-03  950   NA     30
13  7902   FORD   ANALYST 7566 1981-12-03 3000   NA     20
14  7934 MILLER     CLERK 7782 1982-01-23 1300   NA     10
>