Author: kishan

You will know about me.

Add filesystem mountpoint in linux

After storage team provision the physical disk layer,check the logical units of the disks using fdisk

[root@orcl oracle]# fdisk -l|grep -A5 sde
Disk /dev/sde: 3221 MB, 3221225472 bytes
255 heads, 63 sectors/track, 391 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xe418c16e
--
/dev/sde1               1         391     3140676   83  Linux
[root@orcl oracle]#

Create a mount point with type(-t) ext4 on /dev/sde

[root@orcl oracle]# mkfs -t ext4 /dev/sde
mke2fs 1.43-WIP (20-Jun-2013)
/dev/sde is entire device, not just one partition!
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
786432 inodes, 3145728 blocks
157286 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=3221225472
96 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

Create a directory to mount the logical mount point

[root@orcl oracle]# mkdir /u01
[root@orcl oracle]# mount /dev/sde /u01

Check if the mount point is added to the OS.Note that so far we have temporarily mounted the filesystem.Post reboot of the system,changes will not reflect for the mount point

[root@orcl oracle]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdd1        20G  6.9G   12G  38% /
tmpfs           1.9G  635M  1.2G  35% /dev/shm
/dev/sdd5        15G  9.3G  4.5G  68% /grid
/dev/sdd2        15G  5.4G  8.4G  40% /oracle
/dev/sde         12G   30M   12G   1% /u01

Give appropriate permissions to the directory of mount point

[root@orcl oracle]# chown oracle:oinstall /u01/
[root@orcl oracle]# chmod -R 755 /u01

Check the UUID of the particular disk

[root@orcl by-uuid]# ls -lrt
total 0
lrwxrwxrwx 1 root root 10 Feb 27 10:43 5307b124-8485-4f2c-ac2f-88af16028c11 -> ../../sdd3
lrwxrwxrwx 1 root root 10 Feb 27 10:43 de13c2f8-d3f3-41a7-9928-46001af5faf9 -> ../../sdd1
lrwxrwxrwx 1 root root 10 Feb 27 10:43 1121a652-c653-4bc5-b475-6285703020ba -> ../../sdd5
lrwxrwxrwx 1 root root 10 Feb 27 10:43 e3a73203-7975-4750-8ab4-e24542c24f31 -> ../../sdd2
lrwxrwxrwx 1 root root  9 Feb 27 11:38 1c1e8596-f9c2-4250-9fcc-1658ab4fec69 -> ../../sde
[root@orcl by-uuid]# pwd
/dev/disk/by-uuid

As we have already mounted the filesystem for current session earlier which vanish after reboot.So we have to update the fstab entry for permanent change

[root@orcl by-uuid]# cat /etc/fstab |grep u01
UUID=1c1e8596-f9c2-4250-9fcc-1658ab4fec69 /u01                    ext4    defaults      1 2

After reboot,you can see the changes using blkid command.

[root@orcl oracle]# blkid
/dev/sdd1: LABEL="FRA" TYPE="oracleasm"
/dev/sda1: UUID="de13c2f8-d3f3-41a7-9928-46001af5faf9" TYPE="ext4"
/dev/sdc1: LABEL="DATA" TYPE="oracleasm"
/dev/sda2: UUID="e3a73203-7975-4750-8ab4-e24542c24f31" TYPE="ext4"
/dev/sda3: UUID="5307b124-8485-4f2c-ac2f-88af16028c11" TYPE="swap"
/dev/sda5: UUID="1121a652-c653-4bc5-b475-6285703020ba" TYPE="ext4"
/dev/sdb: UUID="1c1e8596-f9c2-4250-9fcc-1658ab4fec69" TYPE="ext4"
/dev/sde1: LABEL="CRS" TYPE="oracleasm"

RECOVER TABLE USING RMAN

Drop a table as a matter of simulation

SQL> drop table test1.regions;

Table dropped.

SQL> select * from test1.regions;

select * from test1.regions
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

Starting from oracle 12c,there is a recover table feature which can recover the particular accidentally modified or dropped table to the previous point in time.

This feature is an alternative to flashback feature which has its own advantage and disadvantage

This feature needs backup to be taken and cannot be used in restoring the table to the recent changes which can be solved by flashback

Below is an example simulation of this feature

I purposefully pasted the whole output so that the steps performed by RMAN are important to understand

RMAN> recover table test1.regions until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')" auxiliary destination '/home/oracle';

Starting recover at 25-FEB-21
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID='sobu'

initialization parameters used for automatic instance:
db_name=ORCL19X
db_unique_name=sobu_pitr_ORCL19X
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/apps01/base
_system_trig_enabled=FALSE
sga_target=1504M
processes=120
db_create_file_dest=/home/oracle
log_archive_dest_1='location=/home/oracle'
#No auxiliary parameter file used


starting up automatic instance ORCL19X

Oracle instance started

Total System Global Area    1577058304 bytes

Fixed Size                     2924832 bytes
Variable Size                402656992 bytes
Database Buffers            1157627904 bytes
Redo Buffers                  13848576 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;

# mount the controlfile
sql clone 'alter database mount clone database';

# archive current online log
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET until clause

Starting restore at 25-FEB-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/04vo2gsh_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/04vo2gsh_1_1 tag=TAG20210225T102357
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl
Finished restore at 25-FEB-21

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  9 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  8 to new;
set newname for clone tempfile  1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 9, 4, 3, 8;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /home/oracle/ORCL19X/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 25-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/ORCL19X/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /home/oracle/ORCL19X/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/ORCL19X/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/ORCL19X/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /home/oracle/ORCL19X/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1 tag=TAG20210225T102357
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:20
Finished restore at 25-FEB-21

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=1065468835 file name=/home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd1lg_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=1065468836 file name=/home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd2bn_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=1065468837 file name=/home/oracle/ORCL19X/datafile/o1_mf_undotbs1_j3hcd1yg_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=1065468838 file name=/home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd146_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1065468839 file name=/home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd23d_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  9 online";
sql clone "alter database datafile  4 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  8 online";
# recover and open database read only
recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

Starting recover at 25-FEB-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 78 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf
archived log for thread 1 with sequence 79 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=77
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1 tag=TAG20210225T102742
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
archived log file name=/home/oracle/1_77_1060279903.dbf thread=1 sequence=77
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf thread=1 sequence=78
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf thread=1 sequence=79
media recovery complete, elapsed time: 00:01:32
Finished recover at 25-FEB-21

sql statement: alter database open read only

contents of Memory Script:
{
   sql clone "create spfile from memory";
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  control_files =
  ''/home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl'' comment=
 ''RMAN set'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script

sql statement: create spfile from memory

database closed
database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1577058304 bytes

Fixed Size                     2924832 bytes
Variable Size                402656992 bytes
Database Buffers            1157627904 bytes
Redo Buffers                  13848576 bytes

sql statement: alter system set  control_files =   ''/home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1577058304 bytes

Fixed Size                     2924832 bytes
Variable Size                402656992 bytes
Database Buffers            1157627904 bytes
Redo Buffers                  13848576 bytes

sql statement: alter database mount clone database

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile  6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  6;

switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 25-FEB-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/SOBU_PITR_ORCL19X/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1 tag=TAG20210225T102357
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 25-FEB-21

datafile 6 switched to datafile copy
input datafile copy RECID=21 STAMP=1065469296 file name=/home/oracle/SOBU_PITR_ORCL19X/datafile/o1_mf_users_j3hcxq5k_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  6 online";
# recover and open resetlogs
recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 25-FEB-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 78 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf
archived log for thread 1 with sequence 79 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=77
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1 tag=TAG20210225T102742
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/1_77_1060279903.dbf thread=1 sequence=77
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/1_77_1060279903.dbf RECID=78 STAMP=1065469303
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf thread=1 sequence=78
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf thread=1 sequence=79
media recovery complete, elapsed time: 00:00:07
Finished recover at 25-FEB-21

database opened

contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle''";
}
executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle''

Performing export of tables...
   EXPDP> Starting "SYS"."TSPITR_EXP_sobu_DrmC":
   EXPDP> Estimate in progress using BLOCKS method...
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   EXPDP> Total estimation using BLOCKS method: 64 KB
   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   EXPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
   EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
   EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   EXPDP> . . exported "TEST1"."REGIONS"                           5.546 KB       4 rows
   EXPDP> Master table "SYS"."TSPITR_EXP_sobu_DrmC" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_sobu_DrmC is:
   EXPDP>   /home/oracle/tspitr_sobu_50421.dmp
   EXPDP> Job "SYS"."TSPITR_EXP_sobu_DrmC" successfully completed at Thu Feb 25 19:43:49 2021 elapsed 0 00:00:50
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script

Oracle instance shut down

Performing import of tables...
   IMPDP> Master table "SYS"."TSPITR_IMP_sobu_pqnF" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_sobu_pqnF":
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
   IMPDP> . . imported "TEST1"."REGIONS"                           5.546 KB       4 rows
   IMPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
   IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
   IMPDP> Job "SYS"."TSPITR_IMP_sobu_pqnF" successfully completed at Thu Feb 25 19:45:30 2021 elapsed 0 00:00:47
Import completed


Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_temp_j3hcp6y2_.tmp deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/onlinelog/o1_mf_3_j3hd1h0j_.log deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/onlinelog/o1_mf_2_j3hd1d8g_.log deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/onlinelog/o1_mf_1_j3hd1798_.log deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/datafile/o1_mf_users_j3hcxq5k_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd23d_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd146_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_undotbs1_j3hcd1yg_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd2bn_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd1lg_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl deleted
auxiliary instance file tspitr_sobu_50421.dmp deleted
Finished recover at 25-FEB-21

RMAN> exit


Recovery Manager complete.

My table is back now!

SQL> select count(*) from test1.regions;

  COUNT(*)
----------
         4

SQL> select * from test1.regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

CELL EFFICIENCY RATIO EXADATA

Often, when invoking cellcli utility in exadata storage server,you see a ratio banner called cell efficiency ratio(CER) after copyright message.Have you ever think of what it is and why has it been displayed everytime?

I see this banner everytime and ignore it because the ratio value is always small.But suddenly , i saw a huge rise in the CER due to some reason.I know that before i see this rise ,i had offloaded lot of sql queries with smart scan,storage index,HCC,flash cache keep etc.. This is the main reason for the huge increase in CER

To define CER, it is the ratio between the data touched on the storage tier and data processed on the database tier

CER = Data touched on storage cell/Data processed by database tier

In above picture,CER is 489 which means most data is processed by database itself or to be descriptive,less data is offloaded for smart scan,storage index is not efficiently used and touched from storage layer

CellCLI> list cell attributes offloadEfficiency
         489.3

Dont get frustated by seeing the low efficiency value but be happy that you dont have any negative values over there!Because negative value indicate that you dont use exadata upto the optimum level for what you pay

Though, CER is no longer increased in one day or night.The value gets better gradually on offloading data.

So always try to offload most of the oltp or batch workloads to the cell storage server and gradually increase CER for which you get blistering performance

The above output shows pretty impressive cell efficiency rate which shows that cell storage server is optimally used

CellCLI> list cell attributes offloadEfficiency
         16,291.9

Lot of objects are kept in flashcache to increase CER.For example, look the following output

The more objects you keep in flash memory,more IO is reduced for database

         
CellCLI> LIST FLASHCACHECONTENT detail

         cachedKeepSize:         0
         cachedSize:             65536
         dbID:                   3727006149
         dbUniqueName:           EXDBX
         hitCount:               2
         missCount:              0
         objectNumber:           87176
         tableSpaceNumber:       1

OSSIPC:SKGXP:[19a6f4c0.31278]{obj}: SKGXPCNH: 0x19a92400 SKGXPCON_CLOSED (1) sconno 40c549b5 accono 767a0096 admno 555ef0 ospid 6155 ANT

Cause:

I create diskgroups for RAC node using ASMCA on 11g but the diskgroup fail to create due to timeouts by VKTM background process and it took almost 3 days for the diskgroup

The thing is even after 3 days, ASMCA still spinning the gears and it pretty frustates.

I checked each and every corner of the ASM,ASMCA,database and clusterware logs but still it is like chasing the tail in a loop

Workaround:

Always give appropriate value to kernel parameters which indirectly affects the performance of lot of tasks performed in future

[oracle@exdbadm01 dbs]$ cat /etc/sysctl.conf|grep net.core
net.core.rmem_default=262144
net.core.rmem_max=8388608
net.core.wmem_default=262144
net.core.wmem_max=2097152

Finally diskgroups got created with in fraction of seconds which really shocked .

As i try to create diskgroups by rebuilding the whole clusterware and database setup almost for 10 to 15 times

All the time it took more than 2 days and i could find timeout and connection retry errors repeatedly

Connect retry: sleeping for 2 seconds, connect attempt 3130 out of maximum 7 attempts

There were lot of vktm hang messages continuously without any trace.Atlast after applying the recommended changes from DBESP blog,the issue got resolved and diskgroups created very fast.

Format the output of numeric scientific notation(12E+10) in sqlplus

In sqlplus commandline, we dont get proper output on the screen for large number because of width and column number format rather we get scientific notation which is absurd in case of dbid or scn number of a database.

There are two ways to get proper output with full digits

One variable is numwidth

kish@exdbx<>select 10000000000*9 from dual;

10000000000*9
-------------
   9.0000E+10

Elapsed: 00:00:00.00
kish@exdbx<>set numwidth 25
kish@exdbx<>select 10000000000*9 from dual;

            10000000000*9
-------------------------
              90000000000

Another variable is col column_name format 99999….

kish@exdbx<>select 10000000000*3 from dual;

10000000000*3
-------------
   3.0000E+10

Elapsed: 00:00:00.01
kish@exdbx<>col 10000000000*3 format 99999999999
kish@exdbx<>select 10000000000*3 from dual;

10000000000*3
-------------
  30000000000

Elapsed: 00:00:00.02

RAC clusterware commands

To stop the cluster resources on all nodes in RAC cluster

[root@exdbadm01 oracle]# crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.crsd' on 'exdbadm02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.MGMT.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'exdbadm01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.oc4j' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.cvu' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.MGMT.dg' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'exdbadm02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.exdbadm01.vip' on 'exdbadm01'
CRS-2677: Stop of 'ora.DATA.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'exdbadm02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.exdbadm02.vip' on 'exdbadm02'
CRS-2677: Stop of 'ora.FRA.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.cvu' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.exdbadm01.vip' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.exdbadm02.vip' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.MGMT.dg' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm01'
CRS-2677: Stop of 'ora.MGMT.dg' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm02'
CRS-2677: Stop of 'ora.asm' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'exdbadm01'
CRS-2677: Stop of 'ora.asm' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.ons' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'exdbadm01'
CRS-2677: Stop of 'ora.net1.network' on 'exdbadm01' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'exdbadm01' has completed
CRS-2677: Stop of 'ora.crsd' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.evmd' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm01'
CRS-2677: Stop of 'ora.evmd' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.asm' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'exdbadm01'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'exdbadm01'
CRS-2677: Stop of 'ora.oc4j' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'exdbadm02'
CRS-2677: Stop of 'ora.cssd' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'exdbadm01'
CRS-2677: Stop of 'ora.ons' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'exdbadm02'
CRS-2677: Stop of 'ora.net1.network' on 'exdbadm02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'exdbadm02' has completed
CRS-2677: Stop of 'ora.crsd' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.evmd' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm02'
CRS-2677: Stop of 'ora.diskmon' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.asm' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'exdbadm02'
CRS-2677: Stop of 'ora.ctssd' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'exdbadm02'
CRS-2677: Stop of 'ora.cssd' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'exdbadm02'
CRS-2677: Stop of 'ora.diskmon' on 'exdbadm02' succeeded

To start cluster resources on local node

[root@exdbadm01 oracle]# crsctl start cluster
CRS-2672: Attempting to start 'ora.mdnsd' on 'exdbadm01'
CRS-2676: Start of 'ora.mdnsd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'exdbadm01'
CRS-2676: Start of 'ora.gpnpd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'exdbadm01'
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'exdbadm01'
CRS-2676: Start of 'ora.cssdmonitor' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.gipcd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'exdbadm01'
CRS-2672: Attempting to start 'ora.diskmon' on 'exdbadm01'
CRS-2676: Start of 'ora.diskmon' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.cssd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'exdbadm01'
CRS-2676: Start of 'ora.ctssd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'exdbadm01'
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'exdbadm01'
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'exdbadm01'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'exdbadm01'
CRS-2676: Start of 'ora.evmd' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.drivers.acfs' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'exdbadm01'
CRS-2676: Start of 'ora.asm' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'exdbadm01'
CRS-2676: Start of 'ora.crsd' on 'exdbadm01' succeeded

To start database instances on all nodes

[oracle@exdbadm01 ~]$ srvctl start database -d exdbx
[oracle@exdbadm01 ~]$

To check the status of the cluster resource.Note that this report is very important while troubleshoot of RAC issues

[oracle@exdbadm01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.DATA2.dg
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.FRA.dg
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.LISTENER.lsnr
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.MGMT.dg
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.asm
               ONLINE  ONLINE       exdbadm01                Started
               ONLINE  ONLINE       exdbadm02                Started
ora.gsd
               OFFLINE OFFLINE      exdbadm01
               OFFLINE OFFLINE      exdbadm02
ora.net1.network
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.ons
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
ora.registry.acfs
               ONLINE  ONLINE       exdbadm01
               ONLINE  ONLINE       exdbadm02
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       exdbadm02
ora.cvu
      1        ONLINE  ONLINE       exdbadm02
ora.exdbadm01.vip
      1        ONLINE  ONLINE       exdbadm01
ora.exdbadm02.vip
      1        ONLINE  ONLINE       exdbadm02
ora.exdbx.db
      1        ONLINE  ONLINE       exdbadm01                Open
      2        ONLINE  ONLINE       exdbadm02                Open
ora.oc4j
      1        ONLINE  ONLINE       exdbadm02
ora.scan1.vip
      1        ONLINE  ONLINE       exdbadm02

To check the status of the database instances on all nodes

[oracle@exdbadm01 ~]$ srvctl status database -d exdbx
Instance exdbx1 is running on node exdbadm01
Instance exdbx2 is running on node exdbadm02

STORAGE INDEX EXADATA

Storage inde(SI) itself is not an index but a filter which is a cache area used in cell server to reduce disk IO on the database layer.

Note:In this article,i will use the term SI instead of storage index so dont get confused

Research say that SI is stored in heap of cellsrv which also has swapping mechanism due to insufficient memory.SI has a regional index which has min and max value column upto eight columns which stores the cached data in 1MB chunks of storage

Storage index also stores nulls to track all the null values from the table

There are certain conditions whenever storage index should be used

Smart scan,At least one predicate and comparison operators.Below are the operators which are most commonly used and conditions which satisfy SI

=,<,>,BETWEEN,>=,<=,IN,IS NULL,IS NOT NULL

There are three hidden parameters which can be used to influence storage index

kish@exdbx<>select KSPPINM from x$ksppi where (KSPPINM like '%kcfis_sto%') or (KSPPINM like '_cell%mode');

KSPPINM
--------------------------------------------------------------------------------
_kcfis_storageidx_disabled
  < == used to disable storage index (default = enabled)
_kcfis_storageidx_diag_mode
 < == database layer cause storage cell to generate trace files on affected cell nodes
_cell_storidx_mode
 < == used for filter options w.r.t conditions(>,<,= ...) (EVA,KDST,ALL)

How to identify if a storage index is used or not

cell physical IO bytes saved by storage index‘ is the statistic name for storage index which show the IO saved by storage index

We can get these details of storage in combination of v$sysstat,v$statname and v$mystat views

Also storage index(SI) requires sufficient memory for utilization because of its nature.If there are less pages to handle the incoming processes,then storage filter cant be used.But all time,there is sufficient memory to use SI though

kish@exdbx<>select name from v$statname where name like '%storage%';

NAME
----------------------------------------------------------------
cell physical IO bytes saved by storage index

Zero indicates that storage index is not being used and some value indicates the IO (mb) saved by storage index

I just perform a test to check how storage index works

First i connect to fresh session and there is no statistics(0 MB) for offloading in database.Every time we disconnect from session,all these statistical information are wiped out from the memory.Hence you all values are zero

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%');  2    3    4

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                                 0
physical write total bytes                                                0
cell physical IO interconnect bytes                                       0
cell physical IO bytes saved during optimized file creation               0
cell physical IO bytes saved during optimized RMAN file restore           0
cell physical IO bytes eligible for predicate offload                     0
cell physical IO bytes saved by storage index                             0
cell physical IO bytes sent directly to DB node to balance CPU            0
cell physical IO interconnect bytes returned by smart scan                0
cell IO uncompressed bytes                                                0

10 rows selected.

I check the count of total rows from sales table.Observe that there is no predicate on the query.As we should be aware that,storage index doesnot work on query without predicate clause.This is an evidence that storage index cannot be used in non predicate query.

kish@exdbx<>set autot traceonly
kish@exdbx<>select count(*) from sales;

Elapsed: 00:00:01.09

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |  3222   (1)| 00:00:39 |
|   1 |  SORT AGGREGATE            |       |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| SALES |  1048K|  3222   (1)| 00:00:39 |
----------------------------------------------------------------------------

Check the statistics ‘cell physical IO bytes saved by storage index‘ row.It is zero.

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%');  2    3    4

NAME                                                                     MB
---------------------------------------------------------------- ----------
physical read total bytes                                          185.6875
physical write total bytes                                                0
cell physical IO interconnect bytes                              24.8948441
cell physical IO bytes saved during optimized file creation               0
cell physical IO bytes saved during optimized RMAN file restore           0
cell physical IO bytes eligible for predicate offload            185.359375
cell physical IO bytes saved by storage index                             0
 <==========
cell physical IO bytes sent directly to DB node to balance CPU            0
cell physical IO interconnect bytes returned by smart scan       24.5667191
cell IO uncompressed bytes                                       185.539063

10 rows selected.

EXAMPLES:

lets query with equality predicate.I have just demonstrated multiple examples belowIf it bores you,i suggest you to skip the examples and go to performance part

kish@exdbx<>set autot traceonly
kish@exdbx<>select count(*) from sales where order_id = 1234;

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  3226   (1)| 00:00:39 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SALES |     1 |     6 |  3226   (1)| 00:00:39 |
------------------------------------------------------------------------------------


   2 - storage("ORDER_ID"=1234)<================================
       filter("ORDER_ID"=1234)<================================

Above output shows storage filter in predicate information section of execution plan which means that oracle has used the storage index column values to filter the unique value.If you see below output,you can see the statistics which shows the IO utlization of 92 MB from the storage layer which saved almost X% of IO

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                       91.6875
 <===========


If i check the size of the particular row,it shows 6 bytes as in execution plan.As an assumption,there should be some additional processing from storage index to scan from min value(1) to 1234 which lead to 91 MB IO cost.

kish@exdbx<>set pages 200
kish@exdbx<>set lines 1000
kish@exdbx<>SELECT NVL(vsize(COUNTRY),0)+1+NVL(vsize(ITEM_TYPE),0)+1+NVL(vsize(SALES_CHANNEL),0)+1+NVL(vsize(ORDER_PRIORITY),0)+1+NVL(vsize(ORDER_DATE),0)+1+NVL(vsize(ORDER_ID),0)+1+NVL(vsize(SHIP_DATE),0)+1+NVL(vsize(UNITS_SOLD),0)+1+NVL(vsize(UNIT_PRICE),0)+1+NVL(vsize(TOTAL_REVENUE),0)+1+NVL(vsize(TOTAL_COST),0)+1+NVL(vsize(TOTAL_PROFIT),0)+1 as ROWSIZE_IN_BYTES from sales where order_id='1234';

ROWSIZE_IN_BYTES
----------------
              67

Lets query with less than operator

kish@exdbx<>select count(*) from sales where order_id < 1234;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - storage("ORDER_ID"<1234)
       filter("ORDER_ID"<1234)

In below output,you can find an increment in storage IO statistics which is double the value (184MB) compared to previous

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                           184
 <===========

Lets query with between operator

kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;

  COUNT(*)
----------
     11112

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

   2 - storage("ORDER_ID"<=12345 AND "ORDER_ID">=1234)
       filter("ORDER_ID"<=12345 AND "ORDER_ID">=1234)

kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                      274.6875

Again,the output value of storage IO is incremented to 275 MB.These examples proves that using certain operators in predicate leads to SI scan thus reducing the need for a traditional BTREE index in database

kish@exdbx<>select count(*) from sales where order_id in ('1234','12345');

  COUNT(*)
----------
         2

Elapsed: 00:00:00.03
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                       365.375

<= operator:

kish@exdbx<>select count(*) from sales where order_id <='1234';

  COUNT(*)
----------
      1234

Elapsed: 00:00:00.03
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                      457.6875

PERFORMANCE TEST:

Lets test the performance difference between storage index and no storage index

kish@exdbx<>alter session set "_kcfis_storageidx_disabled"=true;

Session altered.

Elapsed: 00:00:00.01
kish@exdbx<>alter system flush shared_pool;

System altered.

Elapsed: 00:00:01.74
kish@exdbx<>alter system flush buffer_cache;

System altered.

Elapsed: 00:00:08.35
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;

  COUNT(*)
----------
     11112

Elapsed: 00:00:03.99
  <=========

It took 4 milliseconds for the select query to execute

Lets test the select query with storage index enabled

kish@exdbx<>alter session set "_kcfis_storageidx_disabled"=false;

Session altered.

Elapsed: 00:00:00.01
kish@exdbx<>alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.01
kish@exdbx<>alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.06
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;

  COUNT(*)
----------
     11112

Elapsed: 00:00:00.10
 <===

It took 0.10 milliseconds to complete which is almost 4 – 0.10 = 3.90 seconds faster than previous run.Amazing is it ! The query executed 3 times faster than normal run without SI

Now let us create a BTREE index on order_id column of sales table and check if there is any additional performance impact or not.I didnot want to focus on the importance of BTREE here but just curious to compare the performance

kish@exdbx<>create index BTREE_ORDER_ID on sales(order_id);

Index created.

Lets check how the index impacts optimizers plan.Note that i have not disabled the storage index.

kish@exdbx<>set autot on;
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;

  COUNT(*)
----------
     11112

Elapsed: 00:00:00.16

Execution Plan
----------------------------------------------------------
Plan hash value: 1371073561

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |     6 |    27   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| BTREE_ORDER_ID | 11113 | 66678 |    27   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ORDER_ID">=1234 AND "ORDER_ID"<=12345)

The above output shows that eventhough i didnot disable the storage index optimizer choose BTREE over storage index

BTREE gives a tough fight when it comes to index scans.If the goal is to perform single block IO, then BTREE index comes into picture but if the goal is to scan multiblock IO,then storage index is better.However it depends on various factors of how data stored in the table and optimizer shortest possible path.

If we want to check if the columns in a table are marked by storage index,then we should identify the object id of the table,tablespace id where the object is stored and database unique id of the database.Then enable dump trace from cell server

Identify the Object id of table

kish@exdbx<>select data_object_id from dba_objects where owner = 'SYS' and object_name = 'SALES';

DATA_OBJECT_ID
--------------
         88693

Next,identify ts# where the object reside

kish@exdbx<>select ts# from v$tablespace t, dba_segments s, dba_objects o where t.name=s.tablespace_name and s.segment_name=o.object_name and o.data_object_id = 88693;

       TS#
----------
         0
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         6
         4

Get the database unique from kernel view

kish@exdbx<>select ksqdngunid from x$ksqdn;

KSQDNGUNID
----------
3727006149

Dump the contents of the storage index on each celldisk to a tracefile

[root@exceladm00 trace]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Sat Feb 20 16:52:46 GMT+05:30 2021

Copyright (c) 2007, 2012, Oracle.  All rights reserved.
Cell Efficiency Ratio: 4,545

CellCLI> alter cell events = "immediate cellsrv.cellsrv_storidx('dumpridx','all',88693,6,3727006149);
Dump sequence #1 has been written to /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/exceladm00/trace/svtrc_4014_27.trc
Cell stocell successfully altered

Get ready to find valuable insights from the tracefile

you can see the message from all storage griddisk

[root@exceladm00 trace]# grep '$$$ Dumping storage idx summary for griddisk DATA_CD_cell0' /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/exceladm00/trace/svtrc_4014_27.trc
2021-02-20 16:53:18.391463*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell06_stocell:
2021-02-20 16:53:18.391463*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell05_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell02_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell04_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell03_stocell:
2021-02-20 16:53:18.396365*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell01_stocell:
2021-02-20 16:53:18.406747*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell07_stocell:
2021-02-20 16:53:18.406747*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell08_stocell:
2021-02-20 16:53:18.406747*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell09_stocell:

Again inside the tracefile,you can see object id and count of the rows of table

lo(low) and hi(high) indicate the min and max value data stored in 1MB chunks of storage index and there is col id [6] which represents column number 6 in table sales.Check also the scn which has an unique identifier for record in index.I marked the important points with [[[ … ]]]” square braces in the below trace to understand the points covered in this paragraph.Isnt that fascinating to see how storage index works !To me it is 🙂

2021-02-20 16:53:18.391463*: $$$ Dumping storage idx summary for griddisk DATA_CD_cell06_stocell:
2021-02-20 16:53:18.391463*: Dump sequence #1:
2021-02-20 16:53:18.391463*: RIDX (0x2b8f85f785cc) : st 2 validBitMap 0 tabn 0 id [[[88693]]] 0 3727006149}
2021-02-20 16:53:18.391463*: RIDX: strt 0 end 2048 offset 234881024 size [[[1048576]]] rgnIdx 224 RgnOffset 0 [[[scn: 0x0000.00fe57b3]]] hist: 0x2
2021-02-20 16:53:18.391463*: RIDX validation history: 0:FullRead 1:Undef 2:Undef 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef
2021-02-20 16:53:18.391463*: Col id [6] numFilt 10 flg 2:
2021-02-20 16:53:18.391463*: [[[lo: c3 18 2a 18 0 0 0 0  <================]]]
2021-02-20 16:53:18.391463*: [[[hi: c3 1a 41 39 0 0 0 0  <================]]]
2021-02-20 16:53:18.391463*: RIDX (0x2b8f85f786a0) : st 2 validBitMap 0 tabn 0 id {88693 0 3727006149}
2021-02-20 16:53:18.391463*: RIDX: strt 0 end 2048 offset 235929600 size 1048576 rgnIdx 225 RgnOffset 0 scn: 0x0000.00fe57b9 hist: 0x2
2021-02-20 16:53:18.391463*: RIDX validation history: 0:FullRead 1:Undef 2:Undef 3:Undef 4:Undef 5:Undef 6:Undef 7:Undef 8:Undef 9:Undef
2021-02-20 16:53:18.391463*: Col id [6] numFilt 10 flg 2:
2021-02-20 16:53:18.391463*: lo: c3 19 35 59 0 0 0 0
2021-02-20 16:53:18.391463*: hi: c3 1b 4d 18 0 0 0 0

SUMMARY

Storage index is a boon for exadata which acts as an alterntive to partitioning.However,the way to get the full potential from SI depends on the data stored in physical storage.And that being said,sorted data attracts storage index and performance compared to unsorted data

RMAN-05589: Materialized objects in skipped tablespaces prevent duplication

Cause:

This error occur due to the materialized views in tablespace which prevents duplication process

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 02/17/2021 08:37:41
RMAN-05501: aborting duplication of target database
RMAN-05589: Materialized objects in skipped tablespaces prevent duplication

Workaround:

I find very rare resource in google for this issue nor i couldnt skip the tablespace which contain materialized views

Only workaround suggested by oracle is to include the tablespace having MV and drop it after duplication

RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

Cause:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/18/2021 13:39:48
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

Workaround:

Incremental backup needs archivelogs to be enabled

SQL> archive log list
Database log mode              No Archive Mode
 <=======
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     315
Current log sequence           317

Check if your database is in archivelog mode

Else enable archivelog in your database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size             989859360 bytes
Database Buffers          637534208 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

Verify the change

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     315
Next log sequence to archive   317
Current log sequence           317

Check this post for more info!

https://wordpress.com/post/alphaoragroup.com/2276

Does rman take incremental lvl 0 backup in noarchivelog mode?

In this article, i am going to test rman level 0 incremental backup in noarchivelog mode.

For long time, i had this thing in my mind to test this scenario.Why because, i know that incremental backup happens when the database is online and it is more of a ground level hot backup or online backup we can say!

Are you curious about it? Lets see

My database is already in archivelog mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     315
Next log sequence to archive   317
Current log sequence           317

I bounce the database and change database to noarchivelog mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size                  2925024 bytes
Variable Size             989859360 bytes
Database Buffers          637534208 bytes
Redo Buffers               13848576 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

During first run,backup fails with existing archive sequence file missing error

backup incremental level 0 database plus archivelog delete input;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 02/18/2021 13:30:23
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /data01/ORCL12X/archivelog/2021_01_17/o1_mf_1_229_j07ss05h_.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

So i delete the expired archivelog backup to guide rman not to bother about very old archives

RMAN> crosscheck archivelog all;
Crosschecked 85 objects

RMAN> delete expired archivelog all;
Deleted 85 EXPIRED objects

Now i trigger only incremental level 0 backup without archivelog clause

RMAN> backup incremental level 0 database;

Starting backup at 18-FEB-21
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_DISK_9
using channel ORA_DISK_10
using channel ORA_DISK_11
using channel ORA_DISK_12
using channel ORA_DISK_13
using channel ORA_DISK_14
using channel ORA_DISK_15
using channel ORA_DISK_16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/18/2021 13:39:48
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN clearly leaves an error message saying that “cannot backup database in noarchivelog mode”

So this made me confidence and given me a solid evidence that inc backup fails in noarchivelog mode

Testing always clear scepticism!