ORA-28017: The password file is in the legacy format.

Today I was trying to change password for one of my 12c database and got below error :-

SQL> alter user dbsnmp identified by "*************"
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.

Cause :- 

Looks like update to password file failed as the file format is wrong. 

Administrative privileges like sysbackup, syskm, sysdg cannot be granted unless the password file is in 12c Format

Solution :-

This error generally comes when you migrate your database from 11g to 12c

Regeneration of password will resolve this error.

PFB steps :-

1. Check which users have access to password file before regenerating it.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

2. Recreate your password file :-

==>orapwd file=$ORACLE_HOME/dbs/orapwamit entries=5 force=y

Enter password for SYS:

[Target_server][oracle][amit]

3. Now by default only sys have access to it

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

4. Grant sysdba privilege to DBSNMP user to have access to password file

grant sysdba to dbsnmp;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID

------------------------------ ----- ----- ----- ----- ----- ----- ----------

SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

DBSNMP                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

5. Now again go for the password change, this time it will be successful.

SQL> alter user dbsnmp identified by "*************";

User altered.

I hope this article helped you.

Thanks
Amit Rath

How to Clone Grid Infrastructure binaries for Oracle Server(Standalone)

Yesterday I was applying patch on one of our development servers and patch on Grid Home failed with space issue and binaries got corrupted.

I thought of reinstalling the grid binaries by cloning it from a fresh development server. PFB steps for that :-

1. Stop the source(fresh) grid binaries. Hostname is Source_server
2. Tar the binaries 
3. Scp to the target(corrupted) server.  Hostname is Target_server

4. Detach the grid home from the Central inventory in Corrupted server :-

==>./runInstaller.sh -silent -detachHome ORACLE_HOME=/opt/grid/12.1.0.2 ORACLE_HOME_NAME=" Ora11g_gridinfrahome1"
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
'DetachHome' was successful.
[target_server][oracle][]

5. Clone the grid home so that binaries are linked and grid home is attached to Central inventory

./runInstaller -clone -waitForCompletion  "ORACLE_HOME=/opt/grid/12.1.0.2" "ORACLE_HOME_NAME=OraHome1Grid" "INVENTORY_LOCATION=/opt/orainv/oraInventory" "LOCAL_NODE=target_server" "CRS=FASLE" "ORACLE_BASE=/opt/oracle" -silent -paramFile /opt/grid/12.1.0.2/clone/clone_oraparam.ini
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 233206 MB    Passed
Checking swap space: must be greater than 500 MB.   Actual 16383 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-03_05-47-08AM. Please wait ...You can find the log of this install session at:
 /opt/orainv/oraInventory/logs/cloneActions2017-10-03_05-47-08AM.log
..................................................   5% Done.
..................................................   10% Done.
..................................................   15% Done.
..................................................   20% Done.
..................................................   25% Done.
..................................................   30% Done.
..................................................   35% Done.
..................................................   40% Done.
..................................................   45% Done.
..................................................   50% Done.
..................................................   55% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   70% Done.
..................................................   75% Done.
..................................................   80% Done.
..................................................   85% Done.
..........Could not backup file /opt/grid/12.1.0.2/root.sh to /opt/grid/12.1.0.2/root.sh.ouibak
Could not backup file /opt/grid/12.1.0.2/rootupgrade.sh to /opt/grid/12.1.0.2/rootupgrade.sh.ouibak

Copy files in progress.

Copy files successful.

Link binaries in progress.

Link binaries successful.

Setup files in progress.

Setup files successful.

Setup Inventory in progress.

Setup Inventory successful.

Finish Setup successful.
The cloning of OraHome1Grid was successful.
Please check '/opt/orainv/oraInventory/logs/cloneActions2017-10-03_05-47-08AM.log' for more details.

Setup Oracle Base in progress.

Setup Oracle Base successful.
..................................................   95% Done.

As a root user, execute the following script(s):
        1. /opt/grid/12.1.0.2/root.sh


..................................................   100% Done.
[target_server][oracle][]

6. Execute the root.sh script

7. Configure your CRS instance on the cluster

While Configuring , I got below error :-

[root@target_server grid]# /opt/grid/12.1.0.2/perl/bin/perl -I/opt/grid/12.1.0.2/perl/lib -I/opt/grid/12.1.0.2/crs/install /opt/grid/12.1.0.2/crs/install/roothas.pl

Using configuration parameter file: /opt/grid/12.1.0.2/crs/install/crsconfig_params

2017/06/03 06:00:59 CLSRSC-350: Cannot configure two CRS instances on the same cluster

2017/10/03 06:00:59 CLSRSC-352: CRS is already configured on this node for the CRS home location /opt/grid/12.1.0.2

we have to deconfigure the existing one before configuring it again

8. Deconfigure the existing CRS 

[root@target_server install]# ./roothas.pl -deconfig -force

Using configuration parameter file: ./crsconfig_params

2017/10/03 06:13:02 CLSRSC-337: Successfully deconfigured Oracle Restart stack

9. Configure it again :-

[root@target_server install]# /opt/grid/12.1.0.2/perl/bin/perl -I/opt/grid/12.1.0.2/perl/lib -I/opt/grid/12.1.0.2/crs/install /opt/grid/12.1.0.2/crs/install/roothas.pl

Using configuration parameter file: /opt/grid/12.1.0.2/crs/install/crsconfig_params

LOCAL ADD MODE

Creating OCR keys for user 'oracle', privgrp 'sigdba'..

Operation successful.

LOCAL ONLY MODE

Successfully accumulated necessary OCR keys.

Creating OCR keys for user 'root', privgrp 'root'..

Operation successful.

CRS-4664: Node target_server successfully pinned.

2017/10/03 06:15:36 CLSRSC-330: Adding Clusterware entries to file 'oracle-ohasd.conf'

target_server 2017/10/03 06:16:02 /opt/grid/12.1.0.2/cdata/target_server/backup_20170603_061602.olr 3924561976

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'target_server'

CRS-2673: Attempting to stop 'ora.evmd' on 'target_server'

CRS-2677: Stop of 'ora.evmd' on 'target_server' succeeded

CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'target_server' has completed

CRS-4133: Oracle High Availability Services has been stopped.

CRS-4123: Oracle High Availability Services has been started.

2017/10/03 06:16:33 CLSRSC-327: Successfully configured Oracle Restart for a standalone server

9. Add the asm details and start the asm

==>srvctl add asm -d /dev/mapper/

[target_server][oracle][+ASM]

/dev/mapper$

==>

[target_server][oracle][+ASM]

/dev/mapper$

==>srvctl start asm

[target_server][oracle][+ASM]

/dev/mapper$

[target_server][oracle][+ASM]

/dev/mapper$

==>sqlplus  /as sysasm

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> alter diskgroup AMIT_GROUP mount;

Diskgroup altered.

10 . Add database to CRS 

==>srvctl add database -d amit -o /opt/oracle/product/database/12.1.0.2/

[target_server][oracle][amit]

/dev/mapper$

==>sqlplus /as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 8589934592 bytes

Fixed Size                  7663544 bytes

Variable Size            3288334408 bytes

Database Buffers         3137339392 bytes

Redo Buffers                9113600 bytes

In-Memory Area           2147483648 bytes

Database mounted.

SQL> alter database open;

Database altered.

Cloning of Grid home binaries completed.

I hope this article helped you

Thanks

Amit Rath