Introduction.

In current project we are relying on replicated databases  for environments like a Pet. On that cloned environment ( 12.2 Rac database  with ASM)  I received request to add a specific adba group to the user(s) who have sysdba privs in the databases . Below you will find the notes i have created to  get there. In the source environment a password file has been setup in asm. During the cloning the database MYDBP is  renamed to MYDBA with the nid tool.

Creating the Password file as a first step in ASM

orapwd file=+MYDBP_DATA01 password=hugo10! entries=10

ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
ORA-17503: ksfdopn:2 Failed to open file
+MYDBP_DATA01/MYDBA/PASSWORD/pwdMYDBa.312.981043193
ORA-15173: entry ‘MYDBA’ does not exist in directory ‘/’
ORA-06512: at line 4
ORA-06512: at “SYS.X$DBMS_DISKGROUP”, line 665
ORA-06512: at line 2

orapwd file=’+MYDBP_DATA01/MYDBA/orapwdb’ dbuniquename=’MYDBA’

ASMCMD> pwcreate –dbuniquename <dbuniquename> <file_path> <sys-password>

ASMCMD [+MYDBP_DATA01/MYDBP/PASSWORD] > ls
pwdMYDBp.256.975838613

## below error
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
ORA-17503: ksfdopn:2 Failed to open file
+MYDBP_DATA01/MYDBA/PASSWORD/pwdMYDBa.312.981043193
ORA-15173: entry ‘MYDBA’ does not exist in directory ‘/’
ORA-06512: at line 4
ORA-06512: at “SYS.X$DBMS_DISKGROUP”, line 665
ORA-06512: at line 2

## as a Workaround:

oracle@Mysrvrr:/app/oracle/product/12201/db/dbs []# asmcmd
[Option -p will be used ]

## In asmcmd created the subdirectory with the name of the cloned database

ASMCMD [+] > cd +MYDBP_DATA01
ASMCMD [+MYDBP_DATA01] > ls
MYDBP/
ASMCMD [+MYDBP_DATA01] > mkdir MYDBA
ASMCMD [+MYDBP_DATA01] > cd MYDBA
ASMCMD [+MYDBP_DATA01/MYDBA] > ls
ASMCMD [+MYDBP_DATA01/MYDBA] > mkdir PASSWORD
ASMCMD [+MYDBP_DATA01/MYDBA] >

### And tried again

orapwd file='+MYDBP_DATA01/MYDBA/orapwdb' dbuniquename='MYDBA' entries=10
oracle@Mysrvrr:/app/oracle/product/12201/db/dbs []# orapwd file='+MYDBP_DATA01/MYDBA/orapwdb' dbuniquename='MYDBA'
Enter password for SYS:

OPW-00010: Could not create the password file. This resource has a Password File.

## Grrr checked with srvctl config and noticed that this one knows about pwfile too :
Database unique name: MYDBA
Database name:
Oracle home: /app/oracle/product/12201/db
Oracle user: oracle
Spfile:
Password file: +MYDBP_DATA01/MYDBA/PASSWORD/pwdMYDBa.312.981043193
Domain: PROD.NL
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: MYDBP_DATA01,MYDBP_FRA01
Mount point paths:
Services: MYDBA_TAF.prod.nl
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: MYDBA1,MYDBA2
Configured nodes: Mysrvrr,nlp1e6dr
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

## Removed the information about the password file in the cluster.

oracle@Mysrvrr: [MYDBA1]# srvctl modify database -db MYDBA -pwfile ”

##Checked again

oracle@Mysrvrr:/app/oracle/diag/rdbms/local10p/LOCAL10P/trace [MYDBA1]# srvctl config database -d MYDBA
Database unique name: MYDBA
Database name:
Oracle home: /app/oracle/product/12201/db
Oracle user: oracle
Spfile:
Password file:
Domain: PROD.NL
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: MYDBP_DATA01,MYDBP_FRA01
Mount point paths:
Services: MYDBA_TAF.prod.nl
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: MYDBA1,MYDBA2
Configured nodes: Mysrvrr,nlp1e6dr
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

## As they say in german  best way  to get things done is doning things right.

oracle@Mysrvrr:/app/oracle/diag/rdbms/local10p/LOCAL10P/trace [MYDBA1]# orapwd file=’+MYDBP_DATA01/MYDBA/orapwdb’ dbuniquename=’MYDBA’

Enter password for SYS:

## in sqlplus

SQL> grant sysdba to system ;

Grant succeeded.

SQL> select * from V$PWFILE_USERS;

USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS PASSWORD_PROFILE LAST_LOGIN LOCK_DATE EXPIRY_DATE EXTERNAL_NAME AUTHENTI COM CON_ID
-------------------------------------------------------------------------------------------------------------------------------- ----- ----- ----- ----- ----- ----- ------------------------------ -------------------------------------------------------------------------------
SYS TRUE TRUE FALSE FALSE FALSE FALSE OPEN PASSWORD NO 0
SYSTEM TRUE FALSE FALSE FALSE FALSE FALSE OPEN DEFAULT PASSWORD NO 0
AIM_DBA TRUE FALSE FALSE FALSE FALSE FALSE OPEN FUNCTIONAL_USER PASSWORD NO 0

Another small puzzle solved ,  but as always happy me again when being able to help  Colleagues / Customers.

Happy reading,

 

Mathijs