Empty v$asm_diskgroup with sqlplus
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?
oracle oracle-12c sqlplus oracle-asm
edited Dec 20 '18 at 0:53
miracle173
6,4161837
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from serverfault.com Dec 21 '17 at 18:19
This question came from our site for system and network administrators.
add a comment |
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?
oracle oracle-12c sqlplus oracle-asm
edited Dec 20 '18 at 0:53
miracle173
6,4161837
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from serverfault.com Dec 21 '17 at 18:19
This question came from our site for system and network administrators.
2
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 '17 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 '17 at 17:31
add a comment |
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?
oracle oracle-12c sqlplus oracle-asm
edited Dec 20 '18 at 0:53
miracle173
6,4161837
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
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?
oracle oracle-12c sqlplus oracle-asm
oracle oracle-12c sqlplus oracle-asm
edited Dec 20 '18 at 0:53
miracle173
6,4161837
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
edited Dec 20 '18 at 0:53
miracle173
6,4161837
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
edited Dec 20 '18 at 0:53
miracle173
6,4161837
edited Dec 20 '18 at 0:53
miracle173
6,4161837
edited Dec 20 '18 at 0:53
miracle173
6,4161837
6,4161837
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
asked Dec 21 '17 at 15:45
RodrigoRodrigo
61
61
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
migrated from serverfault.com Dec 21 '17 at 18:19
This question came from our site for system and network administrators.
migrated from serverfault.com Dec 21 '17 at 18:19
This question came from our site for system and network administrators.
2
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 '17 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 '17 at 17:31
add a comment |
2
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 '17 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 '17 at 17:31
2
2
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 '17 at 18:22
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 '17 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 '17 at 17:31
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 '17 at 17:31
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
2
What?!SYSOPERis not more powerful.SYSOPERcan not queryv$asm_diskgroup, or better: it can not even querydual. There is noSYSASMprivilege 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 '17 at 20:10
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
add a comment |
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.
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f193760%2fempty-vasm-diskgroup-with-sqlplus%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
2
What?!SYSOPERis not more powerful.SYSOPERcan not queryv$asm_diskgroup, or better: it can not even querydual. There is noSYSASMprivilege 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 '17 at 20:10
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
add a comment |
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.
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
2
What?!SYSOPERis not more powerful.SYSOPERcan not queryv$asm_diskgroup, or better: it can not even querydual. There is noSYSASMprivilege 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 '17 at 20:10
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
add a comment |
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.
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
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.
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
edited Dec 21 '17 at 20:16
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
answered Dec 21 '17 at 19:45
Mark StewartMark Stewart
7801621
7801621
2
What?!SYSOPERis not more powerful.SYSOPERcan not queryv$asm_diskgroup, or better: it can not even querydual. There is noSYSASMprivilege 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 '17 at 20:10
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
add a comment |
2
What?!SYSOPERis not more powerful.SYSOPERcan not queryv$asm_diskgroup, or better: it can not even querydual. There is noSYSASMprivilege 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 '17 at 20:10
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
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 '17 at 20:10
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 '17 at 20:10
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
Thanks for straightening me out; I forgot SYSASM in just available in ASM instances. Updated answer.
– Mark Stewart
Dec 21 '17 at 20:13
add a comment |
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.
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
add a comment |
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.
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
add a comment |
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.
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
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.
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
answered Dec 22 '17 at 2:24
SilvarionSilvarion
24415
24415
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f193760%2fempty-vasm-diskgroup-with-sqlplus%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
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 '17 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 '17 at 17:31