'오라클 RAC'에 해당되는 글 2건

2020.12.24 Oracle 11G RAC Engine,OCR,VOTE Backup/Recovery(백업/복구)
2019.03.06 오라클 RAC Estd.interconnect Traffic 관리 스크립트

Oracle 11G RAC Engine,OCR,VOTE Backup/Recovery(백업/복구)

1. IT Story/DB 2020. 12. 24. 11:05

1. Oracle Engine,OCR,VOTE 백업 수행

1) ocr/vote 백업

백업수행: $ORACLE_HOME/bin/ocrconfig -manualbackup
백업위치확인: $ORACLE_HOME/bin/ocrconfig -showbackup

2) oracle, oragrid 엔진 백업
/oracle, /oragrid 영역 전체를 tar로 묶음

[디렉토리 권한에 문제 발생시]

1) oragrid

# chown -R oragrid:dba $GRID_HOME
# chmod -R 755 $GRID_HOME
-- 이후 정상적인 퍼미션으로 변경
# cd <GRID_HOME>/crs/install/
# ./rootcrs.pl -init

2) oracle

# chown -R oracle:dba $ORACLE_HOME
# chmod -R 755 $ORACLE_HOME
-- 이후 정상적인 퍼미션으로 변경
a) relink all 수행 (oracle user)
$ relink all

3) oraInventory

# chown -R oragrid:dba $oraInventory_home
# $oraInventory_home/orainstRoot.sh

2. Oracle Engine,OCR,VOTE 복구 수행

0) grid / oracle / oraInventory 압축 해제

tar xvfp <grid_home>
tar xvfp <oracle_home>
tar xvfp <oraInventory>

1) shared volume 확인 (oragrid user)

# $ORACLE_HOME/bin/cluvfyrac.sh comp ssa -n testdb1,testdb2 -s /dev/rhdisksys1

# $ORACLE_HOME/bin/cluvfyrac.sh comp ssa -n testdb1,testdb2 -s /dev/rhdisksys2

2. crs exclusive mode로 open (root user)

# $CRS_HOME/bin/crsctl start crs -excl -nocrs

3. ocr/vote diskgroup 생성 (oragrid user)

$ sqlplus / as sysasm
SQL> alter system set asm_diskstring='/dev/*sys*' scope=memory sid='*';
SQL> create diskgroup ASM_CRS external redundancy disk '/dev/rhdisksys1','/dev/rhdisksys2' attribute COMPATIBLE.ASM' = '11.2';
SQL> exit;

4. crs restore (root user)

# cd $GRID_HOME/cdata/<cluster name>/
# ocrconfig -restore <backup_20190610_111155.ocr>

5. vote disk 재생성 (1번 노드 / root)

# crsctl replace votedisk +ASM_CRS

6. asm spfile 재생성 (1번노드 / asm user)

- ASM 경로 변경이 없다면 제외해도 됌

[init+ASM1.ora 파일]

*._disable_rebalance_compact=TRUE
*._enable_shared_pool_durations=FALSE

+ASM2.asm_diskgroups='ASM_REDO','ASM_DATA','ASM_ARCH' #Manual Dismount
+ASM1.asm_diskgroups='ASM_REDO','ASM_DATA','ASM_ARCH' #Manual Dismount
*.diagnostic_dest='/oracle_log'
+ASM1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb1-vip)(PORT=1521))))'
+ASM2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb2-vip)(PORT=1521))))'
*.memory_target=0
*.pga_aggregate_target=419430400
*.sga_max_size=4G
*.sga_target=4294967296*._disable_rebalance_compact=TRUE
*._enable_shared_pool_durations=FALSE
+ASM2.asm_diskgroups='ASM_REDO','ASM_DATA','ASM_ARCH' #Manual Dismount
+ASM1.asm_diskgroups='ASM_REDO','ASM_DATA','ASM_ARCH' #Manual Dismount
*.diagnostic_dest='/oracle_log'
+ASM1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb1-vip)(PORT=1521))))'
+ASM2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb2-vip)(PORT=1521))))'
*.memory_target=0
*.pga_aggregate_target=419430400
*.sga_max_size=4G
*.sga_target=4294967296

$ sqlplus / as sysasm
SQL> create spfile='+ASM_CRS' from pfile='/ora_backup/init+ASM1.ora';

7. crs 재기동 (1번노드 / root)
# crsctl stop crs -f
-- 올리는건 1/2번 노드 양쪽에서 수행
# crsctl start crs

-- 검증
# crsctl check cluster -all
# crsctl query css votedisk

# Oracle 11G RAC Engine Backup/Recovery

# Copia de seguridad / recuperación de Oracle 11G RAC Engine

# Oracle 11G RAC引擎备份/恢复

# Oracle 11G RAC Engineバックアップ/リカバリ

# Oracle 11G RAC इंजन बैकअप / रिकवरी

저작자표시 동일조건

'1. IT Story > DB' 카테고리의 다른 글

DB 동시성제어 Locking (0)	2020.12.24
Amazon AWS Aurora DB 구축 Q&A 정리(2020) (0)	2020.12.24
Oracle MySQL 운영관리 Q&A 정리(2020) (0)	2020.12.24
대량 데이터 변경 세션 KILL 수행 후, ROLLBACK 모니터링 방법 (0)	2020.12.24
Oracle Database Appliance(X5-2) Disk Online replacement (0)	2015.12.30
Adding MySQL Cluster Data Nodes Online (0)	2015.12.24
MySQL Monitor Tool Install (0)	2015.12.24
Oracle 서비스지원(SR) 신청 방법 (0)	2015.10.07

운명을바꾸는자

IT와 함께 살아가는 삶

오라클 RAC Estd.interconnect Traffic 관리 스크립트

1. IT Story/Scripts 2019. 3. 6. 21:39

오라클 RAC Estd.interconnect Traffic 관리 스크립트

SELECT SNAP_TIME, SNAP_TIME_RANGE,

TO_CHAR ("Estd.Interconnect Traffic(KB)", 'FM999,999,999.9')

"Estd.Interconnect Traffic(KB)",

TO_CHAR (

"Blocks Receive Time"

/ DECODE ("Blocks Received", 0, 1, "Blocks Received"),

'FM999,999,999.9')

"Interconnect Latency(ms)",

TO_CHAR (

"Blocks Served Time"

/ DECODE ("Blocks Served", 0, 1, "Blocks Served"),

'FM999,999,999.9')

"Prepare Latency(ms)",

TO_CHAR (

("Blocks Receive Time"

/ DECODE ("Blocks Received", 0, 1, "Blocks Received"))

- ("Blocks Served Time"

/ DECODE ("Blocks Served", 0, 1, "Blocks Served")),

'FM999,999,999.9')

"Transfer Latency(ms)",

TO_CHAR ("gc cr blocks served", 'FM999,999,999.9')

"gc cr blocks served",

TO_CHAR ("gc current blocks served", 'FM999,999,999.9')

"gc current blocks served",

TO_CHAR ("gc cr blocks received", 'FM999,999,999.9')

"gc cr blocks received",

TO_CHAR ("gc current blocks received", 'FM999,999,999.9')

"gc current blocks received",

TO_CHAR ("gcs messages sent", 'FM999,999,999.9') "gcs messages sent",

TO_CHAR ("ges messages sent", 'FM999,999,999.9') "ges messages sent",

TO_CHAR ("gcs msgs received", 'FM999,999,999.9') "gcs msgs received",

TO_CHAR ("ges msgs received", 'FM999,999,999.9') "ges msgs received",

TO_CHAR ("gc blocks lost", 'FM999,999,999.9') "gc blocks lost",

TO_CHAR ("gc cr block build time", 'FM999,999,999.99')

"gc cr block build tm(ms)",

TO_CHAR ("gc cr block flush time", 'FM999,999,999.99')

"gc cr block flush tm(ms)",

TO_CHAR ("gc current block flush time", 'FM999,999,999.99')

"gc current block flush tm(ms)",

TO_CHAR ("gc cr block receive time", 'FM999,999,999.99')

"gc cr block receive tm(ms)",

TO_CHAR ("gc current block receive time", 'FM999,999,999.99')

"gc current block receiv tm(ms)",

TO_CHAR ("gc cr block send time", 'FM999,999,999.99')

"gc cr block send tm(ms)",

TO_CHAR ("gc current block send time", 'FM999,999,999.99')

"gc current block send tm(ms)",

TO_CHAR ("gc current block pin time", 'FM999,999,999.99')

"gc current block pin tm(ms)",

TO_CHAR ("Blocks Served Time", 'FM999,999,999.99')

"Blocks Served tm(ms)",

TO_CHAR ("Blocks Served", 'FM999,999,999.9') "Blocks Served",

TO_CHAR ("Blocks Receive Time", 'FM999,999,999.99')

"Blocks Receive tm(ms)",

TO_CHAR ("Blocks Received", 'FM999,999,999.9') "Blocks Received"

FROM (SELECT SNAP_TIME,

SNAP_TIME_RANGE,

"gc cr blocks served",

"gc current blocks served",

"gc cr blocks received",

"gc current blocks received",

"gcs messages sent",

"ges messages sent",

"gcs msgs received",

"ges msgs received",

"gc blocks lost",

"gc cr block build time" * 10 "gc cr block build time",

"gc cr block flush time" * 10 "gc cr block flush time",

"gc current block flush time" * 10

"gc current block flush time",

"gc cr block send time" * 10 "gc cr block send time",

"gc current block send time" * 10 "gc current block send time",

"gc current block pin time" * 10 "gc current block pin time",

( ( "gc cr blocks served"

+ "gc current blocks served"

+ "gc cr blocks received"

+ "gc current blocks received")

* DB_BLOCK_SIZE

+ ( "gcs msgs received"

+ "ges msgs received"

+ "gcs messages sent"

+ "ges messages sent")

* 200)

/ 1024

"Estd.Interconnect Traffic(KB)",

( "gc cr block build time"

+ "gc cr block flush time"

+ "gc current block flush time"

+ "gc cr block send time"

+ "gc current block send time"

+ "gc current block pin time")

* 10

"Blocks Served Time",

"gc current blocks served" + "gc cr blocks served"

"Blocks Served",

("gc cr block receive time" + "gc current block receive time")

* 10

"Blocks Receive Time",

"gc cr blocks received" + "gc current blocks received"

"Blocks Received",

"gc cr block receive time" "gc cr block receive time",

"gc current block receive time"

FROM ( SELECT SNAP_TIME,

SNAP_TIME_RANGE,

SUM (DECODE (STAT_NAME, 'gc cr blocks served', VALUE))

"gc cr blocks served",

SUM (

DECODE (STAT_NAME,

'gc current blocks served', VALUE))

"gc current blocks served",

SUM (

DECODE (STAT_NAME, 'gc cr blocks received', VALUE))

"gc cr blocks received",

SUM (

DECODE (STAT_NAME,

'gc current blocks received', VALUE))

"gc current blocks received",

SUM (DECODE (STAT_NAME, 'gcs messages sent', VALUE))

"gcs messages sent",

SUM (DECODE (STAT_NAME, 'ges messages sent', VALUE))

"ges messages sent",

SUM (DECODE (STAT_NAME, 'gcs msgs received', VALUE))

"gcs msgs received",

SUM (DECODE (STAT_NAME, 'ges msgs received', VALUE))

"ges msgs received",

SUM (DECODE (STAT_NAME, 'gc blocks lost', VALUE))

"gc blocks lost",

SUM (

DECODE (STAT_NAME, 'gc cr block build time', VALUE))

"gc cr block build time",

SUM (

DECODE (STAT_NAME, 'gc cr block flush time', VALUE))

"gc cr block flush time",

SUM (

DECODE (STAT_NAME,

'gc current block flush time', VALUE))

"gc current block flush time",

SUM (

DECODE (STAT_NAME, 'gc cr block send time', VALUE))

"gc cr block send time",

SUM (

DECODE (STAT_NAME,

'gc current block send time', VALUE))

"gc current block send time",

SUM (

DECODE (STAT_NAME,

'gc current block pin time', VALUE))

"gc current block pin time",

SUM (

DECODE (STAT_NAME,

'gc cr block receive time', VALUE))

"gc cr block receive time",

SUM (

DECODE (STAT_NAME,

'gc current block receive time', VALUE))

"gc current block receive time",

MIN (P.PVALUE) DB_BLOCK_SIZE

FROM (SELECT STAT_NAME,

DECODE (

G1,

1, 'SUB AVG',

SUBSTR (SNAP_TIME,

INSTR (SNAP_TIME, '-') - 1))

SNAP_TIME,

SNAP_TIME SNAP_TIME_RANGE,

VALUE,

VALUE_DIFF

FROM ( SELECT STAT_NAME,

START_TIME || '-' || END_TIME

SNAP_TIME,

ROUND (AVG (NVL (VALUE, 0)), 1) VALUE,

ROUND (AVG (NVL (VALUE_DIFF, 0)), 1)

VALUE_DIFF,

GROUPING (

START_TIME || '-' || END_TIME)

G1,

GROUPING (STAT_NAME) G2

FROM (SELECT STAT_NAME,

TO_CHAR (SNAP_TIME_C1,

'MM.DD HH24:MI')

START_TIME,

TO_CHAR (SNAP_TIME_C2,

'MM.DD HH24:MI')

END_TIME,

DECODE (

SNAP_TIME_C2,

NULL, 0,

(CASE

WHEN VALUE_2 < VALUE_1

THEN

ELSE

VALUE_2 - VALUE_1

END)

/ (EXTRACT (

DAY FROM SNAP_TIME_C2

- SNAP_TIME_C1)

* 86400

+ EXTRACT (

HOUR FROM SNAP_TIME_C2

- SNAP_TIME_C1)

* 3600

+ EXTRACT (

MINUTE FROM SNAP_TIME_C2

- SNAP_TIME_C1)

* 60

+ EXTRACT (

SECOND FROM SNAP_TIME_C2

- SNAP_TIME_C1)))

VALUE,

(CASE

WHEN VALUE_2 < VALUE_1

THEN

ELSE

VALUE_2 - VALUE_1

END)

VALUE_DIFF,

ROW_NUMBER ()

OVER (

PARTITION BY INSTANCE_NUMBER,

STAT_NAME

ORDER BY SNAP_ID)

RNUM,

SNAP_ID,

INSTANCE_NUMBER

FROM ( SELECT SNAP.END_INTERVAL_TIME

SNAP_TIME_C1,

LEAD (

SNAP.END_INTERVAL_TIME)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

STAT_NAME

ORDER BY

SNAP.SNAP_ID)

SNAP_TIME_C2,

STAT.STAT_NAME,

STAT.VALUE VALUE_1,

LEAD (

STAT.VALUE)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

STAT_NAME

ORDER BY

SNAP.SNAP_ID)

VALUE_2,

SNAP.SNAP_ID,

DBI.INSTANCE_NUMBER

FROM ( SELECT DI.DBID,

DI.INSTANCE_NUMBER,

MAX (

DI.STARTUP_TIME)

STARTUP_TIME

FROM DBA_HIST_DATABASE_INSTANCE DI

WHERE DI.DBID = (select dbid from v$database) --변경

AND DI.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경

GROUP BY DI.DBID,

DI.INSTANCE_NUMBER) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_SYSSTAT STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND DBI.DBID =

SNAP.DBID

AND SNAP.SNAP_ID >= :3

AND SNAP.SNAP_ID <= :4

AND SNAP.DBID =

STAT.DBID

AND SNAP.INSTANCE_NUMBER =

STAT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

STAT.SNAP_ID

AND STAT.STAT_NAME IN

('gc blocks lost',

'gc cr block build time',

'gc cr block flush time',

'gc current block flush time',

'gc cr block send time',

'gc current block send time',

'gc current block pin time',

'gc cr blocks served',

'gc current blocks served',

'gc cr blocks received',

'gc current blocks received',

'messages sent',

'messages received',

'gcs messages sent',

'ges messages sent',

'gc cr block receive time',

'gc current block receive time')

ORDER BY SNAP.SNAP_ID)

WHERE SNAP_TIME_C2 <> SNAP_TIME_C1)

WHERE START_TIME IS NOT NULL

AND END_TIME IS NOT NULL

GROUP BY ROLLUP (STAT_NAME,

START_TIME

|| '-'

|| END_TIME))

WHERE NOT (G1 = 1 AND G2 = 1)

UNION ALL

SELECT STAT_NAME,

DECODE (

G1,

1, 'SUB AVG',

SUBSTR (SNAP_TIME,

INSTR (SNAP_TIME, '-') - 1))

SNAP_TIME,

SNAP_TIME SNAP_TIME_RANGE,

VALUE,

VALUE_DIFF

FROM ( SELECT STAT_NAME,

START_TIME || '-' || END_TIME

SNAP_TIME,

ROUND (AVG (NVL (VALUE, 0)), 1) VALUE,

ROUND (AVG (NVL (VALUE_DIFF, 0)), 1)

VALUE_DIFF,

GROUPING (

START_TIME || '-' || END_TIME)

G1,

GROUPING (STAT_NAME) G2

FROM (SELECT STAT_NAME,

TO_CHAR (SNAP_TIME_C1,

'MM.DD HH24:MI')

START_TIME,

TO_CHAR (SNAP_TIME_C2,

'MM.DD HH24:MI')

END_TIME,

DECODE (

SNAP_TIME_C2,

NULL, 0,

ROUND (

(CASE

WHEN VALUE_2 <

VALUE_1

THEN

ELSE

VALUE_2 - VALUE_1

END)

/ (EXTRACT (

DAY FROM SNAP_TIME_C2

- SNAP_TIME_C1)

* 86400

+ EXTRACT (

HOUR FROM SNAP_TIME_C2

- SNAP_TIME_C1)

* 3600

+ EXTRACT (

MINUTE FROM SNAP_TIME_C2

- SNAP_TIME_C1)

* 60

+ EXTRACT (

SECOND FROM SNAP_TIME_C2

- SNAP_TIME_C1)),

1))

VALUE,

(CASE

WHEN VALUE_2 < VALUE_1

THEN

ELSE

VALUE_2 - VALUE_1

END)

VALUE_DIFF,

ROW_NUMBER ()

OVER (

PARTITION BY INSTANCE_NUMBER,

STAT_NAME

ORDER BY SNAP_ID)

RNUM,

SNAP_ID,

INSTANCE_NUMBER

FROM ( SELECT SNAP.END_INTERVAL_TIME

SNAP_TIME_C1,

LEAD (

SNAP.END_INTERVAL_TIME)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

NAME

ORDER BY

SNAP.SNAP_ID)

SNAP_TIME_C2,

STAT.NAME STAT_NAME,

STAT.VALUE VALUE_1,

LEAD (

STAT.VALUE)

OVER (

PARTITION BY DBI.INSTANCE_NUMBER,

NAME

ORDER BY

SNAP.SNAP_ID)

VALUE_2,

SNAP.SNAP_ID,

DBI.INSTANCE_NUMBER

FROM ( SELECT DI.DBID,

DI.INSTANCE_NUMBER,

MAX (

DI.STARTUP_TIME)

STARTUP_TIME

FROM DBA_HIST_DATABASE_INSTANCE DI

WHERE DI.DBID = (select dbid from v$database) --변경

AND DI.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경

GROUP BY DI.DBID,

DI.INSTANCE_NUMBER) DBI,

DBA_HIST_SNAPSHOT SNAP,

DBA_HIST_DLM_MISC STAT

WHERE DBI.DBID = SNAP.DBID

AND DBI.INSTANCE_NUMBER =

SNAP.INSTANCE_NUMBER

AND DBI.DBID =

SNAP.DBID

AND SNAP.SNAP_ID >= :7

AND SNAP.SNAP_ID <= :8

AND SNAP.DBID =

STAT.DBID

AND SNAP.INSTANCE_NUMBER =

STAT.INSTANCE_NUMBER

AND SNAP.SNAP_ID =

STAT.SNAP_ID

AND STAT.NAME IN

('gcs msgs received',

'ges msgs received')

ORDER BY SNAP.SNAP_ID)

WHERE SNAP_TIME_C2 <> SNAP_TIME_C1)

WHERE START_TIME IS NOT NULL

AND END_TIME IS NOT NULL

GROUP BY ROLLUP (STAT_NAME,

START_TIME

|| '-'

|| END_TIME))

WHERE NOT (G1 = 1 AND G2 = 1)

ORDER BY STAT_NAME, SNAP_TIME) V,

(SELECT /*+ NO_MERGE */

VALUE PVALUE

FROM DBA_HIST_PARAMETER DHP, DBA_HIST_SNAPSHOT SNAP

WHERE SNAP.DBID = (select dbid from v$database) --변경

AND SNAP.INSTANCE_NUMBER =(select INSTANCE_NUMBER from v$instance) --변경

AND SNAP.SNAP_ID = :11

AND SNAP.DBID = DHP.DBID

AND SNAP.INSTANCE_NUMBER = DHP.INSTANCE_NUMBER

AND SNAP.SNAP_ID = DHP.SNAP_ID

AND DHP.PARAMETER_NAME = 'db_block_size') P

GROUP BY SNAP_TIME, SNAP_TIME_RANGE))

ORDER BY SNAP_TIME

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

PostgreSQL 인덱스 관리 스크립트 (0)	2019.03.10
PostgreSQL 테이블 상세 정보 조회 스크립트 (0)	2019.03.09
PostgreSQL 테이블별 사이즈 확인 스크립트 (0)	2019.03.08
PostgreSQL LOCK TREE 조회 스크립트 (0)	2019.03.07
오라클 물리적 IO 관리 스크립트 (0)	2019.03.05
오라클 인덱스 상세 조회 스크립트 (0)	2019.03.04
Oracle 수행 시간 기준 SQL 추출 스크립트 (0)	2019.03.03
Oracle Golden Gate 사전 점검 스크립트 (0)	2019.03.02