60 Backup

ryunyryuny 2024. 1. 8. 18:15

# 요약
	■ Backup 용어 1. Whole database Backup 2. Partial database Backup 3. 일관성 있는 Backup 4. 일관성 없는 Backup 5. FULL Backup 6. Incremental Backup ■ 백업대상 파일 # Data File # Control File # Redo Log File # Temp file # Tablespace Logging ALTER TABLESPACE example LOGGING; # Archive Log List

■ Backup 용어

1. Whole database Backup
    - 모든 datafile, control file, redo log file backup
    - Database 가 open 되거나 shutdown 되어 있을 때 backup

2. Partial database Backup
    - 특정한 tablespace 에서 datafile backup
    - control file

3. 일관성 있는 Backup
    - closed backup, cold backup, offline backup
    - 모든 파일은 동일한 checkpoint 정보를 가지고 있다.
    - db 정상적으로 종료한 후 백업을 수행한다.
        shutdown [ normal | transactional | immediate ]
    - noarchive log, archive log mode 다 사용가능하다.
    - noarchive log mode 에 백업 정책은 일관성있는 backup 만 수행해야한다.

4. 일관성 없는 Backup
    - open backup, hot backup, online backup
    - db 운영중에 백업을 할 수 있다.
    - archive log mode 에서만 가능하다.
    - tablespace 레벨로 백업 수행한다.

5. FULL Backup
    - 선택한 파일 모두 데이터 블록 backup

6. Incremental Backup
    - 이전 백업 이후에 변경된 블록에 대해서만 backup
    - RMAN 백업을 수행해야 한다.

■ 백업 대상 파일

# Data File
	SELECT * FROM v$datafile; SELECT * FROM dba_data_files; FILE_NAME TABLESPACE_NAME --------------------------------------------- ---------------- /u01/app/oracle/oradata/ora11g/undotbs01.dbf UNDOTBS /u01/app/oracle/oradata/ora11g/users01.dbf USERS /u01/app/oracle/oradata/ora11g/sysaux01.dbf SYSAUX /u01/app/oracle/oradata/ora11g/system01.dbf SYSTEM /u01/app/oracle/oradata/ora11g/example01.dbf EXAMPLE > 나머지 TEST용 삭제 DROP TABLESPACE oltp_tbs INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE insa_dbs INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE audit_tbs INCLUDING CONTENTS AND DATAFILES;
# Control File
	SELECT * FROM v$controlfile; NAME -------------------------------------------- /u01/app/oracle/oradata/ora11g/control01.ctl
# Redo Log File
	SELECT * FROM v$logfile; SELECT a.group#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1,2; GROUP# MEMBER MB ARCHIVED STATUS ------- ----------------------------------------------------- ---- --------- --------- 1 /u01/app/oracle/fast_recovery_area/ora11g/redo01.log 100 NO INACTIVE 2 /u01/app/oracle/fast_recovery_area/ora11g/redo02.log 100 NO CURRENT 3 /u01/app/oracle/fast_recovery_area/ora11g/redo03.log 100 NO INACTIVE >> 파일 위치 및 용량 변경 ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/ora11g/redo04.log') size 50M; ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/app/oracle/oradata/ora11g/redo05.log') size 50M; ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/app/oracle/oradata/ora11g/redo06.log') size 50M; SELECT * FROM v$log; ALTER SYSTEM SWITCH LOGFILE; ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 2; ALTER DATABASE DROP LOGFILE GROUP 3; ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ora11g/redo01.log') size 50M; ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/ora11g/redo02.log') size 50M; ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/ora11g/redo03.log') size 50M; SELECT * FROM v$log; ALTER SYSTEM SWITCH LOGFILE; ALTER DATABASE DROP LOGFILE GROUP 4; ALTER DATABASE DROP LOGFILE GROUP 5; ALTER DATABASE DROP LOGFILE GROUP 6; SELECT * FROM v$logfile; SELECT a.group#, a.member, b.bytes/1024/1024 mb, b.archived, b.status FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1,2; GROUP# MEMBER MB ARCHIVED STATUS ------- ------------------------------------------ --- --------- --------- 1 /u01/app/oracle/oradata/ora11g/redo01.log 50 NO CURRENT 2 /u01/app/oracle/oradata/ora11g/redo02.log 50 NO INACTIVE 3 /u01/app/oracle/oradata/ora11g/redo03.log 50 NO INACTIVE
# Temp file
	SELECT * FROM dba_temp_files; FILE_NAME TABLESPACE_NAME ------------------------------------------ ---------------- /u01/app/oracle/oradata/ora11g/temp01.dbf TEMP > 나머지 삭제 DROP TABLESPACE insa_temp INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE temp10 INCLUDING CONTENTS AND DATAFILES;
# Tablespace Logging
	SELECT tablespace_name, logging FROM dba_tablespaces; TABLESPACE_NAME LOGGING ------------------------------ --------- SYSTEM LOGGING SYSAUX LOGGING TEMP NOLOGGING USERS LOGGING EXAMPLE NOLOGGING UNDOTBS LOGGING LOGGING MODE : 이 테이블스페이스에 속한 segment를 DML,DDL 작업을 수행하면 redo 정보 생성 NOLOGGING : redo 정보를 생성하지 않는다. # Tablespace Logging Mode로 변경 ALTER TABLESPACE example LOGGING; SELECT tablespace_name, logging FROM dba_tablespaces; TABLESPACE_NAME LOGGING ------------------------------ --------- SYSTEM LOGGING SYSAUX LOGGING TEMP NOLOGGING USERS LOGGING EXAMPLE LOGGING UNDOTBS LOGGING
# Archive Log List
	SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 52 Current log sequence 54

Logical Storage Structures
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/logical-storage-structures.html

Database Concepts

This chapter describes the nature of and relationships among logical storage structures. These structures are created and recognized by Oracle Database and are not known to the operating system.

docs.oracle.com

SQL Processing
https://docs.oracle.com/en/database/oracle/oracle-database/21/tgsql/sql-processing.html#GUID-C7B96C36-3AE9-4120-A511-C4B2992275DD

SQL Tuning Guide

This chapter explains how database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.

docs.oracle.com

DBMS_SQL
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQL.html#GUID-B7B7B40D-9AEC-4CE8-A467-AE0E3A0C6FA7

PL/SQL Packages and Types Reference

The DBMS_SQL package provides an interface to use dynamic SQL to parse any data manipulation language (DML) or data definition language (DDL) statement using PL/SQL.

docs.oracle.com

Oracle Database Instance
https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-728C6BE1-5687-4DC5-B570-D2042C88F935

Database Concepts

This chapter explains the nature of an Oracle database instance, the parameter and diagnostic files associated with an instance, and what occurs during instance creation and the opening and closing of a database.

docs.oracle.com

Managing Archived Redo Log Files
https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-archived-redo-log-files.html#GUID-21A9A3AC-1D90-4848-B3BB-3A9E797547F8

Database Administrator’s Guide

You manage the archived redo log files by completing tasks such as choosing between NOARCHIVELOG or ARCHIVELOG mode and specifying archive destinations.

docs.oracle.com

저작자표시 (새창열림)

'Oracle 11g > Backup & Recovery' 카테고리의 다른 글

65 Backup&Recovery, Archive Log Mode (0)	2024.01.15
64 Backup&Recovery, Archive Log Mode (0)	2024.01.12
63 Backup&Recovery, Noarchive Log, Redo Log File, Temp File (0)	2024.01.11
62 Backup&Recovery, Noarchive Log, Data File, Undo Data File (1)	2024.01.10
61 Backup&Recovery, Noarchive Log, Data File (0)	2024.01.09

'Oracle 11g/Backup & Recovery'의 다른글

현재글60 Backup

Record.

60 Backup

'Oracle 11g > Backup & Recovery' 카테고리의 다른 글

티스토리툴바