Cannot change undo_tablespace parameter in RAC node

[johnnie]

Hi,

I am trying to change the undo tablespace in DB instance, unsuccessfully.

Approach 1:

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = FUSION_UNDOTS scope=BOTH;

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------- ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string FUSION_UNDOTS
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 9898557440 bytes
Fixed Size 3720936 bytes
Variable Size 3858762008 bytes
Database Buffers 6006243328 bytes
Redo Buffers 29831168 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>


Approach 2:

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>
SQL> create pfile='/tmp/initnew.ora' from spfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@abcdef tmp]$
[oracle@abddef tmp]$ vi initnew.ora #changing ORCL1.undo_tablespace='FUSION_UNDOTS'
[oracle@slc03phf tmp]$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL> startup pfile='/tmp/initnew.ora'
ORACLE instance started.

Total System Global Area 9898557440 bytes
Fixed Size 3720936 bytes
Variable Size 3858762008 bytes
Database Buffers 6006243328 bytes
Redo Buffers 29831168 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string FUSION_UNDOTS
SQL>
SQL> create spfile from pfile='/tmp/initnew.ora'
2 ;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 9898557440 bytes
Fixed Size 3720936 bytes
Variable Size 3858762008 bytes
Database Buffers 6006243328 bytes
Redo Buffers 29831168 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL>


Can anyone please help?
Thanks,
Johnnie

[gandolf989]

Are you using an spfile? If not the change will not persist. If you are, then you need to check the value of the spfile
using the strings commands before you restart the database to make sure that the change has persisted.

[johnnie]

Gandolf,

Thank you for the suggestion, it makes sense. In the process of following it, I used a slightly different command to create spfile from pfile, so instead of
create spfile from pfile='/tmp/initnew.ora';
I did
create spfile='+DATA/ORCL/spfileorcl.ora' from pfile='/tmp/initnew.ora';
That is, I specified explicitly the location of the spfile in the command. Surprisingly, that seems to have done the trick, now the undo_space has the desired value even after DB bounce!