pat98's always & forever

Red Hat Enterprise Linux 7 소개 자료

원래 일본사람이 작성한 자료이나 한글자료는 마땅히 정리가 잘된게 없어

발표 겸  구글신의 힘을 빌려 직접 번역한 자료임

참고하실 분들은 따운~~~

rhel7_newfeature.pdf

set linesize 150
col name for A30
col value for A30
select name, type, value,isdefault, isses_modifiable, issys_modifiable from v$parameter where name like '%process%';

  - NAME : 파라메터 이름
  - TYPE : 파라메터 설정 타입
  - VALUE : 파라메터 값
  - ISDEFAULT : 파라메터 기본 값 사용 유무
  - ISSES_MODIFIABLE : ALTER SESSION 명령으로 파라메터 설정 값을 변경할 수 있는지 여부
  - ISSYS_MODIFIABLE : ALTER SYSTEM 명령으로 파라메터 설정 값을 변경할 수 있는지 여부
 
 TYPE 컬럼 값         내용           파라메터 예제
     1       TRUE/FALSE 중 하나를 의미  TIMED_STATISTICS
     2       문자 값을 의미          CONTROL_FILES
     3       작은 숫자 값을 의미  SESSION
     4       파일을 의미          IFILE
     5       지정된 값 없음          -
     6       큰 숫자 의미          SHARED_POOL_SIZE

EM 사용시 사용되는 SYSMAN 유저생성 및 권한 부여

=================================================

DROP USER SYSMAN CASCADE;

CREATE USER SYSMAN
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE MGMT_TABLESPACE
  TEMPORARY TABLESPACE TEMP
  PROFILE MGMT_INTERNAL_USER_PROFILE
  ACCOUNT UNLOCK;
  -- 4 Roles for SYSMAN
  GRANT CONNECT TO SYSMAN;
  GRANT MGMT_USER TO SYSMAN WITH ADMIN OPTION;
  GRANT RESOURCE TO SYSMAN;
  GRANT SELECT_CATALOG_ROLE TO SYSMAN;
  ALTER USER SYSMAN DEFAULT ROLE ALL;
  -- 19 System Privileges for SYSMAN
  GRANT ALTER SESSION TO SYSMAN;
  GRANT ALTER USER TO SYSMAN;
  GRANT CREATE ANY SYNONYM TO SYSMAN;
  GRANT CREATE DATABASE LINK TO SYSMAN;
  BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  PRIVILEGE    => SYS.DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
  GRANTEE      => 'SYSMAN',
  GRANT_OPTION => FALSE);
END;
/
  GRANT CREATE JOB TO SYSMAN;
  GRANT CREATE MATERIALIZED VIEW TO SYSMAN;
  GRANT CREATE PUBLIC SYNONYM TO SYSMAN;
  BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  PRIVILEGE    => SYS.DBMS_RULE_ADM.CREATE_RULE_OBJ,
  GRANTEE      => 'SYSMAN',
  GRANT_OPTION => FALSE);
END;
/
  BEGIN
SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  PRIVILEGE    => SYS.DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
  GRANTEE      => 'SYSMAN',
  GRANT_OPTION => FALSE);
END;
/
  GRANT CREATE SESSION TO SYSMAN;
  GRANT CREATE TABLE TO SYSMAN;
  GRANT CREATE USER TO SYSMAN;
  GRANT CREATE VIEW TO SYSMAN;
  GRANT DROP ANY SYNONYM TO SYSMAN;
  GRANT DROP PUBLIC SYNONYM TO SYSMAN;
  GRANT DROP USER TO SYSMAN;
  GRANT MANAGE SCHEDULER TO SYSMAN;
  GRANT SELECT ANY DICTIONARY TO SYSMAN;
  -- 3 Tablespace Quotas for SYSMAN
  ALTER USER SYSMAN QUOTA UNLIMITED ON MGMT_AD4J_TS;
  ALTER USER SYSMAN QUOTA UNLIMITED ON MGMT_ECM_DEPOT_TS;
  ALTER USER SYSMAN QUOTA UNLIMITED ON MGMT_TABLESPACE;
  -- 48 Object Privileges for SYSMAN
    GRANT EXECUTE ON MGMT_VIEW.EM_COMPLIANCE_VIEW_USER TO SYSMAN;
    GRANT EXECUTE ON MGMT_VIEW.EM_METRIC_EVAL_VIEW_USER TO SYSMAN;
    GRANT DELETE, INSERT, SELECT, UPDATE ON SYS.AUX_STATS$ TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_APPLICATION_INFO TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_AQ TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_AQADM TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_AQ_BQVIEW TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_AUTO_TASK_ADMIN TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_CRYPTO TO SYSMAN WITH GRANT OPTION;
    GRANT EXECUTE ON SYS.DBMS_FEATURE_USAGE TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_JOB TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_LOB TO SYSMAN WITH GRANT OPTION;
    GRANT EXECUTE ON SYS.DBMS_LOCK TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_OBFUSCATION_TOOLKIT TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_OUTPUT TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_RANDOM TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_REDEFINITION TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_RLS TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_SESSION TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_SHARED_POOL TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_SNAPSHOT TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_SPACE TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_SQL TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_STATS TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_TRANSACTION TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_TYPES TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_UTILITY TO SYSMAN;
    GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO SYSMAN;
    GRANT EXECUTE ON SYS.MGMT_PASS_VERIFY TO SYSMAN;
    GRANT SELECT ON SYS.OBJ$ TO SYSMAN;
    GRANT SELECT ON SYS.PROCEDURE$ TO SYSMAN;
    GRANT SELECT ON SYS.QT59150_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT59325_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT59731_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT59795_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT59837_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT59859_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT60485_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT60718_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT64134_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT71031_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.QT73997_BUFFER TO SYSMAN;
    GRANT SELECT ON SYS.USER$ TO SYSMAN;
    GRANT EXECUTE ON SYS.UTL_FILE TO SYSMAN;
    GRANT EXECUTE ON SYSMAN_RO.DERIVED_ASSOC_SANDBOX TO SYSMAN;
    GRANT EXECUTE ON SYSMAN_RO.EM_METRIC_EVAL_RO_USER TO SYSMAN;
    GRANT EXECUTE ON SYSMAN_RO.EM_RO_SANDBOX TO SYSMAN;

DBA_REGISTRY 는 이미 설치된 컴포넌트를 보여주는 View 입니다. 

1.Status 값 Loaded는 어떤 상태를 뜻하는것 입니까? 

VALID (혹은 INVALID) 상태로 변경하기 전의 상태를 뜻합니다 , 상태가 Loaded 거나 Valid 면 컴포넌트가 이미 성공적으로 설치 되었음을 뜻합니다. 

더욱 상세한 정보는 아래 문서를 참조하세요 : Interpretation of STATUS in DBA_REGISTRY ( Doc ID 789626.1 ) 

2. Loaded 상태를 Valid 상태로 변경하는 방법? 

Step 1: Status 가 Loaded 인 컴포넌트의 comp_id를 찾아냅니다. 

SQL> select comp_id, comp_name, status, version from dba_registry; 

Step 2:아래 명령문으로 변경시킵니다. 

SQL> execute sys.dbms_registry.valid(' comp_id 값 '); 

지난 1월 21일 오라클에서 1년만에 X4에 이어 Exadata x5-2 가 출시되었음..

몇가지 특징을 정리해 보면..

1. Database node - CPU 18core(Xeon® E5-2699 v3)로 확장,클럭수 2.3Hz로 살짝 내려갔으나 기존대비 50% 늘어남

   Memory 용량이 768G 까지 확장가능, 기존대비 50% 늘어남

2. 디스크 컨트롤러 배터리를 교체할 필요가 없어졌음,아주 바람직해짐 (super capaciator 적용, Maintainance 용이)

3.Cell Node - 기존의 HC,HP Disk가 구분되던 것에서 X5 부터 HP는 아예 사라짐

  대신 EF(Extream Flash)디스크라고 해서 ALL Flash 스토리지 지원됨 (하나가 1.6TB, Total 12.8TB 가능)

  -> 그냥 SSD 디스크가 아니라 PCIe 방식의 NVMe Flash Drive 로 가장 최신기술임

  -> 2.5inch F160 PCIe Drives 

  -> 거의 현존 최고 속도라고 봐도 될듯.

4. 노드확장이 용이해 졌음

  -> 이전에는 Scale-up 시 상당히 제한적이었으나, X5부터 유연하게 필요한 만큼 확장가능

  -> 난 이번에 DB node 2개, Cell 4개 붙이고 싶다. 그러면 원하는 만큼 ~~

5. X5를 지원하는 이미지 12.1.2.1이 발표되었음.

  -> Exafusion 이라는 기존의 RDS 프로토콜보다 3배 빠른 캐시퓨전 구현됨 ㄷㄷㄷ

  -> 기존 infiniband 를 발라버리는 속도..이건 테스트해봐야 체감할 듯..

6. Exadata 내에서 OVM 가상환경 구현가능..아 이건 글쎄...좀더 두고봐야 할듯..

7. In-Merory fault Tolerance 구현됨. 세계최초라고 함.

8. DB노드에서도 기존 Cell 처럼 DBMCLI 라는 명령어 생기고 모니터링 및 관리가능해짐.

9. Oracle Linux 6.6 버전 채용으로 기존 5.X 버전에서 쑥 올려버림

   -> 글고 X5부터는 Solaris 기반 Exa 는 아예 없어짐..안녕 Solaris...

10. Exadata에서 ACFS가 지원이 됨..요건 사용하기 나름..

=> 결론 : Exadata X5 아주 좋음...많이 사주세요..ㅋㅋ

2014년 10월 쯤 작성한 Exadata Troubleshooting 문서

Exadata Troubleshooting.pptx

11.2 버전은 기본으로 disable 되어 있다.

How To Verify If DNFS Is Enabled (“ON”) Or Disabled (“OFF”) Before The Database Instance Is Started In 11.2 Release? (Doc ID 1551909.1)

SQL> shutdown immediate

oracle kernel level 에서 DNFS 옵션 enable

oracle 계정에서

1. id 

2. cd $ORACLE_HOME/rdbms/lib

3. 
make -f ins_rdbms.mk dnfs_on

아래와 같은 메세지만 썰렁하게 보인다.
rm -f /oracle/product/11.2.0.4/lib/libodm11.so; cp /oracle/product/11.2.0.4/lib/libnfsodm11.so /oracle/product/11.2.0.4/lib/libodm11.so

make -f ins_rdbms.mk dnfs_off
      
마찬가지로 아래와 같은 메세지만 썰렁하게 보인다.   
rm -f /oracle/product/11.2.0.4/lib/libodm11.so; cp /oracle/product/11.2.0.4/rdbms/lib/libodm11.so.dummy /oracle/product/11.2.0.4/lib/libodm11.so

4. 
이렇게만 나오기 때문에 적용되었는지 확인할 방법은 아래와 같다.

방법 1) 적용이 안되어 있으면 아래와 같이 ODM ERR: Calling stubbed version 메세지가 보인다.

[oracle:/oracle/product/11.2.0.4/rdbms/lib]#strings /oracle/product/11.2.0.4/lib/libodm11.so | grep -i odm
odm_deregisternic
odm_registernic
odm_destroy
odm_deregistermem
odm_registermem
odm_mname
odm_resize
odm_cancel
odm_ioerror
odm_io
odm_cleanup
odm_unidentify
odm_reidentify
odm_identify
odm_delete
odm_abort
odm_commit
odm_create
odm_error
odm_fini
odm_init
odm_discover
ODM ERR: Calling stubbed version
Stubbed ODM Library, Version: 1.0

적용이 되어 있으면 아래와 같이 ODM ERR: Calling stubbed version 메세지가 없다.
[oracle:/oracle/product/11.2.0.4/rdbms/lib]#strings /oracle/product/11.2.0.4/lib/libodm11.so | grep -i odm
kgodm_deregisternic
kgodm_registernic
kgodm_destroy
kgodm_deregistermem
kgodm_registermem
kgodm_mname
kgodm_resize
kgodm_cancel
kgodm_ioerror
kgodm_io
kgodm_cleanup
kgodm_unidentify
kgodm_reidentify
kgodm_identify
kgodm_readlink
kgodm_readdir
kgodm_fsinfo
kgodm_fsstat
kgodm_setattr
kgodm_getattr
kgodm_rmdir
kgodm_mkdir
kgodm_delete
kgodm_abort
kgodm_commit
kgodm_create
kgodm_error
kgodm_fini
kgodm_init
kgodmfhtr_
odm_registerthread
kgodm_discover
nfs odm heap
kgodm event %u set to level %u

방법 2)

5. SQL> startup

6. alertlog 메세지 확인

적용이 되면 파라미터 화일을 읽고 바로 그 아래에 ODM Running 되었다는 메세지를 확인 할수 있다.

  audit_file_dest          = "/oracle/admin/TEST/adump"
  audit_trail              = "DB"
  db_name                  = "TEST"
  open_cursors             = 300
  diagnostic_dest          = "/oracle"
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0 
Tue May 07 22:08:18 2024
PMON started with pid=2, OS id=1964 
Tue May 07 22:08:18 2024
PSP0 started with pid=3, OS id=1966 


Direct NFS: channel id [0] path [celntap3-bc] to filer [celntap3-bc] via local [] is UP
Direct NFS: channel id [1] path [celntap3-bc] to filer [celntap3-bc] via local [] is UP

7. DNFS 설정값 확인 view (RAC 인 경우 gv$DNFS_* )

1) select * from V$DNFS_SERVERS; Shows a table of servers accessed using Direct NFS.

2) select * from V$DNFS_FILES; Shows a table of files currently open using Direct NFS.

3) select * from V$DNFS_CHANNELS; Shows a table of open network paths (or channels) to servers for which Direct NFS is providing files.

4) select * from V$DNFS_STATS; Shows a table of performance statistics for Direct NFS.

[EXA1]root@exa1:/root/patch_11.2.3.3.1.140708# ./patchmgr -h
Usage:
./patchmgr -cells cell_host_file
          [-patch_check_prereq | -rollback_check_prereq [-rolling] [-ignore_alerts]]
          [-patch | -rollback [-rolling] [-ignore_alerts]]
          [-cleanup]

./patchmgr -ibswitches [ibswitch_list_file]
          <-upgrade | -downgrade> [-ibswitch_precheck] [-force]]

./patchmgr -h

OPTIONS
    -h
         Displays this screen

  Cell patching and rollback
    The following options are supported for cell patching and(or) rollback:

    -cells cell_list_file
         Specifies the name of the cell list file. The file contains one
         cell hostname or ip per line. The cell patching will fail if the
         list file is not specified.

    -cleanup
         Cleanup mode. Cleans up all patch files and temporary content on all
         cells.  Before cleaning up, collects logs and information for problem
         diagnostics and analysis. Cleaning up patch files can be done manually
         if patch fails by removing directory /root/_patch_hctap_ on each cell.

    -ignore_alerts
         Ignore any active hardware alerts on the Exadata cell and proceed with
         the patching.

    -patch
         Applys the patch, including firmware updates, wherever possible (BIOS,
         Disk Controller and if possible disk drives) to all cells in the cell
         list file.
   
    -patch_check_prereq
         Runs prerequisite check on all the cells to determine if the patch can
         be applied to the cells.

    -rollback
         Rolls back the patch.
   
    -rollback_check_prereq
         Runs prerequisite check on all the cells to determin if the cells can
         be rolled back for this specific patch.

    -rolling
         Applies the patch or executes the rollback in rolling fashion, one cell
         at a time.

         Environment variable EXA_PATCH_ACTIVATE_TIMEOUT_SECONDS controls the
         timeout value waiting for the grid disks to be activated.
         The default is set to 36000 (10 hours).
 
    -smtp_from "addr"
         The address patchmgr notification sent from.
         sendmail daemon must be started to send email notification.

    -smtp_to "addr1 addr2 addr3 ..."
         The address(es) patchmgr notification sent to.
         sendmail daemon must be started to send email notification.

  Infiniband switch upgrade and downgrade
    The following options are supported for infiniband switch upgrade and(or)
    downgrade:
   
    -ibswitches [ibswitch_list_file]
         Specifies the name of the InfiniBand switch list file. The file has one
         switch hostname or ip per line. If no filename is provided, then it
         runs the command on all InfiniBand switches discovered from this host
         by running ibswitches command.

    -downgrade
         Downgrade the InfiniBand switches in the list file to 1.3.3-2

    -force
         Specifies to proceed with the upgrade or downgrade even on non-critical
         failures.
     
    -ibswitch_precheck
         Runs the pre-update validation checks on the InfiniBand switches in the
         list file.

    -upgrade
         Upgrade the InfiniBand switches in the list file to 2.1.3-4

NOTE Cells will reboot during the patch or rollback process.
NOTE For non-rolling patch or rollback, ensure all ASM instances using
NOTE the cells are shut down for the duration of the patch or rollback.
NOTE For rolling patch or rollback, ensure all ASM instances using
NOTE the cells are up for the duration of the patch or rollback.

WARNING Do not start more than one instance of patchmgr.
WARNING Do not interrupt the patchmgr session.
WARNING Do not alter state of ASM instances during patch or rollback.
WARNING Do not resize the screen. It may disturb the screen layout.
WARNING Do not reboot cells or alter cell services during patch or rollback.
WARNING Do not open log files in editor in write mode or try to alter them.