Management Server(MS) running on database nodes.

On By IsikIn Oracle DatabaseLeave a comment

From exadata database machine administration guide:
“Starting with Oracle Exadata Database Machine 12.1.2.1.0
The database nodes now run the Management Server (MS). Previously MS ran only on the storage nodes.
The database nodes now run a new service called Database Machine Service (dbmsrv).
This new service is based on the MS that runs on the storage servers and provides enhanced management capabilities to the database nodes.”

[root@###exa03db01 ~]# dbmcli
DBMCLI: Release – Production on Mon Dec 14 13:25:01 CST 2015

Copyright (c) 2007, 2014, Oracle. All rights reserved.

DBMCLI> list dbserver detail
name: ###exa03db01
bbuStatus: normal
coreCount: 18/36
cpuCount: 36/36
diagHistoryDays: 7
fanCount: 16/16
fanStatus: normal
id: #########
interconnectCount: 2
interconnect1: ib0
interconnect2: ib1
ipaddress1: 192.168.10.1/22
ipaddress2: 192.168.10.2/22
kernelVersion: 2.6.39-400.250.4.el6uek.x86_64
locatorLEDStatus: off
makeModel: Oracle Corporation ORACLE SERVER X5-2
metricHistoryDays: 7
msVersion: OSS_12.1.2.1.2_LINUX.X64_150617.1
notificationMethod: snmp
notificationPolicy: critical,warning,clear
powerCount: 2/2
powerStatus: normal
releaseImageStatus: success
releaseVersion: 12.1.2.1.2.150617.1
releaseTrackingBug: 20748218
snmpSubscriber: host=10.240.6.21,port=162,community=public,type=ASR
status: online
temperatureReading: 22.0
temperatureStatus: normal
upTime: 32 days, 19:15
msStatus: running
rsStatus: running

DBMCLI>
DBMCLI> help

HELP [topic]
Available Topics:
ALTER
ALTER ALERTHISTORY
ALTER DBSERVER
ALTER IBPORT
ALTER THRESHOLD
CREATE
CREATE DBSERVER
CREATE THRESHOLD
DESCRIBE
DROP
DROP ALERTHISTORY
DROP DBSERVER
DROP THRESHOLD
LIST
LIST ALERTDEFINITION
LIST ALERTHISTORY
LIST DBSERVER
LIST IBPORT
LIST LUN
LIST METRICCURRENT
LIST METRICDEFINITION
LIST METRICHISTORY
LIST PHYSICALDISK
LIST THRESHOLD
SET
SPOOL
START

DBMCLI> list alerthistory
2_1 2015-09-09T07:04:25-05:00 critical “File system “/u01” is 89% full, which is above the 80% threshold. This alert will be cleared when file system “/u01″ becomes less than 75% full. Top three directories ordered by total space usage are as follows: /u01/app : 56.64G /u01/Software : 13.62G /u01/onecommand : 12.43G”

Like Exadata storage serves you can create file system threshold limit instead of crontab scripts.

DBMCLI> list metriccurrent
DS_BBU_TEMP ##exa03db01 26.0 C
DS_CPUT ##exa03db01 3.2 %
DS_CPUT_MS ##exa03db01 0.0 %
DS_FANS ##exa03db01 16
DS_FSUT / 42 %
DS_FSUT /boot 9 %
DS_FSUT /u01 34 %
DS_FSUT /u02 45 %
DS_FSUT /usr/openv 34 %
DS_MEMUT ##exa03db01 58 %
DS_MEMUT_MS ##exa03db01 0.1 %
DS_RUNQ ##exa03db01 1.4
DS_SWAP_IN_BY_SEC ##exa03db01 0.0 KB/sec
DS_SWAP_OUT_BY_SEC ##exa03db01 0.0 KB/sec
DS_SWAP_USAGE ##exa03db01 0 %
DS_TEMP ##exa03db01 22.0 C
DS_VIRTMEM_MS ##exa03db01 4,260 MB
N_HCA_MB_RCV_SEC ##exa03db01 0.351 MB/sec
N_HCA_MB_TRANS_SEC ##exa03db01 0.336 MB/sec
N_IB_MB_RCV_SEC HCA-1:1 0.158 MB/sec
N_IB_MB_RCV_SEC HCA-1:2 0.193 MB/sec
N_IB_MB_TRANS_SEC HCA-1:1 0.159 MB/sec
N_IB_MB_TRANS_SEC HCA-1:2 0.177 MB/sec
N_IB_UTIL_RCV HCA-1:1 0.0 %
N_IB_UTIL_RCV HCA-1:2 0.0 %
N_IB_UTIL_TRANS HCA-1:1 0.0 %
N_IB_UTIL_TRANS HCA-1:2 0.0 %
N_NIC_KB_RCV_SEC ##exa03db01 84.8 KB/sec
N_NIC_KB_TRANS_SEC ##exa03db01 3.5 KB/sec

DBMCLI> CREATE THRESHOLD DS_FSUT comparison=’>’,warning=75,critical=90
Threshold DS_FSUT successfully created

DBMCLI> alter THRESHOLD DS_FSUT comparison=”>”, warning=70
Threshold DS_FSUT successfully altered

DBMCLI> list threshold detail
name: DS_FSUT
comparison: >
critical: 90.0
warning: 70.0

DBMCLI> drop threshold DS_FSUT;
Threshold DS_FSUT successfully dropped

DBMCLI> list threshold detail

PRVG-10603 : Clusterware version consistency failed

On By IsikIn Oracle DatabaseLeave a comment

I got this error while upgrading my grid version from 11.2.0.3 to 11.2.0.4 on Linux box.

./cluvfy stage -pre crsinst -upgrade -n node01,node02-rolling  -src_crshome /optware/grid/11.2.0.3 -dest_crshome /optware/grid/11.2.0.4 -dest_version 11.2.0.4.0 -fixup -fixupdir /orabase/grid/fixup -verbose

Here is the error:

PRVF-4038 : CRS is not installed on nodes:
***-phys1
Verification will proceed with nodes:
***-phys2

PRVG-10603 : Clusterware version consistency failed **********************Check first inventory

Pre-check for cluster services setup was unsuccessful.
Checks did not pass for the following node(s):
***-phys1

I checked first my inventory is valid or not. You must have ONE inventory showing your ALL homes. This is output of node02

more /etc/oraInst.loc

#Oracle Installer Location File Location
#Tue Mar 26 06:05:04 EDT 2013
inst_group=dba
inventory_loc=/optware/grid/11.2.0.3/oraInventory

more inventory.xml
All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.4.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”oraGrid11gR2_20130322″ LOC=”/optware/grid/11.2.0.3″ TYPE=”O” IDX=”1″ CRS=”true”>  *********************TRUE for old home, at most one grid home is showing “CRS=true”
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”oraDB11gR2_20130320″ LOC=”/optware/oracle/11.2.0.3/db” TYPE=”O” IDX=”2″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”Ora11g_gridinfrahome1” LOC=”/optware/grid/11.2.0.4″ TYPE=”O” IDX=”3″/> ****New home must be on both nodes
<HOME NAME=”OraDb11g_home1” LOC=”/optware/oracle/11.2.0.4/db” TYPE=”O” IDX=”4″>  ****New home must be on both nodes
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY

But the first node has no inventory entities showing new installation for ASM and RDBMS home. So you’ve to attach these homes on node01 (missing inventory entities).Don’t try to update inventory.xml manually use runInstaller, run it on node01

./runInstaller -silent -attachHome -local ORACLE_HOME=”/optware/grid/11.2.0.4″ ORACLE_HOME_NAME=”Ora11g_gridinfrahome1” “CLUSTER_NODES={node02,node01}” LOCAL_NODE=’node01′

./runInstaller -silent -attachHome -local ORACLE_HOME=”/optware/oracle/11.2.0.4/db” ORACLE_HOME_NAME=”OraDb11g_home1” “CLUSTER_NODES={node02,node01}” LOCAL_NODE=’node01′

and check your inventory on inventory.xml whether it is there or not and make sure that you’ve ONE inventory pointing from /etc/oraInst.loc

after rootupgrade.sh inventory will be updated by OUI
<?xml version=”1.0″ standalone=”yes” ?>
<!– Copyright (c) 1999, 2013, Oracle and/or its affiliates.
All rights reserved. –>
<!– Do not modify the contents of this file by hand. –>
<INVENTORY>
<VERSION_INFO>
<SAVED_WITH>11.2.0.4.0</SAVED_WITH>
<MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME NAME=”oraGrid11gR2_20130322″ LOC=”/optware/grid/11.2.0.3″ TYPE=”O” IDX=”1″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”oraDB11gR2_20130320″ LOC=”/optware/oracle/11.2.0.3/db” TYPE=”O” IDX=”2″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”Ora11g_gridinfrahome1″ LOC=”/optware/grid/11.2.0.4″ TYPE=”O” IDX=”3″ CRS=”true”>  ************************ only once CRS=TRUE .(must be)
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
<HOME NAME=”OraDb11g_home1″ LOC=”/optware/oracle/11.2.0.4/db” TYPE=”O” IDX=”4″>
<NODE_LIST>
<NODE NAME=”isgswdbd66n1″/>
<NODE NAME=”isgswdbd66n2″/>
</NODE_LIST>
</HOME>
</HOME_LIST>
<COMPOSITEHOME_LIST>
</COMPOSITEHOME_LIST>
</INVENTORY

How to move execution plan to another database using SQLHC/SQLT

On By IsikIn Oracle DatabaseLeave a comment

One of my  query has different execution plan on both databases. One is running faster but the other is not.So I decided to move faster execution plan to slow database.

On faster database,

SQL> @sqlt/utl/coe_xfr_sql_profile.sql  SQL_ID PLAN_HASH_VALUE

This will create a script named coe_xfr_sql_profile_**SQL_ID***_***PLAN_HASH_VALUE***.sql in the local directory.

Edit this script and change the line:that starts
old force_match => FALSE
new force_match => TRUE

On the slower database,execute this script

SQL> @coe_xfr_sql_profile_***.sql

and then execute the query again and get the report and check the plan is correct.

SQL>START sqlhc.sql T sql_id

P.S

SQLHC is running online mode of SQLT

Why my tablespace report is slow on one of my databases ?

On By IsikIn Oracle DatabaseLeave a comment

Application team started complain about getting table space report is too slow.When I checked it, It took 20 min to get report. But I can get the same report in a sec. from other databases.

I executed SQL script and get the SQLHC report.

——————————————————————————————————————
| Id  | Operation                         | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
——————————————————————————————————————
|   0 | SELECT STATEMENT                  |                      |       |       |       |  1628 (100)|          |
|   1 |  SORT ORDER BY                    |                      |    17 |  1163 |       |  1627  (37)| 00:00:02 |
|   2 |   UNION-ALL                       |                      |       |       |       |            |          |
|   3 |    HASH JOIN OUTER                |                      |    16 |  1008 |       |  1621  (37)| 00:00:02 |
|   4 |     VIEW                          |                      |    16 |   528 |       |     9  (23)| 00:00:01 |
|   5 |      HASH GROUP BY                |                      |    16 |   416 |       |     9  (23)| 00:00:01 |
|   6 |       VIEW                        | DBA_DATA_FILES       |    42 |  1092 |       |     8  (13)| 00:00:01 |
|   7 |        UNION-ALL                  |                      |       |       |       |            |          |
|   8 |         HASH JOIN                 |                      |     1 |    89 |       |     3   (0)| 00:00:01 |
|   9 |          NESTED LOOPS             |                      |     1 |    41 |       |     3   (0)| 00:00:01 |
|  10 |           NESTED LOOPS            |                      |     1 |    24 |       |     2   (0)| 00:00:01 |
|  11 |            TABLE ACCESS FULL      | FILE$                |     1 |    21 |       |     2   (0)| 00:00:01 |
|  12 |            FIXED TABLE FIXED INDEX| X$KCCFE (ind:1)      |     1 |     3 |       |     0   (0)|          |
|  13 |           TABLE ACCESS CLUSTER    | TS$                  |     1 |    17 |       |     1   (0)| 00:00:01 |
|  14 |            INDEX UNIQUE SCAN      | I_TS#                |     1 |       |       |     0   (0)|          |
|  15 |          FIXED TABLE FULL         | X$KCCFN              |    41 |  1968 |       |     0   (0)|          |
|  16 |         NESTED LOOPS              |                      |    41 |  3813 |       |     5  (20)| 00:00:01 |
|  17 |          HASH JOIN                |                      |    41 |  3690 |       |     5  (20)| 00:00:01 |
|  18 |           NESTED LOOPS            |                      |    41 |  2993 |       |     2   (0)| 00:00:01 |
|  19 |            HASH JOIN              |                      |    41 |  2378 |       |     2   (0)| 00:00:01 |
|  20 |             FIXED TABLE FULL      | X$KCCFN              |    41 |  1968 |       |     0   (0)|          |
|  21 |             TABLE ACCESS FULL     | FILE$                |    42 |   420 |       |     2   (0)| 00:00:01 |
|  22 |            FIXED TABLE FIXED INDEX| X$KTFBHC (ind:1)     |     1 |    15 |       |     0   (0)|          |
|  23 |           TABLE ACCESS FULL       | TS$                  |    16 |   272 |       |     2   (0)| 00:00:01 |
|  24 |          FIXED TABLE FIXED INDEX  | X$KCCFE (ind:1)      |     1 |     3 |       |     0   (0)|          |
|  25 |     VIEW                          |                      |     7 |   210 |       |  1612  (37)| 00:00:02 |
|  26 |      HASH GROUP BY                |                      |     7 |   161 |       |  1612  (37)| 00:00:02 |
|  27 |       VIEW                        | DBA_FREE_SPACE       | 46412 |  1042K|       |  1597  (36)| 00:00:02 |
|  28 |        UNION-ALL                  |                      |       |       |       |            |          |
|  29 |         NESTED LOOPS              |                      |     1 |    66 |       |     2   (0)| 00:00:01 |
|  30 |          NESTED LOOPS             |                      |     1 |    60 |       |     2   (0)| 00:00:01 |
|  31 |           TABLE ACCESS FULL       | FET$                 |     1 |    39 |       |     2   (0)| 00:00:01 |
|  32 |           TABLE ACCESS CLUSTER    | TS$                  |     1 |    21 |       |     0   (0)|          |
|  33 |            INDEX UNIQUE SCAN      | I_TS#                |     1 |       |       |     0   (0)|          |
|  34 |          INDEX UNIQUE SCAN        | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  35 |         NESTED LOOPS              |                      |  2715 |   114K|       |     8  (75)| 00:00:01 |
|  36 |          HASH JOIN                |                      |  2715 |    98K|       |     7  (72)| 00:00:01 |
|  37 |           TABLE ACCESS FULL       | TS$                  |    10 |   270 |       |     2   (0)| 00:00:01 |
|  38 |           FIXED TABLE FULL        | X$KTFBFE             |  2959 | 29590 |       |     4 (100)| 00:00:01 |
|  39 |          INDEX UNIQUE SCAN        | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  40 |         HASH JOIN                 |                      | 43695 |  4693K|       |  1108  (38)| 00:00:02 |
|  41 |          TABLE ACCESS FULL        | TS$                  |    10 |   270 |       |     2   (0)| 00:00:01 |
|  42 |          HASH JOIN                |                      | 47619 |  3859K|       |  1104  (38)| 00:00:02 |
|  43 |           INDEX FULL SCAN         | I_FILE2              |    42 |   252 |       |     1   (0)| 00:00:01 |
|  44 |           HASH JOIN               |                      |   100K|  7519K|  7520K|  1098  (37)| 00:00:02 |
|  45 |            FIXED TABLE FULL       | X$KTFBUE             |   100K|  6347K|       |   205 (100)| 00:00:01 |
|  46 |            TABLE ACCESS FULL      | RECYCLEBIN$          |   795K|  9316K|       |   443  (28)| 00:00:01 |
|  47 |         HASH JOIN                 |                      |     1 |    91 |       |   480  (33)| 00:00:01 |
|  48 |          NESTED LOOPS             |                      |     1 |    79 |       |     4   (0)| 00:00:01 |
|  49 |           NESTED LOOPS            |                      |     1 |    73 |       |     4   (0)| 00:00:01 |
|  50 |            TABLE ACCESS FULL      | TS$                  |     1 |    21 |       |     2   (0)| 00:00:01 |
|  51 |            TABLE ACCESS CLUSTER   | UET$                 |     1 |    52 |       |     2   (0)| 00:00:01 |
|  52 |             INDEX RANGE SCAN      | I_FILE#_BLOCK#       |     1 |       |       |     2   (0)| 00:00:01 |
|  53 |           INDEX UNIQUE SCAN       | I_FILE2              |     1 |     6 |       |     0   (0)|          |
|  54 |          TABLE ACCESS FULL        | RECYCLEBIN$          |   795K|  9316K|       |   443  (28)| 00:00:01 |
|  55 |    HASH GROUP BY                  |                      |     1 |   155 |       |     6  (17)| 00:00:01 |
|  56 |     NESTED LOOPS OUTER            |                      |     1 |   155 |       |     5   (0)| 00:00:01 |
|  57 |      NESTED LOOPS                 |                      |     1 |   127 |       |     4   (0)| 00:00:01 |
|  58 |       HASH JOIN                   |                      |     2 |   220 |       |     2   (0)| 00:00:01 |
|  59 |        NESTED LOOPS               |                      |     5 |   355 |       |     0   (0)|          |
|  60 |         NESTED LOOPS              |                      |     5 |   285 |       |     0   (0)|          |
|  61 |          FIXED TABLE FULL         | X$KCCTF              |     6 |    72 |       |     0   (0)|          |
|  62 |          FIXED TABLE FIXED INDEX  | X$KCCFN (ind:1)      |     1 |    45 |       |     0   (0)|          |
|  63 |         FIXED TABLE FIXED INDEX   | X$KTFTHC (ind:1)     |     1 |    14 |       |     0   (0)|          |
|  64 |        VIEW                       | V_$TEMP_SPACE_HEADER |     3 |   117 |       |     2   (0)| 00:00:01 |
|  65 |         NESTED LOOPS              |                      |     3 |   141 |       |     2   (0)| 00:00:01 |
|  66 |          TABLE ACCESS FULL        | TS$                  |     1 |    27 |       |     2   (0)| 00:00:01 |
|  67 |          FIXED TABLE FIXED INDEX  | X$KTFTHC (ind:2)     |     3 |    60 |       |     0   (0)|          |
|  68 |       TABLE ACCESS BY INDEX ROWID | TS$                  |     1 |    17 |       |     1   (0)| 00:00:01 |
|  69 |        INDEX UNIQUE SCAN          | I_TS1                |     1 |       |       |     0   (0)|          |
|  70 |      VIEW PUSHED PREDICATE        | V_$TEMP_EXTENT_POOL  |     1 |    28 |       |     1   (0)| 00:00:01 |
|  71 |       NESTED LOOPS                |                      |     1 |    79 |       |     1   (0)| 00:00:01 |
|  72 |        TABLE ACCESS BY INDEX ROWID| TS$                  |     1 |    27 |       |     1   (0)| 00:00:01 |
|  73 |         INDEX UNIQUE SCAN         | I_TS1                |     1 |       |       |     0   (0)|          |
|  74 |        FIXED TABLE FIXED INDEX    | X$KTSTFC (ind:1)     |     1 |    52 |       |     0   (0)|          |
——————————————————————————————————————
I purged the recycle bin.

purge dba_recyclebin;

select count(*) from dba_recyclebin; –> should return 0. it may take time to purge.

and gather the fixed object statistic again and also  dictionary statistics.

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ;
exec DBMS_STATS.GATHER_DICTIONARY_STATS ; –>More important

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SYS’, cascade => TRUE, estimate_percent => 100);

It solved my problem.My query came in a second.

After that I executed SQLHC again and see no full table scan on RECYLEBIN

SELECT STATEMENT                      |                      |      1 |        |       |    70 (100)|          |     12 |00:00:00.19 |    4841 |      2 |       |       |          |
|   1 |  SORT ORDER BY                        |                      |      1 |     17 |  1163 |    69  (51)| 00:00:01 |     12 |00:00:00.19 |    4841 |      2 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL                           |                      |      1 |        |       |            |          |     12 |00:00:00.17 |    4841 |      2 |       |       |          |
|*  3 |    HASH JOIN OUTER                    |                      |      1 |     16 |  1008 |    63  (54)| 00:00:01 |      9 |00:00:00.17 |    4789 |      2 |  1368K|  1368K|  921K (0)|
|   4 |     VIEW                              |                      |      1 |     16 |   528 |     9  (23)| 00:00:01 |      9 |00:00:00.12 |      68 |      0 |       |       |          |
|   5 |      HASH GROUP BY                    |                      |      1 |     16 |   416 |     9  (23)| 00:00:01 |      9 |00:00:00.12 |      68 |      0 |  1048K|  1048K|  993K (0)|
|   6 |       VIEW                            | DBA_DATA_FILES       |      1 |     42 |  1092 |     8  (13)| 00:00:01 |     42 |00:00:00.12 |      68 |      0 |       |       |          |
|   7 |        UNION-ALL                      |                      |      1 |        |       |            |          |     42 |00:00:00.12 |      68 |      0 |       |       |          |
|*  8 |         HASH JOIN                     |                      |      1 |      1 |    89 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |   982K|   982K|  162K (0)|
|   9 |          NESTED LOOPS                 |                      |      1 |      1 |    41 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|  10 |           NESTED LOOPS                |                      |      1 |      1 |    24 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|* 11 |            TABLE ACCESS FULL          | FILE$                |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |      0 |       |       |          |
|* 12 |            FIXED TABLE FIXED INDEX    | X$KCCFE (ind:1)      |      0 |      1 |     3 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  13 |           TABLE ACCESS CLUSTER        | TS$                  |      0 |      1 |    17 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 14 |            INDEX UNIQUE SCAN          | I_TS#                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 15 |          FIXED TABLE FULL             | X$KCCFN              |      0 |     41 |  1968 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  16 |         NESTED LOOPS                  |                      |      1 |     41 |  3813 |     5  (20)| 00:00:01 |     42 |00:00:00.12 |      66 |      0 |       |       |          |
|* 17 |          HASH JOIN                    |                      |      1 |     41 |  3690 |     5  (20)| 00:00:01 |     42 |00:00:00.01 |      66 |      0 |  1421K|  1421K| 1132K (0)|
|  18 |           NESTED LOOPS                |                      |      1 |     41 |  2993 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |      44 |      0 |       |       |          |
|* 19 |            HASH JOIN                  |                      |      1 |     41 |  2378 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |       2 |      0 |  2440K|  2440K| 1279K (0)|
|* 20 |             FIXED TABLE FULL          | X$KCCFN              |      1 |     41 |  1968 |     0   (0)|          |     42 |00:00:00.01 |       0 |      0 |       |       |          |
|* 21 |             TABLE ACCESS FULL         | FILE$                |      1 |     42 |   420 |     2   (0)| 00:00:01 |     42 |00:00:00.01 |       2 |      0 |       |       |          |
|* 22 |            FIXED TABLE FIXED INDEX    | X$KTFBHC (ind:1)     |     42 |      1 |    15 |     0   (0)|          |     42 |00:00:00.01 |      42 |      0 |       |       |          |
|  23 |           TABLE ACCESS FULL           | TS$                  |      1 |     16 |   272 |     2   (0)| 00:00:01 |     16 |00:00:00.01 |      22 |      0 |       |       |          |
|* 24 |          FIXED TABLE FIXED INDEX      | X$KCCFE (ind:1)      |     42 |      1 |     3 |     0   (0)|          |     42 |00:00:00.11 |       0 |      0 |       |       |          |
|  25 |     VIEW                              |                      |      1 |     11 |   330 |    53  (59)| 00:00:01 |      9 |00:00:00.05 |    4721 |      2 |       |       |          |
|  26 |      HASH GROUP BY                    |                      |      1 |     11 |   253 |    53  (59)| 00:00:01 |      9 |00:00:00.05 |    4721 |      2 |  1088K|  1088K|  993K (0)|
|  27 |       VIEW                            | DBA_FREE_SPACE       |      1 |  25367 |   569K|    45  (52)| 00:00:01 |   3270 |00:00:00.03 |    4721 |      2 |       |       |          |
|  28 |        UNION-ALL                      |                      |      1 |        |       |            |          |   3270 |00:00:00.03 |    4721 |      2 |       |       |          |
|  29 |         NESTED LOOPS                  |                      |      1 |      1 |    66 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  30 |          NESTED LOOPS                 |                      |      1 |      1 |    60 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  31 |           TABLE ACCESS FULL           | FET$                 |      1 |      1 |    39 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|* 32 |           TABLE ACCESS CLUSTER        | TS$                  |      0 |      1 |    21 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 33 |            INDEX UNIQUE SCAN          | I_TS#                |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 34 |          INDEX UNIQUE SCAN            | I_FILE2              |      0 |      1 |     6 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  35 |         NESTED LOOPS                  |                      |      1 |   2971 |   124K|     8  (75)| 00:00:01 |   3236 |00:00:00.03 |     642 |      0 |       |       |          |
|* 36 |          HASH JOIN                    |                      |      1 |   2971 |   107K|     7  (72)| 00:00:01 |   3236 |00:00:00.03 |     638 |      0 |  1421K|  1421K| 1135K (0)|
|* 37 |           TABLE ACCESS FULL           | TS$                  |      1 |     10 |   270 |     2   (0)| 00:00:01 |      9 |00:00:00.01 |      22 |      0 |       |       |          |
|  38 |           FIXED TABLE FULL            | X$KTFBFE             |      1 |   3238 | 32380 |     5 (100)| 00:00:01 |   3236 |00:00:00.02 |     616 |      0 |       |       |          |
|* 39 |          INDEX UNIQUE SCAN            | I_FILE2              |   3236 |      1 |     6 |     0   (0)|          |   3236 |00:00:00.01 |       4 |      0 |       |       |          |
|* 40 |         HASH JOIN                     |                      |      1 |  22394 |  2405K|    26  (66)| 00:00:01 |     34 |00:00:00.01 |    4035 |      2 |  2061K|  2061K| 1490K (0)|
|  41 |          INDEX FULL SCAN              | I_FILE2              |      1 |     42 |   252 |     1   (0)| 00:00:01 |     42 |00:00:00.01 |       1 |      0 |       |       |          |
|  42 |          NESTED LOOPS                 |                      |      1 |  45879 |  4659K|    23  (66)| 00:00:01 |     34 |00:00:00.01 |    4034 |      2 |       |       |          |
|  43 |           MERGE JOIN                  |                      |      1 |      2 |    78 |     9  (12)| 00:00:01 |      2 |00:00:00.01 |    4022 |      0 |       |       |          |
  |  44 |            TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$          |      1 |      2 |    24 |     6   (0)| 00:00:01 |      2 |00:00:00.01 |    4000 |      0 |       |       |          |
                      |  45 |             INDEX FULL SCAN           | RECYCLEBIN$_TS       |      1 |      2 |       |     4   (0)| 00:00:01 |      2 |00:00:00.01 |    3998 |      0 |       |       |          |
|* 46 |            SORT JOIN                  |                      |      2 |     10 |   270 |     3  (34)| 00:00:01 |      2 |00:00:00.01 |      22 |      0 |  2048 |  2048 | 2048  (0)|
|* 47 |             TABLE ACCESS FULL         | TS$                  |      1 |     10 |   270 |     2   (0)| 00:00:01 |      9 |00:00:00.01 |      22 |      0 |       |       |          |
|* 48 |           FIXED TABLE FIXED INDEX     | X$KTFBUE (ind:1)     |      2 |  25000 |  1586K|     7 (100)| 00:00:01 |     34 |00:00:00.01 |      12 |      2 |       |       |          |
|  49 |         NESTED LOOPS                  |                      |      1 |      1 |    91 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  50 |          NESTED LOOPS                 |                      |      1 |      2 |    91 |     9   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  51 |           NESTED LOOPS                |                      |      1 |      1 |    79 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  52 |            NESTED LOOPS               |                      |      1 |      1 |    73 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|* 53 |             TABLE ACCESS FULL         | TS$                  |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |      22 |      0 |       |       |          |
|  54 |             TABLE ACCESS CLUSTER      | UET$                 |      0 |      1 |    52 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 55 |              INDEX RANGE SCAN         | I_FILE#_BLOCK#       |      0 |      1 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 56 |            INDEX UNIQUE SCAN          | I_FILE2              |      0 |      1 |     6 |     0   (0)|          |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 57 |           INDEX RANGE SCAN            | RECYCLEBIN$_TS       |      0 |      2 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
                      |* 58 |          TABLE ACCESS BY INDEX ROWID  | RECYCLEBIN$          |      0 |      1 |    12 |     5   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  59 |    HASH GROUP BY                      |                      |      1 |      1 |   155 |     6  (17)| 00:00:01 |      3 |00:00:00.02 |      52 |      0 |   809K|   809K|  748K (0)|
|  60 |     NESTED LOOPS OUTER                |                      |      1 |      1 |   155 |     5   (0)| 00:00:01 |      6 |00:00:00.02 |      52 |      0 |       |       |          |
|  61 |      NESTED LOOPS                     |                      |      1 |      1 |   127 |     4   (0)| 00:00:01 |      6 |00:00:00.02 |      43 |      0 |       |       |          |
|* 62 |       HASH JOIN                       |                      |      1 |      2 |   220 |     2   (0)| 00:00:01 |      6 |00:00:00.02 |      34 |      0 |  1483K|  1483K|  915K (0)|
|  63 |        NESTED LOOPS                   |                      |      1 |      5 |   355 |     0   (0)|          |      6 |00:00:00.02 |       6 |      0 |       |       |          |
|  64 |         NESTED LOOPS                  |                      |      1 |      5 |   285 |     0   (0)|          |      6 |00:00:00.02 |       0 |      0 |       |       |          |
|* 65 |          FIXED TABLE FULL             | X$KCCTF              |      1 |      6 |    72 |     0   (0)|          |      6 |00:00:00.01 |       0 |      0 |       |       |          |
|* 66 |          FIXED TABLE FIXED INDEX      | X$KCCFN (ind:1)      |      6 |      1 |    45 |     0   (0)|          |      6 |00:00:00.01 |       0 |      0 |       |       |          |
|* 67 |         FIXED TABLE FIXED INDEX       | X$KTFTHC (ind:1)     |      6 |      1 |    14 |     0   (0)|          |      6 |00:00:00.01 |       6 |      0 |       |       |          |
|  68 |        VIEW                           | V_$TEMP_SPACE_HEADER |      1 |      3 |   117 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      28 |      0 |       |       |          |
|  69 |         NESTED LOOPS                  |                      |      1 |      3 |   141 |     2   (0)| 00:00:01 |      6 |00:00:00.01 |      28 |      0 |       |       |          |
|* 70 |          TABLE ACCESS FULL            | TS$                  |      1 |      1 |    27 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |      22 |      0 |       |       |          |
|* 71 |          FIXED TABLE FIXED INDEX      | X$KTFTHC (ind:2)     |      2 |      3 |    60 |     0   (0)|          |      6 |00:00:00.01 |       6 |      0 |       |       |          |
|* 72 |       TABLE ACCESS BY INDEX ROWID     | TS$                  |      6 |      1 |    17 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |      0 |       |       |          |
|* 73 |        INDEX UNIQUE SCAN              | I_TS1                |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |      0 |       |       |          |
|* 74 |      VIEW PUSHED PREDICATE            | V_$TEMP_EXTENT_POOL  |      6 |      1 |    28 |     1   (0)| 00:00:01 |      5 |00:00:00.01 |       9 |      0 |       |       |          |
|  75 |       NESTED LOOPS                    |                      |      6 |      1 |    79 |     1   (0)| 00:00:01 |     25 |00:00:00.01 |       9 |      0 |       |       |          |
|* 76 |        TABLE ACCESS BY INDEX ROWID    | TS$                  |      6 |      1 |    27 |     1   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |      0 |       |       |          |
|* 77 |         INDEX UNIQUE SCAN             | I_TS1                |      6 |      1 |       |     0   (0)|          |      6 |00:00:00.01 |       3 |      0 |       |       |          |
|* 78 |        FIXED TABLE FIXED INDEX        | X$KTSTFC (ind:1)     |      6 |      1 |    52 |     0   (0)|          |     25 |00:00:00.01 |       0 |      0 |       |       |          |
——————————————————————————————————————————————————————————————–

Here is the query

select /*+ monitor gather_plan_statistics */
a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
round((a.bytes_alloc – nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
100 – round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_used,
a.autoext
FROM
(
select f.tablespace_name,
sum(f.bytes) bytes_alloc,
max(f.autoextensible) autoext
from dba_data_files f
group by tablespace_name
) a,
(
select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION ALL
SELECT
h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
round((sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
100 – round((sum((h.bytes_free + h.bytes_used) – nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
max(f.autoextensible) autoext
FROM
sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
WHERE
p.file_id(+) = h.file_id
AND
p.tablespace_name(+) = h.tablespace_name
AND
f.file_id = h.file_id
AND
f.tablespace_name = h.tablespace_name
GROUP BY
h.tablespace_name, f.maxbytes
ORDER BY 1

and check plan with command

select * from table(dbms_xplan.display_cursor);

To Index or Not to Index on Exadata System

On By IsikIn Oracle DatabaseLeave a comment

I started reading Expert Oracle Exadata 2nd Edition yesterday,I read 1 st edition before. I can say if you like working with Exadata ,you should read this book.

When the topic comes to Exadata, one of the popular discussion is about  using index on Exadata. Some says (Sales forces 🙂 ) you don’t need any indexes on Exadata but I don’t agree with them.

Most of the databases  are in the real world system  have mixed workload,long running query  which is throughput  sensitive in DSS and latency-sensitive statement in OLTP , so how to deal with indexes ? DSS doesn’t like index even it is analytic function index (Thanks smart scan) but on the other hand OLTP system need indexes.

So you can create 2 services ,one is for DSS system named, for example DSSSRV the other one is for OLTP is OLTPSRV

But you don’t want DSS system to use index , so put your indexes in INVISIBLE mode (come with 11g) which you don’t need to use them in DSS connection ,and make it VISIBLE when your connections come from OLTP service.

ALTER INDEX  oltp_index1 INVISIBLE;

select index_name, visibility  from user_indexes 3 where index_name = ‘oltp_index1’;

Write little procedure and put it into LOGON trigger later.

CREATE procedure coming_service is
begin
if LOWER(sys_context(‘userenv’,’service:name’)) = ‘DSSRV’ then
execute immediate ‘Alter session set optimizer_use_invisible_indexes = FALSE’;
elsif  LOWER(sys_context(‘userenv’,’service:name’)) = ‘OLTPSRV’ then
execute immediate ‘Alter session set optimizer_use_invisible_indexes =TRUE’;
end if;
end;
/

Alter session set optimizer_use_invisible_indexes = TRUE

This command is session level command , so it is valid only your session.

P.S

The procedure was taken from Chapter 17 –Unlearning  Some Things We Thought We Knew

ORA-4030 Error during SQL Loader –mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***

On By IsikIn ORA-4030Leave a comment

I got these error during sqlldr process.

In the trace file, I’m seeing lot of mmap error. This process using tons of map entries.

mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B
mmap(offset=207343616, len=8192) failed with errno=12 for the file oracle***1B

So the system was running out of map entries. Default number of map entries is 65536 (number of)
The default realfree allocator pagesize is 64KB.So you can use up to 4GB memory for process.

But increasing it to 262144 ,you can use up to 16GB memory.

more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=262144

Second option on the database side, you can set parameter _realfree_heap_pagesize_hint=262144 and _use_realfree_heap=TRUE

How to reset Exadata root password

On By IsikIn exadataLeave a comment

This is a little bit Linux server issue.If you’re familiar with resetting root password on Linux server,it will be easy for you.
Here the action plan:

Please note: From step 2 below all commands have to be executed on System Console (either directly sitting in front of the physical system with console screen attached or via Web Based ILOM remote console option.

1. Reboot via the ILOM either:
via the web interface:
go to “System Overview”, at “Host Power” choose ‘Power Cycle”
From ILOM web interface start remote console by going to Remote Control -> Launch Remote Console
via ssh:
reset /SYS
2. When the splash screen appears, immediately hit the ESC key. Please note, here is a short window for this , so carefully watch the terminal.
3. Select a line and enter “p” enter password > sos1Exadata —> Generally this password works
On the storage servers use the “default” entry, which is usually the first entry in the grub boot menu
4. At the grub line starting with “kernel” add at the end the following string: “single init=/bin/bash” —> Syntax is very important,please be carefull
5. Hit “Enter” key (will save changes )
6. Hit “b” key (to boot)
7. On the command line: mount -o remount,rw / (mount / in read/write mode) –> without executing this commmand,the OS is read-only mode
8. On the command line: passwd (change the password)
9. On the command line: the following command, which will avoid the requirement to reset password and expiration every 90 days:
# chage -d 14000 -E -1 -m 0 -M -1 root

10. To resume the boot process, run steps 11 and 12:
11. On the command line: mount -o remount,ro / (mount / in read-only mode, as expected by the fsck init script)
12. On the command line: exec /sbin/init (to spawn init in it’s default runlevel and resume the boot)
13. If steps 11 or 12 above fail, then run on the command line “sync” and reboot or power cycle the system.

ORA-01624: log 73 needed for crash recovery of instance

On By IsikIn DataguardLeave a comment

On the primary standby side,when I tried to drop log file I got this error.
Why I need to drop this redolog file,the reason is that resized online redol log file on primary side

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 73;

Database altered.

SQL> alter database drop logfile group 73;
alter database drop logfile group 73
*
ERROR at line 1:
ORA-01624: log 73 needed for crash recovery of instance pdw1k9 (thread 9)
ORA-00312: online log 73 thread 9:
‘+DATAX2KSC00/pdw1k/onlinelog/group_73.5219.849813663’

The action plan is offered from oracle support

On the primary:

1). Add online redo logs of the desirable sizes.

2). Then do a log switch and drop ORL when status is INACTIVE

3). Add SRL and drop the ones with INACTIVE status

4). On the standby follow this concise procedure to recreate standby controlfile:

>> Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)

GİDERAYAK Gide…

On By IsikIn Hayata DairLeave a comment

GİDERAYAK
Giderayak işlerim var bitirilecek,
giderayak.
Ceylanı kurtardım avcının elinden
ama daha baygın yatar ayılamadı.
Kopardım portakalı dalından
ama kabuğu soyulamadı.
Oldum yıldızlarla haşır neşir
ama sayısı bir tamam sayılamadı.
Kuyudan çektim suyu
ama bardaklara konulamadı.
Güller dizildi tepsiye
ama taştan fincan oyulamadı.
Sevdalara doyulamadı.
Giderayak işlerim var bitirilecek,
giderayak.