To find the current DB_NAME and ORACLE_SID: =========================================== Query the views v$database and v$thread. V$DATABASE gives DB_NAME V$THREAD gives ORACLE_SID If ORACLE_SID = DB_SID and db_name = DBNAME: To find the current value of ORACLE_SID: (For Oracle9i, must use SQL*Plus) SVRMGR> select instance from v$thread; INSTANCE ---------------- DB_SID To find the current value of DB_NAME: SVRMGR> select name from v$database; NAME --------- DBNAME
Modifying a database to run under a new ORACLE_SID: =================================================== 1. Shutdown the instance The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT. 2. Backup all control, redo, and data files. 3. Go through the .profile, .cshrc, .login, oratab, tnsnames.ora, (for SQL*Net version 2), and redefine the ORACLE_SID environment variable to a new value. For example, search through disks and do a grep ORACLE_SID * 4. Change locations to the "dbs" directory % cd $ORACLE_HOME/dbs and rename the following files: o init<sid>.ora (or use pfile to point to the init file) o control file(s). This is optional if you do not rename any of the controlfiles, and the control_files parameter is used. The "control_files" parameter is set in the "init<SID>.ora" file or in a file it references with the ifile parameter. Make sure that the control_file parameter does not point to old file names, if they have been renamed. o "crdb<sid>.sql" & "crdb2<sid>.sql", This is optional, these are only used at database creation. NOTE: for Oracle9i if using an SPFILE: You must rename it (i.e. spfile<sid>.ora) along with changing parameter values in the SPFILE. Please review the following for more information on SPFILE. [NOTE:162491.1] Spfile and Init.ora Parameter File Startup of an Oracle9i Instance [NOTE:137483.1] How to Modify the Content of an SPFILE Parameter File 5. To rename the database files and redo log files, follow the instructions in [NOTE:9560.1]. 6. Change the ORACLE_SID environment variable to the new value. 7. Check in the "$ORACLE_HOME/dbs" directory to see if the password file has been enabled. If enabled, the file "orapw<OLD_SID>" will exist and a new password file for the new SID must be created (renaming the old file will not work). If "orapw<OLD_SID>" does not exist, skip to step 8. To create a new password file, issue the following command as oracle owner: orapwd file=orapw<NEWSID> password=?? entries=<number of users to be granted permission to start the database instance> 8. Start up the database and verify that it works. Once this is done, shutdown the database and take a final backup of all control, redo, and data files. The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT. 9. When the instance is started, the control file is updated with the current ORACLE_SID.
Changing the "db_name" for a Database: ====================================== 1. Login to Server Manager (Oracle7 & Oracle8) SQL*Plus for Oracle9i % svrmgrl SVRMGR> connect internal NOTE: For Oracle9i, must use SQL*Plus (svrmgrl is obsolete in Oracle9i) % sqlplus "/ as sysdba" 2. Type SVRMGR> alter system switch logfile; to force a checkpoint. 3. Type SVRMGR> alter database backup controlfile to trace resetlogs; This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form. 4. Shutdown the database and exit SVRMGR SVRMGR> shutdown SVRMGR> exit The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT. 5. Change locations to the directory where the trace files are located. They are usually in the "$ORACLE_HOME/rdbms/log" directory. If "user_dump_dest" is set in the "init<SID>.ora" file, then go to the directory listed in the "user_dump_dest" variable. The trace file will have the form "ora_NNNN.trc with NNNN being a number. NOTE: for Oracle9i if using an SPFILE: Please review the following for changing parameter values in the SPFILE. [NOTE:137483.1] How to Modify the Content of an SPFILE Parameter File 6. Copy the contents of the trace file starting from the line with STARTUP NOMOUNT down to the end of the trace file and put it in a new file called something like "ccf.sql". 7. Edit the "ccf.sql" file FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ... TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ... Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'. It is possible to recreate the controlfile using the syntax: CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ... But this syntax will allow the existing controlfiles to be overwritten without giving an error. FROM: # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE TO: # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. # RECOVER DATABASE USING BACKUP CONTROLFILE The last command in ccf.sql should be: alter database open resetlogs 8. Save and exit the "ccf.sql" file 9. Rename the old control files for backup purposes and so that they do not exist when creating the new ones. 10. Edit the "init<SID>.ora" file so that db_name="newdb_name" . NOTE: for Oracle9i if using an SPFILE: Please review the following for changing parameter values in the SPFILE. [NOTE:137483.1] How to Modify the Content of an SPFILE Parameter File 11. Login to Server Manager % svrmgrl SVRMGR> connect internal NOTE: For Oracle9i, must use SQL*Plus (svrmgrl is obsolete in Oracle9i) % sqlplus "/ as sysdba" 12. Run the "ccf.sql" script SVRMGR> @ccf This will issue a startup nomount, and then recreate the controlfile. If, at this point, an error stating that a file needs media recovery is reported, then the database was not shutdown normally as specified in step 4. Try recovering the database using the redo in the current logfile, by issuing: SVRMGRL> recover database using backup controlfile until cancel; This will prompt for an archived redologfile. It may be possible to open the database after applying the current logfile. BUT this is not guaranteed. To apply the necessary redo, check the online logfiles and apply the one with the same sequence number as reported in the message. This usually is the logfile with status=CURRENT. If not apply, the logfiles in turn until the logfile with status=CURRENT has been applied. If, after applying the current logfile, the database will not open then it is highly likely that the operation must be restarted having shutdown the database normally. To find a list of the online logfiles: SVRMGR> select group#, seq#, status from v$log; GROUP# SEQUENCE# STATUS ---------- --------- ---------------- 1 123 CURRENT <== this redo needs to be applied 2 124 INACTIVE 3 125 INACTIVE 4 126 INACTIVE 5 127 INACTIVE 6 128 INACTIVE 7 129 INACTIVE 7 rows selected. SVRMGR> select member from v$logfile where GROUP# = 1; Member ------------------------------------ /u02/oradata/V815/redoV81501.log After applying the current online log file the following prompt should be displayed: Log Applied Media Recovery Complete At this point the database can be opened with: SVRMGR> alter database open resetlogs; 13. The global database name may also need to be changed: alter database rename global_name to <newdb_name>.<domain> See [NOTE:1018634.102] for further detail. 14. Make sure the database is working. 15. Shutdown and backup the database. The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.
Query the views v$database and v$thread.
V$DATABASE gives DB_NAME V$THREAD gives ORACLE_SID
If ORACLE_SID = DB_SID and db_name = DBNAME:
To find the current value of ORACLE_SID: (For Oracle9i, must use SQL*Plus)
SVRMGR> select instance from v$thread;
INSTANCE ---------------- DB_SID
To find the current value of DB_NAME:
SVRMGR> select name from v$database;
NAME --------- DBNAME
1. Shutdown the instance
The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.
2. Backup all control, redo, and data files.
3. Go through the .profile, .cshrc, .login, oratab, tnsnames.ora, (for SQL*Net version 2), and redefine the ORACLE_SID environment variable to a new value.
For example, search through disks and do a grep ORACLE_SID *
4. Change locations to the "dbs" directory
% cd $ORACLE_HOME/dbs
and rename the following files:
o init<sid>.ora (or use pfile to point to the init file) o control file(s). This is optional if you do not rename any of the controlfiles, and the control_files parameter is used. The "control_files" parameter is set in the "init<SID>.ora" file or in a file it references with the ifile parameter. Make sure that the control_file parameter does not point to old file names, if they have been renamed. o "crdb<sid>.sql" & "crdb2<sid>.sql", This is optional, these are only used at database creation.
NOTE: for Oracle9i if using an SPFILE: You must rename it (i.e. spfile<sid>.ora) along with changing parameter values in the SPFILE. Please review the following for more information on SPFILE. [NOTE:162491.1] Spfile and Init.ora Parameter File Startup of an Oracle9i Instance [NOTE:137483.1] How to Modify the Content of an SPFILE Parameter File
5. To rename the database files and redo log files, follow the instructions in [NOTE:9560.1].
6. Change the ORACLE_SID environment variable to the new value.
7. Check in the "$ORACLE_HOME/dbs" directory to see if the password file has been enabled. If enabled, the file "orapw<OLD_SID>" will exist and a new password file for the new SID must be created (renaming the old file will not work). If "orapw<OLD_SID>" does not exist, skip to step 8. To create a new password file, issue the following command as oracle owner:
orapwd file=orapw<NEWSID> password=?? entries=<number of users to be granted permission to start the database instance>
8. Start up the database and verify that it works. Once this is done, shutdown the database and take a final backup of all control, redo, and data files.
The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.
9. When the instance is started, the control file is updated with the current ORACLE_SID.
1. Login to Server Manager (Oracle7 & Oracle8) SQL*Plus for Oracle9i
% svrmgrl SVRMGR> connect internal
NOTE: For Oracle9i, must use SQL*Plus (svrmgrl is obsolete in Oracle9i) % sqlplus "/ as sysdba"
2. Type
SVRMGR> alter system switch logfile;
to force a checkpoint.
3. Type
SVRMGR> alter database backup controlfile to trace resetlogs;
This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.
4. Shutdown the database and exit SVRMGR
SVRMGR> shutdown
SVRMGR> exit
The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.
5. Change locations to the directory where the trace files are located. They are usually in the "$ORACLE_HOME/rdbms/log" directory. If "user_dump_dest" is set in the "init<SID>.ora" file, then go to the directory listed in the "user_dump_dest" variable. The trace file will have the form "ora_NNNN.trc with NNNN being a number.
NOTE: for Oracle9i if using an SPFILE: Please review the following for changing parameter values in the SPFILE. [NOTE:137483.1] How to Modify the Content of an SPFILE Parameter File
6. Copy the contents of the trace file starting from the line with STARTUP NOMOUNT down to the end of the trace file and put it in a new file called something like "ccf.sql".
7. Edit the "ccf.sql" file
FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ... TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...
Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'.
It is possible to recreate the controlfile using the syntax: CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ...
But this syntax will allow the existing controlfiles to be overwritten without giving an error.
FROM: # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE TO: # Recovery is required if any of the datafiles are restored backups, # or if the last shutdown was not normal or immediate. # RECOVER DATABASE USING BACKUP CONTROLFILE
The last command in ccf.sql should be: alter database open resetlogs
8. Save and exit the "ccf.sql" file
9. Rename the old control files for backup purposes and so that they do not exist when creating the new ones.
10. Edit the "init<SID>.ora" file so that db_name="newdb_name" .
NOTE: for Oracle9i if using an SPFILE: Please review the following for changing parameter values in the SPFILE. [NOTE:137483.1] How to Modify the Content of an SPFILE Parameter File
11. Login to Server Manager
% svrmgrl SVRMGR> connect internal
NOTE: For Oracle9i, must use SQL*Plus (svrmgrl is obsolete in Oracle9i) % sqlplus "/ as sysdba"
12. Run the "ccf.sql" script
SVRMGR> @ccf
This will issue a startup nomount, and then recreate the controlfile.
If, at this point, an error stating that a file needs media recovery is reported, then the database was not shutdown normally as specified in step 4. Try recovering the database using the redo in the current logfile, by issuing:
SVRMGRL> recover database using backup controlfile until cancel;
This will prompt for an archived redologfile. It may be possible to open the database after applying the current logfile. BUT this is not guaranteed.
To apply the necessary redo, check the online logfiles and apply the one with the same sequence number as reported in the message. This usually is the logfile with status=CURRENT. If not apply, the logfiles in turn until the logfile with status=CURRENT has been applied. If, after applying the current logfile, the database will not open then it is highly likely that the operation must be restarted having shutdown the database normally.
To find a list of the online logfiles:
SVRMGR> select group#, seq#, status from v$log; GROUP# SEQUENCE# STATUS ---------- --------- ---------------- 1 123 CURRENT <== this redo needs to be applied 2 124 INACTIVE 3 125 INACTIVE 4 126 INACTIVE 5 127 INACTIVE 6 128 INACTIVE 7 129 INACTIVE
7 rows selected.
SVRMGR> select member from v$logfile where GROUP# = 1;
Member ------------------------------------ /u02/oradata/V815/redoV81501.log
After applying the current online log file the following prompt should be displayed:
Log Applied Media Recovery Complete
At this point the database can be opened with:
SVRMGR> alter database open resetlogs;
13. The global database name may also need to be changed:
alter database rename global_name to <newdb_name>.<domain>
See [NOTE:1018634.102] for further detail.
14. Make sure the database is working.
15. Shutdown and backup the database.
The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not be shutdown abnormally using SHUTDOWN ABORT.