Thursday 11 January 2018

Change of DB_UNIQUE_NAME in a database

Today I created a new database. I created with usage of Oracle Universal Installer graphic interface. I did not find the switch for advanced configuration, where I could change some parameters along the installation phase, so I was left over with this what gives the basic configuration for Server-class database option. May be I missed some tab or checkbox, does not matter.
Now the starting point is to have a 12c standalone database registered in clusterware.
  • the simple change in the spfile ends with
    SQL> alter system set db_unique_name='pizmak_node3' scope=spfile;
    alter system set db_unique_name='pizmak_node3' scope=spfile
    *
    ERROR at line 1:
    ORA-32017: failure in updating SPFILE
    ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
    
  • so I found "How to change the DB_UNIQUE_NAME in a RAC database (Doc ID 1604421.1)", and remove the database from clusterware, but then
    SQL> startup mount
    ORA-01078: failure in processing system parameters
    ORA-01565: error in identifying file '+DATA/pizmak/spfilepizmak.ora'
    ORA-17503: ksfdopn:2 Failed to open file +DATA/pizmak/spfilepizmak.ora
    ORA-15056: additional error message
    ORA-17503: ksfdopn:2 Failed to open file +DATA/pizmak/spfilepizmak.ora
    ORA-15173: entry 'spfilepizmak.ora' does not exist in directory 'pizmak'
    ORA-06512: at line 4
    
    
    Apparently the oratab entry was removed with the srvctl remove database command
  • so I found "Alert Log Error ORA-17503 ORA-15173 After Deleting Database. (Doc ID 1900471.1)"
  • so I looked at $ORACLE_HOME/dbs and found pfile with SPFILE pointing to ASM location and put it aside
  • after some time I discovered true reason - initially spfile was located in +DATA/PIZMAK/ (possibly on some stage of db installation), but eventually the location was +DATA/PIZMAK/PARAMETERFILE
  • so I used the pfile in $ORACLE_BASE/admin/pizmak/pfile/, which is put there during installation
  • so I started with startup nomount pfile=my_pfile_copy - this pfile had already db_unique_name changed to the one I wanted to, no changes to db_create_file_dest (as it would point to
  • I've got info 12c is able to move files online to another location, so
    SQL> select file_id||':'||file_name from dba_data_files;
    
    FILE_ID||':'||FILE_NAME
    --------------------------------------------------------------------------------
    6:+DATA/PIZMAK/DATAFILE/users.302.965122931
    4:+DATA/PIZMAK/DATAFILE/undotbs1.303.965122931
    3:+DATA/PIZMAK/DATAFILE/sysaux.300.965122739
    1:+DATA/PIZMAK/DATAFILE/system.314.965139835
    
    alter database move datafile 1;
    alter database move datafile 3;
    alter database move datafile 4;
    alter database move datafile 6;
    
    SQL> select file_id||':'||file_name from dba_data_files;
    
    FILE_ID||':'||FILE_NAME
    --------------------------------------------------------------------------------
    6:+DATA/PIZMAK_NODE3/DATAFILE/users.303.965140667
    3:+DATA/PIZMAK_NODE3/DATAFILE/sysaux.301.965140607
    4:+DATA/PIZMAK_NODE3/DATAFILE/undotbs1.300.965140655
    1:+DATA/PIZMAK_NODE3/DATAFILE/system.314.965139835
    
  • now logs
    SQL> select member from v$logfile;
    
    MEMBER
    --------------------------------------------------------------------------------
    +DATA/PIZMAK/ONLINELOG/group_3.310.965122991
    +DATA/PIZMAK/ONLINELOG/group_3.311.965122991
    +DATA/PIZMAK/ONLINELOG/group_2.308.965122991
    +DATA/PIZMAK/ONLINELOG/group_2.309.965122991
    +DATA/PIZMAK/ONLINELOG/group_1.306.965122991
    +DATA/PIZMAK/ONLINELOG/group_1.307.965122991
    
    SQL> select GROUP#, SEQUENCE#, MEMBERS, STATUS from v$log;
    
        GROUP#  SEQUENCE#    MEMBERS STATUS
    ---------- ---------- ---------- ------------------------------------------------
             1          7          1 INACTIVE
             2          8          2 INACTIVE
             3          9          2 CURRENT
    
    
    alter database drop logfile group 1;
    alter database add logfile group 1 size 512m;
    -- here in alert log (as this was 1st log file on REDO) an error
    -- Thu Jan 11 14:41:29 2018
    -- ERROR: failed to establish dependency between database pizmak_node3 and diskgroup resource ora.REDO.dg
    
    
    alter database drop logfile group 2;
    alter database add logfile group 2 size 512m;
    -- no more errors as the structure already exists
    
    alter system switch logfile;
    alter system switch logfile;
    
    alter database drop logfile group 3;
    alter database add logfile group 3 size 512m;
    
    SQL> select member from v$logfile;
    
    MEMBER
    ------------------------------------------------------------------------------------------------------------------------
    +REDO/PIZMAK_NODE3/ONLINELOG/group_3.275.965141729
    +REDO/PIZMAK_NODE3/ONLINELOG/group_2.274.965141165
    +REDO/PIZMAK_NODE3/ONLINELOG/group_1.256.965140887
    
    
    
    I have to double members yet
  • now spfile
    create spfile='+DATA' from pfile='/oracle/m010162/init.ora.011201894952';
    -- and change in initpizmak.ora
    -- SPFILE='+DATA/pizmak_node3/parameterfile/spfile.311.965142657'
    !cp initpizmak.ora $ORACLE_HOME/dbs/
    
  • to move temporary tablespace I just created a new one and switched default temporary tablespace to it
    create temporary tablespace temp2 tempfile size 128m autoextend on next 128m maxsize 16g;
    alter database default temporary tablespace temp2;
    
    -- then as I wanted temporary tablespace named TEMP I did that once again
    create temporary tablespace temp tempfile size 128m autoextend on next 128m maxsize 16g;
    alter database default temporary tablespace temp;
    
    SQL> select file_name from dba_temp_files;
    
    FILE_NAME
    ------------------------------------------------------------------------------------------------------------------------
    +DATA/PIZMAK_NODE3/TEMPFILE/temp.312.965143967
    
    
  • to move control files I used asmcmd
    1. change control_files parameter to new location with scope spfile
    2. shutdown immediate
    3. copy or move control files to the new location in asmcmd (with cp one have to specify the name of the target file, but without instance/revision? number)
    4. start the db