Oracle version :- 11.2.0.1.0
Database Sid : prim
Step 1.
Find out current dbname:-
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string prim
Step 2.
Shutdown & start database in mount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 318769144 bytes
Database Buffers 88080384 bytes
Redo Buffers 4308992 bytes
Database mounted.
Step 3.Changing dbname .
We are changing the dbname from prim to stand using NID utility.
[oracle@server1 ~]$ nid target=sys/password dbname=stand
DBNEWID: Release 11.2.0.1.0 - Production on Sun Dec 28 00:40:20 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to database PRIM (DBID=4130009889)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Change database ID and database name PRIM to STAND? (Y/[N]) => y
Proceeding with operation
Changing database ID from 4130009889 to 1595074421
Changing database name from PRIM to STAND
Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed, wrote new name
Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed, wrote new name
Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed, wrote new name
Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to STAND.
Modify parameter file and generate a new password file before restarting.
Database ID for database STAND changed to 1595074421.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
Step 4. Modify parameter file and generate a new password file and restart the dbase.
[oracle@server1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstand password=redhat force=y
Now create a new pfile from the existing spfile.
SQL> create pfile from spfile;
File created.
[oracle@server1 ]$cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ cp initprim.ora initstand.ora
change *.db_name='prim' to *.db_name='stand' in init file.
Step 5. Open the database with Resetlogs option.
[oracle@server1 dbs]$ export ORACLE_SID=stand
[oracle@server1 dbs]$ sqlplus / as sysdba
SQL> startup nomount from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 339740664 bytes
Database Buffers 67108864 bytes
Redo Buffers 4308992 bytes
SQL> ALTER SYSTEM SET DB_NAME=STAND SCOPE=SPFILE;
System altered.
SQL> SHUT IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
File created.
SQL> STARTUP mount;
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 339740664 bytes
Database Buffers 67108864 bytes
Redo Buffers 4308992 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
stand
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string STAND
