# 요약
  ■ Control File, Data File, Redo Log File 모든 파일이 손상 되었다. 다른 위치에서 복구 수행
- 백업 보유 : Pfile, RMAN Backup, Archive Log File 
- shutdown
- 신규 디스크 위치 확인
- vi pfile > *.control_files='위치수정'
- startup Pfile=위치 nomount
- Restore ControlFile from autobakup;
- alter database mount;
- run {
    sql "alter database rename file ''기존리두'' to ''신규리두''";
    set newname for datafile ' ' to ' ' ;
    set newname for tempfile ' ' to ' ';
    restore database;
    switch datafile all;
    switch tempfile alll;
    recover database;
    set until scn = ' ';
    recover databae;
    alter database open resetlogs;
    }

■ Control File, Data File, Redo Log File 모든 파일들의 위치를 변경 (이관 작업)
- shutdown immediate
- vi pfile > *.control_files='위치수정'
- startup Pfile=위치 mount
- alter database rename file '기존위치' to '신규위치';
- alter database open;

■ RMAN 백업 유형
- backup incremental level 0 database;
- backup incremental level 1 database;
- backup incremental level 1 cumulative database;

■ Block Change Tracking
- alter database enable block change tracking using file ' ';
- alter database disable block change tracking;

■ Flashback Query
- select 칼럼, ... from ~ as of timestamp to_timestamp()

■ Flashback version query
- select versions_xid, 칼럼, ... from 테이블 versions between scn minvalue and maxvalue
- select versions_xid, 칼럼, ... from 테이블 versions between timestamp to_timestamp( ) and to_timestamp( )

■ Flashback Table
- alter table 테이블 enable row movement;
- flashback table 테이블 to timestamp to_timestamp( );
- alter table 테이블 disable row movement;
- flashback table 테이블 to before drop;   --> p.11 : Drop Table 복구 참고


■ Backup
- Pfile
- Control File
- Data File
- Redo Log File (current 상태의 최신 데이터 복구, 아카이브가 없다면 (in)active 상태의 데이터 복구 가능)
- Archive Log File (백업시점부터 연속)

 

RMAN Backup Type LV Size
backup database; Full   1.16G
backup as compressed backupset database; Full   306M
backup incremental level 0 database; Incr 0 1.16G
backup incremental level 1 database; Incr 1 176K





■ Control File, Data File, Redo Log File 모든 파일이 손상 되었다. 다른 위치에서 복구 수행
- 백업 보유 : Pfile, RMAN Backup, Archive Log File 

  SYS@ora11g> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora


SYS@ora11g> create pfile from spfile;


[oracle@oracle ~]$ mkdir /home/oracle/ora_data


[oracle@oracle ~]$ rman target /
connected to target database: ORA11G (DBID=256148331)


RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/ora11g/system01.dbf
2    620      SYSAUX               ***     /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3    14       UNDOTBS              ***     /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/ora11g/users01.dbf
5    331      EXAMPLE              ***     /u01/app/oracle/oradata/ora11g/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/ora11g/temp01.dbf


RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46      Full    304.89M    DISK        00:00:26     30-JAN-24
        BP Key: 69   Status: AVAILABLE  Compressed: YES  Tag: TAG20240130T095054
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095054_lvjkzgrk_.bkp
  List of Datafiles in backup set 46
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1344698    30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2       Full 1344698    30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  3       Full 1344698    30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
  4       Full 1344698    30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5       Full 1344698    30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47      Full    9.95M      DISK        00:00:01     30-JAN-24
        BP Key: 70   Status: AVAILABLE  Compressed: NO  Tag: TAG20240130T095129
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159609889_lvjl0l76_.bkp
  SPFILE Included: Modification time: 29-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 1344767      Ckp time: 30-JAN-24


SYS@ora11g> alter system switch logfile;
SYS@ora11g> alter system switch logfile;
SYS@ora11g> alter system switch logfile;
--Thread 1 advanced to log sequence 4 (LGWR switch)
--  Current log# 1 seq# 4 mem# 0: /u01/app/oracle/oradata/ora11g/redo01.log


SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
    b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1;
GROUP# SEQUENCE# MEMBER                                                     MB ARCHIVED  STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME  FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
     1         4 /u01/app/oracle/oradata/ora11g/redo01.log                  50 NO        CURRENT
30-JAN-24         1345712   2.8147E+14

     2         2 /u01/app/oracle/oradata/ora11g/redo02.log                  50 YES       INACTIVE
30-JAN-24         1345706      1345709

     3         3 /u01/app/oracle/oradata/ora11g/redo03.log                  50 YES       INACTIVE
30-JAN-24         1345709      1345712


SELECT name,sequence#,first_change#,first_time,next_change#,next_time
FROM v$archived_log where name is not null order by next_time;
NAME                                               SEQUENCE# FIRST_CHANGE# FIRST_TIME  NEXT_CHANGE# NEXT_TIME
-------------------------------------------------- --------- ------------- ----------- ------------ ---------
/home/oracle/arch1/arch_1_1_1159549594.arc                 1       1333566 29-JAN-24        1345706 30-JAN-24
/home/oracle/arch1/arch_1_2_1159549594.arc                 2       1345706 30-JAN-24        1345709 30-JAN-24
/home/oracle/arch1/arch_1_3_1159549594.arc                 3       1345709 30-JAN-24        1345712 30-JAN-24


SYS@ora11g> ! ls /home/oracle/arch1/
arch_1_1_1159549594.arc  arch_1_2_1159549594.arc  arch_1_3_1159549594.arc


SYS@ora11g> shutdown immediate



# 장애
! rm /u01/app/oracle/oradata/ora11g/*.*



SYS@ora11g> startup
ORA-00205: error in identifying control file, check alert log for more info


SYS@ora11g> select status from v$instance;
STATUS
-------------
STARTED


SYS@ora11g> ! ls /u01/app/oracle/oradata/ora11g/*.*
ls: cannot access /u01/app/oracle/oradata/ora11g/*.*: No such file or directory

>> 컨트롤파일, 데이터파일, 리두로그파일 없다
> 캔슬베이스리커버리 > rnam : scn으로 시도


SYS@ora11g> ! ls /home/oracle/arch1/
arch_1_1_1159549594.arc  arch_1_2_1159549594.arc  arch_1_3_1159549594.arc

>> 아카이브는 살아있다
# 복구
  SYS@ora11g> shutdown abort


[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ ls
cntrlora11g.dbf  hc_clone.dat  hc_ora11g.dat  initclone.ora  init.ora  initora11g.ora  
lkCLONE  lkDUMMY  lkORA11G  orapwora11g  snapcf_ora11g.f  spfileora11g.ora

>> initora11g.ora : pfile 있다


[oracle@oracle dbs]$ vi initora11g.ora

*.control_files='/home/oracle/ora_data/control01.ctl'

: 위치 수정


SYS@ora11g> startup pfile=$ORACLE_HOME/dbs/initora11g.ora nomount
ORACLE instance started.

>> 또는 RMAN에서 동일하게 가능


RMAN> restore controlfile from autobackup;
Starting restore at 30-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORA11G
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159609889_lvjl0l76_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159609889_lvjl0l76_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/ora_data/control01.ctl
Finished restore at 30-JAN-24

>> 또는 autobackup 실패 시 controlfile piece file 로 수동 작업


RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1


RMAN> run {
    sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo01.log'' to ''/home/oracle/ora_data/redo01.log''";
    sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo02.log'' to ''/home/oracle/ora_data/redo02.log''";
    sql "alter database rename file ''/u01/app/oracle/oradata/ora11g/redo03.log'' to ''/home/oracle/ora_data/redo03.log''";
    set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf';
    set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf';
    set newname for datafile 3 to '/home/oracle/ora_data/undotbs01.dbf';
    set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf';
    set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf';
    set newname for tempfile 1 to '/home/oracle/ora_data/temp01.dbf';
    restore database;
    switch datafile all;
    switch tempfile all;
    recover database;
    alter database open resetlogs;
    }

>> 일단 기본으로 시도

sql statement: alter database rename file ''/u01/app/oracle/oradata/ora11g/redo01.log'' to ''/home/oracle/ora_data/redo01.log''
sql statement: alter database rename file ''/u01/app/oracle/oradata/ora11g/redo02.log'' to ''/home/oracle/ora_data/redo02.log''
sql statement: alter database rename file ''/u01/app/oracle/oradata/ora11g/redo03.log'' to ''/home/oracle/ora_data/redo03.log''
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 30-JAN-24
Starting implicit crosscheck backup at 30-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 30-JAN-24

Starting implicit crosscheck copy at 30-JAN-24
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-JAN-24

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159194558_lv3wfgtv_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159197639_lv3zfqxc_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159201597_lv439fvt_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159202764_lv44fxn5_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159210598_lv4d2q0r_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159211122_lv4dm2t6_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_25/o1_mf_s_1159213118_lv4gkgnv_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159523091_lvfx83vs_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159524233_lvfyctbw_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159525098_lvfz6tx9_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_n_1159529588_lvg3m53c_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159530577_lvg5wrm3_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159537147_lvgbzd51_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_29/o1_mf_s_1159547756_lvgobwvy_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159609889_lvjl0l76_.bkp

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/ora_data/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/ora_data/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/ora_data/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/ora_data/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/ora_data/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095054_lvjkzgrk_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095054_lvjkzgrk_.bkp tag=TAG20240130T095054
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 30-JAN-24

datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=1159612475 file name=/home/oracle/ora_data/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=1159612475 file name=/home/oracle/ora_data/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=1159612475 file name=/home/oracle/ora_data/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=1159612475 file name=/home/oracle/ora_data/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=1159612475 file name=/home/oracle/ora_data/example01.dbf

renamed tempfile 1 to /home/oracle/ora_data/temp01.dbf in control file

Starting recover at 30-JAN-24
using channel ORA_DISK_1

starting media recovery
archived log file name=/home/oracle/arch1/arch_1_1_1159549594.arc thread=1 sequence=1
archived log file name=/home/oracle/arch1/arch_1_2_1159549594.arc thread=1 sequence=2
archived log file name=/home/oracle/arch1/arch_1_3_1159549594.arc thread=1 sequence=3
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/30/2024 10:34:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 1345712

>> seq#4 > currunt 상태였던 리두가 없다 > 여기는 복구불가


[oracle@oracle ~]$ cd ora_data/

[oracle@oracle ora_data]$ ls
control01.ctl  example01.dbf  sysaux01.dbf  system01.dbf  undotbs01.dbf  users01.dbf

[oracle@oracle ora_data]$ rm *.dbf

[oracle@oracle ora_data]$ ls
control01.ctl

>> 중간에 멈췄으니 지우고 다시 실행


RMAN> run {
    set newname for datafile 1 to '/home/oracle/ora_data/system01.dbf';
    set newname for datafile 2 to '/home/oracle/ora_data/sysaux01.dbf';
    set newname for datafile 3 to '/home/oracle/ora_data/undotbs01.dbf';
    set newname for datafile 4 to '/home/oracle/ora_data/users01.dbf';
    set newname for datafile 5 to '/home/oracle/ora_data/example01.dbf';
    set newname for tempfile 1 to '/home/oracle/ora_data/temp01.dbf';
    restore database;
    switch datafile all;    -- 컨트롤 파일 내 정보 변경
    switch tempfile all;    
    set until scn = 1345712;
    recover database;
    alter database open resetlogs;
    }

>> 리두는 컨트롤 파일에 이미 저장되어 제외

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 30-JAN-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /home/oracle/ora_data/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /home/oracle/ora_data/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /home/oracle/ora_data/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/ora_data/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /home/oracle/ora_data/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095054_lvjkzgrk_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnndf_TAG20240130T095054_lvjkzgrk_.bkp tag=TAG20240130T095054
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 30-JAN-24

executing command: SET until clause

Starting recover at 30-JAN-24
using channel ORA_DISK_1

starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file /home/oracle/arch1/arch_1_1_1159549594.arc
archived log for thread 1 with sequence 2 is already on disk as file /home/oracle/arch1/arch_1_2_1159549594.arc
archived log for thread 1 with sequence 3 is already on disk as file /home/oracle/arch1/arch_1_3_1159549594.arc
archived log file name=/home/oracle/arch1/arch_1_1_1159549594.arc thread=1 sequence=1
archived log file name=/home/oracle/arch1/arch_1_2_1159549594.arc thread=1 sequence=2
archived log file name=/home/oracle/arch1/arch_1_3_1159549594.arc thread=1 sequence=3
media recovery complete, elapsed time: 00:00:02
Finished recover at 30-JAN-24

database opened


SYS@ora11g> conn / as sysdba
Connected.


SYS@ora11g> select status from v$instance;
STATUS
-------------
OPEN


SYS@ora11g> select name from v$controlfile;
NAME
--------------------------------------------------
/home/oracle/ora_data/control01.ctl


SYS@ora11g> select name from v$datafile;
NAME
--------------------------------------------------
/home/oracle/ora_data/system01.dbf
/home/oracle/ora_data/sysaux01.dbf
/home/oracle/ora_data/undotbs01.dbf
/home/oracle/ora_data/users01.dbf
/home/oracle/ora_data/example01.dbf


SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
    b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1;
GROUP# SEQUENCE# MEMBER                                                     MB ARCHIVED  STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME  FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
     1         1 /home/oracle/ora_data/redo01.log                           50 NO        CURRENT
30-JAN-24         1345713   2.8147E+14

     2         0 /home/oracle/ora_data/redo02.log                           50 YES       UNUSED
                        0            0

     3         0 /home/oracle/ora_data/redo03.log                           50 YES       UNUSED
                        0            0


SYS@ora11g> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string


SYS@ora11g> create spfile from pfile;


SYS@ora11g> shutdown immediate


SYS@ora11g> startup


SYS@ora11g> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileora11g.ora





■ Control File, Data File, Redo Log File 모든 파일들의 위치를 변경 (이관 작업)
- RMAN에서 작업시에도 조합하여 편한 방법으로 사용하면 됨

/home/oracle/ora_data/ -> /u01/app/oracle/oradata/ora11g/

select name from v$controlfile
union 
select name from v$datafile
union 
select member from v$logfile
union 
select name from v$tempfile;
NAME
--------------------------------------------------
/home/oracle/ora_data/control01.ctl
/home/oracle/ora_data/example01.dbf
/home/oracle/ora_data/redo01.log
/home/oracle/ora_data/redo02.log
/home/oracle/ora_data/redo03.log
/home/oracle/ora_data/sysaux01.dbf
/home/oracle/ora_data/system01.dbf
/home/oracle/ora_data/temp01.dbf
/home/oracle/ora_data/undotbs01.dbf
/home/oracle/ora_data/users01.dbf


SYS@ora11g> shutdown immediate


! mv /home/oracle/ora_data/*.* /u01/app/oracle/oradata/ora11g/



[oracle@oracle ora_data]$ cd $ORACLE_HOME/dbs

[oracle@oracle dbs]$ vi initora11g.ora

*.control_files='/u01/app/oracle/oradata/ora11g/control01.ctl'

: 위치 수정


SYS@ora11g> startup pfile=$ORACLE_HOME/dbs/initora11g.ora mount
Database mounted.


select name from v$controlfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/control01.ctl


# Redo Log File 새로운 위치로 이관 작업
alter database rename file '/home/oracle/ora_data/redo01.log' to '/u01/app/oracle/oradata/ora11g/redo01.log';
alter database rename file '/home/oracle/ora_data/redo02.log' to '/u01/app/oracle/oradata/ora11g/redo02.log';
alter database rename file '/home/oracle/ora_data/redo03.log' to '/u01/app/oracle/oradata/ora11g/redo03.log';

> Redo Log File : current 상태는 운영중 수정 불가


SYS@ora11g> select member from v$logfile;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/redo01.log
/u01/app/oracle/oradata/ora11g/redo03.log
/u01/app/oracle/oradata/ora11g/redo02.log


# Data File 새로운 위치로 이관 작업
alter database rename file '/home/oracle/ora_data/system01.dbf' to '/u01/app/oracle/oradata/ora11g/system01.dbf';
alter database rename file '/home/oracle/ora_data/sysaux01.dbf' to '/u01/app/oracle/oradata/ora11g/sysaux01.dbf';
alter database rename file '/home/oracle/ora_data/undotbs01.dbf' to '/u01/app/oracle/oradata/ora11g/undotbs01.dbf';
alter database rename file '/home/oracle/ora_data/users01.dbf' to '/u01/app/oracle/oradata/ora11g/users01.dbf';
alter database rename file '/home/oracle/ora_data/example01.dbf' to '/u01/app/oracle/oradata/ora11g/example01.dbf';

> System File : 운영중 수정 불가


SYS@ora11g> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/system01.dbf
/u01/app/oracle/oradata/ora11g/sysaux01.dbf
/u01/app/oracle/oradata/ora11g/undotbs01.dbf
/u01/app/oracle/oradata/ora11g/users01.dbf
/u01/app/oracle/oradata/ora11g/example01.dbf


# Temp File 새로운 위치로 이관 작업
alter database rename file '/home/oracle/ora_data/temp01.dbf' to '/u01/app/oracle/oradata/ora11g/temp01.dbf';


SYS@ora11g> select name from v$tempfile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/ora11g/temp01.dbf


SYS@ora11g> alter database open;
Database altered.





■ RMAN 백업 유형
- FULL 백업은 상용된 모든 데이터 파일 블록을 포함한다.
- Level 0 Incremental 백업은 레벨 0으로 표시된 FULL 백업과 동일
- Cumulative Level 1 Incremental 백업은 마지막 레벨 0 Incremental 백업 이후 수정한 블록만 포함
- Differential Level 1 Incremental 백업은 마지막 Incremental 백업 이후 수정한 블록만 포함

        일  월  화  수  목   금  토  일
BK      F   D   D   C   D   D   D   F
Level   0   1   1   1   1   1   1   0
                    ㄴ Cumulative : Full 기준 이후 수정 블록
            ㄴ Differential : 어제 백업 이후 수정 블록만
        ㄴ Full

- 레벨 0에서 Incremental Backup (Full Backup)
RMAN> backup incremental level 0 database;

- Differential Incremental Backup (Incremental Backup 이후에 변경한 블록만 받자)
RMAN> backup incremental level 1 database;

- Cumulative Incremental Level 1 Database (Incremental Backup(0) 이후에 변경한 블록들 다 받자)
RMAN> backup incremental level 1 cumulative database;




■ Block Change Tracking (10g)
- 변경사항 추적 파일에서 변경된 블록을 기록한다.
- CTWR :Background Process
- 활성화된 경우 RMAN에 의해 자동으로사용된다.

SYS@ora11g> ! ps -ef | grep ctwr
oracle   23707     1  0 14:55 ?        00:00:00 ora_ctwr_ora11g


# 파일을 생성하면서 활성화, 파일명.확장자 마음대로, 바이너리형식의 파일
alter database enable block change tracking using file '/home/oracle/backup/rman/block_tracking.txt';


# 비활성화
alter database disable block change tracking;


SYS@ora11g> ! ls -l /home/oracle/backup/rman/block_tracking.txt
-rw-r-----. 1 oracle oinstall 11600384 Jan 30 14:57 /home/oracle/backup/rman/block_tracking.txt

- 백업중에 전체 데이터 파일을 스캔하지 않도록 incremental 백업을 최적화한다.


select * from v$block_change_tracking;
STATUS        FILENAME                                   BYTES
------------- --------------------------------------------- ----------
ENABLED       /home/oracle/backup/rman/block_tracking.txt     11599872





# db_create_file_dest

SYS@ora11g> show parameter db_create_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string

- db_create_file_dest 파라미터를 설정한 경우 블록 변경 사항 추적 파일을 설정할 필요가 없다.
- OMF(Oracle Managed File_) 방식 : 설정된 위치로 파일이 알아서 생성 된다

alter database enable block change tracking;
alter database disable block change tracking;



# backup incremental level 0
  RMAN> backup incremental level 0 database;
Starting backup at 30-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=188 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-JAN-24
channel ORA_DISK_1: finished piece 1 at 30-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd0_TAG20240130T150523_lvk4f4g0_.bkp tag=TAG20240130T150523 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 30-JAN-24

Starting Control File and SPFILE Autobackup at 30-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159628758_lvk4g7p5_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-JAN-24


RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
50      Incr 0  1.16G      DISK        00:00:33     30-JAN-24
        BP Key: 73   Status: AVAILABLE  Compressed: NO  Tag: TAG20240130T150523
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd0_TAG20240130T150523_lvk4f4g0_.bkp
  List of Datafiles in backup set 50
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 1363933    30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2    0  Incr 1363933    30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  3    0  Incr 1363933    30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
  4    0  Incr 1363933    30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5    0  Incr 1363933    30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51      Full    9.95M      DISK        00:00:01     30-JAN-24
        BP Key: 74   Status: AVAILABLE  Compressed: NO  Tag: TAG20240130T150558
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159628758_lvk4g7p5_.bkp
  SPFILE Included: Modification time: 30-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 1363964      Ckp time: 30-JAN-24
# backup incremental level 1
  RMAN> backup incremental level 1 database;
Starting backup at 30-JAN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=190 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 30-JAN-24
channel ORA_DISK_1: finished piece 1 at 30-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd1_TAG20240130T151500_lvk4z5fc_.bkp tag=TAG20240130T151500 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JAN-24

Starting Control File and SPFILE Autobackup at 30-JAN-24
piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159629304_lvk4z8c2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 30-JAN-24


RMAN> list backup;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
52      Incr 1  1008.00K   DISK        00:00:01     30-JAN-24
        BP Key: 75   Status: AVAILABLE  Compressed: NO  Tag: TAG20240130T151500
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd1_TAG20240130T151500_lvk4z5fc_.bkp
  List of Datafiles in backup set 52
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 1364350    30-JAN-24 /u01/app/oracle/oradata/ora11g/system01.dbf
  2    1  Incr 1364350    30-JAN-24 /u01/app/oracle/oradata/ora11g/sysaux01.dbf
  3    1  Incr 1364350    30-JAN-24 /u01/app/oracle/oradata/ora11g/undotbs01.dbf
  4    1  Incr 1364350    30-JAN-24 /u01/app/oracle/oradata/ora11g/users01.dbf
  5    1  Incr 1364350    30-JAN-24 /u01/app/oracle/oradata/ora11g/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53      Full    9.95M      DISK        00:00:00     30-JAN-24
        BP Key: 76   Status: AVAILABLE  Compressed: NO  Tag: TAG20240130T151504
        Piece Name: /u01/app/oracle/fast_recovery_area/ORA11G/autobackup/2024_01_30/o1_mf_s_1159629304_lvk4z8c2_.bkp
  SPFILE Included: Modification time: 30-JAN-24
  SPFILE db_unique_name: ORA11G
  Control File Included: Ckp SCN: 1364358      Ckp time: 30-JAN-24
# 작업중 장애 발생
  SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
    b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1;
GROUP# SEQUENCE# MEMBER                                             MB ARCHIVED  STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME  FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
     1         1 /u01/app/oracle/oradata/ora11g/redo01.log                  50 YES       INACTIVE
30-JAN-24         1345713      1363291

     2         2 /u01/app/oracle/oradata/ora11g/redo02.log                  50 NO        CURRENT
30-JAN-24         1363291   2.8147E+14

     3         0 /u01/app/oracle/oradata/ora11g/redo03.log                  50 YES       UNUSED
                        0            0


select STATUS,FILENAME,BYTES/1024/1024 mb from v$block_change_tracking;
STATUS        FILENAME                                      MB
------------- --------------------------------------------- ----------
ENABLED       /home/oracle/backup/rman/block_tracking.txt      11.0625


SYS@ora11g> update hr.employees set salary = salary * 1.1 where department_id = 20;

SYS@ora11g> commit;


SYS@ora11g> create table hr.inc_emp as select * from hr.employees;


SYS@ora11g> alter system switch logfile;


SELECT a.group#,b.sequence#,a.member,b.bytes/1024/1024 mb,b.archived,b.status,
    b.first_time,b.first_change#,b.next_change#
FROM v$logfile a, v$log b WHERE a.group# = b.group# ORDER BY 1;
GROUP# SEQUENCE# MEMBER                                             MB ARCHIVED  STATUS
------ --------- -------------------------------------------------- ---------- --------- -------------
FIRST_TIME  FIRST_CHANGE# NEXT_CHANGE#
----------- ------------- ------------
     1         1 /u01/app/oracle/oradata/ora11g/redo01.log                  50 YES       INACTIVE
30-JAN-24         1345713      1363291

     2         2 /u01/app/oracle/oradata/ora11g/redo02.log                  50 YES       ACTIVE
30-JAN-24         1363291      1364230

     3         3 /u01/app/oracle/oradata/ora11g/redo03.log                  50 NO        CURRENT
30-JAN-24         1364230   2.8147E+14


select STATUS,FILENAME,BYTES/1024/1024 mb from v$block_change_tracking;
STATUS        FILENAME                                      MB
------------- --------------------------------------------- ----------
ENABLED       /home/oracle/backup/rman/block_tracking.txt      11.0625


SELECT e.segment_name,f.tablespace_name, f.file_name, count(*)
FROM dba_extents e, dba_data_files f WHERE f.file_id = e.file_id
AND e.owner = 'HR' and e.segment_name = 'INC_EMP'
group by e.segment_name,f.tablespace_name,f.file_name;
SEGMENT_NAME            TABLESPACE_NAME FILE_NAME                                            COUNT(*)
----------------------- --------------- -------------------------------------------------- ----------
INC_EMP                 USERS           /u01/app/oracle/oradata/ora11g/users01.dbf                  1



# 장애 발생
! rm /u01/app/oracle/oradata/ora11g/users01.dbf



SYS@ora11g> shutdown immediate


SYS@ora11g> startup
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'
# 복구
  [oracle@oracle ~]$ rman target /
connected to target database: ORA11G (DBID=256148331, not open)


RMAN> alter database datafile 4 offline;


RMAN> list failure 45 detail;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
45         HIGH     OPEN      30-JAN-24     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 45
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  8604       HIGH     OPEN      30-JAN-24     Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
    Impact: Some objects in tablespace USERS might be unavailable


RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
45         HIGH     OPEN      30-JAN-24     One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 45
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  8604       HIGH     OPEN      30-JAN-24     Datafile 4: '/u01/app/oracle/oradata/ora11g/users01.dbf' is missing
    Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=171 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/ora11g/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_418264038.hm


RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_418264038.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';


RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/ora11g/ora11g/hm/reco_418264038.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? y
executing repair script

Starting restore at 30-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd0_TAG20240130T150523_lvk4f4g0_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd0_TAG20240130T150523_lvk4f4g0_.bkp tag=TAG20240130T150523
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 30-JAN-24

Starting recover at 30-JAN-24
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd1_TAG20240130T151500_lvk4z5fc_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORA11G/backupset/2024_01_30/o1_mf_nnnd1_TAG20240130T151500_lvk4z5fc_.bkp tag=TAG20240130T151500
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-JAN-24

sql statement: alter database datafile 4 online
repair failure complete


RMAN> report schema;
Report of database schema for database with db_unique_name ORA11G
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    750      SYSTEM               ***     /u01/app/oracle/oradata/ora11g/system01.dbf
2    620      SYSAUX               ***     /u01/app/oracle/oradata/ora11g/sysaux01.dbf
3    16       UNDOTBS              ***     /u01/app/oracle/oradata/ora11g/undotbs01.dbf
4    5        USERS                ***     /u01/app/oracle/oradata/ora11g/users01.dbf
5    331      EXAMPLE              ***     /u01/app/oracle/oradata/ora11g/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/ora11g/temp01.dbf


RMAN> alter database open;


select count(*) from hr.inc_emp;
  COUNT(*)
----------
       107





■ Flashback Query
- 특정 시간에 query를 수행할 수 있다. (특정 시점의 데이터 확인)
- SELECT문의 AS OF 절을 사용하여 데이터를 확인할 시간기록을 지정할 수 있다.
- 데이터 불일치 분석에 유용하다.
- 단, undo_retention에 설정된 시간까지만 보장

SYS@ora11g> ! date
Tue Jan 30 18:50:19 KST 2024

SYS@ora11g> create table hr.emp_30 as select * from hr.employees where department_id = 30;

SYS@ora11g> select count(*) from hr.emp_30;
  COUNT(*)
----------
         6


SYS@ora11g> ! date
Tue Jan 30 18:56:00 KST 2024

SYS@ora11g> update hr.emp_30 set salary = 30000 where employee_id = 114;

SYS@ora11g> commit;


SYS@ora11g> ! date
Tue Jan 30 18:57:06 KST 2024

SYS@ora11g> select salary from hr.emp_30 where employee_id = 114;
    SALARY
----------
     30000

>> 수정 오류!! 업데이트 전 상태를 보고 싶다


SYS@ora11g> select salary 
            from hr.emp_30 as of timestamp to_timestamp ('2024-01-30 18:57:00','yyyy-mm-dd hh24:mi:ss')
            where employee_id = 114;
    SALARY
----------
     11000


SYS@ora11g> show parameter undo_retention
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900

>> undo_retention : 900 = 15분. 작업시간으로부터 15분안에 확인이 가능하다.
>> 시간을 모른다? > Log Miner 사용.

 



■ Flashback version query
- version 절을 사용하여 point-in time(시간범위) 또는 두 scn 사이에 존재하는 행의 모든 버전을 검색할 수 있다.
- 단, undo_retention에 설정된 시간까지만 보장

select * from hr.emp_30;


select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
    1368324 30-JAN-24 04.27.53.348078 PM +09:00


update hr.emp_30 set salary = salary * 1.1 where employee_id = 115;


delete from hr.emp_30 where employee_id = 116;


commit;


select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
    1368339 30-JAN-24 04.28.04.017578 PM +09:00


select employee_id, salary 
from hr.emp_30 as of timestamp to_timestamp('2024-01-30 16:27:00','yyyy-mm-dd hh24:mi:ss');
EMPLOYEE_ID     SALARY
----------- ----------
        114      30000
        115       3100
        116       2900
        117       2800
        118       2600
        119       2500


select employee_id, salary 
from hr.emp_30 as of timestamp to_timestamp('2024-01-30 16:28:00','yyyy-mm-dd hh24:mi:ss');
EMPLOYEE_ID     SALARY
----------- ----------
        114      30000
        115       3410
        117       2800
        118       2600
        119       2500


select versions_xid, employee_id, last_name, salary
from hr.emp_30 versions between scn minvalue and maxvalue;
VERSIONS_XID     EMPLOYEE_ID LAST_NAME             SALARY
---------------- ----------- ------------------------- ----------
03000400E4040000         116 Baida                   2900
03000400E4040000         115 Khoo                    3410
                         114 Raphaely               30000
                         115 Khoo                    3100
                         116 Baida                   2900
                         117 Tobias                  2800
                         118 Himuro                  2600
                         119 Colmenares              2500


select versions_xid, employee_id, last_name, salary
from hr.emp_30 versions between timestamp to_timestamp('2024-01-30 16:27:00','yyyy-mm-dd hh24:mi:ss')
                                    and to_timestamp('2024-01-30 16:28:00','yyyy-mm-dd hh24:mi:ss');
VERSIONS_XID     EMPLOYEE_ID LAST_NAME             SALARY
---------------- ----------- ------------------------- ----------
03000400E4040000         116 Baida                   2900
03000400E4040000         115 Khoo                    3410
                         114 Raphaely               30000
                         115 Khoo                    3100
                         116 Baida                   2900
                         117 Tobias                  2800
                         118 Himuro                  2600
                         119 Colmenares              2500





■ Flashback Table
- 백업으로 복원하지않고 테이블을 특정 시점으로 recovery 할 수 있다.
- 데이터베이스는 온라인 상태를 유지한다. (Backup으로 Time Base Recovery 는 Shutdown 필수)
- Flashback Table 작업을 수행하기위해 Undo Tablespace에서 데이터를 사용한다.
- 권한 부여
    grant flashback on hr.emp_30 to scott;  -- 객체 권한
    grant flashback any table to hr;        -- 시스템 권한(함부로 사용하지 말자)
- Flashback Table에 대한 행 이동이 활성화 되어있어야한다.
- 단, undo_retention에 설정된 시간까지만 보장

select current_scn, systimestamp from v$database;
CURRENT_SCN SYSTIMESTAMP
----------- ---------------------------------------------------------------------------
    1369006 30-JAN-24 04.46.38.387431 PM +09:00

SYS@ora11g> delete from hr.emp_30;

SYS@ora11g> commit;


SYS@ora11g> select * from hr.emp_30;


# Flashback Table에 대한 행 이동이 활성화
SYS@ora11g> alter table hr.emp_30 enable row movement;
Table altered.

SYS@ora11g> flashback table hr.emp_30 to timestamp to_timestamp('2024-01-30 16:46:00','yyyy-mm-dd hh24:mi:ss');
Flashback complete.


SYS@ora11g> select count(*) from hr.emp_30;
  COUNT(*)
----------
         5

>> 테이블이 살아났다.


# Flashback Table에 대한 행 이동이 비활성화
SYS@ora11g> alter table hr.emp_30 disable row movement;
Table altered.

 

'Oracle 11g > Backup & Recovery' 카테고리의 다른 글

77 Backup&Recovery, FBDA, RVWR, DBVerify, RMAN Validate, Offline Backup  (0) 2024.01.31
75 Backup&Recovery, RMAN  (0) 2024.01.29
74 Backup&Recovery, RMAN  (0) 2024.01.26
73 Backup&Recovery, RMAN  (0) 2024.01.25
72 Backup&Recovery, RMAN  (0) 2024.01.24
'Oracle 11g/Backup & Recovery'의 다른글
  • 현재글76 Backup&Recovery, RMAN, Block Change Tracking, Flashback

티스토리툴바