ArchiveMode 에서의 Backup & Recovery-
기초
Archive log mode 에서의 Backup과 Recovery
- BACKUP
- Cold, Closed DB BACKUP, Offline
- Closed DB(Cold, Whole)/ Offilne
- 일관된 DB가 필요
- DB를 종료(정상종료)하고 전체 파일을 백업
- Archivelog 는 data + control
- No archive log 에서는 data + control +redo
- 모든 파일(datafile)은 동일한 시점의 Checkpoint SCN을 갖는다.
- 단점 : 백업을 받는 동안 DB를 사용할 수 없다.
- partial
- 데이터베이스 일부분
- HOT, Open DB BACKUP, Online : DB가 운영중일 때 백업
- Hot backup / online backup
- DB 운영중에 받는 백업
- datafile과 control file만 가능
- datafile 백업 : 반드시 백업모드로 변경하고 백업하고 백업모드를 해제한다.
- SQL> alter tablespace USERS begin backup;
- recovery 할때 첫번째 redolog파일의 시점을 알기 위해 datfile을 cold 해준다.
- block단위의 redo entry가 생성된다.
- cp -av .....user01.dbf /home/oracle/backup/arch/cold
- SQL> alter tablespace USERS end backup;
- v$backup 의 status : ACTIVE(backup mode), 파일이 백업모드에 있을 때 강제 종료 됬다면 다음 시작시에 복구 대상이 된다.
- Full
- 100MB DB이면 100MB짜리 Backup
- Incremental
- 변경된 부분만 Backup
- Backup을 빨리 할 수 있다.
- RECOVERY
- Closed DB recovery, offline recovery
- MOUNT 에서 하는 Recovery
- Critical한 file의 손실이 일어났을때 진행
- system, control, undo, checkpoint가 발생하지 않은 current redo
- 대부분의 datafile의 유실
- Open DB recovery, Online recovery
- DB가 Opne된 상태해서 하는 Recovery
- datafile이 깨졌을때
- tempfile은 아예 recovery대상이 아니다
- Complete Recovery
- 유저가 변경한 모든 데이터를 복구
- user data의 유실이 하나도 없는 복구
- 장애 시점까지 모두 복구
- Incomplete Recovery(offline recovery)
- 과거의 특정 시점까지 복구
- 잘못된 작업(tx, 테이블스페이스 삭제, 테이블 삭제)의 바로 전(time based recovery : SQL> recover database until time ......;)
- backup controlfile(예전)을 가지고 복구를 진행할 경우
- DATA GUARD
- 다른 곳에 똑같이 운영하는 DB
복구 실습
- logmode변경
- backup
- datafile_system
- datafile_users_usingdb
- datafile_users_startup
- datafile_nobackup
- datafile_newlocate
- datafile_part
- alldatafiles
- data01 tablespace 의 장애가 발생, 원래 위치로 복구할 수 없는 상황에서 data file 의 이름을 rename 해서 복구
=============archive log mode 변경하기=============
SYS@orcl2> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Current log sequence 10
==================================================================================================SYS@orcl2> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileorcl2.ora
-- spfile을 안 쓰고 있는 경우
SYS@orcl2> create spfile from pfile;
==================================================================================================
SYS@orcl2> alter system set control_files = '/u01/app/oracle/orcl2/control01.ctl'
scope=spfile;
[orcl2:~]$ mkdir arch1
[orcl2:~]$ mkdir arch2
SYS@orcl2> show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
..............
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
-- 내가 지정한 위치에 백업을 정할 수 있다.
SYS@orcl2> alter system set log_archive_dest_1="location=/home/oracle/arch1 mandatory"
scope=spfile;
System altered.
SYS@orcl2> alter system set log_archive_dest_2="location=/home/oracle/arch2 optional"
scope=spfile;
System altered.
SYS@orcl2> alter system set log_archive_format='arch_%t_%s_%r.arc' scope=spfile;
System altered.
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> alter database archivelog;
Database altered.
SYS@orcl2> select group#, sequence#, status, archived, members, bytes/1024/1024 mb
from v$log;
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 16 CURRENT NO 1 50
2 14 INACTIVE YES 1 50
3 15 INACTIVE YES 1 50
SYS@orcl2> save log
Created file log.sql
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/arch2
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SYS@orcl2> create table hr.t1 as select * from hr.employees;
Table created.
SYS@orcl2> select e.tablespace_name, f.file_name
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name = 'T1';
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> save ext
Created file ext.sql
SYS@orcl2> alter system switch logfile;
System altered.
SYS@orcl2> save switch
Created file switch.sql
SYS@orcl2> col destination format a40
SYS@orcl2> select destination, binding, status from v$archive_dest;
DESTINATION BINDING STATUS
---------------------------------------- --------- ---------
/home/oracle/arch1 MANDATORY VALID
/home/oracle/arch2 OPTIONAL VALID
OPTIONAL INACTIVE
OPTIONAL INACTIVE
.........
31 rows selected.
-- MANDATORY : 아카이브가 완료되어야 v$log에 YES로 표시하겠다.
-- Online log 가 재사용되기전에 반드시 성공적으로 경로에 아카이브 로그를 기록하도록 지시한다
-- OPTRIONAL : 필수가 아닌 선택을 의미
SYS@orcl2> alter system archive log current;
System altered.
=====================archived _backup_COLD==========================
vi db_list.sql
set echo off
set pages 0
set head off
set feed off
create pfile from spfile;
spool orcl2_backup.sh
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$controlfile
union all
select 'cp -av ' || name||' /home/oracle/backup/arch/cold'
from v$datafile
union all
select 'cp -av ' || member ||' /home/oracle/backup/arch/cold'
from v$logfile;
select 'cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold' from dual;
spool off
set feedback on
set head on
set pages 100
set echo o
SYS@orcl2> @db_list
cp -av /u01/app/oracle/orcl2/control01.ctl /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/system01.dbf /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/sysaux01.dbf /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/undotbs01.dbf /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/users01.dbf /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/example01.dbf /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/redo03.log /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/redo02.log /home/oracle/backup/arch/cold
cp-av /u01/app/oracle/orcl2/redo01.log /home/oracle/backup/arch/cold
cp -av $ORACLE_HOME/dbs/initorcl2.ora /home/oracle/backup/arch/cold
[orcl2:~]$ chmod 755 orcl2_backup.sh
SYS@orcl2> select tablespace_name, logging from dba_tablespaces;
TABLESPACE_NAME LOGGING
------------------------------ ---------
SYSTEM LOGGING
SYSAUX LOGGING
UNDOTBS1 LOGGING
TEMP NOLOGGING
USERS LOGGING
EXAMPLE NOLOGGING
6 rows selected.
-- NOLOGING은 얘가 하는 작업이 redo를 만들지 않겠다는 뜻.
SYS@orcl2> alter tablespace example logging;
Tablespace altered.
SYS@orcl2> select tablespace_name, logging from dba_tablespaces;
TABLESPACE_NAME LOGGING
------------------------------ ---------
SYSTEM LOGGING
SYSAUX LOGGING
UNDOTBS1 LOGGING
TEMP NOLOGGING
USERS LOGGING
EXAMPLE LOGGING
6 rows selected.
SYS@orcl2> @log
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-- log switch가 발생하면서 archive가 다음으로 넘어간다.
-- 콜드백업을 받으면 이전 아카이브가 필요없어지므로 아카이브를 삭제한다.
[orcl2:arch1]$ rm *
[orcl2:arch2]$ rm *
[orcl2:~]$ . orcl2_backup.sh
=====================archived_backup_HOT==========================
<<1>>
SYS@orcl2> select a.file#, a.name,a.checkpoint_change#, b.status, b.change#, b.time
from v$datafile a, v$backup b
where a.file#=b.file#;
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ ------------------ ---------- -------
1 /u01/app/oracle/orcl2/system01 951692 NOT ACTIVE 0
.dbf
2 /u01/app/oracle/orcl2/sysaux01 951692 NOT ACTIVE 0
.dbf
3 /u01/app/oracle/orcl2/undotbs0 951692 NOT ACTIVE 0
1.dbf
4 /u01/app/oracle/orcl2/users01. 951692 NOT ACTIVE 0
dbf
5 /u01/app/oracle/orcl2/example0 951692 NOT ACTIVE 0
1.dbf
-- CHANGE# : 프리징 했을때 checkpoint number
-- STATUS : backup이 active한지 아닌지 즉 아직 backup mode에 있지 않다는 것이다.
SYS@orcl2> save backup
Created file backup.sql
SYS@orcl2> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
951692
-- 컨트롤파일에 쓰여있는 현재 checkpoint_number.
SYS@orcl2> save db
Created file db.sql
SYS@orcl2> alter database begin backup;
Database altered.
-- 지금 부터는 database전체 파일이 cold된 상황
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952172 ACTIVE 952172 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 ACTIVE 952172 15-APR-19
1.dbf
-- 얼려진 checkpoint_change#가 출력된다.
-- status 가 전부 active된것을 확인 할 수 있다.
SYS@orcl2>
!cp -av /u01/app/oracle/orcl2/*.dbf /home/oracle/backup/arch/hot
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/arch/hot/example01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/arch/hot/sysaux01.dbf'
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/arch/hot/system01.dbf'
`/u01/app/oracle/orcl2/temp01.dbf' -> `/home/oracle/backup/arch/hot/temp01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/arch/hot/undotbs01.dbf'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/arch/hot/users01.dbf'
SYS@orcl2>@backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952172 ACTIVE 952172 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 ACTIVE 952172 15-APR-19
1.dbf
SYS@orcl2> alter database end backup;
Database altered.
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952172 NOT ACTIVE 952172 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
-- 백업 완료(일관성 있는 백업은 아니다)
<<2>>
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952172 NOT ACTIVE 952172 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
SYS@orcl2> alter tablespace users begin backup;
Tablespace altered.
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952657 ACTIVE 952657 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
SYS@orcl2> ! cp -av /u01/app/oracle/orcl2 /home/oracle/backup/arch/hot/
`/u01/app/oracle/orcl2' -> `/home/oracle/backup/arch/hot/orcl2'
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/backup/arch/hot/orcl2/redo01.log'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/backup/arch/hot/orcl2/redo03.log'
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/example01.dbf'
`/u01/app/oracle/orcl2/temp01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/temp01.dbf'
`/u01/app/oracle/orcl2/redo04.log' -> `/home/oracle/backup/arch/hot/orcl2/redo04.log'
`/u01/app/oracle/orcl2/afiedt.buf' -> `/home/oracle/backup/arch/hot/orcl2/afiedt.buf'
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/system01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/sysaux01.dbf'
`/u01/app/oracle/orcl2/control03.ctl' -> `/home/oracle/backup/arch/hot/orcl2/control03.ctl'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/users01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/arch/hot/orcl2/undotbs01.dbf'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/backup/arch/hot/orcl2/redo02.log'
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/backup/arch/hot/orcl2/control01.ctl'
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952657 ACTIVE 952657 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
SYS@orcl2> alter tablespace users end backup;
Tablespace altered.
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 952172 NOT ACTIVE 952172 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 952657 NOT ACTIVE 952657 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 952172 NOT ACTIVE 952172 15-APR-19
1.dbf
=======================CONTROL FILE의 ONLINE BACKUP=======================
SYS@orcl2> alter database backup controlfile to '/home/oracle/backup/arch/hot/control.bak';
Database altered.
-- binary로 밭는다.
-- OS의 cp는 쓸 수 없다.
-- 이 명령어로만 백업을 할 수 있다.
-- db의 구조정보와 동기화정보가 있다. 하지만 백업의 동기화정보는 의미가 없다 그러나 현재 DB의 컨트롤 파일이 전부 삭제되어있다면 이로부터 recovery가 가능하다.
SYS@orcl2> !ls -al /home/oracle/backup/arch/hot
total 1468040
drwxr-xr-x 3 oracle oinstall 4096 Apr 15 14:21 .
drwxr-xr-x 4 oracle oinstall 4096 Apr 15 13:41 ..
-rw-r----- 1 oracle dba 9748480 Apr 15 14:21 control.bak
-rw-r----- 1 oracle dba 104865792 Apr 15 14:01 example01.dbf
drwxr-x--- 2 oracle oinstall 4096 Apr 15 09:48 orcl2
-rw-r----- 1 oracle dba 534781952 Apr 15 14:01 sysaux01.dbf
-rw-r----- 1 oracle dba 713039872 Apr 15 14:01 system01.dbf
-rw-r----- 1 oracle dba 30416896 Apr 15 12:10 temp01.dbf
-rw-r----- 1 oracle dba 110108672 Apr 15 14:01 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Apr 15 14:01 users01.dbf
==================CONTROL FILE의 ONLINE BACKUP (TEXT FILE, SQL Script file)===================
SYS@orcl2> alter database backup controlfile to trace;
Database altered.
SYS@orcl2> 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/orcl2/orcl2
1 Diag Trace /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2
_ora_3640.trc
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
SYS@orcl2> !cp -av /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_3640.trc /home/oracle/control.sql
`/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_3640.trc' -> `/home/oracle/control.sql'
-- 컨트롤 파일을 재생성할 수 있는 스크립트 형태로 백업 한 것.
==================================================================================================
SYS@orcl2> @switch : alter system switch logfile;
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- --------------- --- ---------- ----------
1 16 INACTIVE YES 1 50
2 17 ACTIVE YES 1 50
3 18 CURRENT NO 1 50
SYS@orcl2> !ls arch1
arch_1_17_1005236600.arc
SYS@orcl2> !ls arch2
arch_1_17_1005236600.arc
SYS@orcl2> @switch
SYS@orcl2> !ls arch1
arch_1_17_1005236600.arc arch_1_18_1005236600.arc
SYS@orcl2> !ls arch2
arch_1_17_1005236600.arc arch_1_18_1005236600.arc
=======================장애 복구 : datafile : system=======================
-- critical 한 file이기 때문에 mount상태에서 복구 진행 - offline 복구
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> ! rm /u01/app/oracle/orcl2/system01.dbf
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/orcl2/system01.dbf'
SYS@orcl2> select status from v$instance;
STATUS
---------------
MOUNTED
SYS@orcl2>
select r.file#, d.name, online_status, error, change#, time
from v$recover_file r, v$datafile d
where r.file# = d.file#;
-- 장애난 파일 정보
FILE# NAME ONLINE_ ERROR CHANGE# TIME
---------- ------------------------------ ------- ------------------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 ONLINE FILE NOT FOUND 0
.dbf
SYS@orcl2> save recover_file
Created file recover_file.sql
SYS@orcl2> select * from v$recovery_log;
-- 리커버리하는데 필요한 log file정보( archive log file가 몇번이 필요한지)
SYS@orcl2> save recover_log
Created file recover_log.sql
SYS@orcl2>
!cp -av /home/oracle/backup/arch/hot/system01.dbf /u01/app/oracle/orcl2/system01.dbf
`/home/oracle/backup/arch/hot/system01.dbf' -> `/u01/app/oracle/orcl2/system01.dbf'
SYS@orcl2> @recover_file
FILE# NAME ONLINE_ ERROR CHANGE# TIME
---------- ------------------------------ ------- ------------------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 ONLINE 952172 15-APR-19
.dbf
SYS@orcl2> recover tablespace system;
Media recovery complete.
SYS@orcl2> @recover_file
no rows selected
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> select status from v$instance;
STATUS
---------------
OPEN
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- --------------- --- ---------- ----------
1 19 CURRENT NO 1 50
2 17 INACTIVE YES 1 50
3 18 INACTIVE YES 1 50
=======================장애 복구 : user 테이블스페이스 복구2=======================
--DB가 내려가 있던 상태에서 DB startup 시에 users tablespace 장애로부터 복구
-- control file이 멀쩡하기 때문에 DB가 startup이 가능
-- user 테이블 스페이스를 offline으로 변경해야 startup 이 된다.
SYS@orcl2> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS /u01/app/oracle/orcl2/users01.dbf
UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf
SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf
SYSTEM /u01/app/oracle/orcl2/system01.dbf
EXAMPLE /u01/app/oracle/orcl2/example01.dbf
SYS@orcl2> create tablespace insa_tbs datafile '/u01/app/oracle/orcl2/insa_tbs01.dbf' size 10m;
Tablespace created.
SYS@orcl2> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
USERS /u01/app/oracle/orcl2/users01.dbf
UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf
SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf
SYSTEM /u01/app/oracle/orcl2/system01.dbf
EXAMPLE /u01/app/oracle/orcl2/example01.dbf
INSA_TBS /u01/app/oracle/orcl2/insa_tbs01.dbf
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/insa_tbs01.dbf
SYS@orcl2> stratup
SP2-0042: unknown command "stratup" - rest of line ignored.
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/app/oracle/orcl2/insa_tbs01.dbf'
SYS@orcl2> alter database datafile '/u01/app/oracle/orcl2/insa_tbs01.dbf' offline drop;
Database altered.
SYS@orcl2> select name, status from v$datafile;
NAME STATUS
------------------------------------------------------------ ---------------
/u01/app/oracle/orcl2/system01.dbf SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE
/u01/app/oracle/orcl2/users01.dbf ONLINE
/u01/app/oracle/orcl2/example01.dbf ONLINE
/u01/app/oracle/orcl2/insa_tbs01.dbf OFFLINE
6 rows selected.
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.
SYS@orcl2> select name, status from v$datafile;
NAME STATUS
------------------------------------------------------------ ---------------
/u01/app/oracle/orcl2/system01.dbf SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf ONLINE
/u01/app/oracle/orcl2/users01.dbf ONLINE
/u01/app/oracle/orcl2/example01.dbf ONLINE
=======================장애 복구 : user 테이블스페이스 복구1=======================
-- DB 운영 중에 user 테이블스페이스 장애로부터 복구
-- non critical한 file
SYS@orcl2> @backup
FILE# NAME CHECKPOINT_CHANGE# STATUS CHANGE# TIME
---------- ------------------------------ ------------------ --------------- ---------- ---------
1 /u01/app/oracle/orcl2/system01 916672 NOT ACTIVE 916672 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 916672 NOT ACTIVE 916672 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 916672 NOT ACTIVE 916672 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. 916769 NOT ACTIVE 916769 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 916672 NOT ACTIVE 916672 15-APR-19
1.dbf
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 ACTIVE YES 1 50
2 11 CURRENT NO 1 50
3 9 INACTIVE YES 1 50
SYS@orcl2> select sequence#, name from v$archived_log;
SEQUENCE# NAME
---------- ------------------------------------------------------------
8 /home/oracle/arch1/arch_1_8_1005236600.arc
8 /home/oracle/arch2/arch_1_8_1005236600.arc
9 /home/oracle/arch1/arch_1_9_1005236600.arc
9 /home/oracle/arch2/arch_1_9_1005236600.arc
10 /home/oracle/arch1/arch_1_10_1005236600.arc
10 /home/oracle/arch2/arch_1_10_1005236600.arc
6 rows selected.
SYS@orcl2> create table hr.test1(id number) tablespace users;
Table created.
SYS@orcl2> insert into hr.test1(id) values(1);
1 row created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- --------------- --- ---------- ----------
1 10 ACTIVE YES 1 50
2 11 CURRENT NO 1 50
3 9 INACTIVE YES 1 50
SYS@orcl2> select sequence#, name, first_change#, next_change# from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# NEXT_CHANGE#
---------- ------------------------------------------------------------ ------------- ------------
8 /home/oracle/arch1/arch_1_8_1005236600.arc 892445 895373
8 /home/oracle/arch2/arch_1_8_1005236600.arc 892445 895373
9 /home/oracle/arch1/arch_1_9_1005236600.arc 895373 915915
9 /home/oracle/arch2/arch_1_9_1005236600.arc 895373 915915
10 /home/oracle/arch1/arch_1_10_1005236600.arc 915915 916906
10 /home/oracle/arch2/arch_1_10_1005236600.arc 915915 916906
6 rows selected.
SYS@orcl2> !ls /home/oracle/arch1/
arch_1_10_1005236600.arc
SYS@orcl2> !ls /home/oracle/arch2/
arch_1_10_1005236600.arc
SYS@orcl2> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id
and e.segment_name = 'TEST1';
FILE_NAME
---------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> select * from hr.test1;
ID
----------
1
SYS@orcl2> create table hr.test2 tablespace users as select * from hr.employees;
create table hr.test2 tablespace users as select * from hr.employees
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/orcl2/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> select a.file#,b.name,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- -------------------- --------------- ------------------
1 SYSTEM SYSTEM 916672
2 SYSAUX ONLINE 916672
3 UNDOTBS1 ONLINE 916672
4 USERS ONLINE 916769
5 EXAMPLE ONLINE 916672
SYS@orcl2> ! ls /u01/app/oracle/orcl2/users01.dbf
ls: /u01/app/oracle/orcl2/users01.dbf: No such file or directory
SYS@orcl2> alter tablespace users offline immediate;
Tablespace altered.
SYS@orcl2> !cp -av /home/oracle/backup/arch/hot/users01.dbf /u01/app/oracle/orcl2/users01.dbf
`/home/oracle/backup/arch/hot/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> recover tablespace users;
Media recovery complete.
SYS@orcl2> alter tablespace users online;
Tablespace altered.
SYS@orcl2> select a.file#,b.name,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ ------- ------------------
1 SYSTEM SYSTEM 916672
2 SYSAUX ONLINE 916672
3 UNDOTBS1 ONLINE 916672
4 USERS ONLINE 917074
5 EXAMPLE ONLINE 916672
SYS@orcl2> select * from hr.test1;
ID
----------
1
SYS@orcl2> create table hr.test2 tablespace users as select * from hr.employees;
Table created.
SYS@orcl2> select count(*) from hr.test2;
COUNT(*)
----------
===============================================================================================
SYS@orcl2> alter database create datafile '/u01/app/oracle/orcl2/insa_tbs01.dbf';
Database altered.
-- 빈 테이블스페이스를 만든다.(=빈깡통)
-- DB startup시에 users tablespace 장애로 부터 복구
SYS@orcl2> select a.file#,a.name,b.status,b.change#,b.time from v$datafile a, v$backup b where a.file#=b.file#;
FILE# NAME STATUS CHANGE# TIME
---------- ----------------------------------- ------------------ ---------- ---------
1 /u01/app/oracle/orcl2/system01.dbf NOT ACTIVE 916672 15-APR-19
2 /u01/app/oracle/orcl2/sysaux01.dbf NOT ACTIVE 916672 15-APR-19
3 /u01/app/oracle/orcl2/undotbs01.dbf NOT ACTIVE 916672 15-APR-19
4 /u01/app/oracle/orcl2/users01.dbf NOT ACTIVE 916672 15-APR-19
5 /u01/app/oracle/orcl2/example01.dbf NOT ACTIVE 916672 15-APR-19
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 INACTIVE YES 1 50
3 12 CURRENT NO 1 50
SYS@orcl2> select sequence#, name from v$archived_log;
SEQUENCE# NAME
---------- --------------------------------------------------
8 /home/oracle/arch1/arch_1_8_1005236600.arc
8 /home/oracle/arch2/arch_1_8_1005236600.arc
9 /home/oracle/arch1/arch_1_9_1005236600.arc
9 /home/oracle/arch2/arch_1_9_1005236600.arc
10 /home/oracle/arch1/arch_1_10_1005236600.arc
10 /home/oracle/arch2/arch_1_10_1005236600.arc
11 /home/oracle/arch1/arch_1_11_1005236600.arc
11 /home/oracle/arch2/arch_1_11_1005236600.arc
8 rows selected.
SYS@orcl2> !ls /home/oracle/backup/arch/hot/
control.bak example01.dbf orcl2 sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
SYS@orcl2> !ls /home/oracle/arch1/
arch_1_10_1005236600.arc arch_1_11_1005236600.arc
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 146803016 bytes
Database Buffers 276824064 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> @recover_file
FILE# NAME ONLINE_ ERROR CHANGE# TIME
---------- ------------------------------ ------- -------------------- ---------- ---------
4 /u01/app/oracle/orcl2/users01. ONLINE FILE NOT FOUND 0
dbf
SYS@orcl2> select a.file#,b.name,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ ------- ------------------
1 SYSTEM SYSTEM 938308
2 SYSAUX ONLINE 938308
3 UNDOTBS1 ONLINE 938308
4 USERS ONLINE 938308
5 EXAMPLE ONLINE 938308
SYS@orcl2> alter database datafile '/u01/app/oracle/orcl2/users01.dbf' offline;
Database altered.
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> @recover_file
FILE# NAME ONLINE_ ERROR CHANGE# TIME
---------- ------------------------------ ------- -------------------- ---------- ---------
4 /u01/app/oracle/orcl2/users01. OFFLINE FILE NOT FOUND 0
dbf
SYS@orcl2> select a.file#,b.name,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ ------- ------------------
1 SYSTEM SYSTEM 938311
2 SYSAUX ONLINE 938311
3 UNDOTBS1 ONLINE 938311
4 USERS OFFLINE 938308
5 EXAMPLE ONLINE 938311
SYS@orcl2> !cp -av /home/oracle/backup/arch/hot/users01.dbf /u01/app/oracle/orcl2/users01.dbf
`/home/oracle/backup/arch/hot/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> recover tablespace users;
Media recovery complete.
SYS@orcl2> alter tablespace users online;
Tablespace altered.
SYS@orcl2> select a.file#,b.name,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ ------- ------------------
1 SYSTEM SYSTEM 938311
2 SYSAUX ONLINE 938311
3 UNDOTBS1 ONLINE 938311
4 USERS ONLINE 938548
5 EXAMPLE ONLINE 938311
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 INACTIVE YES 1 50
3 12 CURRENT NO 1 50
-- 백업이 없는 상태에서 장애가 발생한 테이블 스페이스 복구
SYS@orcl2> select a.file#,b.name,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ ------- ------------------
1 SYSTEM SYSTEM 938311
2 SYSAUX ONLINE 938311
3 UNDOTBS1 ONLINE 938311
4 USERS ONLINE 938548
5 EXAMPLE ONLINE 938311
SYS@orcl2> select a.file#,a.name,b.status,b.change#,b.time from v$datafile a, v$backup b where a.file#=b.file#;
FILE# NAME STATUS CHANGE# TIME
---------- ------------------------------ ------------------ ---------- ---------
1 /u01/app/oracle/orcl2/system01 NOT ACTIVE 916672 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 NOT ACTIVE 916672 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 NOT ACTIVE 916672 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. NOT ACTIVE 916672 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 NOT ACTIVE 916672 15-APR-19
1.dbf
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 INACTIVE YES 1 50
3 12 CURRENT NO 1 50
SYS@orcl2> select sequence#, name, first_change#, first_time, next_change#, next_time from v$archived_log;
SEQUENCE# NAME FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ------------------------------ ------------- --------- ------------ ---------
8 /home/oracle/arch1/arch_1_8_10 892445 11-APR-19 895373 15-APR-19
05236600.arc
8 /home/oracle/arch2/arch_1_8_10 892445 11-APR-19 895373 15-APR-19
05236600.arc
9 /home/oracle/arch1/arch_1_9_10 895373 15-APR-19 915915 15-APR-19
05236600.arc
9 /home/oracle/arch2/arch_1_9_10 895373 15-APR-19 915915 15-APR-19
05236600.arc
10 /home/oracle/arch1/arch_1_10_1 915915 15-APR-19 916906 15-APR-19
005236600.arc
10 /home/oracle/arch2/arch_1_10_1 915915 15-APR-19 916906 15-APR-19
005236600.arc
11 /home/oracle/arch1/arch_1_11_1 916906 15-APR-19 937231 16-APR-19
005236600.arc
11 /home/oracle/arch2/arch_1_11_1 916906 15-APR-19 937231 16-APR-19
005236600.arc
8 rows selected.
SYS@orcl2> create tablespace data01
datafile '/u01/app/oracle/oradata/data01.dbf' size 5m
extent management local uniform size 64k
segment space management auto;
Tablespace created.
SYS@orcl2> select a.file#,a.name,b.status,b.change#,b.time from v$datafile a, v$backup b where a.file#=b.file#;
FILE# NAME STATUS CHANGE# TIME
---------- ------------------------------ ------------------ ---------- ---------
1 /u01/app/oracle/orcl2/system01 NOT ACTIVE 916672 15-APR-19
.dbf
2 /u01/app/oracle/orcl2/sysaux01 NOT ACTIVE 916672 15-APR-19
.dbf
3 /u01/app/oracle/orcl2/undotbs0 NOT ACTIVE 916672 15-APR-19
1.dbf
4 /u01/app/oracle/orcl2/users01. NOT ACTIVE 916672 15-APR-19
dbf
5 /u01/app/oracle/orcl2/example0 NOT ACTIVE 916672 15-APR-19
1.dbf
6 /u01/app/oracle/oradata/data01 NOT ACTIVE 0
.dbf
6 rows selected.
SYS@orcl2> create table hr.dept_temp tablespace data01 as select * from hr.departments;
Table created.
SYS@orcl2> select count(*) from hr.dept_temp;
COUNT(*)
----------
27
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 INACTIVE YES 1 50
2 11 INACTIVE YES 1 50
3 12 CURRENT NO 1 50
SYS@orcl2> @switch
System altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 13 CURRENT NO 1 50
2 11 INACTIVE YES 1 50
3 12 ACTIVE YES 1 50
SYS@orcl2> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'DEPT_TEMP';
FILE_NAME
-----------------------------------------------------------------------------------------
/u01/app/oracle/oradata/data01.dbf
SYS@orcl2> ! rm /u01/app/oracle/oradata/data01.dbf
SYS@orcl2> ! ls /u01/app/oracle/oradata/data01.dbf
ls: /u01/app/oracle/oradata/data01.dbf: No such file or directory
SYS@orcl2> ! ls /u01/app/oracle/orcl2/data01.dbf
ls: /u01/app/oracle/orcl2/data01.dbf: No such file or directory
SYS@orcl2> select * from hr.dept_temp;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
SYS@orcl2> alter system flush buffer_cache;
System altered.
SYS@orcl2> select * from hr.dept_temp;
select * from hr.dept_temp
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/data01.dbf'
SYS@orcl2> select name, file#, status from v$datafile;
NAME FILE# STATUS
---------------------------------------- ---------- -------
/u01/app/oracle/orcl2/system01.dbf 1 SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf 2 ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf 3 ONLINE
/u01/app/oracle/orcl2/users01.dbf 4 ONLINE
/u01/app/oracle/orcl2/example01.dbf 5 ONLINE
/u01/app/oracle/oradata/data01.dbf 6 RECOVER
6 rows selected.
SYS@orcl2> alter tablespace data01 offline immediate;
Tablespace altered.
SYS@orcl2> select name, file#, status from v$datafile;
NAME FILE# STATUS
---------------------------------------- ---------- -------
/u01/app/oracle/orcl2/system01.dbf 1 SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf 2 ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf 3 ONLINE
/u01/app/oracle/orcl2/users01.dbf 4 ONLINE
/u01/app/oracle/orcl2/example01.dbf 5 ONLINE
/u01/app/oracle/oradata/data01.dbf 6 RECOVER
6 rows selected.
SYS@orcl2> alter database create datafile '/u01/app/oracle/oradata/data01.dbf';
Database altered.
SYS@orcl2> ! ls /u01/app/oracle/oradata/data01.dbf
/u01/app/oracle/oradata/data01.dbf
SYS@orcl2> recover tablespace data01;
Media recovery complete.
SYS@orcl2> select name, file#, status from v$datafile;
NAME FILE# STATUS
---------------------------------------- ---------- -------
/u01/app/oracle/orcl2/system01.dbf 1 SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf 2 ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf 3 ONLINE
/u01/app/oracle/orcl2/users01.dbf 4 ONLINE
/u01/app/oracle/orcl2/example01.dbf 5 ONLINE
/u01/app/oracle/oradata/data01.dbf 6 OFFLINE
6 rows selected.
SYS@orcl2> alter tablespace data01 online;
Tablespace altered.
SYS@orcl2> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name = 'DEPT_TEMP';
FILE_NAME
-----------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/data01.dbf
SYS@orcl2> select name, file#, status from v$datafile;
NAME FILE# STATUS
---------------------------------------- ---------- -------
/u01/app/oracle/orcl2/system01.dbf 1 SYSTEM
/u01/app/oracle/orcl2/sysaux01.dbf 2 ONLINE
/u01/app/oracle/orcl2/undotbs01.dbf 3 ONLINE
/u01/app/oracle/orcl2/users01.dbf 4 ONLINE
/u01/app/oracle/orcl2/example01.dbf 5 ONLINE
/u01/app/oracle/oradata/data01.dbf 6 ONLINE
6 rows selected.
/ data01 tablespace 의 장애가 발생
/ 원래 위치로 복구할 수 없는 상황에서
/ data file 의 위치를 바꿔주자
SYS@orcl2> create table hr.emp_tmp tablespace data01 as select * from hr.employees;
Table created.
SYS@orcl2> select count(*) from hr.emp_tmp;
COUNT(*)
----------
107
SYS@orcl2> select a.file#,a.name,b.status,b.change#,b.time from v$datafile a, v$backup b where a.file#=b.file#;
FILE# NAME STATUS CHANGE# TIME
---------- ---------------------------------------- ------------------ ---------- ---------
1 /u01/app/oracle/orcl2/system01.dbf NOT ACTIVE 916672 15-APR-19
2 /u01/app/oracle/orcl2/sysaux01.dbf NOT ACTIVE 916672 15-APR-19
3 /u01/app/oracle/orcl2/undotbs01.dbf NOT ACTIVE 916672 15-APR-19
4 /u01/app/oracle/orcl2/users01.dbf NOT ACTIVE 916672 15-APR-19
5 /u01/app/oracle/orcl2/example01.dbf NOT ACTIVE 916672 15-APR-19
6 /u01/app/oracle/oradata/data01.dbf NOT ACTIVE 0
6 rows selected.
SYS@orcl2> alter tablespace data01 begin backup;
Tablespace altered.
SYS@orcl2> !cp -av /u01/app/oracle/oradata/data01.dbf /home/oracle/backup/arch/hot/
`/u01/app/oracle/oradata/data01.dbf' -> `/home/oracle/backup/arch/hot/data01.dbf'
SYS@orcl2> alter tablespace data01 end backup;
Tablespace altered.
SYS@orcl2> select a.file#,a.name,b.status,b.change#,b.time from v$datafile a, v$backup b where a.file#=b.file#;
FILE# NAME STATUS CHANGE# TIME
---------- ---------------------------------------- ------------------ ---------- ---------
1 /u01/app/oracle/orcl2/system01.dbf NOT ACTIVE 916672 15-APR-19
2 /u01/app/oracle/orcl2/sysaux01.dbf NOT ACTIVE 916672 15-APR-19
3 /u01/app/oracle/orcl2/undotbs01.dbf NOT ACTIVE 916672 15-APR-19
4 /u01/app/oracle/orcl2/users01.dbf NOT ACTIVE 916672 15-APR-19
5 /u01/app/oracle/orcl2/example01.dbf NOT ACTIVE 916672 15-APR-19
6 /u01/app/oracle/oradata/data01.dbf NOT ACTIVE 959424 16-APR-19
6 rows selected.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 13 INACTIVE YES 1 50
2 14 CURRENT NO 1 50
3 12 INACTIVE YES 1 50
SYS@orcl2> @switch
System altered.
SYS@orcl2> ! rm /u01/app/oracle/oradata/data01.dbf
SYS@orcl2> ! ls /u01/app/oracle/oradata/data01.dbf
ls: /u01/app/oracle/oradata/data01.dbf: No such file or directory
SYS@orcl2> select * from hr.emp_tmp;
SYS@orcl2> alter system flush buffer_cache;
System altered.
SYS@orcl2> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 16 09:46:53 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl2> select * from hr.emp_tmp;
select * from hr.emp_tmp
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/data01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SYS@orcl2> alter tablespace data01 offline immediate;
Tablespace altered.
SYS@orcl2> !cp -av /home/oracle/backup/arch/hot/data01.dbf /u01/app/oracle/orcl2/data01.dbf
`/home/oracle/backup/arch/hot/data01.dbf' -> `/u01/app/oracle/orcl2/data01.dbf'
SYS@orcl2> alter database rename file '/u01/app/oracle/oradata/data01.dbf' to '/u01/app/oracle/orcl2/data01.dbf';
Database altered.
SYS@orcl2> recover tablespace data01;
ORA-00279: change 959424 generated at 04/16/2019 09:45:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_14_1005236600.arc
ORA-00280: change 959424 for thread 1 is in sequence #14
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
<<Enter>>
Log applied.
Media recovery complete.
SYS@orcl2> alter tablespace data01 online;
Tablespace altered.
SYS@orcl2> select count(*) from hr.emp_tmp;
COUNT(*)
----------
107
SYS@orcl2> select a.file#,a.name,b.status,b.change#,b.time from v$datafile a, v$backup b where a.file#=b.file#;
FILE# NAME STATUS CHANGE# TIME
---------- ----------------------------------- ------------------ ---------- ---------
1 /u01/app/oracle/orcl2/system01.dbf NOT ACTIVE 916672 15-APR-19
2 /u01/app/oracle/orcl2/sysaux01.dbf NOT ACTIVE 916672 15-APR-19
3 /u01/app/oracle/orcl2/undotbs01.dbf NOT ACTIVE 916672 15-APR-19
4 /u01/app/oracle/orcl2/users01.dbf NOT ACTIVE 916672 15-APR-19
5 /u01/app/oracle/orcl2/example01.dbf NOT ACTIVE 916672 15-APR-19
6 /u01/app/oracle/orcl2/data01.dbf NOT ACTIVE 959424 16-APR-19
6 rows selected.
SYS@orcl2> select f.file_name from dba_extents e, dba_data_files f where e.file_id = f.file_id and e.segment_name ='EMP_TMP';
FILE_NAME
--------------------------------------------------------------------
/u01/app/oracle/orcl2/data01.dbf
SYS@orcl2> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- -----------------------------------
USERS /u01/app/oracle/orcl2/users01.dbf
UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf
SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf
SYSTEM /u01/app/oracle/orcl2/system01.dbf
EXAMPLE /u01/app/oracle/orcl2/example01.dbf
DATA01 /u01/app/oracle/orcl2/data01.dbf
6 rows selected.
<<<<<<<<<<<<<<<<<<<<<<<<<복구 실습이 끝나면 다시 백업을 하자>>>>>>>>>>>>>>>>>>>>>>>
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[orcl2:~]$ rm /home/oracle/arch1/*
[orcl2:~]$ rm /home/oracle/arch2/*
[orcl2:~]$ . orcl2_backup.sh
`/u01/app/oracle/orcl2/control01.ctl' -> `/home/oracle/backup/arch/cold/control01.ctl'
`/u01/app/oracle/orcl2/system01.dbf' -> `/home/oracle/backup/arch/cold/system01.dbf'
`/u01/app/oracle/orcl2/sysaux01.dbf' -> `/home/oracle/backup/arch/cold/sysaux01.dbf'
`/u01/app/oracle/orcl2/undotbs01.dbf' -> `/home/oracle/backup/arch/cold/undotbs01.dbf'
`/u01/app/oracle/orcl2/users01.dbf' -> `/home/oracle/backup/arch/cold/users01.dbf'
`/u01/app/oracle/orcl2/example01.dbf' -> `/home/oracle/backup/arch/cold/example01.dbf'
`/u01/app/oracle/orcl2/redo03.log' -> `/home/oracle/backup/arch/cold/redo03.log'
`/u01/app/oracle/orcl2/redo02.log' -> `/home/oracle/backup/arch/cold/redo02.log'
`/u01/app/oracle/orcl2/redo01.log' -> `/home/oracle/backup/arch/cold/redo01.log'
`/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora' -> `/home/oracle/backup/arch/cold/initorcl2.ora'
ARCHIVE LOG : 전체 백업
DB 끄고 수행할 것.
[orcl2:~]$ cp -av /u01/app/oracle/orcl2/*.dbf /home/oracle/backup/arch/cold/
==================recover전에 archive파일들을 삭제한 경우=========================
SYS@orcl2> recover database until cancel
ORA-00279: change 979083 generated at 04/15/2019 15:57:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_26_1005236600.arc
ORA-00280: change 979083 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 1 CURRENT NO 1 50
2 0 UNUSED YES 1 50
3 0 UNUSED YES 1 50
-- log가 전부 reset된 상황이다. 이때, 가장 먼저 해야할것은 다시 cold백업을 실행하는 것이다.
-- 전에 만들어 놓은 cold백업은 전부 쓸모없는 파일이다.
-- arch1과 arch2 디렉토리 내의 파일들도 전부 삭제하자.
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-redo log (0) | 2019.04.22 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-archive_log (0) | 2019.04.22 |
| [Oracle DBA]Backup & Recovery (0) | 2019.04.10 |
| [Oracle DBA]성능 관리와 데이터 이동 (0) | 2019.04.10 |
| [Oracle DBA]데이터베이스 유지 관리 (0) | 2019.04.08 |