====================================================================
In this post i would be changing the DB name of a single instance DB from TESTDB to RENAMEDB
====================================================================
-- check TESTDB is primary role and read write mode
[oracle@subhen-db01 ~]$ . oraenv
ORACLE_SID = [oracle] ? TESTDB
The Oracle base has been set to /oracle/app
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 30 15:57:45 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESTDB READ WRITE PRIMARY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
-- stop the database and start it in mount mode
[oracle@subhen-db01 ~]$ srvctl stop database -d TESTDB -o immediate
[oracle@subhen-db01 ~]$ srvctl start database -d TESTDB -o MOUNT
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 30 16:02:32 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESTDB MOUNTED PRIMARY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
-- starting with NID utility
[oracle@subhen-db01 ~]$ nid -help
DBNEWID: Release 11.2.0.3.0 - Production on Fri Nov 30 16:05:51 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)
DBNAME New database name (NONE)
LOGFILE Output Log (NONE)
REVERT Revert failed change NO
SETNAME Set a new database name only NO
APPEND Append to output log NO
HELP Displays these messages NO
[oracle@subhen-db01 ~]$ nid TARGET=sys@TESTDB DBNAME=RENAMEDB
DBNEWID: Release 11.2.0.3.0 - Production on Fri Nov 30 16:07:24 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TESTDB (DBID=2584054004)
Connected to server version 11.2.0
Control Files in database:
+DATA01/testdb/controlfile/current.827.800725877
Change database ID and database name TESTDB to RENAMEDB? (Y/[N]) => y
Proceeding with operation
Changing database ID from 2584054004 to 257482690
Changing database name from TESTDB to RENAMEDB
Control File +DATA01/testdb/controlfile/current.827.800725877 - modified
Datafile +DATA01/testdb/datafile/system.278.80072581 - dbid changed, wrote new name
Datafile +DATA01/testdb/datafile/sysaux.394.80072581 - dbid changed, wrote new name
Datafile +DATA01/testdb/datafile/undotbs1.838.80072581 - dbid changed, wrote new name
Datafile +DATA01/testdb/datafile/users.463.80072581 - dbid changed, wrote new name
Datafile +DATA01/testdb/tempfile/temp.555.80072589 - dbid changed, wrote new name
Control File +DATA01/testdb/controlfile/current.827.800725877 - dbid changed, wrote new name
Instance shut down
Database name changed to RENAMEDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database RENAMEDB changed to 257482690.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
-- starting with the new database RENAMEDB and change the parameter file to include the DBname as RENAMEDB
[oracle@subhen-db01 ~]$ export ORACLE_SID=RENAMEDB
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 30 16:08:53 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/database/11.2.0.3/dbhome_1/dbs/initRENAMEDB.ora'
SQL> exit
-- here i will create a pfile from the spfile of TESTDB(earlier database)
Disconnected
[oracle@subhen-db01 ~]$ ps -fe|grep pmon
oracle 5258 911 0 16:09 pts/2 00:00:00 grep pmon
oracle 19437 1 0 Jul24 ? 00:00:03 asm_pmon_+ASM
[oracle@subhen-db01 ~]$ . oraenv
ORACLE_SID = [RENAMEDB] ? +ASM
The Oracle base remains unchanged with value /oracle/app
[oracle@subhen-db01 ~]$ asmcmd
ASMCMD> ls
DATA01/
ASMCMD> cd *
ASMCMD> ls
ASM/
BLAINT1/
BLAINT2/
BLAOID1/
BLAOID2/
TESTDB/
ASMCMD> cd TESTDB/
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB.ora
ASMCMD> pwd
+DATA01/TESTDB
ASMCMD> exit
[oracle@subhen-db01 ~]$ export ORACLE_SID=RENAMEDB
[oracle@subhen-db01 ~]$ export ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 30 16:11:00 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create pfile='/tmp/initRENAME.ora' from spfile='+DATA01/TESTDB/spfileTESTDB.ora';
File created.
SQL> exit
Disconnected
-- modify the parameters like db_name='RENAMEDB' and start the database
[oracle@subhen-db01 ~]$ vi /tmp/initRENAME.ora
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 30 16:14:44 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/initRENAME.ora'
ORACLE instance started.
Total System Global Area 2355015680 bytes
Fixed Size 2230592 bytes
Variable Size 536872640 bytes
Database Buffers 1795162112 bytes
Redo Buffers 20750336 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RENAMEDB READ WRITE PRIMARY
note: control file and other locations still refer to the old locations
