Rename database using NID utility

We will rename PROD database to TEST using NID utility of oracle

Connect to PROD database, shutdown and start it in mount state

C:\Users\farif>set oracle_sid=prod

C:\Users\farif>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 08:27:41 2013

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, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2259480 bytes
Variable Size             234882536 bytes
Database Buffers          557842432 bytes
Redo Buffers                6717440 bytes
Database mounted.

Create a pfile from spfile of PROD database which we will used later after renaming database to update parameters.

SQL> create pfile from spfile;

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Invoke NID utility and use following command to rename db from PROD to TEST

C:\Users\farif>nid TARGET=sys/oracle@prod dbname=TEST

DBNEWID: Release 11.2.0.3.0 - Production on Mon May 27 08:32:06 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database PROD (DBID=228347871)

Connected to server version 11.2.0

Control Files in database:
    D:\APP\FARIF\ORADATA\PROD\CONTROL01.CTL
    D:\APP\FARIF\FAST_RECOVERY_AREA\PROD\CONTROL02.CTL

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

Proceeding with operation
Changing database ID from 228347871 to 2114642825
Changing database name from PROD to TEST
    Control File D:\APP\FARIF\ORADATA\PROD\CONTROL01.CTL - modified
    Control File D:\APP\FARIF\FAST_RECOVERY_AREA\PROD\CONTROL02.CTL - modified
    Datafile D:\APP\FARIF\ORADATA\PROD\SYSTEM01.DB - dbid changed, wrote new name
    Datafile D:\APP\FARIF\ORADATA\PROD\SYSAUX01.DB - dbid changed, wrote new name
    Datafile D:\APP\FARIF\ORADATA\PROD\UNDOTBS01.DB - dbid changed, wrote new name
    Datafile D:\APP\FARIF\ORADATA\PROD\USERS01.DB - dbid changed, wrote new name
    Datafile D:\APP\FARIF\ORADATA\PROD\TEMP01.DB - dbid changed, wrote new name
    Control File D:\APP\FARIF\ORADATA\PROD\CONTROL01.CTL - dbid changed, wrote new name
    Control File D:\APP\FARIF\FAST_RECOVERY_AREA\PROD\CONTROL02.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2114642825.
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.
-------------------------------------------------------------------
DELETE old SID PROD and create new SID TEST

C:\Users\farif>oradim -delete -sid prod
Instance deleted.

C:\Users\farif>oradim -new -sid TEST
Instance created.

Create pfile INITTEST.ora in ORACLE_HOME\database folder for database TEST and rename ORACLE_HOME\database\PWDprod.ora to PWDtest.ora

Connect to new database instance TEST

C:\Users\farif>set oracle_sid=test

C:\Users\farif>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 08:40:53 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.
----------------------------------------------------------------
Rename file ORACLE_HOME\database\initPRO.ora to initTEST.ora
Find and replace everything PROD to TEST in ORACLE_HOME\database\initTEST.ora
----------------------------------------------------------------
Content of initTEST.ora which we will use to start newly created instance TEST
--------------------------------------------------------------

test.__db_cache_size=557842432
test.__java_pool_size=4194304
test.__large_pool_size=4194304
test.__oracle_base='D:\app\farif'#ORACLE_BASE set from environment
test.__pga_aggregate_target=268435456
test.__sga_target=805306368
test.__shared_io_pool_size=0
test.__shared_pool_size=222298112
test.__streams_pool_size=4194304
*.audit_file_dest='D:\app\farif\admin\test\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\farif\oradata\test\control01.ctl','D:\app\farif\fast_recovery_area\test\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test'
*.db_recovery_file_dest='D:\app\farif\fast_recovery_area'
*.db_recovery_file_dest_size=52556726272
*.diagnostic_dest='D:\app\farif'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.open_cursors=300
*.pga_aggregate_target=268435456
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS1'
---------------------------------------------------------------------
SQL> startup nomount pfile=D:\app\farif\product\11.2.0\dbhome_1\database\initTEST.ora
ORACLE instance started.

Total System Global Area  801701888 bytes
Fixed Size                  2259480 bytes
Variable Size             234882536 bytes
Database Buffers          557842432 bytes
Redo Buffers                6717440 bytes




SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      D:\APP\FARIF\ORADATA\TEST\CONTROL01.CTL, D:\APP\FARIF\FAST_RECOVERY_AREA\TEST\CONTROL02.CT L
control_management_pack_access       string      DIAGNOSTIC+TUNING

RENAME DATAFILE DIRECTORIES

D:\APP\FARIF\ORADATA\PROD\
to
D:\APP\FARIF\ORADATA\TEST

RENAME FRA DIRECTORIES

D:\APP\FARIF\FAST_RECOVERY_AREA\PROD\
to
D:\APP\FARIF\FAST_RECOVERY_AREA\TEST\

RENAME ADMIN DIRECTORIES

D:\APP\FARIF\ADMIN\PROD\ADUMP
to
D:\APP\FARIF\ADMIN\TEST\ADUMP


SQL> alter database mount;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\APP\FARIF\ORADATA\PROD\SYSTEM01.DBF
D:\APP\FARIF\ORADATA\PROD\SYSAUX01.DBF
D:\APP\FARIF\ORADATA\PROD\UNDOTBS01.DBF
D:\APP\FARIF\ORADATA\PROD\USERS01.DBF

RENAME DATAFILES

ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\SYSTEM01.DBF' TO 'D:\APP\FARIF\ORADATA\TEST\SYSTEM01.DBF';
ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\SYSAUX01.DBF' TO 'D:\APP\FARIF\ORADATA\TEST\SYSAUX01.DBF';
ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\UNDOTBS01.DBF' TO 'D:\APP\FARIF\ORADATA\TEST\UNDOTBS01.DBF';
ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\USERS01.DBF' TO 'D:\APP\FARIF\ORADATA\TEST\USERS01.DBF';

RENAME REDO LOGS

ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\REDO01.LOG' TO 'D:\APP\FARIF\ORADATA\TEST\REDO01.LOG';
ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\REDO02.LOG' TO 'D:\APP\FARIF\ORADATA\TEST\REDO02.LOG';
ALTER DATABASE RENAME FILE 'D:\APP\FARIF\ORADATA\PROD\REDO03.LOG' TO 'D:\APP\FARIF\ORADATA\TEST\REDO03.LOG';

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> SELECT NAME FROM V$DATABASE;

NAME
---------
TEST

SQL>