闪回系列专题(2)基于UNDO技术的闪回特性

基于依赖UNDO技术的FLASHBACK特性有四种,分别是:

1.FLASHBACK TABLE (恢复行级数据)
2.FLASHBACK QUERY
3.FLASHBACK VERSION QUERY
4.FLASHBACK TRANSACTION QUERY

(1)FLASHBACK TABLE

--引用自Oracle官方文档
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table. 

You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause. 

By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior and keep the triggers enabled during the Flashback process.

This clause affects only those database triggers defined on table that are already enabled. To enable currently disabled triggers selectively, use the ALTER TABLE ... enable_disable_clause before you issue the FLASHBACK TABLE statement with the ENABLE TRIGGERS clause. 
--创建测试表:
SQL> conn albert/albert
Connected.
SQL>  create table Liangce as select * from dba_objects;

Table created.

SQL> select object_type,count(*) from liangce group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
EDITION                      1
INDEX PARTITION            370
TABLE SUBPARTITION          32
CONSUMER GROUP              25
SEQUENCE                   182
TABLE PARTITION            358
SCHEDULE                     3
QUEUE                       24
RULE                         1
JAVA DATA                  314
PROCEDURE                  139
OPERATOR                    23
LOB PARTITION                5
DESTINATION                  2
WINDOW                       9
SCHEDULER GROUP              4
DATABASE LINK                1
LOB                        238
PACKAGE                    785
PACKAGE BODY               748
LIBRARY                    149
PROGRAM                     19
RULE SET                    15
CONTEXT                      7
TYPE BODY                  126
JAVA RESOURCE              864
TRIGGER                     69
JOB CLASS                   13
UNDEFINED                   11
DIRECTORY                    5
TABLE                     1600
INDEX                     1978
SYNONYM                  30947
VIEW                      4531
FUNCTION                   131
JAVA CLASS               27024
JAVA SOURCE                  2
INDEXTYPE                    5
CLUSTER                     10
TYPE                      1448
RESOURCE PLAN               10
JOB                         11
EVALUATION CONTEXT          11

43 rows selected.

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20200425 13:22:02

开始“误操作”行为:

SQL> update liangce set owner='Travelskydba' WHERE OWNER <> 'SYS';

33643 rows updated.

SQL> commit;

Commit complete.

SQL> select owner,count(*) from liangce group by owner order by 2 desc;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 38607
Travelskydba                        33643

-----commit后发现为误操作行为-----

执行恢复操作:

SQL> alter table liangce enable row movement;

Table altered.

SQL> flashback table liangce to timestamp to_timestamp('20200425 13:22:02','yyyymmdd hh24:mi:ss'); 

--也可支持to_scn

Flashback complete.

SQL>  select owner,count(*) from liangce group by owner order by 2 desc;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                 38607
PUBLIC                              30932
OLAPSYS                               721
SYSTEM                                622
CTXSYS                                389
WMSYS                                 333
RMAN                                  252
EXFSYS                                192
OTSC                                   77
DBSNMP                                 57
APPUSER                                17
B2C3U_XREZAGENT                        10
OUTLN                                  10
ORACLE_OCM                              8
LCA                                     6
APPQOSSYS                               5
HJ                                      4
DEMO                                    3
TRAVELSKYDBA                            2
LIANGCE                                 2
ALBERT                                  1

21 rows selected.

(2)FLASHBACK QUERY

--引用Oracle 官方文档--
To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.

Uses of Oracle Flashback Query include:

    Recovering lost data or undoing incorrect, committed changes.

    For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.

    Comparing current data with the corresponding data at an earlier time.

    For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.

    Checking the state of transactional data at a particular time.

    For example, you can verify the account balance of a certain day.

    Selecting data that was valid at a particular time or at any time within a user-defined valid time period.

    For example, you can find employees with valid employee information as of a particular timestamp or between a specified start and end time in the specified valid time period. (For more information, see Temporal Validity Support.)

    Simplifying application design by removing the need to store some kinds of temporal data.

    Oracle Flashback Query lets you retrieve past data directly from the database.

    Applying packaged applications, such as report generation tools, to past data.

    Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.

创建测试表,借助闪回查询功能进行数据恢复:

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> show user
USER is "ALBERT"
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20200425 14:06:00

SQL> delete from t1;

72252 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
         0

SQL> select count(*) from t1 as of timestamp to_timestamp('20200425 14:06:00','yyyymmdd hh24:mi:ss');

  COUNT(*)
----------
     72252

SQL> insert into t1 select * from t1 as of timestamp to_timestamp('20200425 14:06:00','yyyymmdd hh24:mi:ss');

72252 rows created.

SQL> commit;

Commit complete.

(3)FLASHBACK VERSION QUERY

--引用自Oracle官方文档--
Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed. 

创建测试表并开始闪回操作:

SQL> select *from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
ALBERT                                777
TRAVELSKYDBA                           20
SYS                                    46
SYS                                    28
SYS                                    29
SYS                                     3
SYS                                    25
SYS                                    41
SYS                                    54

9 rows selected.

SQL> 
SQL> 
SQL> delete from t2 where owner='ALBERT';  --事务1 版本1

1 row deleted.

SQL> delete from t2 where owner='TRAVELSKYDBA';  --事务1 版本1

1 row deleted.

SQL> update t2 set owner='TRAVELSKYDBA'; --事务2 版本2

7 rows updated.

SQL> commit;

Commit complete.

SQL> insert into t2 values('ALBERT',77); --事务3 版本3

1 row created.

SQL> commit;

Commit complete.

一张表经历了多次事务,我们可以利用闪回查询进行不同事务版本中的闪回切换:


SSQL> SELECT owner,
  2         object_id,
  3         versions_startscn,
  4         to_char(versions_starttime, 'yyyymmdd hh24:mi:ss') versions_starttime,
  5         versions_endscn,
  6         to_char(versions_endtime, 'yyyymmdd hh24:mi:ss') versions_endtime,
  7         versions_xid,
  8         versions_operation
  9    FROM t2 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('20200425 15:54:00', 'yyyymmdd hh24:mi:ss') AND TO_TIMESTAMP('20200425 15:58:40', 'yyyymmdd hh24:mi:ss')
 10   order by 3;

OWNER                           OBJECT_ID VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME  VERSIONS_XID     VERSIONS_OPERATION
------------------------------ ---------- ----------------- ------------------ --------------- ----------------- ---------------- ------------------
TRAVELSKYDBA                           20         108825318 20200425 15:54:49                                    0A001700E59E1900 D  --事务1 版本1:delete from t2 where owner='TRAVELSKYDBA'; 
ALBERT                                777         108825318 20200425 15:54:49                                    0A001700E59E1900 D  --事务1 版本1:delete from t2 where owner='ALBERT'; 
TRAVELSKYDBA                            3         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           29         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           28         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           46         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA                           25         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           41         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA'; 
TRAVELSKYDBA                           54         108825426 20200425 15:56:16                                    0A00060036771900 U  --事务2 版本2:update t2 set owner='TRAVELSKYDBA'; 
ALBERT                                 77         108825465 20200425 15:56:56                                    0A000800649D1900 I  --事务3 版本3:insert into t2 values('ALBERT',77);
TRAVELSKYDBA                           20                                            108825318 20200425 15:54:49                  
SYS                                    46                                            108825426 20200425 15:56:16                  
SYS                                    54                                            108825426 20200425 15:56:16                  
SYS                                    29                                            108825426 20200425 15:56:16                  
SYS                                     3                                            108825426 20200425 15:56:16                  
SYS                                    25                                            108825426 20200425 15:56:16                  
SYS                                    41                                            108825426 20200425 15:56:16                  
ALBERT                                777                                            108825318 20200425 15:54:49                  
SYS                                    28                                            108825426 20200425 15:56:16                  

19 rows selected
 
 
--current t2 table状态:

SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54
ALBERT                                 77

8 rows selected.
 
恢复T2表至最初状态(未发生过事务)

SQL> alter table t2 enable row movement;

Table altered.

SQL> flashback table t2 to scn 108825317; VERSIONS_ENDSCN为18

Flashback complete.


SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
ALBERT                                777
TRAVELSKYDBA                           20
SYS                                    46
SYS                                    28
SYS                                    29
SYS                                     3
SYS                                    25
SYS                                    41
SYS                                    54

9 rows selected.

恢复至T2表至INSERT之前状态:

SQL>  flashback table t2  to scn 108825426;

Flashback complete.

SQL>  select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54

7 rows selected.


SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54
ALBERT                                 77

8 rows selected.


SQL> flashback table t2 to scn 108825465;

Flashback complete.

SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                           46
TRAVELSKYDBA                           28
TRAVELSKYDBA                           29
TRAVELSKYDBA                            3
TRAVELSKYDBA                           25
TRAVELSKYDBA                           41
TRAVELSKYDBA                           54
ALBERT                                 77

8 rows selected.

(4)Flashback Transaction Query

--引用自Oracle官方文档---
Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY, whose columns are described in Oracle Database Reference.

The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction. However, there are cases where the UNDO_SQL code is not the exact opposite of the original transaction. For example, a UNDO_SQL INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted. 

闪回事务查询功能中,除了数据恢复之外,还有一项重要的功能是,提供给我们是谁修改了某个表的某行数据,例如,我们发现ALBERT中的T2表被进行了修改,但是不知道是谁修改了,可以利用如下查询进行操作:

SQL> conn p_liangce/p_liangce;
Connected.
SQL> 
SQL> 
SQL> select * from albert.t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
ALBERT                                666

8 rows selected.

SQL> update albert.t2 set object_id=666666 where owner='ALBERT'

1 rows updated.

SQL> commit;

Commit complete.

LIANGCE这个用户对ALBERT 用户下表T2某行进行了UPDATE操作,我们可以用如下方式进行追踪与数据修改

此时ALBERT用户向DBA数据被修改,DBA可以利用闪回事务查询中的LOGON_USER进行修改
SQL> select * from t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
ALBERT                             666666  --原来应为666

8 rows selected.

SQL> 
SQL> select logon_user,
  2         start_timestamp,
  3         commit_timestamp,
  4         operation,
  5         table_name,
  6         table_owner,
  7         row_id,
  8         undo_sql
  9    from flashback_transaction_query
 10   where table_name = 'T2'
 11     AND table_OWNER = 'ALBERT'
 12     and start_timestamp between
 13         to_timestamp('20200425 16:00:00', 'yyyymmdd hh24:mi:ss') and
 14         to_timestamp('20200425 16:30:00', 'yyyymmdd hh24:mi:ss')
 15  ;

LOGON_USER                     START_TIMESTAMP COMMIT_TIMESTAMP OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
------------------------------ --------------- ---------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAA  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAA';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAB  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAB';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAJ  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAJ';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAK  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAK';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAL  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAL';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAM  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAM';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAN  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAN';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAO  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAO';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAI  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','54');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAH  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','41');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAG  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','25');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAF  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','3');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAE  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','29');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAD  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','28');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAC  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','46');
P_LIANGCE                      2020/4/25 16:27 2020/4/25 16:28: UPDATE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAO  update "ALBERT"."T2" set "OBJECT_ID" = '666' where ROWID = 'AAAkGVAANAAD1AFAAO';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAC  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAC';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAD  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAD';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAE  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAE';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAF  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAF';

LOGON_USER                     START_TIMESTAMP COMMIT_TIMESTAMP OPERATION                        TABLE_NAME                                                                       TABLE_OWNER                      ROW_ID              UNDO_SQL
------------------------------ --------------- ---------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAG  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAG';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAH  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAH';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: INSERT                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAI  delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAI';
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAQ  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','54');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAP  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','41');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAO  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','25');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAN  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','3');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAM  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','29');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAL  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','28');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAK  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','46');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAB  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','20');
ALBERT                         2020/4/25 16:05 2020/4/25 16:05: DELETE                           T2                                                                               ALBERT                           AAAkGVAANAAD1AFAAA  insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('ALBERT','777');

-数据恢复:
SQL> update "ALBERT"."T2" set "OBJECT_ID" = '666' where ROWID = 'AAAkGVAANAAD1AFAAO'; --执行UNDO SQL前请谨慎确认是否执行的是误操作之后的反向操作。

1 row updated.

SQL> 
SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> select * from albert.t2;

OWNER                           OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
TRAVELSKYDBA                          666
ALBERT                                666

8 rows selected.

闪回系列专题(1)FlashBack Database

前不久,参加了一个Oracle原厂的网络研讨会,其中宣讲人再介绍Oracle MAA架构时提到了数据恢复的问题,会上宣讲人提到了许多Oracle的闪回技术,周末值守无法出门,那就在这里做一个闪回系列总结实验记录一下,顺便对闪回技术进行一次回顾。

Flashback Database,此项操作在之前的利用DataGuard 恢复误操作数据时提到过,原理一致。

--引用Oracle 官方文档针对FlashBack Database的简要功能
Use the FLASHBACK DATABASE statement to return the database to a past time or system change number (SCN). This statement provides a fast alternative to performing incomplete database recovery.

Following a FLASHBACK DATABASE operation, in order to have write access to the flashed back database, you must reopen it with an ALTER DATABASE OPEN RESETLOGS statement. 

实验环境为 Oracle 11.2.0.4 RAC in 3 nodes

1.首先设置闪回恢复区并且开启闪回特性,否则会抛出如下错误

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

SQL> alter system set db_recovery_file_dest_size=30g;

System altered.

SQL> alter system set db_recovery_file_dest='+DG_DATA';

System altered.
 
SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +DG_DATA
db_recovery_file_dest_size           big integer 30G
recovery_parallelism                 integer     0

SQL> alter database flashback on;

Database altered.

创建测试表,删除测试表数据,以便是否验证数据恢复与否

SQL> create table ftest as select object_id from dba_objects;

Table created.

SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'
-----------------
20200425 10:25:51

SQL> truncate table ftest;

Table truncated.

数据已经被破坏,确认闪回日志写入FRA后,准备开启闪回操作,将RAC节点实例关闭

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

将节点1启动到Mount模式:

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.3495E+11 bytes
Fixed Size                  2270072 bytes
Variable Size            3.6507E+10 bytes
Database Buffers         9.8247E+10 bytes
Redo Buffers              197844992 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('20200425 10:28:10','yyyymmdd hh24:mi:ss');  --时间点为

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from ftest;

  COUNT(*)
----------
     72275

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +DG_ARC
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> 

闪回完成后,将RAC其他节点启动即可。

这里,需要注意的是我们在分配FRA区域时,要考虑到数据库事务繁忙程度,以免造成因为FRA区域满,无法写入flashback log导致闪回失败的情形,我将FRA区域改为30m,进行事务操作,因FRA满,闪回日志无法写入,缺失闪回日志所以无法进行闪回操作。

ORA-19815: WARNING: db_recovery_file_dest_size of 31457280 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Setting Resource Manager plan SCHEDULER[0x32DE]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Apr 25 11:29:54 2020
Beginning global checkpoint up to RBA [0x79.272.10], SCN: 108173284
Completed checkpoint up to RBA [0x79.272.10], SCN: 108173284


QL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,
-----------------
20200425 11:31:21


SQL> flashback database to timestamp to_timestamp('20200425 11:30:00','yyyymmdd hh24:mi:ss');
flashback database to timestamp to_timestamp('20200425 11:30:00','yyyymmdd hh24:mi:ss')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.


SQL> select * from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
           108194136 25-APR-20             1440     1467973632                        0

SQL> 
SQL> 
SQL> 
SQL> select * from V$RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                      4096                         0               6
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.


--可以利用v$flashback_database_log与V$RECOVERY_AREA_USAGE视图来监控目前闪回日志的大小与可用空间。
--可以利用如下方式估算FRA的合理配置大小
SQL> set numwidth 16
SQL> select to_char(BEGIN_TIME,'yyyymmdd hh24:mi:ss') BEGIN_TIME,to_char(END_TIME,'yyyymmdd hh24:mi:ss'),FLASHBACK_DATA,DB_DATA,REDO_DATA,ESTIMATED_FLASHBACK_SIZE from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME        TO_CHAR(END_TIME,   FLASHBACK_DATA          DB_DATA        REDO_DATA ESTIMATED_FLASHBACK_SIZE
----------------- ----------------- ---------------- ---------------- ---------------- ------------------------
20200425 13:44:03 20200425 14:44:05         85270528        113090560         99827200             134545121280
20200425 12:37:54 20200425 13:44:03       6095396864      11968888832      14131610624             132688896000

ORA-15028: ASM file ‘+DG_ARC***’ not dropped; currently being accessed 案例

昨日,接到硬件团队同事电话,告知一个数据库出现内存耗尽,需要重启。查询了一下配置,确认此数据库database_role为 physical standby,随后硬件团队同事进行了服务器重启。

服务器重启过后,启动数据库,但是数据库登录依然缓慢,因为我们配置了broker,会自动启用实时应用:

alter database recover managed standby database using current logfile disconnect from session;

但是奇怪的是,数据库并没有应用日志,一直在处于waiting for log的状态,等待的日志文件已经在备库本地,无奈之下,手工注册日志后数据库进行了日志应用,但是很块MRP0进程继续处于 waiting for log的状态 ,再次注册日志却抛出了

ORA-00311: cannot read header from archiveed log
ORA-27072: File I/O error

看来这次故障并不是简单的内存耗尽,严重怀疑是存储链路层面出现了问题。向硬件团队反馈后,准备断掉主备库DataGuard关系,等存储问题恢复后进行重构。

将DataGuard断掉后,验证一下主库归档备份的状态,发现了更加奇怪的现象:

    RECID OPERATION                         STATUS                  START_TIME          END_TIME            OBJECT_TYPE
---------- --------------------------------- ----------------------- ------------------- ------------------- -------------	
     54558 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:30:37 2020-04-16 23:30:50 ARCHIVELOG
     54556 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:19:42 2020-04-16 23:19:57 ARCHIVELOG
     54554 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:15:10 2020-04-16 23:15:24 ARCHIVELOG
     54552 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 22:58:50 2020-04-16 22:59:12 ARCHIVELOG
     54550 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 21:58:51 2020-04-16 21:59:08 ARCHIVELOG
     54548 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 20:58:51 2020-04-16 20:59:04 ARCHIVELOG
     54546 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 19:58:51 2020-04-16 19:59:04 ARCHIVELOG


input archived log thread=1 sequence=54511 RECID=217898 STAMP=1037920783
input archived log thread=2 sequence=54482 RECID=217899 STAMP=1037920784
channel ch00: starting piece 1 at 16-APR-20
channel ch00: finished piece 1 at 16-APR-20
piece handle=arch_Travelskydba_std_27847_1_1037920788 tag=TAG20200416T231948 comment=API Version 2.0,MMS Version 5.0.0.0
channel ch00: backup set complete, elapsed time: 00:00:07
channel ch00: deleting archived log(s)
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54501.352.1037918509 RECID=217870 STAMP=1037918508
RMAN-08118: WARNING: could not delete the following archived redo log
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54501.352.1037918509 thread=1 sequence=54501
error from target database:
ORA-15028: ASM file '+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54501.352.1037918509' not dropped; currently being accessed

archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54502.355.1037918877 RECID=217873 STAMP=1037918877
RMAN-08118: WARNING: could not delete the following archived redo log
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54502.355.1037918877 thread=1 sequence=54502
error from target database:
ORA-15028: ASM file '+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54502.355.1037918877' not dropped; currently being accessed

archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54474.308.1037918875 RECID=217872 STAMP=1037918875
RMAN-08118: WARNING: could not delete the following archived redo log
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54474.308.1037918875 thread=2 sequence=54474
error from target database:
ORA-15028: ASM file '+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54474.308.1037918875' not dropped; currently being accessed

archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54481.388.1037920773 RECID=217897 STAMP=1037920772
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54510.318.1037920771 RECID=217896 STAMP=1037920770
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_1_seq_54511.332.1037920783 RECID=217898 STAMP=1037920783
archived log file name=+DG_ARC/Travelskydba_std/archivelog/2020_04_16/thread_2_seq_54482.321.1037920785 RECID=217899 STAMP=1037920784
Finished backup at 16-APR-20

released channel: ch00	

备份日志中出现了ORA-15028错误, error from target database:
ORA-15028: ASM file not dropped; currently being accessed , 可是主备库关系已经断开,备库理论上不需要主库的归档日志并且主库归档进程也是正常的。

再次登录备库,找到了异常的跟因:备库已经shutdown complete,但是操作系统中PMON与arch进程却没有终止(即使用操作系统kill -9也无果)。

随后将备库操作系统关机,再次执行主库的归档备份后, ORA-15028 错误不再出现,备份成功完成

     RECID OPERATION                         STATUS                  START_TIME          END_TIME            OBJECT_TYPE
---------- --------------------------------- ----------------------- ------------------- ------------------- -------------
     54560 BACKUP                            COMPLETED               2020-04-16 23:32:37 2020-04-16 23:32:50 ARCHIVELOG
     54558 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:30:37 2020-04-16 23:30:50 ARCHIVELOG
     54556 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:19:42 2020-04-16 23:19:57 ARCHIVELOG
     54554 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 23:15:10 2020-04-16 23:15:24 ARCHIVELOG
     54552 BACKUP                            COMPLETED WITH WARNINGS 2020-04-16 22:58:50 2020-04-16 22:59:12 ARCHIVELOG

随后在Oracle Support上搜寻了一下这个错误,文档 Doc ID 1466848.1 进行了非常详细的描述,对应本次的故障案例,备库ARCH进程夯死,传输接收时出现异常,从而主库没有及时的释放archive log的lock,RMAN备份archivelog后无法删除文件。

ORACLE Flashback+DataGuard进行数据恢复

上周,在公司中心范围内进行了一次技术分享,主要阐述一下Datagurd技术的使用范围,其中涉及一些Flashback技术的应用:我们主要利用Flashback闪回特性进行应用人员的误操作,进行恢复。

实际操作流程如下:

主库:Travelskydba            
备库:Travelskydba_std

主库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba                   READ WRITE           PRIMARY          YES NO


备库:	
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES

SQL> show parameter db_flashback_retention_target   

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440

主库执行事务:
SQL> create user albert identified by albert;

User created.

SQL> grant connect,resource to albert;

Grant succeeded.

SQL> conn albert/albert
Connected.

SQL> create table Travelskydba (id1 number,id2 number,id3 number);

Table created.

SQL> insert into Travelskydba values(1,2,3);

1 row created.

SQL> insert into Travelskydba values(4,5,6);

1 row created.

SQL> commit;

Commit complete.

主库:
SQL>  select * from albert.Travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
         4          5          6

SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   20619438


--开启实时应用的备库已经完成redo应用,数据已经保持与主库同步了:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES

SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         4          5          6

有时我们接收到应用人员的电话,数据误删除了,或者对数据的调研不足,导致实时应用的数据当做历史数据删除掉了,请求DBA需要进行数据恢复,此时我们可以利用DataGuard 备库已经开启的flashback特性进行数据恢复。

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES   
SQL> select OLDEST_FLASHBACK_SCN,to_char(OLDEST_FLASHBACK_TIME,'yyyymmdd hh24:mi:ss') time,RETENTION_TARGET,FLASHBACK_SIZE,ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TIME              RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ----------------- ---------------- -------------- ------------------------
            20611974 20200325 12:16:12             1440      629145600               1323294720


如果数据量较少,主库可以利用DBLINK 进行数据恢复:
SQL> show user
USER is "ALBERT"
SQL> insert into travelskydba select * from albert.travelskydba@TRAVELSKYDBA_LINK where id1=1;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
         4          5          6


--数据恢复后,备库可以继续进行实时应用		 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1543505184 bytes
Database Buffers         2550136832 bytes
Redo Buffers               12857344 bytes
Database mounted.
Database opened.		

--备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0
  2  /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1       6942          1             5            5

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1       6942          1             5            5
RFS       CLOSING      UNKNOWN           1       6944          1             0            0
RFS       OPENING      UNKNOWN           1       6943          0             0            0
RFS       OPENING      UNKNOWN           1       6942          0             0            0
RFS       OPENING      UNKNOWN           1       6946          0             0            0
RFS       OPENING      UNKNOWN           1       6948          0             0            0

6 rows selected.
 
 
--主库:
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from gv$managed_standby where process in ('LNS','RFS','MRP0') and  THREAD# <> 0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          5             0            0

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          6             0            0

SQL> /
/
PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6950          7             0            0

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/archive_Travelskydba/Traveskydba
Oldest online log sequence     6948
Next log sequence to archive   6950
Current log sequence           6950

上述情形适用于误删除数据量较小的情况,如果遇到误删除的数据较多,利用DB_LINK的方式效率相对是比较低下的,我们需要将备库打开至read write mode,将数据导入导出,从而达到进行数据恢复的目的

--备库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba  Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES



SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

主库误删除用户:
SQL> drop user albert cascade;

User dropped.

备库:
SQL> select * from albert.travelskydba;
select * from albert.travelskydba
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> create restore point tra guarantee flashback database; --创建resotre point

Restore point created.

SQL>  flashback database to scn 20668180;

Flashback complete.


SQL>  ALTER DATABASE ACTIVATE STANDBY DATABASE;--将备库打开至可读可写状态

Database altered.

SQL> alter database open;

Database altered.


SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  READ WRITE           PRIMARY          YES YES


SQL> select * from albert.travelskydba; --数据恢复完成,将此用户导出,导入恢复至主库

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3
		 
SQL> create directory tra_dir as '/home/ora11g/';

Directory created.

SQL> 
SQL> grant read,write,execute on directory tra_dir to public;

Grant succeeded.


[ora11g@albert ]$ expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log 

Export: Release 11.2.0.4.0 - Production on Wed Mar 25 09:59:48 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=expdp_20200325.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11g/albert_dmp.20200325
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Mar 25 10:00:08 2020 elapsed 0 00:00:17

[ora11g@albert ~]$ scp albert_dmp.20200325 ******:/home/ora11g
The authenticity of host '*******' can't be established.
RSA key fingerprint is 45:b9:52:12:86:55:8d:e5:33:8b:b8:59:02:68:81:ff.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '******' (RSA) to the list of known hosts.
ora11g@******'s password: 
albert_dmp.20200325                                                                                                                                     100%  180KB 180.0KB/s   00:00 


--导入主库:

SQL> create directory tra_dir as '/home/ora11g/';

Directory created.

SQL> grant read,write,execute on directory tra_dir to public;

Grant succeeded.


[ora11g@vm-kvm17146-ora ~]$ impdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log 

Import: Release 11.2.0.4.0 - Production on Wed Mar 25 20:28:48 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200325 logfile=impdp_20200325.log 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Mar 25 20:28:59 2020 elapsed 0 00:00:04

主库数据恢复:
SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

主库数据已经恢复,但是我们现在要继续处理备库,让备库继续进行实时应用

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount; 
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> flashback database to restore point tra; --闪回至restore point

Flashback complete.


SQL> alter database convert to physical standby; 更新control_file,database_role为physical standby

Database altered.


SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON
--------- ------------------------------ -------------------- ---------------- --- ------------------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES



SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.


主库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6972         90             0            0

SQL> alter system switch logfile;

System altered.

SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0
  2  ;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1       6973          4             0            0


备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
RFS       IDLE         LGWR              1       6973          8             0            0
MRP0      APPLYING_LOG N/A               1       6973          8             5            5


SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

我们还可以利用Oracle snapshot方式进行备库的read write模式打开

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba  Travelskydba                   READ WRITE           PRIMARY          YES NO                    20755536


SQL>  select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3



--备库:

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES                   20755688

SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3


--主库误删除数据:


SQL> drop user albert cascade;

User dropped.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba                   READ WRITE           PRIMARY          YES NO                    20755761

   

--备库:
SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  READ ONLY WITH APPLY PHYSICAL STANDBY YES YES                   20755770
   

SQL> select * from albert.travelskydba;
select * from albert.travelskydba
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

准备恢复数据:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> show parameter broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
                                                 1.ora
dg_broker_config_file2               string      /oracle/oradata_Travelskydba/Travelskydba/dataguardconfig/dg_config0
                                                 2.ora
dg_broker_start                      boolean     TRUE
SQL> alter system set dg_broker_start=false;

System altered.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> flashback database to scn 20755688;

Flashback complete.

SQL> alter database open;

Database altered.

SQL>  select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> 
SQL> alter database convert to snapshot standby;

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              SNAPSHOT STANDBY YES YES                          0


SQL> alter database open; 

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba  Travelskydba_std                  READ WRITE           SNAPSHOT STANDBY YES YES                   20755867


SQL> select * from albert.travelskydba;

       ID1        ID2        ID3
---------- ---------- ----------
         1          2          3

[ora11g@albert ~]$  expdp schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log 

Export: Release 11.2.0.4.0 - Production on Thu Mar 26 01:12:23 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=ALBERT directory=tra_dir dumpfile=albert_dmp.20200326 logfile=expdp_20200326.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "ALBERT"."TRAVELSKYDBA"                     5.812 KB       1 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/ora11g/albert_dmp.20200326
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Mar 26 01:12:44 2020 elapsed 0 00:00:18

--数据导入过程省略--

--备库恢复至standby模式:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 4108759040 bytes
Fixed Size                  2259680 bytes
Variable Size            1577059616 bytes
Database Buffers         2516582400 bytes
Redo Buffers               12857344 bytes
Database mounted.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20755688  --之前闪回的SCN点,flashback database to scn 20755688;




SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757332


SQL> /

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757332


SQL> alter database open; 

Database altered.

SQL> select name,DB_UNIQUE_NAME,open_mode,database_role,force_logging,flashback_on,current_scn from v$database;

NAME      DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    FOR FLASHBACK_ON       CURRENT_SCN
--------- ------------------------------ -------------------- ---------------- --- ------------------ -----------
Travelskydba Travelskydba_std                  MOUNTED              PHYSICAL STANDBY YES YES                   20757498 

主库:
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1         19        206             0            0

SQL> 
SQL> alter system switch logfile;

System altered.

SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
LNS       WRITING      LNS               1         20          6             0            0

备库:
SQL> set linesize 200 
SQL> select process,status,client_process,thread#,sequence#,block#,active_agents,known_agents from  gv$managed_standby where process in('LNS','RFS','MRP0') and thread# <>0;

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1         19        142             5            5
RFS       IDLE         LGWR              1         19        142             0            0

SQL> /

PROCESS   STATUS       CLIENT_P    THREAD#  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ---------- ------------- ------------
MRP0      APPLYING_LOG N/A               1         20         55             5            5
RFS       IDLE         LGWR              1         20         55             0            0