반응형
1. 백업 전 SCN
| ### controlfile SCN select checkpoint_change# from v$database; CHECKPOINT_CHANGE# ------------------ 28450658 ### Data file SCN select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# ---------------------------------------------------------------------------------------------------- ------------------ /DBMS/ORACLE/oradata/ORCL19C/system01.dbf 28450658 /DBMS/ORACLE/oradata/ORCL19C/sysaux01.dbf 28450658 /DBMS/ORACLE/oradata/ORCL19C/undotbs01.dbf 28450658 /DBMS/ORACLE/oradata/ORCL19C/users01.dbf 28450658 /DBMS/ORACLE/oradata/ORCL19C/ts_test01.dbf 28450658 |
2. 테이블 생성 & 데이터 삽입
| ### 유저생성 create user back_test identified by "oracle" default tablespace TS_TEST; grant connect, resource to back_test; ALTER USER back_test QUOTA UNLIMITED ON TS_TEST ; ### . 테이블 생성 CREATE TABLE back_test.test_backup ( id NUMBER PRIMARY KEY, name VARCHAR2(100), created_at DATE )tablespace TS_TEST; ### 데이터 삽입 BEGIN FOR i IN 1 .. 100 LOOP INSERT INTO back_test.test_backup ( id, name, created_at ) VALUES ( i, 'User_' || TO_CHAR(i), SYSDATE - MOD(i, 365) ); END LOOP; COMMIT; END; select count(*) from back_test.TEST_BACKUP; COUNT(*) ---------- 100 |
3. begin backup + 데이터 insert
| ### Control file SCN SQL> select checkpoint_change# from v$database; CHECKPOINT_CHANGE# ------------------ 28450658 -> 그대로임 ### Data file SCN SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# ---------------------------------------------------------------------------------------------------- ------------------ /DBMS/ORACLE/oradata/ORCL19C/system01.dbf 28473487 /DBMS/ORACLE/oradata/ORCL19C/sysaux01.dbf 28473487 /DBMS/ORACLE/oradata/ORCL19C/undotbs01.dbf 28473487 /DBMS/ORACLE/oradata/ORCL19C/users01.dbf 28473487 /DBMS/ORACLE/oradata/ORCL19C/ts_test01.dbf 28473487 -> datafile수준에서의 SCN고정됨 ❗ 전체 데이터베이스 수준의 checkpoint (global checkpoint)는 아님 대신 각 데이터파일 수준의 checkpoint가 발생합니다. ### Table Row Count SQL> select count(*) from back_test.TEST_BACKUP; COUNT(*) ---------- 300 -> 200개 더 넣음 ### Swtich log SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; -rw-r-----. 1 oracle dba 1024 5월 22 09:34 1_1645_1156688778.dbf -rw-r-----. 1 oracle dba 25789952 5월 22 09:34 1_1644_1156688778.dbf -rw-r-----. 1 oracle dba 6144 5월 22 09:34 1_1646_1156688778.dbf ### CheckPoint 발생 select checkpoint_change# from v$database; CHECKPOINT_CHANGE# ------------------ 28473805 SQL> ALTER SYSTEM CHECKPOINT; -> 체크포인트 까지 시켜서 data buffer까지 비움 System altered. SQL> select checkpoint_change# from v$database; CHECKPOINT_CHANGE# ------------------ 28473846 |
4. 추가로 data insert + endbackup
| ### DATA FILE COPY cp -R oradata oradata_bak ### 100건 insert select count(*) from back_test.TEST_BACKUP; COUNT(*) ---------- 400 ### end backup alter database end backup; ### switch log alter system switch logfile; alter system switch logfile; alter system switch logfile; ### 100건 더 insert SQL> select count(*) from back_test.TEST_BACKUP; COUNT(*) ---------- 500 |
5. Controlfile backup
| ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/DBMS/ORACLE/work/con_bak.sql' REUSE NORESETLOGS; |
장애 발생 ( ontrolfile 유실, redolog 파일 유실, Archive 일부유실)
1. copy 했던 datafile 로 복구
| mv oradata_bak oradata |
2. 컨트롤 파일 재생성
| STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "ORCL19C" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/DBMS/ORACLE/oradata/ORCL19C/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/DBMS/ORACLE/oradata/ORCL19C/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/DBMS/ORACLE/oradata/ORCL19C/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/DBMS/ORACLE/oradata/ORCL19C/system01.dbf', '/DBMS/ORACLE/oradata/ORCL19C/sysaux01.dbf', '/DBMS/ORACLE/oradata/ORCL19C/undotbs01.dbf', '/DBMS/ORACLE/oradata/ORCL19C/users01.dbf', '/DBMS/ORACLE/oradata/ORCL19C/ts_test01.dbf' CHARACTER SET AL32UTF8; SELECT status, database_status, instance_name 2 FROM v$instance; STATUS DATABASE_STATUS INSTANCE_NAME ------------ ----------------- ---------------- MOUNTED ACTIVE ORCL19C -> 생성하니 바로 MOUNT로 올라감, |
Oracle 버전CREATE CONTROLFILE 실행 후,
| 11g 이하 | NOMOUNT 상태 유지 | 수동으로 ALTER DATABASE MOUNT 필요 |
| 12c~19c | 자동으로 MOUNT 상태로 진입 | 기본 동작처럼 보임 |
3.Recover
| SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01195: online backup of file 1 needs more recovery to be consistent ORA-01110: data file 1: '/DBMS/ORACLE/oradata/ORCL19C/system01.dbf' RECOVER DATABASE USING BACKUP CONTROLFILE; SQL> RECOVER DATABASE USING BACKUP CONTROLFILE; ORA-00279: change 28473487 generated at 05/22/2025 09:29:23 needed for thread 1 ORA-00289: suggestion : /DBMS/ORACLE/oraarch/1_1644_1156688778.dbf ORA-00280: change 28473487 for thread 1 is in sequence #1644 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/DBMS/ORACLE/oraarch/1_1644_1156688778.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory |
4. 아카이브 최소한 어디까지 있어야 복구 되는지 확인
| RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; ORA-00279: change 28474037 generated at 05/22/2025 09:42:36 needed for thread 1 ORA-00289: suggestion : /DBMS/ORACLE/oraarch/1_1650_1156688778.dbf ORA-00280: change 28474037 for thread 1 is in sequence #1650 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. 1_1644_1156688778.dbf -> begin이후 체크포인트가 찍힌 최소한의 아카이브로 DB가 기동됨 |
5. Table count
| select count(*) from back_test.TEST_BACKUP; COUNT(*) ---------- 400 |
※결론
-. Tibero는 endbackup 이후 Ahivelog까지 필요한데, 오라클은
begin이후에 최소한의 Archivelog(체크포인트가 찍혀진) 있으면 거기까지 복구가 된다.
반응형
'Database > Oracle' 카테고리의 다른 글
| [ORACLE] SID(DBNAME)변경 작업 (0) | 2023.12.01 |
|---|---|
| [ORACLE] UNIQUE INDEX, UNIQUE Constraint 동작 (0) | 2023.11.16 |
| [ORACLE] ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다. (0) | 2023.07.06 |
| [ORACLE] 특정 IP 차단, 접속 (0) | 2023.07.04 |
| [ORACLE] sqltrace (0) | 2023.06.30 |