Flashback 기술03
====================================================================================================
restore point 까지 flashback
restore point before_ins
restore point before_upd
두 개 찍고 처음엔 before_upd까지 flashback database 했다가
before_ins까지 다시 flashback database 한다.
====================================================================================================
SYS@orcl2> create table hr.emp_flash_db
as
select employee_id, last_name, salary
from hr.employees
where department_id=60;
Table created.
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
1701441
SYS@orcl2> create restore point before_ins
guarantee flashback database;
Restore point created.
SYS@orcl2> insert into hr.emp_flash_db select * from hr.emp_flash_db;
5 rows created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> create restore point before_upd
guarantee flashback database;
Restore point created.
SYS@orcl2> update hr.emp_flash_db
set salary=500000;
10 rows updated.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select *
from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 500000
104 Ernst 500000
105 Austin 500000
106 Pataballa 500000
107 Lorentz 500000
103 Hunold 500000
104 Ernst 500000
105 Austin 500000
106 Pataballa 500000
107 Lorentz 500000
10 rows selected.
SYS@orcl2> select current_scn
from v$database;
CURRENT_SCN
-----------
1701562
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> flashback database to restore point before_upd;
Flashback complete.
SYS@orcl2> alter database open read only;
Database altered.
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
10 rows selected.
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> flashback database to restore point before_ins;
Flashback complete.
SYS@orcl2> alter database open read only;
Database altered.
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
0
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
1701618
** 실습 정리
SYS@orcl2> drop table hr.emp_flash_db ;
Table dropped.
SYS@orcl2> drop restore point before_ins;
Restore point dropped.
SYS@orcl2> drop restore point before_upd;
Restore point dropped.
-- 위와 동일 하나, 이번에는 resetlogs로 open 한다음에 다시 플래시백 해보자
SYS@orcl2> create table hr.emp_flash_db
as
select employee_id, last_name, salary
from hr.employees
where department_id=60;
Table created.
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
1701818
SYS@orcl2> create restore point before_ins
guarantee flashback database;
Restore point created.
SYS@orcl2> insert into hr.emp_flash_db select * from hr.emp_flash_db;
5 rows created.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> create restore point before_upd
guarantee flashback database;
Restore point created.
SYS@orcl2> update hr.emp_flash_db
set salary=500000;
10 rows updated.
SYS@orcl2> update hr.emp_flash_db
set salary=500000;
10 rows updated.
SYS@orcl2> commit;
Commit complete.
SYS@orcl2> select *
from hr.emp_flash_db;
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
1701918
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> flashback database to restore point before_upd;
Flashback complete.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
10 rows selected.
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> flashback database to restore point before_ins;
Flashback complete.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
1702140
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup mount
ORACLE instance started.
Total System Global Area 431038464 bytes
Fixed Size 1337016 bytes
Variable Size 155191624 bytes
Database Buffers 268435456 bytes
Redo Buffers 6074368 bytes
Database mounted.
SYS@orcl2> flashback database to restore point before_upd;
Flashback complete.
SYS@orcl2> alter database open resetlogs;
Database altered.
SYS@orcl2> select current_scn from v$database;
CURRENT_SCN
-----------
1702181
SYS@orcl2> select * from hr.emp_flash_db;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
103 Hunold 9000
104 Ernst 6000
105 Austin 4800
106 Pataballa 4800
107 Lorentz 4200
10 rows selected.
'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글
| [Oracle DBA]TRANSPORTABLE Tablespace (0) | 2019.05.03 |
|---|---|
| [Oracle DBA]Flashback 기술02 (0) | 2019.05.03 |
| [Oracle DBA]Flashback 기술01 (0) | 2019.05.03 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-Recovery Catalog (0) | 2019.05.03 |
| [Oracle DBA]ArchiveMode 에서의 Backup & Recovery-RMAN04 (0) | 2019.05.03 |