2

I have an Oracle Database instance installed under an OL7. It is a 12c2. Oracle GI also installed and my database has some files stored in ASM.

I am using sqlplus to connect to the database instance and query v$asm_diskgroup. My ORACLE_HOME points to the home of my database instance.

When I connect without specifying a "@CONNECTION" (e.g using: sqlplus user/name), then the view v$asm_diskgroup returns no rows. When I connect specifying a @TNSNAME (e.g: sqlplus user/name@TNSNAME ), then the view v$asm_diskgroup returns data correctly.

Also, when I connect using sql developer, from another machine, the v$asm_diskgroup returns the rows.

Why are no v$asm_diskgroup rows returned when I use sqlplus without @CONNECTION, that is, the default logon?

Improve this question
2
  • 3
    Let me guess, you have a grid user for Grid infrastructure, your listener runs as grid user (which means your server process inherits the privileges of grid user when you connect through the listener), and your disks are owned by grid user.
    – Balazs Papp
    Dec 21, 2017 at 18:22
  • Yes, you right. The grid user owns the disk. I guess that you clarified this. I dont knew that server process inherits listener user rights (I'm new on Oracle). When I connect with "sqlplus / ..." ,it bypass listener, right? Then, it creates the server process under the some other user (i'm calling the sqlplus with the root). Also, i did another test: testing with the oracle database instance owner, all works fines. The oracle owner is in same groups that grid owner. I will search and study more about oracle. This is very interesting. Thanks @BalazsPapp
    – Rodrigo
    Dec 22, 2017 at 17:31

3 Answers 3

Reset to default
0

You would need to connect to the ASM instance using a user ID that has the SYSASM system privilege:

connect scott as sysasm;

Balaz Papp's comment above is correct in the explanation why connected without a connect string is working. And his comment below is correct; any user could be granted query access to the view, when connected to the regular Oracle database instance.

Improve this answer
2
  • 2
    What?! SYSOPER is not more powerful. SYSOPER can not query v$asm_diskgroup, or better: it can not even query dual. There is no SYSASM privilege in database instances, it is for ASM instances. And any user can query diskgroup information with select/read privilege on (g)v$asm_diskgroup.
    – Balazs Papp
    Dec 21, 2017 at 20:10
  • Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
    – Mark Stewart
    Dec 21, 2017 at 20:13
0

@BalazPapp answered the question in his comment.

When I connect with "sqlplus / ..." ,it seems bypass listener... Then, it creates the server process under some other user (I'm calling the sqlplus with the root).

Also, I did another test: testing with the oracle database instance owner, all works fines. The oracle owner is in same groups that grid owner.

Improve this answer
-1

you need to set the search string in ASM.

Failing to do so, will result in the gv$asm_diskgroup view to be empty.

Tomorrow morning with a clearer head and a desktop to give you exact commands I will edit and extend this answer.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.