'ORACLE/ADMIN' 카테고리의 글 목록 (3 Page)

'ORACLE/ADMIN'에 해당되는 글 169건

2023.06.09 오라클 ASM diskgroup 생성 및 disk 추가 방법
2023.06.08 ORACLE ASM DISK 삭제(DROP) 정리
2023.06.02 oracle restart 기동 절차
2023.05.25 ORACLE ASM DISKGROUP 삭제
2023.05.25 오라클 asm disk 그룹 추가 시 설정 ASM AFD
2022.09.15 ORACLE SEHA 인스턴스 이동
2022.09.15 ORACLE SEHA LISTENER설정 및 상태 체크
2022.05.22 RAC DB삭제
2022.05.22 오라클 메모리 튜닝 권고
2018.08.08 oracle ERROR: ORA-12547: TNS:lost contact

오라클 ASM diskgroup 생성 및 disk 추가 방법

RAC 기준으로 설명

오라클 ASM diskgroup 생성 및 disk 추가

※ 선행 작업으로 disk 쪼개고 공유 설정 후 서버에 할당 해줌

여기서는 버추얼 박스로 총 ASM01~05까지 5개를 만들고 서버마다 할당 해줌

-- 쪼개기

H:\Vbox\storage>vboxmanage createmedium --filename ASM01.vdi --size 100 --format VDI --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Medium created. UUID: 519461cf-729c-418f-af0f-0fede3ed646b

H:\Vbox\storage>vboxmanage createmedium --filename ASM02.vdi --size 100 --format VDI --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Medium created. UUID: 3715751b-9226-40b2-95e9-f68698b16a63

H:\Vbox\storage>vboxmanage createmedium --filename ASM03.vdi --size 100 --format VDI --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Medium created. UUID: 1e423ac5-6474-4617-a6e8-8e7b6270593e

H:\Vbox\storage>vboxmanage createmedium --filename ASM04.vdi --size 100 --format VDI --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Medium created. UUID: 361e559f-1efe-4e95-ad13-9ad007149583

H:\Vbox\storage>vboxmanage createmedium --filename ASM05.vdi --size 100 --format VDI --variant Fixed
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Medium created. UUID: a8432efa-251b-4f1f-8a05-b99d57fbd009

-- 버추얼 박스 관리자에서 각각의 서버에 공유 볼륨을 똑 같은 순서로 할당

-- 공유 디바이스로 설정
H:\Vbox\storage>vboxmanage modifymedium ASM01.vdi --type shareable

H:\Vbox\storage>vboxmanage modifymedium ASM02.vdi --type shareable

H:\Vbox\storage>vboxmanage modifymedium ASM03.vdi --type shareable

H:\Vbox\storage>vboxmanage modifymedium ASM04.vdi --type shareable

H:\Vbox\storage>vboxmanage modifymedium ASM05.vdi --type shareable

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

-- 추가한 disk 확인

[root@rac1 dev]# ls -al sd*
brw-rw----. 1 root disk 8,   0  6월  8 16:11 sda
brw-rw----. 1 root disk 8,   1  6월  8 16:11 sda1
brw-rw----. 1 root disk 8,   2  6월  8 16:11 sda2
brw-rw----. 1 root disk 8,  16  6월  8 16:11 sdb
brw-rw----. 1 root disk 8,  17  6월  8 16:11 sdb1
brw-rw----. 1 root disk 8,  32  6월  8 16:11 sdc
brw-rw----. 1 root disk 8,  33  6월  8 16:11 sdc1
brw-rw----. 1 root disk 8,  48  6월  8 16:11 sdd
brw-rw----. 1 root disk 8,  49  6월  8 16:11 sdd1
brw-rw----. 1 root disk 8,  64  6월  8 16:11 sde
brw-rw----. 1 root disk 8,  65  6월  8 16:11 sde1
brw-rw----. 1 root disk 8,  80  6월  8 16:11 sdf
brw-rw----. 1 root disk 8,  81  6월  8 16:11 sdf1
brw-rw----. 1 root disk 8,  96  6월  8 16:11 sdg
brw-rw----. 1 root disk 8,  97  6월  8 16:11 sdg1
brw-rw----. 1 root disk 8, 112  6월  8 16:16 sdh
brw-rw----. 1 root disk 8, 113  6월  8 17:19 sdh1
brw-rw----. 1 root disk 8, 128  6월  8 16:11 sdi
brw-rw----. 1 root disk 8, 144  6월  8 16:11 sdj
brw-rw----. 1 root disk 8, 160  6월  8 16:11 sdk
brw-rw----. 1 root disk 8, 176  6월  8 16:11 sdl
brw-rw----. 1 root disk 8, 192  6월  8 16:11 sdm

-- 파티션 생성
[root@rac1 dev]# fdisk /dev/sdi
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x1763f317.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-204799, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799):
Using default value 204799
Partition 1 of type Linux and of size 99 MiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# fdisk /dev/sdj
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x697410e9.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-204799, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799):
Using default value 204799
Partition 1 of type Linux and of size 99 MiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# fdisk /dev/sdk
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x0dde1be5.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-204799, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799):
Using default value 204799
Partition 1 of type Linux and of size 99 MiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# fdisk /dev/sdl
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0xd45bb1c8.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-204799, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799):
Using default value 204799
Partition 1 of type Linux and of size 99 MiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@rac1 dev]# fdisk /dev/sdm
Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table
Building a new DOS disklabel with disk identifier 0x57f6bcbe.

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p):
Using default response p
Partition number (1-4, default 1):
First sector (2048-204799, default 2048):
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-204799, default 204799):
Using default value 204799
Partition 1 of type Linux and of size 99 MiB is set

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
-- ASM disk 등록
oracleasm createdisk asm01 /dev/sdi1
oracleasm createdisk asm02 /dev/sdj1
oracleasm createdisk asm03 /dev/sdk1
oracleasm createdisk asm04 /dev/sdl1
oracleasm createdisk asm05 /dev/sdm1

-- 그리드 계정에서 접속
sqlplus / as sysasm

set line 200
col path for a30
select group_number, mount_status, path, total_mb
from v$asm_disk where mount_status='CLOSED';

GROUP_NUMBER MOUNT_STATUS          PATH TOTAL_MB
------------ --------------------- ------------------------------ ----------
           0 CLOSED                /dev/oracleasm/disks/ASM03              0
           0 CLOSED                /dev/oracleasm/disks/ASM01              0
           0 CLOSED                /dev/oracleasm/disks/ASM05              0
           0 CLOSED                /dev/oracleasm/disks/ASM02              0
           0 CLOSED                /dev/oracleasm/disks/ASM04              0

-- disk group 생성
SQL> create diskgroup asmdata external redundancy disk '/dev/oracleasm/disks/ASM01' ;

Diskgroup created.

-- DISK 추가
SQL> create diskgroup asmdata external redundancy disk '/dev/oracleasm/disks/ASM01' ;

Diskgroup created.

SQL> alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM02'  REBALANCE POWER 5;

Diskgroup altered.

SQL> alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM03'  REBALANCE POWER 5;

Diskgroup altered.

alter diskgroup asmdata add disk '/dev/oracleasm/disks/ASM02'  REBALANCE POWER 5;
※ REBALANCE POWER : 0부터 1024까지 설정 할 수 있으며 숫자가 높을수록 리밸런스 속도가 빨라지지만

I/O 가 늘어남으로 시스템 상태 사용률이 낮거나 업무시간 이후에 높이는 것 추천!!

SQL> col path for a30
SQL> col name for a14
SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb
  2  from v$asm_disk
  3  order by 1,3;

GROUP_NUMBER DISK_NUMBER NAME           MOUNT_STATUS          PATH                             TOTAL_MB    FREE_MB
------------ ----------- -------------- --------------------- ------------------------------ ---------- ----------
           0           0                CLOSED                /dev/oracleasm/disks/ASM05              0          0
           1           0 CRS_0000       CACHED                /dev/oracleasm/disks/CRS1            1020        760
           1           1 CRS_0001       CACHED                /dev/oracleasm/disks/CRS2            1020        764
           1           2 CRS_0002       CACHED                /dev/oracleasm/disks/CRS3            1020        760
           1           3 CRS_0003       CACHED                /dev/oracleasm/disks/CRS4            1020        816
           2           1 DATA2          CACHED                /dev/oracleasm/disks/DATA2          50996      47884
           2           0 DATA_0000      CACHED                /dev/oracleasm/disks/DATA           30716      28820
           3           0 FRA_0000       CACHED                /dev/oracleasm/disks/FRA            20476      19084
           4           0 ASMDATA_0000   CACHED                /dev/oracleasm/disks/ASM01             99         79
           4           1 ASMDATA_0001   CACHED                /dev/oracleasm/disks/ASM02             99         79
           4           2 ASMDATA_0002   CACHED                /dev/oracleasm/disks/ASM03             99         79
           4           3 ASMDATA_0003   CACHED                /dev/oracleasm/disks/ASM04             99         79

12 rows selected.

저작자표시

'ORACLE > ADMIN' 카테고리의 다른 글

oracle COMBO OF OJVM RU COMPONENT 12.2.0.1.220118 + 12.2.0.1.220118DBJAN2022RU (패치) (0)	2023.09.20
오라클 manual 11G GIPSU 절차 (0)	2023.09.15
ORACLE ASM DISK 삭제(DROP) 정리 (0)	2023.06.08
oracle restart 기동 절차 (0)	2023.06.02
ORACLE ASM DISKGROUP 삭제 (0)	2023.05.25

Posted by [PineTree]

ORACLE ASM DISK 삭제(DROP) 정리

ORACLE ASM DISK 삭제 CRS_003-- ASM DISKGROUP 상태 확인

DISK_GROUP LABEL      STATE
---------- ---------- ----------
CRS        CRS_0000   MOUNTED
CRS        CRS_0001   MOUNTED
CRS        CRS_0002   MOUNTED
CRS        CRS_0003   MOUNTED
DATA       DATA2      MOUNTED
DATA       DATA_0000  MOUNTED
FRA        FRA_0000   MOUNTED

7 rows selected.

-- ASM DISKGROUP 세부 정보 확인
SQL> col path for a30
SQL> col name for a14
SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb
  2  from v$asm_disk
  3  order by 1,3;

GROUP_NUMBER DISK_NUMBER NAME           MOUNT_STATUS          PATH                             TOTAL_MB    FREE_MB
------------ ----------- -------------- --------------------- ------------------------------ ---------- ----------
           1           0 CRS_0000       CACHED                /dev/oracleasm/disks/CRS1            1020        760
           1           1 CRS_0001       CACHED                /dev/oracleasm/disks/CRS2            1020        764
           1           2 CRS_0002       CACHED                /dev/oracleasm/disks/CRS3            1020        760
           1           3 CRS_0003       CACHED                /dev/oracleasm/disks/CRS4            1020        816
           2           1 DATA2          CACHED                /dev/oracleasm/disks/DATA2          50996      47884
           2           0 DATA_0000      CACHED                /dev/oracleasm/disks/DATA           30716      28820
           3           0 FRA_0000       CACHED                /dev/oracleasm/disks/FRA            20476      19084

7 rows selected.

-- CRS_003 삭제

SQL> alter diskgroup crs drop disk CRS_0003;

-- ASM DISKGROUP 세부 정보 확인
SQL> col path for a30
SQL> col name for a14
SQL> select group_number, disk_number, name, mount_status, path, total_mb, free_mb
  2  from v$asm_disk
  3  order by 1,3;

GROUP_NUMBER DISK_NUMBER NAME           MOUNT_STATUS          PATH                             TOTAL_MB    FREE_MB
------------ ----------- -------------- --------------------- ------------------------------ ---------- ----------
           0           0                CLOSED                /dev/oracleasm/disks/CRS4               0          0
           1           0 CRS_0000       CACHED                /dev/oracleasm/disks/CRS1            1020        700
           1           1 CRS_0001       CACHED                /dev/oracleasm/disks/CRS2            1020        696
           1           2 CRS_0002       CACHED                /dev/oracleasm/disks/CRS3            1020        696
           2           1 DATA2          CACHED                /dev/oracleasm/disks/DATA2          50996      47884
           2           0 DATA_0000      CACHED                /dev/oracleasm/disks/DATA           30716      28820
           3           0 FRA_0000       CACHED                /dev/oracleasm/disks/FRA            20476      19084

7 rows selected.

-- OS에서 CRS4번 disk 정보 삭제
[root@rac1 disks]# oracleasm deletedisk crs4
Clearing disk header: done
Dropping disk: done

[root@rac1 disks]# oracleasm listdisks
CRS1
CRS2
CRS3
DATA
DATA2
FRA

-- ASM DISKGROUP 세부 정보 CLOSE 였던 CRS_003 목록에서 사라짐 확인
GROUP_NUMBER DISK_NUMBER NAME           MOUNT_STATUS          PATH                             TOTAL_MB    FREE_MB
------------ ----------- -------------- --------------------- ------------------------------ ---------- ----------
           1           0 CRS_0000       CACHED                /dev/oracleasm/disks/CRS1            1020        700
           1           1 CRS_0001       CACHED                /dev/oracleasm/disks/CRS2            1020        696
           1           2 CRS_0002       CACHED                /dev/oracleasm/disks/CRS3            1020        696
           2           1 DATA2          CACHED                /dev/oracleasm/disks/DATA2          50996      47884
           2           0 DATA_0000      CACHED                /dev/oracleasm/disks/DATA           30716      28820
           3           0 FRA_0000       CACHED                /dev/oracleasm/disks/FRA            20476      19084

6 rows selected.

'ORACLE > ADMIN' 카테고리의 다른 글

오라클 manual 11G GIPSU 절차 (0)	2023.09.15
오라클 ASM diskgroup 생성 및 disk 추가 방법 (0)	2023.06.09
oracle restart 기동 절차 (0)	2023.06.02
ORACLE ASM DISKGROUP 삭제 (0)	2023.05.25
오라클 asm disk 그룹 추가 시 설정 ASM AFD (0)	2023.05.25

Posted by [PineTree]

oracle restart 기동 절차

oracle restart 기동 절차

## 상태 확인
[grid@+ASM ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       restart19c               STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       restart19c               STABLE
ora.asm
               ONLINE  ONLINE       restart19c               Started,STABLE
ora.ons
               OFFLINE OFFLINE      restart19c               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.evmd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.restart.db
      1        ONLINE  ONLINE       restart19c               Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE
--------------------------------------------------------------------------------

## oracle restart 정지
-- (1) DB 정지
[oracle@RESTART ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 2 20:43:45 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- (2) DB 정지 확인
[grid@+ASM ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       restart19c               STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       restart19c               STABLE
ora.asm
               ONLINE  ONLINE       restart19c               Started,STABLE
ora.ons
               OFFLINE OFFLINE      restart19c               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.evmd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.restart.db
      1        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------

-- (3) 그리드 인프라 스트럭처 정지 및 프로세스  확인
그리드 계정으로 명령어 실행

[grid@+ASM ~]$ crsctl stop has
CRS-2791: 'restart19c'에서 Oracle 고가용성 서비스가 관리하는 리소스의 종료 시작 중
CRS-2673: 'ora.DATA.dg'을(를) 'restart19c'에서 정지하려고 시도하는 중입니다.
CRS-2673: 'ora.LISTENER.lsnr'을(를) 'restart19c'에서 정지하려고 시도하는 중입니다.
CRS-2677: 'ora.DATA.dg' 정지('restart19c')가 성공했습니다.
CRS-2673: 'ora.asm'을(를) 'restart19c'에서 정지하려고 시도하는 중입니다.
CRS-2677: 'ora.LISTENER.lsnr' 정지('restart19c')가 성공했습니다.
CRS-2677: 'ora.asm' 정지('restart19c')가 성공했습니다.
CRS-2673: 'ora.evmd'을(를) 'restart19c'에서 정지하려고 시도하는 중입니다.
CRS-2677: 'ora.evmd' 정지('restart19c')가 성공했습니다.
CRS-2673: 'ora.cssd'을(를) 'restart19c'에서 정지하려고 시도하는 중입니다.
CRS-2677: 'ora.cssd' 정지('restart19c')가 성공했습니다.
CRS-2673: 'ora.driver.afd'을(를) 'restart19c'에서 정지하려고 시도하는 중입니다.
CRS-2677: 'ora.driver.afd' 정지('restart19c')가 성공했습니다.
CRS-2793: 'restart19c'에서 Oracle 고가용성 서비스가 관리하는 리소스 종료가 완료되었습니다.
CRS-4133: Oracle 고가용성 서비스가 정지되었습니다.
[grid@+ASM ~]$ ps -ef |grep d.bin
grid      6929  6514  0 20:48 pts/1    00:00:00 grep --color=auto d.bin
[grid@+ASM ~]$ crsctl stat res -t
CRS-4639: Oracle 고가용성 서비스에 접속할 수 없습니다.
CRS-4000: Status 명령을 실패했거나 오류와 함께 완료되었습니다.
[grid@+ASM ~]$

## 오라클 리스타트 기동
-- 서버가 재 기동 하면 자동 기동 한다
-- db 인스턴스를 정상 정지 (shutdown immediate) 하였다면 자동으로 올라 가지 않는다
[grid@+ASM ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       restart19c               STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       restart19c               STABLE
ora.asm
               ONLINE  ONLINE       restart19c               Started,STABLE
ora.ons
               OFFLINE OFFLINE      restart19c               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.evmd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.restart.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
--------------------------------------------------------------------------------

--(2) DB 기동 및 확인

[oracle@RESTART ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 2 20:56:36 2023
Version 19.19.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 3154113376 bytes
Fixed Size                  8930144 bytes
Variable Size             637534208 bytes
Database Buffers         2499805184 bytes
Redo Buffers                7843840 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
[oracle@RESTART ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       restart19c               STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       restart19c               STABLE
ora.asm
               ONLINE  ONLINE       restart19c               Started,STABLE
ora.ons
               OFFLINE OFFLINE      restart19c               STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.evmd
      1        ONLINE  ONLINE       restart19c               STABLE
ora.restart.db
      1        ONLINE  ONLINE       restart19c               Open,HOME=/u01/app/o
                                                             racle/product/19c/db
                                                             _1,STABLE

※ 그리드 인프라 스트럭처 기동 명령어
[grid@+ASM ~]$ crsctl start has
CRS-4123: Oracle 고가용성 서비스가 시작되었습니다.
[grid@+ASM ~]$ ps -ef |grep d.bin
grid      6216     1 11 21:01 ?        00:00:01 /u01/app/oracle/19c/grid/bin/ohasd.bin reboot
grid      6346     1  1 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/orarootagent.bin
grid      6569     1  6 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/oraagent.bin
grid      6596     1  1 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/evmd.bin
grid      6603     1  0 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
grid      6658  6596  1 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/evmlogger.bin -o /u01/app/oracle/19c/grid/log/[HOSTNAME]/evmd/evmlogger.info -l /u01/app/oracle/19c/grid/log/[HOSTNAME]/evmd/evmlogger.log
grid      6674     1  1 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/cssdagent
grid      6694     1  0 21:01 ?        00:00:00 /u01/app/oracle/19c/grid/bin/ocssd.bin
grid      6706  5865  0 21:01 pts/0    00:00:00 grep --color=auto d.bin

'ORACLE > ADMIN' 카테고리의 다른 글

오라클 ASM diskgroup 생성 및 disk 추가 방법 (0)	2023.06.09
ORACLE ASM DISK 삭제(DROP) 정리 (0)	2023.06.08
ORACLE ASM DISKGROUP 삭제 (0)	2023.05.25
오라클 asm disk 그룹 추가 시 설정 ASM AFD (0)	2023.05.25
ORACLE SEHA 인스턴스 이동 (0)	2022.09.15

Posted by [PineTree]

ORACLE ASM DISKGROUP 삭제

1.ASMCA 실행

ASMCA

2. 삭제 할 DISKGROUP DATA1 선택 후 마운트 해제

3.DATA1 dismounted 확인

4. 레이블 삭제 및 지우기 선택

5.확인

6. 삭제 완료

7. crs 상태 확인 - 더이상 DATA1 볼륨 그룹은 보이지 않는다.

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE ASM DISK 삭제(DROP) 정리 (0)	2023.06.08
oracle restart 기동 절차 (0)	2023.06.02
오라클 asm disk 그룹 추가 시 설정 ASM AFD (0)	2023.05.25
ORACLE SEHA 인스턴스 이동 (0)	2022.09.15
ORACLE SEHA LISTENER설정 및 상태 체크 (0)	2022.09.15

Posted by [PineTree]

오라클 asm disk 그룹 추가 시 설정 ASM AFD

root유저로 실행

### 경로 설정
export ORACLE_HOME=/u01/app/oracle/19c/grid
export ORACLE_BASE=/tmp

$ORACLE_HOME/bin/asmcmd afd_label RDATA1 /dev/sdf --migrate
$ORACLE_HOME/bin/asmcmd afd_label RDATA2 /dev/sdg --migrate
$ORACLE_HOME/bin/asmcmd afd_label RDATA3 /dev/sdh --migrate
$ORACLE_HOME/bin/asmcmd afd_label RFRA1 /dev/sdi --migrate

$ORACLE_HOME/bin/asmcmd afd_lslbl /dev/sdf
$ORACLE_HOME/bin/asmcmd afd_lslbl /dev/sdg
$ORACLE_HOME/bin/asmcmd afd_lslbl /dev/sdh
$ORACLE_HOME/bin/asmcmd afd_lslbl /dev/sdi

grid 유저로 asmca 실행 후 그룹 추가

[grid@+ASM ~]$

'ORACLE > ADMIN' 카테고리의 다른 글

oracle restart 기동 절차 (0)	2023.06.02
ORACLE ASM DISKGROUP 삭제 (0)	2023.05.25
ORACLE SEHA 인스턴스 이동 (0)	2022.09.15
ORACLE SEHA LISTENER설정 및 상태 체크 (0)	2022.09.15
RAC DB삭제 (0)	2022.05.22

Posted by [PineTree]

ORACLE SEHA 인스턴스 이동

상태 체크

[oracle@seha admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISA.lsnr
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.chad
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.net1.network
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.ons
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.proxy_advm
               OFFLINE OFFLINE      seha1                    STABLE
               OFFLINE OFFLINE      seha2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       seha2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    Started,STABLE
      2        ONLINE  ONLINE       seha2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       seha1                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       seha1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       seha2                    STABLE
ora.seha.db
      1        ONLINE  ONLINE       seha1                    Open,HOME=/u01/app/o
racle/product/19c,ST
ABLE
ora.seha1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha2.vip
      1        ONLINE  ONLINE       seha2                    STABLE
--------------------------------------------------------------------------------
2번 서버로 인스턴스 이동
[oracle@seha admin]$ srvctl relocate database -d seha -n seha2 -w 1 -v

[oracle@seha admin]$ srvctl   relocate   database -h

RAC One Node 데이터베이스의 온라인 재배치를 시작합니다.

사용법: srvctl relocate database -db <db_unique_name> {[-node <target>] [-timeout <timeout>] [-stopoption <stop_option>] | -abort [-revert]} [-drain_timeout <timeout>] [-verbose]
    -db <db_unique_name>           재배치할 데이터베이스의 고유 이름
    -node <target>                 데이터베이스를 재배치할 대상 노드
    -timeout <timeout>             온라인 재배치 시간 초과(분)
    -abort                         실패한 온라인 재배치 중단
    -revert                        관리자 관리 RAC One Node 데이터베이스의 후보 서버 목록에서 실패한 온라인 재배치 요청의 대상 노드 제거
    -stopoption <stop_option>      인스턴스 실행을 위한 기본 종료 옵션 무효화(NORMAL만 허용됨)
    -drain_timeout <drain_timeout> 초 단위로 지정된 서비스 비우기 시간 초과입니다.
    -verbose                       상세 정보 출력
    -help                          사용법 인쇄
옵션 설명
출처:https://hoing.io/archives/5280#relocate
-w 옵션을 기술 하지 않는다면 online relocation timeout : 30  이 적용 됩니다.
단위 는 분 이며 이 시간은 세션을 종료 하고 , 인스턴스를 migrate , 트랜잭션을 OPEN 하는 시간을 의미 합니다.

시간 안에  트랜젝션이 종료 되지 않는다면, shutdown abort 과 같은 일환으로 트랜젝션은 취소 가  되며, 세션은 사라지게 됩니다.

이런 시간에 대한 제한에 대해서 default 30분이 아닌 다른 시간으로 제한을 두고자 할 때 -w 옵션을 사용하게 됩니다.

보통의 경우 테스트 문서나 외국 문서들의 대부분이 -w 1 을 주며 시간의 범위는 1~720 분 까지 입니다.

[oracle@seha admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISA.lsnr
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.chad
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.net1.network
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.ons
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.proxy_advm
               OFFLINE OFFLINE      seha1                    STABLE
               OFFLINE OFFLINE      seha2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       seha2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    Started,STABLE
      2        ONLINE  ONLINE       seha2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       seha1                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       seha1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       seha2                    STABLE
ora.seha.db
      1        ONLINE  ONLINE       seha2                    Open,HOME=/u01/app/o
racle/product/19c,ST
ABLE
ora.seha1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha2.vip
      1        ONLINE  ONLINE       seha2                    STABLE
--------------------------------------------------------------------------------
1번 서버로 인스턴스 이동
srvctl relocate database -d seha -n seha1 -w 1 -v

[oracle@seha admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISA.lsnr
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.chad
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.net1.network
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.ons
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.proxy_advm
               OFFLINE OFFLINE      seha1                    STABLE
               OFFLINE OFFLINE      seha2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       seha2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    Started,STABLE
      2        ONLINE  ONLINE       seha2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       seha1                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       seha1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       seha2                    STABLE
ora.seha.db
      1        ONLINE  ONLINE       seha1                    Open,HOME=/u01/app/o
racle/product/19c,ST
ABLE
ora.seha1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha2.vip
      1        ONLINE  ONLINE       seha2                    STABLE
--------------------------------------------------------------------------------

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE ASM DISKGROUP 삭제 (0)	2023.05.25
오라클 asm disk 그룹 추가 시 설정 ASM AFD (0)	2023.05.25
ORACLE SEHA LISTENER설정 및 상태 체크 (0)	2022.09.15
RAC DB삭제 (0)	2022.05.22
오라클 메모리 튜닝 권고 (0)	2022.05.22

Posted by [PineTree]

ORACLE SEHA LISTENER설정 및 상태 체크

/etc/hosts
### Public
192.168.219.141         seha1
192.168.219.143         seha2

### Private
192.168.56.41           seha1-priv
192.168.56.42           seha2-priv

### Virtual
192.168.219.192         seha1-vip
192.168.219.194         seha2-vip

### SCAN
192.168.219.147         seha-scan

[root@seha1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.chad
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.net1.network
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.ons
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.proxy_advm
               OFFLINE OFFLINE      seha1                    STABLE
               OFFLINE OFFLINE      seha2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       seha2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    Started,STABLE
      2        ONLINE  ONLINE       seha2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       seha1                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       seha1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       seha2                    STABLE
ora.seha.db
      1        ONLINE  ONLINE       seha1                    Open,HOME=/u01/app/o
                                                             racle/product/19c,ST
                                                             ABLE
ora.seha1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha2.vip
      1        ONLINE  ONLINE       seha2                    STABLE
--------------------------------------------------------------------------------

tnsnames.ora 등록
1번 서버 등록
lisa =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = seha1-vip)(PORT = 2555))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =seha)
    )
  )

2번 서버 등록
lisa =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = seha2-vip)(PORT = 2555))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =seha)
    )
  )

리스너 삭제 후 변경
srvctl stop listener
srvctl remove listener -l listener

리스너 생성 2555포트 사용
srvctl add listener -o /u01/app/grid -p 2555 -l lisa

리스너 기동
srvctl start listener -l lisa

상태 확인
[oracle@seha admin]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISA.lsnr
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.chad
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.net1.network
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.ons
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.proxy_advm
               OFFLINE OFFLINE      seha1                    STABLE
               OFFLINE OFFLINE      seha2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  INTERMEDIATE seha1                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    Started,STABLE
      2        ONLINE  ONLINE       seha2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       seha1                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       seha1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha.db
      1        ONLINE  ONLINE       seha1                    Open,HOME=/u01/app/o
                                                             racle/product/19c,ST
                                                             ABLE
ora.seha1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha2.vip
      1        ONLINE  ONLINE       seha2                    STABLE
--------------------------------------------------------------------------------

[oracle@seha ~]$ sqlplus system/oracle@lisa

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:25:24 2022
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 15 2022 10:58:22 +09:00

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

접속 실패 시 DB 인스턴스 재 기동
[oracle@seha admin]$ sqlplus scott/tiger@lisa

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 10:13:12 2022
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

[oracle@seha admin]$ lsnrctl status lisa

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2022 09:57:55

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISA)))
STATUS of the LISTENER
------------------------
Alias                     LISA
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2022 09:56:03
Uptime                    0 days 0 hr. 1 min. 51 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/seha1/lisa/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISA)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.219.141)(PORT=2555)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.219.192)(PORT=2555)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
The command completed successfully

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

인스턴스 재 기동 후
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISA.lsnr
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.chad
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.net1.network
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.ons
               ONLINE  ONLINE       seha1                    STABLE
               ONLINE  ONLINE       seha2                    STABLE
ora.proxy_advm
               OFFLINE OFFLINE      seha1                    STABLE
               OFFLINE OFFLINE      seha2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       seha2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    Started,STABLE
      2        ONLINE  ONLINE       seha2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       seha1                    STABLE
      2        ONLINE  ONLINE       seha2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       seha1                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       seha1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       seha2                    STABLE
ora.seha.db
      1        ONLINE  ONLINE       seha1                    Open,HOME=/u01/app/o
racle/product/19c,ST
ABLE
ora.seha1.vip
      1        ONLINE  ONLINE       seha1                    STABLE
ora.seha2.vip
      1        ONLINE  ONLINE       seha2                    STABLE
--------------------------------------------------------------------------------

[oracle@seha admin]$ lsnrctl status lisa

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-SEP-2022 09:58:31

Copyright (c) 1991, 2022, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISA)))
STATUS of the LISTENER
------------------------
Alias                     LISA
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-SEP-2022 09:56:03
Uptime                    0 days 0 hr. 2 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/seha1/lisa/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISA)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.219.141)(PORT=2555)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.219.192)(PORT=2555)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "seha" has 1 instance(s).
  Instance "seha", status READY, has 1 handler(s) for this service...
Service "sehaXDB" has 1 instance(s).
  Instance "seha", status READY, has 1 handler(s) for this service...
The command completed successfully

접속 확인
[oracle@seha admin]$ sqlplus system/oracle@lisa

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 15 11:38:28 2022
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Sep 15 2022 11:25:24 +09:00

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL>
================================================

'ORACLE > ADMIN' 카테고리의 다른 글

오라클 asm disk 그룹 추가 시 설정 ASM AFD (0)	2023.05.25
ORACLE SEHA 인스턴스 이동 (0)	2022.09.15
RAC DB삭제 (0)	2022.05.22
오라클 메모리 튜닝 권고 (0)	2022.05.22
oracle ERROR: ORA-12547: TNS:lost contact (0)	2018.08.08

Posted by [PineTree]

RAC DB삭제

RAC DB 삭제

[oracle@racdb1 ~]$ crsctl status res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       pradg1
               ONLINE  ONLINE       pradg2
ora.DATA.dg
               ONLINE  ONLINE       pradg1
               ONLINE  ONLINE       pradg2
ora.LISTENER.lsnr
               ONLINE  ONLINE       pradg1
               ONLINE  ONLINE       pradg2
ora.RECO.dg
               ONLINE  ONLINE       pradg1
               ONLINE  ONLINE       pradg2
ora.asm
               ONLINE  ONLINE       pradg1                   Started
               ONLINE  ONLINE       pradg2                   Started
ora.gsd
               OFFLINE OFFLINE      pradg1
               OFFLINE OFFLINE      pradg2
ora.net1.network
               ONLINE  ONLINE       pradg1
               ONLINE  ONLINE       pradg2
ora.ons
               ONLINE  ONLINE       pradg1
               ONLINE  ONLINE       pradg2
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       pradg2
ora.cvu
      1        ONLINE  ONLINE       pradg2
ora.oc4j
      1        ONLINE  ONLINE       pradg2
ora.pradg1.vip
      1        ONLINE  ONLINE       pradg1
ora.pradg2.vip
      1        ONLINE  ONLINE       pradg2
ora.racdb.db
      1        ONLINE  ONLINE       pradg1                   Open
      2        ONLINE  ONLINE       pradg2                   Open
ora.scan1.vip
      1        ONLINE  ONLINE       pradg2
[oracle@racdb1 ~]$ srvctl stop database -d racdb
[oracle@racdb1 ~]$ srvctl remove instance -d racdb -i racdb1
racdb 데이터베이스에서 인스턴스를 제거하겠습니까? (y/[n]) y
[oracle@racdb1 ~]$ srvctl remove instance -d racdb -i racdb2
racdb 데이터베이스에서 인스턴스를 제거하겠습니까? (y/[n]) y
[oracle@racdb1 ~]$ srvctl remove database -d racdb
racdb 데이터베이스를 제거하겠습니까? (y/[n]) y
[oracle@racdb1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 21 23:18:54 2022

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount restsrict exclusive;
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2257352 bytes
Variable Size             788532792 bytes
Database Buffers         2332033024 bytes
Redo Buffers               17203200 bytes
ORA-01504: database name 'RESTSRICT' does not match parameter db_name 'racdb'

SQL> startup mount restrict exclusive;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter systsem set cluster_database=false scope=spfile;
alter systsem set cluster_database=false scope=spfile
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shut immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount restrict exclusive;
ORACLE instance started.

Total System Global Area 3140026368 bytes
Fixed Size                  2257352 bytes
Variable Size             721423928 bytes
Database Buffers         2399141888 bytes
Redo Buffers               17203200 bytes
Database mounted.
SQL> drop database;

Database dropped.

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE SEHA 인스턴스 이동 (0)	2022.09.15
ORACLE SEHA LISTENER설정 및 상태 체크 (0)	2022.09.15
오라클 메모리 튜닝 권고 (0)	2022.05.22
oracle ERROR: ORA-12547: TNS:lost contact (0)	2018.08.08
오라클 role 에 관하여 (0)	2018.07.21

Posted by [PineTree]

오라클 메모리 튜닝 권고

######  What are the major tuning areas in database performance tuning  #######

  - Memory - shared pool, large pool, buffer cache, redo log buffer, and sort area size.
  - I/O - distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
  - CPU - CPU utilization.
  - Space management - extent allocation and Oracle block efficiency.
  - Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint.
  - Rollback segment - sizing rollback segments.
  - Network

###### 조정해야할 메모리 영역
전체 SGA 영역 : 2.4G
shared_pool_size = ???
large_pool_size = ???
java_pool_size = ???
db_cache_size = ???
SHARED_POOL_RESERVED_SIZE=???

##################################################
### PGA 성능(할당량) 조정
##################################################
PGA target advice => v$pga_target_advice를 이용하여 적당 할당량을 조사한다.

-- PGA 어드바이스
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

=>위의 쿼리를 실행해서 최적의 PGA할당량을 찾아낸다

PGA Memory Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0

                                       Estd Extra    Estd PGA   Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
        94     0.1        326,688.8         11,869.3     96.0      2,140
       188     0.3        326,688.8         11,869.3     96.0      2,139
       375     0.5        326,688.8          2,393.2     99.0        646
       563     0.8        326,688.8            110.8    100.0          0
       750     1.0        326,688.8            110.8    100.0          0
       900     1.2        326,688.8            110.8    100.0          0
     1,050     1.4        326,688.8            110.8    100.0          0
     1,200     1.6        326,688.8            110.8    100.0          0
     1,350     1.8        326,688.8            110.8    100.0          0
     1,500     2.0        326,688.8            110.8    100.0          0
     2,250     3.0        326,688.8            110.8    100.0          0
     3,000     4.0        326,688.8            110.8    100.0          0
     4,500     6.0        326,688.8            110.8    100.0          0
     6,000     8.0        326,688.8            110.8    100.0          0
          -------------------------------------------------------------

:::::::::::::::::::::::         결과          ::::::::::::::::::::::::::::::
=========> PGA 사이즈 조정 : 현재 사이즈 적정

##################################################
### SGA 크기 조정
##################################################

SGA Target Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886

SGA Target SGA Size   Est DB      Est DB   Est Physical
  Size (M)   Factor Time (s) Time Factor          Reads
---------- -------- -------- ----------- --------------
       752       .5   78,897         1.0      5,109,138
     1,128       .8   78,044         1.0      4,873,605
     1,504      1.0   77,494         1.0      4,723,249
     1,880      1.3   77,293         1.0      4,667,665
     2,256      1.5   77,238         1.0      4,653,967
     2,632      1.8   77,238         1.0      4,653,967
     3,008      2.0   77,239         1.0      4,653,967
-------------------------------------------------------------

:::::::::::::::::::::::         결과          ::::::::::::::::::::::::::::::
=========> SGA 사이즈 조정 : 1.5GB -> 2.5GB로 사이즈 조정
★★★★ statspack분석 결과에 의해  SGA_TARGET 파라메타 2.5GB로 재조정.
alter system set SGA_MAX_SIZE=(2.5GB)

##################################################
### shared pool 성능(할당량) 조정
##################################################

1.먼저 현재 사용중인 데이타베이스에 대해 라이브러리 캐시영역에 대한 크기가 적정한지 조사한다.

select namespace,gets,gethits,gethitratio
from v$librarycache
where namespace = 'SQL AREA';

=> 참조 : 만약 gethitratio 컬럼의 값이 90%이상이라면 라이브러리 캐쉬영역이 개발자들의 SQL 파싱정보를 저장하기에 충분한 메모리 공간을 확보하고 있음을 의미하며 만약 90% 이하라면 성능이 저하 될 수도 있다는 것을 의마한다.

=> Quality DB의 경우 다음과 같은 결과가 나옴.
NAMESPACE             GETS    GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA            885978     878552  .991618302
=========> 99%

## 부가적으로 pins에 대한 reloads의 비율을 확인한다.

select sum(pins),sum(reloads),sum(reloads) / sum(pins)
from v$librarycache
where namespace = 'SQL AREA';

=> 완성차 DB의 경우 다음과 같이 나옴.
SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
---------- ------------ ----------------------
  85931701          506             5.8884E-06

========> 참조 : PINS에 대한 reloads에 대한 비율이 1%미만일 경우 라이브러리 캐쉬 영역의 크기가 SQL 파싱정보를
                 저장하기에 충분하다는 의미이다.

현재 할당된 shared_pool 사이즈를 조사한다.
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

=> 완성차 DB의 경우 다음과 같이 나옴.
POOL             MBYTES
------------ ----------
             863.996956
shared pool  511.985039
streams pool 48.0495529
large pool           32
java pool            64

2. Shared pool advice => v$shared_pool_advice 를 이용해서 oracle의 사이즈 advice를 조사한다.

SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
       shared_pool_size_factor "Size Factor",estd_lc_time_saved "Time Saved in sec"
  FROM v$shared_pool_advice;

Shared Pool Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886
-> SP: Shared Pool     Est LC: Estimated Library Cache   Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid

                                        Est LC Est LC  Est LC Est LC
    Shared    SP   Est LC                 Time   Time    Load   Load      Est LC
      Pool  Size     Size       Est LC   Saved  Saved    Time   Time         Mem
  Size (M) Factr      (M)      Mem Obj     (s)  Factr     (s)  Factr    Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
       256    .5       72        4,370 #######    1.0  33,605    1.4  32,095,562
       320    .6      132        7,162 #######    1.0  28,485    1.2  32,102,312
       384    .8      195       12,092 #######    1.0  24,534    1.0  32,102,864
       448    .9      204       13,130 #######    1.0  24,534    1.0  32,102,864
       512   1.0      204       13,130 #######    1.0  24,534    1.0  32,102,864
       576   1.1      204       13,130 #######    1.0  24,534    1.0  32,102,864
       640   1.3      204       13,130 #######    1.0  24,534    1.0  32,102,864
       704   1.4      204       13,130 #######    1.0  24,534    1.0  32,102,864
       768   1.5      204       13,130 #######    1.0  24,534    1.0  32,102,864
       832   1.6      204       13,130 #######    1.0  24,534    1.0  32,102,864
       896   1.8      204       13,130 #######    1.0  24,534    1.0  32,102,864
       960   1.9      204       13,130 #######    1.0  24,534    1.0  32,102,864
     1,024   2.0      204       13,130 #######    1.0  24,534    1.0  32,102,864
          -------------------------------------------------------------+
The above output shows the current setting of the shared pool is   512M (for which Size factor is 1).

+ It also shows decreasing the size of the shared pool to the 50% of its
  current value will also be equally efficient as the current value.
+ Also doubling the size of the shared pool will save extra 2300 sec in parsing.
+ Using this view a DBA has the correct picture to design Shared pool.

========> 결론 : 현재 PEMS DB의 shared_pool 사이즈를 init*.ora 파일에서 늘려준다.
shared_pool_size = 800M(????)

### 현재 SGA에 대한 할당 조사
select current_size from v$buffer_pool;
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

3. SHARED_POOL_RESERVED_SIZE에 대한 성능 조절

=> SHARED_POOL_RESERVED_SIZE는 PL/SQL 블록으로 실행된 sql문의 구문분석 정보를 저장할때 사용되는 공유 풀 영역의크기를  지정하는 파라메타이다
=> 기본값은 SHARED_POOL_SIZE의 10%이며 최대값은 SHARED_POOL_SIZE 값의 1/2 이다.
=> v$shared_pool_reserved 자료사전의 REQUEST_FAILURES의 컬럼값이 0이 아니거나 계속 증가값을 보일때
    이 파라메타 값을 크게 해준다.

select request_failures from v$shared_pool_reserved;

REQUEST_FAILURES
----------------
               0

========> 결론 :SHARED_POOL_RESERVED_SIZE 변경 없음
SHARED_POOL_RESERVED_SIZE = 25M(?????)

##################################################
#### DB Cache Size 성능 조정
##################################################

1. DB cache advice => v$db_cache_advice 동적 성능 뷰를 이용하여 db cache 사이즈를 시뮬레이션 해본다.

column size_for_estimate         format 999,999,999,999 heading 'Cache Size (m)'
column buffers_for_estimate      format 999,999,999 heading 'Buffers'
column estd_physical_read_factor format 999.90 heading 'Estd Phys|Read Factor'
column estd_physical_reads       format 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate,estd_physical_read_factor, estd_physical_reads
  FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
   AND block_size    = (SELECT value FROM V$PARAMETER
                         WHERE name = 'db_block_size')
   AND advice_status = 'ON';

    Size for  Size      Buffers   Read     Phys Reads     Est Phys % dbtime
P    Est (M) Factr  (thousands)  Factr    (thousands)    Read Time  for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D         80    .1           10    2.1          9,703       21,737     28.0
D        160    .2           20    1.1          5,315        5,540      7.1
D        240    .3           30    1.1          5,029        4,485      5.8
D        320    .4           40    1.0          4,948        4,186      5.4
D        400    .5           50    1.0          4,897        4,001      5.2
D        480    .6           59    1.0          4,862        3,869      5.0
D        560    .6           69    1.0          4,829        3,750      4.8
D        640    .7           79    1.0          4,796        3,628      4.7
D        720    .8           89    1.0          4,761        3,497      4.5
D        800    .9           99    1.0          4,740        3,419      4.4
D        864   1.0          107    1.0          4,723        3,359      4.3
D        880   1.0          109    1.0          4,709        3,306      4.3
D        960   1.1          119    1.0          4,668        3,152      4.1
D      1,040   1.2          129    1.0          4,649        3,083      4.0
D      1,120   1.3          139    1.0          4,621        2,979      3.8
D      1,200   1.4          149    1.0          4,607        2,929      3.8
D      1,280   1.5          159    1.0          4,599        2,899      3.7
D      1,360   1.6          169    1.0          4,594        2,882      3.7
D      1,440   1.7          178    1.0          4,594        2,880      3.7
D      1,520   1.8          188    1.0          4,592        2,875      3.7
D      1,600   1.9          198    1.0          4,580        2,829      3.7
          -------------------------------------------------------------

========> 결론 : 현재 PEMS DB의 db_cache 사이즈를 1.6GB로 변경
db_cache_size = 1.6GB(???)

##################################################
#### Redo buffer Size 성능 조정
##################################################

Check the statistic redo buffer allocation retries in the V$SYSSTAT view.
If this value is high relative to redo blocks written, try to increase the LOG_BUFFER size.

Query for the same is

select * from v$sysstat where name like 'redo buffer allocation retries'
or
select * from v$sysstat where name like 'redo blocks written';

혹은 v$sysstat 자료사전에서 서버 프로세스가 로그 정보를 저장했던 로그버퍼의 블록 수(REDO ENTRIES)와 로그버퍼의 경합으로
인해 발생한 대기상태에서 다시 로그 버퍼공간을 할당 받았던 불록 수(redo buffer allocation entries)를 확인한다.

=>이 SQL문에 의한 실행 결과
select name,value
from v$sysstat
where name in ('redo buffer allocation retries','redo entries');

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                       23465374
redo buffer allocation retries                                           91=>0일 수록 좋은것

========> 결론 : 현재 quality DB의 log_buffer 사이즈를 14MB * 2 이상으로 init*.ora 파일에서 늘려준다.
log_buffer = 30M

##################################################
#### java_pool Size 성능 조정
##################################################

** Java Pool advice => v$java_pool_advice

select JAVA_POOL_SIZE_FOR_ESTIMATE,JAVA_POOL_SIZE_FACTOR,ESTD_LC_LOAD_TIME
from v$java_pool_advice

JAVA_POOL_SIZE_FOR_ESTIMATE JAVA_POOL_SIZE_FACTOR ESTD_LC_LOAD_TIME
--------------------------- --------------------- -----------------
                          4                     1              9493
                          8                     2              9493

========> 결론 : 현재 PEMS DB의 java_pool_size 사이즈를 8MB 이상으로 init*.ora 파일에서 늘려준다.
java_pool_size=128M(20971520)

##################################################
#### Redo-log file  Size 성능 조정
##################################################
FAST_START_MTTR_TARGET='숫자값'으로 설정한다(V$MTTR_TARGET_ADVICE)
alter system set FAST_START_MTTR_TARGET=300

SQL> select ACTUAL_REDO_BLKS,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,
    OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES  from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE CKPT_BLOCK_WRITES
---------------- ---------------- ----------- -------------- --------------------
          942            18432          71             59                     49
388462

The recommended optimal redolog file size is 49 MB as seen from column -OPTIMAL_LOGFILE_SIZE.
This is as per the setting of "fast_start_mttr_target" = 170

각 리두로그 사이즈 600M로 조정

$@# !! SQL 튜닝 전에

SQL>alter session set timed_statistics=true;
SQL>alter session set sql_trace=true;
요거 켜주기......

### REDO LOG 파일 재배치 해야함.(물리적으로 서로 다른 디스크 경로에 변경 배치한다)

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/ora_log/KMSMESV1/rdo1/redo01a.log','/ora_dump/KMSMESV1/rdo2/redo01b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/ora_log/KMSMESV1/rdo1/redo02a.log','/ora_dump/KMSMESV1/rdo2/redo02b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/ora_log/KMSMESV1/rdo1/redo03a.log','/ora_dump/KMSMESV1/rdo2/redo03b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/ora_log/KMSMESV1/rdo1/redo04a.log','/ora_dump/KMSMESV1/rdo2/redo04b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/ora_log/KMSMESV1/rdo1/redo05a.log','/ora_dump/KMSMESV1/rdo2/redo05b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/ora_log/KMSMESV1/rdo1/redo06a.log','/ora_dump/KMSMESV1/rdo2/redo06b.log') SIZE 500M

### ADDITIONAL 1 : Disk I/O 튜닝

select tablespace_name,file_name,phyrds, phywrts
from dba_data_files df,v$filestat fs
where df.file_id = fs.file#;

===============> 결론 : 쿼리 결과 system 테이블 스페이스와 undo table space를 분리해야함.
1.DB SHUTDOWN

2.undo datafile 이동 ex)F:\->H:\
=>이동 후 기존 datafile 삭제
3.db startup(mount까지)

alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS01.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS01.DBF';
alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS02.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS02.DBF';
alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS03.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS03.DBF';

4.DB OPEN

##### redolog 변경 샘플

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('I:\ora_log\KMSMESQ1\rdo1\REDO01A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO01B.LOG') SIZE 200M;

ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('I:\ora_log\KMSMESQ1\rdo1\REDO02A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO02B.LOG') SIZE 200M;

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('I:\ora_log\KMSMESQ1\rdo1\REDO03A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO03B.LOG') SIZE 200M;

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE SEHA LISTENER설정 및 상태 체크 (0)	2022.09.15
RAC DB삭제 (0)	2022.05.22
oracle ERROR: ORA-12547: TNS:lost contact (0)	2018.08.08
오라클 role 에 관하여 (0)	2018.07.21
오라클 리스너 시작시 에러 tns-12546,12560,00516 (0)	2018.07.19

Posted by [PineTree]

oracle ERROR: ORA-12547: TNS:lost contact

증상

- 로컬로 접속할 수 없습니다.
- Sql*Plus를 사용하여 접속하려고 시도하면 다음 오류가 발생합니다.

ERROR:
----------------
ORA-12547: TNS:lost contact

예:

$sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 30 11:59:06 2011

Copyright (c) 1982, 2008, Oracle. All rights reserved.

ERROR:
ORA-12547: TNS:lost contact
원인

다음이 원인일 수 있습니다.

커널 파라미터 설정
ORACLE 실행 파일의 잘못된 권한 설정
스택에 대한 ulimit 설정 부족
$ORACLE_HOME/rdbms/lib/config.o가 0바이트임
Oracle 바이너리가 올바르게 링크되지 않음
누락된 $ORACLE_HOME/dbs 디렉토리
빈 dbs 디렉토리를 생성하면 이 오류가 해결되므로 예를 들어 인스턴스를 종료할 수도 있지만 누락된 파라미터 파일로 인해 인스턴스를 재시작할 수는 없습니다. 따라서 dbs 디렉토리를 완전히 복원해야 합니다.
해결책

해결책을 구현하려면 다음 단계를 실행하십시오.:

1. 커널 파라미터 설정 때문일 수 있습니다.

커널 파라미터에 필요한 설정을 제공하는 아래 노트를 참조하십시오.
Note 169706.1 Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64

2. ORACLE.exe의 권한 설정이 잘못되었기 때문일 수 있습니다.

선택적으로 다음을 사용하여 OS 트레이스를 수집한다면:

$strace -f -o /tmp/trace.1.log $ORACLE_HOME/bin/sqlplus / as sysdba

이 경우 trace.1.log에서 다음과 같은 몇 개의 (Permission denied)를 볼 수 있습니다.:

21810 open("/oracle/PROD/db/tech_st/11.1.0/admin/PROD_erptest/diag/rdbms/prod/PROD/alert/log.xml", O_WRONLY|O_CREAT|O_APPEND, 0664) = -1 EACCES (Permission denied)
......

21810 open("/oracle/PROD/db/tech_st/11.1.0/admin/PROD_erptest/diag/rdbms/prod/PROD/trace/alert_PROD.log", O_WRONLY|O_CREAT|O_APPEND, 0664) = -1 EACCES (Permission denied)

'ls' 명령을 실행하면 다음과 같이 permissions 권한이 6751 이어야 합니다

다음을 실행하여 권한 설정을 확인하십시오 :

$ cd $ORACLE_HOME/bin
$ ls -l oracle

출력에 다음과 같은 올바른 권한 설정이 표시되어야 합니다.

-rwsr-s--x 1 oracle dba

그렇지 않은 경우 다음을 실행하여 권한 설정을 수정합니다.

$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle

다음 사항이 올바른지도 확인합니다.

echo $ORACLE_HOME
echo $ORACLE_SID
echo $LD_LIBRARY_PATH
echo $PATH

3. 스택에 대한 현재 ulimit 설정을 확인합니다.

ulimit -a

설치 설명서에서 현재 플랫폼 및 Oracle 버전을 확인하고 스택을 적절하게 설정합니다..

4. 다음 두 파일이 0바이트가 아닌지 확인합니다.

$ORACLE_HOME/bin/oracle
$ORACLE_HOME/rdbms/lib/config.o

0바이트인 경우 다음 파일의 이름을 바꿉니다.

% cd $ORACLE_HOME/rdbms/lib
% mv config.o config.o.bad

그런 다음 Oracle 바이너리를 다시 리링크합니다.

% relink oracle

5. 위와 같이 해도 문제가 해결되지 않으면 데이터베이스 및 리스너를 종료한 다음 "relink all"을 실행합니다.
Note 131321.1 How to Relink Oracle Database Software on UNIX

다음이 원인일 수 있습니다.
1. 커널 파라미터 설정
2. ORACLE 실행 파일의 잘못된 권한 설정
3. 스택에 대한 ulimit 설정 부족
4. $ORACLE_HOME/rdbms/lib/config.o가 0바이트임
5. Oracle 바이너리가 올바르게 링크되지 않음
6. 누락된 $ORACLE_HOME/dbs 디렉토리
빈 dbs 디렉토리를 생성하면 이 오류가 해결되므로 예를 들어 인스턴스를 종료할 수도 있지만 누락된 파라미터 파일로 인해 인스턴스를 재시작할 수는 없습니다. 따라서 dbs 디렉토리를 완전히 복원해야 합니다.

===============================
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact

추가로
/dev/null 퍼미션에 other에 쓰기 권한이 없을때도 발생

chmod o+w /dev/null
해결

저작자표시 비영리

'ORACLE > ADMIN' 카테고리의 다른 글

RAC DB삭제 (0)	2022.05.22
오라클 메모리 튜닝 권고 (0)	2022.05.22
오라클 role 에 관하여 (0)	2018.07.21
오라클 리스너 시작시 에러 tns-12546,12560,00516 (0)	2018.07.19
downgrading oracle database to earlier release , 11.2.0.3 to 11.2.0.1 , oracle 11g to 10g (0)	2017.10.10

Posted by [PineTree]

About DATABASE

'ORACLE/ADMIN'에 해당되는 글 169건

오라클 ASM diskgroup 생성 및 disk 추가 방법

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE ASM DISK 삭제(DROP) 정리

'ORACLE > ADMIN' 카테고리의 다른 글

oracle restart 기동 절차

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE ASM DISKGROUP 삭제

'ORACLE > ADMIN' 카테고리의 다른 글

오라클 asm disk 그룹 추가 시 설정 ASM AFD

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE SEHA 인스턴스 이동

'ORACLE > ADMIN' 카테고리의 다른 글

ORACLE SEHA LISTENER설정 및 상태 체크

'ORACLE > ADMIN' 카테고리의 다른 글

RAC DB삭제

'ORACLE > ADMIN' 카테고리의 다른 글

오라클 메모리 튜닝 권고

'ORACLE > ADMIN' 카테고리의 다른 글

oracle ERROR: ORA-12547: TNS:lost contact

'ORACLE > ADMIN' 카테고리의 다른 글

카테고리

공지사항

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바