Look in:

Web oracle-core-dba.blogspot.com

Tuesday, November 06, 2007

Changing the current Database Name and DB ID

CHANGING DBNAME & DBID
-----------------------

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT

$nid TARGET=sys/sys@swdm DBNAME=swm


Change database ID and database name SWDM to SWM? (Y/[N]) => Y

SQL>SHUTDOWN IMMEDIATE

Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

SQL>STARTUP MOUNT
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=SPFILE;
SQL>SHUTDOWN IMMEDIATE

Create a new password file:

$cd /u01/app/oracle/product/10.1.0/db_1/dbs/

$orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10

If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:

$ORACLE_SID=SWM; export ORACLE_SID

Rename the SPFILE to match the new DBNAME.

If you are using Windows you must recreate the service so the correct name and parameter file are used:

oradim -delete -sid SWDM
oradim -new -sid SWM -intpwd password -startmode a -pfile c:\oracle\920\database\spfileSWM.ora

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

$lsnrctl reload

Open the database with RESETLOGS:

SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;

Backup the database.

==========================================================================
CHANGING DBNAME Only
---------------------

Repeat the process as before except use the following command to start the DBNEWID utility:

$nid TARGET=sys/sys@SWDM DBNAME=swm SETNAME=YES

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER SYSTEM SET DB_NAME=SWM SCOPE=spfile;
SQL>SHUDOWN IMMEDIATE;

On Windows:
C:\> orapwd file= C:\oracle\product\10.1.0\db_1\database\pwdSWM.ora password=xxxxxxx entries=10

On Unix:
$ orapwd file=/u01/app/oracle/product/10.1.0/db_1/dbs/orapw password=sys entries=10

SQL>STARTUP;
SQL>SHOW PARAMETER DB_NAME

We have changed only the database name, The SETNAME parameter tells the DBNEWID utility to only alter the database name. and

not the database ID, it is not necessary to use the RESETLOGS option when you open the database

Backup the database.
===========================================================================

CHANGING DBID Only
-------------------
Backup the database.
Mount the database after a clean shutdown:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT

Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:

$nid TARGET=sys/password@SWDM

Shutdown and open the database with RESETLOGS:

SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP MOUNT
SQL>ALTER DATABASE OPEN RESETLOGS;

Backup the database.

No comments: