pat98's always & forever

Mounting Diskgroup Fails With ORA-15063 and V$ASM_DISK Shows PROVISIONED (문서 ID 1487443.1)

ORA-15063,  디스크 그룹이 mount 안되고 ASM file head 손상이 의심될때...

상태를 조회해 보면 PROVISIONED 라고 나옴..

SQL> select group_number gn,disk_number dn, mount_status, header_status,mode_status,state, total_mb, free_mb, label, path from v$asm_disk order by group_number, disk_number;

  GN   DN MOUNT_STATUS HEADER_STATU MODE_STATUS  STATE         TOTAL_MB    FREE_MB        LABEL          PATH                                     

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

   0    0 CLOSED              PROVISIONED        ONLINE            NORMAL                  0                0       CRS             ORCL:CRS  

   0    1 CLOSED              PROVISIONED        ONLINE            NORMAL                  0                0       DATA           ORCL:DATA

[oracle:/home/oracle]#kfed read /dev/ocrvote01

kfbh.endian:                          1 ; 0x000: 0x01

kfbh.hard:                          130 ; 0x001: 0x82

kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD

kfbh.datfmt:                          1 ; 0x003: 0x01

kfbh.block.blk:                       0 ; 0x004: blk=0

kfbh.block.obj:              2147483648 ; 0x008: disk=0

kfbh.check:                   318085142 ; 0x00c: 0x12f59816

kfbh.fcn.base:                        0 ; 0x010: 0x00000000

kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000

kfbh.spare1:                          0 ; 0x018: 0x00000000

kfbh.spare2:                          0 ; 0x01c: 0x00000000

kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8

kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000

kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000

kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000

kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000

kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000

kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000

kfdhdb.compat:                186646528 ; 0x020: 0x0b200000

kfdhdb.dsknum:                        0 ; 0x024: 0x0000

kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL

kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER

kfdhdb.dskname:               DATA_0000 ; 0x028: length=9

kfdhdb.grpname:                    DATA ; 0x048: length=4

kfdhdb.fgname:                DATA_0000 ; 0x068: length=9

kfdhdb.capname:                         ; 0x088: length=0

kfdhdb.crestmp.hi:             33037568 ; 0x0a8: HOUR=0x0 DAYS=0x8 MNTH=0x7 YEAR=0x7e0

kfdhdb.crestmp.lo:           3173142528 ; 0x0ac: USEC=0x0 MSEC=0x94 SECS=0x12 MINS=0x2f

kfdhdb.mntstmp.hi:             33037568 ; 0x0b0: HOUR=0x0 DAYS=0x8 MNTH=0x7 YEAR=0x7e0

kfdhdb.mntstmp.lo:           3327275008 ; 0x0b4: USEC=0x0 MSEC=0x8c SECS=0x25 MINS=0x31

kfdhdb.secsize:                     512 ; 0x0b8: 0x0200

kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

kfdhdb.mfact:                    113792 ; 0x0c0: 0x0001bc80

kfdhdb.dsksize:                    1019 ; 0x0c4: 0x000003fb

kfdhdb.pmcnt:                         2 ; 0x0c8: 0x00000002

kfdhdb.fstlocn:                       1 ; 0x0cc: 0x00000001

kfdhdb.altlocn:                       2 ; 0x0d0: 0x00000002

kfdhdb.f1b1locn:                      2 ; 0x0d4: 0x00000002

kfdhdb.redomirrors[0]:                0 ; 0x0d8: 0x0000

kfdhdb.redomirrors[1]:                0 ; 0x0da: 0x0000

kfdhdb.redomirrors[2]:                0 ; 0x0dc: 0x0000

kfdhdb.redomirrors[3]:                0 ; 0x0de: 0x0000

kfdhdb.dbcompat:              168820736 ; 0x0e0: 0x0a100000

kfdhdb.grpstmp.hi:             33037568 ; 0x0e4: HOUR=0x0 DAYS=0x8 MNTH=0x7 YEAR=0x7e0

kfdhdb.grpstmp.lo:           3172499456 ; 0x0e8: USEC=0x0 MSEC=0x220 SECS=0x11 MINS=0x2f

kfdhdb.vfstart:                     128 ; 0x0ec: 0x00000080

kfdhdb.vfend:                       160 ; 0x0f0: 0x000000a0

kfdhdb.spfile:                        0 ; 0x0f4: 0x00000000

kfdhdb.spfflg:                        0 ; 0x0f8: 0x00000000

kfdhdb.ub4spare[0]:                   0 ; 0x0fc: 0x00000000

kfdhdb.ub4spare[1]:                   0 ; 0x100: 0x00000000

kfdhdb.ub4spare[2]:                   0 ; 0x104: 0x00000000

kfdhdb.ub4spare[3]:                   0 ; 0x108: 0x00000000

kfdhdb.ub4spare[4]:                   0 ; 0x10c: 0x00000000

kfdhdb.ub4spare[5]:                   0 ; 0x110: 0x00000000

kfdhdb.ub4spare[6]:                   0 ; 0x114: 0x00000000

kfdhdb.ub4spare[7]:                   0 ; 0x118: 0x00000000

kfdhdb.ub4spare[8]:                   0 ; 0x11c: 0x00000000

kfdhdb.ub4spare[9]:                   0 ; 0x120: 0x00000000

kfdhdb.ub4spare[10]:                  0 ; 0x124: 0x00000000

kfdhdb.ub4spare[11]:                  0 ; 0x128: 0x00000000

kfdhdb.ub4spare[12]:                  0 ; 0x12c: 0x00000000

kfdhdb.ub4spare[13]:                  0 ; 0x130: 0x00000000

kfdhdb.ub4spare[14]:                  0 ; 0x134: 0x00000000

kfdhdb.ub4spare[15]:                  0 ; 0x138: 0x00000000

kfdhdb.ub4spare[16]:                  0 ; 0x13c: 0x00000000

kfdhdb.ub4spare[17]:                  0 ; 0x140: 0x00000000

kfdhdb.ub4spare[18]:                  0 ; 0x144: 0x00000000

kfdhdb.ub4spare[19]:                  0 ; 0x148: 0x00000000

kfdhdb.ub4spare[20]:                  0 ; 0x14c: 0x00000000

kfdhdb.ub4spare[21]:                  0 ; 0x150: 0x00000000

kfdhdb.ub4spare[22]:                  0 ; 0x154: 0x00000000

kfdhdb.ub4spare[23]:                  0 ; 0x158: 0x00000000

kfdhdb.ub4spare[24]:                  0 ; 0x15c: 0x00000000

kfdhdb.ub4spare[25]:                  0 ; 0x160: 0x00000000

kfdhdb.ub4spare[26]:                  0 ; 0x164: 0x00000000

kfdhdb.ub4spare[27]:                  0 ; 0x168: 0x00000000

kfdhdb.ub4spare[28]:                  0 ; 0x16c: 0x00000000

kfdhdb.ub4spare[29]:                  0 ; 0x170: 0x00000000

kfdhdb.ub4spare[30]:                  0 ; 0x174: 0x00000000

kfdhdb.ub4spare[31]:                  0 ; 0x178: 0x00000000

kfdhdb.ub4spare[32]:                  0 ; 0x17c: 0x00000000

kfdhdb.ub4spare[33]:                  0 ; 0x180: 0x00000000

kfdhdb.ub4spare[34]:                  0 ; 0x184: 0x00000000

kfdhdb.ub4spare[35]:                  0 ; 0x188: 0x00000000

kfdhdb.ub4spare[36]:                  0 ; 0x18c: 0x00000000

kfdhdb.ub4spare[37]:                  0 ; 0x190: 0x00000000

kfdhdb.ub4spare[38]:                  0 ; 0x194: 0x00000000

kfdhdb.ub4spare[39]:                  0 ; 0x198: 0x00000000

kfdhdb.ub4spare[40]:                  0 ; 0x19c: 0x00000000

kfdhdb.ub4spare[41]:                  0 ; 0x1a0: 0x00000000

kfdhdb.ub4spare[42]:                  0 ; 0x1a4: 0x00000000

kfdhdb.ub4spare[43]:                  0 ; 0x1a8: 0x00000000

kfdhdb.ub4spare[44]:                  0 ; 0x1ac: 0x00000000

kfdhdb.ub4spare[45]:                  0 ; 0x1b0: 0x00000000

kfdhdb.ub4spare[46]:                  0 ; 0x1b4: 0x00000000

kfdhdb.ub4spare[47]:                  0 ; 0x1b8: 0x00000000

kfdhdb.ub4spare[48]:                  0 ; 0x1bc: 0x00000000

kfdhdb.ub4spare[49]:                  0 ; 0x1c0: 0x00000000

kfdhdb.ub4spare[50]:                  0 ; 0x1c4: 0x00000000

kfdhdb.ub4spare[51]:                  0 ; 0x1c8: 0x00000000

kfdhdb.ub4spare[52]:                  0 ; 0x1cc: 0x00000000

kfdhdb.ub4spare[53]:                  0 ; 0x1d0: 0x00000000

kfdhdb.acdb.aba.seq:                  0 ; 0x1d4: 0x00000000

kfdhdb.acdb.aba.blk:                  0 ; 0x1d8: 0x00000000

kfdhdb.acdb.ents:                     0 ; 0x1dc: 0x0000

kfdhdb.acdb.ub2spare:                 0 ; 0x1de: 0x0000

kfed tool 로 헤더정보를 읽었을 때 header 정보가 깨지거나 corrupt 되었을 경우, 일부 spare 컬럼 값이 0 이 아닌 다른 값으로 표현된다.

kfdhdb.acdb.ub2spare:     43605 ; 0x1de: 0xaa55    

kfdhdb.ub4spare[39]       3465798583 ; 0x198: 0xce93e3b7 

- 원인

1. MBR 파티션 영역의 boot signature 가 다른값으로 표현되었을시 (human error로 파티션 작업시 잘못 건드리거나)

2. 3rd party 솔루션 (netbackup 같은것들)을 이용하여 백업시 디스크 헤더영역을 건드려서 손상되었을 가능성

- 해결책

1. ASM Instance 를 내림

2. dd if=dev/ocrvote01  of=/tmp/DATA.dd bs=1M count=10 (해당 디스크를 백업받음)

3. AU(Allocation Unit) size 를 확인함

[oracle:/home/oracle]#kfed read  /dev/ocrvote01 |grep ausize

kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000

4. 복구함

kfed repair /dev/ocrvote01 aus=1048576

5. ASM Instance 올림

6. 유의점

해당 Case 는 해당 디바이스의 (0xaa55 on 510th location on device header ) 510 번째 2byte에 문제가 있을 경우 복구할수 있는 방법이었음.

ASM 이 올라오지 않는 경우는 다양하기 때문에 절대 맹신하지 말것.

SNMP subscriber 환경셋업시 ASR 추가
SNMP subscriber 환경셋업시 Grid Control 추가

확인

ellcli -e "list cell attributes snmpsubscriber"

추가

cellcli -e "alter cell snmpsubscriber=((host='x.x.x.1',port=3887,community=public),(host='x.x.x.2',port=3887,community=public),(host='x.x.x.x',port=162,community=public,type=asr))"

dcli -g cell_group -l celladmin -n cellcli -e "alter cell snmpsubscriber= \(\(host=\'x.x.x.1\',port=3887,community=public,,\),\(host=\'x.x.x.2\',port=3887,community=public,\),\(host=\'x.x.x.x\',port=162,community=public,type=asr,\)\)"

RHEL6, OL6 SUSE 11 and UEK2 kernel 부터 해당

Disabling Transparent HugePages

Transparent HugePages 설정으로 인해 메모리가 동적 할당되어 메모리할당에 되려 delay 를 주어 성능에 영향을 미칠수 있으므로 오라클에서는 disable 권고함.

확인방법 :

RHEL kernel

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled

다른 kernel

cat /sys/kernel/mm/transparent_hugepage/enabled

결과 :  3가지 값중 always 에 괄호가 쳐져 있으면 enable 되어있는 것임.

[always] madvise never

조치방법 : 붉은색 라인 추가

vi /etc/grub.conf (RHEL7.2인 경우 /boot/grub2/grub.cfg)

[root:/root]#vi /etc/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /boot/, eg.
#          root (hd0,0)
#          kernel /vmlinuz-version ro root=/dev/cciss/c0d0p5
#          initrd /initrd-[generic-]version.img
#boot=/dev/cciss/c0d0
default=0
timeout=5
splashimage=(hd0,0)/grub/splash.xpm.gz
hiddenmenu
title Red Hat Enterprise Linux Server (2.6.32-573.26.1.el6.x86_64)
        root (hd0,0)
        kernel /vmlinuz-2.6.32-573.26.1.el6.x86_64 ro root=UUID=aa061763-d563-4ba1-b0bf-af05357dd7aa rd_NO_LUKS rd_NO_LVM  LANG=en_US.UTF-        8 rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=auto  KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM rhgb  

        quiet  transparent_hugepage=never
        initrd /initramfs-2.6.32-573.26.1.el6.x86_64.img

추가후 리부팅..

부팅후에

cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

cat /proc/meminfo

AnonHugePages:   0 인지 확인완료

RAC 설치시 root.sh 가 호출하는 스크립트 리스트.

<GRID_HOME>/install/utl/rootmacro.sh                  # small - validates home and user
<GRID_HOME>/install/utl/rootinstall.sh                  # small - creates some local files
<GRID_HOME>/network/install/sqlnet/setowner.sh   # small - opens up /tmp permissions
<GRID_HOME>/rdbms/install/rootadd_rdbms.sh      # small - misc file/permission checks
<GRID_HOME>/rdbms/install/rootadd_filemap.sh    # small - misc file/permission checks
<GRID_HOME>/crs/install/rootcrs.pl                     # MAIN CLUSTERWARE CONFIG SCRIPT

alter system switch logfile vs alter archive log current

log switch 해주는 작업은 같지만 동작 방식은 다른 두가지 명령어...

1. SQL> alter system switch logfile ;

redo switch 를 ARCH 프로세스가 진행해주며, Backgroud 로 수행되지 때문에 명령어 끝나고 바로 제어권을 가져옴.

2. SQL> alter archive log current;

redo switch 를 서버 프로세스가 진행해주며, Backgroud 로 수행되지 않기 때문에 switch 완료 될때 까지 기다림.

모든 thread 에 대해 수행을 할수 있기 때문에 RAC 환경에서 thread별로 unarchive 된 놈들에 대해 유용하게 쓰일수 있다.

ex) alter archive log thread 2 current;

개념이 이해 되었으면 Hot backup,rman 스크립트 백업시 alter system switch logfile 후 alter archive log current 를 추가해서 쓰면 좋겠다.

RAC 의 RESOURCE 에 application vip 를 부여하여 failover 하게 활용할수 있다. apache, ogg 등등

구성은 간단하지만 application에 따라 작동방식, failover 등을 고려한다면 추가적인 script 작성 등 손 볼 부분은 많다..

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

현재 /etc/hosts 화일

#### Public ######
192.168.56.50   rac1
192.168.56.51   rac2

#### Virtual ######
192.168.56.52   rac1-vip
192.168.56.53   rac2-vip

#### Private ######
10.10.10.1      rac1-priv
10.10.10.2      rac2-priv

#### SCAN ######
192.168.56.54   rac-scan

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

1. 생성
[RAC1]root@rac1:/root# appvipcfg create -network=1 -ip=192.168.56.55 -vipname=appsvip -user=root

2. 시작

[RAC1]root@rac1:/root# crsctl start resource appsvip
CRS-2672: Attempting to start 'appsvip' on 'rac2'
CRS-2676: Start of 'appsvip' on 'rac2' succeeded

노드명을 안 주면 2번에서 뜬다. 다시..

[RAC1]root@rac1:/root# crsctl start resource appsvip -n rac1
CRS-2672: Attempting to start 'appsvip' on 'rac1'
CRS-2676: Start of 'appsvip' on 'rac1' succeeded

3. 확인

ifconfig 해보면

eth0:2    Link encap:Ethernet  HWaddr 08:00:27:BF:04:20 
          inet addr:192.168.56.55  Bcast:192.168.56.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

appsvip
      1        ONLINE  ONLINE       rac2                     STABLE
appsvip2
      1        ONLINE  ONLINE       rac1                     STABLE

지울때는..

[RAC1]root@rac1:/root# crsctl stop res appsvip -f

[RAC1]root@rac1:/root# appvipcfg delete -vipname=appsvip

2개도 가능함..활용하기 나름일듯..

appvipcfg create -network=1 -ip=192.168.56.55 -vipname=appsvip -user=root
appvipcfg create -network=1 -ip=192.168.56.56 -vipname=appsvip2 -user=root

default 로 만들면 속성이 거의 정의되어 있지 않다.

crsctl stat res -p

NAME=appsvip
TYPE=app.appvipx.type
ACL=owner:root:rwx,pgrp:root:r-x,other::r--,user:root:r-x
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
APPSVIP_FAILBACK=0
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=0
CLEAN_TIMEOUT=60
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Application VIP
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
GEN_USR_ORA_STATIC_VIP=
GEN_USR_ORA_VIP=
HOSTING_MEMBERS=
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=balanced
RELOCATE_BY_DEPENDENCY=1
RESTART_ATTEMPTS=0
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=*
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network)
START_TIMEOUT=0
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(ora.net1.network)
STOP_TIMEOUT=0
TYPE_VERSION=1.1
UPTIME_THRESHOLD=7d
USER_WORKLOAD=no
USE_STICKINESS=0
USR_ORA_ENV=
USR_ORA_VIP=192.168.56.55
VERSION=12.1.0.1.0

Application VIP on second network ora.net2.network missing after addnode (문서 ID 1617281.1)에 근거

아래와 같이 필요한 속성값을 변경해 주면 되겠다.

crsctl modify resource appsvip -attr "RESTART_ATTEMPTS=2,START_TIMEOUT=100,STOP_TIMEOUT=100,CHECK_INTERVAL=10,USR_ORA_VIP=192.168.56.55,START_DEPENDENCIES='hard(ora.net1.network)pullup(ora.net1.network)',STOP_DEPENDENCIES='hard(ora.net1.network)',HOSTING_MEMBERS=rac1"

crsctl query 명령어 확인, crs sw version 및 patch 번호 등을 간단히 확인 할수 있다.

[TEST1]oracle@rac1:/home/oracle# crsctl query crs autostart
'Autostart delay':       0
'Autostart servercount': 1

[TEST1]oracle@rac1:/home/oracle# crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [12.1.0.2.0]

[TEST1]oracle@rac1:/home/oracle# crsctl query crs softwareversion
Oracle Clusterware version on node [rac1] is [12.1.0.2.0]

[TEST1]oracle@rac1:/home/oracle# crsctl query crs releasepatch
Oracle Clusterware release patch level is [2039526626] and the complete list of patches [19769480 20299023 20831110 21359755 21436941 21948354 22291127 22502518 22502555 ] have been applied on the local node.

[TEST1]oracle@rac1:/home/oracle# crsctl query crs softwarepatch
Oracle Clusterware patch level on node rac1 is [2039526626].

RAC DB의 INSTSNCE_NAME 및 DB_NAME 을 변경해 보자.

Single 과 크게 다를것은 없다. nid 를 이용하여 변경하면 되고 cluter_database=false 후 parameter 재설정 작업만 해주면 된다. 그외 리소스 삭제후 추가작업

-- 먼저 INSTANCE 이름을 바꾸어 본다.

현재 RAC1,RAC2 를 TEST1,TEST2 로 바꾸자

1. 현재 parameter file 확인

------------------------------------ ---------------------- ------------------------------
spfile                               string                 +DATA/RAC/PARAMETERFILE/spfile
                                                            .270.910889297
2. 현재 Instance 이름 확인

sys@RAC1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 RAC1

sys@RAC1> alter system set instance_name=TEST1 scope=spfile sid='RAC1';

System altered.

Elapsed: 00:00:00.16

3. 재기동 한후 확인, 별거없다. 바뀌었다.

sys@RAC1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 TEST1

sys@RAC1> show parameter db_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_name                              string                 RAC
sys@RAC1> show parameter db_unique

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 RAC

sys@RAC1> SELECT instance FROM v$thread;

INSTANCE
-------------------------------------------------------------------------------------------
RAC1
RAC2

2번 노드도 동일하게 작업한다.

sys@RAC2> alter system set instance_name=TEST2 scope=spfile sid='RAC2';

.....

작업후 변경확인..잘된다.

-- 이제 DB_NAME 도 바꾸어 보겠다.

1. pfile 생성 후 내용수정

SQL> create pfile from spfile ;

생성된 pfile 을 수정해서 RAC1,RAC2 로 된 부분을 모두 TEST1,2 로 변경한다.

RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# vi initTEST1.ora
TEST1.__data_transfer_cache_size=0
TEST2.__data_transfer_cache_size=0
TEST1.__db_cache_size=83886080
TEST2.__db_cache_size=130023424
TEST1.__java_pool_size=4194304
TEST2.__java_pool_size=4194304
TEST1.__large_pool_size=4194304
TEST2.__large_pool_size=4194304
TEST1.__oracle_base='/u01/app/oracle'#OTESTLE_BASE set from environment
TEST2.__oracle_base='/u01/app/oracle'#OTESTLE_BASE set from environment
TEST1.__pga_aggregate_target=104857600
TEST2.__pga_aggregate_target=104857600
TEST1.__sga_target=419430400
TEST2.__sga_target=419430400
TEST1.__shared_io_pool_size=8388608
TEST2.__shared_io_pool_size=12582912
TEST1.__shared_pool_size=310378496
TEST2.__shared_pool_size=260046848
TEST1.__streams_pool_size=0
TEST2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
*.audit_sys_operations=FALSE
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='12.1.0'
*.control_files='+DATA/rac/controlfile/current.256.907339295','+RECO/rac/controlfile/current.256.907339297'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='TEST'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
TEST2.instance_number=2
TEST1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sga_target=419430400
TEST2.thread=2
TEST1.thread=1
TEST2.undo_tablespace='UNDOTBS2'
TEST1.undo_tablespace='UNDOTBS1'

2. password 화일 복사, adump directory 생성, 기존 initRAC1 화일 백업

[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# mkdir -p /u01/app/oracle/admin/TEST/adump
[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cp orapwRAC1 orapwTEST1
[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cp initRAC1.ora  initRAC1.ora.old

3. 양쪽 모두 내린다.

srvctl stop database -d RAC

4. exclusive 로 1번 startup

idle> startup mount exclusive pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initRAC1.ora

ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.

5. nid 로 변경

[RAC1]oracle@rac1:/home/oracle# nid target=sys/oracle dbname=TEST setname=yes

DBNEWID: Release 12.1.0.2.0 - Production on Tue May 17 10:56:34 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to database RAC (DBID=2495321304)

Connected to server version 12.1.0

Control Files in database:
    +DATA/rac/controlfile/current.256.907339295
    +RECO/rac/controlfile/current.256.907339297

Change database name of database RAC to TEST? (Y/[N]) => y

Proceeding with operation
Changing database name from RAC to TEST
    Control File +DATA/rac/controlfile/current.256.907339295 - modified
    Control File +RECO/rac/controlfile/current.256.907339297 - modified
    Datafile +DATA/rac/datafile/system.259.90733933 - wrote new name
    Datafile +DATA/rac/datafile/sysaux.260.90733937 - wrote new name
    Datafile +DATA/rac/datafile/undotbs1.261.90733940 - wrote new name
    Datafile +DATA/rac/datafile/undotbs2.263.90733943 - wrote new name
    Datafile +DATA/rac/datafile/users.264.90733943 - wrote new name
    Datafile +DATA/rac/tempfile/temp.262.90733941 - wrote new name
    Control File +DATA/rac/controlfile/current.256.907339295 - wrote new name
    Control File +RECO/rac/controlfile/current.256.907339297 - wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

6. profile의 환경변수 TEST로 변경, cluster_database=false 로 하고 pfile로 기동,

*.cluster_database=false
 
idle> startup pfile=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initTEST1.ora;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

7. spfile 을 맹글어 준다.

idle> create spfile='+DATA' from pfile;

File created.

asmcmd -p 로 +ASM1 접속, 화일 확인

Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.271.912078483
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.272.912079201
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.273.912080113

8. pfile에 spfile 경로를 적어준다.

[TEST1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# vi initTEST1.ora
SPFILE='+DATA/TEST/PARAMETERFILE/spfile.273.912080113'

7. startup 한후에 다시 내린다.

idle> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

9. 2번 노드도 nid 실행하는거 빼고 나머지 절차를 동일하게 진행한다. 잘된다.

10. cluster_database=true 로 변경후 재기동

SQL> alter system set cluster_database=true scope=spfile sid='*';

11. CRS RESOURCE에서 바뀐 INSTANCE 를 인식못하니 기존것 삭제후 새로 등록한다.

ora.rac.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
12. 기존꺼 삭제 (쫄지 말자, 이건 DB에 아무런 영향을 주지 않는다)

[TEST1]oracle@rac1:/home/oracle# srvctl remove instance -d RAC -i RAC1
Remove instance from the database RAC? (y/[n]) y
[TEST1]oracle@rac1:/home/oracle# srvctl remove instance -d RAC -i RAC2
Remove instance from the database RAC? (y/[n]) y
[TEST1]oracle@rac1:/home/oracle# srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y

13. 새로운거 추가

[TEST1]oracle@rac1:/home/oracle# srvctl add database -d TEST -o /u01/app/oracle/product/12.1.0.2/dbhome_1

[TEST1]oracle@rac1:/home/oracle# srvctl add instance -d TEST -i TEST1 -n rac1
[TEST1]oracle@rac1:/home/oracle# srvctl add instance -d TEST -i TEST2 -n rac2

14. RESOURCE에서 바로 인식 못할수 있으니 깔끔하게 crsctl stop crs 해서 다시 start 해준다.

15. 정상 등록 확인

ora.test.db
      1        ONLINE  ONLINE       rac1                     Open,STABLE
      2        ONLINE  ONLINE       rac2                     Open,STABLE

16. 적용된 값들 확인

sys@TEST1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 TEST1
sys@TEST1> show parameter db_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_name                              string                 TEST
sys@TEST1> show parameter db_unique

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 TEST

17. 작업후기

RAC 도 별거 없이 잘 바뀌며, 작업시 parameter 백업, cluster_database=true,false 만 manage 잘하면 하면 OK ~~

SEVICE_NAME 등에 변경내역 반영, 접속 확인할 것.

windows 에서는 oracle 이 thread 방식으로 구동되기 때문에  작업관리자 등으로 process 를 살펴볼려고 해도 ORACLE.EXE 하나만 보여서 정확한 정보

를 파악할수 없는  경우가 많다. 떠 있으면 그냥 아 살아있네 하는 정도.?

services.msc 를 실행하여 개별 서비스 상태를 살펴볼수도 있지만 간혹 맛탱이가 가 있는 경우가 있어서 믿음이 가지 않는다.

유닉스의 ps -ef |grep ora_ 처럼 비스무리하게  process 를 자세하게 살펴볼수 있는데 윈도우즈의 sc 명령어를 활용하는 방법이다.

1. C:\Windows\System32>sc query state= all | findstr SERVICE_NAME | findstr Oracle

SERVICE_NAME: OracleDBConsoleorcl
SERVICE_NAME: OracleJobSchedulerORCL
SERVICE_NAME: OracleJobSchedulerXXX
SERVICE_NAME: OracleMTSRecoveryService
SERVICE_NAME: OracleOraDb11g_home1ClrAgent
SERVICE_NAME: OracleOraDb11g_home1TNSListener
SERVICE_NAME: OracleOraDb11g_home2ClrAgent
SERVICE_NAME: OracleOraDb11g_home2TNSListener
SERVICE_NAME: OracleServiceORCL
SERVICE_NAME: OracleServiceXXX
SERVICE_NAME: OracleVssWriterORCL
SERVICE_NAME: OracleVssWriterXXX

2. C:\Windows\System32>sc query OracleServiceORCL

SERVICE_NAME: OracleServiceORCL
        종류               : 10  WIN32_OWN_PROCESS
        상태              : 1  STOPPED
        WIN32_EXIT_CODE    : 1077  (0x435)
        SERVICE_EXIT_CODE  : 0  (0x0)
        검사점         : 0x0
        WAIT_HINT          : 0x0

3. C:\Windows\System32>sc qc OracleServiceORCL

[SC] QueryServiceConfig 성공

SERVICE_NAME: OracleServiceORCL
        종류               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 3   DEMAND_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : f:\app\oracle\product\11.2.0\dbhome\bin\ORACLE.EXE ORCL
        LOAD_ORDER_GROUP   :
        태그                : 0
        DISPLAY_NAME       : OracleServiceORCL
        종속성       :
        SERVICE_START_NAME : LocalSystem

12c Real-Time SQL Monitoring 조회

dbms_sql_monitor package 이용

(전제조건 : Tuning Pack 사용이 가능하여야 함)

- REAL TIME SQL 조회

set trimspool on
set trim on
set pagesize 0
set linesize 32767
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sql_monitor.report_sql_monitor(type=>'active')
from dual;
spool off

- 특정 SQL_ID 로 조회

set trimspool on
set trim on
set pagesize 0
set linesize 32767
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sql_monitor.report_sql_monitor(
sql_id=>' 1n482vfrxw014', type=>'active')
from dual;
spool off