| # 요약 | |
| ■ 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 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
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 |