Tuesday, January 20, 2009

Changing ORACLE_SID, database name

1) Find instance name and db name
SQL> select instance from v$thread; --> Instance name
SQL> select name from v$database; --> Database name

2) Change instance name

SQL> create pfile from spfile;
SQL> shutdown immediate;

Change ORACLE_SID on /etc/oratab and .profile scripts, tnsnames.ora

Go to the $ORACLE_HOME/dbs directory and change the name of the pfile , review pfile entries. Set ORACLE_SID variable pointing the new instance name and create a new password file.
SQL>create spfile from pfile;
SQL>startup;

Run the following sql to verify the instance name changed

SQL> select instance from v$thread;
SQL> select name from v$database;

3) Change database name

Attention the db_name must be 8 characters.

Connect as sysdba and do
SQL> alter system switch logfile; to force a checkpoint
SQL> alter database backup controlfile to trace resetlogs;
SQL> shutdown immediate;

Edit the trace file contaning the control file backup by copying all commands from startup nomount to the end. Change the REUSE DATABASE "OLD_DB_NAME" to SET DATABASE "NEW_DBNAME".
Recovery commands will ne needed if the shutdown was not normal or immediate; Copy the commands until ALTER DATABASE OPEN RESETLOGS;

Edit in pfile the db_name parameter. Take a backup of previous control files if you did not change the control_files parameter in the pfile.

SQL> create spfile from pfile;
SQL> @cf.sql (control file script)

You should see ..

Control file created.
Database altered.

SQL> shutdown immediate;
SQL> startup
SQL> create pfile from spfile;
SQL> select instance from v$thread;
SQL> select name from v$database;

To confirm changes !!!

Add tempfile to temporary tablespace if needed.

If you have configured dbconsole you must drop and recreate repository.

No comments:

Post a Comment