Follow the below procedure
1. do: alter database backup controlfile to trace;
2. extract the "create controlfile" command from the
background-dump-destination tracefile.
3. shutdown the DB.
4. Change the DB-Name in your init<SID>.ora and change the init<NEWSID>.ora
5. Change the SID in the /etc/oratab or /var/opt/oracle/oratab
6. Change the SID in your environment and source it
7. Startup the database to mount-status
startup mount
8. Re-Create the controlfile with the statement from position 2.
9. Do a alter database rename global_name to <SID>
10.Change the TNS-Configuration accordingly
-- $ORACLE_HOME/network/admin/*.ora Look for SID and GLOBAL_NAME $
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:318216852435
-- Change SID
SQL> select instance from v$thread;
orcl
SQL> select name from v$database;
ORCL
-- to change
orcl -> pftest
ORCL -> PFTEST
-- .bash_profile, /etc/oratab, tnsnames.ora -> service_name,
-- orapwd file=orapw<NEWSID> password=?? entries=<number of users to be granted permission to start the database instance>
SQL> alter system switch logfile;
SQL> alter database backup controlfile to trace resetlogs;
берем директорию трэйсов из инит или спфайла
находим файл с CREATE CONTROLFILE
выдираем весь sql
создаем в домашней директории ccf.sql и складываем все туда
правим
FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" NORESETLOGS ...
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...
комментим
-- RECOVER DATABASE USING BACKUP CONTROLFILE
старые контрол файлы уносим куда то в бэкап
правим tnsnames -> название раздела
Edit the "init<SID>.ora" file so that db_name="newdb_name" (если через спфайл то
create pfile from spfile;
правим потом обратно
)
SQL>@ccf