2016. 8. 25. 11:44 오라클
해외 사이트에 Oracle Database 12cR2 정식버전 출시 지연에 대한 루머가 있네요..
해당 기사는 2016년 6월에 나온것임..
- 정리하면..
2016년 9월 Open World 행사에서 12cR2 정식버전을 발표하지 않을까? 싶은데 하지만 on-premise 버전은 당장 사용할수 없고 아마도 Cloud 버전만 사용할수 있을것 같다.
왜냐? -> Cloud 로의 사용전환을 위한 일종의 Cloud Sales 전략으로 보임..
Oracle 12cR2 Beta 프로그램 접속해 보니 2016,8.10일에 Beta3 가 Release 되었네요..거의 막바지 인듯..
열심히 해라 개발자들~~
2016. 8. 5. 16:49 오라클
RMAN 화일 복구시에 Reanme 하는 경우가 있는데 여러가지 값이 있으며 우선순위가 존재한다.
TSPITR (Tablespace Point-In-Time-Recovery) 시에 Rename 하는 방법들의 우선순위
1. SET NEWNAME
2. CONFIGURE AUXNAME
3. DB_FILE_NAME_CONVERT
4. AUXILIARY DESTINATION argument to RECOVER TABLESPAC
Target, Auxiliary 양쪽에 값이 적용이 되어 있을시 제일위에 설정된 값이 아래값들을 override 해 버린다.
2016. 8. 1. 17:19 오라클
현재구성은 RAC -> single 로 Dataguard 구성했음.
1번노드 : rac1
2번노드 : rac2
standby 노드 : test2
이상없이 구성한거 같은데 alertlog에 계속적으로 ORA-16191 계속뜰때..
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
Solution :
1. 1번에서 redo 전송 중지
SQL>alter system set log_archive_dest_state_2=DEFER sid='*' scope=both;
2. 1번 노드에서 passwd file 재생성
orapwd file=$ORACLE_HOME/dbs/orapwRAC1 password=oracle entries=10
3. 2번 노드 및 스탠바이 서버에 복사
scp orapwRAC1 rac2:$ORACLE_HOME/dbs/orapwRAC2
scp orapwRAC1 single2:$ORACLE_HOME/dbs/orapwTEST2
4. 1번에서 redo 전송 재개
SQL>alter system set log_archive_dest_state_2=ENABLE sid='*' scope=both;
5. log switch
SQL>alter system switch all logfile;
6. 잘되는지 alert 로그확인, 이상 없이 잘 되는구먼..
2016. 7. 29. 14:51 오라클
옛날꺼 9i 설치화일 찾아보다가 화일 이름이 구분이 잘 안되어 있어 기록해 놓는다.
해당 URL은 지금가도 어차피 down 안됨. 개인적으로 가지고 있는 것들..
Oracle9i Database Release 2 Enterprise/Standard/Personal Edition for Windows NT/2000/XP
http://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk1.zip
http://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk2.zip
http://download.oracle.com/otn/nt/oracle9i/9201/92010NT_Disk3.zip
Oracle9i Database Release 2 Enterprise/Standard/Personal/Client Edition for Windows XP 2003/Windows Server 2003 (64-bit)
http://download.oracle.com/otn/nt/oracle9i/9202/92021Win64_Disk1.zip
http://download.oracle.com/otn/nt/oracle9i/9202/92021Win64_Disk2.zip
Oracle9i Database Release 2 Enterprise/Standard Edition for Intel Linux
http://download.oracle.com/otn/linux/oracle9i/9204/ship_9204_linux_disk1.cpio.gz
http://download.oracle.com/otn/linux/oracle9i/9204/ship_9204_linux_disk2.cpio.gz
http://download.oracle.com/otn/linux/oracle9i/9204/ship_9204_linux_disk3.cpio.gz
Oracle9i Database Release 2 (9.2.0.4) Enterprise/Standard Edition for Linux x86-64
http://download.oracle.com/otn/linux/oracle9i/9204/amd64_db_9204_Disk1.cpio.gz
http://download.oracle.com/otn/linux/oracle9i/9204/amd64_db_9204_Disk2.cpio.gz
http://download.oracle.com/otn/linux/oracle9i/9204/amd64_db_9204_Disk3.cpio.gz
Oracle9i Database Release 2 Enterprise/Standard Edition for AIX - Based 4.3.3 Systems (64-bit)
http://download.oracle.com/otn/aix/oracle9i/9201/server_9201_AIX64_Disk1.cpio.gz
http://download.oracle.com/otn/aix/oracle9i/9201/server_9201_AIX64_Disk2.cpio.gz
http://download.oracle.com/otn/aix/oracle9i/9201/server_9201_AIX64_Disk3.cpio.gz
http://download.oracle.com/otn/aix/oracle9i/9201/server_9201_AIX64_Disk4.cpio.gz
Oracle9i Database Release 2 Enterprise/Standard Edition for AIX- Based 5L Systems
http://download.oracle.com/otn/aix/oracle9i/9201/A99331-01.zip
http://download.oracle.com/otn/aix/oracle9i/9201/A99331-02.zip
http://download.oracle.com/otn/aix/oracle9i/9201/A99331-03.zip
http://download.oracle.com/otn/aix/oracle9i/9201/A99331-04.zip
Oracle9i Database Release 2 Enterprise/Standard Edition for Sun SPARC Solaris (32-bit)
http://download.oracle.com/otn/solaris/oracle9i/9201/92010Sol_Disk1.cpio.gz
http://download.oracle.com/otn/solaris/oracle9i/9201/92010Sol_Disk2.cpio.gz
http://download.oracle.com/otn/solaris/oracle9i/9201/92010Sol_Disk3.cpio.gz
Oracle9i Database Release 2 Enterprise/Standard Edition for Sun SPARC Solaris (64-bit)
http://download.oracle.com/otn/solaris/oracle9i64/9201/solaris64_9.2.0.1.0.Disk1.cpio.gz
http://download.oracle.com/otn/solaris/oracle9i64/9201/solaris64_9.2.0.1.0.Disk2.cpio.gz
http://download.oracle.com/otn/solaris/oracle9i64/9201/solaris64_9.2.0.1.0.Disk3.cpio.gz
2016. 7. 28. 16:12 오라클
휴지통에서 원하는 날짜만큼 명령어로 딱딱 지우는 명령어는 없고 명령어로 조건을 줘서 스크립트를 뽑아내는 방법이 있긴 하다.
1. 7일꺼만 남기고 다 지우기
spool purge_table_older_than_7_days.sql
select 'purge table '||owner||'."'||OBJECT_NAME||'";'
from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-7;
spool off;
2. 5분 보다 오래된거 다 지우기
spool purge_table_older_than_5_minutes.sql
select 'purge table '||owner||'."'||OBJECT_NAME||'";'
from dba_recyclebin where type='TABLE' and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(5/(24*60));
spool off;
spool purge_table_older_than_5_min.txt
@purge_table_older_than_5_minutes.sql
spool off;
------------------------------------------------------------
나머지 조건들은 필요에 따라 수정해서 상황에 맞게 쓰면 될듯.
2016. 7. 8. 10:28 오라클
ASM 디스크 그룹에 sector size 지정방법
기본값은 512 임.
단, 최초 생성시에만 지정할수 있다. 중간에 alter로 못 바꿈.
=============================
create diskgroup DATA EXTERNAL REDUNDANCY
DISK '/dev/asm1',
'/dev/asm2',
'/dev/asm3',
'/dev/asm4'
ATTRIBUTE 'compatible.asm'='11.2', 'compatible.rdbms'='11.2',
'au_size'='4M', 'sector_size='4096';
또한 SSD 사용시 Redo 로그 생성을 아래와 같이 해 주기도 한다. (성능향상을 위해)
Oracle Redo 기본값이 512 인데 SSD 의 기본 섹터 size 가 4K 이므로 맞춰준다.
1. 파라메터 반영
ALTER SYSTEM SET "_DISK_SECTOR_SIZE_OVERRIDE"="TRUE" ;
2. Redo 생성
ALTER DATABASE ADD LOGFILE GROUP 5 SIZE 100M BLOCKSIZE 4096;
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 이 올라오지 않는 경우는 다양하기 때문에 절대 맹신하지 말것.
2016. 7. 7. 12:39 오라클
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,\)\)"
2016. 6. 28. 13:21 오라클
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 인지 확인완료
2016. 6. 17. 11:42 오라클
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
