AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

Option 1: Use DBMS_LOB package to get cell configuration information, as column CONFVAL is CLOB spool confval.txt and search for text make in text file you will see get value in tag <makeModel>  </makeModel> set pagesize 0 SELECT dbms_lob . substr (CONFVAL,4000,1) from V$CELL_CONFIG; spool off This option is good if you don't  have privilege to access storage node and can't execute command Cellcli. You would be able to find out interleaving attribute about Celldisk also as follows. <interleaving>none</interleaving> Explore about interleaving attribute of Celldisk at https://uhesse.com/2011/05/18/exadata-part-vii-meaning-of-the-various-disk-layers/amp/ http://basededonnyes.blogspot.com/2012/01/creating-interleaved-grid-disks.html?m=1 Option 2: From Tanel Podder Blog. I have modified some format to display Make Model properly. COL cv_cellname       HEAD CELL_NAME        FOR A30 COL cv_cell_path      HEAD CELL_PATH        FOR A30 COL cv_cellversion    HEA

adrci - Automatic Diagnostic Repository Command Interpreter $ adrci adrci> show homes Set home for Oracle Database: adrci> set home List incidents: adrci> show incidents Pack incident for incident number: adrci> ips pack incident <incident_no> in /tmp Show incident information in brief: adrci> show incident -mode brief -p "incident_id=<incident_no>" Pack incident for problem key (Get problem key by commnad show incidents): adrci> ips create package problemkey " ORA 600 [qmtGetBaseType]" Pack incident by time (Get time record by commnd show incidents): adrci> IPS CREATE PACKAGE TIME '2017-08-23 11:31:44.296000 -07:00' to '2017-08-22 08:32:16.394000 -07:00' Ref.: http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BGBICJED  

Tuning Set Name : Test_TOP_SQL_1503437315950 select name from dba_sqlset; select * from dba_sqlset where name like 'Test%'; Dropping SQL Task select name from dba_sqlset; execute dbms_sqltune.drop_sqlset('Test_TOP_SQL_1503437315950'); You could get an error, if it is reference to advisory task Get advisory task name select description, created, owner  from DBA_SQLSET_REFERENCES  where sqlset_name = 'Test_TOP_SQL_1503437315950'; task: SQL_TUNING_1503437363054 select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = 'SQL_TUNING_1503437363054'; execute dbms_sqltune.drop_tuning_task('SQL_TUNING_1503437363054'); execute DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'Test_TOP_SQL_1503437315950'); If you can't drop SQL Set then check whether record exist in work load repository or not SELECT count(*)  FROM wri$_sqlset_definitions a, wri$_sqlset_references b  WHERE a.name = 'Test_TOP_SQL_1503437315950'  A

Enable smart scan for ASM Disk group while creating disk group in Exadata SQL> CREATE DISKGROUP data HIGH REDUNDANCY DISK 'o/*/DATA*' ATTRIBUTE 'AU_SIZE' = '4M',           'content.type' = 'data',           'cell.smart_scan_capable'='TRUE',           'compatible.rdbms'='11.2.0.2',           'compatible.asm'='11.2.0.3'; Ref.: http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-administering-asm.htm#SAGUG20526 Check cell smart scan is enables for ASM disk or not:  Make sure to connect ASM instance, if you connect to DB instance you won't find value. SELECT dg.name AS diskgroup, SUBSTR(a.name,1,24) AS name, SUBSTR(a.value,1,24) AS value FROM V$ASM_DISKGROUP dg, V$ASM_ATTRIBUTE a WHERE dg.group_number = a.group_number and a.NAME = 'cell.smart_scan_capable'; EXADATA - Importance of Cellinit.ora and Cellip.ora files Cellinit.ora and Cellip.ora After Oracle Exadata Storage Server is configured

Oracle kfod command line tool for ASM disk inspection: kfod tool is located in grid home's bin directory. List optinos for kfod toll using $kfod -h _asm_a/llow_only_raw_disks              KFOD allow only raw devices [_asm_allow_only_raw_disks=(TRUE)/FALSE] _asm_l/ibraries         ASM Libraries[_asm_libraries=lib1,lib2,...] _asms/id                ASM Instance[_asmsid=sid] _b/oot          Running in pre-install env (boot=TRUE/FALSE) _f/lexinfo              Provide flexinfo      (_flexinfo=TRUE/FALSE) _p/atch_lib             Patchlib [_patch_lib=<asmclntsh_path>] _u/ser          OS Username asm_/diskstring         ASM Diskstring [asm_diskstring=discoverystring, discoverystring ...] asmc/ompatibility               Include diskgroup ASM compatibility [asmcompatibility=TRUE/(FALSE)] cli/ent_cluster         client cluster name clus_/version           cluster version clust/er                KFOD cluster [cluster=TRUE/(FALSE)] db_/unique_name         db_unique_name for ASM instance[d

Oracle parallel option: Parallel option parameter in int.ora: parallel_degree_level                        integer     100 parallel_degree_limit                        string      CPU parallel_degree_policy                      string      AUTO parallel_execution_message_size     integer     15343 parallel_max_servers                        integer     743 parallel_min_servers                         integer     76 parallel_min_time_threshold            string      AUTO parallel_server                                   boolean     TRUE parallel_server_instances                  integer     4 parallel_servers_target                      integer     374 parallel_threads_per_cpu                  integer     2 In an explain plan PX stands for Parallel processing. PX Send are producer PX Receiver receiver PX Coordinator PX COORDINATOR FORCED SERIAL operation means that, although the plan looks like a parallel execution, Oracle effectively will run this serially at execution time. IN-OUT col

Option 1: Display sql statement execution plan and sql execution statistics also set pagesize 0 set linesize 5000 spool plan_q1.log set auto trace on @sql_statement.sql spool off Explain plan could lie explore at Kerry Osborne's Blog - http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/ Option 2: Use DBMS_XPLAN, get explain plan from awr, using SQL_ID. set pagesize 0 set linesize 5000 spool plan_q1.log select * from table(dbms_xplan.display_awr('sql_id')); spool off Options for SQL statement: SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +predicate +cost')); SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical -cost -bytes')); SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +note')); Select plan_table_output From table(dbms_xplan.display_cursor(null,null,'TYPICAL'); SELECT plan_table_output FROM tabl

Find out OCR Location for RAC environment: Option 1: Get the ocr location form file ocr.loc location at /etc/oracle cat /etc/oracle/ ocr.loc Option 2: Execute ocrcheck utility form grid home. Typical grid home is  /u01/app/<version>/grid/bin ./ocr check Find out location of Voting disk: Option 1: Query V$ASM_DISK view. If it is Exadata you would be able to see path as o/cell_ip_address/cell_griddisk_name. i.e. o/192.168.1.1..... SELECT PATH FROM V$ASM_DISK WHERE VOTING_FILE='Y'; Option 2: Use utility crsctl. crsctl is location in bin directory of grid home. Typical grid home is /u01/app/<version>/grid/bin ./crsctl query css votedisk