Tuesday 26 March 2019

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

Friday 8 March 2019

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;