ArchiveMode 에서의 Backup & Recovery-TEMP , UNDO
추가정보
@con
SYS@orcl2> select name from v$controlfile;
@log
select group#, sequence#, status, archived, members, first_change#, bytes/1024/1024 mb
from v$log;
@logfile
col status for a10
col member for a35
SELECT a.group# ,b.sequence#,a.member , b.bytes/1024/1024 MB , b.archived , b.status, b.first_change#
FROM v$logfile a , v$log b
WHERE a.group#=b.group#
ORDER BY 1;
@data
col name for a35
select name, status from v$datafile;
@datafile
col name1 for a15
col name2 for a35
select a.file#,b.name name1,a.name name2,a.status,a.checkpoint_change# from v$datafile a, v$tablespace b where a.ts#=b.ts#;
@switch
alter system switch logfile;
@check
alter system checkpoint;
@backup
col name for a30
col status for a15
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#;
@recover_file
col name for a35
col error for a20
select r.file#, d.name, online_status, error, change#, time
from v$recover_file r, v$datafile d
where r.file# = d.file#;
@logmnr
col filename for a35
select db_name, filename from v$logmnr_logs;
@track
col filename format a50
select status,filename,bytes/1024/1024 mb from v$block_change_tracking;
@db_list
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 on
<< temp파일이 없을 경우 >>
SYS@orcl2> create temporary tablespace temp tempfile '/u01/app/oracle/orcl2/temp01.dbf' size 20m autoextend on;
Tablespace created.
SYS@orcl2> alter database default temporary tablespace temp;
Database altered.
SYS@orcl2> select * from database_properties;
DEFAULT_TEMP_TABLESPACE
TEMP1
Name of default temporary tablespace
SYS@orcl2> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SYS@orcl2> select * from v$tempfile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS
---------- ---------------- --------- ---------- ---------- ------- ---------- ---------- --------
CREATE_BYTES BLOCK_SIZE
------------ ----------
NAME
--------------------------------------------------------------------------------------------------
1 1255459 19-APR-19 10 1 ONLINE READ WRITE 20971520 2560
20971520 8192
/u01/app/oracle/orcl2/temp01.dbf
--temp 파일의 유실
1. temp file에 장애 발생
SYS@orcl2> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/temp01.dbf
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/temp01.dbf
SYS@orcl2> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/temp01.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/temp01.dbf
ls: /u01/app/oracle/orcl2/temp01.dbf: No such file or directory
SYS@orcl2> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------------------------------------------------------------------------
TEMP1
SYS@orcl2> select create_bytes/1024/1024 from v$tempfile;
CREATE_BYTES/1024/1024
----------------------
20
2. 해결방법
1) add tempfile로 추가합시다. size는 기존과 동일한 20M
SYS@orcl2> alter tablespace temp add tempfile '/u01/app/oracle/orcl2/temp02.dbf' size 20m;
Tablespace altered.
SYS@orcl2> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/temp01.dbf
/u01/app/oracle/orcl2/temp02.dbf
SYS@orcl2> ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/orcl2/temp01.dbf';
Tablespace altered.
SYS@orcl2> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------------
/u01/app/oracle/orcl2/temp02.dbf
/* 하지말것.
2) 새로운 disk storage 영역에 temp TBS를 생성하고 default Temp TBS로 지정합니다.
SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/u01/app/oracle/orcl2/temp_new01.dbf' size 20m;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
Database altered.
SQL> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
------------------------------------------------
TEMP_NEW
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/orcl2/temp_new01.dbf
*/
3) TEMP file이 손상된걸 모르는 상황. DB를 정상적 종료를 했다가 다시 올리게 되면 oracle이 알아서 re-create temp file을 실행
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/temp02.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/temp02.dbf
ls: /u01/app/oracle/orcl2/temp02.dbf: No such file or directory
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
================================================================
alert.log
SMON: enabling tx recovery
Re-creating tempfile /u01/app/oracle/orcl2/temp02.dbf
Database Characterset is AL32UTF8
No Resource Manager plan active
================================================================
=========================================================================
다음을 반드시 수행 : 전체 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
=========================================================================
Temporary Table 생성
- 테이블
- 영구테이블
- 임시테이블(temporary table)
- 데이터의 지속기간 : 한TX, 한 세션
SYS@orcl2> create global temporary table temp_emp
(a number) on commit delete rows; -- TX
Table created.
SYS@orcl2> insert into temp_emp values(1);
1 row created.
SYS@orcl2> select * from temp_emp;
A
----------
1
1 row selected.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select * from temp_emp;
no rows selected
==================================================================================================
SYS@orcl2> create global temporary table temp_emp2
(a number)
on commit preserve rows; --하나의 세션동안 데이터를 유지
Table created.
SYS@orcl2> insert into temp_emp2 values(1);
1 row created.
SYS@orcl2> select * from temp_emp2;
A
----------
1
1 row selected.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select * from temp_emp2;
A
----------
1
1 row selected.
SYS@orcl2> conn / as sysdba
Connected.
SYS@orcl2> select * from temp_emp2;
no rows selected
=========================================================================
다음을 반드시 수행 : 전체 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
=========================================================================
UNDO 테이블스페이스 복구 --critical file: OFFLINE RECOVERY
- DB shutdown 상태에서 유실
- DB Open 상태에서 유실
-- DB shutdown 상태에서 유실
1. DB 작업을 일으킨다.
SYS@orcl2> create table hr.t1 ( a number);
Table created.
SYS@orcl2> insert into hr.t1 values (1);
1 row created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> @switch
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> /
System altered.
SYS@orcl2> @logfile
GROUP# SEQUENCE# MEMBER MB ARC STATUS FIRST_CHANGE#
---------- ---------- ----------------------------------- ---------- --- ---------- -------------
1 22 /u01/app/oracle/orcl2/redo01.log 50 NO CURRENT 1259079
2 20 /u01/app/oracle/orcl2/redo02.log 50 YES INACTIVE 1259071
3 21 /u01/app/oracle/orcl2/redo03.log 50 YES INACTIVE 1259074
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ---------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1259079
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1259079
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1259079
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1259079
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE
2. shutdown 상태에서 undo 테이블스페이스 데이타 파일 유실
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> ! rm -f /u01/app/oracle/orcl2/undotbs01.dbf
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/orcl2/undotbs01.dbf'
==========================================================================================
alert.log
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_dbw0_8671.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/orcl2/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
==========================================================================================
3. undo 데이타파일 restore
SYS@orcl2> @recover_file
FILE# NAME ONLINE_ ERROR CHANGE# TIME
---------- ----------------------------------- ------- -------------------- ---------- ---------
3 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE FILE NOT FOUND 0
SYS@orcl2> !cp -av /home/oracle/backup/arch/cold/undotbs01.dbf /u01/app/oracle/orcl2/
`/home/oracle/backup/arch/cold/undotbs01.dbf' -> `/u01/app/oracle/orcl2/undotbs01.dbf'
4. undo datafile 복구 : Offline recovery
SYS@orcl2> recover tablespace undotbs1
ORA-00279: change 1258701 generated at 04/19/2019 11:39:38 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_17_1005906168.arc
ORA-00280: change 1258701 for thread 1 is in sequence #17
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1259058 generated at 04/19/2019 11:43:35 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_18_1005906168.arc
ORA-00280: change 1259058 for thread 1 is in sequence #18
ORA-00279: change 1259061 generated at 04/19/2019 11:43:36 needed for thread 1
ORA-00289: suggestion : /home/oracle/arch2/arch_1_19_1005906168.arc
ORA-00280: change 1259061 for thread 1 is in sequence #19
Log applied.
Media recovery complete.
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> update hr.employees
set salary = 2000
where employee_id = 100;
1 row updated.
SYS@orcl2> SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk
FROM v$session s,v$transaction t WHERE s.saddr = t.ses_addr;
USERNAME XIDUSN UBAFIL UBABLK USED_UBLK
------------------------------ ---------- ---------- ---------- ----------
SYS 3 3 1979 1
SYS@orcl2> rollback;
Rollback complete.
=========================================================================
다음을 반드시 수행 : 전체 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
=========================================================================
-- DB Open 상태에서 유실
1. DBA SESSION : 현재 UNDO 설정 확인
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1280996
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1280996
3 UNDOTBS1 /u01/app/oracle/orcl2/undotbs01.dbf ONLINE 1280996
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1280996
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1280996
SYS@orcl2> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
2. HR SESSION : DML 작업
HR@orcl2> update employees
set salary = 2000
where employee_id = 100;
1 row updated.
3. DBA SESSION : UNDO data 발생 정보 확인
SYS@orcl2> @undodata
SID SERIAL# USERNAME NAME XIDUSN UBAFIL UBABLK USED_UBLK
---------- ---------- ---------- ------------------ ---------- ---------- ---------- ----------
17 13 HR _SYSSMU10_41314894 10 3 284 1
74
4. DBA SESSION : undo 데이타 파일 삭제해서 장애 발생시킨다.
SYS@orcl2> ! rm /u01/app/oracle/orcl2/undotbs01.dbf
SYS@orcl2> ! ls /u01/app/oracle/orcl2/undotbs01.dbf
ls: /u01/app/oracle/orcl2/undotbs01.dbf: No such file or directory
5. UNDO 테이블스페이스를 생성하여 깨진 언두 테이블스페이스를 대신하도록 한다.
SYS@orcl2> CREATE UNDO TABLESPACE undo1 DATAFILE '/u01/app/oracle/orcl2/undo1.dbf' SIZE 10M AUTOEXTEND ON;
Tablespace created.
SYS@orcl2> alter system set undo_tablespace=undo1;
System altered.
-- 파라미터 수정해서 새로운 언두 테이블스페이스로 지정
SYS@orcl2> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO1
6. 새로운 DBA SESSION
SYS@orcl2> update hr.employees
set salary = 2000
where employee_id = 200;
1 row updated.
7. DBA SESSION
SYS@orcl2> @undodata
SID SERIAL# USERNAME NAME XIDUSN UBAFIL UBABLK USED_UBLK
---------- ---------- ---------- ------------------ ---------- ---------- ---------- ----------
143 34 SYS _SYSSMU13_32350200 13 6 162 1
53$
17 13 HR _SYSSMU10_41314894 10 3 284 1
74$
-- HR session과 SYS session이 잡고 있는 Undo segment의 file 번호가 다르다.
SYS@orcl2> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
-- 현재 UNDO 테이블스페이스인 undotbs1에 HR 세션의 언두 정보가 있기 때문에 drop 불가
7. HR SESSION : 트랜잭션을 종료
-- flsush buffer_cache해서 알아보자.... undo tablespace 로 부터 읽다 에러나는지
HR@orcl2> rollback;
Rollback complete.
8. DBA SESSION
SYS@orcl2> @undodata
SID SERIAL# USERNAME NAME XIDUSN UBAFIL UBABLK USED_UBLK
---------- ---------- ---------- ------------------ ---------- ---------- ---------- ----------
143 34 SYS _SYSSMU13_32350200 13 6 162 1
53$
SYS@orcl2> SELECT a.name,b.status
FROM v$rollname a, v$rollstat b
WHERE a.name IN (SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'UNDOTBS1')
AND a.usn = b.usn;
NAME STATUS
------------------------------ ---------------
_SYSSMU10_4131489474$ PENDING OFFLINE
9. 5번에서 수행한 새로운 DBA SESSION
SYS@orcl2> update hr.employees
set salary = 2000
where employee_id = 200;
1 row updated.
SYS@orcl2> rollback;
Rollback complete.
10. DBA SESSION
SYS@orcl2> @undodata
no rows selected
SYS@orcl2> SELECT a.name,b.status
FROM v$rollname a, v$rollstat b
WHERE a.name IN (SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'UNDOTBS1')
AND a.usn = b.usn;
no rows selected
NAME STATUS
------------------------------ ---------------
_SYSSMU13_3235020053$ PENDING OFFLINE
SYS@orcl2> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SYS@orcl2> !
[oracle@localhost ~]$ vi $ORACLE_HOME/dbs/initorcl2.ora
*.undo_tablespace='UNDO1'
*.undo_management=manual
_offline_rollback_segments=(_SYSSMU13_3235020053$)
SYS@orcl2> startup pfile=$ORACLE_HOME/dbs/initorcl2.ora
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 536874576 bytes
Database Buffers 306184192 bytes
Redo Buffers 5132288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/orcl2/undotbs01.dbf'
SYS@orcl2> alter database datafile '/u01/app/oracle/orcl2/undotbs01.dbf' offline drop;
Database altered.
SYS@orcl2> alter database open;
Database altered.
SYS@orcl2> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO1
-- 이제 언두 테이블스페이스 원상 복구하자.
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
Database opened.
SYS@orcl2> @datafile
FILE# NAME1 NAME2 STATUS CHECKPOINT_CHANGE#
---------- --------------- ----------------------------------- ------- ------------------
1 SYSTEM /u01/app/oracle/orcl2/system01.dbf SYSTEM 1307005
2 SYSAUX /u01/app/oracle/orcl2/sysaux01.dbf ONLINE 1307005
4 USERS /u01/app/oracle/orcl2/users01.dbf ONLINE 1307005
5 EXAMPLE /u01/app/oracle/orcl2/example01.dbf ONLINE 1307005
6 UNDO1 /u01/app/oracle/orcl2/undo1.dbf ONLINE 1307005
SYS@orcl2> CREATE UNDO TABLESPACE undotbs1 DATAFILE '/u01/app/oracle/orcl2/undotbs01.dbf' size 10m autoextend on;
Tablespace created.
SYS@orcl2> alter system set undo_tablespace=undotbs1;
System altered.
SYS@orcl2> drop tablespace undo1 including contents and datafiles;
Tablespace dropped.
SYS@orcl2> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
=========================================================================
다음을 반드시 수행 : 전체 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]Clone DB (0) | 2019.05.03 |
|---|---|
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN03 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN02 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN01 (0) | 2019.04.22 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-control file (0) | 2019.04.22 |