高逻辑读Top Sql 优化案例一则

发表于由

前不久，有个高逻辑读的sql需要进行优化处理，详情如下：

SQL_TEXT如下：
SELECT DISTINCT object_name
FROM t2
WHERE (substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
	AND object_name IS NOT NULL
	AND object_type IS NULL
	AND object_name NOT IN (
		SELECT object_name
		FROM t3
		WHERE object_name IS NOT NULL
	)
	AND regexp_like(object_name, '[[:upper:]]{4}'))

SQL的统计信息如下 ：
Historical Plans Summary(dba_hist_sqlstat):

  RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS      EXECS FIRST_SNAP          LAST_SNAP
---- --------------- ------------ ------------ ----------- ---------- ---------- ---------- ------------------- -------------------
   1       527727976      113.451       32.239      907578     899049         18          8 2020-05-20 10:00:15 2020-05-28 10:00:51


Current Plans Summary(gv_sql):

 RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME      LAST_ACTIVE
--- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- --------------------
  1       527727976      154.255       45.998     1131102    1130264         23          1 2020-05-28/09:00:02  2020-05-28 09:02:36


执行计划如下：
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null and
object_type is null    and object_name not in        (select
object_name from t3 where object_name is not null)    and
regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 4047468152

--------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |   239 (100)|          |     51 |00:00:00.03 |     863 |
|   1 |  HASH UNIQUE        |      |      1 |      1 |   239   (2)| 00:00:03 |     51 |00:00:00.03 |     863 |
|*  2 |   HASH JOIN ANTI    |      |      1 |      1 |   238   (1)| 00:00:03 |     77 |00:00:00.03 |     863 |
|*  3 |    TABLE ACCESS FULL| T2   |      1 |     70 |   153   (1)| 00:00:02 |     77 |00:00:00.01 |     557 |
|*  4 |    TABLE ACCESS FULL| T3   |      1 |  85011 |    85   (2)| 00:00:02 |  71958 |00:00:00.01 |     306 |
--------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T2@SEL$1
   4 - SEL$5DA710D3 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$5DA710D3" "T2"@"SEL$1")
      FULL(@"SEL$5DA710D3" "T3"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T3"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T3"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$5DA710D3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - filter(("OBJECT_TYPE" IS NULL AND  REGEXP_LIKE
              ("OBJECT_NAME",'[[:upper:]]{4}',HEXTORAW('105FF26E1D000000FCE30302000000000000000000000000703EDC060000
              0000000000000000000000000000000000003600000000000000605FF26E1D00000004000000010000000000000081000000')
               ) AND SUBSTR("CREATED",0,10)=TO_CHAR(SYSDATE@!-1,'yyyy-mm-dd') AND "OBJECT_NAME" IS NOT NULL))
   4 - filter("OBJECT_NAME" IS NOT NULL)

生产中，T2表63127459行记录（8g），T3表5703行记录（3m），join时都使用了全表扫描，其中cretad ndv很高，每天的数据量记录数并不多，可以创建如下索引：

create index t2_idx on t2(substr(created, 0, 10));
create index T3_IDX01 on t3(object_name);

执行计划如下，仍然不是最优的执行计划，更高效的应该是走NESTED LOOPS ANTI

-------------------------------------
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null and
object_type is null    and object_name not in        (select
object_name from t3 where object_name is not null)    and
regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 1600442906

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |    93 (100)|          |    247 |00:00:00.03 |     317 |
|   1 |  HASH UNIQUE                  |        |      1 |      5 |    93   (3)| 00:00:02 |    247 |00:00:00.03 |     317 |
|*  2 |   HASH JOIN ANTI              |        |      1 |      5 |    92   (2)| 00:00:02 |    404 |00:00:00.02 |     317 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2     |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    TABLE ACCESS FULL          | T3     |      1 |  85303 |    85   (2)| 00:00:02 |  72028 |00:00:00.01 |     306 |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$5DA710D3
   3 - SEL$5DA710D3 / T2@SEL$1
   4 - SEL$5DA710D3 / T2@SEL$1
   5 - SEL$5DA710D3 / T3@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX_RS_ASC(@"SEL$5DA710D3" "T2"@"SEL$1" "T2_IDX")
      FULL(@"SEL$5DA710D3" "T3"@"SEL$2")
      LEADING(@"SEL$5DA710D3" "T2"@"SEL$1" "T3"@"SEL$2")
      USE_HASH(@"SEL$5DA710D3" "T3"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$5DA710D3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME"="OBJECT_NAME")
   3 - filter(("OBJECT_TYPE" IS NULL AND  REGEXP_LIKE ("OBJECT_NAME",'[[:upper:]]{4}',HEXTORAW('5815F42D1D000000FC
              E30302000000000000000000000000703EDC0600000000000000000000000000000000000000003600000000000000A815F42D1D0000000400
              0000010000000000000081000000') ) AND "OBJECT_NAME" IS NOT NULL))
   4 - access("T2"."SYS_NC00005$"=TO_CHAR(SYSDATE@!-1,'yyyy-mm-dd'))
   5 - filter("OBJECT_NAME" IS NOT NULL)

我们可以添加HINT改变执行计划：

方式1：使用qb_name hint

select /*+ use_nl(t2,t3@bl) */
distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ qb_name(bl) */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')

SQL_ID  guj3z7b3w630x, child number 0
-------------------------------------
select /*+ use_nl(t2,t3@bl) */ distinct object_name   from t2  where
substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')    and
object_name is not null    and object_type is null    and object_name
not in        (select /*+ qb_name(bl) */          object_name from t3
       where object_name is not null)    and regexp_like(object_name,
'[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

方式2：使用hint：nl_aj + qb_name方式 

select /*+ nl_aj(@bl) */
distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ qb_name(bl) */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

方式3： nl_aj ，在子查询中加入hint，可以不用指定查询块 

select distinct object_name
  from t2
 where substr(created, 0, 10) = to_char(sysdate - 1, 'yyyy-mm-dd')
   and object_name is not null
   and object_type is null
   and object_name not in
       (select /*+ nl_aj */
         object_name
          from t3
         where object_name is not null)
   and regexp_like(object_name, '[[:upper:]]{4}')


SQL_ID  7hb1ksq4s1v6p, child number 0
-------------------------------------
select distinct object_name   from t2  where substr(created, 0, 10) =
to_char(sysdate - 1, 'yyyy-mm-dd')    and object_name is not null
and object_type is null    and object_name not in        (select /*+
nl_aj */          object_name           from t3          where
object_name is not null)    and regexp_like(object_name,
'[[:upper:]]{4}')

Plan hash value: 3009087575

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        |   956 (100)|          |    247 |00:00:00.01 |      24 |
|   1 |  HASH UNIQUE                  |          |      1 |      5 |   956   (1)| 00:00:12 |    247 |00:00:00.01 |      24 |
|   2 |   NESTED LOOPS ANTI           |          |      1 |      5 |   955   (0)| 00:00:12 |    404 |00:00:00.01 |      24 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T2       |      1 |    474 |     7   (0)| 00:00:01 |    404 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN          | T2_IDX   |      1 |    474 |     2   (0)| 00:00:01 |    410 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN           | T3_IDX01 |    266 |  85303 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      13 |
----------------------------------------------------------------------------------------------------------------------------

生产系统SQL低效索引案例

发表于由

前不久，无意中看到生产中一则TOP SQL，消耗资源较多，感觉有很大优化空间

SQL_ID  1agm6bmnhh609, child number 24
--------------------------------------
select *
  from UMETRIP_EVENTLOG
where FLIGHTDATE = :1 
   and EVENTTYPE = 'GATE_CHG' 
   and PASSREASON = '-91000

TIME         EXECU_D      BUFFER        DISK      ET_D     ROWS_PRO                                        
------------ ------- ----------- ----------- --------- ------------                                        
2020052004         2      229527       75055       205         4201                                        
2020052101         2      288633       67290       166         4061                                        
2020052202         2      226528       74570       181         4224                                        
2020052203         2      205266       50359       134         4421                                        
2020052303         2      206985       67949       156         4638                                        
2020052306         1      152840       76679       196         1723                                        
2020052601         2      213294       47103       125         3441                                        
2020052603         2      209591        9441        36         3765                                        
2020052810         2      248329       30198        91         4765                                        
2020052813         2      271982       46553       123         4849

这条SQL执行次数并不多，却在AWR中进入了TOP 10 SQL，执行1-2次消耗了200000+的逻辑读，表记录数25042676行，NDV如下：

COLUMN_NAME                       NDV NUL  NUM_NULLS     DATA_TYPE      LAST_ANALYZED        HISTOGRAM
------------------------------ ---------- --- ---------- ------------ -------------------- ---------
CITYCODE                             2379 Y         6073 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
DESTA                                2365 Y          849 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
EVENTTYPE                              59 Y            0 VARCHAR2     2020-05-27 22:09:30  FREQUENCY
EXTRAINFO                         2189824 Y     14600645 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDATE                           1891 Y          723 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDYNFIELD                         45 Y         3651 VARCHAR2     2020-05-27 22:09:30  NONE
FLIGHTNO                            58972 Y            0 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
ID                               25042676 Y            0 NUMBER       2020-05-27 22:09:30  HEIGHT BA
INFOAFTER                          713856 Y          336 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
INFOBEFOR                          243808 Y     21175542 VARCHAR2     2020-05-27 22:09:30  NONE
INSERTTIME                         626176 Y            0 DATE         2020-05-27 22:09:30  HEIGHT BA
ISUSED                                  2 Y          165 NUMBER       2020-05-27 22:09:30  FREQUENCY
PASSREASON                             91 Y      3295111 NUMBER       2020-05-27 22:09:30  FREQUENCY


Plan hash value: 3303868666
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       | 22722 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| UMETRIP_EVENTLOG      |    72 |  7776 | 22722   (1)| 00:04:33 |
|*  2 |   INDEX RANGE SCAN          | UMETRIP_EVENTLOG_IDX2 | 17964 |       |  5724   (1)| 00:01:09 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("PASSREASON"=(-91000))
   2 - access("EVENTTYPE"='GATE_CHG' AND "FLIGHTDATE"=:1)
       filter("FLIGHTDATE"=:1)

目前SQL执行计划走了UMETRIP_EVENTLOG_IDX2的索引，EVENTTYPE/ID/CITYCODE/FLIGHTDATE，起到过滤作用的只有EVENTTYPE，而FLIGHTDATE只能起到过滤作用。

继续阅读 →

生产数据库查询与插入hang案例

发表于由

前不久，生产Oracle数据库上出现一个比较紧急的案例，前台业务查询与插入操作全部夯死，涉及这些SQL的业务全部中断。接到电话后立即查看系统状态

此时系统中出现了latch: row cache objects ，library cache lock这些本不该在生产系统中出现的事件：
SQL> select event,count(*) from v$session where wait_class <> 'Idle' group by event order by 2 desc;

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
library cache lock                                                        2
SQL*Net message to client                                                 2
log file sync                                                             1
latch: row cache objects                                                  1



这些等待的Blocking_session均为空
SQL> select username,sid,serial#,sql_id,blocking_session,event from  v$session where wait_class <> 'Idle';

USERNAME                              SID    SERIAL# SQL_ID        BLOCKING_SESSION EVENT
------------------------------ ---------- ---------- ------------- ---------------- ----------------------------------------------------------------
Albert                               1015       3251 fdn44h4am80mb                  gc cr multi block request
Albert                               1184      54311 6sx33q2nq3c67                  library cache lock
Albert                               2738       4293 3ndnsw62wz2f1                  latch: row cache objects
Albert                               2957      17039                                reliable message
Albert                               3414      13501 7jj34snwajctj                  library cache lock
SYS                                  4192      29879 dh8qnqqr8jqyt                  SQL*Net message to client

此时需要找到Library cache lock等事件的阻塞源头，尽快恢复中断业务，可以利用如下代码进行确认追踪

SQL> select /*+ ordered */
  2  w1.sid waiting_session,
  3  h1.sid holding_session,
  4  w.kgllktype lock_or_pin,
  5  w.kgllkhdl address,
  6  decode(h.kgllkmod,
  7          0,
  8          'None',
  9          1,
 10          'Null',
 11          2,
 12          'Share',
 13          3,
 14          'Exclusive',
 15          'Unknown') mode_held,
 16  decode(w.kgllkreq,
 17          0,
 18          'None',
 19          1,
 20          'Null',
 21          2,
 22          'Share',
 23          3,
 24          'Exclusive',
 25          'Unknown') mode_requested
 26    from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 27  where (((h.kgllkmod != 0) and (h.kgllkmod != 1) and
 28         ((h.kgllkreq = 0) or (h.kgllkreq = 1))) and
 29         (((w.kgllkmod = 0) or (w.kgllkmod = 1)) and
 30         ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 31     and w.kgllktype = h.kgllktype
 32     and w.kgllkhdl = h.kgllkhdl
 33     and w.kgllkuse = w1.saddr
 34     and h.kgllkuse = h1.saddr;

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
           3414            2738 Lock 0000001FDF10B328 Share     Exclusive
           1184            2738 Lock 0000001FDF10B328 Share     Share

可以从上述结果中看到，2738会话持有Hanle address “0000001FDF10B328”的独占锁：Exclusive，所以应急杀掉2738会话，KILL SESSION会话后，故障恢复。

SQL> r
  1* select username,sid,serial#,sql_id,blocking_session,event from  v$session where wait_class <> 'Idle'

USERNAME                              SID    SERIAL# SQL_ID        BLOCKING_SESSION EVENT
------------------------------ ---------- ---------- ------------- ---------------- ----------------------------------------------------------------
Albert                                   1015       3251 35gvvuy7b2p2s                  db file scattered read
Albert                                   1184      54311 6sx33q2nq3c67                  SQL*Net more data from client
Albert                                   2957      17041 6sx33q2nq3c67                  SQL*Net more data from client
Albert                                 4192      29961 dh8qnqqr8jqyt                  SQL*Net message to client

事后根因分析，为何在业务高峰期间会有library cache lock长时间占有不释放的情况？

确认ASH时间点后，VTERM\GS02是受害者，基本大部分会话都是被2738这个会话阻塞
SQL> select --to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  2   event,
  3   sql_id,
  4   program,
  5   machine,
  6   sql_opname,
  7   BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  8   count(*)
  9    from v$active_session_history a
 10   where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 11         '20200501 00:00:00' and '20200501 10:44:00'
 12     and event = 'library cache lock'
 13   group by --to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 14            event,
 15            sql_id,
 16            program,
 17            machine,
 18            BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 19            sql_opname
 20   order by 7
 21  ;

EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
library cache lock                                               0uujs8nqcm145 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                         7  
library cache lock                                               49qykykfhwqtm plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        22
library cache lock                                               973s831gy6jk4 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        38
library cache lock                                                             sqlldr@hostname (TNS V1-V3)                      hostname                                                                                                                          _                                                                                        58
library cache lock                                               g639trfsrp9m5 plsqldev.exe                                     VTERM\Travelskydba01                                             PL/SQL EXECUTE                                                   _                                                                                        62
library cache lock                                               3y3gt31q23uk1 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                        65
library cache lock                                               6bwmj9n38kjp6 plsqldev.exe                                     VTERM\GS02                                                       SELECT                                                           _                                                                                       494
library cache lock                                               973s831gy6jk4 plsqldev.exe                                     VTERM\Travelskydba01                                             SELECT                                                           _                                                                                      1475
library cache lock                                               7jj34snwajctj detrreceipt@hostname2 (TNS V1-V3)                hostname2                                                        INSERT                                                           1_2738                                                                                 2419
library cache lock                                               6sx33q2nq3c67 sqlldr@hostname (TNS V1-V3)                      hostname                                                         INSERT                                                           _                                                                                      2419
library cache lock                                               7jj34snwajctj detrreceipt@hostname2 (TNS V1-V3)                hostname2                                                        INSERT                                                           _                                                                                      2515
library cache lock                                               6sx33q2nq3c67 sqlldr@hostname (TNS V1-V3)                      hostname                                                         INSERT                                                           1_2738                                                                                10760

12 rows selected

ASH中后续并没有记录2738这个会话再等latch: row cache objects，但是抓到了其他的进程信息，例如PROGRAM/MACHINE，猜测故障当时此会话已经不是ACTIVE

ASH中并没有记录2738这个会话再等latch: row cache objects
SQL> select --to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  2   event,
  3   sql_id,
  4   program,
  5   machine,
  6   sql_opname,
  7   BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  8   count(*)
  9    from ash_20200501 a
 10   where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 11         '20200430 00:00:00' and '20200501 10:44:00'
 12     and session_id=2738 and program ='plsqldev.exe'
 13   group by --to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 14            event,
 15            sql_id,
 16            program,
 17            machine,
 18            BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 19            sql_opname
 20   order by 1
 21  ;

EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                                                 3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                    SELECT                                                           _                                                                            58210
                                                                 3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                                                                                     _                                                                             6

SQL>

2738这个会话最早在4月30日18点20开始出现，一直持续到故障发生时（故障发生时间：20200501 10:00左右）

738这个会话最早在4月30日18点20开始出现，一直持续到故障发生时：
SQL> select rownum, a.*
  2    from (select to_char(sample_time, 'yyyymmdd hh24:mi:ss') sample_time,
  3                 event,
  4                 sql_id,
  5                 program,
  6                 machine,
  7                 sql_opname,
  8                 BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
  9                 count(*)
 10            from ash_20200501 a
 11           where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
 12                 '20200430 00:00:00' and '20200501 10:44:00'
 13             and session_id = 2738
 14             and program = 'plsqldev.exe'
 15           group by to_char(sample_time, 'yyyymmdd hh24:mi:ss'),
 16                    event,
 17                    sql_id,
 18                    program,
 19                    machine,
 20                    BLOCKING_INST_ID || '_' || BLOCKING_SESSION,
 21                    sql_opname
 22           order by 1) a where rownum <2
 23  ;

    ROWNUM SAMPLE_TIME       EVENT                                                            SQL_ID        PROGRAM                                          MACHINE                                                          SQL_OPNAME                                                       BLOCKING_INST_ID||'_'||BLOCKING_SESSION                                            COUNT(*)
---------- ----------------- ---------------------------------------------------------------- ------------- ------------------------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
         1 20200430 18:20:47                                                                  3ndnsw62wz2f1 plsqldev.exe                                     VTERM\Travelskydba01                                                                                                                       _                                                                                         1
继续阅读 →

RAC集群节点关闭长时间Waiting for ASM to shutdown处理过程与案例分析

发表于由

5月1日晚，接到值班同事求助电话，RAC集群重启节点后出现监听无法注册SERVICE的情况，应用连接在数据库实例重启后无法在维护节点上连接。

放下电话登录到系统中查看CRS状态出现如下错误

CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors。

CRS此时已经处于非正常状态，listener/service都是以资源的方式注册在CRSD中，所以出现SERVICE的异常现象是符合情理的。随后我准备重启CRS的时候，在stop的过程中却出现了长时间hang的情况，等待Waiting for ASM to shutdown。

[root@travelskydba-rac grid]# cd bin
[root@travelskydba-rac bin]# ./crsctl stat res tt
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@travelskydba-rac bin]# ./crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@travelskydba-rac bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.crsd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.crsd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.crf' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.ctssd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.evmd' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.asm' on 'travelskydba-rac'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.mdnsd' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.crf' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.evmd' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'travelskydba-rac' succeeded

CRS-2675: Stop of 'ora.asm' on 'travelskydba-rac' failed  ---卡在这里很长时间，大概有8分钟左右
CRS-2679: Attempting to clean 'ora.asm' on 'travelskydba-rac'
CRS-2681: Clean of 'ora.asm' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.cssd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.gipcd' on 'travelskydba-rac' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'travelskydba-rac'
CRS-2677: Stop of 'ora.drivers.acfs' on 'travelskydba-rac' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'travelskydba-rac' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'travelskydba-rac' has completed
CRS-4133: Oracle High Availability Services has been stopped.

2020-05-01 22:37:28.633:
[ctssd(2029)]CRS-2405:The Cluster Time Synchronization Service on host travelskydba-rac is shutdown by user
[client(89699)]CRS-10001:01-May-20 22:37 ACFS-9290: Waiting for ASM to shutdown
.... --省略重复输出
[client(124967)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125047)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125174)]CRS-10001:01-May-20 22:46 ACFS-9290: Waiting for ASM to shutdown.
[client(125491)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
[client(125548)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
[client(125614)]CRS-10001:01-May-20 22:47 ACFS-9290: Waiting for ASM to shutdown.
2020-05-01 22:47:28.727:
[/opt/app/11.2.0/grid/bin/oraagent.bin(759)]CRS-5818:Aborted command 'stop' for resource 'ora.asm'. Details at (:CRSAGF00113:) {0:0:52684} in /opt/app/11.2.0/grid/log/travelskydba-rac/agent/ohasd/oraagent_grid//oraagent_grid.log.
2020-05-01 22:47:30.730:
[ohasd(196541)]CRS-2757:Command 'Stop' timed out waiting for response from the resource 'ora.asm'. Details at (:CRSPE00111:) {0:0:52684} in /opt/app/11.2.0/grid/log/travelskydba-rac/ohasd/ohasd.log.
2020-05-01 22:47:33.404:
[cssd(872)]CRS-1603:CSSD on node travelskydba-rac shutdown by user.
2020-05-01 22:47:33.513:
[ohasd(196541)]CRS-2767:Resource state recovery not attempted for 'ora.cssdmonitor' as its target state is OFFLINE
2020-05-01 22:47:33.609:
[cssd(872)]CRS-1660:The CSS daemon shutdown has completed
2020-05-01 22:47:35.756:
[gpnpd(790)]CRS-2329:GPNPD on node travelskydba-rac shutdown.

DB alert日志：
NOTE: ASMB terminating
Errors in file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_asmb_188518.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 1828 Serial number: 22601
Errors in file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_asmb_188518.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 1828 Serial number: 22601
ASMB (ospid: 188518): terminating the instance due to error 15064
Fri May 01 22:47:30 2020
System state dump requested by (instance=1, osid=188518 (ASMB)), summary=[abnormal instance termination].
System State dumped to trace file /opt/app/ora11g/diag/rdbms/Albert/Albert1/trace/Albert1_diag_188401_20200501224730.trc
Dumping diagnostic data in directory=[cdmp_20200501224730], requested by (instance=1, osid=188518 (ASMB)), summary=[abnormal instance termination].
Instance terminated by ASMB, pid = 188518

节点2：集群日志：
2020-05-01 22:47:33.505:
[cssd(133163)]CRS-1625:Node travelskydba-rac, number 1, was shut down
2020-05-01 22:47:33.517:
[cssd(133163)]CRS-1601:CSSD Reconfiguration complete. Active nodes are travelskydba2-rac travelskydba3-rac .


节点3集群日志：
2020-05-01 22:47:33.505:
[cssd(141351)]CRS-1625:Node travelskydba-rac, number 1, was shut down
2020-05-01 22:47:33.517:
[cssd(141351)]CRS-1601:CSSD Reconfiguration complete. Active nodes are tr730e67-rac travelskydba3-rac .

也就是说节点1集群完全停下来，耗时22:37-22:47 10分钟，hang期间，让值班员联系硬件重启服务器,但是在硬件重启操作前，CRS却停下来了。

保险起见，还是让硬件重启一下服务器（因21点左右，硬件团队同事刚刚对此节点集群的私网心跳线进行更换）。

继续阅读 →

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

主库 drop tablespace后备库存储空间不释放案例一则

发表于由

上周，应用人员误操作在平台上tablespace name 打错名字，从而想删除表空间进行重新建立。重建后此数据库备库触发了文件系统满的报警，备库文件系统使用率已经为100%，但是数据库值班人员观察到备库之前建立错误的数据文件已经不在，为何文件系统存储中文件却没有删掉？之前怎么没有出现如此的情况？

我随后做了一个实验给他，原因是在drop tablespace时，没有加上including contents and datafiles

--主库：
SQL> select username,count(*) from gv$session group by username order by 2 desc;

USERNAME                         COUNT(*)
------------------------------ ----------
                                      174
ZABBIX                                  6
SYS                                     5
DBSNMP                                  4
PUBLIC                                  1

SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create tablespace albert datafile '+DG_DATA' size 1g;

Tablespace created.


SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT --刚刚创建的表空间

     FILE# NAME
---------- -----------------------------------------------------------------
         1 +DG_DATA/Travelskydba/datafile/system.259.1002299725
         2 +DG_DATA/Travelskydba/datafile/sysaux.260.1002299741
         3 +DG_DATA/Travelskydba/datafile/undotbs1.261.1002299753
         4 +DG_DATA/Travelskydba/datafile/undotbs2.263.1002299769
         5 +DG_DATA/Travelskydba/datafile/users.264.1002299781
         6 +DG_DATA/Travelskydba/datafile/tbs_test_1025_1.269.1022603323
         7 +DG_DATA/Travelskydba/datafile/tbs_test_1025_2.270.1022603429
         8 +DG_DATA/Travelskydba/datafile/albert.271.1035217783


--备库：
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT


SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert.271.1035217783

8 rows selected.

一、加入including contents and datafiles

--主库删除：
SQL> drop tablespace albert including contents and datafiles;

Tablespace dropped.



--备库确认，正常删除掉
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

7 rows selected.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected.


total 68780320
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:35 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:35 stdctl01.ctl

此时文件已经正常删除掉。


二、主库不加including contents and datafiles;

--主库创建文件：
SQL> create tablespace albert_leung datafile '+DG_DATA' size 2g;

Tablespace created.

--备库确认：

     FILE# NAME
---------- -----------------------------------------------------------------
         1 /oracle/oradata_Travelskydba/Travelskydba/system.259.1002299725
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429
         8 /oracle/oradata_Travelskydba/Travelskydba/albert_leung.271.1035218283

8 rows selected.

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2
ALBERT_LEUNG


drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:35 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:38 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:38 system.259.1002299725
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:38 stdctl01.ctl


--主库删除文件：
SQL> drop tablespace albert_leung;

Tablespace dropped.

--备库数据字典：

SQL> select name from v$tablespace; 

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
UNDOTBS2
USERS
TBS_TEST_1025_1
TBS_TEST_1025_2

8 rows selected

SQL> select FILE#,NAME from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         2 /oracle/oradata_Travelskydba/Travelskydba/sysaux.260.1002299741
         3 /oracle/oradata_Travelskydba/Travelskydba/undotbs1.261.1002299753
         4 /oracle/oradata_Travelskydba/Travelskydba/undotbs2.263.1002299769
         5 /oracle/oradata_Travelskydba/Travelskydba/users.264.1002299781
         6 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_1.269.1022603323
         7 /oracle/oradata_Travelskydba/Travelskydba/tbs_test_1025_2.270.1022603429

--可以看到，备库中的数据字典中已经删除掉，也就是说controlfile中该记录已经被删除

--但是观察到，备库文件系统，物理的数据文件还在，并没有删除。
[ora11g@vm-vmw45661-ora Travelskydba]$ ls -l
total 68780320
-rw-r----- 1 ora11g dba  2147491840 Mar 16 16:38 albert_leung.271.1035218283   --主库CHECKPOINT后不更新
drwxr-xr-x 2 ora11g dba        4096 Oct 14 11:51 dataguardconfig
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl01.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl01.ctl.bak
-rw-r----- 1 ora11g dba    24526848 Mar 16 16:43 stdctl02.ctl
-rw-r----- 1 ora11g dba    24526848 Oct 23 15:31 stdctl02.ctl.bak
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 sysaux.260.1002299741
-rw-r----- 1 ora11g dba  7340040192 Mar 16 16:41 system.259.1002299725
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_1.269.1022603323
-rw-r----- 1 ora11g dba 21474844672 Mar 16 15:56 tbs_test_1025_2.270.1022603429
-rw-r----- 1 ora11g dba 10485768192 Oct 23 15:34 temp.262.1002299765
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs1.261.1002299753
-rw-r----- 1 ora11g dba  5242888192 Mar 16 16:41 undotbs2.263.1002299769
-rw-r----- 1 ora11g dba  2097160192 Mar 16 15:56 users.264.1002299781
[ora11g@vm-vmw45661-ora Travelskydba]$ 

--此时，可以使用操作系统命令rm删除掉文件系统中命名错误的数据文件，因为controlfile中已经没有此记录，所以不会涉及数据库的实例恢复。

这个实验，没有加上including contents and datafiles 我只是回答了他第一个问题，但是第二个问题：“为什么之前我删除tablespace的时候，也没有加，怎么就没事呢？

根因在于Oracle Managed Files,这次出问题的此套备库没有使用OMF特性，也就是drop tablespace后，Oracle不会帮你自动的去清理物理文件，只会删除controlfile中的记录，但是之前同事所提到的没事的状况，是因为备库为RAC+ASM架构，默认采用了OMF管理数据文件，所以及时没有添加 including contents and datafiles 也会帮你自动清理文件。

官方原文如下：

1、 Unlike files that are not managed by the database, when an Oracle managed data file or temp file is dropped, the filename is removed from the control file and the file is automatically deleted from the file 

2、 When recovery of a standby database encounters redo for the deletion of a tablespace, it deletes any Oracle managed data files in the local file system. Note that this is independent of the INCLUDING DATAFILES option issued at the primary database.