Category: ASM

Posted on

Install and Upgrade Grid Infrastructure 12.1.0.2

Happy New Year!

Upgraded 12.1.0.1 Grid Infrastructure to 12.1.0.2 and applied the Oct 2014 PSU. Had an error during rootupgrade.sh as well, due to the ASM spfile being on disk instead of on ASM diskgroup (MOS Note: Upgrading ASM Failed With ORA-32002: cannot create SPFILE – Doc ID 1912581.1).

The install & upgrade steps documented in PDF and uploaded to slideshare…

 

Posted on

ASM User Management Q&A

usersQ: Can you create users in ASM instance?

A: Yes, absolutely.

Q: Why?

A: For better security.

Q: I was told there is no database associated with ASM, it is just an instance, so where is this user really created?

A: You must have a password file to create users. The users are added to password file.

Q: Ha! are there default users?

A: Yes, SYS and ASMSNMP are default users.

Q: What privileges can I grant to a user?

A: You can grant SYSDBA, SYSASM or SYSOPER privileges.

Q: How do you create a new user?

A: Use SQL*Plus, connect to the instance as SYSASM for all user management. You may also use orapwusr command in asmcmd.

Q: Can you show an example of creating a new user using SQL*Plus?

A: Sure.

$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:29:59 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0

SQL> create user bt1 identified by bt1;

User created.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0
BT1                            FALSE FALSE FALSE FALSE FALSE FALSE          0

SQL> grant sysdba, sysasm to bt1;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0
BT1                            TRUE  FALSE TRUE  FALSE FALSE FALSE          0

SQL>

Q: I notice that you used 12.1 release database. Is this feature available in lower release as well?

A: Absolutely. the syntax, privileges and functionality is exactly same in 11gR2. This feature is not available in 10g ASM.

$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 5 23:07:34 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  TRUE
ASMSNMP                        TRUE  FALSE FALSE

SQL>

Q: How do you add user using ASMCMD?

A: Use the orapwusr command to add, modify or delete a user. Use the lspwusr command to list the user and privileges.

Q: Can you show how to?

A: Here, I create an user, modify its privilege, list the user, and finally drop the user. Notice that only one privilege can be granted using the –privilege option, and all other privileges gets revoked. Using SQL*Plus, I was able to grant more than one privilege.

$ asmcmd -p
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
ASMCMD [+] > orapwusr --add --privilege sysasm bt2
Enter password: ***
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
     BT2  FALSE   FALSE   TRUE 
ASMCMD [+] > orapwusr --modify --privilege sysdba bt2
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
     BT2   TRUE   FALSE  FALSE 
ASMCMD [+] > orapwusr --modify --password bt1
Enter password: ***
ASMCMD [+] > orapwusr --delete bt2
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm 
     SYS   TRUE   FALSE   TRUE 
     BT1   TRUE   FALSE   TRUE 
ASMCMD [+] >

Q: How do I connect as the new user when logging in?

A: In SQL*Plus, you use the id and password. For “asmcmd”, there is no option – connect as sysasm or sysdba privilege based on the OS group membership.

$ sqlplus bt1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:43:41 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> 
SQL> show user
USER is "SYS"
SQL>

Q: Does ASM write anywhere which user logged in?

A: I would guess so, but in reality, it does not write which user. Just says [/], instead of user name. Looking the audit file generated in  AUDIT_FILE_DEST location.

$ sqlplus bt1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:55:34 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> exit;

$ 
$ cat +ASM_ora_8024_20140305215536821385143795.aud
Audit file /u03/app/oracle/grid/12.1.0/rdbms/audit/+ASM_ora_8024_20140305215536821385143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORACLE_HOME = /u03/app/oracle/grid/12.1.0
System name:    Linux
Node name:      btsj.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: +ASM
Redo thread mounted by this instance: 0 
Oracle process number: 21
Unix process pid: 8024, image: oracle@btsj.com (TNS V1-V3)

Wed Mar  5 21:55:36 2014 -08:00
LENGTH : '149'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[0] ''

Q: You are connected as SYSDBA, how about if you connect as SYSASM?

A: Good question, here the audit file shows the actual user.

$ sqlplus bt1 as sysasm

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 21:59:43 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option

SQL> show user
USER is "SYS"
SQL> 
SQL> exit

$ cat +ASM_ora_8106_20140305215946201566143795.aud
Audit file /u03/app/oracle/grid/12.1.0/rdbms/audit/+ASM_ora_8106_20140305215946201566143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Automatic Storage Management option
ORACLE_HOME = /u03/app/oracle/grid/12.1.0
System name:    Linux
Node name:      btsj.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: +ASM
Redo thread mounted by this instance: 0 
Oracle process number: 21
Unix process pid: 8106, image: oracle@btsj.com (TNS V1-V3)

Wed Mar  5 21:59:46 2014 -08:00
LENGTH : '151'
ACTION :[7] 'CONNECT'
DATABASE USER:[3] 'bt1'
PRIVILEGE :[6] 'SYSASM'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[0] ''