Sunday, May 12, 2019

Partial Indexing in Oracle 12c


There are lots of really nice capabilities introduced in the 12c database that significantly improves the manageability of partitioned environments.
One of those new capabilities is the ability to create a partial index. In fact, in Oracle 12c we can create an index on a specific partition and of course this subject will true about indexes that will create by INDEXING PARTIAL expression. This property will decrease space utilization, Create index performance improvement and also, increase the maintainperformance of the index and will increase the speed of DML on partition tables.
For using this facility when we create a table we can use INDEXING expression and use  INDEXING OFF for a partition that prevents to create an index on a specific partition.
Indexing property is in the table and partition level is by default on.

create table vahid.raptor(id number(9), create_date date,DEC VARCHAR2(4000)) INDEXING OFF
    PARTITION BY RANGE (create_date)
    INTERVAL(NUMTOYMINTERVAL(12, 'MONTH'))
    (
    partition p_1 VALUES LESS THAN(to_date('2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INDEXING OFF,
    partition p_2 VALUES LESS THAN(to_date('2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INDEXING OFF,
    partition p_3 VALUES LESS THAN(to_date('2020-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) INDEXING ON
    );

We can change property of partition indexing after create table:
   SQL> alter table vahid.raptor modify partition p_2 indexing on;
    SQL> alter table vahid.raptor modify partition p_2 indexing off;

Now I want to insert rows in the raptor and I will check index status as well as query execution status on the table.
   begin
    for i in 1.. 10000 loop
  insert into vahid.raptor values(1,TO_DATE('2000-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',15,'Raptor-F35'));

  insert into vahid.raptor values(i,TO_DATE('2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),RPAD('Raptor',100,'Raptor-F35'));

    end loop;

    commit;

   end;

gather table statistics and check the size of each segment
   begin
      dbms_stats.gather_table_stats(ownname =>'VAHID' ,tabname => 'RAPTOR' ,degree => 8,cascade => true);
    end;


Check indexing status:

select d.table_name,d.partition_name,d.indexing from dba_tab_partitions d where d.table_name='RAPTOR';


For viewing indexing property of a table using the below query:

  select table_name, def_indexing from dba_part_tables where table_name='RAPTOR';



As well as we can change above property by:

SQL> alter table vahid.RAPTOR modify default attributes indexing ON;

Let me create partial and full index on CREATE_DATE and check it.
SQL> set timing on;
SQL>   CREATE INDEX IDX_01 ON vahid.RAPTOR(CREATE_DATE) LOCAL INDEXING PARTIAL;
Index created.
Elapsed: 00:00:00.27

Check index partition status:
    select index_name, partition_name, status from dba_ind_partitions  where index_name='IDX_01' order by partition_position;

As you can see in the above grid just index IDX_01 is usable on partition p_3.
Check index space utilization:

    select p.segment_name,p.partition_name,p.bytes/1024/1024 SIZE_MB from dba_segments p where p.segment_name='IDX_01';
   
Now we will check the execution plan of query and the point is full table scan on p_1 and p_2

SELECT COUNT(create_date) FROM vahid.raptor WHERE 
    create_date BETWEEN TO_DATE('02/01/2002','MM/DD/YYYY') AND TO_DATE('02/01/2010','MM/DD/YYYY') ;



Also, check the query on partition p_3 and we will understand a big difference in the plan
   SELECT COUNT(create_date) FROM vahid.raptor WHERE 
    create_date BETWEEN TO_DATE('02/01/2019','MM/DD/YYYY') AND TO_DATE('03/01/2019','MM/DD/YYYY') ;


The point: we can rebuild unusable partitions manually:
SQL> ALTER INDEX IDX_01 REBUILD PARTITION p_1;
    select index_name, partition_name, status from dba_ind_partitions  where index_name='IDX_01' and partition_name='P_1' order by partition_position;



As we should know when I change INDEXING from ON to OFF the index will unusable.

FULL INDEXING:
At first, I dropped IDX_01 and recreate it.
SQL> drop index IDX_01;
SQL>    CREATE INDEX  IDX_01 ON vahid.raptor(create_date) LOCAL INDEXING FULL;
Index created.
Elapsed: 00:00:00.54
The time of index creation as you see increased and also the index space utilization will increase.

Thursday, March 15, 2018

ORA-01017: invalid username/password; logon denied in 12c R2

If you occurred with above error when you are installing Oracle 12C R2, be careful about needed groups. this error belong to RACDBA group. If you don't add grid user to RACDBA group oracle raise this error hence be sure when you want to create your groups please add oracle and grid in to RACDBA. also I put it oracle help center link.

Refrence

$ id oracle
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba), 
54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54327(asmdba),54330(racdba)
$ id grid
uid=54331(grid) gid=54321(oinstall) groups=54321(oinstall),54322(dba),
54327(asmdba),54328(asmoper),54329(asmadmin),54330(racdba)

Saturday, February 24, 2018

Check tablespace usage

One of the most problem when monitoring team or dba wants to check tablespace usage is real space.
In fact in the most query the query shows free space base on amount of extended space and somebody has problem because you have to calculate that how much space is remaining on your tablespace of whole. I changed a script for getting to this purpose and also it supports bigfile. you can  use it below query o check your real free space of your tablespaces.

--if bigfile=no the each datafile=32 GB
--if bigfile=YES the each datafile=32768 GB
--and also  db_block_size=8192


SELECT *
  FROM (SELECT SUBSTR(c.contents, 1, 1) C,
               a.tablespace_name,
               a.alloc_gb,
               a.alloc_gb - nvl(b.free_gb, 0) used_gb,
               nvl(b.free_gb, 0) free_gb,
               
               a.file_count,
               CASE
                 WHEN (a.max_gb - a.alloc_gb) <= 0 THEN
                  ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.alloc_gb * 100)
                 ELSE
                  ROUND((a.alloc_gb - nvl(b.free_gb, 0)) / a.max_gb * 100)
               END pct_used,
               
               case
                 when (c.bigfile = 'NO') then
                  (32 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))
                 when (c.bigfile = 'YES') then
                  (32768 * a.file_count) - (a.alloc_gb - nvl(b.free_gb, 0))
               end real_free_SIZE_GB
        
          FROM (SELECT tablespace_name,
                       COUNT(*) file_count,
                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,
                       ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb
                  FROM dba_data_files
                 GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) free_gb
                  FROM dba_free_space
                 GROUP BY tablespace_name) b,
               dba_tablespaces c
         WHERE a.tablespace_name = b.tablespace_name(+)
           AND a.tablespace_name = c.tablespace_name
        
        UNION
        
        SELECT SUBSTR(c.contents, 1, 1) C,
               a.tablespace_name,
               a.alloc_gb,
               NVL(b.used_gb, 0) used_gb,
               a.alloc_gb - NVL(b.used_gb, 0) free_gb,
               
               a.file_count,
               CASE
                 WHEN (a.max_gb - a.alloc_gb) <= 0 THEN
                  ROUND(NVL(b.used_gb, 0) / a.alloc_gb * 100)
                 ELSE
                  ROUND(NVL(b.used_gb, 0) / a.max_gb * 100)
               END
               
               pct_used,
               
               case
                 when (c.bigfile = 'NO') then
                  (32 * a.file_count) - (used_gb)
                 when (c.bigfile = 'YES') then
                  (32768 * a.file_count) - (used_gb)
               end real_free_SIZE_GB
        
          FROM (SELECT tablespace_name,
                       COUNT(*) file_count,
                       ROUND(SUM(bytes) / 1024 / 1024 / 1024) alloc_gb,
                       ROUND(SUM(DECODE(maxbytes, 0, bytes, maxbytes) / 1024 / 1024 / 1024)) max_gb
                  FROM dba_temp_files
                 GROUP BY tablespace_name) a,
               (SELECT tablespace tablespace_name,
                       ROUND(SUM(blocks * b.VALUE) / 1024 / 1024 / 1024, 2) used_gb
                  FROM v$tempseg_usage a, v$parameter b
                 WHERE b.name = 'db_block_size'
                 GROUP BY tablespace) b,
               dba_tablespaces c
         WHERE a.tablespace_name = b.tablespace_name(+)
           AND a.tablespace_name = c.tablespace_name)
 ORDER BY 7 DESC NULLS LAST, 2

Monday, August 7, 2017

Drop datafiles from a tablespace

This is an important question that I have heard a lot these days. The question is how we can remove a data file from a tablespace in oracle and what is the solution for doing this issue  and It is possible or not.I should say please concentrate on this subject that when you add a datafile in a specific tablespace Oracle distribute the extends on all datafiles for improving I/O, therefore be careful If you remove a datafile you will lost your data.What is the solution If we want to drop some datafiles from a tablespace?
Create a new tablespace in your database and move all of your segments on it for example Table, Index, MVW, Partititon and Subpartitions.

--Move Table
ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;

--Move Index
select 'alter index '||owner||'.'||index_name||' rebuild tablespace TO_TABLESPACE_NAME;' from all_indexes where owner='OWNERNAME';

--Move Partition Index
ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace> NOLOGGING;

--Move Partition
alter table owner.your_table move partition p1 tablespace TBS4 update global indexes;
in 12c:
alter table owner.your_table move partition p1 tablespace TBS4 online;

--Move LOBs:
SELECT 'ALTER TABLE <schema_name>.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE <table_space>);'
FROM DBA_TAB_COLS
WHERE OWNER = '<schema_name>' AND DATA_TYPE like '%LOB%';

ALTER TABLE SCOT.bin$6t926o3phqjgqkjabaetqg==$0 MOVE LOB(calendar) STORE AS (TABLESPACE USERS);


After that you move all your segments you can drop old tablespace and rename new tablespace with old tablespace name.

Tuesday, August 9, 2016

Data Redaction in Oracle Database 12c

I want to explain about a new feature in oracle 12c that I’ve studied recently. It’s one of the best features that I’ve seen recently in oracle security.
Data Redaction in Oracle Database 12c
Data Redaction is a new advance feature in oracle 12c that enables the protection of data shown to the user in real time, without requiring changes to the application. In before version also we have some features about it like VDP but above feature are very structured. You can read and implement daa Redaction completely in oracle web site with below link.

Thanks in Advance

Sunday, May 8, 2016

Multiprocess and Multithreaded Oracle Database


Multithreaded Configuration
A process is a mechanism in an operating system that can run a series of steps. The process execution architecture depends on the operating system. For example, on Windows an Oracle background process is a thread of execution within a process. On Linux and UNIX, an Oracle process is either an operating system process or a thread within an operating system process.

Types of Processes
A database instance contains or interacts with the following types of processes:
·         A client process runs the application or Oracle tool code.
·         An Oracle process is a unit of execution that runs the Oracle database code. In the multithreaded architecture, an Oracle process can be an operating system process or a thread within an operating system process. Oracle processes include the following subtypes:
·         A background process starts with the database instance and perform maintenance tasks such as performing instance recovery, cleaning up processes, writing redo buffers to disk, and so on.
·         A server process performs work based on a client request.

Oracle database running on Linux uses the same architecture and every process like LGWR, DBWR, SMON has it’s own OS processes. The resources consumed are visible in OS level process monitoring tools.Oracle 12c database has the same architecture. The below command was run on fresh install of 12c database.

ps -ef | grep [o]ra_
oracle 2191 1 0 06:07 ? 00:00:00 ora_pmon_cdb12c
oracle 2193 1 0 06:07 ? 00:00:00 ora_psp0_cdb12c
oracle 2195 1 31 06:07 ? 00:00:20 ora_vktm_cdb12c
oracle 2199 1 0 06:07 ? 00:00:00 ora_gen0_cdb12c
oracle 2201 1 0 06:07 ? 00:00:00 ora_mman_cdb12c
oracle 2205 1 0 06:07 ? 00:00:00 ora_diag_cdb12c
oracle 2207 1 0 06:07 ? 00:00:00 ora_dbrm_cdb12c
oracle 2209 1 0 06:07 ? 00:00:00 ora_dia0_cdb12c
oracle 2211 1 0 06:07 ? 00:00:00 ora_dbw0_cdb12c
oracle 2213 1 0 06:07 ? 00:00:00 ora_lgwr_cdb12c
oracle 2215 1 0 06:07 ? 00:00:00 ora_ckpt_cdb12c
oracle 2217 1 0 06:07 ? 00:00:00 ora_smon_cdb12c
oracle 2219 1 0 06:07 ? 00:00:00 ora_reco_cdb12c
oracle 2221 1 0 06:07 ? 00:00:00 ora_lreg_cdb12c
oracle 2223 1 5 06:07 ? 00:00:03 ora_mmon_cdb12c
oracle 2225 1 0 06:07 ? 00:00:00 ora_mmnl_cdb12c
oracle 2227 1 0 06:07 ? 00:00:00 ora_d000_cdb12c
oracle 2229 1 0 06:07 ? 00:00:00 ora_s000_cdb12c
oracle 2241 1 0 06:07 ? 00:00:00 ora_tmon_cdb12c
oracle 2243 1 0 06:07 ? 00:00:00 ora_tt00_cdb12c
oracle 2245 1 0 06:07 ? 00:00:00 ora_smco_cdb12c
oracle 2247 1 0 06:07 ? 00:00:00 ora_aqpc_cdb12c
oracle 2266 1 0 06:07 ? 00:00:00 ora_w000_cdb12c
oracle 2270 1 6 06:07 ? 00:00:03 ora_p000_cdb12c
oracle 2272 1 6 06:07 ? 00:00:03 ora_p001_cdb12c
oracle 2274 1 0 06:07 ? 00:00:00 ora_p002_cdb12c
oracle 2276 1 0 06:07 ? 00:00:00 ora_p003_cdb12c
oracle 2279 1 0 06:07 ? 00:00:00 ora_qm02_cdb12c
oracle 2283 1 0 06:07 ? 00:00:00 ora_q002_cdb12c
oracle 2285 1 0 06:07 ? 00:00:00 ora_q003_cdb12c
oracle 2309 1 0 06:07 ? 00:00:00 ora_p004_cdb12c
oracle 2311 1 1 06:07 ? 00:00:00 ora_p005_cdb12c
oracle 2329 1 5 06:08 ? 00:00:01 ora_cjq0_cdb12c
oracle 2333 1 2 06:08 ? 00:00:00 ora_p006_cdb12c
oracle 2335 1 2 06:08 ? 00:00:00 ora_p007_cdb12c
oracle 2338 1 0 06:08 ? 00:00:00 ora_vkrm_cdb12c
oracle 2346 1 0 06:08 ? 00:00:00 ora_p008_cdb12c
oracle 2348 1 0 06:08 ? 00:00:00 ora_p009_cdb12c
oracle 2350 1 0 06:08 ? 00:00:00 ora_p00a_cdb12c
oracle 2352 1 0 06:08 ? 00:00:00 ora_p00b_cdb12c
oracle 2354 1 2 06:08 ? 00:00:00 ora_j000_cdb12c
oracle 2356 1 4 06:08 ? 00:00:00 ora_j001_cdb12c
oracle 2358 1 1 06:08 ? 00:00:00 ora_j002_cdb12c
oracle 2360 1 2 06:08 ? 00:00:00 ora_j003_cdb12c
oracle 2362 1 3 06:08 ? 00:00:00 ora_j004_cdb12c
oracle 2364 1 2 06:08 ? 00:00:00 ora_j005_cdb12c
oracle 2366 1 2 06:08 ? 00:00:00 ora_j006_cdb12c
oracle 2368 1 3 06:08 ? 00:00:00 ora_j007_cdb12c
oracle 2370 1 1 06:08 ? 00:00:00 ora_j008_cdb12c
oracle 2372 1 2 06:08 ? 00:00:00 ora_j009_cdb12c
oracle 2374 1 1 06:08 ? 00:00:00 ora_j010_cdb12c
oracle 2376 1 1 06:08 ? 00:00:00 ora_j011_cdb12c
oracle 2378 1 2 06:08 ? 00:00:00 ora_j012_cdb12c
oracle 2380 1 2 06:08 ? 00:00:00 ora_j013_cdb12c
oracle 2382 1 1 06:08 ? 00:00:00 ora_j014_cdb12c
oracle 2384 1 3 06:08 ? 00:00:00 ora_j015_cdb12c
oracle 2386 1 1 06:08 ? 00:00:00 ora_j016_cdb12c
oracle 2388 1 1 06:08 ? 00:00:00 ora_j017_cdb12c
oracle 2390 1 2 06:08 ? 00:00:00 ora_j018_cdb12c
oracle 2392 1 1 06:08 ? 00:00:00 ora_j019_cdb12c
oracle 2394 1 1 06:08 ? 00:00:00 ora_j020_cdb12c
oracle 2396 1 0 06:08 ? 00:00:00 ora_j021_cdb12c
oracle 2398 1 5 06:08 ? 00:00:00 ora_m000_cdb12c
$ ps -ef | grep [o]ra_|wc
40 320 2560

Multithreaded Configuration
Starting in Oracle 12c database, you can change this and enable the new Multithreaded configuration. You can change the architecture by making Oracle database use thread based architecture instead of process based. Once changed, all Oracle processes will be threads within a few Oracle processes. Thus if CPU moves from one Oracle process to the other, the time between the context switch will be reduced significantly as the switching from one thread to the other is within the same process. During testing, this has resulted in up to 30% performance improvements. You can use the following command in SQL*PLUS to view the current value of THREADED_EXECUTION parameter.
The choice of threading model is dictated by the THREADED_EXECUTION initialization parameter.
THREADED_EXECUTION=FALSE : The default value causes Oracle to run using the multiprocess model.
THREADED_EXECUTION=TRUE : Oracle runs with the multithreaded model.
To switch to the multithreaded model, simply set the THREADED_EXECUTION parameter and restart the database.
CONN sys AS SYSDBA
ALTER SYSTEM SET threaded_execution=TRUE SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
If you encounter “ORA-01017: invalid username/password; logon denied” while restarting database then please refer to the end of this article under OS Authentication.
SQL> SHOW PARAMETER thread;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 0
threaded_execution boolean TRUE


Also for all incoming requests to make use of this architecture you need to set the below mentioned parameter in Listener.ora file.
DEDICATED_THROUGH_BROKER_<listener-name>=ON


You need to provide your own Listener name at the end.
Now we will run the OS command again to check how many processes are created.
$ ps -ef | grep [o]ra_
oracle 2544 1 0 06:25 ? 00:00:00 ora_pmon_cdb12c
oracle 2546 1 0 06:25 ? 00:00:00 ora_psp0_cdb12c
oracle 2548 1 25 06:25 ? 00:01:47 ora_vktm_cdb12c
oracle 2552 1 0 06:25 ? 00:00:01 ora_u004_cdb12c
oracle 2558 1 9 06:25 ? 00:00:41 ora_u005_cdb12c
oracle 2564 1 0 06:25 ? 00:00:00 ora_dbw0_cdb12c
$ ps -ef | grep [o]ra_|wc
6 48 384
The number of processes have been reduced from 40 to 6. That is a huge plus and that’s because there isn’t a separate process for every Oracle process. Most of the Oracle processes are now threads within these 6 processes.
OS Authentication
When you enable Multithreaded configuration then you are not allowed to log into Oracle using OS level authentication. The following error will occur if you try to.
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 6 06:40:39 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Use the following method to log in as SYS.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.


Monday, January 11, 2016

How to Chang VIP Address in Oracle RAC 11g

Today, I have a request from network administrator for changing vip address in oracle RAC database because of he needs that ip that it set on node1. You can follow below scripts for changing VIP IP address.
On node1:

[root@test-1 bin]# ./srvctl stop vip -n test-1 –f

[root@test-1 bin]# ./srvctl config vip -n test-1
VIP exists: /test-1-vip/10.8.72.27/10.8.72.0/255.255.255.0/eth0, hosting node test-1

·         Be careful, you should add new IP address to your host to each node.
·         We want to change 10.8.72.27 to 10.8.72.32.

[root@test-1 bin]# cd /grid/product/11.2.0/grid_1/bin/

[root@test-1 bin]# ./srvctl  modify nodeapps -n test-1 -A 10.8.72.32/255.255.255.0/eth0

·         Check your new IP:

[root@test-1 bin]# /sbin/ifconfig -a | egrep '(eth0|Mask)'