닫힌 백업 (콜드백업; Cold backup)
- 오라클을 셧다운 시칸 상태에서 백업
- 단점 : 백업받은 동안은 DB서비스 불가능.
주의사항
- 모든 파일은 동일한 시점의 것이어야함 (SCN)
- 정상적으로 db 셧다운 시킨상태에서 백업을 받아야함
- 아카이브 모드 , 노아카이브 가능
- 데이터 파일, 컨트롤 파일, 리두로그파일 모두 백업 가능.
순서
1) 백업 받을 파일의 경로 확인 후 DB정상 셧다운
2) 원하는 경로에 파일 복사
3) DB 오픈
=============================================================================================================
**
Recovery 원리
1) recover database
- DB 전체(system, undo 포함) 을 모두검사하여 고침
- 마운트 상태 ( 컨트롤 파일안에 있는 정보를 이용하여 recover 하므로)
2) recover tablespace 테이블스페이스명
- 해당 테이블 스페이스만 복구
- 마운트(system과 같은...), 오픈상태
- 테이블스페이스 오프라인 후 작업
3) recover datafile '테이터파일경로명'
- 해당 데이터파일로 복구
- 마운트, 오픈상태
=============================================================================================================
핫백업(Hot Backup) = 온라인백업
- DB가 운영중인 (Open) 상태에서 백업하는 방법.
- 아카이브 모드에서만 가능.
- 이유 : no아카이브모드에서 오픈백업을 진행하게 된다면(현재는 불가능)
online redo log 을 재사용하게 되어 추후 recovery 가 불가능해질 수 있다.
- DATA FILE 백업 or 테이블스페이스의 모든 data file 백업 가능
- redo logfile backup 불가.
db 운영중에 백업을 진행하므로 redo logfile 은 불가하다.
-> alter database open resetlogs;
단점 : 리두로그의 양이 굉장히 증가하므로 dml 작업이 적은 시간에 백업 진행.
주의사항
- 열린백업은 테이블스페이스 단위로 백업을 수행함
- 반드시 DB는 아키이브 모드
- 데이터파일, 컨트롤 파일만 백업 가능
-리두로그파일은 현재 변경되는 내용이 저장되어지는 중이기 때문 안됨.
순서
1) 백업 받을 테이블스페이스 조회
2) alter talblespace 테이블스페이스명 begin bakcup;
3) 실제 os 명령어로 원하는 경로에 파일 복사
4) alter tablespace 테이블스페이스명 end backup
hot backup 스크립트 생성 쿼리
select 'alter tablespace '||b.name||' begin backup;' ,
'!cp '||a.name||' '|| '&backuppath' ,
'alter tablespace '||b.name||' end backup;'
from v$datafile a,
v$tablespace b
where a.ts# = b.ts#;
1) 컨트롤 파일 위치 확인.
SYS@ORCL AS SYSDBA> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
2) 해당 테이블 스페이서의 데이터 파일 위치 및 이름 확인.
SYS@ORCL AS SYSDBA> SELECT file_id, tablespace_name, file_name
FROM DBA_DATA_FILES;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------ ---------------------------------------------
4 USERS /u01/app/oracle/oradata/orcl/users01.dbf
3 UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf
2 SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf
1 SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf
5 EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf
6 TBS_A /u01/app/oracle/oradata/orcl/tbs_a_01.dbf
7 TBS_A /u01/app/oracle/oradata/orcl/tbs_a_02.dbf
8 TBS_B /u01/app/oracle/oradata/orcl/tbs_b_01.dbf
9 UNDOTBS2 /u01/app/oracle/oradata/orcl/undotbs02.dbf
10 UNDOTBS2 /u01/app/oracle/oradata/orcl/undotbs02_2.dbf
11 TBS_C /u01/app/oracle/oradata/orcl/tbs_c_01.dbf
12 TBS_D /u01/app/oracle/oradata/orcl/tbs_d_01.dbf
13 PBJ_DAT /home/oracle/pbj_dat01.dbf
14 PBJ_TEMP /home/oracle/pbj_temp01.dbf
15 PBJ_DAT_2 /home/oracle/pbj_dat_2_01.dbf
16 MARY_TS_DAT /home/oracle/ts/mary_ts_dat01.dbf
17 DATA01 /home/oracle/ts/DATA01.dbf
18 DATA01 /home/oracle/ts/DATA01_1.dbf
19 ENC_TS /home/oracle/ts/enc_dat.dbf
20 ENC_TS_AES256 /home/oracle/ts/enc_aes256_dat.dbf
21 TBS_SCOTT_IDX /home/oracle/scott_idx01.dbf
22 TBS_PBJ_IDX /home/oracle/pbj_idx01.dbf
23 TBS_PBJ_DAT /home/oracle/pbj_dat_01.dbf
24 PUMP /home/oracle/PUMP_idx01.dbf
25 TBS_TEST /home/oracle/ts/TBS_TEST01.dbf
3) Redo Log 파일들의 위치 및 이름 확인
SYS@ORCL AS SYSDBA> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- --------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO
===========================================================================================================
순서
1) 백업 받을 테이블스페이스 조회
2) alter talblespace 테이블스페이스명 begin bakcup;
3) 실제 os 명령어로 원하는 경로에 파일 복사
4) alter tablespace 테이블스페이스명 end backup
1) 테이블 스페이스를 open 백업을 수행.
SYS@ORCL AS SYSDBA> alter tablespace system begin backup;
2) 테이블스페이스 system 에 속한 data file 을 백업한다.
SYS@ORCL AS SYSDBA> host cp /u01/app/oracle/oradata/orcl/system01.dbf /home/oracle/backup/system01.dbf
3) 현재 백업중인 데이터 파일의 정보 확인.
system 테이블스페이스 file_id = 1
active = 백업 모드 중이다.(백업중)
not active = 백업 모드가 아니다.(백업 하고 있지 않음). = end backup
change# - SCN(System Change Number) 보여줌.
time = 가장 마지막에 begin backup 받은 시간.
SYS@ORCL AS SYSDBA> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------
1 ACTIVE 1.4696E+13 10-MAR-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
6 NOT ACTIVE 0
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
12 NOT ACTIVE 0
13 NOT ACTIVE 0
14 NOT ACTIVE 0
15 NOT ACTIVE 0
16 NOT ACTIVE 0
17 NOT ACTIVE 0
18 NOT ACTIVE 0
19 NOT ACTIVE 0
20 NOT ACTIVE 0
21 NOT ACTIVE 0
22 NOT ACTIVE 0
23 NOT ACTIVE 0
24 NOT ACTIVE 0
25 NOT ACTIVE 0
4) 백업이 완료 되었다면 ( Host cp 명령어가 끝나면) 온라인 백업을 종료. = end backup
SYS@ORCL AS SYSDBA> alter tablespace system end backup;
Tablespace altered.
5) 백업 확인.
SYS@ORCL AS SYSDBA> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------
1 NOT ACTIVE 1.4696E+13 10-MAR-16
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
6 NOT ACTIVE 0
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
10 NOT ACTIVE 0
11 NOT ACTIVE 0
12 NOT ACTIVE 0
13 NOT ACTIVE 0
14 NOT ACTIVE 0
15 NOT ACTIVE 0
16 NOT ACTIVE 0
17 NOT ACTIVE 0
18 NOT ACTIVE 0
19 NOT ACTIVE 0
20 NOT ACTIVE 0
21 NOT ACTIVE 0
22 NOT ACTIVE 0
23 NOT ACTIVE 0
24 NOT ACTIVE 0
25 NOT ACTIVE 0
6) Control 파일 백업.
- data file 만을 백업 하였으므로, control file 은 따로 백업을 진행.
-> 데이터베이스의 구조가 변경될시(테이블스페이스 추가 등... )
컨트롤 파일을 백업을 받아놔야 추가된 부분까지도 복구가 가능.
* control 파일을 백업 받아야 할 경우.
1. create tablespace
2. alter database logfile
3. alter database logfile member
4. alter database logfile group
5. alter database rename file
6. alter database datafile
7. drop tablespace
SYS@ORCL AS SYSDBA> select * from v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl
NO 16384 656
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
NO 16384 656
6-1) 컨트롤 파일 백업
SYS@ORCL AS SYSDBA> alter database backup controlfile to 'control01.ctl' ;
Database altered.
로그.
Thu Mar 10 11:06:04 2016
alter database backup controlfile to 'control01.ctl'
Completed: alter database backup controlfile to 'control01.ctl'
SYS@ORCL AS SYSDBA> alter database backup controlfile to 'control02.ctl';
Database altered.
로그.
Thu Mar 10 11:06:32 2016
alter database backup controlfile to 'control02.ctl'
Completed: alter database backup controlfile to 'control02.ctl'
- 컨트롤 파일 명칭으로 백업 할 경우 바이너리파일로 생성되며, 읽을수가 없음.
- 백업된 컨트롤 파일이 저장되는 경로.
[oracle@DBTEST dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbs
[oracle@DBTEST dbs]$
[oracle@DBTEST dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0
[oracle@DBTEST dbs]$
[oracle@DBTEST dbs]$ ls -al
total 41320
drwxr-x--- 2 oracle oinstall 4096 Mar 10 11:06 .
drwxr-x--- 75 oracle oinstall 4096 Feb 16 09:17 ..
-rw-r----- 1 oracle oinstall 10764288 Mar 10 11:06 control01.ctl
-rw-r----- 1 oracle oinstall 10764288 Mar 10 11:06 control02.ctl
6-2) 컨트롤 파일 재생성 할수 있도로 스크립트를 생성(트레이스를 통해 읽을수 있도록)
SYS@ORCL AS SYSDBA> alter database backup controlfile to trace;
로그 확인
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13819.trc
Completed: alter database backup controlfile to trace
==============================================================
[oracle@DBTEST trace]$ vi /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13819.trc
Trace file /u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13819.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0
System name: Linux
Node name: DBTEST
Release: 2.6.32-573.el6.x86_64
Version: #1 SMP Wed Jul 1 18:23:37 EDT 2015
Machine: x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 13814, image: oracle@DBTEST (TNS V1-V3)
*** 2016-03-10 10:54:05.857
*** SESSION ID:(22.33080) 2016-03-10 10:54:05.857
*** CLIENT ID:() 2016-03-10 10:54:05.857
*** SERVICE NAME:(SYS$USERS) 2016-03-10 10:54:05.857
*** MODULE NAME:(sqlplus@DBTEST (TNS V1-V3)) 2016-03-10 10:54:05.857
*** ACTION NAME:() 2016-03-10 10:54:05.857
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=name_%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="orcl"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/orcl'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
"/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13814.trc" 228L, 10036C 1,1 Top
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_a_01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_a_02.dbf',
'/u01/app/oracle/oradata/orcl/tbs_b_01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs02.dbf',
'/u01/app/oracle/oradata/orcl/undotbs02_2.dbf',
'/u01/app/oracle/oradata/orcl/tbs_c_01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_d_01.dbf',
'/home/oracle/pbj_dat01.dbf',
'/home/oracle/pbj_temp01.dbf',
'/home/oracle/pbj_dat_2_01.dbf',
'/home/oracle/ts/mary_ts_dat01.dbf',
'/home/oracle/ts/DATA01.dbf',
'/home/oracle/ts/DATA01_1.dbf',
'/home/oracle/ts/enc_dat.dbf',
'/home/oracle/ts/enc_aes256_dat.dbf',
'/home/oracle/scott_idx01.dbf',
'/home/oracle/pbj_idx01.dbf',
'/home/oracle/pbj_dat_01.dbf',
'/home/oracle/PUMP_idx01.dbf',
'/home/oracle/ts/TBS_TEST01.dbf'
CHARACTER SET KO16MSWIN949
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/orcl/name_1_1_694916203.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/orcl/name_1_1_899810832.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 49283072 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf'
SIZE 30408704 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp03_4.dbf'
SIZE 524288000 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp03_2.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp03.dbf'
SIZE 30408704 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP4 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp4_2'
SIZE 1048576000 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP4 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp4.dbf'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP5 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp5.dbf'
SIZE 52428800 REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
==============================================================
* 컨트롤파일이 없을 경우.
테스트. - 시나리오 ( trace 백업받은 것으로 복구하기 )
1. 컨트롤 파일을 강제적으로 이동
2. shutdown abort
3. startup
4. alert log 확인
5. 조치
1. 컨트롤 파일 01 을 강제이동.
!mv /u01/app/oracle/oradata/orcl/control01.ctl /home/oracle/ctl_bak/
2. 강제 shutdown
SYS@ORCL AS SYSDBA> shutdown abort;
ORACLE instance shut down.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 14081
Session ID: 22 Serial number: 3310
3. 시작
SYS@ORCL AS SYSDBA> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3003123864 bytes
Database Buffers 268435456 bytes
Redo Buffers 16568320 bytes
ORA-00205: error in identifying control file, check alert log for more info
4. alert log 확인
컨트롤파일을 찾을수 없다는 메시지.
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thu Mar 10 11:20:05 2016
Checker run found 1 new persistent data failures
ORA-205 signalled during: ALTER DATABASE MOUNT...
5. 백업받아놓은 trace 파일을 열어본다.
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13819.trc
STARTUP NOMOUNT - DB의 아카이브 로그 모드에 대한 정보
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE - 로그 파일 에 대한 그룹과 멤버 정보
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE - 테이블스페이스 내용, temportary 없음.
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_a_01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_a_02.dbf',
'/u01/app/oracle/oradata/orcl/tbs_b_01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs02.dbf',
'/u01/app/oracle/oradata/orcl/undotbs02_2.dbf',
'/u01/app/oracle/oradata/orcl/tbs_c_01.dbf',
'/u01/app/oracle/oradata/orcl/tbs_d_01.dbf',
'/home/oracle/pbj_dat01.dbf',
'/home/oracle/pbj_temp01.dbf',
'/home/oracle/pbj_dat_2_01.dbf',
'/home/oracle/ts/mary_ts_dat01.dbf',
'/home/oracle/ts/DATA01.dbf',
'/home/oracle/ts/DATA01_1.dbf',
'/home/oracle/ts/enc_dat.dbf',
'/home/oracle/ts/enc_aes256_dat.dbf',
'/home/oracle/scott_idx01.dbf',
'/home/oracle/pbj_idx01.dbf',
'/home/oracle/pbj_dat_01.dbf',
'/home/oracle/PUMP_idx01.dbf',
'/home/oracle/ts/TBS_TEST01.dbf'
CHARACTER SET KO16MSWIN949 - 케릭터셋
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/orcl/name_1_1_694916203.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/oradata/orcl/name_1_1_899810832.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE - Redo log 사용해 최신의 상태로 복구
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' - tempory tablespace 영역을 재할당
SIZE 49283072 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf'
SIZE 30408704 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp03_4.dbf'
SIZE 524288000 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp03_2.dbf'
SIZE 104857600 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp03.dbf'
SIZE 30408704 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP4 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp4_2'
SIZE 1048576000 REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP4 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp4.dbf'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP5 ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp5.dbf'
SIZE 52428800 REUSE AUTOEXTEND OFF;
6. DB를 중지후. trace 파일 실행
SYS@ORCL AS SYSDBA> shutdown abort
ORACLE instance shut down.
SYS@ORCL AS SYSDBA> @/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_13819.trc
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3003123864 bytes
Database Buffers 268435456 bytes
Redo Buffers 16568320 bytes
Control file created.
Database altered.
Media recovery complete.
System altered.
Database altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
SYS@ORCL AS SYSDBA> select status from v$instance;
STATUS
------------
OPEN
alter system checkpoint;
=======================================================================
SYS@ORCL AS SYSDBA> select * from v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS
--- ---------- --------------
/u01/app/oracle/oradata/orcl/control01.ctl
NO 16384 614
1) 컨트롤파일 강제 이동
!mv /u01/app/oracle/oradata/orcl/control01.ctl /home/oracle/ctl_bak/control01.ctl_bak
2) shutdown
SYS@ORCL AS SYSDBA> shutdown abort
ORACLE instance shut down.
3) 시작
SYS@ORCL AS SYSDBA> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3019901080 bytes
Database Buffers 251658240 bytes
Redo Buffers 16568320 bytes
ORA-00205: error in identifying control file, check alert log for more info
로그 확인
Thu Mar 10 11:48:04 2016
ALTER DATABASE MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27037: unable to obtain file status
4) !cp /home/oracle/ctl_bak/control01.ctl_bak /u01/app/oracle/oradata/orcl/control01.ctl
startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3019901080 bytes
Database Buffers 251658240 bytes
Redo Buffers 16568320 bytes
5)
recover database using backup controlfile
SYS@ORCL AS SYSDBA> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SYS@ORCL AS SYSDBA> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------------- ------------- ------------ ------------ ------------
1 1 625 52428800 512 1 YES
INACTIVE 1.4696E+13 10-MAR-16 1.4696E+13 10-MAR-16
3 1 624 52428800 512 1 YES
INACTIVE 1.4696E+13 10-MAR-16 1.4696E+13 10-MAR-16
2 1 626 52428800 512 1 NO
CURRENT 1.4696E+13 10-MAR-16 2.8147E+14
=> resetlog 이므로 이전까지 쌓인 아카이브, redo 필요가 없어짐 ( scn 초기 로 재설정 되므로)
==================================================================================
* 컨트롤파일이 저장된 모든 디스크가 장애가 발생시.
마지막 온라인 백업 파일을 이용한 완전 복구작업.
1.) 백업파일 확인.
[oracle@DBTEST orcl]$ cd /home/oracle/arch
[oracle@DBTEST arch]$ ls
control01.ctl system01.dbf tbs_c_01.dbf temp03.dbf temp05.dbf temp5.dbf users01.dbf
example01.dbf tbs_a_01.dbf tbs_d_01.dbf temp03_2.dbf temp06.dbf undotbs01.dbf
haksa01.dbf tbs_a_02.dbf temp01.dbf temp03_4.dbf temp07.dbf undotbs02.dbf
sysaux01.dbf tbs_b_01.dbf temp02.dbf temp04.dbf temp4.dbf undotbs02_2.dbf
.
2)장애 발생시킴 , 컨트롤파일 & 데이터 파일 삭제
[oracle@DBTEST orcl]$ rm control01.ctl
[oracle@DBTEST orcl]$ rm system01.dbf
[oracle@DBTEST orcl]$ rm temp01.dbf
[oracle@DBTEST orcl]$ rm users01.dbf
3) shutdown
SYS@ORCL AS SYSDBA> shutdown abort
ORACLE instance shut down.
SYS@ORCL AS SYSDBA> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3019901080 bytes
Database Buffers 251658240 bytes
Redo Buffers 16568320 bytes
ORA-00205: error in identifying control file, check alert log for more info
4) 문제가 발생된 모든 데이터파일 및 컨트롤 파일 카피.
[oracle@DBTEST arch]$ cp ./system01.dbf //u01/app/oracle/oradata/orcl/
[oracle@DBTEST arch]$ cp ./temp01.dbf /u01/app/oracle/oradata/orcl/
[oracle@DBTEST arch]$ cp ./users01.dbf /u01/app/oracle/oradata/orcl/
[oracle@DBTEST arch]$ cp ./control01.ctl /u01/app/oracle/oradata/orcl/
5. 마운트
SYS@ORCL AS SYSDBA> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 3290345472 bytes
Fixed Size 2217832 bytes
Variable Size 3019901080 bytes
Database Buffers 251658240 bytes
Redo Buffers 16568320 bytes
Database mounted.
6. recover
AUTO 키워드로 - 모든 아카이브 파일을 적용 시킴.
=> 에러. name_1_627_899810832.arc 아카이브 파일이 해당 경로에 존재하지 않음.
=> 아카이브파일에 저장되어 있지 않고 current 리두로그파일에 존재하기 때문.
SYS@ORCL AS SYSDBA> recover database using backup controlfile
ORA-00279: change 14695593995640 generated at 03/10/2016 12:07:05 needed for
thread 1
ORA-00289: suggestion : /u01/app/oracle/oradata/orcl/name_1_627_899810832.arc
ORA-00280: change 14695593995640 for thread 1 is in sequence #627
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
Log applied.
Media recovery complete.
SYS@ORCL AS SYSDBA> alter database open ;
Database altered.
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA>
SYS@ORCL AS SYSDBA> select status from v$instance;
STATUS
------------
OPEN
'Oracle > backup' 카테고리의 다른 글
| RMAN 완전복구/불완전복구 (0) | 2022.01.26 |
|---|---|
| 아카이브 모드에서 완전복구/불완전복구 (0) | 2022.01.26 |
| RMAN (Recovery Manager) (0) | 2022.01.26 |
| BCT(Block change tracking) (0) | 2022.01.26 |