How to rename a database?

Following are the steps to rename a Database



1. Start by making a full database backup of your database (in case you need to restore if this procedure is not working).

2.Execute this command from sqlplus while connected to 'SYS AS SYSDBA':
ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;

3.Locate the latest dump file in your USER_DUMP_DEST directory (show parameter USER_DUMP_DEST) - rename it to something like dbrename.sql.

4.Edit dbrename.sql, remove all headers and comments, and change the database's name. Also change "CREATE CONTROLFILE REUSE ..." to "CREATE CONTROLFILE SET ...".
5.Shutdown the database (use SHUTDOWN NORMAL or IMMEDIATE, don't ABORT!) and run dbrename.sql.

6.Rename the database's global name:
ALTER DATABASE RENAME GLOBAL_NAME TO new_db_name;

* To rename database you must recreate controlfiles.
* Through the GUI (DBAstudio) you can only BACKUP CONTROLFILE TO TRACE;
* Then find created trace file in your USER_DUMP_DESTINATION catalog.
* It will be something like this:
* The following commands will create a new control file and use it to open the database.
* Data used by the recovery manager will be lost. Additional logs may
be required for media recovery of offline data files. Use this
only if the current version of all online logs are available.


Eg:-vi odsprod_ora_22609_cntrl.trc

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ODSQA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2336
LOGFILE
GROUP 1 (
'/u02A/oradata/admin/origlogA/redo01.log',
'/u02C/oradata/admin/mirrlogA/redo01_2.log'
) SIZE 50M,
GROUP 2 (
'/u02A/oradata/admin/origlogA/redo02.log',
'/u02C/oradata/admin/mirrlogA/redo02_2.log'
) SIZE 50M,
GROUP 3 (
'/u02A/oradata/admin/origlogA/redo03.log',
'/u02C/oradata/admin/mirrlogA/redo03_2.log'
) SIZE 50M,
GROUP 4 (
'/u02A/oradata/admin/origlogA/redo04.log',
'/u02C/oradata/admin/mirrlogA/redo04_2.log'
) SIZE 50M,
GROUP 5 (
'/u02A/oradata/admin/origlogA/redo05.log',
'/u02C/oradata/admin/mirrlogA/redo05_2.log'
) SIZE 50M,
GROUP 6 (
'/u02A/oradata/admin/origlogA/redo06.log',
'/u02C/oradata/admin/mirrlogA/redo06_2.log'
) SIZE 50M
DATAFILE
'/u01/app/oracle/product/10g/oradata/ODSQA/system01.dbf',
'/u04/oradata/ods1/undotbs01.dbf',
'/u01/app/oracle/product/10g/oradata/ODSQA/sysaux01.dbf',
'/u01/app/oracle/product/10g/oradata/ODSQA/users01.dbf',
'/u07/oradata/ods4/DSSODS01.dbf',
'/u06/oradata/ods3/DSSODS02.dbf',
'/u06/oradata/ods3/EODODS01.DBF',
'/u06/oradata/ods3/EODODS02.DBF',
'/u06/oradata/ods3/Datemp01.dbf',
'/u06/oradata/ods3/EODODS03.DBF',
'/u06/oradata/ods3/DATEMP02.DBF',
'/u06/oradata/ods3/EODODS04.DBF',
'/u06/oradata/ods3/Datemp03.dbf',
'/u06/oradata/ods3/EODODS05.DBF',
'/u06/oradata/ods3/Datemp04.dbf',
'/u06/oradata/ods3/Datemp05.dbf',
'/u06/oradata/ods3/datemp06.dbf',
'/u06/oradata/ods3/EODODS06.dbf',
'/u06/oradata/ods3/Datemp07.dbf',
'/u06/oradata/ods3/EODODS07.DBF',
'/u06/oradata/ods3/EODODS08.DBF',
'/u05/oradata/ods2/datemp07.dbf',
'/u05/oradata/ods2/Datemp08.dbf',
'/u05/oradata/ods2/EODODS09.DBF',
'/u05/oradata/ods2/DSSODS03.dbf'
CHARACTER SET WE8ISO8859P1
;

7. Recovery is required if any of the datafiles are restored backups,if the last shutdown was not normal or immediate.
RECOVER DATABASE;

8. Database can now be opened normally.
ALTER DATABASE OPEN;

9. Commands to add tempfiles to temporary tablespaces.

ALTER TABLESPACE TEMP ADD TEMPFILE '/u05/oradata/ods2/temp01.dbf' SIZE 3077M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

ALTER TABLESPACE ODSTEMP ADD TEMPFILE '/u05/oradata/ods2/ODSTEMP02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M;

ALTER TABLESPACE ODSTEMP ADD TEMPFILE '/u05/oradata/ods2/ODSTEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE ODSTEMP ADD TEMPFILE '/u05/oradata/ods2/ODSTEMP.dbf' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP04' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP03.DBF' SIZE 2048M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP02.DBF' SIZE 1024M REUSE AUTOEXTEND OFF;

ALTER TABLESPACE EODODSTEMP ADD TEMPFILE '/u05/oradata/ods2/EODODSTEMP01.DBF' SIZE 1024M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M;



Alter User MGMT_VIEW temporary tablespace TEMP;
Alter User DEVOPS temporary tablespace TEMP;
Alter User AKT007 temporary tablespace TEMP;
Alter User SYSMAN temporary tablespace TEMP;
Alter User SYS temporary tablespace TEMP;
Alter User SYSTEM temporary tablespace TEMP;
Alter User WMSYS temporary tablespace TEMP;
Alter User ORDSYS temporary tablespace TEMP;
Alter User EXFSYS temporary tablespace TEMP;
Alter User XDB temporary tablespace TEMP;
Alter User DMSYS temporary tablespace TEMP;
Alter User OLAPSYS temporary tablespace TEMP;
Alter User MDDATA temporary tablespace TEMP;
Alter User SI_INFORMTN_SCHEMA temporary tablespace TEMP;
Alter User ORDPLUGINS temporary tablespace TEMP;
Alter User OUTLN temporary tablespace TEMP;
Alter User MDSYS temporary tablespace TEMP;
Alter User TSMSYS temporary tablespace TEMP;
Alter User CTXSYS temporary tablespace TEMP;
Alter User DIP temporary tablespace TEMP;
Alter User ANONYMOUS temporary tablespace TEMP;
Alter User SCOTT temporary tablespace TEMP;

Create TEMPORARY TABLESPACE ODSTEMP TEMPFILE '/u05/oradata/ods2/ODSTEMP02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


Create TEMPORARY TABLESPACE EODODSTEMP TEMPFILE '/u05/oradata/ods2/EODODSTEMP04' SIZE 2048M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
alter database default temporary tablespace ODSTEMP;

DROP tablespace temp;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u05/oradata/ods2/temp01.dbf' SIZE 3077M REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M


10.we have to edit .bash profile

vi .profile
".profile" 20 lines, 721 characters
ORACLE_HOME=/u01/app/oracle/product/10g;
export ORACLE_HOME
ORACLE_SID=ODSQA;export ORACLE_SID
##ORACLE_SID=ODSPROD;export ORACLE_SID
ORACLE_PATH=/opt/bin;export ORACLE_PATH
PATH=$ORACLE_HOME/bin:$OPENWINHOME/bin:/usr/sbin/:/usr/ucb/:$PATH;export PATH
CURRENT_USER=`whoami`;export CURRENT_USER
LD_LIBRARY_PATH=$ORACLE_HOME/10.2.0/db_1/lib:$OPENWINHOME/lib:/cdrom/lib;
export LD_LIBRARY_PATH
EDITOR=vi;export EDITOR
ORACLE_DOC=$ORACLE_HOME/doc; export ORACLE_DOC
## Prompt Setup
PS1="`echo $CURRENT_USER`@`hostname | sed 's/\..*//'`>"
## end
#alias bdump='cd /u01/app/oracle/product/10g/admin/ODSPROD/bdump'
#alias archive='cd /u03/oradata/archivelogs'
#alias pfile='cd /u01/app/oracle/product/10g/dbs'
. ./.profile

11.edit initalisation parameter file with new database name

vi initODSQA.ora

ODSQA.__db_cache_size=10049552384
ODSQA.__java_pool_size=33554432
ODSQA.__large_pool_size=16777216
ODSQA.__shared_pool_size=1660944384
ODSQA.__streams_pool_size=33554432
*.audit_file_dest='/u01/app/oracle/product/10g/admin/ODSQA/adump'
*.background_dump_dest='/u01/app/oracle/product/10g/admin/ODSQA/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10g/oradata/ODSQA/control01.ctl','/u04/oradata/ods1/control02.ctl','/u05/oradata/ods2/control03.ctl'
*.core_dump_dest='/u01/app/oracle/product/10g/admin/ODSQA/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ODSQA'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ODSQAXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u03/oradata/archivelogs/'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=3221225472
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=11811160064
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/product/10g/admin/ODSQA/udump'


12.Create necessary folders in corresponding drives(/u01/app/oracle/product/10g/admin/ODSQA/)
mkdir bdump cdump udump adump

13.copy control file
/u01/app/oracle/product/10g/oradata/
mkdir ODSQA
oracle@DSS-ODSQA>cp ../ODSPROD//control01.ctl

CREATE CONTROLFILE REUSE DATABASE "ODSQA" NORESETLOGS NOARCHIVELOG





* DON’T FORGET BACKUP YOUR DATABASE BEFORE SCRIPT EXECUTION! *

Thanks and regards

Jafar Ali