[10/14] 정리

Admin2 C7 일부 ~ C11 복습 및 정리 clear

admin1 기본 연습 정리

2 HOST 2DB duplication 정리 next

SQL script인 $ORACLE_HOME/sqlplus/admin/pupbld.sql은 Product와 User Profile table을 만들기 위해서 사용자 SYSTEM으로서 실행될 것이다.

SYS@orcl>select flashback_on from v$database;

FLASHBACK_ON

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

YES

RMAN> list backup of database by file;

List of Datafile Backups

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

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Compressed Tag

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

1 4319 B F A 1201428 2015-10-14:11:07:15 7 1 NO TAG20151014T110715

2 4317 B F A 1201427 2015-10-14:11:07:15 5 1 NO TAG20151014T110715

3 4317 B F A 1201427 2015-10-14:11:07:15 5 1 NO TAG20151014T110715

4 4319 B F A 1201428 2015-10-14:11:07:15 7 1 NO TAG20151014T110715

5 4317 B F A 1201427 2015-10-14:11:07:15 5 1 NO TAG20151014T110715

6 4318 B F A 1201448 2015-10-14:11:07:27 1 1 NO TAG20151014T110715

RMAN> list backup tag=TAG20151014T110715;;

List of Backup Sets

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

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4317 Full 481.88M DISK 00:00:12 2015-10-14:11:07:27

List of Datafiles in backup set 4317

File LV Type Ckp SCN Ckp Time Name

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

2 Full 1201427 2015-10-14:11:07:15 /u01/app/oracle/oradata/orcl/sysaux01.dbf

3 Full 1201427 2015-10-14:11:07:15 /u01/app/oracle/oradata/orcl/undotbs01.dbf

5 Full 1201427 2015-10-14:11:07:15 /u01/app/oracle/oradata/orcl/example01.dbf

Backup Set Copy #1 of backup set 4317

Device Type Elapsed Time Completion Time Compressed Tag

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

DISK 00:00:12 2015-10-14:11:07:27 NO TAG20151014T110715

List of Backup Pieces for backup set 4317 Copy #1

BP Key Pc# Status Piece Name

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

4330 1 AVAILABLE /BACKUP/rman/ORCL_143_1

4331 2 AVAILABLE /BACKUP/rman/ORCL_143_2

4332 3 AVAILABLE /BACKUP/rman/ORCL_143_3

4333 4 AVAILABLE /BACKUP/rman/ORCL_143_4

4334 5 AVAILABLE /BACKUP/rman/ORCL_143_5

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4318 Full 7.75M DISK 00:00:02 2015-10-14:11:07:29

BP Key: 4335 Status: AVAILABLE Compressed: NO Tag: TAG20151014T110715

Piece Name: /BACKUP/rman/ORCL_145_1

List of Datafiles in backup set 4318

File LV Type Ckp SCN Ckp Time Name

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

6 Full 1201448 2015-10-14:11:07:27 /u01/app/oracle/oradata/orcl/rman_tbs.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4319 Full 603.73M DISK 00:00:15 2015-10-14:11:07:30

List of Datafiles in backup set 4319

File LV Type Ckp SCN Ckp Time Name

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

1 Full 1201428 2015-10-14:11:07:15 /u01/app/oracle/oradata/orcl/system01.dbf

4 Full 1201428 2015-10-14:11:07:15 /u01/app/oracle/oradata/orcl/users01.dbf

Backup Set Copy #1 of backup set 4319

Device Type Elapsed Time Completion Time Compressed Tag

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

DISK 00:00:15 2015-10-14:11:07:30 NO TAG20151014T110715

List of Backup Pieces for backup set 4319 Copy #1

BP Key Pc# Status Piece Name

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

4336 1 AVAILABLE /BACKUP/rman/ORCL_144_1

4337 2 AVAILABLE /BACKUP/rman/ORCL_144_2

4338 3 AVAILABLE /BACKUP/rman/ORCL_144_3

4339 4 AVAILABLE /BACKUP/rman/ORCL_144_4

4340 5 AVAILABLE /BACKUP/rman/ORCL_144_5

4341 6 AVAILABLE /BACKUP/rman/ORCL_144_6

4342 7 AVAILABLE /BACKUP/rman/ORCL_144_7

FBDAUSER@orcl>select * from v$diag_info;

INST_ID NAME

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

VALUE

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

1 Diag Enabled

TRUE

1 ADR Base

/u01/app/oracle

1 ADR Home

/u01/app/oracle/diag/rdbms/orcl/orcl

1 Diag Trace

/u01/app/oracle/diag/rdbms/orcl/orcl/trace

1 Diag Alert

/u01/app/oracle/diag/rdbms/orcl/orcl/alert

1 Diag Incident

/u01/app/oracle/diag/rdbms/orcl/orcl/incident

1 Diag Cdump

/u01/app/oracle/diag/rdbms/orcl/orcl/cdump

1 Health Monitor

/u01/app/oracle/diag/rdbms/orcl/orcl/hm

1 Default Trace File

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_31391.trc

1 Active Problem Count

1 Active Incident Count

11 rows selected.

SYS@orcl>select name, internal_check from v$hm_check;

NAME I

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

HM Test Check Y

DB Structure Integrity Check N

CF Block Integrity Check N

Data Block Integrity Check N

Redo Integrity Check N

Logical Block Check Y

Transaction Integrity Check N

Undo Segment Integrity Check N

No Mount CF Check Y

Mount CF Check Y

CF Member Check Y

All Datafiles Check Y

Single Datafile Check Y

Tablespace Check Check Y

Log Group Check Y

Log Group Member Check Y

Archived Log Check Y

Redo Revalidation Check Y

IO Revalidation Check Y

Block IO Revalidation Check Y

Txn Revalidation Check Y

Failure Simulation Check Y

Dictionary Integrity Check N

ASM Mount Check Y

ASM Allocation Check N

ASM Disk Visibility Check Y

ASM File Busy Check Y

27 rows selected.

SYS@orcl>select name from v$hm_check where internal_check='N';

NAME

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

DB Structure Integrity Check

CF Block Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

ASM Allocation Check

8 rows selected.

SYS@orcl>begin

2 dbms_hm.run_check('DB Structure Integrity Check','testrun');

3 end;

4 /

PL/SQL procedure successfully completed.

SYS@orcl>set long 100000

SYS@orcl>set longchunksize 1000

SYS@orcl>set pagesize 1000

SYS@orcl>set linesize 512

SYS@orcl>col DBMS_HM.GET_RUN_REPORT('TESTRUN') for a70

SYS@orcl>select dbms_hm.get_run_report('testrun') from dual;

DBMS_HM.GET_RUN_REPORT('TESTRUN')

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

Basic Run Information

Run Name : testrun

Run Id : 7001

Check Name : DB Structure Integrity Check

Mode : MANUAL

Status : COMPLETED

Start Time : 2015-10-14 15:25:19.663959 +09:00

End Time : 2015-10-14 15:25:19.688590 +09:00

Error Encountered : 0

Source Incident Id : 0

Number of Incidents Created : 0

Input Paramters for the Run

Run Findings And Recommendations

[oracle@lnx04 alert]$ oerr ora 1578

01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)"

// *Cause: The data block indicated was corrupted, mostly due to software

// errors.

// *Action: Try to restore the segment containing the block indicated. This

// may involve dropping the segment and recreating it. If there

// is a trace file, report the errors in it to your ORACLE

// representative.

dbverify

[oracle@lnx04 alert]$ dbv

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Oct 14 15:50:34 2015

Keyword Description (Default)

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

FILE File to Verify (NONE)

START Start Block (First Block of File)

END End Block (Last Block of File)

BLOCKSIZE Logical Block Size (8192)

LOGFILE Output Log (NONE)

FEEDBACK Display Progress (0)

PARFILE Parameter File (NONE)

USERID Username/Password (NONE)

SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)

HIGH_SCN Highest Block SCN To Verify (NONE)

(scn_wrap.scn_base OR scn)

analyze

db_block_checking

db_block_checksum

exp block scaning을 통해 훼손될 블록 감지 full sacn every block

훼손 블록 있을 경우 error 1578 발생

flashback 논리적 손상 복구

dbms_repair 손상 감지 및 복구하는 패키지 desc

rman block media recovery

SYS@orcl>show parameter db_block

NAME TYPE VALUE

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

db_block_buffers integer 0

db_block_checking string FALSE

db_block_checksum string TYPICAL

db_block_size integer 8192

[oracle@lnx04 ~]$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Oct 14 16:06:59 2015

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/users01.dbf

DBVERIFY - Verification complete

Total Pages Examined : 640 data

Total Pages Processed (Data) : 103 table

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 39

Total Pages Failing (Index): 0

Total Pages Processed (Other): 267

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 231 extent를 주는데 안쓰는 공간들이 있다 ( 할당 받고 안쓰는 애들 있다)

Total Pages Marked Corrupt : 0 훼손된게 있다면 여기 개수로 올라감

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 1199272 (0.1199272)

blktrace 블럭이 어떻게 사용되는지 읽히고 있는지 체크한다

/u01을 조사하게 하고 sql로 접속해서 작업을 하면 화면에 찍힌다

option을 통해 작업했던 trace 파일도 만들 수 있다.

gnuplot 홈피를 통해 다운 받아서 2d 3d로 사용량을 표현할 수도 있다.

디스크 성능 체크 벤치마킹시 많이 사용된다 프리웨어

ssd hdd 둘 비교를 할때 유용하게 사용 i/o 횟수가 뜸

SYS@orcl>select * from v$database_block_corruption;

no rows selected

exec뒤를 다 빼고 하면 리스트만 나온다

m 모디파이

c 크리에이티션

'스터디북' 카테고리의 다른 글

[10/14] 실습 - 2 flashback database (0)	2015.10.14
[10/14] 실습 - 1 Flashback Transaction BACKOUT table (0)	2015.10.14
[10/13] admin 기본 연습 (0)	2015.10.13
[10/13] 실습 - 4 Flashback query, versions query, transaction query (0)	2015.10.13
[10/13] 실습 - 3 Backupset의 병렬화 (0)	2015.10.13

선발투수

[10/14] 정리

'스터디북' 카테고리의 다른 글

티스토리툴바

[10/14] 정리

'스터디북' 카테고리의 다른 글

관련글

티스토리툴바