Dec 17, 2019

Stop/Start all RAC databases at once from each individual node

Some times you may notice to stop all the databases running on oracle rac cluster as soon as possible, and assume there are 50+ databases are running on same host, then manually stoping all the databases will take lots of time. 
So, Stopping all RAC databases running on an ORACLE_HOME at once and saving the state before shutting them down is really helpful when you are doing patching on a server.
In my case I have databases running on some nodes and not the others, it’s getting complicated to keep track of which database is running where. With this method you will shutdown all databases at once and keep track of the databases running on that node before shutting them down.

Stopping all RAC databases of an ORACLE_HOME

This command will stop all databases running on the ORACLE_HOME specified and save the state before the shutdown in a state file.
$ srvctl stop home -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp


Starting all RAC databases of an ORACLE_HOME

This command uses a state file to restart all databases previously running on a node.
srvctl start home -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 -node node1 -statefile /tmp/node1_stop.dmp

Dec 11, 2019

SQL Error: ORA-01720: grant option does not exist

After upgrading from 12.1.0.2 to 12.2.0.1, application team raised one issue. They not able to create view even all required grants exits. I reproduced the same issue. Let me explain.

Assume their is user scott. Now scott wants to create a view using tables of HR schema. SCOTT has select access on all tables of HR schema along with create view access.

e.g.,

create or replace view scott.vu_emp_details
(empid, ename, locality,street, city, state, pin, phone, email)
as
select * from
(select  empid, ename, address1,address2, city, state, pin, phone1, email1
from hr.emp_details);

Error received:

SQL Error: ORA-01720: grant option does not exist for 'hr.emp_details'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view and the grant option was
           not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view.


After long verification, I did not find any solution. But below workaround helped me.

 
-- drop the view first

drop view scott.vu_emp_details;

-- create the view then

create view scott.vu_emp_details ....

The reason behind:

"The code was changed in 12.2.0.1 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option, then ORA-1720 is the expected result (even in 11.2.0.3). In 12.2.0.1, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. The behavior in release 12.1.0.2 (and earlier) was incorrect; the new behavior in 12.2.0.1 is intentional and correct."

To correct:
1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
2. Drop and recreate the view. Dropping the view will automatically remove all grants.
In my case, the customer had just upgrade to 12.2.0.1. Hope this can help someone else!

Translate >>