Create ASM DISKGROUP with EXADATA FLASHDISK

X7-2 half rack Exadata machine having 4 storage nodes and 24 TB allocated to flashcache on each cell

We are going allocate 12TB flashcache and 12TB to flashdisk

Check flashcache details:

CellCLI> list flashcache detail

         name:                   dbm0celadm01_FLASHCACHE

         cellDisk:               FD_00_dbm0celadm01,FD_02_dbm0celadm01,FD_01_dbm0celadm01,FD_03_dbm0celadm01

         creationTime:           2019-01-19T19:57:14-08:00

         degradedCelldisks:

         effectiveCacheSize:     23.28692626953125T

         id:                     1241246b-af5c-4636-b4a1-d23f21334c66

         size:                   23.28692626953125T

         status:                 normal

Will drop and recreate flashcache with 12TB:

CellCLI> drop flashcache

CELL-02769: Cannot complete flash cache drop on: FD_00_dbm0celadm01. Received error: CELL-02732: There may be data not synchronized to the grid disks (dirty data) in flash cache. Use ALTER FLASHCACHE to flush the dirty data and to stop caching, and then retry the command. Flash cache was not dropped on these cell disks: FD_00_dbm0celadm01 FD_02_dbm0celadm01 FD_01_dbm0celadm01 FD_03_dbm0celadm01 .

CellCLI> alter flashcache all flush

Monitor flush status from another session:

CellCLI>  list flashcache detail

         name:                   dbm0celadm01_FLASHCACHE

         cellDisk:               FD_00_dbm0celadm01,FD_02_dbm0celadm01,FD_01_dbm0celadm01,FD_03_dbm0celadm01

         creationTime:           2019-01-19T19:57:14-08:00

         degradedCelldisks:

         effectiveCacheSize:     23.28692626953125T

         id:                     1241246b-af5c-4636-b4a1-d23f21334c66

         size:                   23.28692626953125T

         status:                 normal - flushing

CellCLI> list flashcache detail

         name:                   dbm0celadm01_FLASHCACHE

         cellDisk:               FD_00_dbm0celadm01,FD_02_dbm0celadm01,FD_01_dbm0celadm01,FD_03_dbm0celadm01

         creationTime:           2019-01-19T19:57:14-08:00

         degradedCelldisks:

         effectiveCacheSize:     23.28692626953125T

         id:                     1241246b-af5c-4636-b4a1-d23f21334c66

         size:                   23.28692626953125T

         status:                 normal - FD_01_dbm0celadm01 (flushing), FD_03_dbm0celadm01 (flushing), FD_00_dbm0celadm01 (flushed), FD_02_dbm0celadm01 (flushed)

CellCLI> list flashcache detail

         name:                   dbm0celadm01_FLASHCACHE

         cellDisk:               FD_00_dbm0celadm01,FD_02_dbm0celadm01,FD_01_dbm0celadm01,FD_03_dbm0celadm01

         creationTime:           2019-01-19T19:57:14-08:00

         degradedCelldisks:

         effectiveCacheSize:     23.28692626953125T

         id:                     1241246b-af5c-4636-b4a1-d23f21334c66

         size:                   23.28692626953125T

         status:                 normal - flushed

Drop Existing flashcache:

CellCLI> drop flashcache

Flash cache dbm0celadm01_FLASHCACHE successfully dropped

CellCLI> list flashcache detail 

Create flashcache with 12TB:

CellCLI> create flashcache all size=12288G

Flash cache dbm0celadm03_FLASHCACHE successfully created

CellCLI> list flashcache detail

         name:                   dbm0celadm03_FLASHCACHE

         cellDisk:               FD_02_dbm0celadm03,FD_03_dbm0celadm03,FD_01_dbm0celadm03,FD_00_dbm0celadm03

         creationTime:           2019-03-21T22:56:17-07:00

         degradedCelldisks:

         effectiveCacheSize:     12T

         id:                     b14774c0-8d09-4cf3-ac15-51d88968e9cf

         size:                   12T

         status:                 normal

Create flashdisk:

CellCLI> CREATE GRIDDISK ALL FLASHDISK PREFIX='FDOM'

GridDisk FDOM_FD_00_dbm0celadm03 successfully created

GridDisk FDOM_FD_01_dbm0celadm03 successfully created

GridDisk FDOM_FD_02_dbm0celadm03 successfully created

GridDisk FDOM_FD_03_dbm0celadm03 successfully created

CellCLI> list griddisk attributes diskType, name where diskType=FLASHDISK

         FlashDisk       FDOM_FD_00_dbm0celadm03

         FlashDisk       FDOM_FD_01_dbm0celadm03

         FlashDisk       FDOM_FD_02_dbm0celadm03

         FlashDisk       FDOM_FD_03_dbm0celadm03

CellCLI> list griddisk  FDOM_FD_00_dbm0celadm01 detail

         name:                   FDOM_FD_00_dbm0celadm01

         asmDiskGroupName:

         asmDiskName:

         asmFailGroupName:

         availableTo:

         cachedBy:

         cachingPolicy:          default

         cellDisk:               FD_00_dbm0celadm01

         comment:

         creationTime:           2019-03-21T23:02:52-07:00

         diskType:               FlashDisk

         errorCount:             0

         id:                     a649aff8-20ec-4622-9315-8d877e6705ab

         size:                   2.8217315673828125T

         status:                 active

We need at least one failover group to create asm diskgroup in x7-2

Repeat same step on storage node 2 and create flashdisk

Set asm disk string parameter:

alter system set asm_diskstring='o/*/DATAC1_*','o/*/RECOC1_*','o/*/FDOM_*','/dev/exadata_quorum/*' scope=both sid='*';

Verify newly created flashdisk from cell nodes visible on asm or not:

SQL> select name, total_mb/1024, free_mb/1024, path, header_status from v$asm_disk where header_status='CANDIDATE';

NAME            TOTAL_MB/1024 FREE_MB/1024 PATH   HEADER_STATU

-------------- ------------- ------------ ----------------------------

                           0            0 o/000.00.00.15;000.00.00.16/FDOM_FD_00_dbm0celadm04                        CANDIDATE

                           0            0 o/000.00.00.9;000.00.00.10/FDOM_FD_03_dbm0celadm01                         CANDIDATE

                           0            0 o/000.00.00.15;000.00.00.16/FDOM_FD_01_dbm0celadm04                        CANDIDATE

                           0            0 o/000.00.00.9;000.00.00.10/FDOM_FD_00_dbm0celadm01                         CANDIDATE

                           0            0 o/000.00.00.13;000.00.00.14/FDOM_FD_03_dbm0celadm03                        CANDIDATE

                           0            0 o/000.00.00.15;000.00.00.16/FDOM_FD_02_dbm0celadm04                        CANDIDATE

                           0            0 o/000.00.00.9;000.00.00.10/FDOM_FD_02_dbm0celadm01                         CANDIDATE

                           0            0 o/000.00.00.15;000.00.00.16/FDOM_FD_03_dbm0celadm04                        CANDIDATE

                           0            0 o/000.00.00.11;000.00.00.12/FDOM_FD_01_dbm0celadm02                        CANDIDATE

                           0            0 o/000.00.00.11;000.00.00.12/FDOM_FD_03_dbm0celadm02                        CANDIDATE

                           0            0 o/000.00.00.13;000.00.00.14/FDOM_FD_01_dbm0celadm03                        CANDIDATE

                           0            0 o/000.00.00.9;000.00.00.10/FDOM_FD_01_dbm0celadm01                         CANDIDATE

                           0            0 o/000.00.00.13;000.00.00.14/FDOM_FD_02_dbm0celadm03                        CANDIDATE

                           0            0 o/000.00.00.11;000.00.00.12/FDOM_FD_02_dbm0celadm02                        CANDIDATE

                           0            0 o/000.00.00.13;000.00.00.14/FDOM_FD_00_dbm0celadm03                        CANDIDATE

                           0            0 o/000.00.00.11;000.00.00.12/FDOM_FD_00_dbm0celadm02                        CANDIDATE

16 rows selected.

Create asm diskgroup:

SQL> CREATE diskgroup FLASHDISK normal redundancy disk 'o/*/FDOM*'

 attribute 'compatible.rdbms'='11.2.0.4.0',

 'content.type' = 'data',

 'compatible.asm'='18.0.0.0.0',

 'cell.smart_scan_capable'='TRUE',

 'au_size'='4M';  2    3    4    5    6

Diskgroup created.

SQL> select name,state,total_mb/1024, free_mb/1024 from v$asm_diskgroup;

NAME           STATE       TOTAL_MB/1024 FREE_MB/1024

-------------- ----------- ------------- ------------

DATAC1         MOUNTED          61440.25    3746.0625

RECOC1         MOUNTED             16368   12160.2344

BACKUP         MOUNTED             30720   2719.21875

FLASHDISK      MOUNTED          46231.25   46230.3164

Analyze Archivelogs using LogMiner

Oracle LogMiner enables you to query online redo logs and archived log files through a SQL interface, It helps in troubleshooting user activity in data missing conditions,

one of our customer data got delete between 5PM to 6PM, here we are troubleshooting user activity during that period,

SQL> select name,open_mode from gv$database;

NAME      OPEN_MODE

--------- --------------------

PRODDB    READ WRITE

Check archive details between 5 to 6PM:

SQL>alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> select first_time, sequence#, thread# from v$log_history

where first_time between '30-OCT-2017 17:00:00' and '30-OCT-2017 18:00:00'

order by thread#,sequence# asc; 

FIRST_TIME            SEQUENCE#    THREAD#

-------------------- ---------- ----------

30-oct-2017 17:00:05      52160          1

30-oct-2017 17:04:24      52161          1

30-oct-2017 17:19:24      52162          1

30-oct-2017 17:24:19      52163          1

30-oct-2017 17:35:44      52164          1

30-oct-2017 17:35:51      52165          1

30-oct-2017 17:44:20      52166          1

30-oct-2017 17:59:35      52167          1

8 rows selected.

Check archive files availability in host:

[oracle@jdepdora01 2017_10_30]$ ls -ltr *5216*

-rw-r----- 1 oracle oinstall   2016768 Oct 30 17:04 o1_mf_1_52160_dzfj78wd_.arc

-rw-r----- 1 oracle oinstall   9224192 Oct 30 17:19 o1_mf_1_52161_dzfk3dx6_.arc

-rw-r----- 1 oracle oinstall   3987456 Oct 30 17:24 o1_mf_1_52162_dzfkdmz1_.arc

-rw-r----- 1 oracle oinstall 200954880 Oct 30 17:35 o1_mf_1_52163_dzfl20q7_.arc

-rw-r----- 1 oracle oinstall 201385984 Oct 30 17:35 o1_mf_1_52164_dzfl27m1_.arc

-rw-r----- 1 oracle oinstall 110521344 Oct 30 17:44 o1_mf_1_52165_dzfll46n_.arc

-rw-r----- 1 oracle oinstall   8595968 Oct 30 17:59 o1_mf_1_52166_dzfmgqkr_.arc

-rw-r----- 1 oracle oinstall   5592576 Oct 30 18:04 o1_mf_1_52167_dzfmr01f_.arc

-rw-r----- 1 oracle oinstall   4830720 Oct 30 18:19 o1_mf_1_52168_dzfnn8cc_.arc

-rw-r----- 1 oracle oinstall   4549120 Oct 30 18:24 o1_mf_1_52169_dzfnx5h2_.arc

Note: if archivelogs are not available physically on host restore from backup,

Make sure supplemental log is enabled in db level:

SQL> select supplemental_log_data_min from v$database ;

SUPPLEME

--------

YES

Add archivelogs to logminor:

SQL>execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52160_dzfj78wd_.arc',options => dbms_logmnr.new);SQL>

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52161_dzfk3dx6_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52162_dzfkdmz1_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52163_dzfl20q7_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52164_dzfl27m1_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52165_dzfll46n_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52166_dzfmgqkr_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/app/oracle/fast_recovery_area/PRODDB/archivelog/2017_10_30/o1_mf_1_52167_dzfmr01f_.arc',options => dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

Start logminor:

SQL> execute dbms_logmnr.start_logmnr;

PL/SQL procedure successfully completed.

Useful logminor views:

V$LOGMNR_DICTIONARY - The dictionary file in use.

V$LOGMNR_PARAMETERS - Current parameter settings for LogMiner.

V$LOGMNR_LOGS - Which redo log files are being analyzed.

V$LOGMNR_CONTENTS - contents of the log files being analyzed.

SQL> select unique operation from v$logmnr_contents;

OPERATION

--------------------------------

DDL

START

COMMIT

SEL_LOB_LOCATOR

SELECT_FOR_UPDATE

ROLLBACK

UPDATE

DELETE

LOB_WRITE

DPI SAVEPOINT

INTERNAL

INSERT

LOB_TRIM

UNSUPPORTED

15 rows selected.

Total operations in db between 5 to 6PM:

SQL> select count(*) from v$logmnr_contents;

  COUNT(*)

----------

    486382

SQL> select count(*) from v$logmnr_contents where operation='DELETE';

  COUNT(*)

----------

     63811 ---- Total deletes 

SQL> select count(*) from v$logmnr_contents where operation='DDL';

  COUNT(*)

----------

        28

Check Drop activity in db:

select session_info, sql_undo, sql_redo,username, timestamp from v$logmnr_contents where operation='DDL' and upper(sql_redo) like 'DROP%' order by timestamp desc;

Check TRUNCATE details:

select session_info,  sql_redo,username, timestamp from v$logmnr_contents where operation='DDL' and upper(sql_redo) like 'TRUNCATE%' order by timestamp desc;

SESSION_INFO           SQL_REDO    USERNAME        TIMESTAMP

--------------------- ----------- --------------- -------------

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9006D; PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9001;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9002;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9005;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9005D; PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9006;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9050;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9010;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9020;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9021;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9023;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9025;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9030;  PRODCTL         30-oct-2017 17:35:16

login_username=PRODCTL client_info= OS_username=tgdev08 Machine_name=IP-AC1F7865 OS_terminal=unknown OS_process_id=3852 OS_program_name=SQL Developer  TRUNCATE TABLE PRODCTL.F9000;  PRODCTL         30-oct-2017 17:35:15

14 rows selected.

from above command we got os user and machine name also db username who performed truncate operation during that time, 

we got the details, lets end logminor 

SQL> execute dbms_logmnr.end_logmnr;