(Les14 備份與恢復的概念)[20180425]

 -閃回查詢，查看在過去某個時間點存在的已提交的歷史數據。帶有AS OF子句的SELECT 命令通過時間戳或SCN引用過去的某一個時間。   

SELECT ... FROM <tab> AS OF SCN SCN號;
SELECT ... FROM <tab> AS OF TIMESTAMP TO_TIMESTAMP('','YYYY-MM-DD HH24:MI:SS');
11:46:45 SQL> select to_char(current_scn) from v$database;
TO_CHAR(CURRENT_SCN)
--------------------------------------------------------------------------------
6533021729470
 
11:47:25 SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2018-04-16 11:48:04
 
11:48:04 SQL> select min(salary) from emp;
 
MIN(SALARY)
-----------
       2090
 
11:48:57 SQL> update emp set salary=salary+1 where salary=2090;
 
已更新 2 個資料列.
 
11:49:35 SQL> commit;
 
確認完成.
 
11:49:38 SQL> select min(salary) from emp;
 
MIN(SALARY)
-----------
 
       2091
11:50:06 SQL> select min(salary) from emp  as of scn 6533021729470;
 
MIN(SALARY)
-----------
       2090
 
11:50:13 SQL> select min(salary) from emp as of timestamp to_timestamp('2018-04-16 11:00:00','YYYY-MM-DD HH24:MI:SS');
 
MIN(SALARY)
-----------
       2090
 

-閃回版本查詢，查看在特定時間間隔內提交的歷史數據。使用SELECT命令的VERSIONS BETWEEN子句（出於性能方面的考慮，使用了現有索引）。
VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
                      
SELECT versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation
  FROM emp
       VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP ('2018-04-16 14:10:08',  'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP ('2018-04-16 14:26:08',  'YYYY-MM-DD HH24:MI:SS');
 
SELECT versions_xid XID,
       versions_startscn START_SCN,
       versions_endscn END_SCN,
       versions_operation OPERATION,
       salary
  FROM emp VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;


-閃回事務處理查詢，查看在事務處理級進行的所有資料庫更改。
SELECT xid,
       start_scn,
       commit_scn,
       operation,
       table_name,
       table_owner
  FROM flashback_transaction_query
 WHERE     table_owner = 'HR'
       AND start_timestamp >=
              TO_TIMESTAMP ('2018-04-16 11:00:00', 'YYYY-MM-DD HH:MI:SS');

　　　　 alter database add logfile group 1 ('/u01/app/oracle/oradata/orcl/p_redo01_01.log','/u02/app/oracle/oradata/orcl/p_redo01_02.log') size 512M;
        alter database add logfile group 2 ('/u01/app/oracle/oradata/orcl/p_redo02_01.log','/u02/app/oracle/oradata/orcl/p_redo02_02.log') size 512M;
        alter database add logfile group 3 ('/u01/app/oracle/oradata/orcl/p_redo03_01.log','/u02/app/oracle/oradata/orcl/p_redo03_02.log') size 512M;

　　　　 alter system set log_archive_format='arch_%r_%t_%s.arc' scope=spfile;
        alter system set log_archive_dest='location=/u01/archive' scope=spfile;
        shutdown immediate
        startup mount 
        alter database archivelog;
        alter database open;