Materialized view a study notes

Every time you use a normal view oracle has to execute the sql statement defined for that view (called view resolution), it must be done each time the view is used. If the view is complex this can take sometime, this is where a materialized views comes in (also known as snapshots in prior releases), unlike a view it contains space and storage just like a regular table.You can either use materialized view against a local table or a remote table .Using materialized views against remote tables is the simplest way to achieve replication of data between sites.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

You can even partition them and create indexes on them. Materialized views take a snapshot of the underlying tables which means that data may not represent the source data. To get the materialized view data up to date you must refresh it.

Materialized Views are mainly used for two reasons,
1) Replication of data to separate remote databases.
2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

With Materialized Views the performance can be improved significantly, because when a materialized view is created it stores all the data along with the execution plans. 

Basic syntax:-
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;

This is a very basic syntax , even you can specify storage level parameter while configuring materialized view .

The BUILD clause options are shown below,
IMMEDIATE(Default) : The materialized view is populated immediately.
DEFERRED : The materialized view is populated on the first requested refresh.

The REFRESH clause options are shown below,
FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.- and its time consuming .
Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.

FORCE (Default): A fast refresh is attempted. If one is not possible a complete refresh is performed.If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

Note:- A materialized view get locked while its being refreshed .

A refresh can be triggered in one of two ways,
ON COMMIT : Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. This clause may increase the time taken to complete the commit, because the database performs the refresh operation as part of the commit process.

Note:- If you want to refresh materialized view automatically they you must set job_queue_processes=n 

ON DEMAND (Default)  : The refresh is initiated by a manual request or a scheduled task.
if your refresh interval is very large and you need to refresh in between use the follwoing procedure.

SQL> EXEC DBMS_MVIEW.REFRESH('mv_name', 'C');
C -> Complete refresh 
F ->FAST refresh 
? -> Force refresh

When you create a materialized view a table segment(with same name) will get automatically created to hold the data represented by the materialized view .

If you have a large table , then creating materialized through normal method take time, especially when base table on a remote box. In this case you can make use of PREBUILT TABLE . Here you can export the  table from remote box and import the table on the target box .Depends on the requirement you can either import the full table or import a part of the table by using impdp parameter QUERY.

That is ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query. 

One simple example  is given below , 

SQL> conn hr/hr
Connected.
SQL> create table my_objects as select object_name,object_type,created from user_objects;

Table created.

SQL> create materialized view mv_objects on prebuilt table as select object_name,object_type,created from user_objects;
create materialized view my_objectsd on prebuilt table as select object_name,object_type,created from user_objects
     *
ERROR at line 1:
ORA-12059: prebuilt table "HR"."MV_OBJECTS" does not exist

That is for materialized view we need to use the same name that we used for prebuilt table.
SQL> create materialized view my_objects on prebuilt table as select object_name,object_type,created from user_objects;

Materialized view created.

SQL> create table t as select * from employees;

Table created.

SQL> col object_name for a15
SQL> set lines 222
SQL> select object_name,object_type,created from user_objects where object_name='T';

OBJECT_NAME     OBJECT_TYPE             CREATED
--------------- ----------------------- ---------
T               TABLE                   07-FEB-16

Check the contents of the materialized view 

SQL>  select object_name,object_type,created from my_objects where object_name='T';

no rows selected

As Materialized view is not yet refreshed , I did a manual refresh .

SQL> EXEC DBMS_MVIEW.REFRESH('MY_OBJECTS','C');

PL/SQL procedure successfully completed.

SQL> select object_name,object_type,created from my_objects where object_name='T';

OBJECT_NAME     OBJECT_TYPE             CREATED
--------------- ----------------------- ---------
T               TABLE                   07-FEB-16

SQL>

Note:- You can't do any DML on the underlying  table that hold the data of the materialized view . if you tried to do so , you will get error like following .

SQL> insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16');
insert into MY_OBJECTS values ('SAMPLE','TABLE','07-FEB-16')
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
SQL>

ENABLE QUERY REWRITE
A materialized view can be stored in the same database as it's base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. 

The following query does an aggregation of the data in the EMP table.

CONN scott/tiger
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |     3 |    21 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Create a materialized view to perform the aggregation in advance, making sure you specify the ENABLE QUERY REWRITE clause.

CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE 
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM   emp
GROUP BY deptno;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

The same query is now rewritten to take advantage of the pre-aggregated data in the materialized view, instead of the session doing the work for itself.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
SET AUTOTRACE TRACE EXPLAIN

SELECT deptno, SUM(sal)
FROM   emp
GROUP BY deptno;

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    21 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV |     3 |    21 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

More example of the query rewrite functionality is given in  below link.
http://www.sqlsnippets.com/en/topic-12918.html

CREATE MATERIALIZED VIEW LOG
A complete refreshes of materialized views can be expensive operations. Fortunately there is a way to refresh only the changed rows in a materialized view's base table. This is called fast refreshing. Before a materialized view can perform a fast refresh however it needs a mechanism to capture any changes made to its base table. This mechanism is called a Materialized View Log. 

Common usage syntax:- 
create materialized view log on table with primarykey|rowid ;
where ,
PRIMARY KEY 
Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log(this is default)
Note:- Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID. 
ROWID 
Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.

Note:- The materialized view log supports fast refresh for primary key materialized views only. If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default. However, the database does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time.

If your table have a PRIMARY KEY, you don't need to creata materialized view log WITH ROWID. If you don't have a PRIMARY KEY, you have to add WITH ROWID.

As more often rowids can change.Partitioned tables with enable row movement allow rowids  to change.  ALTER TABLE t MOVE will change rowids.  In 10g more and more things will  change rowids.So oracle recommented to create primary key based materialized view log.

Eg:- create materialized view log on employees with primary key;

Note how the materialized view log is not given a name. This is because a table can only ever have one materialized view log related to it at a time, so a name is not required.

Basic example for creating a materialized view ,

CREATE MATERIALIZED VIEW mv_emp_pk
 REFRESH FAST START WITH SYSDATE 
 NEXT  SYSDATE + 1/1440 ---- > every one minute 
 WITH PRIMARY KEY -- this is default , no need to specify though 
 AS SELECT employee_id,name,salary FROM emp@remote_db;

ON COMMIT
In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.

Assuming that employee table  have primary key , so create a materialized view log on employee table.If your table does'nt have a primary key defined you will get error like ORA-12014: table does not contain primary key constraint.
sql>conn hr/hr
Connected
SQL> create materialized view log on employees with primary key;
Materialized view log created.

You can see following table got created while creating materialized view log on employees table ,
MLOG$_EMPLOYEES:- This is a table created along with the materialized view. It contains data that has changed in the base table.
RUPD$_EMPLOYEES:- This table is created when a materialized view uses primary key for fast refresh. This is used to support updatable materialized views.

SQL> conn / as sysdba
Connected.
SQL> grant select any table to mahi;

Grant succeeded.

SQL> grant create any materialized view to mahi;

Grant succeeded.

SQL> create database link hrlink connect to hr identified by hr using 'study';

Database link created.

SQL> conn mahi/mahi
Connected.
SQL>
SQL> create materialized view mv_hr_employee REFRESH FAST ON COMMIT as select * from employees@hrlink;
create materialized view mv_hr_employee
*
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

While googling I learned following - 
Things learned:- This materialized view is selecting from a remote table over a database link (a distributed materialized view). For "on commit", you can use only if you have your master table in the same database where you are creating the materialized view. Therefore, on commit is not supported in remote databases. 

SQL> conn hr/hr
Connected.
SQL> create materialized view mv_employee REFRESH FAST ON COMMIT as select * from employees;

Materialized view created.

SQL> select count(1) from employees;

  COUNT(1)
----------
       107
SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       107
SQL> delete from employees where EMPLOYEE_ID=206;

1 row deleted.

SQL> select count(1) from employees;

  COUNT(1)
----------
       106
SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       107
SQL> commit;

Commit complete.

SQL> select count(1) from mv_employee;

  COUNT(1)
----------
       106
SQL>

As soon as I put commit , my materialized view got reflected 

Check the details of the materialized view using following query ,
SQL> select mview_name, refresh_method, refresh_mode, build_mode, fast_refreshable from user_mviews where mview_name = 'MV_EMPLOYEE';

How to know when was the last refresh happened on materialized views:
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;

Difference between View vs Materialized View in database
Based upon on our understanding of View and Materialized View, Let’s see, some short difference between them :

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.

2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different.

3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.

4) Performance of View is less than Materialized view.

5) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

Points to note:- 
*It is  always recommended to gather the statistics of the underlying table after materialized view got created .

*Although materialized view logs improve the performance of materialized view refreshes, they do increase the work needed to perform DDL on the base table.

*If regular refreshes are not performed, materialized view logs can grow very large, potentially reducing the performance of their maintenance and blowing tablespace limits

What is special permission in Unix -- An interesting topic for all Unix admin / DBAs

Yesterday , I had faced a strange issue while starting up oracle instance after applying PSU4 into our 12.1.0.2 database . Even when my ASM was up and running , I couldn't    able to start my database instances . Alerts log clearly showing ASM is not available .its quite strange to me so I started googling .After an hours of googling  I found the solution .The file permission of $ORACLE_HOME/bin/oracle got changed som how  while applying the PSU , So I have executed following and I am able to start my database instances .

#chown oracle:asmadmin $ORACLE_HOME/bin/oracle 

#chmod 6751 $ORACLE_HOME/bin/oracle 

  

We are familiar with setting file permission with 3 digit combination like  777,755 etc .

Now the question is,what is the purpose of this extra digit 6 here ?  and the answer is, its the combination of unix special file permission suid and sgid. 

So this time  instead of talking about Oracle specific topic , I would like to discuss more about Special permission in Unix based operating system. So for all DBAs who all are working in Unix based OS ,this will be an added advantage for them , if they have good idea about special permission in Unix. 

There are three type of special permission bits that may be setup on executable files or directories if required.

These permission bits are,

1.setuid (set user identification bit )

2.setgid (set group identification bit)

3.sticky bit

1)The suid (set user id bit)

Setting the suid bit on a file allows normal users to run that application with raised (usually superuser) priviledges. Remember that when a user launches an application, that application runs with the same permissions as that user. This is one of the fundamental differences between Windows and *nix systems.

An example of a file that has the suid bit set in most cases is the /usr/bin/passwd application. You can see that the /usr/bin/passwd application has the suid bit set by the letter s in place of the user’s eXecutable bit.

-rwsr-xr-x 1 root root 26680 May 10 13:44 passwd

For listing the setuid bit enabled files you can use the common ls command with long list parameter as follows

[root@node2 ~]# ls -lrt /bin/su

-rwsr-xr-x 1 root root 24060 Nov 27 2006 /bin/su

You can see that the owner-executable bit is set to 's', that means the executable file is setuid enabled

The passwd application allows users to change their own passwords. In order to do so, it has to write to the etc/passwd file which contains all of the accounts on a GNU/Linux system. However, if the suid bit was not set on the passwd application then the passwd application would only have the rights of the user and therefore could not make changes to the etc/passwd file. Setting the suid bit on the passwd application allows it to run as the superuser and it can therefore write the new password to the etc/passwd file.

How to set the suid bit?

Use the number 4 in front of a normal chmod string:

#chmod 4755 /home/mahi/mahi.sh

Alternatively you can use symbolic notation to get the exact result

#chmod u+s /home/mahi/mahi.sh

To unset the setuid bit use

# chmod u-s /home/mahi/mahi.sh
or
#chmod 0755 /home/mahi/mahi.sh

To search for all files in the system that have setuid bit set on them , use find command

# find / -type f -perm -04000 -exec ls -lrt {} \;

setuid on directories

Setting uid on a directory is easy to understand as it is simply ignored by Linux. i.e you can set it but it is given no special meaning when set on a directory. On Linux The setuid bit on a directory is only effective when it is on the group bit.

2)Setgid bit (set group id bit )

we can set setgid bit on both file and directory.

2.1 setgid on a file

The setgid bit is set on executable files at  the group level. When this bit is enabled , the file will be executed by the other users  with exact same privileges  that the group member have on it. SGID modes on a file don't occur nearly as frequently as SUID.

For example the linux write and wall command is owned by root with group membership set to tty. These command has setgid bit enabled on it.See the highlighted “s” in the group permission class below

[root@node2 ~]# ls -lrt /usr/bin/wall

-r-xr-sr-x 1 root tty 10420 Oct 13  2006 /usr/bin/wall

The write and wall commands are used to send messages to other users' terminals (ttys) or to any psuedo terminal (pts/n). The write command writes a message to a single user, while wall writes to all connected users. For eg;

[root@node2 ~]# wall

Hi

h r u?

^d

Then it will send message to all connected users. Sending text to another user's terminal or graphical display is normally not allowed. In order to bypass this problem, a group has been created, which owns all terminal devices. When the write and wall commands are granted SGID permissions, the commands will run using the access rights as applicable to this group, tty in the example. Since this group has write access to the destination terminal, also a user having no permissions to use that terminal in any way can send messages to it.

From the following output you can see that  each terminal device (tty1 ,pts/0,pts/1 etc) is owned by the group tty . So when a normal user run the ‘wall’ or ‘write’ command it will run with the access rights of the group tty .From the output we can see that ‘tty’  group have  the write permission  on each  destination terminal. So we will get the output on each terminal

[root@node2 ~]# ls -lrt /dev/tty1

crw--w---- 1 root tty 4, 1 Jan 21 23:29 /dev/tty1

[root@node2 ~]# ls -lrt /dev/pts

crw--w---- 1 root tty 136, 0 Jan 21 23:19 0

crw--w---- 1 root tty 136, 1 Jan 21 23:29 1

You can also send message to any destination terminal by using ‘echo’ if you have enough permission, for eg

[root@node2 ~]# echo Hi dear > /dev/pts/1

Then it will display the message “Hi dear “ on the pseudo terminal  dev/pts/1 , now try to execute

The same command as a normal user 

[mahi@node2 ~]$ echo Hi dear > /dev/pts/1

-bash: /dev/pts/1: Permission denied

Ie local user have no write permission to the destination terminal , here the ‘setuid’ bit comes into play .

How to set setgid bit on files and directory

To set setgid bit you must be either be the owner of the file or root , you can use chmod command to set setgid on files and directories

#chmod 2755 /home/mahi/free.sh

Alternatively you can use symbolic notation to get the exact result

#chmod  g+s  /home/mahi/free.sh

To unset the setgid bit

# chmod  g-s  /home/mahi/free.sh
or
#chmod 0755 /home/mahi/free.sh

To search for all files and directories in the system having setgid bit enabled

# find / -type f -perm -02000 -exec ls -lrt {} \;   (for  directories use ‘d’ instead of ‘f’ )

2.2 Setgid bit on directories

We can use the command chmod to set the group ID bit for a directory.
#chmod g+s /mydir

or with numeric mode:
#chmod 2775 /mydir

After the change, the permission of the directory "/mydir" becomes "drwxrwsr-x".

drwxrwsr-x 3 ora ora 4096 2010-03-18 19:57 /mydir
But what is so special about setting the group ID for a directory? The trick is that when another user creates a file or directory under such a directory "/mydir", the new file or directory will have its group set as the group of the owner of "/mydir", instead of the group of the user who creates it.

For example, if mahi belongs to the groups "mahi" (main group) and "ora", and he creates a file "setgid.txt" under the diretory "/mydir", "setgid.txt" will be owned by the group of "ora" instead of  mahi's main group ID "mahi".

   -rw-r--r-- 1 mahi ora   10 2010-03-18 20:01 setgid.txt

Even if ‘mahi’ does not belong to the group "ora", the files or directories he creates under "/mydir" (if "/mydir" grants the write permission to "others") will also get owned by group "ora".

You can use such feature to share files within the group. Create a directory which permits the group to write, and set the group ID bit. Every files or directories created under it will have the same group ownership. Therefore, the whole group can share them.

One commnad for finding all the files with setuid or setgid bit

#find / -perm  +6000 -type f -exec ls -lrt {} \;

3)sticky bit 

The sticky bit is normally set on public writable directories to protect files and sub-directories  of individual users from being  deleted  by other users. This bit is typically set on /tmp and /var/tmp directories. Thus If the sticky bit is set for a directory, only the owner of that directory or the owner of a file can delete or rename a file within that directory.

Normally all users are allowed to create and delete files and sub-directories in these directories.

With default permission, any user can remove any others files and sub-directories.

Sticky bit shows up as a t in the execute position of the other permission , foe eg

[root@server ~]# ls -ld /var/tmp/ /tmp

drwxrwxrwt 32 root root 4096 Mar 15 13:35 /tmp

drwxrwxrwt  2 root root 4096 Feb 20 10:35 /var/tmp/

How to set sticky bit permission

When digit 1 is used with chmod command it sets the sticky bit on the directory

#chmod  1777 test

Alternatively you can use symbolic notation to set the same

#chmod o+t  test

There is no need to specify  ‘o’ along with chmod command you can simply do it with

#chmod  +t  test 

How to unset sticky bit permission

 #chmod  -t  test

Or

#chmod 0777 test

Note:

You may see both a ‘t’ and ‘T’ to indicate that the sticky bit is set. You can see a ‘t’  if the world already have a execute permission before you set the sticky bit , and a ‘T’ if the world  didn’t have execute set before the sticky bit was put in place. For eg:

#mkdir  testdir

#chmod 754 testdir

#chmod  o+t  testdir

# ls -ld testdir

drwxr-xr-T 2 root root 4096 Mar 15 14:23 testdir

To list all directories having sticky bit enabled

#find / -perm  -1000 -type d

Note:

Linux ignores the sticky bit when it sets on files. It is possible to set combination of suid , sgid and the sticky bit at the same time . 

0	Remove sticky bit,suid &sgid
1	Sets sticky bit
2	Sets sgid bit
3	Sets sticky bit and sgid bit
4	Sets suid bit
5	Sets sticky bit and suid
6	Sets suid and sgid bit
7	Sets  sticky bit, suid &sgid bit

Be sure to note that using a 0 removes suid , sgid and sticky bit all at the same time . if you use 0 to remove suid but you still want the sticky bit set you need to go back and reset the sticky bit.

How to apply an interim patch in RAC database .

we can follow the following practice to apply an interim patch(one- off patch ) in RAC database .

1.Before applying  the interim patch in RAC nodes , first take the backup of ORACLE_HOME in each database nodes through tar command .

As root/oracle user take the backup of oracle home that we intend to patch 

#tar -cvzf /bkp_location/oracle_12.1.0.2.tar.gz  /u01/app/oracle/product/12.1.0.2/

 To appy patch we need to login as  oracle user 

2.If the patch is a rolling one ,we can apply the patch either through local mode or in rolling patch mode.
How to check the patch is a rolling one  ?

[oracle@pdc01]$ $ORACLE_HOME/OPatch/opatch query -is_rolling_patch /nfs1/BINARIES/DB_Patch/p20476175_121020_Linux-x86-64.zip
Oracle Interim Patch Installer version 12.1.0.1.8
Copyright (c) 2015, Oracle Corporation.  All rights reserved.
Oracle Home       : /u01/app/oracle/product/12.1.0.2/db_1
Central Inventory : /u01/app/aoraInventory
   from           : /u01/app/oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.1.0.1.8
OUI version       : 12.1.0.2.0
Log file location : /u01/app/oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2015-11-12_11-32-12AM_1.log
--------------------------------------------------------------------------------
 Patch is a rolling patch: true
 Patch is a FMW rolling patch: false
OPatch succeeded.
[oracle@pdc01]$
Or you can test it by following method,

3.Unzip the patch and go to patch directory

[oracle@pdc01]$unzip /nfs1/BINARIES/DB_Patch/p20476175_121020_Linux-x86-64.zip
[oracle@pdc01 20476175]$ /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatch query -all | grep -i "Need to shutdown Oracle instances"
 Need to shutdown Oracle instances: true
[oracle@pdc01 20476175]$
[oracle@pdc01 20476175]$ /u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatch query -all | grep -i "Patch is a rolling patch"
 Patch is a rolling patch: true
[oracle@pdc01 20476175]$

4.Shut down all the services running from the Oracle home of the database of Oracle Database home. - please see the readme file for more information.

5.Apply the interim patch (one-off patch )
choose one of the following method to apply interim patches in RAC environment.

5.1   -local
              Patch the local node, then update inventory of the
              local node. Do not propagate the patch or inventory
              update to other nodes.

[oracle@pdc01 20476175]$/u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatch apply -local 

Repeat the steps in remaining nodes .
 

5.2 -all_nodes (Default)
            if you don't specify -local along with opatch apply  then opatch will  patch node 1 first , once it finish with node1 , opatch will list the remaining nodes to patch . At this point we can select the remaining nodes and opatch will continue patching with remaining nodes .

[oracle@pdc01 20476175]$/u01/app/oracle/product/12.1.0.2/db_1/OPatch/opatch apply