dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Cannot change undo_tablespace parameter in RAC node

  1. #1
    Join Date
    Feb 2018
    Posts
    2

    Cannot change undo_tablespace parameter in RAC node

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,165
    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.
    this space intentionally left blank

  3. #3
    Join Date
    Feb 2018
    Posts
    2
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width