Solution to error NID-00135 in Oracle 10g, Solaris 10

Question

We are trying to build a test version of a production database by restoring the production backup using RMAN and renaming the database to test.

We've successfully restored and opened the database as production (with different IP from the real production server).

When using the NID utility to rename the database to test, we are encountering this error:

NID-00135: There are 1 active threads.

We've been having difficulty getting the database to shutdown normally using the SHUTDOWN IMMEDIATE command. After an hour or so of waiting for the damn thing to die, we are forced to resort to grepping all the ora_* processes and killing them.

We left the listener up, the SQLPlus thread running, the bash thread owned by the Oracle user running, but that's about it for all Oracle-owned processes.

We've even restarted the server.

Are we missing something here?

Answer 1

The "damn thing" tries to prevent you from destroying your database. The error is described in the manual. You did not await the shutdown immediate of your database but killed the oracle processes. Therefor not all sessions could be rolled back. Start the database again and make a shutdown immediate and wait until the database is shut down cleanly.

Stop the listner make a local connection with the dbnewid utility. The dbnewid utility is described in the manual too. You have always a good chance to destroy your database if you handle the dbnewid utility in the wrong way. The dbnewid is necessary to change the id of the database that is used by rman in an rmancatalog. if you dont use an rmancatalog th change of the dbid is not necessary. Renaming can be done by recreating the controlfile. Often it suffices only to change the insatnce name. This can be done in the parameterfile (init.ora, spfile.ora)

The simplest and therefore best way to create a copy of the production database for testing is to use the rman duplicate database command.

The reason why it tooks so long to shutdown your database is that there is a long running transaction that must be rolled back. Such a situation can be avoided if you recover your database to a point in time where no such long transactions where running on the database.