nid failure and open_cursors

Last day I was working on changing the database name of a 4TB database. The database had nearly 4000 datafiles and I was slightly tensed about the operation. I had experience in which the nid operation failed resulting in a corrupt database and nothing could be done to recover from the scenario. nid utility provides an option to revert the operation, that too didn’t work as expected and even the attempt to recreate the controlfile did not succeed because of the partial updates(updates on some datafiles succeeded and failed for the rest). Another issue that I have seen with nid is with Oracle on AIX, wherein the nid utility fails if the tempfiles are not found during the rename operation and the failure was irreversible and catastrophic.

I’d prefer to recreate the controlfile with the “SET” clause for the database name in future instead of using nid utility after going through this failure.

So, here goes the story.

As always, take binary and trace backup of the control file before such operations.

1) Tried nid utility to change the dbname to the different name.

2) Disaster stuck! and the operation failed with the below error.

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.

3) Tried the REVERT operation by specifying the REVERT=Y option

nid target=/ REVERT=Y

Alas! the revert operation also failed with the same error :(

4) As the error message was pointing to the value of open_cursors parameter, I checked the parameter and it was set to 400. I decided to change the parameter and set it to 6000 – 2000 more than the number of datafiles in the database to be safe. Made the change to the parameter file, shutdown and mounted the database.

5) Ran the nid operation with REVERT=Y and it succeeded!

6) Ran the nid operation again with the new name and it went through without any issues.

7) Updated the new DB_NAME in parameter file, reverted the value of open_cursors to its original value, mounted the database and opened the database in RESETLOGS mode.

Oracle nid 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.

Good luck!

Share

4 comments to nid failure and open_cursors

  • May

    very nice document. Thanks for sharing! We ran into problem using nid to change db name for our 4TB database with 1300+ datafile before as well. We thought there was a limit of the # of datafiles in order to use nid. Will try increase the open_cursor setting next time (currently it is set to 1400).

  • admin

    Thank you, I’m following this practice ever since when changing database name.
    Recreating controlfile with the “SET” clause is another option, but not sure if that can also similar failure.

  • Vikas

    amazing doc below is the scenario i faced.

    nid target = / DBNAME = TARGETDB

    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 used the above steps and i could change the DB name and DBID successfully…

  • admin

    Thank you Vikas.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>