Search This Blog

Wednesday, 8 September 2021

SMART SCAN PRE_REQUISITE

 

=> Query must go full table scan

=>_Serial_direct_read = Always

=>Cell_offload_process= True


Generate explain plan

=>table access full(Smart Scan)

=>v$session_stat (Total eligible bytes I/O / Eligible bytes I/O returned by SMARTSCAN)

Saturday, 4 September 2021

 

Bash While loop running on linux:

while true; do {command}; sleep 180; done &

 

Check services on windows: 

wmic service get name,startname

Wednesday, 25 August 2021

CELLCLI COMMANDS

 

 => To check asmmode status (should be online before activity) 

[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATA1_CD_00_abc3PCELabc03     ONLINE  Yes
         DATA1_CD_01_abc3PCELabc03     ONLINE  Yes
         DATA1_CD_02_abc3PCELabc03     ONLINE  Yes
        
=> To inactivate all griddisk under cellnode 3:


[root@abc3pcelabc03 ~]# cellcli -e alter griddisk all inactive
GridDisk DATA1_CD_00_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_01_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_02_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_03_abc3PCELabc03 successfully altered
GridDisk DATA1_CD_04_abc3PCELabc03 successfully altered

=> Check griddisk status (it should be offline)
=> To check asmmode status (should be offline after inactive gridisk activity)


[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATA1_CD_00_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_01_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_02_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_03_abc3PCELabc03     OFFLINE         Yes
         DATA1_CD_04_abc3PCELabc03     OFFLINE         Yes
        

[root@abc3pcelabc03 ~]# cellcli -e list griddisk
         DATA1_CD_00_abc3PCELabc03     inactive
         DATA1_CD_01_abc3PCELabc03     inactive
         DATA1_CD_02_abc3PCELabc03     inactive
         DATA1_CD_03_abc3PCELabc03     inactive
         DATA1_CD_04_abc3PCELabc03     inactive
        
=> connect to asm and check cellcheck:

PATH                                                         FAILGROUP                      MODE_ST HEADER_STATU STATE
------------------------------------------------------------ ------------------------------ ------- ------------ --------
o/191.100.09.99;191.100.09.110/DATA1_CD_00_abc3pcelabc01    abc3PCELabc01                 ONLINE  MEMBER       NORMAL
o/191.100.09.99;191.100.09.110/DATA1_CD_00_abc3pcelabc01    abc3PCELabc01                 ONLINE  MEMBER       NORMAL
o/191.100.09.99;191.100.09.110/DATA1_CD_01_abc3pcelabc01    abc3PCELabc01                 ONLINE  MEMBER       NORMAL

 

[root@abc3pdbabc01 ~]# ssh abc3pcelabc03
Last login: Thu Jun 11 21:45:40 2020 from 10.53.5.130
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATAC1_CD_00_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_01_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_02_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_03_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_04_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_05_abc3PCELabc03     OFFLINE         Yes
         DATAC1_CD_06_abc3PCELabc03     OFFLINE         Yes
         
[root@abc3pcelabc03 ~]# cellcli -e list griddisk
         DATAC1_CD_00_abc3PCELabc03     inactive
         DATAC1_CD_01_abc3PCELabc03     inactive
         DATAC1_CD_02_abc3PCELabc03     inactive
         DATAC1_CD_03_abc3PCELabc03     inactive


[root@abc3pcelabc03 ~]# cellcli -e alter griddisk all active
GridDisk DATAC1_CD_00_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_01_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_02_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_03_abc3PCELabc03 successfully altered
GridDisk DATAC1_CD_04_abc3PCELabc03 successfully altered

[root@abc3pcelabc03 ~]# cellcli -e list griddisk
         DATAC1_CD_00_abc3PCELabc03     active
         DATAC1_CD_01_abc3PCELabc03     active
         DATAC1_CD_02_abc3PCELabc03     active
         DATAC1_CD_03_abc3PCELabc03     active
         DATAC1_CD_04_abc3PCELabc03     active
       
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     SYNCING
         DATAC1_CD_01_abc3PCELabc03     SYNCING
         DATAC1_CD_02_abc3PCELabc03     SYNCING
         DATAC1_CD_03_abc3PCELabc03     SYNCING


[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     SYNCING
         DATAC1_CD_01_abc3PCELabc03     SYNCING
         DATAC1_CD_02_abc3PCELabc03     SYNCING
         DATAC1_CD_03_abc3PCELabc03     SYNCING
         
[root@abc3pcelabc03 ~]# date
Thu Jun 11 22:17:23 +08 2020
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     SYNCING
         DATAC1_CD_01_abc3PCELabc03     SYNCING
         DATAC1_CD_02_abc3PCELabc03     SYNCING
         DATAC1_CD_03_abc3PCELabc03     SYNCING
         DBFS_DG_CD_02_abc3pcelabc03    ONLINE
         DBFS_DG_CD_03_abc3pcelabc03    ONLINE
         DBFS_DG_CD_04_abc3pcelabc03    ONLINE
         

[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name,asmmodestatus
         DATAC1_CD_00_abc3PCELabc03     ONLINE
         DATAC1_CD_01_abc3PCELabc03     ONLINE
         DATAC1_CD_02_abc3PCELabc03     ONLINE
         DATAC1_CD_03_abc3PCELabc03     ONLINE

 [root@abc3pdbabc01 ~]# ssh abc3pcelabc03
Last login: Fri May 29 00:46:39 2020 from abc3pdbabc01.intra2.wda.gov.sg

[root@abc3pcelabc03 ~]#
[root@abc3pcelabc03 ~]# cellcli -e create griddisk DATA1_CD_01_abc3pcelabc03  celldisk=CD_01_abc3pcelabc03, offset=32M, size=6.1015625T
GridDisk DATA1_CD_01_abc3pcelabc03 successfully created
[root@abc3pcelabc03 ~]# cellcli -e create griddisk DATA1_CD_02_abc3pcelabc03  celldisk=CD_02_abc3pcelabc03, offset=32M, size=6.1015625T
GridDisk DATA1_CD_02_abc3pcelabc03 successfully created
[root@abc3pcelabc03 ~]# cellcli -e create griddisk DATA1_CD_03_abc3pcelabc03  celldisk=CD_03_abc3pcelabc03, offset=32M, size=6.1015625T
GridDisk DATA1_CD_03_abc3pcelabc03 successfully created

[root@abc3pcelabc03 ~]#
[root@abc3pcelabc03 ~]#
[root@abc3pcelabc03 ~]# cellcli -e list griddisk attributes name, asmdiskgroupName, status, asmmodestatus, asmdeactivationoutcome
         DATA1_CD_00_abc3pcelabc03                     active  UNUSED  Yes
         DATA1_CD_01_abc3pcelabc03                     active  UNUSED  Yes
         DATA1_CD_02_abc3pcelabc03                     active  UNUSED  Yes
         DATA1_CD_03_abc3pcelabc03                     active  UNUSED  Yes
         DS_DG_CD_02_abc3pcelabc03    DS_DG         active  ONLINE  Yes
         DS_DG_CD_03_abc3pcelabc03    DS_DG         active  ONLINE  Yes
         REC1REC1_CD_00_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_01_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_02_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_03_abc3pcelabc03     REC1          active  ONLINE  Yes
         REC1_CD_04_abc3pcelabc03     REC1          active  ONLINE  Yes

[root@abc3pcelabc03 ~]# cellcli -e LIST GRIDDISK ATTRIBUTES name,asmDiskGroupName,cachingpolicy,size,offset
         DATA1_CD_00_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_01_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_02_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_03_abc3pcelabc03                     default         6.1015625T              32M
         DATA1_CD_04_abc3pcelabc03                     default         6.1015625T              32M

         DS_DG_CD_02_abc3pcelabc03    DS_DG         default         33.796875G              7.1192474365234375T
         DS_DG_CD_03_abc3pcelabc03    DS_DG         default         33.796875G              7.1192474365234375T
         REC1_CD_00_abc3pcelabc03     REC1          none            1.01763916015625T       6.1016082763671875T
         REC1_CD_01_abc3pcelabc03     REC1          none            1.01763916015625T       6.1016082763671875T
----------------------------------------------------------------------------------------------------------------------------
[root@abc3pcelabc03 ~]# cellcli -e LIST celldisk attributes name, freespace
         CD_00_abc3pcelabc03    0
         CD_01_abc3pcelabc03    0
         CD_02_abc3pcelabc03    0
         CD_03_abc3pcelabc03    0
         FD_00_abc3pcelabc03    0
         FD_01_abc3pcelabc03    0
         FD_02_abc3pcelabc03    0
         FD_03_abc3pcelabc03    0
[root@abc3pcelabc03 ~]#

----------------------------------------------------------------------------------------------------------------------------
To activate griddisks:


cellcli -e list griddisk
cellcli -e alter griddisk all active

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

TO verify alert history:

cellcli -e list alerthistory 


[root@abc3pcelabc03 ~]# cellcli -e list alerthistory
         1_1     2020-01-11T11:12:42+08:00       critical        "Configuration check discovered the following problems:   Verify network configuration: /opt/oracle.cellos/ipconf -check-consistency -semantic -ignore-get-ilom-errors [Info]: ipconf command line: /opt/oracle.cellos/ipconf.pl -check-consistency -semantic -ignore-get-ilom-errors -nocodes [Info]: Verify that the configured values in the Exadata configuration file /opt/oracle.cellos/cell.conf agree with the actual values in use on this system ILOM IP address 10.53.0.96 must match 10.230.14.190 in /opt/oracle.cellos/cell.conf              : FAILED ILOM netmask 255.255.248.0 must match 255.255.255.0 in /opt/oracle.cellos/cell.conf              : FAILED ILOM gateway 10.53.0.1 matches 10.230.14.7 from /opt/oracle.cellos/cell.conf                     : FAILED ILOM DNS server 10.84.94.130 must match 10.230.20.21 in /opt/oracle.cellos/cell.conf             : FAILED ILOM first NTP server 10.84.94.130 must match 10.230.20.21 in /opt/oracle.cellos/cell.conf       : FAILED [Info]: Consistency check FAILED"
         2_1     2020-01-19T14:54:57+08:00       critical        "Internal USB status is degraded.  Affected USB Slots : 0"
         3_1     2020-01-21T03:10:02+08:00       critical        "ILOM has stopped responding, and did not reset after issuing reset commands."
         3_2     2020-05-28T20:35:27+08:00       clear           "ILOM is back to a normal state."
         7       2020-05-15T05:18:03+08:00       critical        "Read-only file system detection thread was hung. Cell was power cycled to restore access to the cell."


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

KFOD Commands:

==================

[+ASM1@abc3pdbabc01]/u01/app/12.1.0.2/grid/bin $ kfod disks=all dscvgroup=true | grep -i abc3pcelabc01 |grep -i DATA1
   3:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_00_abc3pcelabc01 DATA1
   4:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_01_abc3pcelabc01 DATA1
   5:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_02_abc3pcelabc01 DATA1
   6:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_03_abc3pcelabc01 DATA1
   7:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_04_abc3pcelabc01 DATA1

[+ASM1@abc3pdbabc01]/u01/app/12.1.0.2/grid/bin $


[+ASM2@wdax3pdbadm02]/u01/app/12.1.0.2/grid/bin $ kfod disks=all dscvgroup=true | grep -i abc3pcelabc01 |grep -i DATA1
   1:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_00_abc3pcelabc01 DATA1
   2:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_01_abc3pcelabc01 DATA1
   3:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_02_abc3pcelabc01 DATA1
   4:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_03_abc3pcelabc01 DATA1
   5:    6397952 Mb o/191.100.09.109;191.100.09.110/DATA1_CD_04_abc3pcelabc01 DATA1

[+ASM2@wdax3pdbadm02]/u01/app/12.1.0.2/grid/bin $

EXADATA Commands & (ADDING DISK & DELETING DISK)

 

 IBSWITCH INFO:

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

ibhosts

ibswitches

LIST DISKS VIA KFOD:

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

kfod disks=all dscvgroup=true | grep -i abc3pceladm03 |grep -i datac1
kfod disks=all dscvgroup=true | grep -i abc3pceladm03

 List all Disks via CELLCLI:

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

cellcli -e list griddisk

cellcli -e list griddisk attributes name,asmmodestatus

list griddisk attributes name where asmdeactivationoutcome!='Yes'

cellcli -e list griddisk attributes name,asmmodestatus,asmdeactivationoutcome

cellcli -e list griddisk attributes name,asmmodestatus


[root@abc3pceladm03 log]# cellcli -e LIST GRIDDISK ATTRIBUTES name,asmDiskGroupName,cachingpolicy,size,offset
         DATA1_CD_00_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_01_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_02_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_03_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_04_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DATA1_CD_05_abc3PCELADM03     DATA1          default         6.1015625T              32M
         DBFS_DG_CD_02_abc3pceladm03    DBFS_DG         default         33.796875G              7.1192474365234375T
         DBFS_DG_CD_03_abc3pceladm03    DBFS_DG         default         33.796875G              7.1192474365234375T

         RREC1_CD_00_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_01_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_02_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_03_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T
         RREC1_CD_04_abc3pceladm03     RREC1          none            1.01763916015625T       6.1016082763671875T

LIST DISKS FROM CELLCLI:

list grid disk from cellnode 3:
=>  ssh abc3pceladm03

 CellCLI> list griddisk
         DATAC1_CD_00_ABC3PCELADM03     active
         DATAC1_CD_01_ABC3PCELADM03     active
         DATAC1_CD_02_ABC3PCELADM03     active
         DATAC1_CD_03_ABC3PCELADM03     active

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

Veritas:

======

Backup list command:

bplist -C abcx212cpdb02-bak -t 4 -l -R / 

ASM DISKGROUP SIZE:

=================

set lines 300 pages 999
col COMPATIBILITY for a12
col DATABASE_COMPATIBILITY for a12
col NAME for a12
col state for a9
col type for a9
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024 "TOTAL GB",FREE_MB/1024 "FREE GB",((TOTAL_MB-FREE_MB)/1024) "USED GB",((total_mb-free_mb)/total_mb)*100 "%Used",OFFLINE_DISKS,COMPATIBILITY,DATABASE_COMPATIBILITY from v$aSQL> sm_diskgroup order by GROUP_NUMBER;


Database creation:

==============================================
DBCA:

nohup $ORACLE_HOME/bin/dbca -silent -createDatabase -templateName General_Purpose.dbc
-gdbName RCT  -sid RCT  -SysPassword We1come-SystemPassword We1come$
-emConfiguration NONE -redoLogFileSize 200  
-storageType ASM -asmSysPassword We1come$
-diskGroupName +DATAU1
-RECOVERYGROUPNAME +RECOU1
-characterSet AL32UTF8
-nationalCharacterSet AL32UTF8
-automaticMemoryManagement true
-totalMemory 2048  
-databaseType MULTIPURPOSE
-nodelist abcx212cudbadm01,abcx212cudbadm02 > RCT_creation.log &
============================

CHANGE RE BALANCE FROM 1 to 16:

GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE                                  CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
           3 REBAL RESYNC    DONE          1          1          0          0          0           0                                          0
           3 REBAL RESILVER  DONE          1          1          0          0          0           0                                          0
           3 REBAL REBALANCE RUN           1          1    2682423    2854811       1225         140                                          0
           3 REBAL COMPACT   WAIT          1          1          0          0          0           0                                          0

SYS@+ASM2> alter diskgroup REDOC1 rebalance power 16 nowait;

Diskgroup altered.

SYS@+ASM2> select * from v$asm_operation;

GROUP_NUMBER OPERA PASS      STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE                                  CON_ID
------------ ----- --------- ---- ---------- ---------- ---------- ---------- ---------- ----------- -------------------------------------------- ----------
           3 REBAL RESYNC    DONE         16         16          0          0          0           0                                          0
           3 REBAL RESILVER  DONE         16         16          0          0          0           0                                          0
           3 REBAL REBALANCE EST          16         16          0          0          0           0                                          0
           3 REBAL COMPACT   WAIT         16         16          0          0          0           0  


Disk repaire time:

===============
 col VALUE for a30;
 col NAME for a20;
 select dg.name,a.value from v$asm_diskgroup dg, v$asm_attribute a where  dg.group_number=a.group_number  and a.name='disk_repair_time';

 
 NAME                 VALUE
-------------------- ------------------------------
DATA1               800h
DB_DG              800h
REDOC1               800h



Thursday, 18 February 2021

SQL agent startup failed with "system cant find the path"

 

Changing the Dump Directory Location may SQL Agent to not start:

 error" sql agent"system cant find the path"

To fix this problem, is quite easy. Open the Registry Editor by typing regedit on cmd and go to the following key and validate if the directory has changed, or it is still pointing to the old one.

 

i have compared with sql configuration and regedit configuration.Then i found errorlog location is diffrent in regedit.