Re: 12.2 RAC: grant visible on one node only?

On 1 Sep 2017 9:25 pm, "Martin Berger" <martin.a.berger_at_gmail.com> wrote:

I assume a rowcache optimization bites here. sys.tab$ should never be used directly (only by sys).

I remember MOS notes which tell how to fix corrupted Data Dictionary. They all go like
start in restricted mode
"update data in xxx$" (fix whatever is broken) immediately afterwards do a shutdown immediate, otherwise things will go worse.

Actually it is a shutdown abort you have to do - because during an immediate shutdown the rowcache is flushed back to the underlying tables - abort skips that and ensures your changes are permanent.

That's written for good reasons.

Vit, pure curiosity: what are you looking for in tab$ what's not available in DBA_% ?

I second that - additionally, using flashback operations on those tables is surely also no good idea.

Martin

2017-09-01 7:02 GMT+02:00 Franck Pachot <franck_at_pachot.net>:

> Hi Vit.
> I see the same. And if I select before the grant (ORA-942) I have to flush
> the shared pool after the grant to see it again. The non-existence of the
> object is not invalidated in other instances when we grant.
> Franck
>
> Le ven. 1 sept. 2017 à 04:17, vit.spinka <vit.spinka_at_vitspinka.cz> a
> écrit :
>
>> Hi all,
>> have you ever seen anything like this?
>>
>> This is a 12.2.0.1 installation, Linux 64-bit, 2-node RAC, no patches
>> applied. Multitenant database.
>>
>> One node node, I create a user and grant it select on a sys table. I can
>> connect as this user to either of the nodes; however, only one the first
>> node (where the grant was made) I can select from that table. On the other
>> node, I don't seem to have the privileges granted.
>>
>> sqlplus sys_at_prod1 as sysdba
>> create user test_42 identified by test_42;
>> grant create session to test_42;
>> grant select, flashback on sys.tab$ to test_42;
>>
>> Then try it out:
>>
>> sqlplus test_42/test_42_at_prod1
>> SQL> select count (*) from sys.tab$;
>>
>> COUNT(*)
>> ----------
>> 2220
>>
>> sqlplus test_42/test_42_at_prod2
>> SQL> select count (*) from sys.tab$;
>> select count (*) from sys.tab$
>> *
>> ERROR at line 1:
>> ORA-00942: table or view does not exist
>>
>> I guess it's a bug, but my Metalink skills seem weak today and I could
>> not find any matching document/bug.
>>
>>
>> Vit
>>
>

-- 
Martin Berger         +43 660 2978929 <+436602978929>
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

--
http://www.freelists.org/webpage/oracle-l