renaming an Oracle 11.2.0.2 database

To change an Oracle 11.2.0.2 DB name from DB1 to DB2

    STEP 1: Backup the database.
   
    STEP 2: Do a cleab shutdown of the database and mount the database after:

        SQL> SHUTDOWN IMMEDIATE
        SQL> STARTUP MOUNT

    STEP 3: Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

        nid TARGET=sys/password@DB1 DBNAME=DB2

        # nid TARGET=sys/password@TSH1 DBNAME=DB2
        DBNEWID: Release 11.2.0.2.0 - Production on Wed Aug 27 12:12:52 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

########################
########################
########################
########################
########################
########################
########################

        Database name changed to DB2.
        Modify parameter file and generate a new password file before restarting.
        Database ID for database DB2 changed to 3437107090.
        All previous backups and archived redo logs for this database are unusable.
        Shut down database and open with RESETLOGS option.
        Succesfully changed database name and ID.
        DBNEWID - Completed succesfully.

    STEP 4: Create new pfile from spfile.

    Shutdown the database:

        SHUTDOWN IMMEDIATE

    STEP 5: Modify the DB_NAME parameter in the initialization parameter file to reflect the new DB name .

      
    STEP 6: Create a new password file:

      
    orapwd file=$ORACLE_HOME/dbs/orapwDB2 password=choicepassword


    STEP 7: Rename the SPFILE to match the new DBNAME.
   

    STEP 8: On UNIX/Linux, reset the ORACLE_SID environment variable:
   

        ORACLE_SID=DB2; export ORACLE_SID

    STEP 9: Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
   

        lsnrctl reload

    STEP 10: Open the database with RESETLOGS:
   

        STARTUP MOUNT
        ALTER DATABASE OPEN RESETLOGS;

    STEP 11: Backup the database.



 ##########################################################################################################################

Issues encountered.



execution of DBNEWID failed initially with the error shown below:

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,                                                                          :nmchged);    end;
ORA-01000: maximum open cursors exceeded



Change of database name and database ID failed.
Must finish change or REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.



I tried to reverse my action using

 nid target=/ REVERT=Y

This also failed with the error below:

NID-00111: Oracle error reported from target database while executing
    begin       dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged,                                                                          :nmchged);    end;
ORA-01000: maximum open cursors exceeded



Reversion of database name and database ID failed.
Must finish REVERT changes before attempting any database operation.
DBNEWID - Completed with errors.



Resolution:

I had to correct the error ORA-01000: maximum open cursors exceeded

SYS> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

SYS> alter system set open_cursors=1024 SCOPE=BOTH;

System altered.

SYS> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     1024


The rename ran successfully after correcting this error.

###########################################################################################################

BEST PRACTICES:

Take controlfile backups – binary and trace backups before the bid operation.

If there are tempfiles in the dictionary, make sure they are accessible. Check both v$tempfile and dba_temp_files, not just dba_temp_files. The nid operation tries to update the dbname in tempfiles too and if it is not found, the operation fails. If there are tempfiles that doesn’t exist, it is better to drop them from the dictionary before the nid operation.

nid requires the database to be mounted in exclusive mode, so set the cluster_database=false if it is RAC(mandatory, else nid will say that the DB needs to be mounted in exclusive mode.

Check the open_cursors parameter and it is safe to be set to a very high value during the operation, this needs to be reverted back to the original value after nid operation.

SOURCES:

http://www.dbapundits.com/blog/oracle-utilities/nid-failure-and-open_cursors/
http://www.dba-oracle.com/t_rename_database_oracle_sid.htm