ArchiveMode 에서의 Backup & Recovery-
archive_log
Archive log
- 완전복구
- Data 유실이 없는 복구
- backup + archive + online log(current까지)
- 불완전 복구
- Data 유실이 있는 복구
- alter database open reset logs
- recover database until cancel
- archive redo log의 유실
- recover databae until time
- time based recovery
- Offline Recovery
- SYSTEM, UNDO에 포함된 datafile이 손실되었을 경우
- 데이터파일 대부분이 손실되었을 겨우
- recover database
- Online Recovery
- alter tablestpace OOOO offline
- recover tablespace
- recover datafile 6
- alter tablestpace OOOO online
복구실습
- data01 테이블스페이스에 데이타파일을 추가하여 data01.dbf, data02.dbf 가 있는데 data01.dbf 만 장애가 발생한 경우 장애난 데이타파일만 RECOVERY
- 모든 데이타 파일이 삭제된 경우 복구
- 불완전 복구- canced based incomplete recovery
- 불완전 복구- time based incomplete recovery
- 테이블스페이스 drop 전으로 돌아가는 recovery
/* data01 테이블스페이스에 데이타파일을 추가하여 data01.dbf, data02.dbf 가있는데
data01.dbf 만 장애가 발생한 경우
장애난 데이타파일만 RECOVERY
*/
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
SYS@orcl2> alter tablespace data01 add datafile '/u01/app/oracle/orcl2/data02.dbf' size 10m;
Tablespace altered.
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
DATA01 /u01/app/oracle/orcl2/data02.dbf
7 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> insert into hr.emp_tmp select * from hr.emp_tmp;
107 rows created.
SYS@orcl2> /
214 rows created.
SYS@orcl2> /
428 rows created.
SYS@orcl2> /
856 rows created.
SYS@orcl2> /
1712 rows created.
SYS@orcl2> /
3424 rows created.
SYS@orcl2> /
6848 rows created.
SYS@orcl2> /
13696 rows created.
SYS@orcl2> /
27392 rows created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select bytes from dba_segments where segment_name = 'EMP_TMP';
BYTES
----------
4718592
SYS@orcl2> select f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name ='EMP_TMP'
group by f.file_name;
FILE_NAME COUNT(*)
----------------------------------- ----------
/u01/app/oracle/orcl2/data02.dbf 36
/u01/app/oracle/orcl2/data01.dbf 36
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
7 /u01/app/oracle/orcl2/data02.dbf NOT ACTIVE 0
7 rows selected.
SYS@orcl2> alter tablespace data01 begin 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/orcl2/data01.dbf ACTIVE 965345 16-APR-19
7 /u01/app/oracle/orcl2/data02.dbf ACTIVE 965345 16-APR-19
SYS@orcl2> !cp -av /u01/app/oracle/orcl2/data02.dbf /home/oracle/backup/arch/hot/
`/u01/app/oracle/orcl2/data02.dbf' -> `/home/oracle/backup/arch/hot/data02.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/orcl2/data01.dbf NOT ACTIVE 965345 16-APR-19
7 /u01/app/oracle/orcl2/data02.dbf NOT ACTIVE 965345 16-APR-19
7 rows selected.
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/data01.dbf
SYS@orcl2> create table hr.test01 tablespace data01 as select * from hr.employees;
Table created.
SYS@orcl2> select f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name ='TEST01'
group by f.file_name;
FILE_NAME COUNT(*)
----------------------------------- ----------
/u01/app/oracle/orcl2/data02.dbf 1
SYS@orcl2> alter system checkpoint;
System altered.
SYS@orcl2> conn / as sysdba
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 985466
2 SYSAUX ONLINE 985466
3 UNDOTBS1 ONLINE 985466
4 USERS ONLINE 985466
5 EXAMPLE ONLINE 985466
6 DATA01 RECOVER 965345
7 DATA01 ONLINE 985466
7 rows selected.
SYS@orcl2> alter tablespace data01 offline temporary;
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 985466
2 SYSAUX ONLINE 985466
3 UNDOTBS1 ONLINE 985466
4 USERS ONLINE 985466
5 EXAMPLE ONLINE 985466
6 DATA01 RECOVER 965345
7 DATA01 OFFLINE 985846
7 rows selected.
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> !cp -av /home/oracle/backup/arch/hot/data02.dbf /u01/app/oracle/orcl2/data02.dbf
`/home/oracle/backup/arch/hot/data02.dbf' -> `/u01/app/oracle/orcl2/data02.dbf'
SYS@orcl2> recover tablespace data01;
Media recovery complete.
SYS@orcl2> alter tablespace data01 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#; 2
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ----------------------------------- ------- ------------------
1 SYSTEM SYSTEM 1009208
2 SYSAUX ONLINE 1009208
3 UNDOTBS1 ONLINE 1009208
4 USERS ONLINE 1009208
5 EXAMPLE ONLINE 1009208
6 DATA01 ONLINE 1009267
7 DATA01 ONLINE 1009267
7 rows selected.
SYS@orcl2> select count(*) from hr.test01;
COUNT(*)
----------
107
SYS@orcl2> select count(*) from hr.emp_tmp;
COUNT(*)
----------
54784
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 = 'TEST01';
FILE_NAME
-----------------------------------
/u01/app/oracle/orcl2/data02.dbf
SYS@orcl2> select f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name ='EMP_TMP'
group by f.file_name;
FILE_NAME COUNT(*)
----------------------------------- ----------
/u01/app/oracle/oradata/data01.dbf 36
/u01/app/oracle/orcl2/data02.dbf 36
-- 다시 cold 백업
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/data01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/data02.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
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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:~]$ chmod 755 orcl2_backup.sh
[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/data01.dbf' -> `/home/oracle/backup/arch/cold/data01.dbf'
`/u01/app/oracle/orcl2/data02.dbf' -> `/home/oracle/backup/arch/cold/data02.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'
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/*
===========================================위와 동일한 상태지만, 다른 방법으로 recover=======================================
/* data01 테이블스페이스에 데이타파일을 추가하여 data01.dbf, data02.dbf 가있는데
data01.dbf 만 장애가 발생한 경우
장애난 데이타파일만 RECOVERY --- 테이블스페이스 offline하지 않고 recover datafile 명령어 사용
*/
1) data01 테이블스페이스에 데이타 파일 2개로 만든다
SYS@orcl2> drop tablespace data01 including contents and datafiles;
Tablespace dropped.
SYS@orcl2> create tablespace data01 datafile '/u01/app/oracle/orcl2/data01.dbf' size 5m;
Tablespace created.
SYS@orcl2> alter tablespace data01 add datafile '/u01/app/oracle/orcl2/data02.dbf' size 10m;
Tablespace altered.
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
DATA01 /u01/app/oracle/orcl2/data02.dbf
7 rows selected.
SYS@orcl2> create table hr.emp_tmp tablespace data01 as select * from hr.employees;
Table created.
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/data02.dbf
2) data01 테이블스페이스 온라인 백업
SYS@orcl2> !cp -av /u01/app/oracle/orcl2/data01.dbf /home/oracle/backup/arch/hot/
`/u01/app/oracle/orcl2/data01.dbf' -> `/home/oracle/backup/arch/hot/data01.dbf'
SYS@orcl2> !cp -av /u01/app/oracle/orcl2/data02.dbf /home/oracle/backup/arch/hot/
`/u01/app/oracle/orcl2/data02.dbf' -> `/home/oracle/backup/arch/hot/data02.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/orcl2/data01.dbf NOT ACTIVE 1032046 16-APR-19
7 /u01/app/oracle/orcl2/data02.dbf NOT ACTIVE 1032046 16-APR-19
7 rows selected.
=> Backup모드가 아닌지 확인.
SYS@orcl2> insert into hr.emp_tmp select * from hr.emp_tmp;
107 rows created.
SYS@orcl2> /
214 rows created.
SYS@orcl2> /
428 rows created.
SYS@orcl2> /
856 rows created.
SYS@orcl2> /
1712 rows created.
SYS@orcl2> /
3424 rows created.
SYS@orcl2> /
6848 rows created.
SYS@orcl2> /
13696 rows created.
SYS@orcl2> /
27392 rows created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select bytes from dba_segments where segment_name = 'EMP_TMP';
BYTES
----------
5242880
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> select f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name ='EMP_TMP'
group by f.file_name;
FILE_NAME COUNT(*)
----------------------------------- ----------
/u01/app/oracle/orcl2/data02.dbf 18
/u01/app/oracle/orcl2/data01.dbf 2
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 1032046 16-APR-19
7 /u01/app/oracle/orcl2/data02.dbf NOT ACTIVE 1032046 16-APR-19
7 rows selected.
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/data01.dbf
SYS@orcl2> create table hr.test01 tablespace data01 as select * from hr.employees;
Table created. Or File Error
SYS@orcl2> select f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name ='TEST01'
group by f.file_name;
FILE_NAME COUNT(*)
----------------------------------- ----------
/u01/app/oracle/orcl2/data02.dbf 1
=> Error가 난다면 다시 접속해서 Select 해본다.
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 1032583
2 SYSAUX ONLINE 1032583
3 UNDOTBS1 ONLINE 1032583
4 USERS ONLINE 1032583
5 EXAMPLE ONLINE 1032583
6 DATA01 ONLINE 1032583
7 DATA01 RECOVER 1032583
7 rows selected.
SYS@orcl2> select * from v$recover_file ;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ------------------ ---------- ----------
7 OFFLINE OFFLINE FILE NOT FOUND
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> 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 1032583
2 SYSAUX ONLINE 1032583
3 UNDOTBS1 ONLINE 1032583
4 USERS ONLINE 1032583
5 EXAMPLE ONLINE 1032583
6 DATA01 RECOVER 1032583
7 DATA01 ONLINE 1032583
7 rows selected.
SYS@orcl2> select * from v$recovery_log ;
log switch 했다면 정보 보일것..
no rows selected
SYS@orcl2> recover datafile 6 ;
Media recovery complete.
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 1032583
2 SYSAUX ONLINE 1032583
3 UNDOTBS1 ONLINE 1032583
4 USERS ONLINE 1032583
5 EXAMPLE ONLINE 1032583
6 DATA01 OFFLINE 1032583
7 DATA01 ONLINE 1032583
7 rows selected.
SYS@orcl2> alter database datafile 6 online ;
Database 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 1032583
2 SYSAUX ONLINE 1032583
3 UNDOTBS1 ONLINE 1032583
4 USERS ONLINE 1032583
5 EXAMPLE ONLINE 1032583
6 DATA01 ONLINE 1032583
7 DATA01 ONLINE 1032583
7 rows selected.
=> Recover상태가 Online으로 바뀐모습을 확인할 수 있다.
SYS@orcl2> select count(*) from hr.test01;
COUNT(*)
----------
107
SYS@orcl2> select count(*) from hr.emp_tmp;
COUNT(*)
----------
54784
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 = 'TEST01';
FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/data02.dbf
SYS@orcl2> select f.file_name, count(*)
from dba_extents e, dba_data_files f
where e.file_id = f.file_id
and e.segment_name ='EMP_TMP'
group by f.file_name;
FILE_NAME COUNT(*)
----------------------------------- ----------
/u01/app/oracle/orcl2/data02.dbf 18
/u01/app/oracle/orcl2/data01.dbf 2
-- 다시 cold백업
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/data01.dbf /home/oracle/backup/arch/cold
cp -av /u01/app/oracle/orcl2/data02.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
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
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:~]$ chmod 755 orcl2_backup.sh
[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/data01.dbf' -> `/home/oracle/backup/arch/cold/data01.dbf'
`/u01/app/oracle/orcl2/data02.dbf' -> `/home/oracle/backup/arch/cold/data02.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'
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/*
[orcl2:~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 16 11:57:51 2019
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
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.
Database opened.
-- 지금까지 실습한 자료들 제거, 백업
SYS@orcl2> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/system01.dbf
/u01/app/oracle/orcl2/sysaux01.dbf
/u01/app/oracle/orcl2/undotbs01.dbf
/u01/app/oracle/orcl2/users01.dbf
/u01/app/oracle/orcl2/example01.dbf
/u01/app/oracle/orcl2/data01.dbf
/u01/app/oracle/orcl2/data02.dbf
7 rows selected.
SYS@orcl2> drop tablespace data01 including contents and datafiles
Tablespace dropped.
SYS@orcl2> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/system01.dbf
/u01/app/oracle/orcl2/sysaux01.dbf
/u01/app/oracle/orcl2/undotbs01.dbf
/u01/app/oracle/orcl2/users01.dbf
/u01/app/oracle/orcl2/example01.dbf
SYS@orcl2> select member from v$logfile;
MEMBER
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/redo03.log
/u01/app/oracle/orcl2/redo02.log
/u01/app/oracle/orcl2/redo01.log
SYS@orcl2> select name from v$controlfile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/control01.ctl
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
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
[orcl2:~]$ chmod 755 orcl2_backup.sh
[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'
======불완전 복구- canceled based incomplete recovery
======users 테이블스페이스 유실후 복구하다
======archive log 유실로 완전 복구 실패
======불완전 복구하는 CASE
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- --------------- --- ---------- ----------
1 25 INACTIVE YES 1 50
2 26 CURRENT NO 1 50
3 24 INACTIVE YES 1 50
1. DB 작업을 일으킨다.
SYS@orcl2> create table hr.t1 as select * from hr.employees;
Table created.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> insert into hr.t1 select * from hr.t1;
107 rows created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select count(*) from hr.t1;
COUNT(*)
----------
214
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- --------------- --- ---------- ----------
1 28 INACTIVE YES 1 50
2 29 INACTIVE YES 1 50
3 30 CURRENT NO 1 50
SYS@orcl2> select sequence#, name from v$archived_log;
SEQUENCE# NAME
---------- ------------------------------
.......
28 /home/oracle/arch1/arch_1_28_1
005236600.arc
28 /home/oracle/arch2/arch_1_28_1
005236600.arc
29 /home/oracle/arch1/arch_1_29_1
005236600.arc
29 /home/oracle/arch2/arch_1_29_1
005236600.arc
44 rows selected.
--- 목록중에서 online로그에 있는 번호 바로 전번호 ( 이경우는 27번)를 삭제할 예정
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
2. 장애 발생
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/users01.dbf
SYS@orcl2> !ls /home/oracle/arch1/
arch_1_26_1005236600.arc arch_1_27_1005236600.arc arch_1_28_1005236600.arc arch_1_29_1005236600.arc
SYS@orcl2> !rm -f /home/oracle/arch1/arch_1_27_1005236600.arc
SYS@orcl2> !rm -f /home/oracle/arch2/arch_1_27_1005236600.arc
SYS@orcl2> !ls /home/oracle/arch1/arch_1_27_1005236600.arc /home/oracle/arch2/arch_1_27_1005236600.arc
ls: /home/oracle/arch1/arch_1_27_1005236600.arc: No such file or directory
ls: /home/oracle/arch2/arch_1_27_1005236600.arc: No such file or directory
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'
/* recover 작업에 앞어 필요한 archive log file이 어떤것들이 필요한지 보여주는 view */
SYS@orcl2> @recover_file
FILE# NAME ONLINE_ ERROR CHANGE# TIME
---------- ------------------------------ ------- -------------------- ---------- ---------
4 /u01/app/oracle/orcl2/users01. ONLINE FILE NOT FOUND 0
dbf
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/users01.dbf /u01/app/oracle/orcl2/users01.dbf
`/home/oracle/backup/arch/cold/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> col archive_name format a60
SYS@orcl2> @recover_log
THREAD# SEQUENCE# TIME ARCHIVE_NAME
---------- ---------- --------- ------------------------------------------------------------
1 26 16-APR-19 /home/oracle/arch2/arch_1_26_1005236600.arc
1 27 16-APR-19 /home/oracle/arch2/arch_1_27_1005236600.arc
3. 완전 복구 시도 -> 실패
SYS@orcl2> recover tablespace users
ORA-00279: change 1061064 generated at 04/16/2019 14:18:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_26_1005236600.arc
ORA-00280: change 1061064 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1062339 generated at 04/16/2019 14:43:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_27_1005236600.arc
ORA-00280: change 1062339 for thread 1 is in sequence #27
ORA-00308: cannot open archived log '/home/oracle/arch2/arch_1_27_1005236600.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
4. 불완전 복구 시도
불완전 복구를 위해 모든 데이타 파일을 resotre
SYS@orcl2> shutdown abort
ORACLE instance shut down.
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/*.dbf /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/data01.dbf' -> `/u01/app/oracle/orcl2/data01.dbf'
`/home/oracle/backup/arch/cold/data02.dbf' -> `/u01/app/oracle/orcl2/data02.dbf'
`/home/oracle/backup/arch/cold/example01.dbf' -> `/u01/app/oracle/orcl2/example01.dbf'
`/home/oracle/backup/arch/cold/sysaux01.dbf' -> `/u01/app/oracle/orcl2/sysaux01.dbf'
`/home/oracle/backup/arch/cold/system01.dbf' -> `/u01/app/oracle/orcl2/system01.dbf'
`/home/oracle/backup/arch/cold/undotbs01.dbf' -> `/u01/app/oracle/orcl2/undotbs01.dbf'
`/home/oracle/backup/arch/cold/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> startup mount --불완전 복구는 offline recovery
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.
/*#27이전까지의 변경이력(~#26)만은 recover 하고자 한다.
<cancel-based recovery>
#26은 그냥 엔터
#27번일때 cancel 타이핑하고 엔터할 것
*/
SYS@orcl2> recover database until cancel;
ORA-00279: change 1061064 generated at 04/16/2019 14:18:13 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_26_1005236600.arc
ORA-00280: change 1061064 for thread 1 is in sequence #26
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1062339 generated at 04/16/2019 14:43:27 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_27_1005236600.arc
ORA-00280: change 1062339 for thread 1 is in sequence #27
ORA-00278: log file '/home/oracle/arch2/arch_1_26_1005236600.arc' no longer needed for this
recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
-- cancel-based recovery의 특징은 현재 시점의 redo log group의 seq#와 복구시점의 seq#가 맞지 않게 된다.
-- 따라서 seq#를 초기화 할 필요가 있음.
-- resetlogs 옵션으로 DB를 open하게 되면 redo log group의 seq# 넘버링이 1부터 초기화된다
-- <주의> resetlogs로 OPEN한 DB는 반드시 이 시점에서 cold backup을 받아두어야 한다.
-- 이유 : resetlogs 이전의 DB와 비교할 때 resetlogs#가 전혀 다르기 때문에 redo log file, archive log file들이 서로 호환이 안됨.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select count(*) from hr.t1;
COUNT(*)
----------
107
==> 불완전 복구되었으므로 107건만 들어있다.
SYS@orcl2> drop table hr.t1;
Table dropped.
SYS@orcl2> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
1 1 1 52428800 512 1 NO CURRENT 1062340
16-APR-19 2.8147E+14
2 1 0 52428800 512 1 YES UNUSED 0
0
3 1 0 52428800 512 1 YES UNUSED 0
0
SYS@orcl2> @switch
System altered.
SYS@orcl2> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- ---------- ---------- ---------- --- --------------- -------------
FIRST_TIM NEXT_CHANGE# NEXT_TIME
--------- ------------ ---------
1 1 1 52428800 512 1 YES ACTIVE 1062340
16-APR-19 1062861 16-APR-19
2 1 2 52428800 512 1 NO CURRENT 1062861
16-APR-19 2.8147E+14
3 1 0 52428800 512 1 YES UNUSED 0
0
-- DB의 resetlogs로 open된 이력을 볼 수 있는 view
-- incarnation의 의미가 현신임을 생각해보면 resetlogs로 open하는건 DB입장에선 새출발 하는거나 다름 없나봅니다.
-- RESETLOGS_ID column의 값은 1_1_984930628.dbf의 resetlogs#로 들어간다.
SYS@orcl2> select * from v$database_incarnation;
INCARNATION# RESETLOGS_CHANGE# RESETLOGS PRIOR_RESETLOGS_CHANGE# PRIOR_RES STATUS
------------ ----------------- --------- ----------------------- --------- ---------------
RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ------------------ --------------------------
1 1 13-AUG-09 0 PARENT
694825248 0 NO
2 754488 10-APR-19 1 13-AUG-09 PARENT
1005236600 1 NO
3 1062340 16-APR-19 754488 10-APR-19 CURRENT
1005749980 2 NO
=========================================================================
-- <주의> resetlogs로 OPEN한 DB는 반드시 이 시점에서 cold backup을 받아두어야 한다.
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
==========불완전 복구- time based incomplete recovery==========
==========테이블스페이스 drop 전으로 돌아가는 recovery==========
1. 새로운 테이블스페이스 insa_tbs 생성
SYS@orcl2> CREATE TABLESPACE insa_tbs
DATAFILE '/u01/app/oracle/orcl2/insa_tbs01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
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 1063405
2 SYSAUX ONLINE 1063405
3 UNDOTBS1 ONLINE 1063405
4 USERS ONLINE 1063405
5 EXAMPLE ONLINE 1063405
6 INSA_TBS ONLINE 1065518
6 rows selected.
SYS@orcl2> save tbs
Created file tbs.sql
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- --------------- --- ---------- ----------
1 1 INACTIVE YES 1 50
2 2 ACTIVE YES 1 50
3 3 CURRENT NO 1 50
2. 전체 DB 백업하기
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
SYS@orcl2> startup
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 1 INACTIVE YES 1 50
2 2 INACTIVE YES 1 50
3 3 CURRENT NO 1 50
3. DB 작업을 일으킨다.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 1 INACTIVE YES 1 50
2 2 INACTIVE YES 1 50
3 3 CURRENT NO 1 50
SYS@orcl2> create table hr.t1 tablespace insa_tbs as select * from hr.employees;
Table created.
SYS@orcl2> alter system archive log current;
System altered.
SYS@orcl2> /
System artered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @log
GROUP# SEQUENCE# STATUS ARC MEMBERS MB
---------- ---------- ---------------- --- ---------- ----------
1 10 CURRENT NO 1 50
2 8 INACTIVE YES 1 50
3 9 INACTIVE YES 1 50
SYS@orcl2> ! ls /home/oracle/arch1
arch_1_2_1005749980.arc arch_1_4_1005749980.arc arch_1_6_1005749980.arc arch_1_8_1005749980.arc
arch_1_3_1005749980.arc arch_1_5_1005749980.arc arch_1_7_1005749980.arc arch_1_9_1005749980.arc
SYS@orcl2> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
16-APR-19 03.47.25.522668 PM +09:00
4. 장애 발생 : 실수로 테이블스페이스 삭제
SYS@orcl2> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.
테이블스페이스 drop 후 작업은 복구될까?
SYS@orcl2> create table hr.emp_20 as select * from hr.employees where department_id = 20;
Table created.
SYS@orcl2> select count(*) from hr.emp_20;
COUNT(*)
----------
2
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_20';
FILE_NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/users01.dbf
-- alert log 파일
Tue Apr 16 15:48:29 2019
drop tablespace insa_tbs including contents and datafiles
Deleted file /u01/app/oracle/orcl2/insa_tbs01.dbf
Completed: drop tablespace insa_tbs including contents and datafiles
5. 불완전 복구시도
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
-- 모든 데이타파일을 restore
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/*.dbf /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/data01.dbf' -> `/u01/app/oracle/orcl2/data01.dbf'
`/home/oracle/backup/arch/cold/data02.dbf' -> `/u01/app/oracle/orcl2/data02.dbf'
`/home/oracle/backup/arch/cold/example01.dbf' -> `/u01/app/oracle/orcl2/example01.dbf'
`/home/oracle/backup/arch/cold/insa_tbs01.dbf' -> `/u01/app/oracle/orcl2/insa_tbs01.dbf'
`/home/oracle/backup/arch/cold/sysaux01.dbf' -> `/u01/app/oracle/orcl2/sysaux01.dbf'
`/home/oracle/backup/arch/cold/system01.dbf' -> `/u01/app/oracle/orcl2/system01.dbf'
`/home/oracle/backup/arch/cold/undotbs01.dbf' -> `/u01/app/oracle/orcl2/undotbs01.dbf'
`/home/oracle/backup/arch/cold/users01.dbf' -> `/u01/app/oracle/orcl2/users01.dbf'
SYS@orcl2> ! cp -av /home/oracle/backup/arch/cold/*.ctl /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/control01.ctl' -> `/u01/app/oracle/orcl2/control01.ctl'
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 session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS@orcl2> recover database until time '2019-04-16 15:45:29'
Media recovery complete.
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
SYS@orcl2> @tbs
FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- ------------------------------ ------- ------------------
1 SYSTEM SYSTEM 1065954
2 SYSAUX ONLINE 1065954
3 UNDOTBS1 ONLINE 1065954
4 USERS ONLINE 1065954
5 EXAMPLE ONLINE 1065954
6 INSA_TBS ONLINE 1065954
6 rows selected.
SYS@orcl2> select count(*) from hr.emp_20;
select count(*) from hr.emp_20
*
ERROR at line 1:
ORA-00942: table or view does not exist
-- Recovery Target Time 이후 수행된 작업은 손실되었음
SYS@orcl2> drop tablespace insa_tbs including contents and datafiles;
Tablespace dropped.
=========================================================================
-- <주의> resetlogs로 OPEN한 DB는 반드시 이 시점에서 cold backup을 받아두어야 한다.
다음을 반드시 수행 : 전체 DB cold backup 수행
ARCHIVE LOG : 전체 DB 백업
SYS@orcl2> @db_list
SYS@orcl2> shutdown immediate
[orcl2:~]$ chmod 755 orcl2_backup.sh
[orcl2:~]$ . orcl2_backup.sh -- 전체 DB 백업
[orcl2:~]$ rm /home/oracle/arch1/* /home/oracle/arch2/* -- 불필요한 archive log 삭제
SYS@orcl2> startup
=========================================================================
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-logmnr (0) | 2019.04.22 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-redo log (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-기초 (0) | 2019.04.22 |
| [Oracle DBA]Backup & Recovery (0) | 2019.04.10 |
| [Oracle DBA]성능 관리와 데이터 이동 (0) | 2019.04.10 |