Make DBA Life Easy

Change ASM Instance SYS ASMSNMP User Password

Change ASM instance SYS ASMSNMP user password by using ASM command “passwd”. If ASM password file is stored and shared in ASM diskgroup, then SQL “alter user” can still be used to change ASM user password.

Here is the way to change ASM instance SYS/ASMSNMP user password in 12c.

$ asmcmd
ASMCMD> pwget --asm
+OCR_VOTE/orapwASM

ASMCMD> cd +OCR_VOTE
ASMCMD> ls -l orapwASM
Type     Redund Striped Time           Sys Name
PASSWORD UNPROT COARSE  AUG 08 17:00:00 N  orapwASM => +OCR_VOTE/ASM/PASSWORD/pwdasm.256.919359249

ASMCMD> cd +OCR_VOTE/ASM/PASSWORD
ASMCMD> ls -l
Type     Redund Striped Time            Sys Name
PASSWORD UNPROT COARSE  AUG 08 17:00:00 Y   pwdasm.256.919359249
ASMCMD>

To see users in password file:

ASMCMD> lspwusr
Username sysdba sysoper sysasm
 SYS     TRUE   TRUE    TRUE
 ASMSNMP TRUE   FALSE   FALSE

ASMCMD> lspwusr --suppressheader
 SYS     TRUE TRUE  TRUE
 ASMSNMP TRUE FALSE FALSE
ASMCMD>

Change user password:

ASMCMD> passwd
usage: passwd <user>
help: help passwd
ASMCMD> help passwd
passwd
 Changes the password of a user.

Synopsis
 passwd <user>

Description
 The option for the passwd command is described below.

user - Name of the user.

An error is raised if the user does not exist in the Oracle ASM
 password file. The user is first prompted for the current password,
 then the new password. The command requires the SYSASM privilege to run

Examples
 This example changes the password of the oracle2 user.

ASMCMD [+] > passwd oracle2
 Enter old password (optional):
 Enter new password: ******


ASMCMD> passwd sys
Enter old password (optional):
Enter new password: *********
ASMCMD>

We can still use “alter user ” to change sys and asmsnmp user password :

SQL> alter user sys identified by "XXXXXXXX";
alter user sys identified by "XXXXXXXX"
 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> connect / as sysasm
Connected.
SQL> alter user sys identified by "XXXXXXXX";

User altered.

Add Extra ASM Control File

Use RMAN command “restore controlfile from” to duplicate a controlfile.

Playing around to add another control file into ‘+DATA2’ disk group.

Check current control files

SQL> select name from v$controlfile;

NAME
----------------------------------------------------------
+DATA2/ractest/controlfile/current.523.925063153
+FRA/ractest/controlfile/current.1395.925063153

SQL> show parameter control_file

NAME                                TYPE VALUE
------------------------------------ ----------- -------------------------
control_file_record_keep_time integer 7
control_files                 string   +DATA2/ractest/controlfile/current.523.925063153, +FRA/ractest/controlfile/current.1395.925063153

Change “control_file” parameter in spfile only.

SQL> alter system set control_files='+DATA2','+DATA2/ractest/controlfile/current.523.925063153','+FRA/ractest/controlfile/current.1395.925063153' scope=spfile;

System altered.

Shut down and startup in nomount mode.

SQL> shutdown immediate;
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 8551575552 bytes
Fixed Size 2270360 bytes
Variable Size 1543506792 bytes
Database Buffers 6996099072 bytes
Redo Buffers 9699328 bytes

Copy another control file to ‘+DATA2’ by restoring from any current available control file.

RMAN> restore controlfile from '+DATA2/ractest/controlfile/current.523.925063153';

Starting restore at 13-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 instance=ractest1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA2/ractest/controlfile/current.555.925125605 <-- New one
output file name=+DATA2/ractest/controlfile/current.523.925063153 <-- Old one
output file name=+FRA/ractest/controlfile/current.1395.925063153  <-- Old one
Finished restore at 13-OCT-16

RMAN> exit

Recovery Manager complete.

Check the parameter ‘control_file’ was updated automatically.

SQL> show parameter control_file

NAME                           TYPE    VALUE
------------------------------ ------- ----------------------
control_file_record_keep_time  integer 7
control_files                  string  +DATA2/ractest/controlfile/current.555.925125605,
+DATA2/ractest/controlfile/current.523.925063153, +FRA/ractest/controlfile/current.1395.925063153


SQL> alter database open;

Database altered.

SQL> show parameter control_file

NAME                          TYPE    VALUE
----------------------------- ------- ------------------------------
control_file_record_keep_time integer 7
control_files                 string +DATA2/ractest/controlfile/current.555.925125605,
+DATA2/ractest/controlfile/current.523.925063153, +FRA/ractest/controlfile/current.1395.925063153

TNS-12542: TNS:address already in use

Listener port should be dedicated to listener only.

Trying to create a dedicated Listener for building active standby database without touching current listeners in a critical production environment. Then got those errors:

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2016 14:24:31

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

Starting /u01/app/oracle/product/11.2.0/dbhome_2/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/racnode1/listener_standby/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=8888)))
TNS-12542: TNS:address already in use
 TNS-12560: TNS:protocol adapter error
 TNS-00512: Address already in use
 Linux Error: 98: Address already in use

Listener failed to start. See the error message(s) above...

Checked port 8888 has been used :

$ netstat -na|grep -i 8888
tcp 0 0 127.0.0.1:34244 127.0.0.1:8888 TIME_WAIT
tcp 0 0 127.0.0.1:34245 127.0.0.1:8888 TIME_WAIT
tcp 0 0 ::ffff:127.0.0.1:8888 :::* LISTEN

SOLUTION

It is resolved by using a free port 9999.

$lsnrctl start LISTENER_STANDBY

$lsnrctl status LISTENER_STANDBY

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2016 15:09:53

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=9999)))
STATUS of the LISTENER
------------------------
Alias LISTENER_STANDBY
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 11-OCT-2016 15:07:36
Uptime 0 days 0 hr. 2 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/racnode1/listener_standby/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=racnode1)(PORT=9999)))
Services Summary...
Service "TSTSTBY1" has 1 instance(s).
 Instance "TSTSTBY1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

ORA-01623: log 31 is current log for instance RACTEST3 (thread 3) – cannot drop

Disable thread before dropping the online redo logfiles.

Three nodes RAC database was migrated to two nodes RAC database, while trying to drop online redo logs of thread 3, the errors are as below:

SQL> alter database drop logfile group 31;
alter database drop logfile group 31
*
ERROR at line 1:
ORA-01623: log 31 is current log for instance RACTEST3 (thread 3) - cannot drop
ORA-00312: online log 31 thread 3: '+DATA1/ractest/onlinelog/group_31.409.924815767'
ORA-00312: online log 31 thread 3: '+FRA/ractest/onlinelog/group_31.262.924815769'

SOLUTION

Disable thread 3 by :

SQL> alter database disable thread 3;

Database altered.

Then drop the redo logs successfully.

SQL> alter database drop logfile group 31;

Database altered.

SQL> alter database drop logfile group 32;

Database altered.

SQL> alter database drop logfile group 33;
Database altered.

...
..
.

ORA-15014: path ‘/dev/oracleasm/disks/ASM_FRA01’ is not in the discovery set

Setting asm_diskstring parameter helps creating a successful ASM diskgroup.

When tried to create diskgroup FRA, and got errors as below:

SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/ASM_FRA01' NAME ASM_FRA01;
CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/ASM_FRA01' NAME ASM_FRA01
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification '/dev/oracleasm/disks/ASM_FRA01' matches no disks
ORA-15014: path '/dev/oracleasm/disks/ASM_FRA01' is not in the discovery set

Checked ASM disk already existed:

# oracleasm listdisks
...
...
ASM_FRA01
OCR_VOTE01
...
...

SQL> ! ls -ltr /dev/oracleasm/disks/ASM_FRA01
brw-rw---- 1 grid dba 253, 2 Sep 26 15:32 /dev/oracleasm/disks/ASM_FRA01

Checked parameter asm_diskstring empty:

SQL> show parameter string

NAME             TYPE      VALUE
---------------- --------- -----
asm_diskstring   string

Could not change parameter asm_diskstring=’/dev/oracleasm/disks/*’

SQL> alter system set asm_diskstring='/dev/oracleasm/disks/*';
 alter system set asm_diskstring='/dev/oracleasm/disks/*'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-15014: path 'ORCL:OCR_VOTE01' is not in the discovery set

Change parameter asm_diskstring=’/dev/oracleasm/disks/*’ in spfile only :

SQL> alter system set asm_diskstring='/dev/oracleasm/disks/*' scope=spfile;

System altered.

Stop and then start CRS again :

# /u01/app/12.1.0.2/grid/bin/crsctl stop crs
...
...
...
# /u01/app/12.1.0.2/grid/bin/crsctl start crs

Check current parameter asm_diskstring :

SQL> show parameter asm

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string
asm_diskstring string /dev/oracleasm/disks/*
asm_power_limit integer 10
asm_preferred_read_failure_groups string

Check disks path :

SQL> col PATH format a60

SQL>  select NAME,LABEL,PATH from v$asm_disk;

NAME LABEL PATH
------------------------------ ------------------------------- ------------------------------------------------------------
 /dev/oracleasm/disks/ASM_disk09
 /dev/oracleasm/disks/ASM_DISK05
 /dev/oracleasm/disks/ASM_DISK08
 /dev/oracleasm/disks/ASM_DISK01
 /dev/oracleasm/disks/ASM_DISK06
 /dev/oracleasm/disks/ASM_DISK07
 /dev/oracleasm/disks/ASM_FRA01
 /dev/oracleasm/disks/ASM_DISK04
 /dev/oracleasm/disks/ASM_DISK02
 /dev/oracleasm/disks/ASM_DISK03
OCR_VOTE01 /dev/oracleasm/disks/OCR_VOTE01

11 rows selected.

Now create diskgroup FRA successfully :

SQL> CREATE DISKGROUP FRA EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/ASM_FRA01' NAME ASM_FRA01;

Diskgroup created.