Flashback [펌]

[출처] http://www.cyworld.com/pistory314/2813122

[oracle@localhost ~]$ sqlplus "/as sysdba"                                   <<<sys사용자 접속

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 2 09:03:46 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> startup mount                                          <<<DB mount단계실행(archive log가능상태)
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1220988 bytes
Variable Size             364908164 bytes
Database Buffers          260046848 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> archive log list                                                               <<<archive log리스트    
Database log mode              No Archive Mode                                                           <<<현재 log상태
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     67
Current log sequence           69

SQL> alter database archivelog;                                              <<<arvhive log상태 변경

Database altered.

*만약 alterdatabase archivelog; 명령시 아래 Err발생시

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

이 명령어 전에 shutdown abort를 하였을 경우이다. 따라서 shutdown immediate/normal을 하여 startup을 정상적으로 시켜서 SMON이 복구를 하여 open이 된 후, 다시 shutdown - startup mount하여 alterdatabase archivelog;할 것

SQL> archive log list                                                                      <<<변경확인
'Database log mode             Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     67
Next log sequence to archive   69
Current log sequence           69

SQL> show parameter db_flashback                                               <<<flashback파라미터

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> alter system set db_flashback_retention_target=2880;             <<<flashback저장시간변경

System altered.

SQL> alter database flashback on;                                              <<<flashback작동

Database altered.

SQL> alter database open;                                                            <<<DB open

Database altered.

SQL> conn hr/hr                                                                         <<<hr사용자 접속
Connected.
SQL> create table dem_dept_80                                        <<<table생성(flashback지점생성)
  2  as select * from employees where department_id=80;

Table created.

SQL> conn /as sysdba                                                    <<<sys사용자 접속
Connected.
SQL> select current_scn                                                           <<<scn정보확인
  2  from v$database;

CURRENT_SCN
-----------
    4023682

SQL> drop table hr.dem_dept_80;                                          <<<hr사용자 테이블 삭제

SQL> select current_scn                                                       <<<scn확인(flashback발생)
  2  from v$database;

CURRENT_SCN
-----------
    4023712

SQL> shut immediate                                                          <<<DB정상종료
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount                                                             <<<DB mount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1220988 bytes
Variable Size             364908164 bytes
Database Buffers          260046848 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> flashback database to scn 4023682;                             <<flashback scn지점으로 복원

Flashback complete.

SQL> alter database open read only;                                     <<<DB open

Database altered.

SQL> shut immediate                                                             <<<DB정상종료
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount                                                                <<<DB mount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1220988 bytes
Variable Size             364908164 bytes
Database Buffers          260046848 bytes
Redo Buffers                2969600 bytes
archive log Database mounted.
SQL> archive log list                                                             <<<리스트
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     67
Next log sequence to archive   69
Current log sequence           69
SQL> alter database open resetlogs;                                             <<<로그정보 reset

Database altered.

SQL> archive log list                                                                
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1220988 bytes
Variable Size             369102468 bytes
Database Buffers          255852544 bytes
Redo Buffers                2969600 bytes
Database mounted.

SQL> alter database flashback off;                               <<<flshback끄기(작업후 꼭 끄기)

Database altered.

SQL> alter database open;                                         <<<DB open

Database altered.

SQL>