闪回系列专题(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

ORA-01196/ORA-01110特殊恢复案例一则

前不久,我们一套生产数据库的dataguard的备库出现实时应用过慢,数据库服务器IOWAIT高的情况,为了避免主库归档日志出现大量挤压,所以值班同事临时应急将主备库关系配置断掉,后续再进行DataGuard重构。除此之外,我们准备将备库打开进行一系列测试,试图探寻数据库服务器IOWAIT高的原因并解决DataGuard延迟的问题。

第二天,同事尝试打开备库,但是报了ORA-01196与错误

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

此错误因为备库还未将redo log与archive恢复至一致状态,数据库此时处于不一致状态是无法打开的。但是,备库所有的归档日志已经被删除,此时数据库无法进行一个完全恢复

SQL> set line 1000 pagesize 200
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;

NAME      OPEN_MODE            CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba  MOUNTED                          7669047             966955     7669046 PHYSICAL STANDBY

SQL>  ALTER DATABASE ACTIVATE STANDBY DATABASE;
 ALTER DATABASE ACTIVATE STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

此时,我准备重建控制文件,进行不完全恢复操作

QL> alter database backup controlfile to trace as '/home/ora11g/albert.ctl';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> 

albert.ctl文件内容:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA1459T" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo11.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo12.log'
  ) SIZE 300M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo21.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo22.log'
  ) SIZE 300M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo31.log',
    '/oracle/redo_Travelskydba1/Travelskydba_DG/redo32.log'
  ) SIZE 300M BLOCKSIZE 512
-- STANDBY LOGFILE
--   GROUP 4 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo04.log'  SIZE 300M BLOCKSIZE 512,
--   GROUP 5 '/oracle/redo_Travelskydba1/Travelskydba_DG/standby_redo05.log'  SIZE 300M BLOCKSIZE 512
DATAFILE
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/sysaux01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/undotbs01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users01.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users02.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users03.dbf',
  '/oracle/oradata_Travelskydba1/Travelskydba_DG/users04.dbf'
CHARACTER SET AL32UTF8
;

[ora11g@ALBERT ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 28 12:36:00 2020

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

Connected to an idle instance.

SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.  --控制文件重建完毕

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started

SQL> alter database open resetlogs;  --仍然无法打开数据库
alter database open resetlogs
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'

修改数据库alter system set “_allow_resetlogs_corruption” =true scope=spfile;参数,再次进行不完全恢复(此操作会有严重丢失数据风险,生产系统请慎用)

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes
SQL> alter system set "_allow_resetlogs_corruption" =true scope=spfile;

System altered.
SQL> shutdown abort;
ORACLE instance shut down.

SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7669047 generated at 11/04/2019 23:00:11 needed for thread 1
ORA-00289: suggestion :
/oracle/oradata_Travelskydba1/Travelskydba_DG/1_247_1017488189.dbf
ORA-00280: change 7669047 for thread 1 is in sequence #247


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [7669055], [0],
[7996264], [4194545], [], [], [], [], [], []
Process ID: 12086
Session ID: 1705 Serial number: 3

此时出现ORA-00600 2262错误,此时距离成功已经不远,只需要推进SCN即可,
SQL> shutdown abort;
ORACLE instance shut down.
SQL> @albert.ctl
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size                  2257720 bytes
Variable Size            1191185608 bytes
Database Buffers         2080374784 bytes
Redo Buffers               16527360 bytes

Control file created.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 7779049 generated at 03/28/2020 12:54:12 needed for thread 1
ORA-00289: suggestion : /oracle/oradata_Travelskydba1/Travelskydba_DG/1_2_1036241636.dbf
ORA-00280: change 7779049 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/oradata_Travelskydba1/Travelskydba_DG/system01.dbf'


ORA-01112: media recovery not started


SQL> oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
SQL>  oradebug poke 0x06001AE70 4 8279047 
BEFORE: [06001AE70, 06001AE74) = 00000000
AFTER:  [06001AE70, 06001AE74) = 007E5407

SQL> alter database open resetlogs;  --数据库成功打开

Database altered.


SQL> set line 1000 pagesize 200    
SQL> select name,open_mode,CONTROLFILE_CHANGE#,CHECKPOINT_CHANGE#,CURRENT_SCN,DATABASE_ROLE from V$database;

NAME      OPEN_MODE            CONTROLFILE_CHANGE# CHECKPOINT_CHANGE# CURRENT_SCN DATABASE_ROLE
--------- -------------------- ------------------- ------------------ ----------- ----------------
Travelskydba  READ WRITE                       8280898            8279049     8280903 PRIMARY

DataGuard常见问题( 二)注册日志恢备库GAP

DataGuard常见问题(一)增量备份恢复主备库GAP实验后,在我们维护Dataguard工作还经常需要用到另一种恢复备库日志GAP的方式-alter database register logfile。通常,这种方法相比增量备份恢复方式更为简单,成本也相对较少,但是前提是主库的归档没有被删除仍然可见的情况下,所以自然就不需要主库增量备份来帮忙,直接将主库的GAP归档日志传输至备库进行恢复即可。

实验内容:

(1)破坏DG关系,模拟GAP产生:

2GAP-1

2gap-2

主库目前Sequence为8543,备库等待8542 Sequnece出现Wait_For_Log状态:

将备库缺失归档传输至主库并进行 alter database register logfile,使得备库识别到缺失归档,否则该文件传输至相关目标路径后,仍然只是个文件而已,对于备库来说只不过是个路人甲。

2gap-3

向备库注册日志后,备库实时应用恢复正常,Wait_For_Log状态消失。

2gap-4

 

 

DataGuard常见问题(一)增量备份恢复主备库GAP

近期,有幸听到Oracle ACED 杨廷琨老师给我们进行DataGuard非常相信的培训课程,培训过程中杨老师提到了几个dataguard常见问题,其中一个为DataGuard备库出现了GAP,备库向主库FAL SERVER进行获取归档日志,但是主库归档日志已经被备份走或人为删除,导致备库日志中断,无法进行正常的实时应用。

这类问题公司在众多Oracle生产库中也颇为常见,培训过程中杨老师利用增量备份进行备库增量恢复,避免主备数据库重构,下面我再次做了这个实验,分享下此类非常实用的方法。

主库状态:

gap-2

备库状态:

gap-1

继续阅读

ORA-600/0RA-7445 导致Instance Crash无法启动恢复案例

前几日,一套开发人员邮件求助,数据库在执行批量删除delete时数据库异常崩溃,无法启动,启动后PMON进程会自动停止实例,数据库后台日志如下,数据库异常时出现ORA-00600与ORA-7445报错:

Wed Aug 21 15:53:29 2019
 Dumping diagnostic data in directory=[cdmp_20190821155329], requested by (instance=1, osid=29995), summary=[incident=437112].
 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97ECFFC, kghalo()+570] [flags: 0x0, count: 1]
 Errors in file /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/trace/Travelskydba_ora_29995.trc (incident=437114):
 ORA-07445: 出现异常错误: 核心转储 [kghalo()+570] [SIGSEGV] [ADDR:0x0] [PC:0x97ECFFC] [SI_KERNEL(general_protection)] []
 ORA-00600: 内部错误代码, 参数: [17182], [0x7F617B5DAE58], [], [], [], [], [], [], [], [], [], []
 ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_437114/Travelskydba_ora_29995_i437114.trc
 1562074,1 99%
 ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_437114/Travelskydba_ora_29995_i437114.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Wed Aug 21 15:53:31 2019
 Sweep [inc][437114]: completed
 Sweep [inc][437113]: completed
 Sweep [inc][437112]: completed
 Sweep [inc2][437113]: completed
 Sweep [inc2][437112]: completed
 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97ECFFC, kghalo()+570] [flags: 0x0, count: 2]
 Errors in file /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/trace/Travelskydba_ora_29995.trc (incident=437115):
 ORA-07445: 出现异常错误: 核心转储 [kghalo()+570] [SIGSEGV] [ADDR:0x0] [PC:0x97ECFFC] [SI_KERNEL(general_protection)] []
 ORA-07445: 出现异常错误: 核心转储 [kghalo()+570] [SIGSEGV] [ADDR:0x0] [PC:0x97ECFFC] [SI_KERNEL(general_protection)] []
 ORA-00600: 内部错误代码, 参数: [17182], [0x7F617B5DAE58], [], [], [], [], [], [], [], [], [], []
 ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_437115/Travelskydba_ora_29995_i437115.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x87F3BD8, dbgexDumpErrDesc()+82] [flags: 0x0, count: 3]
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Wed Aug 21 15:53:57 2019
 Block recovery from logseq 8936, block 446894 to scn 843928251
 Recovery of Online Redo Log: Thread 1 Group 3 Seq 8936 Reading mem 0
 Mem# 0: /oracle/redo_Travelskydba/Travelskydba/redo31.log
 Mem# 1: /oracle/redo_Travelskydba/Travelskydba/redo32.log
 Block recovery completed at rba 8936.446897.16, scn 0.843928254
 Errors in file /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/trace/Travelskydba_pmon_51643.trc (incident=432032):
 ORA-00600: internal error code, arguments: [17182], [0x7FDB167BDBF8], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_432032/Travelskydba_pmon_51643_i432032.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97E6BEC, kghpmfal()+216] [flags: 0x0, count: 1]
 Errors in file /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/trace/Travelskydba_pmon_51643.trc (incident=432033):
 ORA-07445: exception encountered: core dump [kghpmfal()+216] [SIGSEGV] [ADDR:0x0] [PC:0x97E6BEC] [SI_KERNEL(general_protection)] []
 ORA-00600: internal error code, arguments: [17182], [0x7FDB167BDBF8], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_432033/Travelskydba_pmon_51643_i432033.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Wed Aug 21 15:53:59 2019
 Dumping diagnostic data in directory=[cdmp_20190821155359], requested by (instance=1, osid=51643 (PMON)), summary=[incident=432032].
 1562114,1 99%
 ORA-07445: 出现异常错误: 核心转储 [kghalo()+570] [SIGSEGV] [ADDR:0x0] [PC:0x97ECFFC] [SI_KERNEL(general_protection)] []
 ORA-00600: 内部错误代码, 参数: [17182], [0x7F617B5DAE58], [], [], [], [], [], [], [], [], [], []
 ORA-00600: 内部错误代码, 参数: [kdsgrp1], [], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_437115/Travelskydba_ora_29995_i437115.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x87F3BD8, dbgexDumpErrDesc()+82] [flags: 0x0, count: 3]
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Wed Aug 21 15:53:57 2019
 Block recovery from logseq 8936, block 446894 to scn 843928251
 Recovery of Online Redo Log: Thread 1 Group 3 Seq 8936 Reading mem 0
 Mem# 0: /oracle/redo_Travelskydba/Travelskydba/redo31.log
 Mem# 1: /oracle/redo_Travelskydba/Travelskydba/redo32.log
 Block recovery completed at rba 8936.446897.16, scn 0.843928254
 Errors in file /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/trace/Travelskydba_pmon_51643.trc (incident=432032):
 ORA-00600: internal error code, arguments: [17182], [0x7FDB167BDBF8], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_432032/Travelskydba_pmon_51643_i432032.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97E6BEC, kghpmfal()+216] [flags: 0x0, count: 1]
 Errors in file /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/trace/Travelskydba_pmon_51643.trc (incident=432033):
 ORA-07445: exception encountered: core dump [kghpmfal()+216] [SIGSEGV] [ADDR:0x0] [PC:0x97E6BEC] [SI_KERNEL(general_protection)] []
 ORA-00600: internal error code, arguments: [17182], [0x7FDB167BDBF8], [], [], [], [], [], [], [], [], [], []
 Incident details in: /opt/app/ora11g/orabase/diag/rdbms/Travelskydba/Travelskydba/incident/incdir_432033/Travelskydba_pmon_51643_i432033.trc
 Use ADRCI or Support Workbench to package the incident.
 See Note 411.1 at My Oracle Support for error and packaging details.
 Wed Aug 21 15:53:59 2019
 Dumping diagnostic data in directory=[cdmp_20190821155359], requested by (instance=1, osid=51643 (PMON)), summary=[incident=432032].
 System state dump requested by (instance=1, osid=51706 (CJQ0)), summary=[abnormal instance termination].
 Wed Aug 21 15:54:03 2019
 CJQ0 (ospid: 51706): terminating the instance due to error 472

从日志中看到,数据库从21日15:53开始报错:ORA-00600与ORA-07445,54分时,数据库实例被CJQ0进程所终止。

继续阅读

Online Redo Log丢失常见场景恢复方法

Online Redo Log丢失损坏场景恢复实验

场景介绍:

场景一.inactive状态的redo恢复,

场景二.active状态的redo恢复 ,

场景三.current状态的redo恢复。

场景一:inactive状态的redo损坏及恢复

会话1:我们可以看到,当前数据库为非归档模式(No Archive Mode),并且当前存在3个日志组,GROUP 3为Current状态,2和3均为INACTIVE状态,我们即将要破坏GROUP# 2或GROUP#3的日志文件,模拟故障现象

redo-1

会话2:dd group#2 日志文件,该文件状态为INACTIVE:

redo-2

会话1:重启数据库报错,通过alert日志中可以看到,无法打开GROUP 2 redolog文件,数据库一致性无法保证,数据库启动失败

redo-3

继续阅读